SQL 随机抽取多条数据/ORDER BY RAND()优化

最新推荐文章于 2024-04-24 15:26:53 发布

达希_

最新推荐文章于 2024-04-24 15:26:53 发布

阅读量6.3k

点赞数 1

分类专栏： MySQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/nibonnn/article/details/125295449

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

偶然打开以前的某个项目，发现代码里面有一个FIXME，写着此为临时方案，SQL存在性能隐患，需优化。这是一个在题库里随机抽题的SQL，采用的是ORDER BY RAND()，因为没有参与后续的迭代，今天把自己当时的思路记录一下。

主要还是因为网上关于这块的博文大多写的一样，没啥参考价值，所以才记录一下自己的想法，如有错误，恳请指正。

ORDER BY RAND() 为什么有性能问题

首先是RAND这个随机函数：我们都知道目前计算机世界里的随机，都是伪随机，所谓的随机数，都是按照一定的算法计算出来的。而这个计算过程其实对CPU是有一定占用的，尤其在随机数数量较大时，CPU的计算时间是很长的。

再有就是，ORDER BY RAND()在执行时会比普通的ORDER BY，多一个步骤，也就是会创建一个临时表（explain时可以看到Using temporary）。这个临时表会把查询出来的数据增加一列，也就是RAND列，把随机数的赋值进去。然后再生成sort_buffer中间表进行排序。

优化方式

个人认为，不同的业务场景对应不用的处理方式：

场景一：没有抽取条件限制，直接抽取

在业务代码里找到随机的id，以该id为起始抽取数据

int max = 数据表最大主键id;
int min = 数据表最小主键id;
int randId = (int)(Math.random() * (max - min)) + min;

SELECT * FROM table WHERE id >= randId LIMIT 数量;

直接使用SQL执行上述的思想

SELECT MAX(id), MIN(id) INTO @M, @N FROM table;
SET @R = FLOOR((@M - @N) * RAND() + @N);
SELECT * FROM table where id >= @R LIMIT 数量;

场景二：不允许抽取id连续的数据行

在业务代码层把需要的随机数据id都给算好，然后直接取

int max = 数据表最大主键id;
int min = 数据表最小主键id;
List<Integer> idList = new ArrayList<>(数量);
int i = 0;
while (i < 数量) {
    Integer randId = (int) (Math.random() * (max - min)) + min;
    if (!idList.contains(randId)) {
        idList.add(randId);
        i++;
    }
}

SELECT * FROM table where id IN idList;