准备测试用例

CREATE TABLE `tb_orders_10w` (
  `order_id` bigint(20) NOT NULL COMMENT '订单自增id',
  `order_no` bigint(20) NOT NULL COMMENT '外部订单号',
  `uid` bigint(20) NOT NULL COMMENT '所属用户id',
  `sender_name` varchar(64) DEFAULT '' COMMENT '发件人',
  `sender_mobile` varchar(20) DEFAULT NULL COMMENT '发件人手机',
  `sender_province` varchar(10) DEFAULT '' COMMENT '发件人省',
  `sender_city` varchar(20) DEFAULT '' COMMENT '发件人市',
  `sender_district` varchar(20) DEFAULT '' COMMENT '发件人区',
  `sender_address` varchar(255) DEFAULT '' COMMENT '发件人详细地址',
  `receiver_name` varchar(64) NOT NULL DEFAULT '' COMMENT '收件人名字',
  `receiver_mobile` varchar(20) DEFAULT '' COMMENT '收件人手机',
  `receiver_province` varchar(10) NOT NULL DEFAULT '' COMMENT '收件人省',
  `receiver_city` varchar(20) NOT NULL DEFAULT '' COMMENT '收件人市',
  `receiver_district` varchar(20) NOT NULL DEFAULT '' COMMENT '收件人区',
  `receiver_address` varchar(255) NOT NULL DEFAULT '' COMMENT '收件人详细地址',
  `status` tinyint(1) DEFAULT '0' COMMENT '订单状态:\r\n0未发货,1已取消,\r\n2已发货(有单号),3已完成(有单号)',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';

脚本插入10万条数据。

mysql> SELECT count(*) as cnt FROM tb_orders_10w;
+--------+
| cnt    |
+--------+
| 100000 |
+--------+
1 row in set (0.03 sec)

mysql> 

需求是要随机返回10个订单的 sender_name 。

开启慢查询日志便于分析:

slow_query_log = ON
slow_query_log_file = /data/mysql/slow.log
long_query_time = 0
log_queries_not_using_indexes = on

1. 使用 rand() 函数

mysql> SELECT sender_name FROM tb_orders_10w ORDER BY rand() LIMIT 10;
+-------------+
| sender_name |
+-------------+
| 孙蓓        |
| 小爽        |
| 李杨        |
| 赵彭丽      |
| 黄日华      |
| 小从        |
| 33          |
| 小熊        |
| 温小岚      |
| 文核轩      |
+-------------+
10 rows in set (0.21 sec)

就是随机排序,取前三个。

通过explain分析:

mysql> EXPLAIN SELECT sender_name FROM tb_orders_10w ORDER BY rand() LIMIT 10;
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|  1 | SIMPLE      | tb_orders_10w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 98397 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set, 1 warning (0.03 sec)

发现 Using temporary; Using filesort 。 说明用到了临时表,并且在临时表上做排序。

通过 optimizer_trace 查看执行过程。

mysql> SET optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT sender_name FROM tb_orders_10w ORDER BY rand() LIMIT 10;
+----------------------+
| sender_name          |
+----------------------+
| 翠                   |
| 小小莉               |
| 张先生               |
| 麻豆家               |
| 不流行户外           |
| 聂红                 |
| 刀锋果园             |
| 莫小千               |
| 王丽娜    |
| 郑乐洁               |
+----------------------+
10 rows in set (0.06 sec)

mysql> SELECT trace FROM `information_schema`.`OPTIMIZER_TRACE`\G

……………………

{
    "creating_tmp_table":{
        "tmp_table_info":{
            "in_plan_at_position":1,
            "columns":2,
            "row_length":203,
            "key_length":0,
            "unique_constraint":false,
            "makes_grouped_rows":false,
            "cannot_insert_duplicates":false,
            "location":"TempTable"
        }
    }
}

……………………

通过trace看到执行用到了内存临时表。

结合MySQL order by 的相关算法, 对于 InnoDB 表来说, 执行全字段排序会减少磁盘访问, 因此会被优先选择,但是对于内存临时表, 回表过程只是简单地根据数据行位置, 直接访问内存得到数据, 根本不会导致多访问磁盘的问题。 优化器没有了这一层顾虑, 那么它会优先考虑的, 就是用于排序的行越小越好, 所以, MySQL 这时就会选择 rowid 排序。

这样的话这条语句的执行流程是这样的:

  1. 创建一个临时表。 使用 memory 引擎, 表里有两个字段, 第一个字段是 double 类型R, 记录 rand() 的值, 第二个字段varchar(64),先叫做W, 用来记录 sender_name, 这个表没有建索引;
  2. 从tb_orders 表中, 按住键顺序取出所有的 sender_name , 然后对于每一个 sender_name, 调用 rand() 函数生成一个 0-1之间的随机数, 并把这两个字段分别填入临时表的W和R中, 到此,扫描行数是 100000;
  3. 接下来对没有缩印的临时内存表按照字段R排序;
  4. 初始化 sort_buffer 。 sort_buffer 中有两个字段,一个是double类型, 一个是整型;
  5. 从临时内存表中一行一行地取出R值和位置信息(类似于InnoDB表的rowid) , 分别存入sort_buffer 的两个字段里。 这个过程需要对内存临时表做全表扫描,此时扫描行数 100000*2;
  6. 在 sort_buffer 中根据R的值进行排序。 这个过程没有涉及到表操作,不会增加扫描行数;
  7. 排序完成后,取出前10个结果的位置信息,依次到内存临时表中取出 sender_name 值, 返回客户端。这个过程中,访问了表的10行数据, 总扫描行数变成了 100000*2 +10 = 200010;

接下来, 我们通过慢查询日志来验证以上分析:

[root@localhost ~]# tail -5 /data/mysql/slow.log 
# Time: 2019-05-21T03:10:22.355830Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 0.066898  Lock_time: 0.000103 Rows_sent: 10  Rows_examined: 200010
SET timestamp=1558408222;
SELECT sender_name FROM tb_orders_10w ORDER BY rand() LIMIT 10;

Rows_examined: 200010 表示这个语句执行过程中扫描了200010行。

所以, order by rand() 使用了内存临时表, 内存临时表排序时使用了 rowid 排序方法。

另外, 不管排序用到了 rowid排序算法、优先队列排序算法、归并排序算法, order by rand() 都需要扫描大量的行, 因此排序过程的资源消耗也很大。

2. 使用 max(id) & min(id) & rand()

将上续问题分解成 随机获取一个 sender_name * 10 。 那么就可以使用这样的思路:

  1. 取表的主键id的最大值M和最小值N;
  2. 生成一个最小和最大之间的随机数 X = (M-N)*rand() + N;
  3. 取 >= X 的第一个主键id所在的 sender_name 。
mysql> select max(order_id),min(order_id) into @M,@N from tb_orders_10w;
Query OK, 1 row affected (0.00 sec)

mysql> set @X= floor((@M-@N+1)*rand() + @N);
Query OK, 0 rows affected (0.00 sec)

mysql> select sender_name from tb_orders_10w where order_id >= @X limit 1;
+-------------+
| sender_name |
+-------------+
| 邱杰        |
+-------------+
1 row in set (0.00 sec)

max(order_id),min(order_id) 不需要扫描索引, 而最后的 select sender_name from tb_orders_10w where order_id >= @X limit 1; 也可以利用主键索引快速定位, 可以认为实际扫描行数是1行,10次就是10行。

这种实现方式, 可能会产生重复值, 如果生成的随机数有重复, 需要在编码时做对应的去重重新生成的处理。

但除了这个问题之外, 还有一个问题需要注意。 就是当主键并不连续的时候, 当主键出现了断层, 比如
order_id = 2001,2002,2010,2011, 也就是 2001和2002之间间隔为1, 2002和2010之间间隔是8, 所以2010命中的概率要高于2001。 在一些应用场景中, 显然这是不合理的。 不过要求不严格的场景可以这么去做, 因为性能最优。

3. 使用 count() & rand()

为了得到严格的随机结果, 可以先取得表的行数 C = count(); 然后 使用 floor(C rand()) 得到一个随机数Y ; 最后再使用 LIMIT Y,1 取得一行记录。

mysql> select count(*) as C from tb_orders_10w;
+--------+
| C      |
+--------+
| 100000 |
+--------+
1 row in set (0.02 sec)

mysql> select floor(10000 * rand()) as Y;
+------+
| Y    |
+------+
| 3063 |
+------+
1 row in set (0.00 sec)

mysql> select sender_name from tb_orders_10w limit 3063,1; 
+-------------+
| sender_name |
+-------------+
| 周安        |
+-------------+

这个方法解决了主键分配不均的情况上一个算法存在bug的问题,
其中 count(*) 还可以用到索引覆盖,

mysql> EXPLAIN select count(*) as cnt from  tb_orders_10w; 
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tb_orders_10w | NULL       | index | NULL          | PRIMARY | 8       | NULL | 98397 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

同时扫描行数也变成了 C+Y1+Y2+……+Y10 +10, 如果随机到的Y的值很大, 同样也会扫描大量的行。

之所以后面要+10, 是因为 limit Y,1 需要扫描的行数是 Y+1行 :

# Time: 2019-05-21T06:39:46.552685Z
# User@Host: root[root] @ localhost []  Id:    16
# Query_time: 0.001562  Lock_time: 0.000119 Rows_sent: 1  Rows_examined: 4087
SET timestamp=1558420786;
select sender_name from tb_orders_10w limit 4086,1;

针对这种算法, 还可以做以下的优化:
原逻辑是需要 10 次 limit。如果针对这10个Y, 求出最小值和最大值, 然后将10次查询更改为

mysql> select floor(10000 * rand()) as Y1,floor(10000 * rand()) as Y2,floor(10000 * rand()) as Y3,floor(10000 * rand()) as Y4,floor(10000 * rand()) as Y5,floor(10000 * rand()) as Y6,floor(10000 * rand()) as Y7,floor(10000 * rand()) as Y8,floor(10000 * rand()) as Y9,floor(10000 * rand()) as Y10;
+------+------+------+------+------+------+----+------+------+------+
| Y1   | Y2   | Y3   | Y4   | Y5   | Y6   | Y7 | Y8   | Y9   | Y10  |
+------+------+------+------+------+------+----+------+------+------+
| 6647 | 5106 | 5587 | 2619 | 6334 | 3812 | 59 | 8861 | 4127 | 4052 |
+------+------+------+------+------+------+----+------+------+------+
1 row in set (0.00 sec)

//算得
Ymin = 59;
Ymax = 8861;

mysql> SELECT sender_name from tb_orders_10w limit 59,8802;

//然后通过代码,对数据集中的Y1 - Y10 行取数据。

这样扫描行数变成 C + Ymax, 但是向客户端返回的数据量变大了。

以上几种思路,方式1逻辑最简单,在数据量小的时候可以用, 方式二性能最优,但是某些场景存在bug, 方式三比较中庸,推荐使用。 不过实际开发过程中,还是需要根据具体应用场景去选择。

参考资料:

  1. MySQL实战四十五讲:如何正确地显示随机消息??

标签: none

添加新评论