工行MySQL研发管控和治理实践

如果无法正常显示，请先停止浏览器的去广告插件。

1. 工行MySQL研发管控和治理实践演讲人：魏亚东

2. 魏亚东个人介绍 ⚫ 中国工商银行软件开发中心三级经理，资深架构师。 ⚫ 杭州研发部数据库专家牵头人、开发中心安全团队成员 ⚫ 6年牵头技术管控和安全管控经验，实现业务价值的高质量快速交付 ⚫ 作为技术专家，为生产安全提供技术支持。 ⚫ 目前牵头教培、预付费等Saas产品线。

3. 风险-不可承受之痛 ◼ 业界：慢SQL是业界通病，成为各公司不可承受之痛。 ◼ 工行： ⚫ 数据库性能急剧下降，cpu占用100%，阿里在SRE团队建设与职能分工一文提及： innodb_thread_concurrency用尽，导致阿里云的ECS Open API调用量数亿/日，ECS峰值创建量交易堵塞百万/日，其管控调度系统在容量规模、极致性能、高 ⚫ 可用性等方面，面临着一系列挑战： ⚫ 主从复制时间延迟，影响RPO和RTO时效性，存在生产隐患。数据库瓶颈，顶配数据库空间仍然无法支撑业务注：涉及主备切换会检查主备的不一致，确半年发展。保备库追平主库后才做切换。 ⚫ 慢SQL数量爆发式增长，应用稳定性岌岌可危 ⚫ ⚫ 全链路预警信息最多每天200+，系统隐患逐步暴雷。读写分离存在过期读，影响数据一致性。

4. 治理实践-剧增之后如何防范风险 ◼ 免费午餐不好吃（一个馒头引发的血案）： MySQL数据库实例近8000个，云化占比90%以上，慢SQL数量呈爆发式增长，一条慢SQL就可以导致服务不可用，降低用户幸福指数。单个事务超过10万的大事务报警次数由年初单月500万次左右，逐月下降至目前100万次左右，问题收敛明显。设计门禁编码门禁测试门禁交付后设计指引规范自动化安全测试 SRE管理元数据管理 SQL注入检查性能测试慢SQL监控治理能力提升课程 SQL写法规则表结构设计工具元数据管理系统 …… 生产案例分析 AIOps根因分析 SonarLint插件慢SQL查杀 …… …… 度量评价模型集成DevOps工具链（线上） +QA定期检查（线下） …… 流水线

5. 治理实践-方法论 ◼ 规范-万事开头难，体系必须要建立方法论基石操作：方法论 ⚫ 每个表必须建立主键 ⚫ 禁止给库、表、字段单独设置字符集/排序规则 ⚫ 。。。量化：精细化的理性思维 ⚫ 扫描命中比（联机rows_examined:rows_sent<100:1） ⚫ 事务大小（undo<10万） ⚫ 。。。避坑：规避MySQL Bug ⚫ 大表truncate改为drop + create table (bug:68184) ⚫ 禁用replace into (bug:73563) ⚫ 。。。易理解：知其然知其所以然

6. 治理实践-确保可落地 ◼ 重自动化轻指引的质量门禁-无法自动管控的规范实际上相当于无法落实 ⚫ 基于druid，扩展Sonarqube的插件，实现mapper.xml的扫描分析，实现本地检查规则与云端同步。 ⚫ 27条规则，逐步完善丰富，涵盖常见错误， ID 优先级备注不应在建表DDL脚本中指定表级的DEFAULT CHARSET、 M001 COLLATE参数和字段级的CHARSET、COLLATE参数。规则中文说明严重《基于MySQL数据库的应用开发技术规范》 M002 不应在建表DDL脚本中指定表级的ENGINE。严重《基于MySQL数据库的应用开发技术规范》 M003 Insert语句应该包含字段列表主要 insert into table(col1,col2) values(1,2) 在table后应包含 col1,col2，避免因表结构变动导致SQL语句运行出错。 M004 请勿使用replace into 阻塞《基于MySQL数据库的应用开发技术规范》 M005 GROUP BY结果,MYSQL默认进行排序,如果不需要排序,建议增加 ORDER BY NULL,以提升性能次要《基于MySQL数据库的应用开发技术规范》 M006 MySql的Update语句的SET中，出现and可能是一个问题，分割更新值应该用逗号严重生产问题：update tab1 t set t.name=null and t.id=2 ,mysql会认为null and t.id=2是一个逻辑表达式 M007 请勿使用Order by rand() 严重《基于MySQL数据库的应用开发技术规范》应避免在联机操作中使用truncate，应使用CREATE+RENAME M008 或EXCHANGE PARTITION方式，规避触发BUG#68184阻塞其他所有交易阻塞《基于MySQL数据库的应用开发技术规范》请勿使用=、!=、<>,null值进行比较，返回将永远为false，应使用is null或is not null。严重注:MYSQL方言中存在安全等于<=> 可以支持NULL值的正常对比 M010 如果想表示空应该用NULL，可能错误的使用了'NULL'字符串主要如果想表示空应该用NULL，可能错误的使用了'NULL'字符串请勿使用group by col asc/desc的mysql方言形式,请替换为 M011 group by col order by col asc/desc 严重注：根据现有资料mysql 8.0将不再支持该语法 M012 语句嵌套层数不宜超过太多层主要 MySql开发规范规定为3层，可以通过规则的max参数配置 M009

