如果你只有一个读请求

将@redsio的答案与一个临时表结合起来(600K并不是很多):

DROP TEMPORARY TABLE IF EXISTS tmp_randorder;
CREATE TABLE tmp_randorder (id int(11) not null auto_increment primary key, data_id int(11));
INSERT INTO tmp_randorder (data_id) select id from datatable;

然后用一个@redsios的版本回答:

SELECT dt.*
FROM
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM tmp_randorder)) AS id)
        AS rnd
 INNER JOIN tmp_randorder rndo on rndo.id between rnd.id - 10 and rnd.id + 10
 INNER JOIN datatable AS dt on dt.id = rndo.data_id
 ORDER BY abs(rndo.id - rnd.id)
 LIMIT 1;

如果表比较大，可以先筛选第一部分:

INSERT INTO tmp_randorder (data_id) select id from datatable where rand() < 0.01;

如果你有很多读请求

Version: You could keep the table tmp_randorder persistent, call it datatable_idlist. Recreate that table in certain intervals (day, hour), since it also will get holes. If your table gets really big, you could also refill holes select l.data_id as whole from datatable_idlist l left join datatable dt on dt.id = l.data_id where dt.id is null; Version: Give your Dataset a random_sortorder column either directly in datatable or in a persistent extra table datatable_sortorder. Index that column. Generate a Random-Value in your Application (I'll call it $rand). select l.* from datatable l order by abs(random_sortorder - $rand) desc limit 1;

这个解决方案用最高和最低的random_sortorder来区分“边缘行”，所以在间隔中重新排列它们(一天一次)。

我需要一个查询从一个相当大的表中返回大量随机行。这是我想到的。首先获取最大记录id:

SELECT MAX(id) FROM table_name;

然后将该值代入:

SELECT * FROM table_name WHERE id > FLOOR(RAND() * max) LIMIT n;

Where max is the maximum record id in the table and n is the number of rows you want in your result set. The assumption is that there are no gaps in the record id's although I doubt it would affect the result if there were (haven't tried it though). I also created this stored procedure to be more generic; pass in the table name and number of rows to be returned. I'm running MySQL 5.5.38 on Windows 2008, 32GB, dual 3GHz E5450, and on a table with 17,361,264 rows it's fairly consistent at ~.03 sec / ~11 sec to return 1,000,000 rows. (times are from MySQL Workbench 6.1; you could also use CEIL instead of FLOOR in the 2nd select statement depending on your preference)

DELIMITER $$

USE [schema name] $$

DROP PROCEDURE IF EXISTS `random_rows` $$

CREATE PROCEDURE `random_rows`(IN tab_name VARCHAR(64), IN num_rows INT)
BEGIN

SET @t = CONCAT('SET @max=(SELECT MAX(id) FROM ',tab_name,')');
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @t = CONCAT(
    'SELECT * FROM ',
    tab_name,
    ' WHERE id>FLOOR(RAND()*@max) LIMIT ',
    num_rows);

PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
$$

then

CALL [schema name].random_rows([table name], n);

我是这样做的:

select * 
from table_with_600k_rows
where rand() < 10/600000
limit 10

我喜欢它，因为它不需要其他表，写起来很简单，执行起来非常快。

如果键之间没有间隙而且都是数字你可以计算随机数然后选择这些行。但事实可能并非如此。

所以一种解决方案是:

SELECT * FROM table WHERE key >= FLOOR(RAND()*MAX(id)) LIMIT 1

这将确保你在键的范围内得到一个随机数然后你选择下一个更大的最佳值。 你必须这样做10次。

然而，这并不是随机的，因为你的钥匙很可能不是均匀分布的。

这真的是一个大问题，不容易解决满足所有的要求，MySQL的rand()是最好的，如果你真的想要10个随机行。

然而，还有另一种解决方案，它速度很快，但涉及到随机性时也需要权衡，但可能更适合你。在这里阅读:我如何优化MySQL的ORDER BY RAND()函数?

问题是你需要它有多随机。

你能多解释一下吗?这样我才能给你一个好的解决办法。

例如，我合作的一家公司有一个解决方案，他们需要非常快的绝对随机性。最后，他们用随机值预填充数据库，这些随机值是从降序选择的，然后再次设置为不同的随机值。

如果你几乎没有更新，你也可以填充一个递增的id，这样你就没有间隙，只是可以在选择之前计算随机键…这取决于用例!

另一个简单的解决方案是对行进行排名，并随机获取其中之一，有了这个解决方案，你将不需要在表中有任何基于“Id”的列。

SELECT d.* FROM (
SELECT  t.*,  @rownum := @rownum + 1 AS rank
FROM mytable AS t,
    (SELECT @rownum := 0) AS r,
    (SELECT @cnt := (SELECT RAND() * (SELECT COUNT(*) FROM mytable))) AS n
) d WHERE rank >= @cnt LIMIT 10;

您可以根据需要更改限制值，以便访问尽可能多的行，但大多数情况下是连续的值。

然而，如果你不想要连续的随机值，那么你可以获取一个更大的样本并从中随机选择。就像……

SELECT * FROM (
SELECT d.* FROM (
    SELECT  c.*,  @rownum := @rownum + 1 AS rank
    FROM buildbrain.`commits` AS c,
        (SELECT @rownum := 0) AS r,
        (SELECT @cnt := (SELECT RAND() * (SELECT COUNT(*) FROM buildbrain.`commits`))) AS rnd
) d 
WHERE rank >= @cnt LIMIT 10000 
) t ORDER BY RAND() LIMIT 10;

这里有一个改变游戏规则的方法，可能对许多人有帮助;

我有一个有200k行的表，有连续的id，我需要选择N个随机行，所以我选择根据表中最大的id生成随机值，我创建了这个脚本来找出哪个是最快的操作:

logTime();
query("SELECT COUNT(id) FROM tbl");
logTime();
query("SELECT MAX(id) FROM tbl");
logTime();
query("SELECT id FROM tbl ORDER BY id DESC LIMIT 1");
logTime();

结果如下:

计数:36.8418693542479毫秒 Max: 0.241041183472 ms 订单:0.216960906982毫秒

根据这个结果，order desc是得到最大id的最快操作， 以下是我对这个问题的回答:

SELECT GROUP_CONCAT(n SEPARATOR ',') g FROM (
    SELECT FLOOR(RAND() * (
        SELECT id FROM tbl ORDER BY id DESC LIMIT 1
    )) n FROM tbl LIMIT 10) a

...
SELECT * FROM tbl WHERE id IN ($result);

供您参考:从一个200k表中随机获得10行，我花了1.78 ms(包括php方面的所有操作)