MySQL：MySQL中 limit 1000000,10 为什么慢？如何优化？

一、前言

  在 MySQL 中，使用 LIMIT X, Y 语句时，如果 X 的值很大，查询性能确实可能会受到影响。这是因为 MySQL 需要先扫描或处理前 X 条记录，然后才能返回从第 X+1 条开始的 Y 条记录。当 X 很大时，这个扫描过程会消耗大量的时间和资源。

  这个问题呢其实就是 MySQL 中典型的深度分页问题。那么我们在日常开发过程中，如何优化呢？

二、案例分析

例如以下示例：

  limit 0,10：查询时间大概在 20 毫秒左右。

  limit 1000000,10：查询时间可能是 15 秒左右（1秒等于 1000 毫秒），甚至更长时间。

  所以，可以看出，limit 中 X 值越大，那么查询速度都越慢。

原因分析：

1. 大量数据扫描

  MySQL 需要扫描大量的数据行（在这个例子中是前100万行），才能定位到需要返回的数据。这个过程中，MySQL 可能会执行全表扫描或全索引扫描，尤其是在没有合适索引支持的情况下。

2. 回表查询

  如果查询涉及非主键索引，MySQL 可能需要先通过非主键索引找到对应的主键ID，然后再通过主键索引回表查询实际数据，这称为“回表”。对于大量数据的查询，回表次数也会显著增加，影响查询性能。

三、优化方法

日常开发中，常用的优化方法

1. ID定位法

  使用上一页最后一条记录的ID（或其他唯一标识符）作为下一页查询的起点。这种方法避免了使用 LIMIT 的 offset 部分

  假设你有一个包含大量记录的表 order，并且你想获取第 10000 页的数据，每页 10 条记录。

传统的查询可能是这样的：

SELECT * FROM orders ORDER BY id LIMIT 99990, 10;

使用ID定位法

SELECT * FROM orders WHERE id > LAST_SEEN_ID ORDER BY id LIMIT 10;

2. 利用子查询或延迟关联优化

SELECT a.*   
FROM ORDER a  
INNER JOIN (  
    SELECT id FROM ORDER WHERE 条件 ORDER BY id LIMIT 1000000, 10  
) b ON a.id = b.id;

3. 索引优化添加适当的索引

  为查询中涉及的列创建索引，特别是 WHERE 子句和 ORDER BY 子句中的列。确保索引的选择是合理的，避免创建过多或不必要的索引。

  使用覆盖索引：如果查询的列都包含在索引中，那么可以直接从索引中获取数据，而无需回表查询，这样可以显著提高查询性能。

4. 使用缓存查询缓存

  对于频繁查询但数据变化不大的结果，可以使用缓存机制，将查询结果缓存起来，减少对数据库的直接访问。但是，需要注意缓存策略和数据变动情况，避免数据不一致问题。

  分页缓存：如果查询的数据经常被访问，可以将查询结果缓存在应用程序中，避免每次都进行数据库查询。

四、总结

  limit 1000000, 10 查询慢的问题主要是由于需要扫描大量数据导致的。通过索引优化、分页查询优化、查询语句调整、使用缓存、等措施，可以显著提高查询性能。在实际应用中，需要根据具体的业务需求和数据特点，选择合适的优化方法，并进行测试和评估，以确定最佳的优化策略。