8. 治理实践-生产防控 ◼ 大事务的纳什均衡-监控和自动查杀危害 ⚫ binlog写入、传输、回放缓慢問題：24小时无法回放 ⚫ 交易写入堵塞 ⚫ 主库故障博弈，切 VS 不切？ ⚫ 。。。 1.提交COMMIT 7.返回成功 2.写入 binlog binlog 3.DUMP线程 4.IO线程 SQL线程 6.Engine Commit 5.ACK Master（主库）自动查杀 ⚫ 监控：show engine innodb status\G Relaylog Slave（备库）一个事务还没有结束，会提示这个事务更新的记录数 ⚫ 超过阈值（10万？1万？）自动执行kill规避“一刀切”，小范围试点-》全面推广 ---TRANSACTION 3236, ACTIVE 84 sec inserting, thread declared inside InnoDB 4882 mysql tables in use 1, locked 1 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 3245768 MySQL thread id 13535, OS thread handle 139673278408448, query id 40827 localhost root executing load data infile '/data/linzx/tmp/20190329_big_trx/test.txt' into table tt.t_big_tx_load (a)

9. 治理实践-SRE管控体系生产应急运维分析牵头生产应急响应研发阶段分析确认运维效果 ⚫ 协助处理紧急生产问题， ⚫ 建立测试和生产巡检机制，通过每日巡检、重要节日深审核相关变更方案，确保规范升级研发流程 ⚫ 需求分析阶段提交待提升项给产品经理，落实版本计划问题闭环，形成总结文档度巡检、投产前风险评估多（包含影响、结果、待提层次巡检保障生产运行安全。 ⚫ 设计阶段指导架构师完成非升项、做的好的地方、处 ⚫ 观察生产运维效果情况，确认SLI、SLO和SLA等是否满理时间线），定期更新 “发布CheckList”。 ⚫ 牵头重点问题复盘，组织安排。功能性需求评估，涉及监控预警、灰度方案、发布方案、足设定目标，辅助产品经理安全可信、应急预案、SLI等；形成运维分析报告。以未来视角指导架构师完成专项排查治理，涉及性能 ⚫ 组织建设进阶要求的相关配套工具，包括质量门禁、白容量、账务一致性、分布性能和容量规划；指导架构式体系等，确保范围无疏名单管控、代码扫描、自动提供可执行的路线路，推进漏，制定方案、审核整改运维平台和全息监控平台的架构转型；做好标准化方案计划。数据分析等。和组件。师形成技术架构的未来规划，发布阶段保障应用发布稳定性 ⚫ 发布阶段按照“发布核对清单”对相关实现情况进行核对勾选，建立有形资产。

10. 未来-畅想 ◼ 数据采集以及未来趋势 [ 常规数据 ] [ 高密度采集 ] ⚫ CPU ⚫ ps.threads ⚫ 内存 ⚫ show engine innodb ⚫ IO、 ⚫ 网络 ⚫ QPS ⚫ 连接数 ⚫ ⚫ [ 低密度采集 ] ⚫ ps.events_statements_summary_by_dig est status ⚫ ps.events_statements_history 。。。 ⚫ 。。。。。。 1分钟定位 5分钟预判 10分钟自愈

11. THANK YOU!