切记！MySQL中order by与limit不要一起用！

很多人写 MySQL 查询的时候，喜欢直接在 order by 后面跟个 limit，看似无害，实际上暗藏性能坑。我们就用最朴素的思路，把这个事说清楚。

问题场景

假设有个电商订单表 orders，字段有 id、user_id、 amount、create_time 等。 有个很常见的需求：查某个用户最近的 10 条订单记录，于是大家很自然地写成：

SELECT * 
FROM orders 
WHERE user_id = 1001 
ORDER BY create_time DESC 
LIMIT 10;

这看上去没毛病，但问题是——如果 orders 有几千万行数据，MySQL 会先做一次排序，然后再截取前 10 行。这个排序过程可能会非常耗内存，还会用到临时文件。

为什么会慢？

MySQL 在执行 order by 时，如果排序列上没有合适的索引，就会：

1. 先把符合条件的所有行都查出来；
2. 按照  create_time 排序；
3. 最后再取 limit 指定的前几条。

这意味着，哪怕只取 10 行数据，也可能要扫描和排序成千上万条记录。尤其是磁盘临时文件排序，会拖慢整个查询。

你可能会问：那我加个 limit 不就是为了减少数据量吗？是的，但 limit 只能减少返回的数据量，不会减少排序时的数据量。

正确姿势

最简单的优化思路就是——利用索引直接“带顺序”取数据，这样就不用额外排序。 比如给 (user_id, create_time) 建复合索引：

ALTER TABLE orders 
ADD INDEX idx_user_time(user_id, create_time DESC);

这样 MySQL 就能按索引顺序直接扫描 10 行就停，不做额外排序。 执行计划里 Extra 字段会变成 Using index 或者 Using where; Using index，而不是 Using filesort。

更坑的地方：大偏移量 limit

另一种常见误用是：

SELECT * 
FROM orders 
WHERE user_id = 1001 
ORDER BY create_time DESC 
LIMIT 100000, 10;

这个写法会先取出前 100010 条记录排序，然后扔掉前 100000 条，只返回后面的 10 条。这对 MySQL 来说等于做了无用功。 更好的方法是用子查询定位游标：

SELECT * 
FROM orders 
WHERE user_id = 1001
AND create_time < (
    SELECT create_time 
    FROM orders 
    WHERE user_id = 1001
    ORDERBY create_time DESC
    LIMIT100000, 1
)
ORDERBY create_time DESC
LIMIT10;

这样 MySQL 会直接从指定位置开始查，不用扫和排序所有记录。

Java 中的分页实现

很多项目用 Java + MyBatis 或 JPA 分页，这里给个简单的例子：

public List getRecentOrders(Long userId, int offset, int pageSize) {
    String sql = "SELECT * FROM orders " +
                 "WHERE user_id = ? " +
                 "ORDER BY create_time DESC " +
                 "LIMIT ?, ?";
    return jdbcTemplate.query(sql, new Object[]{userId, offset, pageSize}, new OrderRowMapper());
}

优化建议：

1. 索引优先：确保分页和排序的字段上有联合索引；
2. 游标分页：大偏移量时不要直接用 limit offset，改用基于上一页最后一条记录的条件。

总结

• order by + limit 如果没配合合适的索引，性能可能会非常差；
• 对于小数据量没事，但数据一大就会踩坑；
• 大偏移量分页要用“游标”方式优化；
• 写 SQL 之前先想清楚：能不能利用索引直接按顺序取？