MySQL索引核心知识点

一、索引的分类

InnoDB 中的索引

• 按功能： 普通索引、唯一索引、主键索引、全文索引、空间索引等
• 按物理实现方式： 聚簇索引、非聚簇索引
• 按照作用字段个数： 单列索引、联合索引
• 8.0新特性索引： （1）隐藏索引，从 MySQL 8.x 开始支持，要删索引之前先把它设为隐藏索引，优化器将忽略该索引，如果对系统没影响再真的删除；（2）降序索引，即可以按降序排列的索引

聚簇索引（主键索引）

非聚簇索引（二级索引、辅助索引）

• 二级索引只放查找值和主键，因为这样让一页可以放更多行，使得B+树更扁、更矮。
• 事实上，二级索引的非叶节点中也放了主键，相当于是索引字段和主键的联合索引，因为索引字段可能会重复（即使是 UNIQUE 的字段也可能重复，比如多个 NULL 或 MVCC 产生的重复）。
• 覆盖索引可以让二级索引避免回表，提升效率。

联合索引

将几个字段放一起构建的二级索引即为联合索引，这些字段从左往右依次比较，所以有最左前缀原则。

MyISAM 中的索引

• MyISAM 中的索引与 InnoDB 中的索引的主要区别在于，MyISAM 中的索引的叶节点存储的是主键（或索引字段）+ 地址，要先找到地址，再通过地址去取数据，所以 MyISAM 中的索引必需要“回表”一次，相当于都是二级索引。
• 在 MyISAM 中，当用变长格式存数据时索引中存的是地址偏移量，当用定长格式存数据时索引中存的是行号。
• 虽然 MyISAM 中的索引必须“回表”，但是实际上也是很快的，因为是直接根据地址去取，而不是像 InnoDB 中那样再走一遍索引。

二、索引的细节知识

B+树的细节

• B+ 树一般不超过4层，因为三层1亿条数据，四层1000亿条数据；同时根节点常驻内存，因此磁盘 I/O 一般不超过 3 次。
• 主键一般为递增的 ID，因为 B+ 树顺序插入快，向中间插入的话会导致页的分裂，会很慢。
• 一般把主键定为不可更新，否则 B+ 树的重建代价很高。
• B+ 树页面分裂时，新建一个页，把该页数据分一半放入新页，然后把中间值存入父节点；若父节点也满了，则父节点按相同步骤分裂（所以根节点位置不会动，也所以 B+ 树每个页面至少有 2 个数据）。

为什么索引使用B+树，而不是Hash、二叉树、红黑树、B-Tree？

• Hash 索引： 只适合等值查询，不适合范围查询（因为无序，范围查询还是一个一个找）；但是 InnoDB 和 MyISAM 虽然没有Hash索引，却有自适应Hash索引，把多次查询的数据放入Hash索引中，提高检索效率。
• 二叉树： 太高了，磁盘 I/O 太多（甚至可能退化成链表，直接变成 $O(n)$ ）。
• 红黑树： 是一种特化的平衡二叉树，还是太高了，磁盘 I/O 太多；MySQL 数据量很大的时候，索引的体积也会很大。
• B-Tree： B-Tree的叶节点和非叶节点都保存数据，这导致（1）B-树比B+树更高，磁盘 I/O 更多；（2）B-树的查询所需的 I/O 次数不稳定，在叶节点就多，在非叶节点就少，而B+树则是稳定的 $O(logn)$ ；（3）B+树的叶节点是双向链表，B-树不是，所以B+树范围查询更快。

三、索引与优化

索引覆盖

二级索引（非聚簇索引）中的索引字段 + 主键覆盖了SELECT、JOIN和WHERE中要用到的所有字段，就不用回表了，高效率。

索引下推

MySQL 5.6 的新特性，当使用联合索引（字段A和字段B）时，找到符合 A 条件的数据先不急着回表，而是用字段 B 的条件先把找到的数据过滤一遍，这样可以有效减少要回表的数据量。

下图为索引下推的举例

索引合并

索引合并就是一次查询中同时使用两个索引来执行。

索引合并的方法分为以下三种：

