前言
之前提到使用以下 sql 会导致慢查询
SELECT COUNT(*) FROM SomeTable
SELECT COUNT(1) FROM SomeTable
原因是会造成全表扫描,有位读者说是有问题的,实际上针对无 where_clause 的 COUNT(*),MySQL 是有优化的,优化器会选择成本最小的辅助索引查询计数,其实反而性能最高,这位读者的说法对不对呢
针对这个疑问,我首先去生产上找了一个千万级别的表使用 EXPLAIN 来查询了一下执行计划
EXPLAIN SELECT COUNT(*) FROM SomeTable
结果如下
如图所示: 发现确实此条语句在此例中用到的并不是主键索引,而是辅助索引,实际上在此例中我试验了,不管是 COUNT(1),还是 COUNT(),MySQL 都会用成本最小的辅助索引查询方式来计数,也就是使用 COUNT() 由于 MySQL 的优化已经保证了它的查询性能是最好的!随带提一句,COUNT()是 SQL92 定义的标准统计行数的语法,并且效率高,所以请直接使用COUNT()查询表的行数!
所以这位读者的说法确实是对的。但有个前提,在 MySQL 5.6 之后的版本中才有这种优化。
那么这个成本最小该怎么定义呢,有时候在 WHERE 中指定了多个条件,为啥最终 MySQL 执行的时候却选择了另一个索引,甚至不选索引?
本文将会给你答案,本文将会从以下两方面来分析
- SQL 选用索引的执行成本如何计算
- 实例说明
SQL 选用索引的执行成本如何计算
就如前文所述,在有多个索引的情况下, 在查询数据前,MySQL 会选择成本最小原则来选择使用对应的索引,这里的成本主要包含两个方面。
- IO 成本: 即从磁盘把数据加载到内存的成本,默认情况下,读取数据页的 IO 成本是 1,MySQL 是以页的形式读取数据的,即当用到某个数据时,并不会只读取这个数据,而会把这个数据相邻的数据也一起读到内存中,这就是有名的程序局部性原理,所以 MySQL 每次会读取一整页,一页的成本就是 1。所以 IO 的成本主要和页的大小有关
- CPU 成本:将数据读入内存后,还要检测数据是否满足条件和排序等 CPU 操作的成本,显然它与行数有关,默认情况下,检测记录的成本是 0.2。
实例说明
为了根据以上两个成本来算出使用索引的最终成本,我们先准备一个表(以下操作基于 MySQL 5.7.18)
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`score` int(11) NOT