当前位置:Gxlcms > mysql > [原]MySQL新旧版本ORDERBY处理方法_MySQL

[原]MySQL新旧版本ORDERBY处理方法_MySQL

时间:2021-07-01 10:21:17 帮助过:33人阅读

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 idTRACE: {"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 *                    

人气教程排行