这是我参与11月更文挑战的第27天,活动详情查看:2021最后一次更文挑战
在执行SQL语句时,MySql会通过连接器、分析器、优化器、执行器等一系列步骤来执行SQL语句。在选择索引,就是优化器的工作。
优化器选择索引的目的,是找到一个最佳的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘的数据就越少,消耗的CPU资源就越少。
优化器选择索引的因素有很多,会根据这些因素进行综合判断。例如
- 扫描行数;
- 是否会使用临时表
- 是否会排序
扫描行数是如何判读的呢
MySQL在执行语句前,需要通过统计信息来估算记录数。这个统计信息指的就是索引的“区分度”。一个索引上不同的值越多,索引的区分度就越好。即这个基数(cardinality)越大,索引的区分度越好。
通过show index
语句查看索引时即可看到索引的基数。
索引的基数又是如何统计的呢
MySQL使用采样统计的方法来统计这个基数。采样统计即是指,随机选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
从这里可以发现,采样统计具有不精确性的(就是随机取样)。而选择全部数据页取平均值代价太高了,所以选择了采样统计。
当数据变更时,索引统计信息也是会变的。当变更的数据行数超过1/M时,会自动触发重新做一次索引统计。 (假设M为10时,表示变更行数超过10%时就会自动触发索引统计)
在MySQL中有两种存储索引统计的方式,可以通过设置参数 innob_stats_persistent
的值来选择:
- 设为on时,表示统计信息会持久化存储。N为20,M为10。
- 设为off时,表示统计信息只存储在内存中。N为8,M为16。
除了根据索引的基数,MySQL还会检查查询语句的扫描行数。有时候会出现MySQL不走索引走全表查询的情况,是因为MySQL认为使用普通索引时会进行回表,而直接从主键索引上扫描,即全表查询的话代价反而可能比较低。(注意这里并不一定走全表查询实际的效率更好,疑似是MySQL优化器的bug)
使用 analyze table t
命令可以重新统计索引信息。
总结
MySQL优化器存在索引误判的情况。有以下几种解决方式
- 由于索引统计信息不准确导致的问题,可以用
analyze table
重新统计索引信息来解决。 - 针对其他优化器误判的问题,可以在应用端用
force index
来强行指定索引,也可以通过修改语句来引导优化器。或者通过增加/删除索引来绕过这个问题。
参考链接
谈谈MySQL的基数统计:www.cnblogs.com/ZhuChangwu/…
《极客时间之MySQL45讲》