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;
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;
end;;
delimiter ;
call idata();
如何随机取3个单词
select word from words order by rand() limit 3;
-- 查看上面语句的执行情况
explain select word from words order by rand() limit 3;
Extra中Using temporary表示使用临时表,Using filesort表示需要执行排序操作。
上述默认使用的临时表是内存表,对于内存表来说,回表过程只是简单地根据数据行的位置直接访问内存得到数据,并不会导致额外的磁盘访问,因此MySQL会在排序时会优先使用rowid排序。
上述SQL语句的执行过程如下:
- 创建一个临时表(该表使用的是memory引擎),表里有两个字段,第一个字段是double类型(记为字段R),第二个字段是varchar(64)类型(记为字段W),临时表没有索引
- 从word表中,按照主键顺序取出所有的word值,对于每一个word值,调用rand函数生成一个大于0小于1的随机小数,把该随机小数和word值存入临时表的R和W字段中,至此扫描行数是10000
- 临时表目前有10000行数据,下面需要对这个临时表按照字段R进行排序
- 初始化sort_buffer,sort_buffer中有两个字段,一个是double类型,另一个是整型
- 从内存临时表中逐行取出R值和位置信息,分别存入sort_buffer中的两个字段,此时扫描行数又增加了10000行,变成了20000(MySQL8.0.12以后这里还是10000行,应该是对内存表做了优化,有知道的朋友可以留言告诉我)
- 在sort_buffer中根据R值进行排序
- 排序完成以后,取出前三个结果的位置信息,到内存临时表中取出word值,返回给客户端。总扫描行数变为20003(MySQL8.0以后这里是10003行)。
MySQL8.0下慢查询日志如下图,扫描行数为100003行:
临时表只能是内存表么?
答案是NO。那什么时候临时表会使用内存,什么时候又会使用磁盘呢?该选择主要依赖于tmp_table_size参数的控制,默认是16M,如果临时表的大小超过了tmp_table_size,那么内存临时表就会转换为磁盘临时表。
磁盘临时表使用的默认引擎是InnoDB,由参数default_tmp_storage_engine控制。
set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace,只对本线程有效 */
SET optimizer_trace='enabled=on';
/* 执行语句 */
select word from words order by rand() limit 3;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
在我们上图的输出中,我们可以看出排序算法使用了优先队列排序算法,然后是全字段排序(也就是说不用回表)。
peak_memory_used代表排序时使用到的内存,按道理应该等于sort_buffer_size指定的值,之所以不等的原因是作者本人的MySQL是8.0.12版本。
MySQL8.0.12之前,MySQL优化器会为排序直接分配sort_buffer_size指定大小的内存,但从MySQL8.0.12开始,为排序分配内存是以增量的方式进行。