参考地址 mysql随机排序
需求：首页热门栏目需要随机显示几条信息
方案1：order by rand()，全表扫描
SELECT SQL_NO_CACHE * FROM `house_rent` order by rand() limit 10
(共 1 行, 查询花费 0.1069秒)
explain SELECT * FROM `house_rent` order by rand() limit 1
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE house_rent ALL NULL NULL NULL NULL 1009 Using temporary; Using filesort
方案2：拆分两个sql语句，第一个先随机取得id，可以用到index索引，第二个根据这些id直接定位。总用时0.0021，单需要两次请求
SELECT SQL_NO_CACHE id FROM `house_rent` order by rand() limit 10
(共 1 行, 查询花费 0.0012 秒)
explain SELECT id FROM `house_rent` order by rand() limit 10
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE house_rent index NULL uid 5 NULL 1690 Using index; Using temporary; Using filesort
SELECT SQL_NO_CACHE * FROM `house_rent` where id in (298,106,533,545,2,446,1367,1509,759,499)
(共 10 行, 查询花费 0.0009 秒)
方案3：将方案2两次请求用子查询合并为一次，查询时间比方案2高5倍，可见效果并不好，为了满足mysql语法生成了2张临时表。但是这种子查询并没有引发了笛卡尔积
SELECT SQL_NO_CACHE * FROM `house_rent` where id in ( select id from (SELECT id FROM `house_rent` order by rand() limit 10 ) as a )
(共 10 行, 查询花费 0.0110 秒)
SELECT SQL_NO_CACHE * FROM `house_rent` where id in ( select id from (SELECT id FROM `house_rent` order by rand() limit 50 ) as a )
(共 50 行, 查询花费 0.0071 秒)
方案4：利用子查询先计算一个随机点，然后再取小于该随机点的n条数据，因为取值是连贯的，所以速度比较快。而且子查询语句是纯数学计算，也不会有临时表影响。此方案虽然结果是连续的几个值，但每次获取的内容不一样，可以满足需求
select SQL_NO_CACHE * from house_rent where status=1 and cid=4 and (select ceil(rand()*(select MAX( id ) from house_rent)))<=id limit 10
(共 10 行, 查询花费 0.0011 秒)
explain select SQL_NO_CACHE * from house_rent where status=1 and cid=4 and (select ceil(rand()*(select MAX( id ) from house_rent)))<=id limit 10
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY house_rent ref status status 2 const 1478 Using where
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away