问题
如果现在有一个随机显示三个单词的需求,有什么方法实现,存在什么问题以及如何改进?
内存临时表
首先,用order by rand()来实现这个逻辑:
select word from words order by rand() limit 3;
这个语句的意思很直白,随机排序取前三个,虽然这个SQL语句写法很简单,但执行流程很难
mysql> explain select * from user order by rand() limit 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec
Extra字段显示Using temporary,表示需要临时表,Using fileSort表示的是需要执行排序操作
- 因此这个Extra的意思就是,需要临时表,并且需要在临时表上排序。
这就有一个问题:
这里到底使用的是什么排序算法呢?
对于不同引擎的表来说不同,对于InnoDB表来说,优先选择全字段排序,可以减少磁盘访问,对于MyAsim表来说,回表过程只是简单的根据数据行的位置直接访问内存得到数据,不会多访问磁盘,这时候便会使用rowid
排序
Mysql是用什么办法来定位“一行数据”的
如果你创建的表没有主键,或者把一个表的主键删掉了,那么InnoDB会自己生成一个6字节的rowid来作为主键
这也就是排序模式中,rowid
名字的来历,实际上它表示的是:每个引擎用来唯一标识数据行的信息
-
对于有主键的InnoDB表来说,这个rowId就是主键ID
-
对于没有主键的InnoDB表来说,这个rowID就是由系统生成的
-
Memory引擎不是索引组织表,在这个例子里面,你可以认为它就是一个数组,因此,这个rowID就是数组的下标
那么是不是所有的临时表都是内存表呢?
- 不是,参数tmp_table_size这个配置限制了内存临时表的大小,默认值是16M,如果临时表大小超过了
tmp_table_size
,那么内存临时表就会转成磁盘临时表 - 磁盘临时表默认的引擎是INNoDB,是由参数internal_tmp_disk_storage_engine来控制的
- 当使用磁盘临时表时,对应的就是一个没有显示索引的InnoDB表的排序过程
Mysql5.6引入的新的排序算法(数据大小超过sort_buffer_size)
优先队列算法
我们现在的SQl语句只需要取R值最小的三个rowid,但是,如果使用归并排序算法的话,虽然最终也能得到三个值,但是这个算法结束后,已经将10000行数据都排好序了
这就有点儿浪费了!!!
优先队列算法,就可以精确地之得到三个最小的值,执行流程如下(就是最大堆排序
):
- 对于这10000个准备排序的(R,rowid),先取前三行,构造成一个堆
- 取下一个行(R1,rowid1),跟当前堆里面的最大的R比较,如果R1小于R,把这个(R,rowid)从堆中去掉,换成(R1,rowid1)
- 重复第二步,直到第10000个(R10000,rowid10000)完成比较
在OPTIMIZER_TRACE结果中:
filesort_priority_queue_optimization
这个部分的chosen=true,
就表示使用了优先队列排序算法,这个过程不需要临时文件,因此对应的number_of_tmp_files
是0
这个过程结束后,我们构建的堆里就是需要的三个最小的元素
如何正确的随机呢?
1、 取得整个表的行数,记为C
2、 取得Y = floor(C * rand)),floor函数在这里的作用,就是取整数部分
3.、再用limit Y,1 取得一行
select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat('select * from limit ',@Y,',1');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;