MySQL 取随机记录的方法

博主： zmx
发布时间：2021 年 12 月 16 日
572 次浏览
暂无评论
1134字数
分类： MySQL

# 引言

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

# 测试机器

| 硬件 | 规格|
| --- | --- |
| CPU | i7-11800H  |
| Ram | 32G 2400 |
| SSD | Intel 760p |

# 小数据量

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

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

```sql
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
```

# 大数据量

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

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

```sql
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
```

# 结论

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