◆背景介绍
2020年6月，商品系统从SAP、中间层等接入的商品数据越来越多且更新频繁，商品数据库主从更新数据量大，约每分钟54万多条更新，约八分钟就会产生大于1G的Binlog文件，在数据库IO能力一定的情况下，发生数据同步延迟，影响写入与读出的及时性，进而影响到商品基础系统的可用性。
如果仅是从翻阅代码的角度去分析，会花费大量人力。抛开系统本身，当商品多个应用都在读写商品库，并在数据库层起到数据汇总和集中反馈的情况下，分析这个点是一个较好的方向。
◆分析模型
把Binlog解析成Sql 纯文本，解析出来的Sql文本 样例如下：
观察解析出来的文本，判断是否是有效更新，关键在于找到实际更新的是哪些字段。即: WHERE块中 和SET块 中 哪些字段上有值的更新。
通过对比发现：
实际只更新了 第 7 和 8 号 的时间字段，如果没有刷新其他值，只是刷新了时间，估计这条更新语句没什么意义；又，商品系统接收到下发数据时，估计是我们拿到了数据后，直接用 modified=now()产生了update语句，这样时间有变化，必然产生Binlog。
基于以上逻辑，只要能分析出一条update语句中，哪些字段更新了，这些更新字段本身对业务是否有意义，来判断是否应该产生Binlog。希望分析出的结果模型如下：
通过分析表X中，字段组合 以及相应的 更新次数，可以得出：
1. 更新的 字段组合 判断出是否是有效更新；
2. 相应的 更新次数 反映出有效和无效更新的条数、占比程度等；
◆组合统计
由于Binlog对应的文本量大，通过脚本把每条 Insert 、Delete、Update语句切到单独的文件中。然后计算每个文件中的Sql 里的字段差异并做归集。流程如下:
统计每张表的每个字段单独更新是否有效：1表示单独更新有效，0表示单独更新无效，如以下统计样例表:
根据以上统计表，计算每种更新语句的“更新是否有效表达式”，计算方法为：
每种更新语句的更新字段组合 的 单独更新是否有效 的数值 做加法。
如果更新字段组合中，所有字段的单独更新是否有效的值为0，则加起来的和等于0；
只要更新字段组合中，某一字段的单独更新是否有效的值为1，则加起来的和大于0；
根据 “更新是否有效表达式”的和是否大于0 得出对应的更新语句是否是 有效的更新。
以商品库表以及字段为例，判断更新语句是否有效，统计表参考如下：
根据以上统计表，无效更新的占比为：sum(无效更新=0的更新量)/sum(更新量)。结果模型举例如下图：
这样为分析提供：定性与定量的基础，并且知道后续优化的预期效果。商品系统经过优化，减少了90%以上的无效更新，并且减少了下游系统订阅商品Binlog的数据更新压力。
◆代码排查
初步分析是我们拿到数据后，在SqlMapper文件中: 时间字段=now()，触发了实际更新产生binlog，根据实际情况修改排查，确认是否对下游系统有影响，如大数据抽数等情况做优化。
商品系统初步的修改方法为：
1.建表时，建议按照以下格式设计：
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
这样做的好处是:Insert时，created字段不需要设置；update时，modified字段不需要设置，让业务代码更纯粹，同时也不用担心数仓拉数据的时候没更新时间；
2. 语句中，update * set * where * ，如果set 块中没有更新数据，set 和 where 直接连在一起，Sql语法会错误，在set 里面加上where 块中的条件字段，如id=#id#, 这样让 Sql 语句语法正确。另外也遵循如果没有实际的数据更新，不会产生binlog的规律；
◆其他引申
用以上的结果模型，清晰地展示出一个库的更新情况，不仅能判断出Sql语句是否是有效更新，也能反馈以下些情况：
1. 数据库设计是否合理，比如在分析我们公司的XX系统的数据库后，结论是：更新都是有效更新，但更新量最大的一张表有98个字段，且更新量最大的部分，只更新了表的 yn字段，由Binlog解析出来的纯文本可知，即使只更新yn字段，也会在SET和WHERE块中带上这98个字段，合计约200多行的更新语句，也触发了大量Binlog文件产生；后面计划通过拆分表字段，通过归类数据字段、状态字段等方式来解决。
2. 给 缓存数据 提供定量依据，比如频繁更新字段，是否能用缓存的启发等；
3. 给系统负责同学提供数据库更新字段维度的透视，知道数据库实际更新了哪些字段，有无必要，还可以做哪些优化启发等；
以上通用的分析方法，特别适合于数据库更新量大的系统，以及通用的脚本分析工具快速出分析结果。
来源：
https://www.toutiao.com/a7073819654254838272/?log_from=fdcad4e826508_1647828163849
“IT大咖说”欢迎广大技术人员投稿，投稿邮箱：aliang@itdks.com
来都来了，走啥走，留个言呗~
IT大咖说 | 关于版权
由“IT大咖说（ID：itdakashuo）”原创的文章，转载时请注明作者、出处及微信公众号。投稿、约稿、转载请加微信：ITDKS10（备注：投稿），茉莉小姐姐会及时与您联系！
感谢您对IT大咖说的热心支持！
- 相关推荐
- 推荐文章
- 简单说说ES6新特性
- Redis 中使用 list,streams,pub/sub 几种方式实现消息队列
- 新一代多系统启动U盘解决方案
- 架构师学习笔记之：并发编程（图解原子操作）
- 容器管理的 9 个最佳 Docker 替代方案
- Redis 中如何保证数据的不丢失，Redis 中的持久化是如何进行的
- JPG 与 JPEG：这些图像文件格式有什么区别？
- JavaScript 中 JSON 的 5 个小技巧
- QingLong - 强大的定时任务管理面板
- MySQL主从复制问题总结及排查过程分享
