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优化案例。

解决过程：数据库整体负载压力较大，分析慢日志优化了部分性能较差的SQL后有明显改善，此处列举出一个比较典型的优化案例：某功能模块更新文档阅读数的一个定时任务，原SQL执行时间稳定在5秒左右。SQL文本结构如下（将就着看）：

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

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

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

 

posted on 2021-03-25 13:26  数据与人文  阅读(10)  评论(0)  编辑  收藏