MYSQL order by排序导致效率低小优化

有一个主表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": ""            } /* 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": "ORDER BY",              "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 */          },          {

以此记录。