MySQL-索引篇

什么是索引？

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构

索引的优缺点

优点：

• 使用索引可以大大加快数据的检索速度，减少IO次数
• 通过创建唯一性索引，可以保证数据库表的每一行数据的的唯一性

缺点：

• 创建索引和维护索引需要耗费许多时间
• 索引需要物理文件存储，也会耗费一定空间

问：索引一定能提高查询性能吗？

索引底层数据结构选型

Hash表

问：为什么MySQL不使用Hash表作为索引的结构？
Hash索引不支持顺序和范围查询，并且每次IO只能取一个

二叉查找树

二叉查找树的性能非常依赖于其平衡程度，也不适合作为MySQL的索引底层数据结构

AVL树

红黑树

B树&B+树

索引类型总结

按不同的角度有不同的划分

主键索引

1. 数据库表主键列使用的索引就是主键索引
2. 一个表只能有一个主键，主键不能为null，不能重复
3. InnoDB中，如果没有指定主键，会自动检查是否有唯一索引并且不允许存在null值的字段，如果有设为主键，否则，自动创建一个6byte的自增主键

二级索引

1. 二级索引的叶子节点所存储数据是主键的值
2. 唯一索引，普通索引，前缀索引，全文索引都属于二级索引

聚集索引与非聚集索引

聚集索引

索引结构和数据存放到一起的索引，InnoDB中的主键索引就是聚集索引，B+树每个非叶子结点存储索引，叶子节点存储索引和索引对应的数据
优点：

1. 查询速度非常快
2. 对排序查找和范围查找优化

缺点：

1. 依赖于有序的数据
2. 更新代价大

非聚集索引

非聚集索引就是索引结构和数据分开存放的索引，二级索引就是非聚集索引
优点：

1. 更新代价较小，因为叶子节点不存放数据

缺点：

1. 依赖于有序的数据
2. 可能会二次查询（回表）

问：什么是回表？
在使用非主键索引查询时，会先找到主键，再根据主键索引查询完整的数据，这个过程被称为回表

覆盖索引与关联索引

覆盖索引

一个索引包含所有需要查询的字段的值，需要查询的字段刚好是索引的字段，直接根据该索引，就可以拿到所需的数据了，不需要回表查询

联合查询

使用表中多个字段建立索引，就是联合索引

最左前缀匹配原则

在使用联合索引时，MySQL根据索引的字段顺序，从左到右依次匹配查询条件中的字段，如果查询条件与索引中的最左侧字段相匹配，就会根据索引来查

#联合索引 idx(name,class)
# 可以命中索引
SELECT * FROM student WHERE name = 'Anne Henry';
EXPLAIN SELECT * FROM student WHERE name = 'Anne Henry' AND class = 'lIrm08RYVk';
# 无法命中索引
SELECT * FROM student WHERE class = 'lIrm08RYVk';

索引下推

是一项索引优化功能，允许存储引擎在索引遍历过程中，执行部分where字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率

如何正确使用索引

选择合适的字段创建索引

1. 不为Null的字段
2. 被频繁查询的字段
3. 被作为条件查询的字段
4. 频繁需要排序的字段
5. 经常需要连接的字段

被频繁更新的字段应该慎重创建索引

限制每张表上的索引数量

建议单张表不超过5个，MySQL优化器在选择如何优化查询时，会对每一个可以用到的索引进行评估，以生成一个最好的执行计划，如果有多个索引可以选择，会增长生成执行计划的时间，也会降低查询效率

尽可能考虑建立联合索引而不是单列索引

索引需要占用磁盘空间，如果是联合索引，多个字段在一个索引上，会节省很大的磁盘空间，且修改数据的效率也会提升

注意避免冗余索引

大多数情况下应扩展已有的索引，而不是创建新索引

字符串类型的字段使用前缀索引而不是普通索引

前缀索引仅限于字符串类型，且相对于普通索引会占用更小的空间

避免索引失效

常见索引失效场景：

1. 创建了联合索引，但查询条件未遵循最左前缀法则
2. 在索引列上计算，函数，类型转换等操作
3. 以%开头的LIKE查询比如LIKE ‘%abc’;
4. 查询条件中使用了or,or左右有一个列没有索引，涉及索引都会失效
5. in的取值范围较大时会导致索引失效，走全表扫描
6. 发生隐式转换（https://javaguide.cn/database/mysql/index-invalidation-caused-by-implicit-conversion.html#sql-%E6%B5%8B%E8%AF%95）

删除长期未使用的索引

不用的索引的存在会造成不必要的索引的损耗