MySQL 中随机选择10条记录

mysql手册中存在rand()命令，能获取到随机行, 并使用limit 10 只采取其中几行。

SELECTidFROMuserORDERBYRAND()LIMIT10;

数据量小于1000行的时候，上面的 sql 执行的快。但是当数据大于10000行, 排序的开销就变得很重。上面的操作中，我们在排序完就把几乎所有的行都丢掉了。

只要我们有一个数字主键，我们可以有更好的方式去实现这个功能，不需要对所有数据进行排序。

在上面的例子中, 我们假设 id 从1开始, 并且在1和 id 的最大值之间是连续的。

通过应用程序解决问题

可以在应用程序中计算随机id, 简化整个计算。

SELECTMAX(id)FROMuser;##在应用程序中生成区间内的随机数：random-id  SELECTnameFROMuserWHEREid=<random-id>

由于MAX(id) == COUNT(id)，我们只是生成1和 max (id) 之间的随机数, 并将其传递到数据库中检索随机行。

第一个select语句是NO-OP，并一直在被优化。第二个是针对常量的 eq 速度也很快。

通过数据库解决问题

#生成一个随机ID>SELECTRAND()*MAX(id)FROMuser;+------------------+|RAND()*MAX(id)|+------------------+|689.37582507297|+------------------+#返回值是double，但是我们需要的是int>SELECTCEIL(RAND()*MAX(id))FROMuser;+-------------------------+|CEIL(RAND()*MAX(id))|+-------------------------+|1000000|+-------------------------+#返回值是int，分析性能>EXPLAIN  SELECTCEIL(RAND()*MAX(id))FROMrandom;+----+-------------+-------+-------+------+-------------+|id|select_type|table|type|rows|Extra|+----+-------------+-------+-------+------+-------------+|1|SIMPLE|random|index|1000000|Usingindex|+----+-------------+-------+-------+------+-------------+##全表扫描？由于使用MAX()函数了，导致优化丢失。>EXPLAIN  SELECTCEIL(RAND()*(SELECTMAX(id)FROMrandom));+----+-------------+-------+------+------+------------------------------+|id|select_type|table|type|rows|Extra|+----+-------------+-------+------+------+------------------------------+|1|PRIMARY|NULL|NULL|NULL|Notablesused||2|SUBQUERY|NULL|NULL|NULL|Selecttablesoptimizedaway|+----+-------------+-------+------+------+------------------------------+##子查询可以将性能损失挽回

通过上面的 sql 已经能够生成随机 id, 但如何获得行？

