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

7. 治理实践-生产防控 慢SQL-监控和自动查杀 ◼ ⚫ 生产慢SQL治理平台在2021年上半年共发现6670条慢SQL并落实版本优化，提前避免了性能风险，治理正确率在20%左 右。 ⚫ ps.events_statements_summary_by_digest 记录了每一条 SQL 从 MySQL 开机到现在运行的计数器，比如截止到目 前运行的次数，花费的时间，扫描记录的条数 ⚫ 关注：执行时间、扫描记录数、扫描命中比例 08:00~09:00期间，平均执行时间=900/100=9秒，扫描命中比例=90,000,000/100=900000:1，存在明显的效率问题 SQL 时间 执行次数 执行时间（秒） 扫描记录数 返回记录数 SELECT * FROM T1 WHERE col=? 计算结果 08:00 200 1,800 180,000,000 200 09:00 300 2,700 270,000,000 300 08:00~09:00 100 900 90,000,000 100 自动查杀 ⚫ 联机超过阈值（例如：10秒）自动执行kill，批量联机混合时存在风险。 ⚫ 监控：ps.threads (show processlist) ⚫ 联机、批量用户分离，针对用户差异性处理 +-------+-----------+-------------------+--------+---------+------+--------+---------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+-----------+-------------------+--------+---------+------+--------+---------------------------------+ | 20338 | appuser | 192.168.1.2:39067 | pub | Query | | update | Update t1 set a=3 | | 20339 | appuser | 127.0.0.1:36455 | Sleep | 641 | | | Query | | update | Insert into t2 select * from t3 | | pub | 20340 | batchuser | 192.168.1.3:36456 | test 12 1 | NULL +-------+-----------+-------------------+--------+---------+------+--------+---------------------------------+

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!