mysql中的order by

最新推荐文章于 2025-03-22 16:23:58 发布

澎仔

最新推荐文章于 2025-03-22 16:23:58 发布

阅读量227

点赞数

分类专栏： mysql 文章标签： mysql 数据库算法

本文链接：https://blog.youkuaiyun.com/ab1024249403/article/details/110912617

版权

mysql 专栏收录该内容

6 篇文章

订阅专栏

准备条件，创建一个单词表，使用存储过程插入1W行；

CREATE TABLE words ( `id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `word` VARCHAR ( 64 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB;

delimiter;;
CREATE PROCEDURE idata () BEGIN
	DECLARE
		i INT;
	START TRANSACTION;
	SET i = 0;
	WHILE
			i < 10000 DO
			INSERT INTO words ( word )
		VALUES
			(
				concat(
					CHAR (
					97+ ( i DIV 1000 )),
					CHAR (
					97+ ( i % 1000 DIV 100 )),
					CHAR (
					97+ ( i % 100 DIV 10 )),
					CHAR (
					97 + ( i % 10 ))));
		
		SET i = i + 1;
		
	END WHILE;
 commit;
END;;
delimiter;

CALL idata ();

select word from words ORDER BY rand() limit 3;

使用explain分析语句

explain select word from words ORDER BY rand() limit 3;

在这里插入图片描述
2. 从EXTRA字段Using temporary; Using filesort可以看到该语句使用了临时表以及相关排序操作；
3. temporary临时表分为内存临时表（默认memory引擎）以及磁盘临时表（默认innodb引擎）；内存临时表空间不够用时就会使用磁盘临时表；内存临时表空间大小由tmp_table_size控制，默认值为 16777216 = 16 * 1024 * 1024 = 16M；
4. innodb会为排序操作分配一定的内存空间使用，由参数innodb_sort_buffer_size进行空间大小控制，默认值是1,048,576 = 1024 * 1024 = 1M。在内存空间中innodb使用的是快速排序，当需要排序的数据大于innodb_sort_buffer_size时，就需要磁盘空间帮助排序了（归并排序）
5. filesort排序方式分为全字段排序（additional_fields）和rowid排序，当查询需要的数据单行长度不超过max_length_for_sort_data这个参数值，那么会优先使用全字段排序。即把所有需要的字段都放到sort_buffer中去排序，那么就不用再次回表去取数据了；而如果超过了max_length_for_sort_data这个参数值，那么就会优先使用rowid排序方式（只把需要排序的字段和主键放入sort_buffer进行排序），这样排序过程中一次可以排序更多行，但是需要再回表取数据。max_length_for_sort_data默认值4096 = 4 * 1024 = 4K
6. 当需要排序的查询语句中使用了limit关键字，限制了需要返回的行数。那么mysql会根据实际情况来使用不同的排序算法（优先队列排序算法、归并排序等）
7. 优先队列排序算法效率更高，并不需要对全部数据进行排序，只需要通过维护一个堆的数据结构，然后遍历一遍数据来调整这个堆的数据值即可，复杂度为O(n)；归并排序则是把所有数据都排好序了再根据limit取前N条，后面排序的数据其实是浪费的；
8. max_length_for_sort_data的值并不是越大越好，这个值过大的话，那么innodb就会使用全字段排序，那么sort_buffer的空间很可能不足以支持数据在内存中排序，就只能借助于磁盘空间来进行排序。（可能无法使用优先队列排序）

如何判断innodb是否使用了优先队列排序

# 打开 optimizer_trace，只对本线程有效
set optimizer_trace = 'enabled=on';
select word from words ORDER BY rand() limit 2000;
select * from `information_schema`.`OPTIMIZER_TRACE`

在这里插入图片描述

“filesort_priority_queue_optimization.chosen”: "true"代表使用了优先队列排序；
"sort_mode"可以看到是使用了全字段排序还是rowid排序
当limit n 这个n的值过大时，一般就不会使用优先队列排序算法了。因为n值过大，需要维护这个堆的成本就越大，sort_buffer的空间很可能就不足以维护这个堆。此时可以通过调低max_length_for_sort_data的值来使得innodb使用rowid排序，以降低需要排序的数据大小。但是rowid排序需要再次回表取数据！