mysql select as 效率_[mysql] 随机查询 效率比较

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值