mysql优化-oder by产生的Using temporary与Using filesort问题解决

下面的mysql代码因为order by的原因,出现了Using temporary与Using filesort,优化前代码如下

SELECT DISTINCT

( tm.task_no ) task_no,

tm.start_time,

tm.end_time,

tm.task_handler job_no,

cb.claim_company_code,

tc.entrust_company_code,

tm.task_status,

tsb.survey_city,

tsb.survey_province,

tsb.survey_area,

tm.task_type,

he.operation_time contact_time,

tsb.survey_address,

cb.damage_time actual_damage_time,

tdb.push_time assign_time,

cb.ins_company_report_no report_no,

cb.reportor_name,

cb.reportor_phone,

cb.report_date,

tm.receive_time,

av.record_id audio_no,

cb.ins_company_code organization,

tm.real_end_time finish_time,

db.c reassign,

ul.latitude,

ul.longitude,

tt.distance,

TIMESTAMPDIFF( SECOND, tdb.push_time, tm.receive_time ) receive_efficiency,

TIMESTAMPDIFF( SECOND, he.operation_time, tm.receive_time ) contact_efficiency,

TIMESTAMPDIFF( SECOND, tm.receive_time, tm.arrive_site_time ) arrive_efficiency,

TIMESTAMPDIFF( SECOND, tm.real_end_time, tm.receive_time ) finish_efficiency 

FROM

task_main AS tm

INNER JOIN task_survey_base AS tsb ON tsb.task_no = tm.task_no AND tsb.version_no = tm.max_version_no

LEFT JOIN task_case_base cb ON cb.task_no = tm.task_no

INNER JOIN task_company_info tc ON tm.task_no = tc.task_no 

LEFT JOIN ( SELECT min( id ), task_no, task_handler, operation_type, operation_time FROM task_handle_efficiency ) he ON he.task_no = tm.task_no AND he.task_handler = tm.task_handler AND he.operation_type = 'contact'

LEFT JOIN ( SELECT MAX( trad.id ), trad.record_id, trad.task_no FROM task_record_audio_detail trad ) av ON av.task_no = tm.task_no

LEFT JOIN ( SELECT task_no, count( 1 ) c FROM task_dispatch_base db WHERE action = '002' OR action = '004' GROUP BY task_no ) db ON db.task_no = tm.task_no

LEFT JOIN ( SELECT MAX( id ), latitude, longitude, task_no FROM task_user_location ) AS ul ON ul.task_no = tm.task_no

LEFT JOIN  (select task_no,push_time from task_dispatch_base ) tdb   ON tdb.task_no = tm.task_no 

LEFT JOIN ( SELECT MAX( id ), distance, task_no, track_status FROM task_track ) tt ON tt.task_no = tm.task_no 

AND tt.track_status = '1' 

WHERE

tm.task_type = 'carSurvey' 

ORDER BY

tdb.push_time DESC 

LIMIT 5

执行时间0.675秒

explain 解释如下,可以看到因为orderby 导致的查询过慢.

然后针对orderby 的字段push_time加索引,发现不生效.

最后使用了force index(push_time) 发现依然不生效.

然后加上了group by push_time ,又在where中强加了一个条件语句 push_time>'1970' 

发现索引生效了.如下:优化后

SELECT DISTINCT
	( tm.task_no ) task_no,
	tm.start_time,
	tm.end_time,
	tm.task_handler job_no,
	cb.claim_company_code,
	tc.entrust_company_code,
	tm.task_status,
	tsb.survey_city,
	tsb.survey_province,
	tsb.survey_area,
	tm.task_type,
	he.operation_time contact_time,
	tsb.survey_address,
	cb.damage_time actual_damage_time,
	tdb.push_time assign_time,
	cb.ins_company_report_no report_no,
	cb.reportor_name,
	cb.reportor_phone,
	cb.report_date,
	tm.receive_time,
	av.record_id audio_no,
	cb.ins_company_code organization,
	tm.real_end_time finish_time,
	db.c reassign,
	ul.latitude,
	ul