业务场景
比如，要做一个学习单词的App，实现用户在打开首页的时候，随机显示三个单词：
// 创建单词表 mysql> create table words( `id` int NOT NULL AUTO_INCREMENT, `word` varchar(64) DEFAULT NULL, PRIMARY KEY(`id`) )ENGINE=InnoDB; // 插入10000行模拟数据 mysql> insert into words values(1, "abandon") (...) (...); /******************************************************** * @ order by rand() * 实现随机排序,随机选取三个单词 ********************************************************/ mysql> select word from words order by rand() limit 3; // 使用explain命令查看该语句的执行情况 mysql> explain select word from words order by rand() limit 3;
通过学习前面 Mysql数据库 order by 实现原理，我们知道，对于使用InnoDB引擎的表来说，全字段排序，可以不用回表，所以，会被优先采用。但是，对于内存表来说，数据已经读到内存了，无非是根据数据行的位置，直接访问内存的得到，不会再访问磁盘。所以，对于使用内存临时表进行的排序来说，优化器没有了回表的顾虑，rowid 排序会被优先采用。
内存临时表
explain结果，Extra字段 Using temporary表示使用临时表，Using filesort表示需要排序。
SQL语句执行流程
- 创建临时表，使用memory引擎。该临时表有两个字段，一个 double 类型字段，我们记为R，一个varchar（64）类型字段，我们记为W，表上没有索引。
- 根据主键id的顺序，从 words 表中读取 word 值。对于每个 word 值，调用rand()生成一个0~1之间的随机小数，放到R字段，并将对应的 word 放入W字段。
- 此时，临时表中有10000行数据，我们需要根据 R 的值进行排序；
- 初始化 sort_buffer，确定放入 doule 和 int 两个字段；（其中：double字段保存临时表的R值，int字段保存“位置信息”，后面我们解释为什么保存的是位置信息）
- 从内存临时表中一行一行读取R和 “位置信息”放入sort_buffer；
- 根据R字段进行排序，排序完，取前三行结果，依次到内存临时表根据位置信息取出word值，返回。
Mysql表是如何定位一条记录的
答案是：rowid，每个引擎用来唯一标记行信息的标识。
- 对于创建了主键的 InnoDB 表来说，rowid 就是主键ID；
- 没有创建主键的 InnoDB 表，系统会默认生成一个6字节的 rowid 作为主键；
- 对于Memory引擎的表，“位置信息”可以理解为数组的下标。
磁盘临时表
临时表一定是内存临时表吗？不一定。
参数 tmp_table_size 限制了内存临时表的大小，默认16 M，如果超过最大限制，就会使用磁盘临时表，磁盘临时表使用的引擎默认是 InnoDB，当使用磁盘临时表的时候，对应的就是一个没有显示索引的InnoDB表的排序过程。
排序优化
经过上面的排序完成后，所有的数据都有序了，但是我们只需要前三个，有点浪费。所以Mysql在5.6版本之后引入新的排序算法：优先队列排序算法（堆排序）。
mysql> select word from words order by rand() limit 3;
如何得到严格的随机结果
- 扫描全表，得到总行数C
- 取得 Y=floor(C * rand())。floor取整。
- select * from words limit Y,1 取得一行
limit Y, 1; 和 id >= Y limit 1; 一样吗？
不一样，MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果。
笔记参考于极客时间《MySQL实战45讲》
