假如我们要从一个单词表中随机取出三个单词，那么这个sql语句该怎么写呢？建表语句和向表中插入数据的命令如下所示。

    1. CREATE TABLE `words` (
    2.   `id` int(11) NOT NULL AUTO_INCREMENT,
    3.   `word` varchar(64) DEFAULT NULL,
    4.   PRIMARY KEY (`id`)
    5. ) ENGINE=InnoDB;
    7. delimiter ;;
    8. create procedure idata()
    9. begin
    10.   declare i int;
    11.   set i=0;
    12.   while i<10000 do
    13.     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))));
    14.     set i=i+1;
    15.   end while;
    16. end;;
    17. delimiter ;
    19. call idata();

    在这里向表中插入了10000条数据。那么怎么从这个单词表里随机取出三个单词呢？
    很自然的想法是我们可以使用Mysql提供的语法order by rand()来实现，那么整个sql语句如下所示。

    select word from words order by rand() limit 3;

    这个sql的语义很简单，先对单词表进行排序，然后取出排序结果中的前3个。那么这个sql语句的执行过程是什么样的呢？我们先来看一下这条语句的explain执行计划。
    image.png
    在extra字段中记录的是using temporary，这个字符串的意思是在这个语句的执行过程中使用了临时表，using filesort的意思是进行了排序。什么是临时表？临时表就是在我们保存临时数据的时候使用的表，什么是临时数据？就是我们在短时间内会使用的数据。临时表只能在创建临时表的sql语句所在的连接上能够看到，并且在连接断开时会删掉临时表，删掉临时表是什么意思？并且释放掉临时表所占用的空间，释放掉临时表所占用的空间是怎么释放的呢？这个extra字段的值表示的是在这个sql语句的执行过程中会用到临时表，并且会对临时表中的数据行进行排序。
    在Mysql中怎么创建一个临时表呢？在Mysql中创建临时表的sql语句如下所示，使用create temporary table关键字来创建临时表。
    怎么从一个单词表中随机取出三个单词？ - 图2
    创建临时表和创建普通表的sql语句很相似，只要在创建普通表的sql语句中的table关键字前面加上temporary关键字，这时创建的表就是临时表了。
    那么在Mysql中怎么删除一个临时表呢？删除临时表和删除普通表使用的sql语句是一样的，使用drop table关键字来删除临时表，如下所示。
    怎么从一个单词表中随机取出三个单词？ - 图3
    临时表和普通表最大的区别就在于临时表只对创建这个临时表的连接可见，并且在连接断开时会自动把临时表删除。
    那么内存表是怎么样的一张表呢？它和普通表有什么区别呢？内存表是一种结构信息持久化到磁盘上，但是数据保存在内存中的表。表的结构信息是指什么？指的就是这个表包含了哪些字段，每个字段占用多少字节这些信息。
    在Mysql中是怎么创建一个内存表的呢？使用的也是create table关键字，但是需要将使用的存储引擎设置为memory存储引擎，创建内存表的sql语句如下。
    怎么从一个单词表中随机取出三个单词？ - 图4
    创建内存表和创建普通表的sql语句的区别在于创建内存表是将存储引擎设置为memory存储引擎。
    那么在Mysql中是怎么删除一个内存表的呢？删除内存表和删除普通表的sql语句是一样的，都是使用drop table关键字来删除。
    怎么从一个单词表中随机取出三个单词？ - 图5
    那么order by rand()语句的执行过程是什么样的呢？
    Mysql会先创建一个临时表，这个临时表使用的是memory存储引擎，在这个临时表中只有两个字段，第一个字段的数据类型是double数据类型，我们假设这个字段的名称是R，第二个字段的数据类型是varchar(64)数据类型，我们假设这个字段的名称是W，在临时表上是不存在索引的。
    然后Mysql会从主键索引树上依次取出所有叶子节点中的word字段的值。每取出一个word字段的值，都会调用rand()函数生成一个0到1之间的数字，然后把这个word字段的值和生成的小数存放到临时表的同一行中，并且分别存放到W字段和R字段中，由于会取出主键索引树上10000个叶子节点中的值，所以到目前为止扫描行数是10000。
    现在在临时表中有10000个数据行，接下来要做的事情是在这个没有索引的临时表上根据R字段的值对数据行进行排序。那么是怎么进行排序的呢？
    首先还是确定在sort_buffer中会放入哪些值，在这个执行过程中我们会向sort_buffer中放进两列值，第一列是double数据类型，第二列是整数类型。接下来就是从临时表中取出每一行的随机小数值，和这个数据行的位置信息，把这两个值放进sort_buffer中，在这个过程中会遍历临时表中所有的数据行，所以扫描行数需要再加上10000，达到20000。
    然后根据R的值对sort_buffer中的数据行进行排序，由于在这个过程中并没有对表中的数据行进行操作，所以并不会增加扫描的行数。
    在完成排序之后，取出排序结果中的前3个数据行中的位置信息，然后根据这些位置信息回到临时表中取出相应的单词，再把取到的单词返回给客户端。在这个过程中，对临时表上的三个数据行进行了操作，所以扫描行数增加到了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;

    其中，rows_examined字段的值就是扫描的行数，rows_examined字段的值是20003表示的就是在这个语句的执行过程中扫描了20003行数据。
    在前面提到过在sort_buffer中保存的是数据行的位置信息，数据行的位置信息指的是什么？既然是位置信息，那么我们就可以根据这个信息来找到数据行，那么Mysql是用什么来标识一个数据行的呢？
    如果把一个Innodb表的主键删掉，那么是不是在这个表上就没有主键了，既然没有主键了那么主键索引树也就不存在了，需要在主键字段上建立索引才有主键索引树吗？并不是，只要我们给一张表设置了主键，那么就会自动生成一棵主键索引树，而不需要专门在主键字段上建立索引，而没有了主键索引树那就没办法回到主键索引树上取数据了，没办法回到主键索引树上取数据那我们该如何获取完整的数据行呢？通常在创建表的时候就会设置主键，那么怎么给一个Innodb表设置主键呢？又怎么把一个Innodb表的主键删掉呢？
    设置主键、删掉主键的sql语句如下。

    alter table 表名 drop primary key;
