为IT服务 [龙飞的博客]

随机取一条数据的一般来讲用SELECT * FROM `table` ORDER BY RAND() LIMIT 1是最方便的，但是order by rand()有个致命的缺点，就是表的索引完全没有作用，在高负载和表数据量大的情况下很容易把mysql拖死。最近在优化论坛无心宠物插件的时候一直卡在了order by rand()。

无心宠物在取怪的时候用了一条order by rand()来选取打怪的怪物。表的数据量不大，但是负载很高，在优化前mysql并发最低值不低于4000经常在5000-6000徘徊。在对sql语句：
select * from {$tablepre}wxnpcdata where (mapid=’$mapid’ and pktime<”.($timestamp-4).”)  or mapid=0 order by rand() limit 1

修改为

SELECT * FROM cdb_wxnpcdata AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(npcid) FROM cdb_wxnpcdata where (mapid=’$mapid’ and pktime<”.($timestamp-4).”)  or mapid=0)-(SELECT MIN(npcid) FROM cdb_wxnpcdata where (mapid=’$mapid’ and pktime<”.($timestamp-4).”)  or mapid=0))+(SELECT MIN(npcid) FROM cdb_wxnpcdata where (mapid=’$mapid’ and pktime<”.($timestamp-4).”)  or mapid=0)) AS id) AS t2 WHERE t1.npcid >= t2.id and (mapid=’$mapid’ and pktime<”.($timestamp-4).”)  or mapid=0 ORDER BY t1.npcid LIMIT 1;

之后mysql负载立刻降到1800左右徘徊，最高2000出头。效果立杆见影

对应SELECT * FROM `table` ORDER BY RAND() LIMIT 1的修改方法是

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;