MySQL 选错索引

查询语句执行性能

explain 查看优化器选择的索引

explain select * from t where a between 10000 and 20000

慢日志查询语句执行时间和扫描行数

set long_query_time=0;
select * from t where a between 10000 and 20000;
select * from t force index(a) where a between 10000 and 20000;

选错索引原因

查看索引基数

show index from t

查看预估扫描行数

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

修复预估行数不准确

analyze table t

选错索引处理

使用 force index

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

改写 sql 引导优化器选择正确索引

创建新的索引或删除错误索引