MySql索引原理和SQL优化方式

目录

• 一、索引与约束
• 1、索引是什么
• 2、索引的分类
• 列的属性-索引约束
• 数据结构
• 索引实现-物理存储
• 3、使用索引的场景
• 二、索引方式
• 1、聚集索引
• 2、辅助索引（二级索引）
• 3、覆盖索引
• 4、最左匹配规则
• 5、索引下推
• 三、索引的失效和原则
• 1、索引失效
• 2、索引原则
• 四、怎么解决慢的问题
• 总结

一、索引与约束

1、索引是什么

索引是一种有序的存储结构，它按照单个或者多个列的值进行排序。

并且它分为：主键索引、唯一索引、普通索引、组合索引、以及全文索引。

我们使用索引的目的就是为了提升搜索的效率。

2、索引的分类

列的属性-索引约束

• 主键索引：非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息；
• 唯一索引：不可以出现相同的值，可以有 NULL 值；
• 普通索引：允许出现相同的索引内容；
• 组合索引：对表上的多个列进行索引；
• 全文索引：将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT; 在短字符串中用 LIKE % ；在全文索引中用 match 和 against ；

-- 主键索引
PRIMARY KEY(key1, key2)

--唯一索引
UNIQUE(key)

--普通索引
INDEX(key)
-- OR
KEY(key[,...])

--组合索引
INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

约束：为了实现数据的完整性，对于 innodb，提供了以下几种约束，primary key，unique key，foreign key，default，not null；

其中外键约束：用来关联两个表，来保证参照完整性；MyISAM 存储引擎本身并不支持外键，只起到注释作 用；而 innodb 完整支持外键，并具备事务性；

创建主键索引或者唯一索引的时候同时创建php了相应的约束；但是约束时逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式；

数据结构

索引包括多种数据结构，其中最常用的就是B+数索引，hash索引，全文索引。我www.devze.com们本文主要讨论的是在InnoDB引擎中所使用的B+数索引。那么为什么我们不使用红黑树呢？

首先B+树全称：多路平衡搜索树。对于瘦高的红黑树来说B+树是胖矮的。我们把所有的数据存放在叶子节点中，而且叶子节点还串联在一起，一个页中可以存放几个叶子节点，而非叶子节点存放索引内容，并且也放在页中，我们可以看下图。

当我们查找一个数据的时候，可以使用更少的磁盘IO就可以获得想要的数据。比如我们想要查找25这个节点的数据，先查找第一个页，找到25的位置，在看第二个页，，在找到第一个叶子节点，然后平移过去找到25这个节点，一共有4次磁盘IO（每次查找页就是一次IO）。

但是使用的是红黑树的话，那么就不止4次的磁盘IO了。当然除了更少的磁盘IO后，也是为了方便范围查找。对于B+树来说，他的叶子节点都串联在一起，当找到第一个节点之后，就可以相继找出其他节点。但是红黑树来说的话，每次都要重新查找叶子节点。

总结：可以减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K，innodb 默认页大小为 16K；对页的访问是一次磁盘 IO，缓存中会缓存常访问的页； 平衡二叉树（红黑树、AVL 树） 特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范围查询；编程客栈 每个索引对应着一个 B+ 树；

索引实现-物理存储

innodb 由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由 64 个 连续页构成）；页的默认值为 16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页连续，存储引擎一般一次从磁盘中申请 4~5 个区；

3、使用索引的场景

我们每次搜索数据都是通过索引来实现的，其中在哪里可以使用到索引呢？是在where，group by，order by后面使用索引的。那么哪些场景不适合使用索引呢？首先就是没有where，group by，order by的地方，还有区分度不高的列，需要经常修改的列，表数据量少。

我们创建B+树类型的索引就是为了通过比较来找到我们所需要的数据，但是当区分度不高的时候，反而会降低速度，如果经常修改这个列，那么我们的B+的结构就要经常变化，更加影响速率，表的数据较少的时候，没有必要去创建索引，创建索引反而会浪费空间。

学习了上面所讲述的B+树和索引之后来想一下下面几个经典的面试题吧：

