SQL反模式学习笔记16 使用随机数排序

目标：随机排序，使用高效的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

                                             

结论：有些查询是无法优化的，换种方法试试看。

 

 

 

SQL反模式，系列学习汇总

1、SQL反模式学习笔记1 开篇

2、SQL反模式学习笔记2 乱穿马路

3、SQL反模式学习笔记3 单纯的树

4、SQL反模式学习笔记4 建立主键规范【需要ID】

5、SQL反模式学习笔记5 外键约束【不用钥匙的入口】

6、SQL反模式学习笔记6 支持可变属性【实体-属性-值】 

7、SQL反模式学习笔记7 多态关联

8、SQL反模式学习笔记8 多列属性

9、SQL反模式学习笔记9 元数据分裂 

10、SQL反模式学习笔记10 取整错误

11、SQL反模式学习笔记11 限定列的有效值

12、SQL反模式学习笔记12 存储图片或其他多媒体大文件

13、SQL反模式学习笔记13 使用索引

14、SQL反模式学习笔记14 关于Null值的使用

15、SQL反模式学习笔记15 分组

16、SQL反模式学习笔记16 使用随机数排序

17、SQL反模式学习笔记17 全文搜索

18、SQL反模式学习笔记18 减少SQL查询数据，避免使用一条SQL语句解决复杂问题

19、SQL反模式学习笔记19 使用*号，隐式的列 

20、SQL反模式学习笔记20 明文密码

21、SQL反模式学习笔记21 SQL注入

22、SQL反模式学习笔记22 伪键洁癖，整理数据