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

面试官考点分析：

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

1. 标准回答

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

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

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

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

2. 核心原理

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

假设我们有一张用户订单表 t_order，在 create_time 上建立了索引，但查询语句是 select * from t_order order by create_time limit 1000000, 10。

执行流程是这样的：

mysql根据 create_time 索引，从第一行开始顺序扫描。
它并不知道前100万条数据长什么样，它必须沿着索引一条一条地读，数到第1000000条。这个过程是纯索引扫描，速度尚可。
性能瓶颈的关键：为了返回 select * 要求的完整数据，mysql每发现一条数据，就需要根据主键去回表查询整行记录。这意味着它要对前100万条数据全部执行回表操作，即使这些数据最终会被丢弃。
最终，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;

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

子查询 select id from t_order order by create_time limit 1000000, 10 只查询了主键id。非常重要的细节是，create_time 索引和主键id可以构成覆盖索引，这意味着整个子查询只需扫描索引树，完全不需要回表！这一步用极快的速度找到了目标页的10个主键id。
外层查询拿着这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<order> {
    // 方法1：原生深度分页（反面教材）
    list<order> selectpagebyoffset(@param("offset") long offset, @param("size") integer size);
    // 方法2：子查询优化
    list<order> selectpagebysubquery(@param("offset") long offset, @param("size") integer size);
    // 方法3：标签记录法（游标分页）
    list<order> selectpagebycursor(@param("lastid") long lastid, @param("size") integer size);
}

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

mapper xml 配置：

<!-- 方法2的实现：子查询优化 -->
<select id="selectpagebysubquery" resulttype="com.example.entity.order">
    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;
</select>

service 层调用与解释：

@service
public class orderservice {
    @autowired
    private ordermapper ordermapper;
    public list<order> getordersbypage(int page, int size) {
        // 计算偏移量
        long offset = (long) (page - 1) * size;
        // 调用子查询优化方法
        return ordermapper.selectpagebysubquery(offset, size);
    }
}

执行流程与注意事项：

执行流程：如原理部分所述，子查询快速定位id，外层按id回表。
注意事项：
- 必须确保 order by 的字段上有索引，并且子查询中只 select 主键，才能构成覆盖索引。
- 如果排序字段有多列，覆盖索引也必须包含这些列加主键。
- 此方案适用于有明确分页跳转需求（如跳到第n页）的场景，但依然有扫描前n页数据的开销，只是消除了回表开销。

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

mapper xml 配置：

<!-- 方法3的实现：标签记录法 -->
<select id="selectpagebycursor" resulttype="com.example.entity.order">
    select *
    from t_order
    <where>
        <if test="lastid != null">
            and id < #{lastid}  -- 使用大于号还是小于号取决于排序方向，这里假设按id降序
        </if>
    </where>
    order by id desc
    limit #{size};
</select>

service 层调用与解释：

@service
public class orderservice {
    @autowired
    private ordermapper ordermapper;
    /**
     * 获取第一页数据
     */
    public list<order> getfirstpage(int size) {
        return ordermapper.selectpagebycursor(null, size);
    }
    /**
     * 获取下一页数据
     * @param lastid 上一页最后一条数据的id
     * @param size 每页大小
     */
    public list<order> getnextpage(long lastid, int size) {
        return ordermapper.selectpagebycursor(lastid, size);
    }
}

执行流程与注意事项：

执行流程：每次查询都从 lastid 之后直接开始扫描，完全避免了 offset，性能极高且稳定，不受数据量增长影响。
注意事项（重要）：
- 必须使用唯一且递增的字段作为游标，通常是自增主键id或时间戳（如 create_time）。
- 致命缺陷：不支持跳页，只能一页一页地顺序往前翻。这是它与生俱来的特性，也是它换取极致性能的代价。
- 字段选择：如果使用时间戳，务必确保其业务层面是递增的，且并发场景下可能不唯一，会导致数据丢失。强烈推荐使用自增id。

5. 扩展延伸

5.1 方案对比与优缺点

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

5.2 实际开发注意事项

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

以上就是在mysql中监控和优化慢sql的完整指南的详细内容，更多关于mysql监控和优化慢sql的资料请关注代码网其它相关文章！