MySQL 的order by 涉及到三個參數:
我來簡單說下MySQL的排序規則。
MySQL內部有兩種排序規則：
這種排序的特點是節省內存，但是最終會對磁盤有一次隨機掃描。 大概主要過程如下：
第二種，是冗余排序。這種排序的特點是不需要二次對磁盤進行隨機掃描，但是缺點很明顯，太浪費內存空間。
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, rowid> 以及<sort_key, packed_additional_fields>分別代表第一種和後續版本MySQL的提升， 自己體驗去吧。
