【MySQL】选错索引的原因分析

目录

• 一. 背景
  • 1. MySQL怎样选择索引？
  • 2. Candinality
  • 3. 采样统计
• 二. 原因
  • 1. Explain显示的rows值和表中数据的实际行数相差较大，导致索引选错。
  • 2. 为什么二级索引扫描行数少，MySQL却选择了全表扫描？
  • 2. 是否排序
  • 3. 是否存在临时表
• 三. 解决思路
  • 1. explain和预估的行数相差较大，可以通过analyze table 来重新统计索引信息
  • 2. 通过【force index】强制指定需要使用的索引，不让优化器进行判断
  • 3. 通过改写SQL语句引导MySQL使用指定索引

一. 背景

1. MySQL怎样选择索引？

1. Server层的优化器决定使用哪个索引
2. 根据语句扫描的行数、是否排序、是否存在临时表来判断选择哪个索引

2. Candinality

1. 一个索引上不同值的个数，称为"基数"，即Candinality。基数越大，索引的区分度越好
2. MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数，这个统计信息就是索引的"区分度"。
   

3. 采样统计

1. 采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
2. 而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候(M是10则表示十分之一)，会自动触发重新做一次索引统计。

---

二. 原因

1. Explain显示的rows值和表中数据的实际行数相差较大，导致索引选错。

1. Explain是根据采样方式统计，而不是统计所有数据，所以可能有误差
2. 一致性视图影响

start transaction with consistent snapshot;

一致性视图影响：使用delete/update语句未提交前会导致数据存储多个版本，会影响到扫描行数的评估

2. 为什么二级索引扫描行数少，MySQL却选择了全表扫描？

1. 因为使用二级索引还要回表查询，而全表扫描只用使用主键索引，MySQL可能认为回表性能更差

2. 是否排序

3. 是否存在临时表

---

三. 解决思路

1. explain和预估的行数相差较大，可以通过analyze table 来重新统计索引信息

2. 通过【force index】强制指定需要使用的索引，不让优化器进行判断

3. 通过改写SQL语句引导MySQL使用指定索引