从单词表中随机选出三个单词。
CREATE TABLE `words` (
`id` int(11) NOTNULLAUTO_INCREMENT,
`word` varchar(64) DEFAULTNULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
随机插入 10000 行数据,之后来分析。
内存临时表
select word fromo words order by rand() limit 3
这个 SQL 就是随机排序取前三个,但执行过程有点复杂。
我们使用 explain 这个命令来看看这个语句的执行情况:
在 Extra 这个字段上的 Using temporary,表示使用了临时表;Using filesort,表示需要执行排序操作。
我们上节课得出的结论:对于 InnoDB 表来说,执行全字段排序会减少磁盘访问,会被优先选择。
这里强调的是 InnoDB 表,但是优化器(server 层)就没有了这个顾虑,那么它会优先考虑,用于排列的行越少越好,所以,MySQL 这时会选择 rowid 排序。那这条语句的执行过程是这样:
- 创建一个临时表,这个临时表使用的是 memory 引擎,表里有两个字段,第一个字段是 double 类型,记为字段 R,第二个字段是 varchar(64) 类型,记为字段 W。并且这个表没有主键。
- 从 word 表中,按主键顺序取出所有 word 值。对于每一个 word 值,调用 rand() 函数生成一个大于 0 小于 1 的随机小数,并和 word 存入临时表的 R 和 W 字段中,到此,扫描行数是 10000;
- 现在在没有索引的内存临时表中,按照字段 R 进行排序;
- 初始化 sort_buffer。sort_buffer 中有两个字段,一个是 double 类型,另一个是整型。
- 从内存临时表中一行一行的取出 R 值和位置信息,分别存入 sort_buffer 中的两个字段里。这个过程要对临时表进行全表扫描,扫描行数变成了 20000;
- 在 sort_buffer 中根据 R 的值进行排序。这里没有涉及到表操作,扫描行数不变;
- 排序完成后,取出前三个结果的位置信息,依次从内存临时表中取出 word 值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了 20003。
在这里 pos 就是位置信息。这里就涉及到:MySQL 表是用什么方法来定位“一行数据”的。
如果我们把表的主键删掉,那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。
这就是排序模式中 rowid 的来源,rowid 实际上表示:每个引擎用来唯一表示数据行的信息。
- 对于有主键的 InnoDB 表,这个 rowid 就是主键 ID;
- 对于没有主键的表,这个 rowid 就是由系统生成;
- MEMORY 引擎不是索引组织表。这个例子中约等于数组,rowid 就是下标。
总结:order by rand() 使用了内存临时表,内存临时表排序时使用了 rowid 排序方法。
磁盘临时表
tmp_table_size
这个配置限制了内存临时表的大小,默认值是 16M。如果临时表大小大于这个值,那么就会转成磁盘临时表。
基本上就是使用外部排序了。
不过 MySQL 5.6 版本引入了一个叫:优先队列排序算法。
上面的 SQL 语句就可以使用这个算法,原理是使用小根堆进行排序,因为只要取 3 个数据。
随机排序方法
先简化一下问题,如果只随机选择 1 个 word 值,怎么做呢?
- 取这个表的主键 id 的最大值 M 和最小值 N;
- 用随机函数生成一个最大值到最小值之间的数 X = ( M − N ) ∗ r a n d ( ) + N X=(M-N)*rand()+N X=(M−N)∗rand()+N。
- 取不小于 X 的第一个 ID 的行。
把这个算法,暂时称为算法 1。
select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @Xlimit 1;
在 MySQL 中 @..
是用户变量,@@..
是系统变量。
这个算法效率很高,但是不严格满足题目随机要求,因为 ID 中间可能有空洞,导致不同行的概率是不一样的。
现在来看算法 2:
- 取得整张表的行数,记为 C;
- 取得 Y = f l o o r ( C ∗ r a n d ( ) ) Y=floor(C*rand()) Y=floor(C∗rand())。floor 函数在这里作用是取整数部分。
- 再用 limit Y,1 取一行。
select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;
由于 limit 后面的参数不能直接跟变量,所以使用了 prepare+execute 方法,相当于 SQL 语句的拼接。
这个算法就是按顺序一个个读,丢到前 Y 个,取 Y + 1 行,这要扫描 Y + 1 行。执行代价比算法 1 高。当然,和 order by rand() 比起来开销还是小很多。
按照算法 2 的思路,随机取三个呢?那就是取整张表行数 C ,得出三个随机数,再执行三个 limit Y,1
语句。
select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1,1; //在应用代码里面取Y1、Y2、Y3值,拼出SQL后执行
select * from t limit @Y2,1;
select * from t limit @Y3,1;