SQL反模式学习笔记16 使用随机数排序
2014-10-15 10:06:48
目标：随机排序，使用高效的SQL语句查询获取随机数据样本。
反模式：使用RAND()随机函数
SELECT * FROM Employees AS e ORDER BY RAND() Limit 1
缺点：无法利用索引，每次选择的时候都不同且不可预测。进行全表遍历，性能极差。
如何识别反模式：当出现以下情况时，可能是反模式
1、在SQL中，返回一个随机行速度非常慢；
2、要获取所有的记录然后随机一个。要如何增加程序可使用的内存大小？
3、有些列出现的频率比别的列要高一些，这个随机算法不是很随机。
合理使用反模式：在数据量很小的时候，可以使用随机排序。
解决方案：没有具体的排序……
1、从1到最大值之间随机选择
select b1.*
from Bugs as b1
join (select ceiling(rand()*(select max(bugId) from Bugs)) as randId) as b2
on b1.bugId = b2.bugId
因为id可能是不连续的，所以可能有时候无法查询到结果。
2、选择下一个最大值
select b1.*
from Bugs as b1
join (select ceiling(rand()*(select max(bugId) from Bugs)) as randId) as b2
where b1.bugId >= b2.bugId --为了避免id不存在，我们找上面那条id的后一条数据。
order by b1.bugId limit 1;
当缝隙中是缝隙不是很大并且每个值都要被等概率选择的重要性不是很高时，可以考虑使用此方案。
3、索取所有的键值，随机选择一个，再使用这个随机选择的主键查找完整的记录。
4、使用偏移量选择随机行
MySQL、PostgreSQL、SQLite支持Limit关键字；
Oracle、MsSql Server使用Row_Number（）函数。
5、专有解决方案
每种数据库都提供专有的解决方案：
Sql Server2005以上版本增加了 TableSample()函数 ：SELECT * FROM Employees TABLESAMPLE(1 rows)；
Oracle使用类似的Sample()函数：
SELECT * FROM ( SELECT * FROM Employees SAMPLE(1)
order by Employees.Id)
where rownum = 1
结论：有些查询是无法优化的，换种方法试试看。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
