SQL优化 | MySQL问题处理案例分享三则

数据与人 2021-03-24

2309

点击上方"数据与人", 右上角选择“设为星标”

分享干货，共同成长！

分享几则MySQL问题处理案例，聊聊我的思路。处理问题的思路和角度各有不同，希望这篇文章可以抛砖引玉。

问题一、

问题描述：

某功能模块涉及保存和提交类操作慢，反映到数据库环境为DML操作普遍偏慢。

处理过程：

排查MySQL数据库发现所有涉及批量插入的功能都有性能问题，数据库选用的是微软云的RDS，对比生产环境和测试开发环境，生产环境的硬件配置比测试开发环境高很多，插入反而更慢。

尝试使用脚本验证两个环境的插入速度：

同样的网络环境，插入3万条数据，测试环境用时6s，而生产环境用时9s；

对比两个环境数据库参数的差异发现生产环境开启了binlog，而测试环境未开启：

经与云厂商确认后，生产环境有灾备和自动备份的功能，开启此功能默认需要打开binlog，而厂商为了确保数据的完整性，将sync_log的值设置为1，即每一个事务都需要刷新数据到磁盘，这样就导致数据库的dml操作性能下降很多。

总结：

开启binlog之后建议合理规划以下两个参数的值来提高数据库性能：sync_binlog = 0 ##控制多少事务刷新一次binlog，0代表由文件系统控制。innodb_flush_log_at_trx_commit = 2 ##控制log buffer的罗盘机制，默认1s刷新一次。

以上的设置可以使用缓存机制，增加数据库插入和修改的速度，但是会带来一定的风险，服务器意外宕机可能会丢失部分缓存中的数据。

问题二、

问题描述：

慢SQL导致数据库CPU告警

解决过程：

某功能模块慢SQL导致系统卡死，且SQL执行频率较高。

到MySQL数据库发现如下SQL严重阻塞：

SQL文本结构如下：

delete 
from 
 表A 
where a.字段1 in 
  (select b.字段1 
   from 表B ，
        表C 
   where b.字段2=xx 
   and b.字段2=c.字段2 
   and c.字段3=0）

此SQL在功能上循环调用执行，效率极差。先从索引层面优化，表B/C都缺失索引，删除的效率极低。

增加如下索引：

create index idx_name ON 表B(字段2);
create index idx_name ON 表C(字段3);

通过添加索引当然能有效的优化SQL执行效率。我们再来看一下SQL的逻辑，这么简单的逻辑有必要搞个子查询吗？来尝试修改一下SQL写法，修改后如下：

delete 表A
from
  表A ,
  表B ,
  表C 
where b.字段2 = xx
  and b.字段2 = c.字段2
  and a.字段1 = c.字段1
  and c.字段3 = 0

修改后的SQL（0.5s以内）

总结：

通过扫描SQL代码发现较多的SQL开发人员习惯使用exists和in的逻辑来过滤数据，但是在MySQL中，exists的性能并不是最高的，即使在字段存在索引的情况下，在结果集比较大情况下，exists的检索速度远不如inner join的hash连接，而且过多的使用exists容易导致SQL的执行计划异常，而inner join逻辑相对更加直接，简化。

我推荐的优先逻辑：join > exists > in。