MySQL是如何选择索引的？

2.3.5. 索引选择

MySQL是如何选择索引的？

优化器决定了具体某一索引的选择，也就是常说的执行计划。而优化器的选择是基于成本（cost），哪个索引的成本越低，优先使用哪个索引。

SQL 优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器称之为：CBO（Cost-based Optimizer，基于成本的优化器）。

Cost  = Server Cost + Engine Cost

      = CPU Cost + IO Cost

CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成；

IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。

优化器认为一条 SQL 需要创建基于磁盘的临时表，这时的成本是最大的，索引键值的比较、记录之间的比较，其实开销是非常低的，但如果要比较的记录数非常多，则成本会变得非常大。

MySQL索引出错案例分析

索引创建在有限状态上：

B+ 树索引通常要建立在高选择性的字段或字段组合上，如性别、订单 ID、日期等，因为这样每个字段值大多并不相同。像性别这种字段只有男女两种，是低选择性的字段，因此无须在性别字段上创建索引。

在有些低选择性的列上，是有必要创建索引的。比如电商的核心业务表。

在电商业务中会有一个这样的逻辑：会定期扫描支付状态为支付中的订单，然后强制让其关闭，从而释放库存，给其他有需求的买家进行购买。一般仅为已完成、支付中、超时已关闭这几种。绝大部分都是已完成，只有绝少部分因为系统故障原因，会在 15 分钟后还没有完成订单，因此订单状态是存在数据倾斜的。

例如支付状态只有已完成、支付中、超时已关闭三种，有一百万条数据，优化器会认为每个状态占用三分之一数据，使用全表扫描，避免二级索引回表效率会更高。

然而，由于数据倾斜，订单状态为支付中的数据非常少（例如有1万条），这时根据索引的查询效率会更高。

这时可以利用 MySQL 8.0 的直方图功能，创建一个直方图，让优化器知道数据的分布，从而更好地选择执行计划。

建立索引时要注意的事：

• 经常频繁用作查询条件的字段应酌情考虑为其创建索引。
• 表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
• 建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
• 建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
• 建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
• 经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
• 对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。
• 尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

同时，还需有些注意点：

• 值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
• 一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。
• 索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
• 建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
• 当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。