深入了解MySQL索引
目的
使以下查询速度更快：
- 按照id查询唯一一条记录
- 按照某些个字段查询对应的记录
- 查找某个范围的所有记录（between and）
- 对查询出来的结果排序
本质
通过不断的缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，通过索引机制，我们可以总是用同一种查找方式来锁定数据。
页与磁盘
磁盘块：文件系统与磁盘交互的最小单位，一个磁盘块由连续几个扇区组成，一般大小为4KB
页：MySQL中和磁盘交互的最小单位称为页，是一种数据结构，默认为16KB，相当于4个磁盘块。
B-树和B+树
区别
- B+树除叶子节点之外其他节点值存储关键字和指向子节点的指针，B-树还存储了数据，同样大小情况下，B+树可以存储更多的关键字
- B+树叶子节点中存储了所有关键字和data，并且多个节点用链表连接，子节点从左到右是有序的，可以支持范围查找。
如何选择
- B-树非叶子节点也存储数据，在查找某个关键字的时候找到即可返回。在同样高度的两种树中，B-树查找某个关键字的效率更高。
- 在找大于某个关键字或者小于某个关键字的数据的时候，B+树只需要找到该关键字然后沿着链表遍历即可，但B-树需要遍历该关键字结点的根结点去搜索。
- 同样总量的数据，B-树的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。
MySQL的存储引擎和索引
InnoDB
两种索引
主键索引/聚集：每个表只有一个主键索引，叶子节点同时保存主键的值和数据
辅助索引/非聚集：叶子节点保存了索引字段的值以及主键的值
辅助索引查询的过程叫回表：先在辅助索引中检索到数据，获取id，然后通过id在主键索引中检索。
MyISAM
两种索引的叶子节点保存了索引字段的值以及数据记录的地址。
过程：在索引中找到关键字，获取地址，通过地址找到数据。
为什么InnoDB的辅助索引不存地址？
表中的数据发生变更的时候，会影响其他记录地址的变化，如果辅助索引中记录数据的地址，此时会受影响，而主键的值一般是很少更新的，当页中的记录发生地址变更的时候，对辅助索引是没有影响的。
索引分类
-
主键索引
每个表都会有，整个表的数据存储在聚集索引中，B+树，非叶子节点存主键的值，叶子节点存主键的值+记录的数据
表中未指定主键时，自动给每条记录添加隐藏的rowid字段作为主键
聚集索引
-
辅助索引
非聚集索引
b+树，非叶子节点存索引字段，叶子节点存索引字段和主键的值
- 单列索引：一个索引只包含一个列
- 多列索引（复合索引）：一个索引包含多个列
- 唯一索引：索引列的值必须唯一，允许有一个空值
CREATE [UNIQUE] INDEX 索引名称 ON 表名(列名);
SHOW INDEX FROM 表名; 查看索引信息
EXPLAIN select * from test1 where sex=2 and name='javacode3500000';
最左匹配原则
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
eg：
按照[a,c]两个字段查询
这种只能利用到索引中的a字段了，通过a确定索引范围，然后加载a关联的所有记录，再对c的值进行过滤。
查询a=1 and b>=0 and c=1的记录
这种情况只能先确定a=1 and b>=0所在页的范围，然后对这个范围的所有页进行遍历，c字段在这个查询的过程中，是无法确定c的数据在哪些页的，此时我们称c是不走索引的，只有a、b能够有效的确定索引页的范围。
类似这种的还有>、<、between and，多字段索引的情况下，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
索引区分度
索引区分度 = count(distinct 记录) / count(记录)
当索引区分度高的时候，检索数据更快一些，索引区分度太低，说明重复的数据比较多，检索的时候需要访问更多的记录才能够找到所有目标数据。
当索引区分度非常小的时候，基本上接近于全索引数据的扫描了，此时查询速度是比较慢的。
创建索引时，尽量选择区分度高的列。
当多个条件中有索引的时候，并且关系是and的时候，会走索引区分度高的
覆盖索引
查询的内容只包含索引字段+id，这个时候在进行辅助索引的检索的时候，就已经可以得到select的全部数据，就不需要进行回表继续检索主键索引。
所以写sql的时候，尽量避免使用*，*可能会多一次回表操作，需要看一下是否可以使用索引覆盖来实现，效率更高一些。
索引下推
ICP(Index Condition Pushdown)，MySQL5.6的新特性，使用索引过滤数据的一种优化方式，减少存储引擎访问及表的次数以及MySQL服务器访问存储引擎的次数。
eg：
我们需要查询name以javacode35开头的，性别为1的记录数，sql如下
select **count**(id) from test1 a where name like 'javacode35%' and sex = 1;
name为辅助索引的过程
- 走name索引检索出以javacode35的第一条记录，得到记录的id
- 利用id去主键索引中查询出这条记录R1
- 判断R1中的sex是否为1，然后重复上面的操作，直到找到所有记录为止。
上面的过程中需要走name索引以及需要回表操作。
采用ICP的方式，创建一个(name,sex)的组合索引，查询过程如下：
- 走(name,sex)索引检索出以javacode35的第一条记录，可以得到(name,sex,id)，记做R1
- 判断R1.sex是否为1，然后重复上面的操作，知道找到所有记录为止
这个过程中不需要回表操作了，通过索引的数据就可以完成整个条件的过滤，速度比上面的更快一些。
使用索引的小tips
- 在区分度高的字段上面建立索引可以有效的使用索引，区分度太低，无法有效的利用索引，可能需要扫描所有数据页，此时和不使用索引差不多
- 联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
- 查询记录的时候，少使用*，尽量去利用索引覆盖，可以减少回表操作，提升效率
- 有些查询可以采用联合索引，进而使用到索引下推（ICP），也可以减少回表操作，提升效率
- 禁止对索引字段使用函数、运算符操作，会使索引失效
- 字符串字段和数字比较的时候会使索引无效
- 模糊查询'%值%'会使索引无效，变为全表扫描，但是'值%'这种可以有效利用索引
- 排序中尽量使用到索引字段，这样可以减少排序，提升查询效率
浙公网安备 33010602011771号