10000 行记录的word表 取3个随机单词
select word from words order by rand() limit 3;
需要临时表，并且需要在临时表上排序
Using temporary: 需要使用临时表
Using filesort: 需要临时表排序,MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
执行流程是这样的：
- 创建临时表(是个内存表) 这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
从 words 表中，取出所有的 word 值,存入临时表。
对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数作为R.
扫描行数是 10000。
2.从内存临时表,取出 R 值和位置信息 (对memory 表就是数组坐标)，存入 sort_buffer (只拿这2个不拿整行 是rowid 排序) 排序。
这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
在 sort_buffer 中根据 R 的值进行排序()
对于 InnoDB 表来说，执行全字段排序会减少磁盘访问，因此会被优先选择。
对于内存表，回表过程 只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。优化器没有了这一层顾虑，那么它会优先考虑的，就是用于排序的行越少越好了，会选择 rowid 排序
3.取出前三个结果的位置信息，到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。
通过慢查询日志（slow log）来验证 确实是20003
# Query_time: 0.900376 Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;
磁盘临时表
如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。
磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。
当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程
优先队列排序算法
这个查询select word from words order by rand() limit 3;
因为这里 limit 3; 只要三个, 不可能超过sort_buffer_size, mysql 会用优先队列排序算法, 而不会生成多个临时文件来归并
如果是 select word from words order by rand() limit 1000; 在sort_buffer_size里面放不下1000条 就不能用 优先队列排序算法 了
太慢了
尽量还是在代码中做
网友评论