How to optimize the sorting of limit in MySQL5.7
MySQL5.7 how to optimize the ranking of limit, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.
Test a case with MySQL5.5 and MySQL5.7 respectively.
In a MySQL5.5 environment:
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 | 13:02:27 | ptt19900201 | 199620 | August 4, 2015 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 department 5.0 | 1.0.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| | 1910914 | 9322390ed909db4f20ebe6a7e9b4aa10888106ad | 60480000 | 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 A360tLines 4.4.2 | 1.0.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
| | 1813583 | 39f7fbab24dad7125517808690c54b2329b624a7 | 60480000 | 22:34:19-07 / 2015 | NULL | 0 | NULL | ed0f81be203d0b281c989d2ea9a64add4ecddeeb824e050d9a067294ac052cf2 | iOS8.1 | 1.0.3 | NULL | NULL | NULL | NULL | 1 | NULL | NULL |
5 rows in set (4.60 sec)
It takes 4.6 seconds.
In the MySQL5.7 test environment:
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 | 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)
It takes 2.29s.
During this test, the table structure is exactly the same in both environments, and the MySQL5.7 time is only 50% of that of the MySQL5.5 version.
The reasons are as follows:
When non-indexed fields are sorted in the MySQL5.5 version, all records in the table are sorted, and then five limit records are fetched. In MySQL5.6/5.7, only 5 records of limit are sorted, which speeds up the process.
After reading the above, have you mastered how to optimize the sorting of limit in MySQL5.7? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
