IT俱乐部 MySql 在MySQL中监控和优化慢SQL的完整指南

在MySQL中监控和优化慢SQL的完整指南

发布: 2026年 11月 12日 2阅读

面试官考点分析：

  1. SQL执行原理：考察对 LIMIT offset, size 工作机制及执行计划的理解。
  2. 索引覆盖与回表：能否准确区分并解释深度分页慢的根本原因在于大量回表。
  3. 解决方案设计：是否掌握子查询优化、标签记录法、以及游标分页等解决方案及其适用场景。
  4. 实践与权衡：能否结合实际业务场景（如APP无限下拉）选择最合适的方案，并清楚其优缺点。
  5. 技术视野：是否了解大数据量下，往往需要与Elasticsearch等搜索引擎协同工作。

1. 标准回答

面试官问： 请谈谈你对MySQL深度分页问题的理解，以及如何解决？

你可以这样回答：
MySQL的深度分页是指在分页查询时，LIMIT 语句的偏移量offset非常大的情况。例如 SELECT * FROM t_order LIMIT 1000000, 10。其核心问题在于性能低下，因为MySQL会读取并丢弃前100万条记录，只为返回最后10条，这造成了大量的I/O和CPU浪费。

解决这个问题的核心思路是“避免扫描并丢弃大量无用数据”。 常用的方案有几种：

  1. 子查询/延迟关联优化：利用覆盖索引快速定位目标页的主键ID，再通过主键回表获取完整数据，把“全表扫描所有列”变成“索引扫描主键”。
  2. 标签记录法：记住上一页最后一条数据的ID或时间戳，下一页查询时用 WHERE id > last_id 来替代 LIMIT offset，直接从目标位置开始扫描。
  3. 业务折衷：限制用户翻页深度，或者对于海量数据的查询场景，引入Elasticsearch等搜索引擎专门处理分页和搜索。

2. 核心原理

要理解优化方案，必须先明白 LIMIT 1000000, 10 为什么慢。

假设我们有一张用户订单表 t_order，在 create_time 上建立了索引，但查询语句是 SELECT * FROM t_order ORDER BY create_time LIMIT 1000000, 10。

执行流程是这样的：

  1. MySQL根据 create_time 索引，从第一行开始顺序扫描。
  2. 它并不知道前100万条数据长什么样，它必须沿着索引一条一条地读，数到第1000000条。这个过程是纯索引扫描，速度尚可。
  3. 性能瓶颈的关键：为了返回 SELECT * 要求的完整数据，MySQL每发现一条数据，就需要根据主键去回表查询整行记录。这意味着它要对前100万条数据全部执行回表操作，即使这些数据最终会被丢弃。
  4. 最终，MySQL拿到第100万零1条到第10条的数据，返回给客户端。

痛点总结： 深度分页的真正代价不在于“有多少条数据被返回”，而在于“有多少条数据被丢弃，但在丢弃前又不得不进行昂贵的回表操作”。

优化原理——延迟关联（Deferred Join）：
优化后的SQL如下：

SELECT * FROM t_order
INNER JOIN (
    SELECT id FROM t_order
    ORDER BY create_time
    LIMIT 1000000, 10
) AS tmp ON t_order.id = tmp.id;

它的执行逻辑完全改变了：

  1. 子查询 SELECT id FROM t_order ORDER BY create_time LIMIT 1000000, 10 只查询了主键ID。非常重要的细节是，create_time 索引和主键id可以构成覆盖索引，这意味着整个子查询只需扫描索引树，完全不需要回表！这一步用极快的速度找到了目标页的10个主键ID。
  2. 外层查询拿着这10个ID，通过主键索引回表获取完整数据行。这次只回表了10次。

一张图看懂区别：

方案 扫描数据量 回表次数 性能
LIMIT 1000000, 10 100万 + 10 行 100万 + 10 次 极低
子查询优化 100万 + 10 行 10次 极高

这是用空间换时间的经典案例，让数据库引擎只做它最擅长的事：在索引中快速定位，而非搬运大量无用的完整行数据。

3. 应用场景

后台管理系统：

  • 场景：管理员查看交易流水、用户列表，可能直接点击“最后一页”或跳转到第500页。
  • 痛点：数据量通常在千万级，传统的LIMIT offset, size查询会导致数据库CPU飙升，接口响应超时。
  • 方案：后端使用子查询优化，前端限制最大可跳转页码或提供“筛选”功能替代深度翻页。

C端用户APP/网页的“无限下拉”/瀑布流：

  • 场景：用户浏览商品列表、朋友圈动态、资讯信息流，每次下拉到底部加载下一页。
  • 痛点：这是典型的深度分页场景，但具有连续性。用户很少会“跳过”几千条数据去看后面的，而是顺序浏览。
  • 方案：这是标签记录法的绝佳应用场景。用上一页最后一条数据的唯一递增ID或时间戳，作为下一页的查询起点，完美规避offset的使用。

数据报表导出：

  • 场景：运营需要导出上个月所有的订单数据到Excel，常常有几十万甚至上百万行。
  • 痛点：一次性加载全部数据到内存会导致应用OOM（内存溢出）。
  • 方案：采用游标分页，即使用WHERE id > last_id LIMIT 1000的方式，循环分批获取数据，边取边写入文件流，实现稳定、低内存占用的导出。

