mysql 随机排列,mysql随机排序

灰太狼 2023-01-14 04:53 93阅读 0赞

需求：首页热门栏目需要随机显示几条信息

方案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