引言

遇言开发过程中需要用到的方法，以及多种方法性能对比。

测试机器

小数据量

SELECT * FROM `table` order by RAND() limit 1   -- 0.016s

SELECT
    *
FROM
    table
WHERE
    id >= (
        SELECT
            floor(
                RAND() * (SELECT MAX(id) FROM table)
            )
    )
ORDER BY
    id
LIMIT 1;  -- 0.017s

SELECT r1.* FROM table AS r1 JOIN ( SELECT round( rand() * ( SELECT max( id ) FROM table )) AS id2 ) AS r2 WHERE r1.id >= r2.id2 ORDER BY r1.id ASC LIMIT 1; -- 0.016s

大数据量

SELECT * FROM `table` order by RAND() limit 1   -- 1.681s

SELECT
    *
FROM
    table
WHERE
    id >= (
        SELECT
            floor(
                RAND() * (SELECT MAX(id) FROM table)
            )
    )
ORDER BY
    id
LIMIT 1;  -- 0.020s

SELECT r1.* FROM table AS r1 JOIN ( SELECT round( rand() * ( SELECT max( id ) FROM table )) AS id2 ) AS r2 WHERE r1.id >= r2.id2 ORDER BY r1.id ASC LIMIT 1; -- 0.018s

结论

一共三种语句，后两种效果最佳。