4. 使用方式

4.1 环境准备（基于MyBatis-Plus和Spring Boot）

假设我们有一个订单实体类 Order 和对应的Mapper。

// 实体类
@Data
@TableName("t_order")
public class Order {
    private Long id;
    private String orderNo;
    private BigDecimal amount;
    private LocalDateTime createTime;
    // ... 其他字段
}
// Mapper接口
@Mapper
public interface OrderMapper extends BaseMapper {
    // 方法1：原生深度分页（反面教材）
    List selectPageByOffset(@Param("offset") Long offset, @Param("size") Integer size);
    // 方法2：子查询优化
    List selectPageBySubQuery(@Param("offset") Long offset, @Param("size") Integer size);
    // 方法3：标签记录法（游标分页）
    List selectPageByCursor(@Param("lastId") Long lastId, @Param("size") Integer size);
}

4.2 方案一：子查询优化实现

Mapper XML 配置：


    SELECT *
    FROM t_order
    INNER JOIN (
        SELECT id
        FROM t_order
        ORDER BY create_time DESC
        LIMIT #{offset}, #{size}
    ) AS tmp ON t_order.id = tmp.id
    ORDER BY t_order.create_time DESC;

Service 层调用与解释：

@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;
    public List getOrdersByPage(int page, int size) {
        // 计算偏移量
        long offset = (long) (page - 1) * size;
        // 调用子查询优化方法
        return orderMapper.selectPageBySubQuery(offset, size);
    }
}

执行流程与注意事项：

  1. 执行流程：如原理部分所述，子查询快速定位ID，外层按ID回表。
  2. 注意事项：

    • 必须确保 ORDER BY 的字段上有索引，并且子查询中只 SELECT 主键，才能构成覆盖索引。
    • 如果排序字段有多列，覆盖索引也必须包含这些列加主键。
    • 此方案适用于有明确分页跳转需求（如跳到第N页）的场景，但依然有扫描前N页数据的开销，只是消除了回表开销。

4.3 方案二：标签记录法实现（推荐用于无限下拉）

Mapper XML 配置：


    SELECT *
    FROM t_order
    
            AND id 
    
    ORDER BY id DESC
    LIMIT #{size};

Service 层调用与解释：

@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;
    /**
     * 获取第一页数据
     */
    public List getFirstPage(int size) {
        return orderMapper.selectPageByCursor(null, size);
    }
    /**
     * 获取下一页数据
     * @param lastId 上一页最后一条数据的ID
     * @param size 每页大小
     */
    public List getNextPage(Long lastId, int size) {
        return orderMapper.selectPageByCursor(lastId, size);
    }
}

执行流程与注意事项：

  1. 执行流程：每次查询都从 lastId 之后直接开始扫描，完全避免了 OFFSET，性能极高且稳定，不受数据量增长影响。
  2. 注意事项（重要）：

    • 必须使用唯一且递增的字段作为游标，通常是自增主键ID或时间戳（如 create_time）。
    • 致命缺陷：不支持跳页，只能一页一页地顺序往前翻。这是它与生俱来的特性，也是它换取极致性能的代价。
    • 字段选择：如果使用时间戳，务必确保其业务层面是递增的，且并发场景下可能不唯一，会导致数据丢失。强烈推荐使用自增ID。

5. 扩展延伸

5.1 方案对比与优缺点

特性 原生 LIMIT 子查询优化 标签记录法 游标分页 (Cursor-based)
实现复杂度 极低 中 低 低
性能(深度分页) 极差 良好 极好 极好
支持跳页 是 是 否 否
数据一致性 要求高 要求高 易受新增/删除数据影响 要求高
适用场景 小数据量后台 中大型后台管理 C端无限下拉、瀑布流 数据导出、API分页接口

5.2 实际开发注意事项

  1. 数据一致性问题：在无限下拉场景中，如果用户正在浏览第2页，此时有人新增了一条数据（ID最大），那么用户刷新第3页时，可能会看到第2页的最后一条数据又出现在了第3页的开头。这是标签记录法的一个常见问题，通常可以通过不将新增数据实时插入到列表头部，或在业务上接受这种偶尔的重复来规避。
  2. 不要为了用而用：如果数据量很小（如10万以内），或者业务上根本不可能翻到几百页之后，使用原生LIMIT并配合适当的索引，完全足够。过度优化会增加系统复杂度。
  3. 组合拳：没有银弹。一个成熟的系统往往结合多种方案，例如：后台管理用子查询优化并限制最大翻页数；C端API用标签记录法；海量数据的全文搜索和复杂排序直接交给Elasticsearch处理。

以上就是在MySQL中监控和优化慢SQL的完整指南的详细内容，更多关于MySQL监控和优化慢SQL的资料请关注IT俱乐部其它相关文章！

本文收集自网络，不代表IT俱乐部立场，转载请注明出处。https://www.2it.club/database/mysql/18693.html
0赞
标签:
上一篇
下一篇

相关推荐

联系我们

联系我们

在线咨询: QQ交谈

邮箱: 1120393934@qq.com

工作时间：周一至周五，9:00-17:30，节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部