>EXPLAINSELECTnameFROMuserWHEREid=(SELECTCEIL(RAND()*  (SELECTMAX(id)  FROMuser));+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+|1|PRIMARY|user|ALL|NULL|NULL|NULL|NULL|1000000|Usingwhere||3|SUBQUERY|NULL|NULL|NULL|NULL|NULL|NULL|NULL|Selecttablesoptimizedaway|+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+>showwarnings;+-------+------+------------------------------------------+|Level|Code|Message|+-------+------+------------------------------------------+|Note|1249|Select2wasreducedduringoptimization|+-------+------+------------------------------------------+

上面的方法是最明显的, 但也是最错误的做法。原因是：where子查询中的select为外部select每一行都会执行。具体解释参考：sql语句嵌套查询性能低

要找一种方法，保证random-id只生成一次：

SELECTnameFROMuserJOIN  (SELECTCEIL(RAND()*  (SELECTMAX(id)  FROMuser))ASid)ASr2USING(id);+----+-------------+------------+--------+------+------------------------------+|id|select_type|table|type|rows|Extra|+----+-------------+------------+--------+------+------------------------------+|1|PRIMARY|<derived2>|system|1|||1|PRIMARY|user|const|1|||2|DERIVED|NULL|NULL|NULL|Notablesused||3|SUBQUERY|NULL|NULL|NULL|Selecttablesoptimizedaway|+----+-------------+------------+--------+------+------------------------------+

内部select生成一个常量临时表, join 只在单行上执行。没有使用排序，没有通过应用程序，查询的大多数部分都被优化了。

非连续数据

删除一些行，构造ID非连续的记录。

SELECTnameFROMrandomASr1JOIN  (SELECT(RAND()*  (SELECTMAX(id)  FROMrandom))ASid)  ASr2WHEREr1.id>=r2.idORDERBYr1.idASC  LIMIT1;+----+-------------+------------+--------+------+------------------------------+|id|select_type|table|type|rows|Extra|+----+-------------+------------+--------+------+------------------------------+|1|PRIMARY|<derived2>|system|1|||1|PRIMARY|r1|range|689|Usingwhere||2|DERIVED|NULL|NULL|NULL|Notablesused||3|SUBQUERY|NULL|NULL|NULL|Selecttablesoptimizedaway|+----+-------------+------------+--------+------+------------------------------+

join现在获取所有大于或等于我们随机值的ID，如果不能直接匹配则选择邻居。 但是一旦找到一行，就停止执行（LIMIT 1）。根据索引（ORDER BY id ASC）读取行。 当使用 >= 而不是a = 时，我们可以摆脱CEIL并以更少的工作获得相同的结果。

平等分配

当我们的ID分布不再相等时，我们选择的行也不是真正随机的。

>select*fromholes;+----+----------------------------------+----------+|id|name|accesses|+----+----------------------------------+----------+|1|d12b2551c6cb7d7a64e40221569a8571|107||2|f82ad6f29c9a680d7873d1bef822e3e9|50||4|9da1ed7dbbdcc6ec90d6cb139521f14a|132||8|677a196206d93cdf18c3744905b94f73|230||16|b7556d8ed40587a33dc5c449ae0345aa|481|+----+----------------------------------+----------+

RAND方法会生成9到15之类的ID，这些ID都会导致id 16被选为下一个更高的数字。

这个问题没有真正的解决方案，但是由于你的数据大多是不变的，你可以添加一个映射表，将行号映射到id：

>createtableholes_map(row_idintnotNULLprimarykey,random_idintnotnull);>SET@id=0;>INSERTINTOholes_mapSELECT@id:=@id+1,idFROMholes;>select*fromholes_map;+--------+-----------+|row_id|random_id|+--------+-----------+|1|1||2|2||3|4||4|8||5|16|+--------+-----------+

row_id现在再次是连续，我们可以再次运行随机查询

SELECTnameFROMholesJOIN(SELECTr1.random_idFROMholes_mapASr1JOIN(SELECT(RAND()*  (SELECTMAX(row_id)  FROMholes_map))ASrow_id)  ASr2WHEREr1.row_id>=r2.row_idORDERBYr1.row_idASC  LIMIT1)asrowsON(id=random_id);

1000次提取后，我们再次看到平均分布：

>select*fromholes;+----+----------------------------------+----------+|id|name|accesses|+----+----------------------------------+----------+|1|d12b2551c6cb7d7a64e40221569a8571|222||2|f82ad6f29c9a680d7873d1bef822e3e9|187||4|9da1ed7dbbdcc6ec90d6cb139521f14a|195||8|677a196206d93cdf18c3744905b94f73|207||16|b7556d8ed40587a33dc5c449ae0345aa|189|+----+----------------------------------+----------+

维护连续的表

DROPTABLEIFEXISTSr2;CREATETABLEr2(  idSERIAL,  nameVARCHAR(32)NOTNULLUNIQUE);DROPTABLEIFEXISTSr2_equi_dist;CREATETABLEr2_equi_dist(  idSERIAL,  r2_idbigintunsignedNOTNULLUNIQUE);

当我们在r2中更改某些内容时，我们希望r2_equi_dist也会更新。

DELIMITER$$DROPTRIGGERIFEXISTStai_r2$$CREATETRIGGERtai_r2AFTERINSERTONr2FOREACHROWBEGIN  DECLAREmBIGINTUNSIGNEDDEFAULT1;    SELECTMAX(id)+1FROMr2_equi_distINTOm;  SELECTIFNULL(m,1)INTOm;  INSERTINTOr2_equi_dist(id,r2_id)VALUES(m,NEW.id);END$$DELIMITER;DELETEFROMr2;INSERTINTOr2VALUES(NULL,MD5(RAND()));INSERTINTOr2VALUES(NULL,MD5(RAND()));INSERTINTOr2VALUES(NULL,MD5(RAND()));INSERTINTOr2VALUES(NULL,MD5(RAND()));SELECT*FROMr2;+----+----------------------------------+|id|name|+----+----------------------------------+|1|8b4cf277a3343cdefbe19aa4dabc40e1||2|a09a3959d68187ce48f4fe7e388926a9||3|4e1897cd6d326f8079108292376fa7d5||4|29a5e3ed838db497aa330878920ec01b|+----+----------------------------------+SELECT*FROMr2_equi_dist;+----+-------+|id|r2_id|+----+-------+|1|1||2|2||3|3||4|4|+----+-------+

INSERT非常简单，DELETE操作我们必须更新equi-dist-id以保持id的连续设置：

DELIMITER$$DROPTRIGGERIFEXISTStad_r2$$CREATETRIGGERtad_r2AFTERDELETEONr2FOREACHROWBEGIN  DELETEFROMr2_equi_distWHEREr2_id=OLD.id;  UPDATEr2_equi_distSETid=id-1WHEREr2_id>OLD.id;END$$DELIMITER;DELETEFROMr2WHEREid=2;SELECT*FROMr2;+----+----------------------------------+|id|name|+----+----------------------------------+|1|8b4cf277a3343cdefbe19aa4dabc40e1||3|4e1897cd6d326f8079108292376fa7d5||4|29a5e3ed838db497aa330878920ec01b|+----+----------------------------------+SELECT*FROMr2_equi_dist;+----+-------+|id|r2_id|+----+-------+|1|1||2|3||3|4|+----+-------+

update操作需要维护外键约束：

DELIMITER$$DROPTRIGGERIFEXISTStau_r2$$CREATETRIGGERtau_r2AFTERUPDATEONr2FOREACHROWBEGIN  UPDATEr2_equi_distSETr2_id=NEW.idWHEREr2_id=OLD.id;END$$DELIMITER;UPDATEr2SETid=25WHEREid=4;SELECT*FROMr2;+----+----------------------------------+|id|name|+----+----------------------------------+|1|8b4cf277a3343cdefbe19aa4dabc40e1||3|4e1897cd6d326f8079108292376fa7d5||25|29a5e3ed838db497aa330878920ec01b|+----+----------------------------------+SELECT*FROMr2_equi_dist;+----+-------+|id|r2_id|+----+-------+|1|1||2|3||3|25|+----+-------+

一次多行

如果要返回多行，您可以：

• 多次执行查询

• 编写执行查询的存储过程并将结果存储在临时表中

存储过程

存储过程为你了程序语言结构：

• 循环

• 控制结构

对于此任务，我们只需要一个循环：

ELIMITER$$DROPPROCEDUREIFEXISTSget_rands$$CREATEPROCEDUREget_rands(INcntINT)BEGIN  DROPTEMPORARYTABLEIFEXISTSrands;  CREATETEMPORARYTABLErands(rand_idINT);loop_me:LOOP  IFcnt<1THEN  LEAVEloop_me;  ENDIF;    INSERTINTOrandsSELECTr1.idFROMrandomASr1JOIN  (SELECT(RAND()*  (SELECTMAX(id)  FROMrandom))ASid)  ASr2WHEREr1.id>=r2.idORDERBYr1.idASC  LIMIT1;    SETcnt=cnt-1;  ENDLOOPloop_me;END$$DELIMITER;CALLget_rands(4);SELECT*FROMrands;+---------+|rand_id|+---------+|133716||702643||112066||452400|+---------+

性能

我们有3个不同的查询来解决我们的问题：

• Q1. ORDER BY RAND()

• Q2. RAND() * MAX(ID)

• Q3. RAND() * MAX(ID) + ORDER BY ID

1001.00010.000100.0001.000.000  Q10:00.718s0:02.092s0:18.684s2:59.081s58:20.000s  Q20:00.519s0:00.607s0:00.614s0:00.628s0:00.637s  Q30:00.570s0:00.607s0:00.614s0:00.628s0:00.637s

正如您所看到的那样，简单的ORDER BY RAND（）已经落后于表中仅100 行的优化查询。