线上慢查询优化

发表于 更新于 分类于 ， 阅读次数：

1、背景

告警页面由APP+ H5开发转纯H5页面优化

好处

  1. 开发与维护需要服务端+APP+H5同时开发，对于项目管理复杂
  2. 转成H5可以减少APP的开发压力

1.1、问题现象

  1. 慢查询16K/天

    2e714778e23510985c77506bd312df31

  2. 线上MySQL CPU有激增现象，不过缓存命中率，内存利用率无明显变化

    dad9dd59dcd8a89c4f7740769393f056

    bdb64a65f6f6b66224f0433610086d82

2、排查方式

结论先行，以下是该列表的优化流程

flowchart 
    A[有先查看MySQL优化建议,依据智能优化作为基础优化方式] -->B(分析慢查询与CPU关系,发现解决慢查询即可优化CPU问题)
    B --> C[分析慢查询统计,排查该优化哪个慢查询sql,对应到Java代码]
    C --> D[explain分析具体优化点]

  1. 直接使用购买MySQL实例的腾讯云上面的智能优化

    可以优先查看AI的优化建议进行快速修复的凭证

    87a79fd8faf9089e282428144daccbfe

  2. 查看慢sql的统计情况，发现慢sql和CPU趋势较为相同

    这说明如果优化了慢查询，则MysqlCPU较高问题也可以解决

    994a8ff4419f284e41339ad008f0d530

  3. 导出慢查询日志，进行sql统计分析，发现其中99%以上是开发的告警信息列表查询语句

    这表明优化单一告警即可优化Mysql的慢查询

  4. 在线上环境使用explain对查询语句进行查看，发现以下现象：

    partition占用较多——打算减少partition

    type为range，不是ref、eq_ref、const、system等——怀疑索引效果差，需要优化索引

    key有值——有索引

    rows大，且filtered较小，只有2%——扫描200条数据需要进而扫描大量条数数据，不合理，需修改查询条件

    extra——有Using filesort，排序未触发索引，需优化索引

    4873f5fb0e88d3f8c86bf327d2fd566e

  5. 随机查看其他用户，发现告警量大的用户稳定出现慢查询，告警总量大约是20W-50W，告警量少的用户，即使是跨分区也不会出现慢查询

3、解决方式

3.1、自己可以修改的

  1. 改变查询条件顺序，减少查询条件

    expire_time >now()——删除，因为每天会将超过过期时间的数据删掉

    alarm_time between and——需要放在最后，因为联合索引在>,<,between and后会失效

    设备id ——放到稍微靠前的位置，因为该字段在线上验证时，发现该字段独立度更高，如id 是114514时占总数据的1-5%，而告警类型2000可以占10%左右，所以设备id需要放在比告警类型更靠前的位置

  2. 优化联合索引顺序，个数

    app的最常见查询条件作为联合索引的个数与顺序

    通过腾讯云迁移数据到测试环境数据库验证解决

    迁移单个表，单表200w数据，去其中的告警量50w单个用户进行压力测试

  3. 尝试增加资源——MySQL实例已经是购买的最贵的实例，无法优化

  4. paritition删除——删除会导致线上长时间停顿20min以上，且涉及到删除过期数据逻辑，无法优化

3.2、需要配合的

产品经理

  1. 重构时默认查询7天，涉及多个partition，可不可以改成默认查询1天——为了在功能上对标海康，大华，不能进行修改

app

  1. 缺少偏移量，期望入参增加偏移量，避免后续出现的深分页问题——app暂时没人力投入，不进行修改

4、问题回溯

  1. 新问题单开发同事开发时直接在原有查询中间添加查询条件

  2. 以下为测试环境压力测试时CPU，内存图片，无问题

    推测可能是因为测试环境压测数据量不够导致问题

    为什么没有准备合适数据，原因为慢查询多为数据量较少用户

    22b3ee0971451c532addaba0d71b9b62

    80f718900c5797ba180e97d6b72d6f36

5、优化效果

  1. 慢查询explain优化

    filtered查询行数降低，筛选率变高

    extra没有using filesort

    type虽然还是range，但是实际上速度已经可以来到200ms左右，顾先不优化，因为本次目的是减少慢查询降低MySQL压力

    image-20250706185239859

  2. 每日慢查询数量降低，从16k/天 -> 500/天

    b658a861b656ca8040d7f77e3f480cc7

6、后续优化

  1. APP侧辅助修改提问题单
  2. 后续排查发现告警有子模块呼叫查询，也会使用tbl_alarm表，为了减少单表压力，提单将对应查询优化到专门存储呼叫的表中进行查询

7、推荐阅读

  1. Mysql Documentation
  2. MySQL documentation explain