Limit optimization in MySQL

"

Finally we have to start optimizing MySQL. This article will explain the optimization of paging. I hope I can get a solution that suits you

"

Preface

The topic of paging is already a cliché, but how many friends want to optimize it? On the other hand, I still maintain my own unique personality in terms of my own system.

Have personality

Optimization requires you to take the initiative by yourself, and get your own test data. Only on the way to testing will you discover more. Things you don’t know.

This article Kaka will also explain the topic of paging optimization.

1. Table structure

This database structure is the table of Kaka’s current online projects, except that Kaka Just changed the field name and canceled the time field.

The database structure is as follows

<span style="display: block; background: url(https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; padding-top: 15px; background: #282c34; border-radius: 5px;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">CREATE</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">TABLE</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">`tp_statistics`</span> (<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_id`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">int</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> AUTO_INCREMENT,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field1`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field2`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field3`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  PRIMARY <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">KEY</span> (<span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_id`</span>)<br/>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">ENGINE</span>=<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">InnoDB</span> AUTO_INCREMENT=<span class="hljs-number" style="color: #d19a66; line-height: 26px;">3499994</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">CHARSET</span>=utf8 <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">COLLATE</span>=utf8mb4_general_ci ROW_FORMAT=<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">COMPACT</span>;<br/><br/></code>

Table structure

According to the above information, you can see that there are currently 3.5 million records in the table. Next, we will optimize the query for these 350W records.

2. Preliminary exploration of query efficiency

Let’s first write a query SQL statement and take a look at the time consumed by the query.

According to the figure below, you can see that the query time is basically ignored, but what should be noted is the offset value of the limit.

Initial query results

So we need to increase the offset step by step and then test it. First change the offset to 10000

You can see that the query time is still very ideal.

Offset 10000 query

In order to save time, adjust the offset value directly to 340W.

At this time, you can see very obvious changes, and the query time soared to 0.79s.

Offset 340w query

If such a situation occurs, it definitely needs to be optimized. Just pick up the keyboard and do it.

3. Reasons why analysis query takes time

When it comes to analyzing SQL statements, the necessary knowledge points are Explain, if you don’t know how to use this tool, you can take a look at the basic part of MySQL.

As shown in the figure below, you can see that all three query statements have undergone table scans.

explain analysis statement

We all know that as long as there is paging, there must be sorting, so add a sorting and look at the query efficiency.

Query time after sorting

Then analyze and view the sorted statements.

You can see here that when sorting is used, the number of rows scanned by the database is the offset plus the number of queries required.

Xu sorting statement

What you can know at this point is that when the offset is very large, like the limit in the case above 3400000,12 such query.

At this time, MySQL needs to query 3400012 rows of data, and then return the last 12 pieces of data.

The 340W data queried previously will be discarded. Such execution results are not what we want.

Kaka saw related articles before saying that the solution to this problem is to either directly limit the number of paging, or to optimize the performance when the offset is very large.

If you have read this article to this point, how could it disappoint you? It must be a performance issue of optimizing large offsets.

4. Optimization

Since optimization is mentioned, there are only two points, add index and use other program to replace this program.

The data table structure information provided by Kaka can be completely understood as the library's borrowing record, so you don't need to worry about anything in the fields.

For sorting, in this scenario, the time will not be sorted, but the primary key will be sorted, and the time field will be canceled due to the addition of test data.

接下来使用覆盖索引加inner join的方式来进行优化。

<span style="display: block; background: url(https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; padding-top: 15px; background: #282c34; border-radius: 5px;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> ss_id,ss_field1,ss_field2,ss_field3 <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> tp_statistics <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">inner</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">join</span> ( <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> ss_id <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> tp_statistics <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">order</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">by</span> ss_id <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">limit</span> <span class="hljs-number" style="color: #d19a66; line-height: 26px;">3000000</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">10</span>) b <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">using</span> (ss_id);<br/></code>

Limit optimization in MySQL

从上图可以看到查询时间从0.8s优化到了0.4s，但是这样的效果还是不尽人意。

于是只能更换一下思路再进行优化。

Limit optimization in MySQL

既然优化最大偏移量这条路有点坎坷，能不能从其它方面进行入手。

估计有很多同学已经知道咔咔将要抛出什么话题了。

没错，就是使用where > id  然后使用limit。

先来测试一波结果，在写具体实现方案。

Limit optimization in MySQL

根据上图可以看到这种方式是十分可行的，分页在300W条数据以后的查询时间也基本忽略不计。

那么这种方案要怎么实现呢！

五、方案落地

其实这个方案真的很简单，只需要简单的转换一下思路即可。

It’s time to make changes

When the client obtains data for the first time, the offset and limit parameters are passed normally.

The data returned for the first time is obtained using the offset and limit passed by the client.

When the first data is returned successfully.

When the client pulls data for the second time, the parameters have changed. They can no longer be offset and limit.

The parameter that should be passed at this time is the id of the last piece of data obtained for the first time.

The parameters at this time are last_id and limit.

After getting the last_id in the background, you can use the where condition in the sql statement

The situation given here is that the data is in flashback. If the positive sequence is greater than last_id, that is Can.

Next, Kaka will use a case to give you a direct and clear explanation.

Practical Case

The following is a case that will be demonstrated in practice. For example, the data is obtained by using page and limit for the first time.

The id of the last piece of data returned is 3499984

##Getting data for the first time

At this time If you are getting the second record, instead of using offset and limit, you need to pass last_id and limit.

As shown below

At this time, the where condition is used to directly filter the data. The condition is that the id is less than the last id of the last data.

Get the second piece of data

Time comparison

Suppose you want to get the last piece of data now

Before optimization

Before optimization

After optimization you can clearly see the change in query time

Query after optimization

6. Summary

Let’s give a brief overview of limit optimization in a few words.

• When the amount of data is large, offset and limit cannot be used for paging, because the larger the offset, the longer the query time.
• Of course it cannot be said that all paging is not possible. If your data only has thousands or tens of thousands of pieces, then it doesn't matter, just use it casually.
• The implementation plan is the one above Kaka. Use offset and limit to obtain data for the first time, and use the where condition for the second time to obtain data to the last id of the first data.

“

Persistence in learning, persistence in blogging, and persistence in sharing are the beliefs that Kaka has always adhered to since he started working in the industry. I hope that Kaka’s success in the huge Internet The article can bring you a little bit of help. I am Kaka, see you next time.

”

The above is the detailed content of Limit optimization in MySQL. For more information, please follow other related articles on the PHP Chinese website!