首页 > 代码库 > mysql 随机查询 效率比较
mysql 随机查询 效率比较
select primary_count as primaryCount, primary_score as primaryScore, junior_count as juniorCount, junior_score as juniorScore, senior_count as seniorCount, senoir_score as senoirScore, total_score as totalScore, pass_score as passScore from pd_paper pwhere p.is_valid = ‘1‘order by RAND() limit 1 分析:ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描。测试发现这样效率非常低。一个15万余条的库,查询5条数据,要8秒以上。 You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. 更高效的做法:查询max(id) * rand()来随机获取数据。 SELECT * FROM `table` AS t1 JOIN ( SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER 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` AS t1 JOIN ( SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) +(SELECT MIN(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1; #随机查询一套考卷定义SELECT p.primary_count as primaryCount, p.primary_score as primaryScore, p.junior_count as juniorCount, p.junior_score as juniorScore, p.senior_count as seniorCount, p.senoir_score as senoirScore, p.total_score as totalScore, p.pass_score as passScore FROM pd_paper AS pJOIN ( SELECT ROUND( RAND() *((SELECT MAX(id) FROM pd_paper)-(SELECT MIN(id) FROM pd_paper)) +(SELECT MIN(id) FROM pd_paper) ) AS id) AS p2 WHERE p.id >= p2.id ORDER BY p.id LIMIT 1; 最后在程序对这两个语句进行分别查询10次,前者花费时间 0.147433 秒后者花费时间 0.015130 秒看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。 附加一个复杂sql:按题目类型(三种)随机查询全部考题信息 SELECT * from ( select p.id as id, p.title as title, p.question as question, p.answer as answer, p.crt_time as crtTime from pd_problem p join (SELECT ROUND(RAND() * ((SELECT MAX(pp.id) FROM pd_problem pp)-(SELECT MIN(pp.id) FROM pd_problem pp)) +(SELECT MIN(pp.id) FROM pd_problem pp)) AS pid) AS p2 where p.id >= p2.pid and p.is_valid = ‘1‘ and p.paper_type = ‘1‘ and p.paper_class = ‘0‘ order by p.id limit 5 ) as t1 union all SELECT * from ( select p.id as id, p.title as title, p.question as question, p.answer as answer, p.crt_time as crtTime from pd_problem p join (SELECT ROUND(RAND() * ((SELECT MAX(pp.id) FROM pd_problem pp)-(SELECT MIN(pp.id) FROM pd_problem pp)) +(SELECT MIN(pp.id) FROM pd_problem pp)) AS pid) AS p2 where p.id >= p2.pid and p.is_valid = ‘1‘ and p.paper_type = ‘1‘ and p.paper_class = ‘1‘ order by p.id limit 5 ) as t2 union all SELECT * from ( select p.id as id, p.title as title, p.question as question, p.answer as answer, p.crt_time as crtTime from pd_problem p join (SELECT ROUND(RAND() * ((SELECT MAX(pp.id) FROM pd_problem pp)-(SELECT MIN(pp.id) FROM pd_problem pp)) +(SELECT MIN(pp.id) FROM pd_problem pp)) AS pid) AS p2 where p.id >= p2.pid and p.is_valid = ‘1‘ and p.paper_type = ‘1‘ and p.paper_class = ‘2‘ order by p.id limit 5 ) as t3
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。