优化Mysql随机取出N条记录

发表于   |   分类于   |  

今天看到一篇很好的文章 优化Mysql随机取出N条记录。 有时候会遇到 比如从一张表中随机取出N条 不重复 记录的需求，然后我以前都是上去就是这样 SELECT * FROM table ORDER BY RAND() LIMIT 1 今天发现这个有个性能漏洞 当数据量大的时候 会吓死你 下面我准备了一张3000W数据的表 就2个字段 来模拟下优化

先来分析下需求 一般这种需求有两种实现方式

使用程序处理

当数据量很大的时候 其实可以通过随机数在程序中随机生成N个ID 然后一条SQL select * from uuid_test where id in (100000,232323,23232322,23232366); 直接获取到结果

看下他的性能 使用 explain extended sql, extended 参数可以显示更详细的信息 不加也可以，如果后面还有 show warnings 的话还可以显示Mysql优化的SQL过程 虽然不一定是最终执行的优化SQL

explain

使用了 using where 但是 预计遍历的 row=4 很少 性能还凑合 不算太差

使用Mysql处理

如果使用Mysql处理的话 又会有几种情况需要区分。不过在此之前先来看下我经常写的那个SQL的性能

explain select * from uuid_test order by RAND() limit 1; 你不要尝试直接查询 我试了下 查询近2分钟 直接用 explain 来分析

explain

可以看到这条SQL 使用了索引 使用了临时表 使用了文件排序 并且是全表扫描 3000多W行。 下面讲下优化

如果ID是连续分布的

如果ID属性在数据库中是连续分布的 可以使用 Max() + RAND() 来获取随机ID

explain select CEIL(RAND() * MAX(id)) from uuid_test;

上面因为 RAND() * MAX(id) 的结果是一个小数 可以通过 CEIL 或者 FLOOR 来取整

explain

因为是主键 使用了一个索引 虽然这样会加速查询 但它同时还是全表扫描 我测了下基本要 6S 左右 还有很大的优化空间

explain select CEIL(RAND() * (select MAX(id) from uuid_test));

explain

使用了子查询后 性能回到了等同于 单查询 max 的程度 看上面分析 分别是没有使用表 和 最优化查询 然后测试了下 执行时间 0.005S 比上面差距了 1000 倍… （不知道有没有算错…）

经过上面已经拿到了 ID 下一步就是获取对应的记录了 用了个常规查询 直接 id = x 然后又死了

explain select * from uuid_test where id = (select CEIL(RAND() * (select MAX(id) from uuid_test)));

性能如下

explain

虽然子查询中已经很快了 但是外面的 where 导致了使用where使用索引 并且全表扫描。经过测试 查询时间为 10S 左右

可以通过 show warnings; 来获取警告信息 顺便说下 show warnings; 是什么 根据官方文档说明

是一个诊断语句，显示有关在当前会话中执行语句所产生的条件（错误，警告和注释）的信息
在EXPLAIN之后也使用SHOW WARNINGS来显示EXPLAIN生成的扩展信息

explain

必须换一种方法确保随机ID只被生成一次

explain SELECT * FROM uuid_test JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM uuid_test)) AS id ) AS r2 USING (id);

explain

SQL换成这样后只选择了一行 没有排序没有应用 查询被大部分优化

如果ID是不连续的

考虑数据库ID是不连贯的情况，可以根据随机的ID 然后取出比它或者小的数据 然后 Limit 限制返回的条数

explain SELECT * FROM uuid_test as r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM uuid_test)) AS id ) AS r2 where r1.id >= r2.id limit 10;

explain

这条SQL的性能和上面基本差不多 关键在于外面的where查询上 有可能会遍历半个表 因为我又3000多W数据 它预计的差不多1500W的样子 即使这样 他的速度也是很快

不过他取出来的结果是在那个ID上或下 连续的10条记录…好像没啥办法优化成完全不规则随机

完全随机N条

如果想完全随机N条记录的话 数据量很大的时候还是建议在程序里随机记录ID吧

或者建立一个映射表把不连贯的ID都存进去变成连贯

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 创建映射表
create table uuid_holes_map ( row_id bigint not NULL primary key, random_id int not null);
SET @id = 0;
INSERT INTO uuid_holes_map SELECT @id := @id + 1, id FROM uuid_test;
select * from uuid_holes_map;
# 最后执行SQL如下 limit 部分是限制返回的条数
SELECT * FROM uuid_test
  JOIN (SELECT r1.random_id
         FROM uuid_holes_map AS r1
         JOIN (SELECT (RAND() *
                      (SELECT MAX(row_id)
                         FROM uuid_holes_map)) AS row_id)
               AS r2
        WHERE r1.row_id >= r2.row_id
        ORDER BY r1.row_id ASC
        LIMIT 10) as rows ON (id = random_id);

差不多就这样了

原文地址

http://jan.kneschke.de/projects/mysql/order-by-rand/ 英文

http://shiningray.cn/order-by-rand.html 中文

-------------The End-------------