Mysql 随机查询10条数据效率最快的查询方法

最新推荐文章于 2024-01-23 14:18:50 发布

weixin_30951231

最新推荐文章于 2024-01-23 14:18:50 发布

阅读量589

点赞数 2

文章标签：数据库

原文链接：http://www.cnblogs.com/wanglijun/p/8926371.html

版权

1）使用join 和 rand() 耗时 0.009

SELECT
    *
FROM
    `t_topic` AS t1
JOIN (
    SELECT
        ROUND(
            RAND() * (
                (SELECT MAX(id) FROM `t_topic`) - (SELECT MIN(id) FROM `t_topic`)
            ) + (SELECT MIN(id) FROM `t_topic`)
        ) AS id
) AS t2
WHERE
    t1.id >= t2.id
ORDER BY
    t1.id
LIMIT 10;

2) 下面这条比上面那个还要慢几秒

SELECT * FROM `t_topic` 
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `t_topic`)-(SELECT MIN(id) FROM `t_topic`)) + (SELECT MIN(id) FROM `t_topic`)))  
ORDER BY id LIMIT 10;

3）或者使用下面这个也可以测试也是耗时0.0012

SELECT
    *
FROM
    `t_topic`
WHERE
    id >= (
        SELECT
            floor(
                RAND() * (SELECT MAX(id) FROM `t_topic`)
            )
    )
ORDER BY
    id
LIMIT 10;