MySQL的LIMIT这么差劲的吗

Answer 1

对于MySQL中的LIMIT语句执行逻辑，我们需要先构建一个表t，该表包含3列：id为主键，key1为二级索引列。表中总共有1万条记录。当执行特定的SQL语句时，MySQL会使用二级索引idx_key1进行查询。此操作容易理解，因为索引是有序的，而查询目标是按照key1列排序的首条记录，MySQL只需从索引中获取第一条记录，再回表获取完整记录。

然而，当将LIMIT 1替换为LIMIT 5000, 1时，MySQL却会执行全表扫描，并进行文件排序，执行计划显示为全表扫描+文件排序。许多用户疑惑，为什么LIMIT 5000, 1不能利用二级索引idx_key1，直接扫描到第5001条二级索引记录，进行回表操作，以减少代价。

遗憾的是，MySQL的实现存在缺陷，它不会如期望那样使用二级索引来优化查询。此过程涉及MySQL内部的server层和存储引擎层交互。server层分析可执行方案，选择成本较低的执行计划，然后调用存储引擎提供的接口。假设采用方案二，即使用二级索引idx_key1执行查询，server层会向存储引擎发送请求，让其找到idx_key1索引中('a', 'b')区间的第一条记录，然后回表获取完整记录。

存储引擎InnoDB执行请求，通过索引B+树快速定位到扫描区间('a', 'b')的第一条记录，进行回表操作，返回完整记录给server层。server层进一步判断条件，如果满足则将记录发送至客户端。存储引擎继续搜索下一个记录，重复此过程直至找到第5001条记录。

MySQL处理LIMIT子句是在准备向客户端发送记录时进行的。在使用二级索引执行查询时，MySQL需要进行5001次回表操作。server层在进行执行计划分析时发现回表操作成本过高，选择全表扫描+文件排序，而非利用二级索引。

解决方法在于修改SQL语句为子查询形式，如：SELECT id FROM t ORDER BY key1 LIMIT 5000, 1。这样，子查询只关注id列，可以仅扫描二级索引idx_key1执行子查询，再根据子查询结果查找表t中的主键值，省去前5000条记录的回表操作，显著提高查询效率。

吐槽部分：MySQL在处理LIMIT语句时的实现效率问题，让人不禁期待优化器逻辑的改进。用户需要采取策略欺骗优化器，手动提升查询效率，这种体验令人费解。希望MySQL的大佬们能重新审视并优化LIMIT子句的执行逻辑，让用户在使用时拥有更加高效、直观的体验。