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: 01 row in set (0.00 sec)mysql>
其中以上紅色部分<sort_key, additional_fields> 表示用了第二種定序。
其他的兩種<sort_key, rowid> 以及<sort_key, packed_additional_fields>分別代表第一種和後續版本MySQL的提升, 自己體驗去吧。