背景
生产数据表达到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误判了查询方案，才导致的全表扫描，
解决方案
- 最优方案，把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;