前言

最近在阅读小林coding的时候，看到了一些关于mysql索引的知识，便记录了这篇文章，仅作学习总结

什么是索引

索引是数据库中的一种数据结构，用于加速数据库表的查询操作。打个比方，当你想查阅书中某个知识的内容，你会选择一页一页的找呢？还是在书的目录去找呢？索引就类似书的目录，用于快速定位到需要查找的内容，索引是以空间换时间的思想。

索引的分类

可以按照四个角度来分类索引。

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。

• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。

• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。

• 按「字段个数」分类：单列索引、联合索引。

按数据结构分类

InnoDB 是 MySQL 5.5之后的默认存储引擎，使用 B+树作为其索引结构。

除主键索引外，创建的其他索引都是二级索引，也称辅助索引或非聚簇索引。

主键索引和二级索引默认采用的都是B+Tree索引。

为了更好的理解 B+Tree 索引的存储和查询的过程，接下来我通过一个简单例子，说明一下 B+Tree 索引在存储数据中的具体实现。

先创建一张商品表，id 为主键，如下：

这些行数据，存储在 B+Tree 索引时是长什么样子的？

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

从数据页的角度看

• 页：页是数据库存储的基本单位，默认为16KB

• 页目录：页中会有一个页目录，页目录由多个槽组成

• 槽：每个槽相当于指针，会指向每个分组的最后一条记录

• 分组：记录（data）会分为几个组，组内包括最小记录和最大记录

简单来讲就是，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，也就定位到分组，再遍历分组的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。就是根节点-->槽-->分组-->记录，然后层层下去，只有叶子节点才存放真实数据，非叶子节点存储的是主键值。

主键索引的 B+Tree 具体如图所示：

通过主键寻找记录

以找到主键为6的记录举例，从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在[1, 7)范围之间，所以到页 30 中查找更详细的目录项；

• 在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；

• 接着，在叶子节点（页16）中，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

通过二级索引查找记录

由于二级节点存放的是主键值，因此还需要再遍历一次B+Tree，这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

不过，当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再查主键索引查，比如下面这条查询语句：

select id from product where product_no = '0003';

这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

为什么不用B树做索引？

1. B+树查询效率更稳定（查找效率固定为O(logn)），因为除了叶子节点，其他节点只有索引作用，所以任何关键字的查找路径长度都是相同的

2. B+树更适合范围查找，因为叶子节点之间用链表有序链接，在查找一个范围的时候，只需要根据范围左端点查询，然后找到叶子节点，遍历链表即可进行范围查找；而B数每次找一个值都需要进行中序遍历

为什么不用Hash，二叉树，红黑树做索引？

1. Hash的话，只适合等值查询，不适合范围查询

2. 用一般二叉树的话，有一种特殊情况就是id作为索引且自增，这时就特殊化成了链表，查找时会进行全表扫描

3. 红黑树的话，MySQL 数据量很大的时候，索引的体积也会很大，这时树的高度会特别高，不利于查询

最左前缀法则

是指在使用联合索引（复合索引）进行查询时，查询条件需要遵循索引中列的顺序，从左到右进行匹配。只有当查询条件满足最左前缀原则时，才能充分利用联合索引的优势，提高查询性能

例子：比如创建了一个（a,b,c）的联合索引，如果用到abc则会生效，用a，ab也会生效，跳过a的b，c，bc都会失效

索引下推

比如你创建了一个联合索引（name，age），然后查询条件是name like '小明%' and age>18，根据最左前缀原则，就会先去找名字是小明的，找到了再去判断age，假设有四条记录，索引下推的话，则会同时判断name和age，假设有两条记录，这样就会减少两条记录的回表，从而提高查询效率

什么时候需要索引

• 字段有唯一性限制的，比如商品编码；

• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。

• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

SQL调优

1. 优化sql：在 mysql 配置文件里开启慢查询，可以设置指定的秒数，然后他会把超过这个秒数的 sql 语句放到指定文件,然后我们将这个 sql 取出来通过 explain 分析，找出问题所在

2. 添加索引：对经常被查询的列建立索引，可以大大提高查询效率

3. 避免索引失效

4. 分页查询：对于数据量较大的表，可以使用分页查询的方式来避免一次性查询所有数据，从而减少查询时间，关于深度分页查询，解决方法是子查询+覆盖查询

5. 不要使用*查询， *查询会查询出许多无用字段，会增加sql执行时间

6. 分库分表

索引在什么情况下会失效？

1. 违反最左前缀法则

2. 范围查询右边的列，索引会失效

3. 在索引列上进行运算（函数运算和算数运算）

4. 字符串不加单引号，造成索引失效（涉及到类型转换）

5. LIKE “%_”百分号在前

执行一条 select 语句，期间发生了什么？

• 连接器：建立连接，管理连接、校验用户身份；

• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；

• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

• 执行 SQL：执行 SQL 共有三个阶段：

  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。

  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；

  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端