【原创】MySQL新旧版本ORDER BY 处理方法

唐伯虎

　　MySQL 的order by 涉及到三个参数:
　　A. sort_buffer_size 排序缓存。
　　B. read_rnd_buffer_size 第二次排序缓存。
　　C. max_length_for_sort_data 带普通列的最大排序约束。
　　

　　我来简单说下MySQL的排序规则。
　　假设查询语句select * from tb1 where 1 order by  a ; 字段a没有建立索引；以上三个参数都足够大。
　　MySQL内部有两种排序规则：
　　第一种，是普通的排序。这种排序的特点是节省内存，但是最终会对磁盘有一次随机扫描。 大概主要过程如下：
　　1. 由于没有WHERE条件，所以直接对磁盘进行全表扫描，把字段a以及每行的物理ID（假设为TID）拿出来。然后把所有拿到的记录全部放到sort_buffer_size中进行排序。
　　2. 根据排好序的TID，从磁盘随机扫描所需要的所有记录，排好序后再次把所有必须的记录放到read_rnd_buffer_size中。
　　第二种，是冗余排序。这种排序的特点是不需要二次对磁盘进行随机扫描，但是缺点很明显，太浪费内存空间。
　　跟第一种不同的是，在第一步里拿到的不仅仅是字段a以及TID，而是把所有请求的记录全部拿到后，放到sort_buffer_size中进行排序。这样可以直接从缓存中返回记录给客户端，不用再次从磁盘上获取一次。
　　从MySQL 5.7 后，对第二种排序进行了打包压缩处理，避免太浪费内存。比如对于varchar(255)来说，实际存储为varchar(3)。那么相比之前的方式节约了好多内存，避免缓存区域不够时，建立磁盘临时表。
　　

　　以下为简单的演示

mysql> use t_girl;
Database changed

　　

　　三个参数的具体值：

mysql> select truncate(@@sort_buffer_size/1024/1024,2)||'MB' as 'sort_buffer_size',truncate(@@read_rnd_buffer_size/1024/1024,2)||'MB' as read_rnd_buffer_zie,@@max_length_for_sort_data as max_length_for_sort_data;
+------------------+---------------------+--------------------------+
| sort_buffer_size | read_rnd_buffer_zie | max_length_for_sort_data |
+------------------+---------------------+--------------------------+
| 2.00MB       | 2.00MB        |           1024 |
+------------------+---------------------+--------------------------+
1 row in set (0.00 sec)

　　

　　演示表的相关数据：

mysql> select table_name,table_rows,concat(truncate(data_length/1024/1024,2),'MB') as 'table_size' from information_schema.tables where table_name = 't1' and table_schema = 't_girl';
+------------+------------+------------+
| table_name | table_rows | table_size |
+------------+------------+------------+
| t1     |  2092640 | 74.60MB  |
+------------+------------+------------+
1 row in set (0.00 sec)

　　

　　

　　开启优化器跟踪：

mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.00 sec)

　　

　　从数据字典里面拿到跟踪结果：

mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
              QUERY: select * from t1 where id < 10 order by id
              TRACE: {
  "steps": [
  {
    "join_preparation": {
    "select#": 1,
    "steps": [
      {
      "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`log_time` AS `log_time` from `t1` where (`t1`.`id` < 10) order by `t1`.`id`"
      }
    ] /* steps */
    } /* join_preparation */
  },
  {
    "join_optimization": {
    "select#": 1,
    "steps": [
      {
      "condition_processing": {
        "condition": "WHERE",
        "original_condition": "(`t1`.`id` < 10)",
        "steps": [
        {
          "transformation": "equality_propagation",
          "resulting_condition": "(`t1`.`id` < 10)"
        },
        {
          "transformation": "constant_propagation",
          "resulting_condition": "(`t1`.`id` < 10)"
        },
        {
          "transformation": "trivial_condition_removal",
          "resulting_condition": "(`t1`.`id` < 10)"
        }
        ] /* steps */
      } /* condition_processing */
      },
      {
      "table_dependencies": [
        {
        "table": "`t1`",
        "row_may_be_null": false,
        "map_bit": 0,
        "depends_on_map_bits": [
        ] /* depends_on_map_bits */
        }
      ] /* table_dependencies */
      },
      {
      "ref_optimizer_key_uses": [
      ] /* ref_optimizer_key_uses */
      },
      {
      "rows_estimation": [
        {
        "table": "`t1`",
        "table_scan": {
          "rows": 2092640,
          "cost": 4775
        } /* table_scan */
        }
      ] /* rows_estimation */
      },
      {
      "considered_execution_plans": [
        {
        "plan_prefix": [
        ] /* plan_prefix */,
        "table": "`t1`",
        "best_access_path": {
          "considered_access_paths": [
          {
            "access_type": "scan",
            "rows": 2.09e6,
            "cost": 423303,
            "chosen": true,
            "use_tmp_table": true
          }
          ] /* considered_access_paths */
        } /* best_access_path */,
        "cost_for_plan": 423303,
        "rows_for_plan": 2.09e6,
        "sort_cost": 2.09e6,
        "new_cost_for_plan": 2.52e6,
        "chosen": true
        }
      ] /* considered_execution_plans */
      },
      {
      "attaching_conditions_to_tables": {
        "original_condition": "(`t1`.`id` < 10)",
        "attached_conditions_computation": [
        ] /* attached_conditions_computation */,
        "attached_conditions_summary": [
        {
          "table": "`t1`",
          "attached": "(`t1`.`id` < 10)"
        }
        ] /* attached_conditions_summary */
      } /* attaching_conditions_to_tables */
      },
      {
      "clause_processing": {
        "clause": "ORDER BY",
        "original_clause": "`t1`.`id`",
        "items": [
        {
          "item": "`t1`.`id`"
        }
        ] /* items */,
        "resulting_clause_is_simple": true,
        "resulting_clause": "`t1`.`id`"
      } /* clause_processing */
      },
      {
      "refine_plan": [
        {
        "table": "`t1`",
        "access_type": "table_scan"
        }
      ] /* refine_plan */
      }
    ] /* steps */
    } /* join_optimization */
  },
  {
    "join_execution": {
    "select#": 1,
    "steps": [
      {
      "filesort_information": [
        {
        "direction": "asc",
        "table": "`t1`",
        "field": "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": 62390,
        "examined_rows": 2097152,
        "number_of_tmp_files": 0,
        "sort_buffer_size": 2097152,
        "sort_mode": "<sort_key, additional_fields>"
      } /* filesort_summary */
      }
    ] /* steps */
    } /* join_execution */
  }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
      INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
mysql>

　　

　　其中以上红色部分<sort_key, additional_fields> 表示用了第二种排序规则。
　　其他的两种<sort_key, rowid> 以及<sort_key, packed_additional_fields>分别代表第一种和后续版本MySQL的提升， 自己体验去吧。