生产环境 800 万行大表加索引，不停服不锁表的完整操作

生产环境 800 万行大表加索引不停服不锁表的完整操作背景仓储系统上线半年shipment_records表积累了 800 万行数据。业务方反馈按carrier_id承运商查询很慢需要加索引。直接执行ALTERTABLEshipment_recordsADDINDEXidx_carrier_id(carrier_id);测试环境跑了一下耗时 14 分钟。生产环境数据量更大这 14 分钟里所有对这张表的写操作会被阻塞等同于停服。先搞清楚ALTER TABLE 为什么会锁表MySQL 5.6 之前ALTER TABLE 的执行过程是创建一张新的临时表把原表数据全量复制到临时表在复制期间原表禁止写入复制完成后临时表重命名替换原表800 万行数据复制过程中写操作全部阻塞这就是锁表的来源。MySQL 5.6 的 Online DDLMySQL 5.6 引入了 Online DDL加索引时默认使用ALGORITHMINPLACE, LOCKNONE理论上不锁表ALTERTABLEshipment_recordsADDINDEXidx_carrier_id(carrier_id),ALGORITHMINPLACE,LOCKNONE;听起来很美好但有几个坑坑一期间的写操作会积压在 row log 里如果 DDL 跑的太慢row log 写满会报错回滚ERROR 1799 (HY000): Creating index idx_carrier_id required more than innodb_online_alter_log_max_size bytes of modification log坑二DDL 开始和结束的瞬间仍然需要短暂的 MDL 锁元数据锁如果此时有长事务在跑MDL 会被阻塞后续所有请求跟着排队。坑三大表 DDL 期间 CPU、IO 压力大可能影响正常业务响应时间。所以生产环境特别是数据量大、写入频繁的表不建议直接用 ALTER TABLE而是用工具。使用 pt-online-schema-changept-online-schema-change简称 pt-osc是 Percona Toolkit 里的工具专门解决大表在线 DDL 问题。工作原理创建一张和原表结构相同的影子表_shipment_records_new在影子表上添加新索引在原表上创建触发器把期间的增删改同步到影子表分批把原表数据复制到影子表可控速度不影响业务数据同步完成后瞬间原子重命名影子表替换原表删除原表和触发器整个过程原表始终可读可写对业务几乎无感知。安装# Ubuntu/Debianapt-getinstallpercona-toolkit# CentOS/RHELyuminstallpercona-toolkit# 验证安装pt-online-schema-change--version执行命令pt-online-schema-change\--host127.0.0.1\--port3306\--userroot\--passwordyour_password\--databasewms\--tableshipment_records\--alterADD INDEX idx_carrier_id (carrier_id)\--chunk-size1000\--max-loadThreads_running50\--critical-loadThreads_running100\--check-interval5\--print\--execute参数说明参数说明--chunk-size1000每批复制 1000 行可根据服务器性能调整--max-load当前负载超过阈值时暂停复制保护业务--critical-load负载超过此值时直接终止防止雪崩--check-interval5每 5 秒检查一次负载--print打印执行过程方便观察--execute真正执行不加这个参数是 dry-run 模式强烈建议先不加--execute跑一次 dry-run确认命令正确再加上去。执行过程输出示例Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering wms.shipment_records... Creating new table... Created new table wms._shipment_records_new OK. Altering new table... Altered wms._shipment_records_new OK. 2024-03-15T14:23:11 Creating triggers... 2024-03-15T14:23:11 Created triggers OK. 2024-03-15T14:23:11 Copying approximately 8000000 rows... 2024-03-15T14:23:16 Copied rows OK. 2024-03-15T14:37:45 Swapping tables... 2024-03-15T14:37:45 Swapped original and new tables OK. 2024-03-15T14:37:45 Dropping old table... 2024-03-15T14:37:46 Dropped old table wms._shipment_records_old OK. Successfully altered wms.shipment_records.整个过程跑了约 14 分钟但业务全程无感知写入正常。操作前必做的检查清单1. 确认没有外键引用pt-osc 对有外键的表处理复杂需要额外参数SELECT*FROMinformation_schema.KEY_COLUMN_USAGEWHEREREFERENCED_TABLE_NAMEshipment_records;2. 确认没有触发器pt-osc 依赖触发器工作原表上已有触发器会冲突SHOWTRIGGERSLIKEshipment_records;3. 检查磁盘空间pt-osc 会创建一张和原表差不多大的影子表磁盘剩余空间需要大于原表的 1.5 倍SELECTtable_name,ROUND((data_lengthindex_length)/1024/1024,2)ASsize_mbFROMinformation_schema.tablesWHEREtable_schemawmsANDtable_nameshipment_records;4. 低峰期执行即使 pt-osc 不锁表复制数据仍然会消耗 IO建议在业务低峰期凌晨执行。执行后验证-- 确认索引已存在SHOWINDEXFROMshipment_records;-- 确认原来的慢 SQL 走了新索引EXPLAINSELECT*FROMshipment_recordsWHEREcarrier_idSF001;-- 确认表数据量没有变化SELECTCOUNT(*)FROMshipment_records;总结方案是否锁表适用场景直接 ALTER TABLE锁表时间长测试环境、小表、可停服Online DDL (ALGORITHMINPLACE)基本不锁但有坑写入量不大的表pt-online-schema-change不锁表最安全生产环境大表首选生产环境大表 DDLpt-osc 是目前最成熟的方案。操作前做好检查清单低峰期执行全程观察输出日志出了问题 CtrlC 随时中止不会影响原表数据。