社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

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

鸭哥聊Java • 1 年前 • 335 次点击  

很多人写 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 之前先想清楚:能不能利用索引直接按顺序取?

-END-

我为大家打造了一份RPA教程, 完全免费:songshuhezi.com/rpa.html

🔥鸭哥私藏精品 🔥

鸭哥作为一名老码农,整理了全网最全《Java高级架构师资料合集》。总量高达  650GB 。点击下方公众号回复关键字 Java 全部免费领取

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/186437