MySQL分页性能问题，Limit 性能问题求解决！
众所周知，Mysql分页就要用到Limit进行分页，数据量/分页数小的时候Limit性能是可想而知的。 如：
SELECT pid,author,hash,dateline FROM posts WHERE pid='123456' ORDER BY pid ASC LIMIT 100,100;
上述SQL语句性能没有任何问题。
但是，如果当offset便宜了过大的情况就会出现性能瓶颈。 如：
SELECT pid,author,hash,dateline FROM posts WHERE pid='123456' ORDER BY pid ASC LIMIT 159000,100;
若要解决Limit offset过大的时候，可以采用子查询的方法进行分页。 如：
SELECT pid,author,hash,dateline FROM posts ORDER BY pid ASC LIMIT 159000,100;
等价于：
SELECT pid,author,hash,dateline FROM posts WHERE pid>=(SELECT pid FROM posts LIMIT 159000,1)ORDER BY pid ASC LIMIT 100;
此时的性能是纯粹LIMIT 159000,100 的几倍至高。
但是问题又来了，子查询又必须是数据连续，所以无法加入WHERE查询条件，如：
SELECT tid,pid,author,hash,dateline FROM posts WHERE tid=10 AND pid>=(SELECT pid FROM posts LIMIT 159000,1)ORDER BY pid ASC LIMIT 100;
此时的结果并非我想要的结果。
所以求高性能解决LIMIT分页的方法。 在线等。
============================ PS: pid 为主键
回复内容：
众所周知，Mysql分页就要用到Limit进行分页，数据量/分页数小的时候Limit性能是可想而知的。 如：
SELECT pid,author,hash,dateline FROM posts WHERE pid='123456' ORDER BY pid ASC LIMIT 100,100;
上述SQL语句性能没有任何问题。
但是，如果当offset便宜了过大的情况就会出现性能瓶颈。 如：
SELECT pid,author,hash,dateline FROM posts WHERE pid='123456' ORDER BY pid ASC LIMIT 159000,100;
若要解决Limit offset过大的时候，可以采用子查询的方法进行分页。 如：
SELECT pid,author,hash,dateline FROM posts ORDER BY pid ASC LIMIT 159000,100;
等价于：
SELECT pid,author,hash,dateline FROM posts WHERE pid>=(SELECT pid FROM posts LIMIT 159000,1)ORDER BY pid ASC LIMIT 100;
此时的性能是纯粹LIMIT 159000,100 的几倍至高。
但是问题又来了，子查询又必须是数据连续，所以无法加入WHERE查询条件，如：
SELECT tid,pid,author,hash,dateline FROM posts WHERE tid=10 AND pid>=(SELECT pid FROM posts LIMIT 159000,1)ORDER BY pid ASC LIMIT 100;
此时的结果并非我想要的结果。
所以求高性能解决LIMIT分页的方法。 在线等。
============================ PS: pid 为主键
这篇PDF可以解决你的问题
Efficient Pagination Using MySQL
你的pid是啥，不是主键吗？最后一条 pid=10 AND pid>=(SELECT pid FROM posts LIMIT 159000,1) 有能同时成立吗
外面有的条件，子查询里面照样写就行了，和子查询的条件相同，order by相同，比如： select * from user where sex=2 and issingle=0 and id<=(select id from user where sex=2 and issingle=0 order by id desc limit 1000000,1) order by id desc limit 10";
<code> ALTER TABLE `posts ` ADD INDEX `tid_pid` USING BTREE (`tid`,`pid`) comment ''; </code>
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
PHP and Python: Comparing Two Popular Programming Languages
Apr 14, 2025 am 12:13 AM
PHP and Python each have their own advantages, and choose according to project requirements. 1.PHP is suitable for web development, especially for rapid development and maintenance of websites. 2. Python is suitable for data science, machine learning and artificial intelligence, with concise syntax and suitable for beginners.
PHP's Purpose: Building Dynamic Websites
Apr 15, 2025 am 12:18 AM
PHP is used to build dynamic websites, and its core functions include: 1. Generate dynamic content and generate web pages in real time by connecting with the database; 2. Process user interaction and form submissions, verify inputs and respond to operations; 3. Manage sessions and user authentication to provide a personalized experience; 4. Optimize performance and follow best practices to improve website efficiency and security.
The Enduring Relevance of PHP: Is It Still Alive?
Apr 14, 2025 am 12:12 AM
PHP is still dynamic and still occupies an important position in the field of modern programming. 1) PHP's simplicity and powerful community support make it widely used in web development; 2) Its flexibility and stability make it outstanding in handling web forms, database operations and file processing; 3) PHP is constantly evolving and optimizing, suitable for beginners and experienced developers.
PHP in Action: Real-World Examples and Applications
Apr 14, 2025 am 12:19 AM
PHP is widely used in e-commerce, content management systems and API development. 1) E-commerce: used for shopping cart function and payment processing. 2) Content management system: used for dynamic content generation and user management. 3) API development: used for RESTful API development and API security. Through performance optimization and best practices, the efficiency and maintainability of PHP applications are improved.
PHP and Python: Different Paradigms Explained
Apr 18, 2025 am 12:26 AM
PHP is mainly procedural programming, but also supports object-oriented programming (OOP); Python supports a variety of paradigms, including OOP, functional and procedural programming. PHP is suitable for web development, and Python is suitable for a variety of applications such as data analysis and machine learning.
PHP and Python: Code Examples and Comparison
Apr 15, 2025 am 12:07 AM
PHP and Python have their own advantages and disadvantages, and the choice depends on project needs and personal preferences. 1.PHP is suitable for rapid development and maintenance of large-scale web applications. 2. Python dominates the field of data science and machine learning.
Why Use PHP? Advantages and Benefits Explained
Apr 16, 2025 am 12:16 AM
The core benefits of PHP include ease of learning, strong web development support, rich libraries and frameworks, high performance and scalability, cross-platform compatibility, and cost-effectiveness. 1) Easy to learn and use, suitable for beginners; 2) Good integration with web servers and supports multiple databases; 3) Have powerful frameworks such as Laravel; 4) High performance can be achieved through optimization; 5) Support multiple operating systems; 6) Open source to reduce development costs.
Choosing Between PHP and Python: A Guide
Apr 18, 2025 am 12:24 AM
PHP is suitable for web development and rapid prototyping, and Python is suitable for data science and machine learning. 1.PHP is used for dynamic web development, with simple syntax and suitable for rapid development. 2. Python has concise syntax, is suitable for multiple fields, and has a strong library ecosystem.
