记一次索引失效引发的Lock wait timeout exceeded排查经验

CIT
387 阅读5分钟

前言

今天想和大家聊的是关于线上Lock wait timeout exceeded问题的排查与可行的解决思路。之所以想聊这个也是因为自己在排查的过程中发现，很多问题只有自己亲自花时间去研究、去解决才有机会迎接挑战、收获经验。

问题背景

近期在浏览线上订单业务的监控页面时，发现订单创建接口耗时模块存在大量尖刺。有些调用的耗时甚至达到了50s以上。作为一名强迫症患者看着这个监控页面属实给我整急了，我立马去看了下该接口的相关代码，好奇是哪位神仙埋下的大坑（之前负责订单业务的同事换部门了bushi）。很快啊，我就啪啪打脸，创建订单的代码非常正常，没啥耗时的RPC调用、手动锁表之类的操作。到此我能猜到问题大概率出在MySQL上面，但是具体还需要进一步排查。

原因分析

针对监控中尖刺点，我首先通过尖刺点的时间在ELK日志系统上定位到了关联时间段内的日志。其中有一条日志是这样的（见图2）,显然监控上面很多次调用卡在50s左右的直接原因就是获取锁超时了。为啥一条简单的插入语句能够触发锁等待超时呢？真相只有一个，订单表被其他事务触发表级锁了。而针对Mysql，常见的引发原因有以下几种：

  1. 显式使用 LOCK TABLES 命令
  2. UPDATE 或 DELETE 语句没有使用索引
  3. 显式使用LOCK IN SHARE MODE 和 FOR UPDATE
  4. 全表扫描导致的锁

image.png

图 1.订单创建接口调用耗时监控图

image.png

图 2.订单插入接口错误堆栈图

通过一轮的代码筛查后，我将原因归结到第2种和第4种上。针对第4种吗，请看以下代码片段，这么多查询组合，都能走索引吗？显然不能。在MySQL中，当出现了索引失效的情况时，MySQL会对整张表进行扫描，逐行读取数据，检查是否符合查询条件。这种操作会极大增加查询的执行时间，特别是在数据量大的情况下，性能损耗明显。如果此时另一个事务尝试插入数据，且插入的数据会导致行级锁冲突（例如插入的数据与扫描的数据范围重叠），就可能会发生锁等待。结合具体的业务代码，问题的答案也有了：查询时索引失效引发了锁表，导致同一时刻的Insert操作锁等待异常。 
<sql id="Unions_Charging_Order_Base_Where_Condition">
    <if test="data.column_a!= null and data.column_a!= ''">
        and co.column_a = #{data.column_a}
    </if>
    <if test="data.column_b!= null and data.column_b!= ''">
        and us.column_b = #{data.column_b}
    </if>
    <if test='data.column_c!= null and data.column_c!= "" and data.column_c!= "2"'>
        and us.column_c = #{data.column_c}
    </if>
    <if test='data.column_c!= null and data.column_c!= "" and data.column_c == "2"'>
        and us.column_c in (2,12)
    </if>

    <if test="data.column_d!= null and data.column_d!= ''">
        and co.column_d = #{data.column_d}
    </if>
    <if test="data.column_e!= null and data.column_e!= ''">
        and co.`column_e` = #{data.column_e}
    </if>
    <if test="data.column_f!= null and data.column_f!= ''">
        and co.`column_f` = #{data.column_f}
    </if>

    <if test="data.column_g!= null and data.column_g!= ''">
        and co.column_g = #{data.column_g}
    </if>

    <if test="data.column_h!= null and data.column_h!= ''">
        and co.status = #{data.column_h}
    </if>
    <if test="data.column_i!= null">
        and co.column_i = #{data.column_i}
    </if>

    <if test="data.column_j!= null and data.column_j!= ''">
        <![CDATA[ and co.end_time >= #{data.column_j} ]]>
    </if>
    <if test="data.column_k!= null and data.column_k!= ''">
        <![CDATA[ and co.end_time <= #{data.column_k} ]]>
    </if>

    <if test="data.column_l!= null and data.column_l!= ''">
        <![CDATA[ and co.start_time >= #{data.column_l} ]]>
    </if>
    <if test="data.column_m!= null and data.column_m!= ''">
        <![CDATA[ and co.start_time <= #{data.column_m} ]]>
    </if>

    <if test="data.column_n!= null and data.column_n!= ''">
        and co.column_a in
        <foreach collection="data.column_n" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>

</sql>

接着我开始定位第2种情况可能发生的地方，最后在一个很隐秘的角落找到了，以下为伪代码，事务与for循环更新一起出现了，没啥好说的，反正不建议这么写，性能太差。

@Transactional(rollbackFor = Exception.class)
public Result<Boolean> batchUpdate() {
    List<DTO> dtos = ...
    for (DTO dto : dtos) {
        ...
        xxxService.updateById(dto);
    }
    return Result.ok(true);
}

总结

上面这第4种情况是一个非常典型的索引失效案例。这个查询条件的组合情况太多，在面对复杂条件查询时往往会引发全表扫描，这种全表扫描会拖慢全局的业务处理速度，极端情况下可能会引起服务的雪崩。这里重点说一下索引失效可能带来的危害:

  1. 全表扫描：

    • 如果查询条件没有匹配到索引，MySQL 会对整张表进行扫描，逐行读取数据，检查是否符合查询条件。这种操作会极大增加查询的执行时间，特别是在数据量大的情况下，性能损耗明显。
    • 全表扫描的情况通常出现在没有合适的索引，或者查询条件未能有效利用现有的索引时。

  2. 增加 I/O 开销：

    • 在全表扫描的过程中，MySQL 需要从磁盘读取大量的行数据。这会导致磁盘 I/O 增加，尤其是当表中的数据量很大时，性能会显著下降。
    • 如果数据存储在磁盘上而不是内存中，I/O 开销尤为严重。

  3. 缓存命中率下降：

    • 如果查询没有使用索引，MySQL 必须读取更多的行，导致更多的数据被加载到缓存中，进而可能影响其他查询的缓存命中率，造成缓存竞争。

  4. 锁的竞争：

    • 在高并发的环境下，如果查询没有使用索引，可能导致锁的竞争更严重。特别是在写操作较多的场景下，全表扫描可能会持有更长时间的锁，从而影响其他查询和写操作的执行效率。

  5. 查询响应时间变慢：

    • 由于全表扫描需要逐行检查数据，查询的响应时间会显著增加。特别是在需要计算聚合函数（如 COUNT, SUM, AVG）或者 JOIN 操作的情况下，性能问题更加明显。

  6. 查询优化器选择不当：

    • 如果查询语句写得不够优化（例如，条件顺序不合理或者没有显式提示 MySQL 使用某个索引），MySQL 的查询优化器可能选择不使用索引。这也可能导致查询效率降低。

说点题外话，我相信在一开始，这段代码肯定是满足正常业务需求的，只是随着业务的更新迭代，这个地方的查询条件越来越臃肿，同时伴随着业务数据量的逐年攀升，这段代码的性能问题也逐渐暴露。虽然我们可以通过添加索引的方式，尝试解决这个问题，但是终归不是一个好的解决方案。后面打算写一篇如何基于Elasticsearch+Canal+RabbitMQ的完成订单搜索，实现订单数据的读写分离。

最后如果大家觉得这篇文章能给你带来些许收获的话，麻烦给我点个赞吧。如果你有自己的独到见解，也欢迎在评论区批评指教。

avatar
文章
阅读
粉丝