InnoDB索引

什么是索引：

索引是存储引擎用于快速找到记录的一种数据接口，这是索引的基本功能，除此之外，在InnoDB，索引是聚集数据的一种方式（聚集索引），InnoDB的加锁机制也是通过索引实现的，相关的内容可以看我的另一篇博客《InnoDB 存储引擎》。

InnoDB索引的结构：

如图所示，在InnoDB中，索引通过B+Tree进行组织，所有的数据存在与叶子节点，叶子之间首尾相连。为什么选择B+Tree作为索引的存储结构，而不选择二叉平衡树或者B-Tree，首先我们要明确一点，数据是存在磁盘上而不是内存中，磁盘IO是昂贵的，因此内存和磁盘进行数据传输时不是以位、字节、数据行或者随机大小的字节为单位，而是以页为单位进行数据交换。这样做一方面是为了减少IO的次数，一方面是数据访问的局部性原理（预计相邻的数据将来会被读取，提前加载到内存，最终还是为了介绍IO次数）。因此不论是磁盘还是内存中，InnoDB组织和管理数据的基本单位都是页。页上可以包含一条或者多条数据，视数据行的大小而定。因此，像二叉平衡树这种结构是无法满足这种设计的。B+Tree是B-Tree的变形，B+Tree区别于B-Tree主要有两点：

1. B-Tree中所有节点都存储数据，而在B+Tree树中只有叶子节点存储数据，非叶子节点只存储索引关键字。
2. B+Tree叶子节点首尾相连。B-Tree则不是。

这两点区别会带来什么样的差别呢？个人理解，采取只在叶子节点存数据会比非叶子节点存储数据更加稳定，通常来说行记录要比索引记录大，非叶子节点只存储索引记录，因此能容纳更多的索引键，因此降低了页分裂的次数。在数据管理方面，链表的形式也会比树状结构更容易一些。

B+Tree索引适用的查询类型：

1. 全值匹配：全值匹配指的是和索引中的所有列进行匹配。
2. 匹配最左前缀列。
3. 匹配列前缀：可以匹配某一列的开通每部分，前提是该列前边的列是全值匹配。
4. 匹配范围值：索引值的某个范围。
5. 精确匹配某一列并范围匹配另外一列，前提是范围匹配之前的列为全匹配。
6. 只访问索引的查询：只需要访问索引，无需访问数据行。

B+Tree索引的限制：

1. 如果不是按索引的最左列查找，则无法使用索引。
2. 不能跳过索引中的列。
3. 如果查询中有某个列的范围查询，则其右边列都无法使用索引优化查找。

索引的优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 所以可以帮助服务器避免排序和临时表（索引天然自带排序功能）。
3. 索引可以将随机IO变为顺序IO。

三星系统：

• 一星：索引将相关的记录放在一起。
• 二星：索引中的数据顺序和查找的排列顺序一致。
• 三星：索引中的列包含了差村中需要的全部。

索引策略：

1. 使用独立的列：独立的列是指索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
2. 使用选择性高的列作为索引：选择性高的列可以缩小索引查找的范围。
3. 主键索引列尽量小：InnoDB中，辅助索引叶子节点中保存的是主键而不是索引数据的指针，主键过大会影响存储的效率。
4. 使用覆盖索引技术：如果一个索引包含所需要查询的字段的值，我们称之为覆盖索引。覆盖索引直接从索引中返回值，减少了回表的过程。
5. 索引能少则少：在满足业务的情况下，使用尽量少的索引，对于更新操作，过多的索引会导致索引维护成本大大提高。

索引规约(阿里规范)
1. 【强制】业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明
显的 ; 另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必
然有脏数据产生。
2. 【强制】超过三个表禁止 join 。需要 join 的字段,数据类型必须绝对一致 ; 多表关联查询时,
保证被关联的字段需要有索引。
说明:即使双表 join 也要注意表索引、 SQL 性能。
3. 【强制】在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据
实际文本区分度决定索引长度即可。
说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分
度会高达 90%以上,可以使用 count(distinct left( 列名, 索引长度 )) / count( * ) 的区分度
来确定。
4. 【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
说明:索引文件具有 B - Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索
引。
5. 【推荐】如果有 order by 的场景,请注意利用索引的有序性。 order by 最后的字段是组合
索引的一部分,并且放在索引组合顺序的最后,避免出现 file _ sort 的情况,影响查询性能。
正例: where a =? and b =? order by c; 索引: a _ b _ c
反例:索引中有范围查找,那么索引有序性无法利用,如: WHERE a >10 ORDER BY b; 索引
a _ b 无法排序。
6. 【推荐】利用覆盖索引来进行查询操作,避免回表。
说明:如果一本书需要知道第 11 章是什么标题,会翻开第 11 章对应的那一页吗?目录浏览
一下就好,这个目录就是起到覆盖索引的作用。
正例:能够建立索引的种类:主键索引、唯一索引、普通索引,而覆盖索引是一种查询的一种
效果,用 explain 的结果, extra 列会出现: using index 。
7. 【推荐】利用延迟关联或者子查询优化超多分页场景。
说明: MySQL 并不是跳过 offset 行,而是取 offset + N 行,然后返回放弃前 offset 行,返回
N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过
特定阈值的页数进行 SQL 改写。
正例:先快速定位需要获取的 id 段,然后再关联:
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
8. 【推荐】 SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts
最好。
说明:
1 ) consts 单表中最多只有一个匹配行 ( 主键或者唯一索引 ) ,在优化阶段即可读取到数据。
2 )ref 指的是使用普通的索引 (normal index) 。
3 )range 对索引进行范围检索。
反例: explain 表的结果, type = index ,索引物理文件全扫描,速度非常慢,这个 index 级
别比较 range 还低,与全表扫描是小巫见大巫。
9. 【推荐】建组合索引的时候,区分度最高的在最左边。
正例:如果 where a =? and b =? , a 列的几乎接近于唯一值,那么只需要单建 idx _ a 索引即
可。
说明:存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如: where a >?
and b =? 那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。
10. 【推荐】防止因字段类型不同造成的隐式转换,导致索引失效。
11. 【参考】创建索引时避免有如下极端误解:
1 ) 宁滥勿缺。认为一个查询就需要建一个索引。
2 ) 宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
3 ) 抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。