alter table 表名 add primary key(字段);

    如果在创建表时没有设置主键，又或者是我们把某张表的主键给删掉了，那么Innodb就会在表中添加一个rowid字段，这个字段会占用6个字节的空间，并且Innodb会把这个字段作为主键字段来使用。
    回到前面Mysql是用什么来标识一个数据行的问题，如果在一个Innodb表中已经设置了主键，那么Mysql就会用主键的值来标识每一个数据行，如果在一个Innodb表中不存在主键，那么Innodb就会向表中添加一个rowid字段，并且给这个字段填充具体的值，给rowid字段填充的值就是每个数据行的标识。所以在前面提到的数据行的位置信息指的就是每个数据行的标识。
    那么临时表中数据行的标识是什么呢？以临时内存表为例，我们可以认为临时内存表中的数据是保存在一个节点数组里的，所以不同数据行的下标是不同的，所以我们就可以使用数组的下标来作为数据行的标识。也就是说我们向sort_buffer中放进去的数据行的位置信息实际上就是保存临时内存表中数据的节点数组的下标，通过数组的下标我们自然能够找到数组中的每一个元素。
    总的来说，在执行order by rand()语句的过程中使用了临时表，并且临时表中数据行排序使用的是基于rowid的排序算法。
    前面提到的临时表实际上指的都是内存临时表，临时表是怎么分类的呢？如果临时表比较小，那么就会把临时表放在内存中，此时的临时表被称为内存临时表，如果临时表比较大，就会把临时表放到磁盘上，这时的临时表被称为磁盘临时表，那么这两种情况以什么为界限呢？取决于我们给tmp_table_size参数设置的值，如果临时表的大小超过了我们给tmp_table_size参数设置的值，那么就会把临时表放到磁盘上，如果临时表的大小小于我们给tmp_table_size参数设置的值，那么就会把临时表放在内存中，那么tmp_table_size参数的默认值是多少呢？tmp_table_size参数的默认值是16M。
    由于临时表的大小不是在创建表之前就可以确定的，取决于会有多少数据放到临时表中，所以Mysql总是先创建一个内存临时表，当临时表的大小超过tmp_table_size参数规定的值后，才会删除创建的内存临时表，再创建一个磁盘临时表。不同类型的临时表之间能直接相互转换吗？
    在创建磁盘临时表时默认使用的是Innodb存储引擎。
    我们可以怎么设置参数从而在执行order by rand()语句的过程中使用的是磁盘临时表而不是内存临时表？我们可以把tmp_table_size参数的值设置为1024，也就是说如果临时表的大小超过1MB，就会把临时表放到磁盘上，使用磁盘临时表。怎么把临时表放到磁盘上？再把sort_buffer的大小设置为32768，也就是32MB，再把max_length_for_sort_data设置成16，指的是sort_buffer中的数据行不能超过16个字节，如果紧跟在select后面的字段与排序字段总共占用了超过了16个字节的空间，那么就要采用基于rowid的排序算法来执行这个sql语句。如果紧跟在select后面的字段与排序字段总共占用的空间小于或者等于16个字节，那么就会采用完全排序算法来执行这个sql语句。
    在Mysql中怎么给参数指定值？使用set关键字就可以把指定参数设置为指定的值。

    set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;

    紧接着开启Mysql提供的optimizer_trace功能，每次开启optimizer_trace功能只会在，然后执行上面的查询语句，再然后查看optimizer_trace文件中的内容，
    由于我们把max_length_for_sort_data参数的值设置为了16，而word字段占用了64个字节的空间，所以我们能够看到optimizer_trace文件中的sort_mode字段的值是rowid，指的是这个查询语句采用了基于rowid的排序算法来执行。在sort_buffer中我们只会放进去两个字段的值，随机值字段与rowid字段。rowid字段是什么字段？当Innodb表中存在主键时，rowid字段指的是主键字段，当Innodb表中不存在主键时，rowid字段指的是Innodb存储引擎给表新增的作为数据行标识的字段。
    由于随机值字段的数据类型是double数据类型，而double数据类型会占用8个字节的空间，而rowid字段占用了6个字节的空间，为什么把rowid字段设计为占用6个字节的空间呢？那么sort_buffer中的一个数据行就需要占用14个字节的空间，在sort_buffer中我们会放进去10000个数据行，那么就需要140000个字节的空间，但是我们设置的sort_buffer的大小是32768字节，sort_buffer中不足以放下所有满足查询条件的数据，但是为什么number_of_tmp_files选项的值是0呢？在这个过程中没有借助磁盘上的临时文件来排序吗？
    也就是说即使在sort_buffer中不足以放下所有满足查询条件的数据，那么也不是一定会借助磁盘上的临时文件来排序的，我们还可以使用优先队列排序算法，在有限的内存空间上不借助磁盘上的空间对数据行进行排序。
    那么优先队列排序算法具体是什么样子呢？
    为什么在这里不使用借助磁盘上临时文件的归并排序算法，而是选用了优先队列排序算法呢？在这个查询语句的执行过程中，我们需要取出随机值最小的3个数据行中的rowid值，拿着rowid的值回到临时表中取出相应的单词。如果我们采用归并排序算法对sort_buffer中的数据行进行排序的话，那么就会给这10000个数据行按照随机值都排好顺序，但是实际上我们只需要知道随机值最小的3个数据行是哪3个，给剩下的9997个数据行排好顺序只会带来不必要的消耗。
    而使用优先队列排序算法就可以做到只找出sort_buffer中随机值最小的3个数据行，并且这3个数据行是按照随机值有序的。优先队列排序算法借助了堆数据结构，Mysql会先从sort_buffer中取出前3个数据行，并将每个数据行中的值封装成一个节点，然后根据节点中的随机值构造出一个堆，所以在这个堆中只有3个节点，接下来就是遍历sort_buffer中剩余的数据行，如果某个数据行的随机值比堆中最大的随机值小，就用这个数据行中的值替换掉堆中随机值最大的节点中的值，如果数据行的随机值比堆中最大的随机值大或者是相等，那么就跳过这个数据行，重复这个判断过程，直到将sort_buffer中剩余的数据行全部检查一遍。
    归并排序算法又是怎么样的一个执行过程呢？
    在optimizer_trace文件中我们还会看到一个叫做filesort_priority_queue_optimization的选项，在它的值中有一个键值对是chosen=true，这个键值对就表示在这个查询语句的执行过程中使用了优先队列排序算法，在使用优先队列排序算法找出最大或者最小的若干个数据行的过程中并不需要借助磁盘上的临时文件，在sort_buffer中足以放下在使用优先队列排序算法时构造出的堆，所以在optimizer_trace文件中我们看到的number_of_tmp_files选项的值是0。
    这个时候就会有这么一个疑问，上一篇文章中也有一个sql语句使用了limit关键字，那么为什么在执行那个sql语句的过程中没有使用优先队列排序算法呢？这条sql语句如下。

    select city,name,age from t where city='杭州' order by name limit 1000;

    这是因为limit关键字后面的数字比较大，是1000，那么我们就会构造出一个包含1000个节点的堆，在每个节点中包含了name字段的值和rowid字段的值，name字段会占用64个字节的空间，rowid字段会占用6个字节的空间，所以一个节点就会占用70个字节的空间，1000个节点就需要占用70000个字节的空间，而sort_buffer的大小在前面被我们设置为了32768，sort_buffer的大小不足以放下使用优先队列排序算法时构造出的堆，所以只能使用归并排序算法。
    从上面的执行过程我们可以看到，在执行过程中使用的不管是内存临时表，还是磁盘临时表，使用的排序算法不管是归并排序算法，还是优先队列排序算法，执行这个sql语句都需要经过很复杂的步骤。那么我们有没有更好的sql语句既能够实现相同的功能，执行的过程也会简单很多呢？在上面好像没有提到使用磁盘临时表的执行过程，而是详细介绍了使用优先队列排序算法的执行过程。
    如果我们只是从单词表中随机选出一个单词，我们可以怎么实现这个目标呢？
    我们可以先取出单词表中主键字段的最大值和最小值，用随机函数生成一个最小值和最大值之间的数，生成的数和最小值、最大值的关系式为X=(M-N)*rand()+N，然后取出单词表中主键字段的值不小于X的最小的数据行。具体的sql语句如下。

    select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

    这三条sql语句的总共执行时间也比一个order by rand()语句的执行时间短，因为这三条sql语句一共只扫描了很少的数据行。怎么获取主键字段id的最小值和最大值？主键索引树上的第一个叶子节点和最后一个叶子节点中保存的值就是主键字段id的最小值和最大值。而主键索引树上的第一个叶子节点和最后一个叶子节点我们是可以直接定位到的，怎么定位到的？遇到节点就走最左边，就能定位到第一个叶子节点，遇到节点就走最右边，就能定位到最后一个叶子节点。由于会从第一个叶子节点和最后一个叶子节点中取出数据行放进结果集中，所以在执行第一条sql语句时需要扫描2个数据行。第二条sql语句与表无关，只是进行代数运算，所以不会增加扫描行数，在第三条sql语句的执行过程中同样是直接定位到第一个满足查询条件的叶子节点上，又由于在sql语句中存在limit 1关键字，所以只取出了一个叶子节点中的数据行放进了结果集中，所以在执行第三条sql语句的过程中只扫描了1个数据行，所以执行了这三条sql语句总共扫描了3个数据行。
    但是这个思路有一个很严重的问题，那就是无法保证每个数据行被取到的概率是一样的，因为主键字段的值可能不是连续的，在主键字段的值中存在空洞，有空洞就会导致每个数据行被取到的概率不一样吗？假如说现在在单词表中有4个数据行，这4个数据行的id分别是1、2、4、5，那么按照上面的思路来取数据行的话，由于rand()生成的值是随机的，所以@X的值为1到5的概率是相等的，那么当@X的值为3或者4时都会取单词表中的id=4的数据行，也就是说，id=4的数据行被取到的概率是2/5，id=1,2,5的数据行被取到的概率是1/5。
    那么如果这4个数据行的id分别是1,2,40000,40001呢？那么就会出现在大多数时候取出的都是id=40000的数据行的现象。
    那么我们可以怎么做来保证每个数据行被取到的概率一样呢？
    我们可以按照下面的思路来取出单词表中的数据行。
    首先我们要获取单词表的总行数，然后使用Y=floor(Crand())的关系式获取到一个随机值，每个随机值被获取到的概率是一致的吗？并不是，虽然Crand()的值发生的概率是一致的，但是由于进行了一次取整运算，所以对随机值被获取的概率产生了一些影响，再使用limit Y,1从单词表中取出一个数据行。
    那么这个过程对应的sql语句是什么样的呢？

    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;

    为什么sql语句写的这么复杂呢？这是因为在limit关键字后面不能直接跟变量，所以使用了prepare和execute关键字来实现这个功能，
    这个思路的优点是什么？解决了第一个思路中明显存在的在表中取数据行的概率不一致的问题。为什么这样就解决了在表中取数据行概率不一致的问题？因为rand()函数的不同结果发生的概率是相等的，所以C*rand()的不同结果发生的概率也是相等的，虽然对等概率的值取floor()函数会导致取整后的值发生的概率不相等，但是当C足够大时我们可以近似认为Y取0~C-1之间的任何值的概率都是相等的，那么我们就有相同的概率取到1~C之间的任何一个数据行。limit Y,1是怎样的一个过程？Mysql会定位到主键索引树上的第一个叶子节点，依次取出前Y+1个叶子节点中的数据行放入到结果集中，然后丢掉前Y个数据行，把第Y+1个数据行发送给客户端，在这个过程中会扫描多少个数据行呢？在这个过程中将单词表中的Y+1个数据行放入了结果集中，所以在这个过程中扫描了Y+1行数据，在统计单词表的总行数时，需要对单词表进行全表扫描，所以在第一个sql语句的执行过程中扫描了C行，所以在这个思路中总共会扫描C+Y+1个数据行。相对于第一种思路，扫描了更多的数据行。虽然第二种思路扫描了更多的数据行，但是第二种思路消耗的资源还是远远比order by rand()语句的执行过程要少。
    那么如果我们按照第二种思路从单词表中随机取出三个单词该怎么做呢？
    首先还是取出整张表的总行数，把行数记为C，使用第二种思路中的方法分别获取三个随机值，Y1,Y2,Y3，再执行三个limit Y,1语句从单词表中取出三个数据行。对应的sql语句如下。

    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；