mysql> select count(*) from s;
+----------+
| count(*) |
+----------+
| 2708736 |
+----------+
1 row in set (6.52 sec)
mysql> select column_name from s order by rand() limit 3;
+-----------------+
| column_name |
+-----------------+
| pay_desc |
| ISOLATION_LEVEL |
| rows_examined |
+-----------------+
3 rows in set (10.76 sec)
为了显示方便,在270万条记录的表里随机取3行,用时11秒。选3000条,也是同样时间。此表s没有主键,没有索引,大约十几个字段,column_name是其中一个字符串型字段。
如果加where条件取其中的十几万条记录,也同样很快。
mysql> select count(*) from s where column_name like "a%";
+----------+
| count(*) |
+----------+
| 142080 |
+----------+
1 row in set (1.61 sec)
mysql> select column_name from s where column_name like "a%" order by rand() limit 3;
+-------------+
| column_name |
+-------------+
| a |
| address |
| apname |
+-------------+
3 rows in set (1.86 sec)
数据库版本5.7,在配置普通的阿里云服务器和自己的DELL XPS笔记本上都测过,结果一致。
难道是因为题主的数据库版本太旧,对order by rand() limit 的优化不够?
建议大家自己都实测验证下。我是用
create table s as select * from information_schema.columns
创建表。然后用
insert into s select * from s;
执行八、九遍,估计就200多万条了。