mysql怎么选择合适的索引

前言: 这是源于最近工作中出现的一个mysql查询问题,该项目是之前的老项目, 最近需要准备验收了,然后发现该表的分页查询(日志表)往往要花费了十来秒左右,所以优化就迫在眉捷了

总表应该有大约20多万数据量,至于为啥20多万数据量就这么慢这个问题, 我尝试过将该表复制到其它库上执行, 发现其它库上执行都很快, 也是同样的执行计划,这我只能说客户提供给我们的这台mysql配置就很低,这个就不多赘述了

1.单列索引

用本地的模拟表进行测试

该表的sql瓶颈

select count(1) from t_audit_log_copy3 t where  t.operate_type = 1  and t.operate_time >='20250603000000' and t.operate_time <='20250603155959' ;

初始的索引结构

上述该表的执行计划

可以看到原表的查询是有两个条件, operate_time和operate_type两个单列索引,而执行计划也确实是用到了索引(operate_type),只不过该表的大部分数据都是operate_type=1的数据,所以该索引用处并不大, 那是不是可以理解为该查询从operate_type索引找到了大概19万的数据-再从19万的数据去库里面找符合时间范围的3万多条数据

2.组合索引

　　那既然这单列索引会不可避免的会进行回表查询，那是不是可以使用组合索引覆盖查询条件，这样是不是可以只在索引处完成统计操作呢

新创建的组合索引

继续执行该表执行计划

奇怪了, 为啥建了组合索引，key走的还是operate_type,跟没走索引一样,

继续分析组合索引是有最左前缀匹配原则,好吧,那我改下查询条件,毕竟我组合索引的第一列是时间字段

 

那为啥改为条件理论上也符合了最左前缀匹配原则了,可走的依然不是组合索引

继续分析:可以看到组合中operate_time为范围查询而operate_type为等值查询,通过查询资料得知

范围查询的限制: 当组合索引的第一列用于范围查询时，MySQL将停止使用该索引中的后续列，直到范围查询结束。这意味着，如果范围查询出现在组合索引的左侧，那么即使后续列有等值查询条件，MySQL也不会利用这些条件来优化查询

那是不是理解mysql的优化器认为组合索引只能使用时间范围的索引, 而为啥选择operate_type是因为operate_type是等值查询,索引可以快速定位到operate_type=1的行,然后在这些行里过滤时间范围不比组合索引的效率差, 好吧那继续修改组合的顺序

修改后的组合索引

修改组合索引后的执行计划

 终于用到了组合索引而不是选择单独索引了,在线上经过多次测试,分页查询只需0.5秒左右提高了约20倍