mysql新旧版本_MySQL新旧版本ORDER BY 处理方法

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 

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` 

}

] /* steps */

} /* join_preparation */

},

{

"join_optimization": {

"select#": 1,

"steps": [

{

"condition_processing": {

"condition": "WHERE",

"original_condition": "(`t1`.`id` 

"steps": [

{

"transformation": "equality_propagation",

"resulting_condition": "(`t1`.`id` 

},

{

"transformation": "constant_propagation",

"resulting_condition": "(`t1`.`id` 

},

{

"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_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` 

"attached_conditions_computation": [

] /* attached_conditions_computation */,

"attached_conditions_summary": [

{

"table": "`t1`",

"attached": "(`t1`.`id` 

}

] /* 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": ""

} /* filesort_summary */

}

] /* steps */

} /* join_execution */

}

] /* steps */

}

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0

INSUFFICIENT_PRIVILEGES: 0

1 row in set (0.00 sec)

mysql>

其中以上红色部分 表示用了第二种排序规则。

其他的两种 以及分别代表第一种和后续版本MySQL的提升， 自己体验去吧。

上海青大实训，最务实的上海java培训，我们的口号是：解决80,90后们与用人单位的“最后一公里”问题，我们将充分调研，根据市场用人需求的变动设置更加科学的科目，同时积极拓展就业渠道，为我们的学员提供最大的就业保障，android培训就到青大实训