MySQL old and new versions ORDER

MySQL's order by involves three parameters:
A. sort_buffer_size sort cache.
B. read_rnd_buffer_size second sorting cache.
C. max_length_for_sort_data has the maximum sorting constraint for normal columns.


Let me briefly describe the sorting rules of MySQL.
Assume that the query statement select * from tb1 where 1 order by a; field a has not been indexed; the preceding three parameters are large enough.
There are two sorting rules in MySQL:
The first type is normal sorting.This sort feature saves memory, but a random scan is performed on the disk. The main process is as follows:
1. Because there is no WHERE condition, the disk is directly scanned for the full table, and the physical IDs of field a and each row (assumed as TID) are taken out. Put all the obtained records in sort_buffer_size for sorting.
2. According to the sorted TID, all required records are randomly scanned from the disk, sorted, and all required records are placed in read_rnd_buffer_size.
The second type is redundant sorting.This sorting feature does not require secondary random scanning of disks, but the disadvantage is obvious, which is a waste of memory space.
Different from the first one, in the first step, we get not only fields a and TID, but all request records and put them in sort_buffer_size for sorting. In this way, records can be directly returned from the cache to the client, instead of being retrieved from the disk once again.
After MySQL 5.7, compress the second sort to avoid wasting memory. For example, for varchar (255), the actual storage is varchar (3 ). Compared with the previous method, this saves a lot of memory to avoid creating a temporary disk table when the cache area is insufficient.


The following is a simple demonstration
Mysql> use t_girl;
Database changed


The specific values of the three parameters:

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)

Data of the demo table:

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)

Enable optimizer tracking:

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

Get the tracking result from the data dictionary:

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: 01 row in set (0.00 sec)mysql> 

<Sort_key, additional_fields> indicates that the second sorting rule is used.

The other two <sort_key, rowid> and <sort_key, packed_additional_fields> indicate the upgrade of MySQL In the first and later versions.