"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.
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;">'0.00'</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;">'0.00'</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;">'0.00'</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>
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.
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.
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.
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.
We all know that as long as there is paging, there must be sorting, so add a sorting and look at the query efficiency.
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.
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>
从上图可以看到查询时间从0.8s优化到了0.4s，但是这样的效果还是不尽人意。
于是只能更换一下思路再进行优化。
既然优化最大偏移量这条路有点坎坷，能不能从其它方面进行入手。
估计有很多同学已经知道咔咔将要抛出什么话题了。
没错，就是使用where > id 然后使用limit。
先来测试一波结果，在写具体实现方案。
根据上图可以看到这种方式是十分可行的，分页在300W条数据以后的查询时间也基本忽略不计。
那么这种方案要怎么实现呢！
五、方案落地
其实这个方案真的很简单，只需要简单的转换一下思路即可。
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
Time comparison
Suppose you want to get the last piece of data now
Before optimization
After optimization you can clearly see the change in query time
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!
Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AMInnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.
What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AMKey metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.
What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AMUsingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB
Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Apr 15, 2025 am 12:11 AMMySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.
MySQL vs. Other Databases: Comparing the OptionsApr 15, 2025 am 12:08 AMMySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.
How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AMMySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.
MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AMThe MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.
Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AMMySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
SublimeText3 English version
Recommended: Win version, supports code prompts!
SublimeText3 Mac version
God-level code editing software (SublimeText3)
