How to optimize limit pagination in SQL Optimization
How to optimize limit pages in SQL optimization, I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.
Example
There are 2375690 data in the table aaaaa.
SQL before optimization
SQL execution result:
SELECT DISTINCT (device_id) uid FROM aaaaa WHERE status = 0 LIMIT 88000 min1000 rows in set (0.48 sec)
SQL execution Plan:
MariaDB [star] > explain SELECT sql_no_cache DISTINCT (device_id) uid FROM aaaaa WHERE status = 0 LIMIT 8800051000 +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+-+ | 1 | SIMPLE | aaaaa | ALL | NULL | 2375690 | Using where Using temporary | +-- +
Optimization mode
Quickly locate the starting ID and use the primary key index to speed up the scanning speed. As you can see, in derived, SQL uses the overlay index to scan, although it is still a full table scan, because only id columns are scanned, which greatly reduces the IO cost of scanning and quickly locates to id.
MariaDB [star] > explain SELECT sql_no_cache DISTINCT (device_id) uid FROM aaaaa join (select id from aaaaa limit 88000hellos 1) k on star_device_5.id > = k.id where status=0 limit 1000 +- -- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+ | 1 | PRIMARY | | ALL | NULL | 88001 | Using temporary | | 1 | PRIMARY | star_device_5 | ALL | PRIMARY | NULL | 2377112 | Range checked for each record (index map: 0x1) | 2 | DERIVED | star_device_5 | index | NULL | idx_star_id | | 8 | NULL | 2377112 | Using index | +-- | -+
Execution result:
SELECT sql_no_cache DISTINCT (device_id) uid FROM star_device_5 join (select id from star_device_5 limit 880000jue 1) k on star_device_5.id > = k.id where status=0 limit 1000 rows in set (0.19 sec)
With the increase of m and n, the SQL execution time of the two writing methods will be essentially different. I have tested that when the m value increases to 880000, the SQL before optimization takes 2 minutes, and the optimized SQL is still about 0.1s.
After reading the above, have you mastered how to optimize limit paging in SQL optimization? 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!