• 交集（Intersection）： 同时在两个索引中进行搜索，将两个索引搜索到的记录的主键进行比较。若相同则进行回表，然后两个索引继续搜索；若不相同则丢弃主键较小的那个（因为二级索引中索引字段相同的数据是按照主键值排序的），并让该索引继续搜索下一个，另一个索引则等待。不断循环上述过程，直到两个索引都完成遍历，进行回表并返回给客户端的那些记录即是要找的全部数据。例如查询SELECT * FROM single_table WHERE key1 = 'a' AND key2 = 'b'。
• 并集（Union）： 同时在两个索引中进行搜索，将两个索引搜索到的记录的主键进行比较。若相同则进行回表（只回表了一次，相当于去重），然后两个索引继续搜索；若不相同则主键较小的那个进行回表（因为二级索引中索引字段相同的数据是按照主键值排序的），并让该索引继续搜索下一个，另一个索引则等待。不断循环上述过程，直到两个索引都完成遍历，进行回表并返回给客户端的那些记录即是要找的全部数据。例如查询SELECT * FROM single_table WHERE key1 = 'a' OR key2 = 'b'。
• 排序-并集（Sort-Union）： 使用上面两个索引合并方法的前提是“二级索引中索引字段相同的数据是按照主键值排序的”，这有时是个过于苛刻的条件，在很多情况下都无法满足。因此可以先让两个索引各自进行遍历搜索，并各自将结果进行排序，然后再用 Union 方法将结果进行合并。例如查询SELECT * FROM single_table WHERE key1 < 'a' OR key2 > 'z'。

适合使用索引的情况

1-5是根据字段用途，6-8是根据字段特点，9-11是其他注意事项

1. 经常作为WHERE查询条件的字段。
2. 经常GROUP BY和ORDER BY的字段（索引已经排序了，进行GROUP BY和ORDER BY就很简单了）。
3. DISTINCT的字段。
4. 用作UPDATE和DELETE的WHERE条件的字段。
5. 多表JOIN连接基于的字段。
   –
6. 有唯一性限制的字段。
7. 区分度高（散列性高）的字段。
8. 类型小的字段。
   –
9. 使用字符串字段的前缀创建索引（但是用了前缀索引就不能索引覆盖了，而且也不能用于排序了）。
10. 联合索引中使用最频繁的字段放左侧（最左前缀原则）。
11. 在多个字段都要创建索引的情况下，联合索引优于单值索引。

不适合使用索引的情况

1-3是根据字段特点，4-5是根据表的特点，6-7是其他注意事项

1. 在where中使用不到的字段
2. 有大量重复数据的字段
3. 无序的字段
   –
4. 数据量小的表最好不要用索引
5. 避免对经常更新的表创建过多的索引
   –
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引

索引失效的情况

1. 使用!=或<>导致索引失效
2. 类型不一致导致索引失效
3. 使用函数导致索引失效
4. 自动类型转化导致索引失效（将 WHERE name = '123'; 写成了 WHERE name = 123;）
5. 查询的范围条件放前面导致索引失效（如 WHERE student.age = 30 AND student.class_id > 20 AND student.name = 'abc'; ，范围条件要放最后）
6. LIKE以通配符%开头导致索引失效
7. OR的条件中存在没有索引的字段（如 WHERE age = 10 OR class_id = 100; 且class_id没有索引）

关联查询优化

1. 外连接中的被驱动表要有索引
2. 用于JOIN的字段，数据类型要保持绝对一致，否则会导致索引失效情况 $2$。
3. 外连接JOIN时，选择小表为驱动表，大表为被驱动表，以减少外层循环的次数。
4. 内连接INNER JOIN时，MySQL 会先为每个表计算成本最低的访问方法，再计算各种连接顺序的成本，选择成本最小的方式进行连接。
5. 能直接多表关联的尽量直接关联，尽量不用子查询，减少查询的趟数（子查询生成的临时表建不了索引）。
6. 尽量不要使用NOT IN或者NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代。

大表优化

1. 读/写分离，一主一从、一主多从或双主双从等，主写从读
2. 垂直拆分（分布式系统）
   
3. 水平拆分