mysql索引底层

参考链接：掘金

今天聊聊mysql的索引吧

今天看了一篇掘金平台的一篇关于mysql的索引与调优的文章，我觉得作者写的特别好理解而且全面，传送链接在本文末尾哦。

下面就写写我今天学习到的mysql索引的原理吧。

在聊mysql的索引原理之前，我觉得有必要跟大家解释一下什么是索引，索引顾名思义就是可以按照线索的指引去找到答案，比如牛津字典，在字典的前面会有目录，其实这里的目录就相当于索引，你想找一个英文mysql的注解，你第一个肯定会先去目录里面找到m开头的目录，找到m，那就省去了25/26的时间全本逐一单词寻找的时间了，是不是感觉有了索引，整个世界都变快了。

好，进入正题

作为一个免费、开源又成熟的关系型数据管理系统，mysql是很受欢迎的，因为是开源的，所以很多企业都可以在此基础上开发自己的存储引擎，而Mysql现在默认的存储引擎是Innodb，在此之前默认是MySIAM，可见Innodb的性能更胜一筹。今天主要聊聊这两种存储引擎的索引结构。

有些同学可能不太理解存储引擎是什么，给大家看下mysql的整体架构，就知道存储引擎是什么了，这里就不再深入了，想了解的可以看这个存储引擎

B+Tree出现的背景

Innodb与MySIAM两种存储引擎用的索引结构都是B+Tree类型，但两者之间具体的使用的又稍有不同，后面会讲。

我们都知道mysql的数据是存储在磁盘中的，那如果没有索引的话，我们正常读取数据是什么操作呢？比如我们有一些数据3，5，9，10，13，15，28，29存储在磁盘上，如果我们要找数据为29的数据，我们假设每个数据占用一个页，则系统就先通过磁盘io把3的数据读到内存，然后判断29大于3，然后又触发磁盘io接着读取下一页，直到找到数据29，这样就触发了多次磁盘io，磁盘io需要的时间是很致命，你想象一下，如果数据量是100万，那就是100万次磁盘io，那岂不是灾难级别的操作？

那对此我们有什么解决方案呢？

我们都知道二叉查找树吧，查找数据的时间复杂度是log(2,N)，2是底数，但随着数据的增多，二叉树的高度也会变的越来越高，因为我们知道索引都是存在磁盘的，虽然二叉查找树能减少查询时间，但随着树的高度变高，那系统的磁盘io也会变多。

这时B+Tree就诞生了，B+Tree是一个多路查找树，通过增加一个节点存储多条数据来实现降低树的高度（一般一个节点存储的数据量是一页4k），这样磁盘io操作次数也会降低很多，一般百万级别的数据，树的高度也就3，也就是只需要3次磁盘io，是不是很惊人。

B+Tree的结构

那B+Tree数据结构是怎样的呢？直接上图。

浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
原文链接

这时查找29这个数据项他的查询路径是这样的。先从磁盘中读取根节点，然后判断得29在17与35之间，所以得到指针p2，然后将指针p2指向的节点从磁盘中读取到内存，判断得29在26与30之间，所以得到p2指针，将p2指针指向的叶子节点从磁盘读取到内存，然后在内存中根据二分查找叶子节点中的数据得到29，所以整个过程下来只需要3次磁盘io就能找到数据。

B+树性质

1. 通过上面的分析，我们知道数据越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2. 当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
   原文链接

MySQL 索引实现

既然索引可以提升我们查找数据的速度，那他是怎么实现的呢？也就是B Tree是怎么实现的呢？因为索引是引擎级别的概念，所以不同引擎他对B Tree的实现是不同的，这也是针对特定引擎使用场景选择的。

MySIAM存储引擎：

MySIAM存储引擎采用的是B Tree数据结构实现索引，他的节点是不存储实际数据的，只是数据的指向，他的叶子节点存放的也是指向具体数据真实存放的地址，这样查找某个数据时，先是通过在树中找到这个数据所在的叶子节点，然后通过叶子节点的指向地址找到真实数据。

是主键索引，假设他只有三个字段col1，col2，col3，而且col1是主键，则从上图中可以看出，索引结构上的节点只存储数据记录的地址，主键索引要求数据是唯一性的，但辅助索引数据是可以重复的，我们假设col2是辅助索引，下面是辅助索引的树状数据结构，其实跟主键是一样的。

MySIAM这种索引结构也叫非聚簇索引，为了跟INNODB的（聚簇）索引结构区分开。

INNODB索引结构：

其实更多人喜欢叫INNODB的索引结构为B+Tree，但与MySIAM是一样的，都是B Tree类型，那INNODB与MySIAM的索引结构有哪些不同呢？

最大的区别是INNODB的叶子节点存储的是真实的数据，并不是数据的地址，这也是为什么叫聚簇索引，因为数据都直接存在了叶子节点，不用根据叶子节点的地址再去磁盘寻找数据。

下面是INNODB的索引结构图：

INNODB索引要求要按主键聚集，所以INNODB存储引擎都是要求有一个主键，而MySIAM不需要，如果没有显示指定，INNODB会隐式创建一个大小为6字节长度的主键id作为主键索引存储，从图中可以看出按主键查找数据非常快。

