How to deal with ORDERBY in the new and old MySQL versions

MySQL's order by involves three parameters:
Let me briefly describe the sorting rules of MySQL.
There are two sorting rules in MySQL:
This sort feature saves memory, but a random scan is performed on the disk. The main process is as follows:
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.
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; + ------------------ + bytes + | sort_buffer_size | bytes | + ---------------- + bytes + | 2.00 MB | 2.00 MB | 1024 | + ------------------ + bytes + 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 | bytes | table_size | + ------------ + | t1 | 2092640 | 74.60 MB | + ------------ + -------------- + 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": []/* dependencies */}]/* 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 */}, {"condition": {"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 ", "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 */} restart: 0 INSUFFICIENT_PRIVILEGES: 01 row in set (0.00 sec) mysql>
The other two <sort_key, rowid> and <sort_key, packed_additional_fields> indicate the upgrade of MySQL In the first and later versions.