前言
大家使用过MySQL索引,都知道在 MySQL 中一张表其实是可以支持多个索引的。但是,你写 SQL 语句的时候,并没有主动指定使用哪个索引。也就是说,使用哪个索引是由 MySQL 来确定的。
不知道你有没有碰到过这种情况,一条本来可以执行得很快的语句,却由于 MySQL 选错了索引,而导致执行速度变得很慢?
一起来看个例子吧,建一张简单表,表里有 a、b 两个字段,并分别建上索引:
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
然后,我们往表 t 中插入 10 万行记录,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。
然后执行一条查询语句:
mysql> select * from t where a between 10000 and 20000;
那么,很显然,这条语句走的是a字段索引。
在我们已经准备好的包含了 10 万行数据的表上,我们再做如下操作:
这时候,session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了。我们可以通过慢查询日志(slow log)来查看一下具体的执行情况。
为了说明优化器选择的结果是否正确,我增加了一个对照,即:使用 force index(a) 来让优化器强制使用索引 a。
set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
- 第一句,是将慢查询日志的阈值设置为 0,表示这个线程接下来的语句都会被记录入慢查询日志中;
- 第二句,Q1 是 session B 原来的查询;
- 第三句,Q2 是加了 force index(a) 来和 session B 原来的查询语句执行情况对比。
可以看到,Q1 扫描了 10 万行,显然是走了全表扫描,执行时间是 40 毫秒。Q2 扫描了 10001 行,执行了 21 毫秒。也就是说,我们在没有使用 force index 的时候,MySQL 用错了索引,导致了更长的执行时间。
那么,到底是为什么呢?
优化器的逻辑
我们知道,选择索引是优化器的工作。而优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。
当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
- 扫描行数是怎么判断的?
MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。
我们可以使用 show index 方法,看到一个索引的基数。
- MySQL 是怎样得到索引的基数的呢?
MySQL采用“采样统计”的方式来计算索引基数。采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:
- 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。
- 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。
由于是采样统计,所以不管 N 是 20 还是 8,这个基数都是很容易不准的。
既然是统计信息不对,那就修正。analyze table t 命令,可以用来重新统计索引信息。我们来看一下执行效果。
所以在实践中,如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大,可以采用这个方法来处理。
索引选择异常和处理
其实大多数时候优化器都能找到正确的索引,但偶尔你还是会碰到我们上面举例的这两种情况:原本可以执行得很快的 SQL 语句,执行速度却比你预期的慢很多,你应该怎么办呢?
- 一种方法是,采用 force index 强行选择一个索引。MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项,然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中,就直接选择这个索引,不再评估其他索引的执行代价。
- 第二种方法就是,我们可以考虑修改语句,引导 MySQL 使用我们期望的索引。比如,在这个例子里,显然把“order by b limit 1” 改成 “order by b,a limit 1” ,语义的逻辑是相同的。
或者如下修改:
mysql> select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
- 第三种方法是,在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。
总结
今天我们一起学习了索引统计的更新机制,并提到了优化器存在选错索引的可能性。
对于由于索引统计信息不准确导致的问题,你可以用 analyze table 来解决。
而对于其他优化器误判的情况,你可以在应用端用 force index 来强行指定索引,也可以通过修改语句来引导优化器,还可以通过增加或者删除索引来绕过这个问题。