“orderby”是怎么工作的

例如：

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

全字段排序：

MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

通常情况下，这个语句执行流程如下所示 ：

  1. 初始化sort_buffer，确定放入name、city、age这三个字段；
  2. 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X；
  3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
  4. 从索引city取下一个记录的主键id；
  5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；
  6. 对sort_buffer中的数据按照字段name做快速排序；
  7. 按照排序结果取前1000行返回给客户端。
image-20230311151255046

图中“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小：

  • 如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。
  • 但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

rowid排序:

我来修改一个参数，让MySQL采用这种算法。

SET max_length_for_sort_data = 16;它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。

新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。

但这时，排序的结果就因为少了city和age字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

  1. 初始化sort_buffer，确定放入两个字段，即name和id；
  2. 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X；
  3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
  4. 从索引city取下一个记录的主键id；
  5. 重复步骤3、4直到不满足city=’杭州’条件为止，也就是图中的ID_Y；
  6. 对sort_buffer中的数据按照字段name进行排序；
  7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

两者区别：

  • 如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

  • 如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

利用索引自然排序

我们可以在这个市民表上创建一个city和name的联合索引，对应的SQL语句是：alter table t add index city_user(city, name);

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足city=’杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要city的值是杭州，name的值就一定是有序的。

这样整个查询过程的流程就变成了：

  1. 从索引(city,name)找到第一个满足city=’杭州’条件的主键id；
  2. 到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
  3. 从索引(city,name)取下一个记录主键id；
  4. 重复步骤2、3，直到查到第1000条记录，或者是不满足city=’杭州’条件时循环结束。

这里还可以进一步优化，建立city、name和age的联合索引，就还可以减少回表的次数

如何正确地显示随机消息

使用order by rand()

内存临时表

首先，你会想到用order by rand()来实现这个逻辑。

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

order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

磁盘临时表

那么，是不是所有的临时表都是内存表呢？

其实不是的。tmp_table_size这个配置限制了内存临时表的大小，默认值是16M。如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是InnoDB，是由参数internal_tmp_disk_storage_engine控制的。

MySQL 5.6版本引入的一个新的排序算法，即：优先队列排序算法。（也就是维护一个堆）

接下来，我们就看看为什么没有使用临时文件的算法，也就是归并排序算法，而是采用了优先队列排序算法。

其实，我们现在的SQL语句，只需要取R值最小的3个rowid。但是，如果使用归并排序算法的话，虽然最终也能得到前3个值，但是这个算法结束后，已经将10000行数据都排好序了。

随机排序方法

方法1：

思路上是这样的：

  1. 取得这个表的主键id的最大值M和最小值N;
  2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
  3. 取不小于X的第一个ID的行。
1
2
3
mysql> 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;

这个方法效率很高，因为取max(id)和min(id)都是不需要扫描索引的，而第三步的select也可以用索引快速定位，可以认为就只扫描了3行。但实际上，这个算法本身并不严格满足题目的随机要求，因为ID中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。

方法2：

思路上是这样的：

  1. 取得整个表的行数，并记为C。
  2. 取得 Y = floor(C * rand())。 floor函数在这里的作用，就是取整数部分。
  3. 再用limit Y,1 取得一行。
1
2
3
4
5
6
mysql> 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;

这个随机算法解决了明显的概率不均匀问题。

但是MySQL处理limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前Y个，然后把下一个记录作为返回结果，因此这一步需要扫描Y+1行。再加上，第一步扫描的C行，总共需要扫描C+Y+1行，执行代价比随机算法1的代价要高。

如果取三个值：

1
2
3
4
5
6
7
mysql> 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；