Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to optimize query statements in MySQL, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

First, take a look at the basic principles of paging:

> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20\ G

* 1. Row *

Id: 1

Select_type: SIMPLE

Table: message

Type: index

Possible_keys: NULL

Key: PRIMARY

Key_len: 4

Ref: NULL

Rows: 10020

Extra:

1 row in set (0.00 sec)

Limit 10000 10000 20 means to scan 10020 rows that meet the conditions, throw away the previous 10000 rows and return to the last 20 rows. This is the problem. If it is a limit 1000000 Magi 100, 100100 rows need to be scanned. In a highly concurrent application, each query needs to scan more than 10W rows, so the performance must be greatly compromised. It is also mentioned that the performance of limit n is fine because only n lines are scanned.

The article mentions a "clue" practice, which provides some "clues" to page flipping, such as SELECT * FROM message ORDER BY id DESC, 20 pages per page according to id descending order, currently page 10, the current page entry id is 9527, the smallest is 9500, if we only provide "previous page" and "next page" jump (do not provide jump to page N) Then when dealing with "previous page", the SQL statement can be:

SELECT * FROM message WHERE id > 9527 ORDER BY idASC LIMIT 20

When dealing with "next page", the SQL statement can be:

SELECT * FROM message WHERE id

< 9500 ORDER BY idDESC LIMIT 20; 　　不管翻多少页，每次查询只扫描20行。 　　缺点是只能提供"上一页"、"下一页"的链接形式，但是我们的产品经理非常喜欢""这样的链接方式，怎么办呢? 　　如果LIMIT m,n不可避免的话，要优化效率，只有尽可能的让m小一下，我们扩展前面的"clue"做法，还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，比如要跳到第8页，我看的SQL语句可以这样写： 　　SELECT * FROM message WHERE id >

9527 ORDER BY idASC LIMIT 20,20

Jump to page 13:

SELECT * FROM message WHERE id < 9500 ORDER BY idDESC LIMIT 40

The principle is still the same, record the maximum and minimum id of the current page, calculate the relative offset between the jump page and the current page, because the page is similar, this offset will not be very large, so the m value is relatively small, greatly reducing the number of rows scanned. In fact, the relative offset of the traditional limit mdirection n is always the first page, so the more you turn to the back, the less efficient it is, and the method given above does not have such a problem.

Pay attention to the ASC and DESC in the SQL statement. If it is the result taken by ASC, remember to invert it when displaying it.

It has been tested in the table of 60W total data, and the effect is very obvious.

After reading the above, do you have any further understanding of how to optimize query statements in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.