前言
经常在开发中会对数据库表进行多表关联查询,同时也会根据字段排序,在实际开发中根据时间字段排序是经常会出现的,在数据量少的情况下通常不会发现查询速度慢的问题,一旦数据变多就会发现查询起来的速度很慢,就好比今天我就接到一个需求就是优化查询,一开始以为只是多表关联查询引起的,结果发现是时间排序导致的,就如下图
- 加了时间排序时的语句和执行时间
select
result.report_health_id,result.user_id, result.user_name, result.id_card, result.sex, result.age,
result.category_type, result.tag_type, result.level_id, result.check_type,result.check_result,
result.check_time, result.org_id, result.create_time, result.update_time,acc.login_name,
d.device_name,dept.dept_name as org_name
from data_check_result result
left join user_info_account acc on acc.user_id = result.user_id
LEFT JOIN report_health report ON result.report_health_id = report.id
LEFT JOIN device d ON report.device_code = d.device_code
LEFT JOIN sys_dept dept ON result.org_id = dept.dept_id where result.org_id =131 ORDER BY result.check_time DESC LIMIT 10
可以看到所花费足足10秒多
- 去掉时间排序后
select
result.report_health_id,result.user_id, result.user_name, result.id_card, result.sex, result.age,
result.category_type, result.tag_type, result.level_id, result.check_type,result.check_result,
result.check_time, result.org_id, result.create_time, result.update_time,acc.login_name,
d.device_name,dept.dept_name as org_name
from data_check_result result
left join user_info_account acc on acc.user_id = result.user_id
LEFT JOIN report_health report ON result.report_health_id = report.id
LEFT JOIN device d ON report.device_code = d.device_code
LEFT JOIN sys_dept dept ON result.org_id = dept.dept_id where result.org_id =131 LIMIT 10
可以看到一秒不到就执行完了,由此可见执行慢就是时间排序引起的问题,那么如何解决呢。
创建索引
我们可以针对时间字段来建一个索引,就拿图中的data_check_result表来说,建立如下索引
CREATE INDEX idx_data_check_result_check_time ON data_check_result (check_time);
修改查询语句
建立索引之后,我们就可以使用索引来查询,一般不会使用你建立的索引,所以我们需要强制mysql使用你建立的索引。
使用FORCE INDEX (idx_data_check_result_check_time)来强制使用索引
select
result.report_health_id,result.user_id, result.user_name, result.id_card, result.sex, result.age,
result.category_type, result.tag_type, result.level_id, result.check_type,result.check_result,
result.check_time, result.org_id, result.create_time, result.update_time,acc.login_name,
d.device_name,dept.dept_name as org_name
from data_check_result result FORCE INDEX (idx_data_check_result_check_time)
left join user_info_account acc on acc.user_id = result.user_id
LEFT JOIN report_health report ON result.report_health_id = report.id
LEFT JOIN device d ON report.device_code = d.device_code
LEFT JOIN sys_dept dept ON result.org_id = dept.dept_id where result.org_id =131 ORDER BY result.check_time DESC LIMIT 10
可以看到在使用索引后查询的速度也只要1秒不到了