MySQL 选错索引

查看执行计划选择的索引

explain select * from t where a between 10000 and 10000;

通过慢日志（slow log）查看语句执行情况

-- 启动慢日志
SHOW VARIABLES LIKE 'slow_query_log';

-- 慢日志存储位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 慢日志阈值
SHOW VARIABLES LIKE 'long_query_time';

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

强制选择索引

select  * from t force index(a) where a between 10000 and 20000;

选错索引原因：统计信息不准确

基数：索引上不同值的个数，基数越大，索引区分度越好。

查看索引基数

show index from t;

MySQL 计算索引基数使用采样统计法：

• 选择N个页，统计每页不同值，计算平均数，再乘以页面数，得到索引基数
• 当变更行数超过 1/M 时，自动触发索引统计

索引统计方式，innodb_stats_persistent

• on，统计信息持久持久化，N=20，M=10
• off，统计信息只存储在内存，N=8，M=16

通过执行计划查看预告扫描行数

explain select * from t where a between 10000 and 20000;

优化器选择索引判断：基数、扫描行数、回表次数

可以通过执行计划和慢日志判断预估扫描行数是否符合实际扫描行数

修复统计信息不准确

analyze table t

选错索引原因：需要使用临时表或排序

因为需要排序，InnoDB 可能会选择 b 索引

explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

选错索引处理

使用 force index 强行选择索引

select * from t force index(1) where a between 10000 and 20000;

在保证逻辑相同的情况下，修改语句，引导 MySQL 使用期望的索引

explain select * from t where a between 1 and 10000 and b between 50000 and 100000 order by b, a limit 1;

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;

新建更合适的索引或删除错误索引