MySQL：为什么用limit时，offset很大会影响性能

Answer 1

MySQL性能问题探讨：使用limit时，大offset影响性能原因解析

本文将深入探讨在MySQL中使用limit时，当offset值较大时，为何查询性能会受到影响。我们将通过具体实例和过程分析，揭示其中的原因。

首先，了解MySQL版本及表结构。表包含id为自增主键和val为非唯一索引的字段。大量数据（500万条）被插入其中。

使用limit offset rows时，当offset值较大，会显著影响查询效率。对比优化后语句，时间差异明显。具体过程如下：

查询时，MySQL需要遍历300005次索引节点和聚簇索引数据，过滤掉前300000条数据，最后取出最后5条结果。这个过程中，大量随机I/O操作集中在查询聚簇索引数据上，而这些数据最终不会出现在结果集中。

有人提出疑问，为何不先利用索引叶子节点查询所需的最后5个节点，再从聚簇索引获取实际数据？这样只需5次随机I/O操作。然而，这一过程并未实现，MySQL的查询逻辑并未遵循这一优化路径。

通过实际操作验证上述分析。发现MySQL无法直接统计利用索引节点查询数据节点的次数，但可以通过间接方法证实。InnoDB缓冲池内存中保存了最近访问的数据页，包括数据页和索引页。通过比较两个SQL语句在缓冲池中加载数据页的数量，可以发现显著差异。

预测结果显示，查询时加载的数据页数量显著不同。第一种情况加载了4098个数据页，而第二种情况仅加载了5个数据页。这验证了大offset导致的性能影响原因：读取大量无用数据行，最终被舍弃。

此外，大量加载热点数据页到缓冲池，会占用缓冲池空间，可能导致buffer池污染，影响查询性能。

为解决每次重启时buffer pool未能清空的问题，可以关闭innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup选项。这些选项控制数据库关闭时备份和重启时加载缓冲池数据。

总结，通过深入分析，我们揭示了大offset导致的性能问题及其原因，并提供了优化策略。理解这些机制有助于更高效地管理和优化数据库查询性能。