Detailed description of paging optimization instances in MySQL, and detailed description of mysql paging instances

Detailed description of paging optimization instances in MySQL, and detailed description of mysql paging instances

Generally, order by limit start and offset are used for paging query. For example, the following SQL statement:

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

Or the following paging SQL statement without any conditions:

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

In general, the time consumed by paging SQL increases sharply with the increase of the start value. Let's look at the time consumed by executing these two paging SQL statements with different start values:

yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10;…10 rows in set (0.05 sec)yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=6 ORDER BY id DESC LIMIT 935500, 10;…10 rows in set (2.39 sec)

As you can see, as the number of pages increases, the SQL query time also increases by dozens of times, which is obviously unscientific. Today, we will analyze how to optimize this paging solution. Generally, the ultimate solution to optimize paging is: no paging, hahaha ~~~, Don't talk nonsense. If so, you can hand over the paging algorithm to a third-party solution such as Sphinx and Lucence. There is no need to let MySQL do what it is not good. Of course, some friends said that it is too troublesome to use the third party. We just want to use MySQL for this paging. What should we do? The following table describes the DDL statements, data volumes, and SQL Execution plans:

yejr@imysql.com> SHOW CREATE TABLE `t1`;CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT,... `ftype` tinyint(3) unsigned NOT NULL,... PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;yejr@imysql.com> select count(*) from t1;+----------+| count(*) |+----------+| 994584 |+----------+yejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 510 Extra: Using whereyejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 935510 Extra: Using where

As you can see, although the primary key index is used for scanning, the number of records to be scanned in the second SQL statement is too large, and about 935510 records need to be scanned first, then retrieve 10 records based on the sorting results, which is definitely very slow. In response to this situation, our optimization ideas are clearer. There are two points:

1. Try to obtain data directly from the index to avoid or reduce the frequency of direct row data scanning.
2. Reduce the number of scanned records as much as possible, that is, first determine the starting range, and then obtain N records later.

Accordingly, we have two corresponding rewriting methods: subquery and table join, that is, the following:

# Use the subquery Method for optimization. In the subquery, obtain the largest id from the index, and then sort it in reverse order, and then obtain 10 rows of result sets.
# Note that two reverse sorting is used here. Therefore, when the start value of LIMIT is obtained, 10 is added to the original value, that is, 935510. Otherwise, the result will be different from the original one.

yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC\G*************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using filesort*************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 type: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 973192 Extra: Using where*************************** 3. row *************************** id: 3 select_type: SUBQUERY table: t1 type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 935511 Extra: Using where

# Using inner join optimization, the JOIN clause first obtains the ID list from the index and then directly associates the query to obtain the final result, there is no need to add a 10yejr@imysql.com> explain select * FROM 't1' inner join (SELECT id FROM 'T1 'WHERE ftype = 1 order by id desc limit 935500,10) t2 USING (id) \ G *************************** 1. row ************************** id: 1 select_type: PRIMARY table: <derived2> type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 935510 Extra: NULL **************************** 2. row ************************** id: 1 select_type: PRIMARY table: t1 type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: t2.id rows: 1 Extra: NULL **************************** 3. row ************************** id: 2 select_type: DERIVED table: t1 type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 973192 Extra: Using where

Then we will compare the execution time of the two optimized new SQL statements:

Yejr@imysql.com> SELECT * FROM (SELECT * FROM 't1' WHERE id> (SELECT id FROM 't1' WHERE ftype = 1 order by id desc limit 935510, 1) LIMIT 10) t order by id DESC ;... rows in set (1.86 sec) # Use subquery optimization. From the results of profiling, It is faster than the original SQL statement: 28.2% yejr@imysql.com> SELECT * FROM 't1' inner join (SELECT id FROM 't1' WHERE ftype = 1 order by id desc limit 935500,10) t2 USING (id );... 10 rows in set (1.83 sec) # using inner join optimization, from the results of profiling, It is faster than the original SQL: 30.8%

Let's take a look at a paging SQL comparison without filtering conditions:

# Original SQLyejr@imysql.com> explain select * FROM 't1' order by id desc limit 935500, 10 \ G *************************** 1. row ************************** id: 1 select_type: SIMPLE table: t1 type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 935510 Extra: NULLyejr@imysql.com> SELECT * FROM 't1' order by id desc limit 935500, 10 ;... 10 rows in set (2.22 sec) # using subquery optimization yejr@imysql.com> EXPLA In select * FROM (SELECT * FROM 't1' WHERE id> (SELECT id FROM 't1' order by id desc limit 935510, 1) LIMIT 10) t order by id DESC; * *************************** 1. row ************************** id: 1 select_type: PRIMARY table: <derived2> type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: using filesort **************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 type: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 973192 Extra: using where **************************** 3. row ************************** id: 3 select_type: SUBQUERY table: t1 type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 935511 Extra: Using indexyejr@imysql.com> SELECT * FROM (SELECT * FROM 'T1 'WHERE id> (SELECT I D FROM 't1' order by id desc limit 935510, 1) LIMIT 10) t order by id DESC ;... 10 rows in set (2.01 sec) # Use subquery optimization. From the results of profiling, It is faster than the original SQL statement: 10.6% # using inner join optimization yejr@imysql.com> explain select * FROM 't1' inner join (SELECT id FROM 't1' order by id desc limit 935500,10) t2 USING (id) \ G *************************** 1. row ************************* id: 1 select_type: PRIMARY table: type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 935510 Extra: NULL ********* * ****************** 2. row ************************** id: 1 select_type: PRIMARY table: t1 type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: t1.id rows: 1 Extra: NULL **************************** 3. row ************************** id: 2 select_type: DERIVED table: t1 type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 973192 Extra: Using indexyejr@imysql.com> SELECT * FROM 't1' inner join (SELECT id FROM 't1' order by id desc limit 935500,10) t2 USING (id );... 10 rows in set (1.70 sec) # using inner join optimization, from the results of profiling, It is faster than the original SQL: 30.2%

So far, we can see that the optimization using subqueries or inner join has been greatly improved. This method is also suitable for small pages, although the start position starting from LIMIT is much smaller, the SQL Execution time is much faster. However, after this method is adopted, paging with WHERE conditions can improve the query efficiency by 24.9% and 156.5% respectively. Paging without WHERE conditions improves the query efficiency respectively: 554.5% and 11.7%. You can perform test and verification on your own. From the perspective of Increasing the proportion, it is quite impressive to ensure that these optimization methods can be applied to various paging modes and can be used from the very beginning. Let's take a look at the corresponding increase ratio in various scenarios:

Conclusion: This is obvious, especially for large pages. Therefore, we recommend using inner join to optimize the paging algorithm.

In each test above, the mysqld instance is restarted and SQL _NO_CACHE is added to ensure that data files or index files are read directly each time. If the data is pushed, the query efficiency will be improved to a certain extent, but the proportion of the efficiency improvement is basically the same.