一. 设置MySQL慢查询日志输出
set long_query_time = 0; -- 开启慢查询日志记录
select * from t force index(a) where a between 10000 and 20000; -- 强制走索引
二. 出现选错索引的情况
- 因为估算扫描行数付出的代价选错索引
优化器的逻辑:优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。
**估算扫描行数:**索引的区分度,一个索引上的不同值越多,这个索引的区分度就越好。而一个索引上不同的值的个数称之为基数,这个基数越大索引的区分度越好。 优化器采用的是采样统计。 表中数据删增幅度比较大优化器中统计的索引信息的rows未及时刷新,优化器认为走索引执行回表的代价要比直接进行全表扫描代价大。使用:anaylyze table t可以重新统计索引信息。 - 因为排序而导致选错索引。
mysql> select *
from t
where (a between 1 and 1000)
and (b between 50000 and 100000)
order by b limit 1;
如果b,a均为索引,并且连续,从上边来看走索引a的扫描行数要少于b,但是以上语句却会走索引b。因为优化器认为使用索引b不需要再做排序,因为索引本身有序,只需要遍历。
做如下优化:
mysql> select *
from (select *
from t
where (a between 1 and 1000)
and (b between 50000 and 100000) order by b limit 100) alias
imit 1;
这就意味这这两个索引都需要排序,因此扫描行数就成为了影响决策的主要条件。于是此时选择了只需要扫描1000行的索引a.