MySQL 用 limit 为什么会影响性能？

Java大神 · 公众号 · 数据库 · 2025-07-06 14:30

主要观点总结

本文介绍了MySQL在查询过程中的效率问题，特别是在使用limit语句时，以及如何通过观察InnoDB的buffer pool来验证查询效率。

关键观点总结

关键观点1: MySQL版本和表结构介绍

文章首先介绍了MySQL的版本为5.7.17，并描述了测试表的表结构，包括id、val和source三个字段，其中id为主键，val为索引。

关键观点2: 大量数据查询问题

文章描述了灌入大量数据（共500万条）后，在使用特定查询语句时出现的效率问题。原始查询语句为'select * from test where val = 4 limit 300000, 5'，该查询需要查询300005个索引节点和300005个聚簇索引上的数据节点，效率低下。

关键观点3: 优化查询方法

为了优化查询效率，文章提出了一种改写查询语句的方法，即使用内连接（inner join）来避免直接查询大量不需要的数据行。改写后的查询语句为'select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id'，这种方法只需要查询5个数据节点，大大提高了查询效率。

关键观点4: 验证查询效率的方法

文章通过实际操作来验证上述推论，通过比较两次查询后buffer pool中的数据页数量来验证两种查询方法的效率差异。第一次查询后buffer pool中有4098个数据页，而第二次查询（使用优化后的查询语句）后只有5个数据页被加载到buffer pool中，验证了优化后的查询方法的有效性。

关键观点5: 遇到的问题及解决方案

为了确保每次重启时清空buffer pool，文章提到了需要关闭innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup这两个选项。这两个选项分别控制数据库关闭时是否dump出buffer pool中的数据和在数据库开启时是否载入在磁盘上备份的buffer pool数据。