解决Mysql坑爹的随机函数rand()

来源：互联网发布：光环国际大数据开发编辑：程序博客网时间：2024/05/21 10:15

原始的sql语句是：

select content poetryDesc,author poetryAnthor,l.backImg labelImg,p.id poetryId,p.fullContent flag from dd_poetry p join dd_label l on l.id = p.archaics order by rand()

直接随机在数据量过大的时候，导致数据库服务器cpu瞬间100%

优化之后的sql：

-- SELECT id FROM table t1 JOIN (SELECT round(RAND() * (SELECT MAX(id) FROM table)) AS nid FROM table LIMIT n) t2 ON t1.id = t2.nid;-- select * from dd_poetry t1 join (select round(rand() * (select max(id) from dd_poetry)) as nid from dd_poetry ) t2 on t1.id = t2.nidselect content poetryDesc,author poetryAnthor,l.backImg labelImg,p.id poetryId,p.fullContent flag from dd_poetry p join dd_label l on l.id = p.archaics join (select round(rand() * (select max(id) from dd_poetry)) as nid from dd_poetry ) t2on p.id = t2.nid

感谢：http://imysql.cn/2014/07/04/mysql-optimization-case-rand-optimize.shtml

0 0