有一个主表left join 同一个小表两次分页语句，因为order by 导致执行时做排序，从执行计划中Using filesort ，以及profile中creating sort index 耗时可以看出。

从trace文件可以看出filesort的计算：

"join_execution":{"select#":1,"steps":[{"filesort_information":[{"direction":"desc","table":"`topxxx``t`","field":"create_date"}]/*filesort_information*/,"filesort_priority_queue_optimization":{"limit":20,"rows_estimate":2302749,"row_size":264,"memory_available":4194304,"chosen":true}/*filesort_priority_queue_optimization*/,"filesort_execution":[]/*filesort_execution*/,"filesort_summary":{"rows":21,"examined_rows":216594,"number_of_tmp_files":0,"sort_buffer_size":5712,"sort_mode":"<sort_key,rowid>"}/*filesort_summary*/}]/*steps*/}/*join_execution*/}

后面通过索引加入排序字段后减去排序操作,排序字段放在索引的最前面。

create index idx_topxxx1 on topic (create_date desc,is_del,is_en);

trace 中可以看出排序使用了索引。

"reconsidering_access_paths_for_index_ordering":{"clause":"ORDERBY","index_order_summary":{"table":"`topic``t`","index_provides_order":true,"order_direction":"desc","index":"idx_topxxx1","plan_changed":true,"access_type":"index"}/*index_order_summary*/}/*reconsidering_access_paths_for_index_ordering*/},{

以此记录。