也来谈一下随即查询数据的效率问题-阿里云开发者社区

也来谈一下随即查询数据的效率问题

2017-11-22 1054

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

看到这篇文章：
[url]http://jnote.cn/blog/mysql/mysql-rand-efficiency.html[/url]

我做了些测试：

mysql> show create table song\G
*************************** 1. row ***************************
       Table: song
Create Table: CREATE TABLE `song` (
`id` int(11) NOT NULL auto_increment COMMENT 'Autoincreament element',
`name` mediumtext NOT NULL,
`datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIME
STAMP,
`rank` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `f_rank` (`rank`)
) ENGINE=MyISAM AUTO_INCREMENT=1752001 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql> select count(1) from song;
+----------+
| count(1) |
+----------+
| 1752000 |
+----------+
1 row in set (0.00 sec)

mysql> select * from song order by rand() limit 10;
+---------+----------+---------------------+---------+
| id      | name     | datetime            | rank    |
+---------+----------+---------------------+---------+
| 440107 | t440107 | 2007-12-04 14:59:37 | 4335260 |
| 1237909 | t1237909 | 2007-12-04 15:00:42 | 7867024 |
|   75076 | t75076   | 2007-12-04 14:59:08 | 1725100 |
| 1351876 | t1351876 | 2007-12-04 15:00:52 | 6407879 |
| 915284 | t915284 | 2007-12-04 15:00:15 | 7382198 |
| 110507 | t110507 | 2007-12-04 14:59:11 | 6505687 |
| 1256420 | t1256420 | 2007-12-04 15:00:44 | 8501775 |
| 668347 | t668347 | 2007-12-04 14:59:56 | 4243303 |
| 547132 | t547132 | 2007-12-04 14:59:46 | 7121637 |
| 621792 | t621792 | 2007-12-04 14:59:52 | 7006891 |
+---------+----------+---------------------+---------+
10 rows in set (3.70 sec)

SELECT * FROM song AS t1 JOIN
(
SELECT ROUND(RAND() * (SELECT MAX(id) FROM song)) AS id
) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 10;
mysql> SELECT * FROM song AS t1 JOIN
    -> (
    ->   SELECT ROUND(RAND() * (SELECT MAX(id) FROM song)) AS id
    -> ) AS t2
    -> WHERE t1.id >= t2.id
    -> ORDER BY t1.id ASC LIMIT 10;
+---------+----------+---------------------+---------+---------+
| id      | name     | datetime            | rank    | id      |
+---------+----------+---------------------+---------+---------+
| 1566855 | t1566855 | 2007-12-04 15:01:10 | 7827537 | 1566855 |
| 1566856 | t1566856 | 2007-12-04 15:01:10 | 846094 | 1566855 |
| 1566857 | t1566857 | 2007-12-04 15:01:10 | 747858 | 1566855 |
| 1566858 | t1566858 | 2007-12-04 15:01:10 | 1201006 | 1566855 |
| 1566859 | t1566859 | 2007-12-04 15:01:10 | 3761459 | 1566855 |
| 1566860 | t1566860 | 2007-12-04 15:01:10 | 5204274 | 1566855 |
| 1566861 | t1566861 | 2007-12-04 15:01:10 | 4736994 | 1566855 |
| 1566862 | t1566862 | 2007-12-04 15:01:10 | 8072147 | 1566855 |
| 1566863 | t1566863 | 2007-12-04 15:01:10 | 6149754 | 1566855 |
| 1566864 | t1566864 | 2007-12-04 15:01:10 | 6532326 | 1566855 |
+---------+----------+---------------------+---------+---------+
10 rows in set (0.02 sec)

SELECT * FROM `song` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `song`)-(SELECT MIN(id) FROM `song`)) + (SELECT MIN(id) FROM `song`))) ORDER BY id LIMIT 10;
mysql> SELECT * FROM `song` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id)
FROM `song`)-(SELECT MIN(id) FROM `song`)) + (SELECT MIN(id) FROM `song`))) ORD
ER BY id LIMIT 10;
+------+-------+---------------------+---------+
| id   | name | datetime            | rank    |
+------+-------+---------------------+---------+
| 2142 | t2142 | 2007-12-04 14:59:03 | 3644248 |
| 2411 | t2411 | 2007-12-04 14:59:03 | 9358685 |
| 4401 | t4401 | 2007-12-04 14:59:03 | 7664728 |
| 4732 | t4732 | 2007-12-04 14:59:03 | 3501462 |
| 4768 | t4768 | 2007-12-04 14:59:03 | 1688296 |
| 4820 | t4820 | 2007-12-04 14:59:03 | 346328 |
| 5075 | t5075 | 2007-12-04 14:59:03 | 3586428 |
| 5167 | t5167 | 2007-12-04 14:59:03 | 3742607 |
| 5224 | t5224 | 2007-12-04 14:59:03 | 5285546 |
| 6244 | t6244 | 2007-12-04 14:59:03 | 3076592 |
+------+-------+---------------------+---------+
10 rows in set (0.03 sec)
SELECT * FROM `song` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `song`)))) ORDER BY id LIMIT 20;
mysql> SELECT * FROM `song` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id)
FROM `song`)))) ORDER BY id LIMIT 20;
+------+-------+---------------------+---------+
| id   | name | datetime            | rank    |
+------+-------+---------------------+---------+
| 1874 | t1874 | 2007-12-04 14:59:03 | 1974147 |
| 2090 | t2090 | 2007-12-04 14:59:03 | 8433426 |
| 2653 | t2653 | 2007-12-04 14:59:03 | 2637918 |
| 3244 | t3244 | 2007-12-04 14:59:03 | 2044034 |
| 3538 | t3538 | 2007-12-04 14:59:03 | 728785 |
| 3703 | t3703 | 2007-12-04 14:59:03 | 5099125 |
| 4941 | t4941 | 2007-12-04 14:59:03 | 7530856 |
| 5059 | t5059 | 2007-12-04 14:59:03 | 2147224 |
| 5494 | t5494 | 2007-12-04 14:59:03 | 8994486 |
| 5550 | t5550 | 2007-12-04 14:59:03 | 1054949 |
| 5953 | t5953 | 2007-12-04 14:59:03 | 5898393 |
| 6515 | t6515 | 2007-12-04 14:59:03 | 7227102 |
| 6818 | t6818 | 2007-12-04 14:59:03 | 4292209 |
| 6962 | t6962 | 2007-12-04 14:59:03 | 7107078 |
| 7242 | t7242 | 2007-12-04 14:59:03 | 1639503 |
| 7449 | t7449 | 2007-12-04 14:59:03 | 547861 |
| 8109 | t8109 | 2007-12-04 14:59:03 | 2031446 |
| 8764 | t8764 | 2007-12-04 14:59:03 | 3934261 |
| 8808 | t8808 | 2007-12-04 14:59:03 | 1433932 |
| 9283 | t9283 | 2007-12-04 14:59:04 | 1558430 |
+------+-------+---------------------+---------+

20 rows in set (0.03 sec)

本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/81274，如需转载请自行联系原作者

也来谈一下随即查询数据的效率问题

热门文章

最新文章

相关电子书

相关实验场景