学步园

原始的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.nid


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 
join (select round(rand() * (select max(id) from dd_poetry)) as nid from dd_poetry ) t2
on p.id = t2.nid

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