selectprimary_countasprimaryCount,
primary_scoreasprimaryScore,
junior_countasjuniorCount,
junior_scoreasjuniorScore,
senior_countasseniorCount,
senoir_scoreassenoirScore,
total_scoreastotalScore,
pass_scoreaspassScorefrompd_paper pwhere p.is_valid = '1'
order by RAND() limit 1分析:ORDERBY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描。
测试发现这样效率非常低。一个15万余条的库,查询5条数据,要8秒以上。
You cannotuse a column with RAND() values in an ORDER BY clause, because ORDER BYwould evaluate thecolumnmultiple times.
更高效的做法:查询max(id)* rand()来随机获取数据。SELECT *
FROM `table` ASt1JOIN(SELECT ROUND(RAND()* (SELECT MAX(id) FROM `table`)) ASid
)ASt2WHERE t1.id >=t2.idORDER BY t1.id ASC LIMIT 5;
但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。
即便如此也值得,因为15万条的表,查询只需要0.01秒不到。
采用join 语法 可以实现真正的随机。SELECT *
FROM `table`WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )ORDER BY id LIMIT 1;
把语句完善一下,加上MIN(id)的判断。
如果没有加上MIN(id)的判断,结果有一半的时间总是查询到表中的前面几行。
完整的语句:
①where子句SELECT * FROM `table`WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))ORDER BY id LIMIT 1;
②join
SELECT *
FROM `table` ASt1JOIN(SELECT ROUND(RAND()* ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) ASt2WHERE t1.id >=t2.idORDER BY t1.id LIMIT 1;
#随机查询一套考卷定义SELECTp.primary_countasprimaryCount,
p.primary_scoreasprimaryScore,
p.junior_countasjuniorCount,
p.junior_scoreasjuniorScore,
p.senior_countasseniorCount,
p.senoir_scoreassenoirScore,
p.total_scoreastotalScore,
p.pass_scoreaspassScoreFROMpd_paperASpJOIN(SELECT ROUND(RAND()*((SELECT MAX(id) FROM pd_paper)-(SELECT MIN(id) FROMpd_paper))+(SELECT MIN(id) FROMpd_paper)
)ASid
)ASp2WHERE p.id >=p2.idORDER BY p.id LIMIT 1;
最后在程序对这两个语句进行分别查询10次,
前者花费时间0.147433秒
后者花费时间0.015130秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。
附加一个复杂sql:按题目类型(三种)随机查询全部考题信息SELECT * from(selectp.idasid,
p.titleastitle,
p.questionasquestion,
p.answerasanswer,
p.crt_timeascrtTimefrompd_problem pjoin(SELECT ROUND(RAND()* ((SELECT MAX(pp.id) FROM pd_problem pp)-(SELECT MIN(pp.id) FROMpd_problem pp))+(SELECT MIN(pp.id) FROM pd_problem pp)) AS pid) ASp2where p.id >=p2.pidand p.is_valid = '1'
and p.paper_type = '1'
and p.paper_class = '0'
order by p.id limit 5)ast1union all
SELECT * from(selectp.idasid,
p.titleastitle,
p.questionasquestion,
p.answerasanswer,
p.crt_timeascrtTimefrompd_problem pjoin(SELECT ROUND(RAND()* ((SELECT MAX(pp.id) FROM pd_problem pp)-(SELECT MIN(pp.id) FROMpd_problem pp))+(SELECT MIN(pp.id) FROM pd_problem pp)) AS pid) ASp2where p.id >=p2.pidand p.is_valid = '1'
and p.paper_type = '1'
and p.paper_class = '1'
order by p.id limit 5)ast2union all
SELECT * from(selectp.idasid,
p.titleastitle,
p.questionasquestion,
p.answerasanswer,
p.crt_timeascrtTimefrompd_problem pjoin(SELECT ROUND(RAND()* ((SELECT MAX(pp.id) FROM pd_problem pp)-(SELECT MIN(pp.id) FROMpd_problem pp))+(SELECT MIN(pp.id) FROM pd_problem pp)) AS pid) ASp2where p.id >=p2.pidand p.is_valid = '1'
and p.paper_type = '1'
and p.paper_class = '2'
order by p.id limit 5)as t3