MySQL加个索引都可能丢数据，这个坑你知道吗？

数据库性能诊断DBdoctor

已于 2024-08-28 11:36:36 修改

阅读量724

点赞数 28

分类专栏： DBdoctor 文章标签： mysql 数据库 dba 性能洞察运维 java pt-osc

于 2024-07-03 11:20:50 首次发布

本文链接：https://blog.csdn.net/DBdoctor_off/article/details/140147305

版权

DBdoctor 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

3）如果pt-osc在无触发器状态，进程最终完成了，会丢数据吗？

4）最终原生的OnlineDDL变更完成

总结

前言

近期，我们收到一位数据库运维小伙伴的咨询，他们有一个MySQL 5.6的数据库，需要对核心支付表做DDL加索引，咨询我们如何加索引更优雅。基于DBA经验，给表添加索引主要有以下几种方式：

用MySQL原生的DDL语句（包括OnlineDDL）
用pt-osc，新建临时表+触发器来实现
用gh-ost，新建临时表+基于binlog来实现
Session级别关闭Binlog，备库先加索引，然后做主备切换

具体选择那种方式需要综合考虑表结构、主备延时、业务写入负载、磁盘IO、是否影响业务、使用习惯等多方面因素。经过我们综合评估该支付场景使用原生的OnlineDDL更佳，但该小伙伴反馈他们的运维流程要求DDL变更必须使用pt-osc，迫于流程最终还是使用pt-osc添加索引。然而接下来运维小伙伴的操作差点让公司发生巨大损失，幸好使用了DBdoctor性能洞察功能提前发现问题并及时止损。

pt-osc原理，有坑吗

1）pt-osc原理

pt-osc的大致方式是通过创建一个临时表，然后按照主键chunk分批方式，拷贝源表中数据，同时通过三个写相关触发器来控制增量数据实时写入到临时表中，达到与源表最终的数据一致，最终rename交换。从原理上来看，实现逻辑比较简单。那么该原理对业务有损吗？

2）运维评估pt-osc是否符合要求

在从pt-osc的实现，我们能直接看出有以下问题：

创建临时表，会导致空间翻倍，需要预留足够空间
触发器的存在，会导致写入翻倍，需要确保磁盘IO能支撑
写入增加会导致主备存在延时
存在死锁导致业务事务回滚

经过运维同学人员的详细评估，基于当前业务量，前三点不会有问题，第四点当前MySQL的innodb_autoinc_lock_mode参数为2，不会产生自增锁导致的死锁问题。所以运维同学评估是可以直接在线上通过pt-osc来添加索引。

3）线上变更，踩坑死锁了

在线上进行变更过程中，发现业务发生了死锁，下面是业务的详细死锁日志：

LATEST DETECTED DEADLOCK-----------------------2024-06-26 02:25:20 7fe147aa4700*** (1) TRANSACTION:TRANSACTION 2653761553, ACTIVE 0 sec insertingmysql tables in use 2, locked 2LOCK WAIT 7 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 3MySQL thread id 143920619, OS thread handle 0x7fe146cad700, query id 7056859581 updateREPLACE INTO `pay`.`_pay_info_new` (`pay_info_id`, `at_date`, `at_progress`, `ag_no`, `bs_status`, `buyer_id`, `contract_code`, `contract_id`...*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 169 page no 507288 n bits 288 index `Record lock, heap no 214 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 8; hex 80000000096dd335; asc      m 5;; 1: len 8; hex 800000000009edf2; asc         ;;
*** (2) TRANSACTION:TRANSACTION 2653761554, ACTIVE 0 sec insertingmysql tables in use 2, locked 26 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 3MySQL thread id 144763335, OS thread handle 0x7fe147aa4700, query id 7056859583 updateREPLACE INTO `pay`.`_pay_info_new` (`pay_info_id`, `at_date`, `at_progress`, `ag_no`, `bs_status`, `buyer_id`, `contract_code`, `contract_id`...*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 169 page no 507288 n bits 288 index `pay_info_id` of table `pay`.`_pay_info_new` trx id 2653761554 lock_mode X locks rec but not gapRecord lock, heap no 214 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 8; hex 80000000096dd335; asc      m 5;; 1: len 8; hex 800000000009edf2; asc         ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 169 page no 507288 n bits 288 index `pay_info_id` of table `pay`.`_pay_info_new` trx id 2653761554 lock_mode X waitingRecord lock, heap no 214 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 8; hex 80000000096dd335; asc      m 5;; 1: len 8; hex 800000000009edf2; asc         ;;
*** WE ROLL BACK TRANSACTION (2)------------