mysql 获取随机数据
数据量为5500W
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
# myisam SELECT * FROM `table_b` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table_b`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5; # 耗时 0.010 SELECT * FROM `table_b` WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `table_b`))) ORDER BY id LIMIT 1; # 耗时 0.100 SELECT * FROM `table_b` WHERE id >= (SELECT FLOOR( RAND() * ((SELECT MAX(id) FROM `table_b`)-(SELECT MIN(id) FROM `table_b`)) + (SELECT MIN(id) FROM `table_b`))) ORDER BY id LIMIT 1; # 耗时 0.100 SELECT * FROM `table_b` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table_b`)-(SELECT MIN(id) FROM `table_b`))+(SELECT MIN(id) FROM `table_b`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1; # 耗时 0.010 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
# innodb SELECT * FROM `table_b` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table_b`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5; # 耗时 0.008 SELECT * FROM `table_b` WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `table_b`))) ORDER BY id LIMIT 1; # 耗时 0.005 SELECT * FROM `table_b` WHERE id >= (SELECT FLOOR( RAND() * ((SELECT MAX(id) FROM `table_b`)-(SELECT MIN(id) FROM `table_b`)) + (SELECT MIN(id) FROM `table_b`))) ORDER BY id LIMIT 1; # 耗时 0.005 SELECT * FROM `table_b` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table_b`)-(SELECT MIN(id) FROM `table_b`))+(SELECT MIN(id) FROM `table_b`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1; # 耗时 0.010 |