• 为什么采用多路的树结构？一个节点有多条链路，相较于平衡二叉搜索树是一个更加矮胖的结构，树的高度更低，可以较少的磁盘io次数来索引数据。
• 为什么非叶子节点只存储索引信息？B+树节点映射固定的大小磁盘数据，可以包含更多的索引信息。能快速锁定数据所在叶子节点的位置。
• 为什么叶子节点依次相连？便于范围查询，避免中序遍历回溯回去查找下一个节点。

二、索引方式

1、聚集索引

按照主键构造的 B+ 树，叶子节点中存放数据页中，数据也是索引的一部分。

一般来说主键索引就可以作为聚集索引，当没有主键的时候，如果有唯一索引，那唯一索引也可以作为聚集索引。18

-- u编程客栈ser表中 有id主键
select * from user where id >= 18 and id < 40;

我们通过上面的SQL语句，进行主键索引（聚集索引），从结构中查找18的位置，然后一层一层找，最后在叶子节点中找到，然后18到40的位置是连续的，我们节点的查找也是顺序的。并且这里的叶子节点，全部都是保存的数据。

2、辅助索引（二级索引）

叶子节点编程客栈不包含行记录的全部数据，辅助索引的叶子节点中，除了用来排序的 key 还包含一个 bookmark ，该书签存储了聚集索引的 key；

-- user表 包含 id name lockyNum; id是主键，lockyNum 辅助索引;
select * from user where lockyNum = 33;

由于这里使用的是辅助索引，在辅助索引中，叶子节点中存储的并不是数据，而是主键的id，当我们通过辅助索引找到相应的位置之后，根据查找到的主键id，再进入聚集索引中，然后操作就是上面聚集索引的过程了。后面简称回表查询。

3、覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引 树；较少磁盘 IO；在实际中我们select后一定不要*，而是具体的写出想要查找什么字段。

4、最左匹配规则

对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配；在下面的索引中，是组合索引，当我们使用id，name，age；id，name；id；这三种方式去索引的话，就可以走索引结构，但是一旦前面没有id之后，那么就不会走索引结构。也就是说，最左匹配规则，必须要按着从左往右的顺序来。

KEY（id，name，age）

5、索引下推

为了减少回表次数，提升查询效率；在 mysql 5.6 的版本开始推出； MySQL 架构分为 server 层和存储引擎层； 没有索引下推机制之前，server 层向存储引擎层请求数据，在 server 层根据索引条件判断进行数据 过滤； 有索引下推机制之后，将部分索引条件判断下推到存储引擎中过滤数据；最终由存储引擎将数据汇 总返回给 server 层；

三、索引的失效和原则

1、索引失效

• 1、select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
• 2、索引字段参与运算，则索引失效；例如： from_Unixtime(idx) = '2021-04-30'; 改成 idx = unix_timestamp("2021-04-30")
• 3、索引字段发生隐式转换，则索引失效；例如：将列隐式转换为某个类型，实际等价于在索引列上作 用了隐式转换函数；
• 4、LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select * from user where name like '%Mark';
• 5、在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；
• 6、组合索引中，没使用第一列索引，索引失效；

2、索引原则

• 1、查询频次较高且数据量大的表建立索引，索引选择使用频次较高，过滤效果好的列或者组合；
• 2、使用短索引；节点包含的信息多，较少磁盘 IO 操作；比如： smallint ， tinyint ；
• 3、对于很长的动态字符串，考虑使用前缀索引；
• 4、对于组合索引，考虑最左侧匹配原则、覆盖索引；
• 5、尽量选择区分度高的列作为索引；该列的值相同的越少越好；
• 6、尽量扩展索引，在现有索引的基础上，添加复合索引；最多 6 个索引；
• 7、不要 select * ； 尽量只列出需要的列字段；方便使用覆盖索引；
• 8、索引列，列尽量设置为非空；
• 9、可以开启自适应 hash 索引或者调整 change buffer；

四、怎么解决慢的问题

我们通过使用 EXPLAIN 来查看 SQL 语句的具体执行过程。 原理：模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。

首先我们需要找到SQL这个语句在哪里，通过 show processlist 列出较慢的连接通道来 以及使用慢查询日志来找到具体的SQL语句。再分析SQL中我们要先查看在where、group by、order by中是否使用索引，如果没有使用，那么就可以考虑是否添加索引，然后继续优化SQL语句中in和not in 变成联合查询，并且减少整体的联合查询。以及一个隐形的问题：age问题，应该存储出生年月，让客户端进行计算年纪。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。