基于InnoDB存储引擎的MySQL索引介绍

1 背景

1.1 什么是索引？

索引（Index）是帮助MySQL高效获取数据的数据结构。如果把数据库比作一本书，那么索引就是这本书的目录，可以帮助我们快速查找这本书的内容，提高数据查询的效率。索引对于数据库查询性能非常关键，索引优化应该是对数据库查询性能优化最有效的手段了，能够轻易将查询性能提高好几个数量级。

1.2 什么是InnoDB存储引擎？

MySQL逻辑结构可以看成是二层架构：

• 语句分析层：主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能；
• 存储引擎层：主要负责接收来自语句分析层的分析结果，完成相应的数据输入输出和文件操作，即如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法；

在MySQL5.5版本之后，默认的存储引擎为InnoDB：

• 支持事务（遵从ACID），具有提交（commit）和回滚（rollback）事务的能力；
• 支持表级锁和行级锁，支持MVCC（Multi-Versioning Concurrency Control，多版本并发控制）；
• 支持数据库异常崩溃后的安全恢复；

1.3 为什么InnoDB选择B+树作为索引模型？

B+树能减少查询时磁盘IO的次数，从而加快查询速度。

• 二叉树通过二分查找降低了时间复杂度
• 平衡二叉树每个节点的左右子树的高度差不能超过1，查询效率更稳定，总体的查询速度更快；
• B树适配磁盘IO&预读的特性，每个节点存储了更多的关键字和数据，可以降低树的高度，从而减少磁盘IO的次数；
• B+树的非叶子节点只存储键值不存储数据，这样就能存储更多的键值，那么树的高度更低，从而进一步减少磁盘IO的次数；

磁盘IO&预读

典型的磁盘IO有两个步骤：

• 磁头移动到数据轨道，等待数据扇区在其下旋转，对应平均访问时间；
• 开始数据读取和传输，对应数据传输时间；

在一个完整的IO周期中，只有数据传输时间才能真正利用磁盘数据通道。如果一次读取的数据越多，那么数据传输时间占比越大，平均访问时间占比越小，从而磁盘IO带宽就会越高。

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到，每一次IO读取的数据我们称之为一页（page）。

有顺序访问模式的地方，就有IO预读的市场，无论是基于机械磁盘还是固态磁盘（SSD）。

B+树

B树是平衡多叉树，B+树是B树的一种变形：非叶子节点不存储具体的数据而是存储数据的索引，叶子节点存储关键字以及相应记录的地址。`

• 非叶子节点最多有m个子节点，关键字个数与子节点个数相同，关键字是子节点的最大或最小值，每一个关键字对应一个指针；`
• 所有叶子节点都在同一层，存放了全部关键字信息及指向含这些关键字记录的指针， 且叶子节点本身依关键字从小而大顺序链接；

2 索引分类

2.1 物理分类

根据物理实现方式，索引可分为聚集索引和辅助索引两种。

聚集索引（clustered index） ，即主键索引。

一张表只能有一个聚集索引，B+树的键值就是主键，其叶子节点存储了表中所有的数据，数据文件本身即为聚集索引。聚集索引的逻辑顺序与磁盘上的物理存储顺序相同。

1. 如果定义了主键（PRIMARY KEY），InnoDB将主键索引作为聚集索引；
2. 如果没有定义主键，InnoDB将第一个非空唯一索引（UNIQUE KEY）作为聚集索引；
3. 如果没有定义主键也没有唯一索引，InnoDB将生成一个6字节长整型的隐式字段ROWID作为聚集索引，该字段的值在数据插入时自增；

辅助索引（secondary index） ，即非聚集索引，除聚集索引之外的所有索引都称为辅助索引。

一张表可以有多个辅助索引，B+树的叶子节点不存储行记录的全部数据，只存储该行的主键。因此使用辅助索引查询行记录时，先查找到主键再去聚集索引中查找行数据，这称为“回表”。辅助索引的逻辑顺序与磁盘上的物理存储顺序不同。

2.2 功能分类

根据功能逻辑，主要有以下三类：

1. 普通索引（KEY）：最基本的索引，无任何限制。包含单个列的称为单列索引；包含多个列的称为联合索引，使用时遵循最左前缀原则（leftmost prefix）；
2. 唯一索引（UNIQUE KEY）：在普通索引的基础上增加了数据唯一性约束，即索引列中的值必须是唯一的；
3. 主键索引（PRIMARY KEY）：在唯一索引的基础上增加了非空约束；

普通索引、唯一索引和主键索引，对数据的约束性逐渐提升。

3 索引优化

3.1 非必要不索引

凡事都有两面性，索引也不是建的越多越好，那么索引有哪些不足呢？

• 占用磁盘空间：辅助索引会新增索引文件，通过空间换时间提升查询效率；
• 降低更新效率：对表进行增删改操作时，不仅要更新数据，还要更新索引；

所以我们应该只在必要的时候才建立索引，同时尽可能地使用更少的索引覆盖更多的查询业务场景。

3.2 explain命令

explain命令（https://dev.mysql.com/doc/refman/8.0/en/explain-output.html），可用来查看SQL的执行计划。虽然它只是作为语句执行过程的一个参考，实际执行的过程不一定和计划完全一致，但是执行计划中透露出的讯息却可以帮助我们选择更好的索引，写出更优化的查询语句。

字段	描述
字段	描述
id	id为SELECT的标识符。它是在SELECT查询中的顺序编号。
select_type	查询类型- SIMPLE：简单查询
table	表名
partitions	分区表命中的分区情况，非分区表该字段为空（NULL）
type	连接类型，性能从好到坏依次是：system > const > eq_ref > ref > range > index > ALL
possible_keys	查询时可能使用的索引
key	查询时实际使用的索引
key_len	使用的索引的长度
ref	表示哪个字段或者常量被用来与索引字段比对，以读取表中的记录- 当使用常量等值查询，显示const
rows	扫描行的数量
filtered	表示符合查询条件的记录数所占的百分比
Extra	执行情况的说明和描述

3.3 优化原则

最左前缀匹配原则：对于联合索引，B+树是按照从左到右的顺序建立搜索树的，因此对索引中的关键字进行对比的时，一定是从左往右依次对比，且不可跳过。

• 跳过之后的列，索引会失效；范围查询（<, >, between等）之后的列，索引会失效；
• 建立联合索引时，区分度高、经常使用的字段放在左边；
• 以%开头的like模糊查询，索引会失效；

最少空间原则：索引的长度越小，B+树每个节点保存的关键字个数就越多，其高度就越低，查询效率就会越高，同时占用的磁盘空间也越少。因此创建索引的关键字要尽可能占用空间小。

避免回表：使用辅助索引查询行记录时，先查找到主键再去聚集索引中查找行数据，这称为“回表”。

• 尽量使用主键去进行查询；
• 覆盖索引：索引列中包含了要查询的所有字段以及查询条件字段，避免回表；
• 尽量选择区分度高的列作为索引，减少回表次数；
• 索引下推（Index Condition Pushdown ICP），如果where 条件可以通过索引中的字段来筛选，则通过索引直接过滤掉不满足条件的记录，减少回表次数；

保持列干净：

• 索引列参与计算：对索引字段进行表达式计算、函数操作等都会导致索引失效；
• 字符串不加‘’：索引字段为字符串类型，如果查询时不加单引号，会自动的进行类型转换，造成索引失效；

几个tips：

• 更新频繁的列不宜建立索引；
• 如果明确知道只有一条结果返回，limit 1 能够提高效率；
• 业务上具有唯一特性的字段，即使是多个字段的组合，也应该建成唯一索引；