优化器的逻辑
- 在前面篇章提到,
选择索引是优化器的工作
,而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。 - 在数据库里面,
扫描行数
是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。 - 当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用
临时表、是否排序
等因素进行综合判断。
扫描行数是怎么判断的?
- MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据
统计信息
来估算记录数。 - 这个统计信息就是
索引的“区分度”
。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)
。也就是说,这个基数越大,索引的区分度越好。 - 我们可以使用
show index
方法,看到一个索引的基数。如图所示,就是表 t 的 show index 的结果,虽然这个表的每一行的三个字段值都是一样的,但是在统计信息中,这三个索引的基数值并不同,而且其实都不准确。
MySQL 是怎样得到索引的基数的呢?
- 这里简单介绍一下 MySQL 采样统计的方法。为什么要采样统计呢?因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。
- 采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
- 而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
- 在 MySQL 中,有两种
存储索引统计
的方式,可以通过设置参数innodb_stats_persistent
的值来选择:- 设置为
on
的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。 - 设置为
off
的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。
- 设置为
优化器逻辑总结
优化器不仅会估算扫描行数,还会考虑普通索引还要进行回表操作
,主键索引不需要回表,有些时候尽管主键索引的估算扫描行数多于普通索引,但是普通索引需要回表,所以优化器综合考虑用的还是主键索引。- 在实践中,如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大,可以使用
analyze table t
命令重新统计索引信息
索引选择异常和处理
- 第一种方法,可以采用
force index
强行选择一个索引。MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后在候选列表中依次判断每个索引需要扫描多少行,如果 force index 指定的索引在候选索引列表中,就直接选择这个索引,不再评估其他索引的执行代价。 - 第二种方法,我们可以考虑
修改语句,引导 MySQL 使用我们期望的索引
。例如以下语句,把“order by b limit 1” 改成 “order by b,a limit 1”语义是相同的,但是后者才是我们想要的结果。
- 第三种方法,在有些场景下,我们可以
新建一个更合适的索引
,来提供给优化器做选择,或删掉误用的索引。
问题总结
- mysql 如何判断一个查询的扫描行数?
- 索引基数如何计算?通过哪个参数可以设置索引统计的存储方式?
- 可以重新统计索引信息的命令是什么?
- 如何定位索引选择异常这样的问题?
- 索引选择异常的问题可以有哪几种处理方式?
笔记来源于《极客时间:MySQL实战45讲:MySQL为什么有时候会选错索引?》