使用order by id引发的慢查询

背景

生产数据表达到900万条数据时有句sql出现全表扫描的情况，功能是模糊搜索file_name字段后使用id排序，sql如下，其中file_name字段有索引

select * from data_file   where file_name like 'hz_inspvatb_036%' order by id

问题分析

理想状态下这句sql应该使用file_name的索引，然后进行排序，实际对比发现不加order by id后走了索引，
加上id排序后使用了全表扫描，问题定位到order by id这里。
百度后解释如下：

你要知道innodb的普通索引块是会把主键值存进去的，当sql语句里面所有的字段都是索引字段(包括主键)时，mysql就可以只扫描索引就获取到需要的值，这时候就是索引覆盖扫描，而一旦你需要查询或者过滤其他的字段，mysql就需要回表扫描，这时候肯定是没有只扫描这个索引快了。一旦mysql发现你需要回表扫描的话，尤其还需要按照主键排序，那mysql就可能认为走主键更快就去走了主键扫描(实际上反而更慢)

所以就是由于MYSQL误判了查询方案，才导致的全表扫描，

解决方案

1. 最优方案，把order by id 改为order by create_time，使用id排序的目的是为了按照创建时间倒序，因此可以使用create_time字段替换

select * from data_file   where file_name like 'hz_inspvatb_036%' order by create_time

测试结果： 时间从10s+变为180ms
2. 使用force index 强制使用指定索引

select df.id,df.file_name from data_file df force index(file_name_index) where file_name like 'hz_ins%' order by df.id

测试结果： 时间从10s+变为180ms
3. 慢回表方案

先用like查询出结果集的id，然后将这些id和原表联表取出所需字段，并通过id排序

select df2.file_name,df2.id,df2.file_path  from (select df.id as id from data_file df  where file_name like 'hz_inspvatb_036%') res
join data_file df2 
on df2.id = res.id
order by df2.id

测试结果： 时间从10s+变为240ms

案例二SQL

select d.file_name ,d.id as id
from data_file d left join permission p on d.permission_group_id = p.permission_group_id
where  d.file_name LIKE CONCAT('20220618_20220618_134206_069.avi','%')  AND
   (d.create_by_id  = 80040319 or ( (p.employee_id = 80040319 or p.department_id = 54 ) and p.has_query=1))
group by d.id ORDER BY id;

同样发现去除order by id 后走了索引，速度从12s 提升到40ms

优化后sql

select d.file_name ,d.id as id,d.create_time as create_time
from data_file d left join permission p on d.permission_group_id = p.permission_group_id
where  d.file_name LIKE CONCAT('20220618_20220618_134206_069.avi','%')  AND
    (d.create_by_id  = 80040319 or ( (p.employee_id = 80040319 or p.department_id = 54 ) and p.has_query=1))
group by d.id ORDER BY crete_time;