|
偶然打开以前的某个项目，发现代码里面有一个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;
场景三：需要根据不同的数据类型进行抽取
举个例子，从题库抽题，需要抽判断题10题，单选题10题，多选题10题
做法其实和上述的方法一样，只是在设计数据库时，做一些调整。
比如可以将主键设计得不连续，如按照数据的类型进行编号，判断题以101开头，单选题102，多选题103。
当然也可以把主键设置为联合主键，用smallint(类型)+int(编号)的方式。
|