Mysql Optimization case Series-mysql paging optimization _mysql

Usually, we will use the order by LIMIT start, offset to do the paging query. For example, the following SQL:

SELECT * from ' T1 ' WHERE ftype=1 the ORDER by ID DESC LIMIT 100, 10;

Or, like the following paged SQL without any conditions:

SELECT * from ' T1 ' ORDER by ID DESC LIMIT 100, 10;

In general, paging SQL takes a dramatic increase as the start value increases, and we look at the following 2 different starting values for paging SQL execution time:

Yejr@imysql.com> SELECT * from ' T1 ' WHERE ftype=1 the ORDER by ID DESC LIMIT;
... Rows in Set (0.05 sec)
yejr@imysql.com> SELECT * from ' T1 ' WHERE ftype=6 the ORDER by ID DESC LIMIT 935500;
... Rows in Set (2.39 sec)

As you can see, as the number of paging increases, the SQL query is dozens of times times more time-consuming and obviously unscientific. Today we will analyze how to optimize this paging scheme. General drops, want to optimize the page's ultimate solution is: no paging, ha ha ~ ~, do not say I talk nonsense, indeed, you can put the page algorithm to Sphinx, Lucence and other third-party solutions, there is no need to let MySQL to do it is not good at things. Of course, a small partner said, with a third party too much trouble, we want to use MySQL to do this pagination, how to do? Don't worry, and wait for us to analyze, first look at the following table DDL, data volume, query SQL execution plan, and other information:

 yejr@imysql.com> show CREATE TABLE ' T1 ';
 CREATE TABLE ' t1 ' (' id ' int ' 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, 10\g ************** 1. Row *************************** id:1 select_type:simple table:t1 type:index possible_keys:null key _len:4 ref:null rows:510 extra:using where yejr@imysql.com> EXPLAIN SELECT * from ' t1 ' where ftype=1 order by I D DESC LIMIT 935500, 10\g *************************** 1. Row *************************** id:1 select_type:simple table:t1 type:index possible_keys:null key _len:4 ref:null rows:935510 extra:using where 

As you can see, although the primary key index is scanned, but the second SQL needs to scan the number of records is too large, and need to scan about 935,510 records, and then based on the results of sorting 10 records, this is certainly very slow. In view of this situation, our optimization thinking is more clear, there are two points:

1, as far as possible from the index to obtain data directly, to avoid or reduce the direct scan of the line data frequency
2, as far as possible to reduce the number of scanned records, that is, first to determine the starting range, and then take N records can be

Accordingly, we have two corresponding rewriting methods: subqueries, table joins, that is, the following:

#采用子查询的方式优化, get the maximum ID from the index in the subquery, then the reverse row, and then take 10 rows of result sets
#注意这里采用了2次倒序排, so when you take the start value of the limit, you add 10 to the original value, or 935510, otherwise the result will be inconsistent with the original

Yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * to ' t1 ' where ID > (select id from ' T1 ' where ftype=1 order by ID DESC LIMIT 935510, 1) LIMIT T ORDER by ID desc\g *************************** 1.  Row *************************** id:1 select_type:primary table: <derived2> type:all possible_keys:null key: NULL key_len:null ref:null rows:10 extra:using filesort *************************** 2. Row *************************** id:2 select_type:derived table:t1 type:all possible_keys:primary key:null Key_ Len:null ref:null rows:973192 extra:using where *************************** 3. Row *************************** id:3 select_type:subquery table:t1 type:index possible_keys:null key:primary k Ey_len:4 ref:null rows:935511 extra:using where #采用INNER join optimization, the JOIN clause also takes precedence from the index to get the ID list, and then directly associates the query to get the final result, there is no need to add a yejr@i Mysql.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:all possible_keys:null key: NULL key_len:null ref:null rows:935510 extra:null *************************** 2. 
 Row *************************** id:1 select_type:primary table:t1 type:eq_ref possible_keys:primary Key_len:4 ref:t2.id rows:1 extra:null *************************** 3. Row *************************** id:2 select_type:derived table:t1 type:index possible_keys:null key:primary ke
 Y_len:4 ref:null rows:973192 extra:using where

Then we'll compare these 2 optimized new SQL execution times:

Yejr@imysql.com> SELECT * FROM (SELECT * to ' t1 ' where ID > (select id from ' T1 ' where ftype=1 order by ID DESC L Imit 935510, 1) LIMIT T order by ID DESC;
...
Rows in Set (1.86 sec)
#采用子查询优化, judging from the results of profiling, it is faster than the original sql: 28.2%

yejr@imysql.com> SELECT * from ' T1 ' INNER JOIN (SELECT ID from ' T1 ' WHERE ftype=1 the ORDER by ID DESC LIMIT 935500,10) T2 USING (ID);
...
Rows in Set (1.83 sec)
#采用INNER join optimization, from the profiling results, compared to the original SQL faster: 30.8%

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

#原始SQL yejr@imysql.com> EXPLAIN SELECT * from ' T1 ' ORDER by ID DESC LIMIT 935500, 10\g *************************** 1. Row *************************** id:1 select_type:simple table:t1 type:index possible_keys:null RY key_len:4 ref:null rows:935510 extra:null yejr@imysql.com> SELECT * from ' T1 ' ORDER by ID DESC LIMIT
935500, 10;
... Rows in Set (2.22 sec) #采用子查询优化 yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * from ' t1 ' WHERE ID > (SELECT i)
D from ' T1 ' ORDER by ID DESC LIMIT 935510, 1 LIMIT) T-ID DESC; 1.
   Row *************************** id:1 select_type:primary table: <derived2> type:all possible_keys:null Key:null key_len:null ref:null rows:10 extra:using filesort *************************** 2. Row *************************** id:2 select_type:derived table:t1 type:all possible_keys:primary L key_len:null ref:null Rows:973192 extra:using where *************************** 3. Row *************************** id:3 select_type:subquery table:t1 type:index possible_keys:null  MARY key_len:4 ref:null rows:935511 extra:using Index yejr@imysql.com> SELECT * FROM [select * from ' T1 '
WHERE ID > (SELECT ID from ' T1 ' ORDER by ID DESC LIMIT 935510, 1) LIMIT ten) T order by ID DESC;
...  Rows in Set (2.01 sec) #采用子查询优化, from the profiling results, compared to the original SQL faster: 10.6% #采用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:ALL possible_keys:null key:null Key_len:null ref:null rows:935510 extra:null *************************** 2. Row *************************** id:1 select_type:primary table:t1 type:eq_ref key: PRIMARY KEy_len:4 ref:t1.id rows:1 extra:null *************************** 3. Row *************************** id:2 select_type:derived table:t1 type:index possible_keys:null ARY key_len:4 ref:null rows:973192 extra:using index yejr@imysql.com> SELECT * from ' T1 ' INNER JOIN (SE
Lect ID from ' T1 ' ORDER by ID DESC LIMIT 935500,10) T2 USING (ID);
...
 Rows in Set (1.70 sec) #采用INNER join optimization, from the profiling results, compared to the original SQL faster: 30.2%

