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

分享几则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 表Afrom  表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。

问题三、问题描述:再来看一个慢SQL优化案例。

):

查看它的执行计划:可以发现,此处的in条件中,MySQL选用了全表扫描的方法进行匹配,字段的单列索引是有的, 与开发人员沟通后,in中的结果不会很大,我们可以将SQL进行分离:

将in的条件单独拿出来查询,然后将获取到的结果拼接到后面的SQL中对比执行计划,此时SQL执行速度可以达到毫秒级别

总结:SQL的逻辑越简单越好,应尽量的简化SQL逻辑,减少这种嵌套,SQL拼接的操作,尽量把一个大的SQL剥离成小的SQL去运行,不同数据库对SQL的执行计划有出入,越复杂的SQL带过来的隐患就越大,简洁的SQL逻辑总是最高效最健壮的。