如何在MySQL随机选择记录-优快云博客

本文介绍了一种在MySQL中高效生成随机行的有效方法。对于大型数据表使用ORDER BY RAND()可能导致性能问题。文章提出了一种解决方案，即在WHERE子句中使用RAND()函数，并通过限制选择比例来获取期望数量的随机行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

The easiest way to generate random rows in MySQL is to use the ORDER BY RAND() clause.

SELECT col1 FROM tbl ORDER BY RAND LIMIT 10;

This can work fine for small tables. However, for big table, it will have a serious performance problem as in order to generate the list of random rows, MySQL need to assign random number to each row and then sort them.
Even if you want only 10 random rows from a set of 100k rows, MySQL need to sort all the 100k rows and then, extract only 10 of them.

My solution for this problem, is to use RAND in the WHERE clause and not in the ORDER BY clause. First, you need to calculate the fragment of your desired result set rows number from the total rows in your table. Second, use this fragment in the WHERE clause and ask only for RAND numbers that smallest (or equal) from this fragment.

For example, suppose you have a table with 200K rows and you need only 100 random rows from the table. The fragment of the result set from the total rows is: 100 / 200k = 0.0005.
The query will look like:

SELECT col1 FROM tbl WHERE RAND()<=0.0005;

In order to get exactly 100 row in the result set, we can increase the fragment number a bit and limit the query:
For example:

SELECT col1 FROM tbl WHERE RAND()<=0.0006 limit 100;

转载于:https://my.oschina.net/victorli/blog/13364