At this point, we see the adoption of subqueries or inner join optimization, there is a significant increase, this method also applies to a smaller paging, although limit started a lot smaller start, SQL execution time is much faster, but this method, The page with where conditions can improve query efficiency: 24.9%, 156.5%, without where conditions of paging to improve query efficiency: 554.5%, 11.7%, you can test your own verification. It's pretty impressive to be able to make sure that these optimization methods can be applied to a variety of paging modes, from the very beginning. Let's take a look at the corresponding elevation ratios for various scenarios:

	Oita page, with where	Oita page, without where	Average elevation ratio of Oita page	Small pagination, with where	Small paging, without where	Overall average elevation ratio
Sub query optimization	28.2%	10.6%	19.4%	24.9%	554.4%	154.53%
INNER Join optimization	30.8%	30.2%	30.5%	156.5%	11.7%	57.3%

Conclusion: This is obvious, especially for the Oita page, so we prefer to use the inner join method to optimize the paging algorithm.

Each of these tests restarts the MYSQLD instance and adds Sql_no_cache to ensure that each time it is read in a direct data file or index file. If the data is warmed up, the query efficiency will be increased to some extent, but the corresponding efficiency improvement ratio is basically consistent.

2014/07/28 PostScript Update:

In fact, if it is not with any conditions of pagination, there is no need to use such a cumbersome method, you can use the primary key to the scope of the search method, such as reference to this article: Advance for MySQL pagination