【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使用指定索引

posted @ 2021-09-29 12:38    阅读(45)  评论(0编辑  收藏  举报