Mysql optimization-paging strategy under large data volume
one. Preface
Usually, how do we implement paging?
SELECT * FROM table ORDER BY id LIMIT 1000, 10;
But what happens when the amount of data increases sharply?
SELECT * FROM table ORDER BY id LIMIT 1000000, 10;
The second query above is very slow and will be delayed to death.
The most critical reason is the problem of mysql query mechanism:
It is not skipping first and querying later;
Instead, query first and skip later. (Explanation below)
#What does it mean? For example, limit 100000,10. When the required 10 items are found, the first 100,000 pieces of data will be polled. First, the field data of the first 100,000 items will be retrieved, and then discarded if found to be useless, until the required 10 items are finally found. .
two. Analysis
limit offset,N, when the offset is very large, the efficiency is extremely low,
The reason is that mysql does not skip the offset row. Then just take N rows,
but take offset+N rows, return the offset row before giving up, and return N rows [Same as mentioned above, query first, then skip].
The efficiency is lower. When the offset is larger, the efficiency is lower
Three. 3 optimization suggestions
1:solve it from a business perspective
##Method:It is not allowed to turn over the 100 page
##Take Baidu as an example,Generally turn the page to around page 70.
##2:No needoffset,Query with conditions.
##Example:mysql> select id, from lx_com limit 5000000,10;
+---------+--------------------------------------------+
| id | name |
+---------+--------------------------------------------+
| 5554609 |温泉县人民政府供暖中心 |
..................
| 5554618 |温泉县邮政鸿盛公司 |
+---------+--------------------------------------------+
10 rows in set (5.33 sec)
mysql> select id,name from lx_com where id>5000000 limit 10;
+---------+--------------------------------------------------------+
| id | name |
+---------+--------------------------------------------------------+
| 5000001 |南宁市嘉氏百货有限责任公司 |
.................
| 5000002 |南宁市友达电线电缆有限公司 |
+---------+--------------------------------------------------------+
10 rows in set (0.00 sec)
Advantages: Use where conditions to avoid
Query first and then skip the questions. Instead, the condition narrows the scope and skips directly.
Problems: Sometimes it is found that the results of using this method and limitM, N, two times are inconsistent [as shown in the example above ]
Cause:The data has been physically deleted,has holes.
Solution:The data is not physically deleted(can be logically deleted). 最终在页面上显示数据时,逻辑删除的条目不显示即可. (一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 is_delete=1) 3:延迟索引. 非要物理删除,还要用offset精确查询,还不限制用户分页,怎么办? 优化思路： 利用索引覆盖，快速查询出满足条件的主键id;然后凭借主键id作为where条件，达到快速查询。 （速度快在哪里？利用索引覆盖不需要回行就可以快速查询出满足条件的id，时间节约在这里了） 我们现在必须要查,则只查索引,不查数据,得到id.再用id去查具体条目. 这种技巧就是延迟索引. 慢原因： 查询100W条数据的id,name,m每次查询回行抛弃，跨过100W后取到真正要的数据。【就是我们刚刚说的，先查询，后跳过】 优化后快原理： a.利用索引覆盖先查询出主键id，在索引上就拿到信息了，避免回行 b.找到主键后，根据已知的目标主键在查询，避免跨大数据行去寻找，而是直接定位哪几条数据直接查询。 本方法即延迟索引查询。 四。总结： 从方案上来说，肯定是方法一优先，从业务上去满足是否要翻那么多页。 如果业务要求，则用id>n limit m的方式来代替limit n,m,但缺点是不能有物理删除 如果非有物理删除有空缺不能用方法二，则用延迟索引法，本质是利用索引覆盖先快速取出索引值，根据锁定的目标的索引值。一次性去回行取值，效果很明显。 以上就是Mysql优化-大数据量下的分页策略的内容，更多相关内容请关注PHP中文网（www.php.cn）！mysql> select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id);
+---------+-----------------------------------------------+
| id | name |
+---------+-----------------------------------------------+
| 5050425 | 陇县河北乡大谈湾小学 |
........
| 5050434 | 陇县堎底下镇水管站 |
+---------+-----------------------------------------------+
10 rows in set (1.35 sec)
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
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1423
52
1317
25
1268
29
1246
24
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
MySQL and phpMyAdmin: Core Features and Functions
Apr 22, 2025 am 12:12 AM
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
Solve database connection problem: a practical case of using minii/db library
Apr 18, 2025 am 07:09 AM
I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.
Laravel framework installation method
Apr 18, 2025 pm 12:54 PM
Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.
MySQL for Beginners: Getting Started with Database Management
Apr 18, 2025 am 12:10 AM
The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA
Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module
Apr 18, 2025 am 08:42 AM
When developing an e-commerce website using Thelia, I encountered a tricky problem: MySQL mode is not set properly, causing some features to not function properly. After some exploration, I found a module called TheliaMySQLModesChecker, which is able to automatically fix the MySQL pattern required by Thelia, completely solving my troubles.
