MySQL5.7新特性之优化排序limit

最新推荐文章于 2021-03-18 09:38:36 发布

cuiruidu3106

最新推荐文章于 2021-03-18 09:38:36 发布

阅读量467

点赞数

文章标签：数据库

测试一个案例，测试环境分别为MySQL5.5和MySQL5.7。
在MySQL5.5环境中：

点击(此处)折叠或打开

mysql> select * from access_test order by rand() desc limit 5;
+---------+------------------------------------------+------------+---------------------+-------------+-------------+---------------------+------------------------------------------------------------------+-----------------------+---------+----------+-----------------+------------+-------+--------+-------------+------------+
| id | access_token | expires_in | creattime | loginuser | loginuserId | logintime | device_token | system | version | vericode | vericodeExpired | veriMobile | photo | status | kickofftime | inviteCode |
+---------+------------------------------------------+------------+---------------------+-------------+-------------+---------------------+------------------------------------------------------------------+-----------------------+---------+----------+-----------------+------------+-------+--------+-------------+------------+
| 2365527 | 212941d3aa4d4f8638f4ca03048752246c67b995 | 60480000 | 2015-08-04 13:02:27 | ptt19900201 | 199620 | 2015-08-04 13:04:01 | a6b20bbb2e0f943a3c44e2abe5e3a608ccc88191ae32e5d8b99f4ea526c699e9 | iOS8.4 | 1.0.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| 2069322 | 25c44c4c1ef914330d65102c65f5274f4d9b9c30 | 60480000 | 2015-07-22 10:19:57 | NULL | 0 | NULL | As1PFaQq0sym0U4sMG3lAdEnMXuLlDIvEO6eZxpUzSQx | X600,21,5.0 | 1.0.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| 1910914 | 9322390ed909db4f20ebe6a7e9b4aa10888106ad | 60480000 | 2015-07-13 21:38:32 | NULL | 0 | NULL | 5c08686798219e8407c6f1d7cc32d6f6f2b0dc66a17647e5c9c32d4ce0e79277 | iOS8.1.2 | 1.0.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| 2336645 | 8d07e5b0a711906c1922963d4bb69ec08f2ba4f2 | 60480000 | 2015-08-02 22:06:10 | NULL | 0 | NULL | 865786027572153 | Lenovo A360t,19,4.4.2 | 1.0.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| 1813583 | 39f7fbab24dad7125517808690c54b2329b624a7 | 60480000 | 2015-07-07 22:34:19 | NULL | 0 | NULL | ed0f81be203d0b281c989d2ea9a64add4ecddeeb824e050d9a067294ac052cf2 | iOS8.1 | 1.0.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
+---------+------------------------------------------+------------+---------------------+-------------+-------------+---------------------+------------------------------------------------------------------+-----------------------+---------+----------+-----------------+------------+-------+--------+-------------+------------+
5 rows in set (4.60 sec)

耗费时间4.6s
在MySQL5.7测试环境中：

点击(此处)折叠或打开

mysql> select * from access_test order by rand() desc limit 5;
+----------+------------------------------------------+------------+---------------------+-------------+-------------+---------------------+------------------------------------------------------------------+-----------------+---------+----------+-----------------+------------+-------+--------+---------------------+------------+
| id | access_token | expires_in | creattime | loginuser | loginuserId | logintime | device_token | system | version | vericode | vericodeExpired | veriMobile | photo | status | kickofftime | inviteCode |
+----------+------------------------------------------+------------+---------------------+-------------+-------------+---------------------+------------------------------------------------------------------+-----------------+---------+----------+-----------------+------------+-------+--------+---------------------+------------+
| 17114767 | cdbe148a51d17e2defb6f26d8141b69c40caafa9 | 604800 | 2017-06-13 09:34:54 | NULL | 0 | NULL | 01dd46326bfeefc1123162efca0b95bbf63eb7f00b5d854ef3e6c63c8b2d6064 | iOS10.3.2 | 2.2.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| 16743322 | 5db141fdbcdb77b846ba9700ed47b299719ccb25 | 604800 | 2017-05-10 11:08:57 | NULL | 0 | NULL | AmWwno8vzQ9qOuQYemc7ou-sSBzC_wpFg8har6o5Q6KS | MI MAX,23,6.0.1 | 2.2.0 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| 16804100 | 257d7f1ae8c3d0c56e1805d95f3a5ba6af351930 | 604800 | 2017-05-16 11:55:55 | 15195807989 | 1418561 | 2017-05-16 11:56:13 | e86a5208841e53c703ddcf4f949c150ecedc7d7d24659b67ec90e58dbbfcc9e7 | iOS10.0.2 | 2.2.0 | NULL | NULL | NULL | NULL | 0 | 2017-05-30 00:41:31 | NULL |
| 17019899 | 46dc11b60606a8e29c2317742c68cd8e5520fe0a | 604800 | 2017-06-05 09:50:34 | NULL | 0 | NULL | b34fe60cf907b551345043f8c82d178a6d360d75f84657c6135cedbe4d4b1f55 | iOS10.3.2 | 2.2.2 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| 17129397 | 42539aa7fd26a77835ca3e88bd43e0257e3906e9 | 604800 | 2017-06-14 12:46:23 | NULL | 0 | NULL | 2c663f14343de2ddfd6fc53078f3df8d765a1564494dcd58f957395cbc1b44dd | iOS9.3.2 | 2.2.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
+----------+------------------------------------------+------------+---------------------+-------------+-------------+---------------------+------------------------------------------------------------------+-----------------+---------+----------+-----------------+------------+-------+--------+---------------------+------------+
5 rows in set (2.29 sec)

耗费时间2.29s。
该测试过程，两个环境中，表结构一模一样，MySQL5.7耗时仅为MySQL5.5版本的50%。
原因如下：
MySQL5.5版本中对非索引字段进行排序时候，会对表所有记录进行排序，然后取出limit 5条记录。MySQL5.6/5.7中，只针对limit 5条记录进行排序，这样就加快了速度。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30135314/viewspace-2144655/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30135314/viewspace-2144655/