我们再来看看INNODB的辅助索引结构是怎么样的

从辅助索引可以看出，辅助索引的叶子节点存储的是这个辅助字段所在的行的主键，要通过辅助索引获取完整数据得先获取主键，然后根据主键获取完整数据，所以需要遍历两次索引数据。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

如何建立合适的索引

我想这是大多数最想了解的，这也是我们今天这篇文章的重点内容，大家在了解了索引的底层实现原理之后，就不难理解为啥要这么建立索引，这样使用索引了。

1.最左原则
可以说这是最重要的，也是最应该注意的原则，因为你可以建立一个组合索引，里面包括多个字段，那这些字段的排序有什么要求，这就是一个学问了，字段不是随意摆放的，比如有个组合索引（A，B，C）

• 那么查询的时候，如果查询【A】【A，B】 【A，B，C】，那么可以通过索引查询
• 如果查询的时候，采用【A，C】，那么C这个虽然是索引，但是由于中间缺失了B，因此C这个索引是用不到的，只能用到A索引
• 如果查询的时候，采用【B】 【B，C】 【C】，由于没有用到第一列索引，不是最左前缀，那么后面的索引也是用不到了
  如果查询的时候，采用范围查询，并且是最左前缀，也就是第一列索引，那么可以用到索引，但是范围后面的列无法用到索引

2.主键字段的选择
我们都知道了INNODB索引都是要求有主键的，但该如何选择什么字段做主键索引合适呢？

从上面的索引结构中我们了解到数据是存放在叶子节点上的，而且是按照主键从小到大排序的，我们一个叶子节点存储的是一页的数据，也就是说一个叶子节点上会存放很多的主键，而且都是排好序的，如果你的主键字段是自增的，那每次插入新数据的时候，都会顺序添加到当前索引节点的后续位置，如果叶子节点满了，会开辟一个新的叶子节点存储数据。假如你的主键不是自增的，而是随机的，比如学号或者身份证号，那你每次要新增一个数据时，先查找这个id他应该要插入的位置，找到后插入到这个位置，那原本这个位置已经有其他数据了，所以你不得不移动后面的数据，这样就增加了移动数据的开销。

所以请尽量选择自增字段作为主键。

建立索引的常用技巧

• 最左匹配原则，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的顺序可以任意调整。
• =和in可以乱序，比如a=1 and b=2 and c=3，建立(a,b,c)可以乱序，mysql优化器可以帮您优化成索引可以识别的形式
• 尽量选择区分度高的字段作为索引，区分度公式是count(distinct a) / count(*)，表示字段不重复的比例，比例越接近1，说明越适合建立索引，因为他过滤的值会越多，那这个值多少才合适呢？看具体使用场景，比如join操作我们要求on后面的字段的值是0.1以上，也就是平均1条只需要扫描10条。
• 索引列不能参与计算，比如 where a + 1 = 10，这样的查询是用不到索引a的，因为我们知道索引节点上保持的是索引的值，如果进行计算，那局势每个节点都要进行计算去对比，显然这样查找成本会变高。但如果使用where a = 10-1，这样就能使用到索引，所以要保持索引列的独立性。

尽量扩展索引，不要新建索引，比如原本已经有一个索引（a），有业务需要在a,b上建立索引，只需要在原来的基础上扩展为（a,b），而不用再建一个（b）当然视场景而定，扩展主要是为了减少遍历多个索引，维护多个索引。

explain语句

不知道大家对这个语句熟不熟悉，这是非常有用的语句，可以分析你业务上跑的sql是不是有用到索引，有哪些优化空间。
执行explain语句后会返回很多字段，每个字段所代表的意义这里解释一下。

mysql> explain select * from servers;±—±------------±--------±-----±--------------±-----±--------±-----±-----±------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±--------±-----±--------------±-----±--------±-----±-----±------+| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |±—±------------±--------±-----±--------------±-----±--------±-----±-----±------+1 row in set (0.03 sec)

• id：表示sql顺序执行的标识，sql从大到小执行
• select_type：表示查询中每个select子句的类型
• table：表示这次查询的表名
• type：表示表中找到所需行的方式，又称”访问类型“，常用的类型有：

类型	描述
ALL	全表扫描
index	索引全扫描
range	索引范围扫描，常用语<,<=,>=,between等操作
ref	使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system	单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
null	MySQL不访问任何表或索引，直接返回结果性能从上到下是 差 => 好

• possible_keys：能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
• key：表示mysql在查找时实际使用的索引名称，如果没有则是null
• key_len：表示索引中使用的字节数，可通过改列计算出查询时使用的索引的长度
• rel：表示上述表的连接匹配条件，即哪些列或常量是被用在查询表索引列上的值
• rows：表示mysql根据统计表信息及索引使用情况，在查到这些记录所需要扫描的行数，理论上值越少，代表性能越好
• extra：该列表示mysql查找记录的其他解决信息

总结

首先我们需要深入理解索引的原理和实现，当理解了原理后，才能够更有助于我们建立合适的索引。然后我们建立索引的时候，不要想当然，要先想清楚业务逻辑，再建立对应的表结构和索引。需要再次强调如下几点:

1. 索引不是越多越好
2. 区分主键和索引
3. 理解索引结构原理
4. 理解查询索引规则