Using filesort文件排序原理详解

trace 

trace是MySQL内置分析工具，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭，通过trace可以看到MySQL对查询请求的格式化、优化、计算预估成本、最优的访问方式以及其他信息。选择执行方式主要是通过cost成本计算。

set session optimizer_trace="enabled=on",end_markers_in_json=on; 
SELECT * FROM task ORDER BY model_id;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `task`.`id` AS `id`,`task`.`user_id` AS `user_id`,`task`.`model_id` AS `model_id`,`task`.`note` AS `note` from `task` order by `task`.`model_id`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`task`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`task`",
                "table_scan": {
                  "rows": 2,
                  "cost": 1
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`task`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 1.4,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 1.4,
                "sort_cost": 2,
                "new_cost_for_plan": 3.4,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`task`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`task`.`model_id`",
              "items": [
                {
                  "item": "`task`.`model_id`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`task`.`model_id`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`task`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "unknown",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [
              {
                "table": "`task`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`task`",
                "field": "model_id"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 3,
              "examined_rows": 3,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 16864,
              "sort_mode": "<sort_key, rowid>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

Using filesort文件排序方式（没有使用索引进行排序）

• 单路排序：一次性取出满足条件数据行的所需要字段，然后再sort buffer（排序缓存，mysql初始化时创建）中进行排序，排序完成直接返回结果。使用trace可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >。
• 双路排序（回表排序）：首先取出满足条件的排序字段和可以直接定位数据的行ID，然后将已上字段拿到sort buffer中进行排序，排好序后再回表取出要取的字段返回。使用trace可以看到sort_mode信息里显示< sort_key, rowid >。

MySQL通过比较系统变量max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

• 如果 字段的总长度小于max_length_for_sort_data ，使用单路排序模式。
• 如果 字段的总长度大于max_length_for_sort_data ，使用双路排序模式。

MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

• 如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。
• 如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择单路排序，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。