MySQL old and new version order by processing method

MySQL's order by involves three parameters:
A. sort_buffer_size sort Cache.
B. Read_rnd_buffer_size the second sort cache.
C. Max_length_for_sort_data The maximum sort constraint with a normal column.


Let me just briefly say MySQL's sort rules.
Suppose query statement select * FROM TB1 where 1 order by A; Field A does not have an index; the above three parameters are large enough.
there are two sorts of collations inside MySQL:
The first kind, is the general sort. This sort is characterized by memory savings, but eventually a random scan of the disk. Probably the main process is as follows:
1. Because there is no where condition, a full table scan of the disk is performed directly, taking out the physical ID of field A and each row (assumed to be TID). Then put all the records you got into the sort_buffer_size and sort them out.
2. According to the well-ordered Tid, all the records needed to scan the disk randomly, and then put all the necessary records into the read_rnd_buffer_size in sequence.
The second is redundant sorting. The feature of this sort is that the disk does not need to be randomly scanned two times, but the drawback is obvious and too much memory space is wasted.
The first difference is that the first step is not just the field A and the TID, but all of the requested records are taken and placed in sort_buffer_size to sort. This allows you to return records directly from the cache to the client without having to retrieve it from disk again.
After MySQL 5.7, the second sort was packaged and compressed to avoid wasting memory too much. For example, for varchar (255), the actual storage is varchar (3). Then save a lot of memory compared to the previous way, avoid the cache area is not enough, set up the disk temporary table.


The following is a simple demo
mysql> use T_girl;
Database changed


Specific values for 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              |                     1024x768 |+------------------+---------------------+--------------------------+1 row in Set (0.00 sec)

Related data for 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)

To turn on optimizer tracking:

mysql> SET optimizer_trace= "Enabled=on", End_markers_in_json=on; Query OK, 0 rows Affected (0.00 sec)

Get the tracking results from the data dictionary:

Mysql> SELECT * from information_schema.optimizer_trace\g*************************** 1.                            Row *************************** Query:select * from t1 where ID < ten 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 '. ' I d ' <) Order by ' T1 '. ' ID ' "}]/* steps */}//Join_preparation */}, {" Join_optimi Zation ": {" select# ": 1," steps ": [{" condition_processing ": {" condition "                  : "WHERE", "Original_condition": "(' T1 '. ' ID ' <]", "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 ' <            "}]//* 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_optimi                Zer_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_ACC Ess_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 */, "at Tached_conditions_summary ": [{" Table ":" ' T1 ' "," Attached ":" (' T1 '. ' id ')  < "}]/* attached_conditions_summary */}/* Attaching_conditions_to_tables */}, {"clause_processing": {"clause": "ORDER by", "Original_cla              Use ":" ' T1 '. ' id ' "," items ": [{" Item ": ' T1 '. ' ID '}            ]/* Items */, "resulting_clause_is_simple": True, "Resulting_clause": "' T1 '. ' ID '" }/* clause_processing */}, {"Refine_plan": [{"Table": "' t  1 ' "," Access_type ":" Table_scan "}]/* Refine_plan */}]//* steps */}/* Join_optimization */}, {"join_execution": {"select#": 1, "steps": [{"Filesort_info               Rmation ": [{" 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_si               Ze ": 2097152," Sort_mode": "<sort_key, additional_fields>"}/* Filesort_summary *}]/* steps *///Join_execution */}]//Steps */}missing_bytes_bey ond_max_mem_size:0 insufficient_privileges:01 Row in Set (0.00 sec) mysql>

The red part above is <sort_key, and additional_fields> indicates the second sort rule is used.

The other two kinds of <sort_key, rowid> and <sort_key, packed_additional_fields> represent the first and subsequent versions of MySQL's ascension, and experience it yourself.