SQL优化 | MySQL问题处理案例分享三则
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL优化 | MySQL问题处理案例分享三则，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2908字，纯文字阅读大概需要5分钟。
内容图文
分享几则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文本结构如下：
| 1 |
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 = xxand b.字段2 = c.字段2??and?a.字段1?=?c.字段1and 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逻辑总是最高效最健壮的。
内容总结
以上是互联网集市为您收集整理的SQL优化 | MySQL问题处理案例分享三则全部内容，希望文章能够帮你解决SQL优化 | MySQL问题处理案例分享三则所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。