MySQL索引核心知识点

目录

• 一、索引的分类
• • 聚簇索引（主键索引）
  • 非聚簇索引（二级索引、辅助索引）
  • 联合索引
• 二、索引的细节知识
• • B+树的细节
  • 为什么索引使用B+树，而不是Hash、二叉树、红黑树、B-Tree？
• 三、索引与优化
• • 索引覆盖
  • 索引下推
  • 适合使用索引的情况
  • 不适合使用索引的情况
  • 索引失效的情况
  • 关联查询优化
  • 大表优化

一、索引的分类

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

聚簇索引（主键索引）

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

• 二级索引只放查找值和主键，因为这样让一页可以放更多行，使得B+树更扁、更矮。
• 覆盖索引可以让二级索引避免回表，提升效率。

联合索引

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

二、索引的细节知识 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 ( l o g n ) O(logn) O(logn) ；（3）B+树的叶节点是双向链表，B-树不是，所以B+树范围查询更快。

三、索引与优化 索引覆盖

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

索引下推

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

下图为索引下推的举例

适合使用索引的情况

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 2 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. 水平拆分