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

2021/9/29 19:13:47

本文主要是介绍【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使用指定索引



这篇关于【MySQL】选错索引的原因分析的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程