MySQL wide range of page optimization cases

On the BBS line business catches the following paged sql:

142597301Meizu_bbs192.168.17.72:39096Meizu_bbs Query217Sending dataSELECT *  fromPre_forum_threadWHEREFid= A  andDisplayorder>=0 ORDER  byLastpostDESCLIMIT1933100, -142597338Meizu_bbs192.168.17.72:39128Meizu_bbs Query216Sending dataSELECT *  fromPre_forum_threadWHEREFid= A  andDisplayorder>=0 ORDER  byLastpostDESCLIMIT1933100, -142604367Nagiosuser127.0.0.1:39893 NULLQuery0   NULLShow FullProcesslist

This SQL has a total of 3 questions:

1:select * This type of writing does not conform to the SQL Authoring specification, do not use * at any time to replace the specific column name, what column to take what column. If there is a text/blob in the table, the effect is more obvious.

The 2:pre_forum_thread table is partitioned in the Tid field, but there is no partition field in the query, so you need to scan all partitions, which is worse than no partitioning.

3: In this page of SQL, the offset is high to 1.93 million.

Limit syntax:

[LIMIT {[offset,]| row_count offset OFFSET}]

The way MySQL handles the limit statement is to take out all offset+rowcount, discard all previous rows, and return only the Row_count rows.

In this case, the number of rows that need to be queried on the MySQL server side is 1933100+50,217s and no results have been obtained.

Optimization scenario: The final need is only 50 rows of records, if you first take out the 50 rows of records of the primary key ID, this will not be fast? Execution plan and execution time:

Mysql>Explain partitionsSELECTTid fromPre_forum_threadWHEREFid= A  andDisplayorder>=0 ORDER  byLastpostDESCLIMIT1933100, -;+----+-------------+------------------+----------------------------------------------------------------------- ------------------------------------+-------+------------------------------------------------------------------ -------------+--------------+---------+------+---------+------------------------------------------+|Id|Select_type| Table            |Partitions|Type|Possible_keys| Key          |Key_len|Ref|Rows|Extra|+----+-------------+------------------+----------------------------------------------------------------------- ------------------------------------+-------+------------------------------------------------------------------ -------------+--------------+---------+------+---------+------------------------------------------+|  1 |Simple|Pre_forum_thread|P0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28|Range|Displayorder,rate,lastpost,fd,fdd,idx_fid_displayorder_heats,idx_displayorder|Displayorder| 4       | NULL | 2673718 |Usingwhere; UsingIndex; Using Filesort|+----+-------------+------------------+----------------------------------------------------------------------- ------------------------------------+-------+------------------------------------------------------------------ -------------+--------------+---------+------+---------+------------------------------------------+1Rowinch Set(0.00sec) MySQL> SELECTSql_no_cache Tid fromPre_forum_threadWHEREFid= A  andDisplayorder>=0 ORDER  byLastpostDESCLIMIT1933100, -;+--------+|Tid|+--------+| 795442 |.........| 795387 || 795168 |+--------+ -Rowsinch Set(1.02Sec

Analyze why only the PK value is removed quickly. In the InnoDB index tree, the leaf node of each level two index holds a PK value, and the process of finding the data through a two-level index is to compare whether the index value matches the query value from the root node of the index tree, and if it does not match, go to the left or right branch, and then compare until the node that meets the requirements is found Then take the PK value from the leaf node and return to the table according to the primary key to get all the data. If you just look for the primary key, then you will have less "then take the PK value from the leaf node and return to the table according to the primary key to get all the data" part, and this part is the most time-consuming. In the execution plan you can see the "Using index", which means that the optimizer uses "overwrite index", only need to scan the index data to obtain the final data, the index is generally smaller than the data, often resident memory, so although the offset of 1.93 million, can also give 1.02 seconds to return results.

After getting these 50 primary key ID values, use these 50 records to correlate the original table query:

Mysql>ExplainSelectSql_no_cache*  fromPre_forum_thread AInner Join(SELECTTid fromPre_forum_threadWHEREFid= A  andDisplayorder>=0 ORDER  byLastpostDESCLIMIT1933100, -) B onA.tid=B.tid;+----+-------------+------------------+--------+-------------------------------------------------------------- -----------------+---------+---------+-------+---------+----------------+|Id|Select_type| Table            |Type|Possible_keys| Key     |Key_len|Ref|Rows|Extra|+----+-------------+------------------+--------+------------------------------------------------------------- ------------------+---------+---------+-------+---------+----------------+|  1 | PRIMARY     | <Derived2>       |  All    | NULL                                                                          | NULL    | NULL    | NULL  |       - |                ||  1 | PRIMARY     |A|Eq_ref| PRIMARY                                                                       | PRIMARY | 4       |B.tid|       1 |                ||  2 |DERIVED|Pre_forum_thread|  All    |Displayorder,rate,lastpost,fd,fdd,idx_fid_displayorder_heats,idx_displayorder| NULL    | NULL    | NULL  | 3307262 |Using Filesort|+----+-------------+------------------+--------+-------------------------------------------------------------- -----------------+---------+---------+-------+---------+----------------+3Rowsinch Set(1.03sec) #执行时间 -Rowsinch Set(1.06Sec

There are many ways to handle paging, where the start of page flipping can be restricted at the business level, and no direct navigation to page 10000 is allowed. There are other ways to deal with database queries, and find the best way to deal with the corresponding business needs. In this case, LIMIT 1933100, 50 needs to be circumvented.

MySQL wide range of page optimization cases