凌晨1点40分我被DBA值班电话吵醒。客户在Oracle那边执行了一条ALTER TABLE PAY_ORDER MODIFY (AMOUNT NUMBER(14,2))几毫秒就返回了同一套业务需求在MySQL这边执行ALTER TABLE pay_order MODIFY amount DECIMAL(14,2)一执行业务立刻开始堆告警进程列表里全是Waiting for table metadata lock。两块库一个秒改一个锁死。当时我就想这个问题值得好好写一写同样是精度扩展这种看起来人畜无害的DDLMySQL和Oracle的表现为什么差这么多本文就把这个对比拆开讲清楚包括算法原理、阻塞根源、实操方案以及我踩过的一些坑。1. 同样是改精度两边的表现为什么天差地别1.1 MySQL这边发生了什么MySQL里的改精度最常见两种一种是把VARCHAR(50)加长到VARCHAR(100)另一种是把DECIMAL(10,2)变成DECIMAL(12,2)。这类操作从业务角度理解就是给字段挪个更大一点的盒子数据一条都不用动理应很快。但MySQL的实际行为是在8.0.12之前绝大多数这类操作都会触发表重建。InnoDB会按新的表定义创建一个临时表然后一行一行把旧数据拷进去拷完再删旧表换新表。几千万行的表拷一遍就是几十分钟甚至几小时的大事。更麻烦的是整个拷贝过程里DML操作能不能并发执行、会不会被阻塞取决于走的是哪种算法。很多老版本走COPY算法插入、更新、删除全部排队业务直接卡死。即使到了MySQL 8.0情况也没好到哪去DECIMAL精度扩展依然是不支持INSTANT算法的InnoDB只能选择重建表的路子。重建表期间就算理论上允许并发DMLMDL锁的排队机制也会在DDL开始和结束的瞬间把大量请求堵在门外。这就是生产环境最常见的一个ALTER拖垮整个库的真相。1.2 Oracle这边发生了什么同一个需求放到Oracle上完全是另一套逻辑。ALTER TABLE PAY_ORDER MODIFY (AMOUNT NUMBER(14,2))这句话Oracle做的是数据字典层面的更新把列定义里的精度从10改成14仅此而已。存量数据行一字节都不用改更不用扫描全表。Oracle为什么敢这么干因为它把精度声明和数据存储格式解耦了。NUMBER类型在行内是变长存储实际占用多少字节取决于这一行存的值本身跟定义的精度没有直接关系。声明精度更大只是放宽了一个约束已存在的行完全不受影响。所以Oracle在很多场景下改精度是秒级操作DML还没感觉到锁的存在事情已经办完了。这个差异不是谁优化得好、谁优化得差而是数据存储引擎的底层设计决定的。不把这一点讲透后面所有运维决策都会踩坑。2. 先把MySQL的三种DDL算法和锁机制捋清楚MySQL处理DDL的算法分三类COPY、INPLACE、INSTANT。很多人只记了个名字不清楚背后到底怎么锁、怎么阻塞结果一到生产环境就抓瞎。2.1 COPY最古老也最坑全程阻塞DMLCOPY算法就是前面说的创建临时表-拷贝数据-换表名三件套。它的特点是整个执行过程中原表上的写入操作基本是要排队的。MySQL会用锁把表保护起来避免拷贝过程中数据不一致代价就是业务写入直接停摆。在MySQL 5.6之前几乎所有的ALTER TABLE都是这个命。直到今天如果表上有全文索引、空间索引这类InnoDB在线DDL管不了的对象或者某些操作被判定为不支持INPLACE它还是会乖乖退回COPY。我见过不少团队在5.7上跑ALTER TABLE ... MODIFY COLUMN ... DECIMAL(14,2)跑了两个小时期间订单系统只读不可写最后还因为日志爆了失败回滚那感觉真是酸爽。判断一条ALTER是不是COPY最简单的方法是执行前用EXPLAIN ALTER TABLE预演一下结果里会明确告诉你它打算用哪个算法执行中看SHOW PROCESSLIST如果State列出现copy to tmp table说明它已经在COPY算法里挣扎了。2.2 INPLACE不复制表却躲不开MDL锁INPLACE是MySQL 5.6引入的在线DDL算法它不创建整表的临时副本而是在原表所在的空间里直接做结构变更很多操作需要重建表空间但不重建整表数据。重点来了INPLACE算法在执行的大部分阶段是允许并发DML的InnoDB会把并发的插入、更新、删除记录到一份在线日志里等DDL快结束时再回放保证数据一致。听起来很美好对不对但实际生产中INPLACE照样能卡死业务。两个原因第一个原因是执行窗口的MDL锁。DDL开始的瞬间需要拿表的MDL写锁结束的瞬间也要再拿一次。虽然理论上只是毫秒级但只要有任何一条长事务或长查询先握着这张表的MDL读锁ALTER就会进入Waiting for table metadata lock状态。这一等可能就不是毫秒级而是等到那个查询跑完。更麻烦的是MDL等待队列是写优先的一旦ALTER排在队首后面新来的所有SELECT、INSERT、UPDATE都会排在它后面。一个本来只需要几秒的DDL因为一条烂SQL能把整张表的访问全部堵死。这场景特别像Java里用阻塞队列然后队列头堵了一个大任务后面全等着。第二个原因是在线日志容量。INPLACE期间并发写越多在线日志涨得越快默认上限是128MBinnodb_online_alter_log_max_size。如果大表上的DDL跑得慢、同时业务写入又猛日志爆了DDL直接失败回滚之前重建的那些工作全部白费。2.3 INSTANT8.0的秒级算法但适用范围很窄INSTANT是MySQL 8.0推出的只改元数据算法。它不碰数据页只在数据字典里做修改所以执行时间是微秒到毫秒级可以认为是无阻塞的。它支持的操作包括在表末尾加列、修改列默认值、修改ENUM/SET定义以及——重点——8.0.12之后支持把VARCHAR列的声明长度增大条件是增大后的最大字节数不超过255。这条VARCHAR规则很关键后面专门讲。INSTANT看起来是救命稻草可惜MySQL对它非常吝啬DECIMAL精度扩展不支持VARCHAR从255字节以内扩到255字节以上不支持其他类型的修改更不支持。所以别指望靠INSTANT解决所有精度扩展问题它只覆盖了一条很窄的路。2.4 元数据锁MDL真正的连锁阻塞根源MDL是MySQL 5.5引入的专门管理表结构元数据并发访问的锁。所有CRUD语句进表前都要拿MDL读锁DDL要拿MDL写锁。读锁之间不互斥写锁和任何读锁/写锁都互斥。生产环境里最常见的连环事故链是某个大查询卡了十分钟一直持有表的MDL读锁DBA执行ALTER申请MDL写锁开始在队列里等待新的应用请求进来发现ALTER已经排在前面全部跟着排队连接池耗尽应用大量报错整个业务雪崩。所以判断一个DDL会不会影响业务不能只看它本身的耗时还要看执行前有没有人在这个表上长事务、长查询。这条经验我后面实操章节还会强调。3. MySQL扩展字段长度时到底哪些能快、哪些必须重建3.1 VARCHAR小扩展8.0.12之后有惊喜但255字节是分水岭先说好消息。在MySQL 8.0.12及以上版本如果你把VARCHAR(50)改成VARCHAR(100)而且这个表的字符集是utf8mb4那么新字段最大字节数是100 × 4 400超过255了不好意思INSTANT不支持。但如果你在latin1字符集下把VARCHAR(50)改成VARCHAR(100)100字节没超过255MySQL会走INSTANT秒改。判断单位是字节不是字符数。utf8mb4一个汉字占4字节所以别看字符数没多少字节数很容易就冲破255了。举个例子VARCHAR(60)在utf8mb4下是240字节小于255可以INSTANTVARCHAR(64)在utf8mb4下是256字节大于255不能INSTANT。这个细节如果不清楚在表上敲了ALTER TABLE ... MODIFY name VARCHAR(64)然后发现锁了一小时回头查文档才知道是字符集的锅那就太冤了。3.2 VARCHAR跨过255字节数据行结构变了必须重写为什么255是个坎因为InnoDB的行格式里变长字段的长度信息是用可变字节数记录的。当列的最大字节数不超过255时长度信息一个字节就够一旦超过255就需要两个字节。跨越这个阈值意味着每行数据的物理结构都变了必须逐行重写。所以从VARCHAR(60)240字节扩到VARCHAR(100)400字节这种操作在MySQL 8.0里无法INSTANT只能重建表。如果表是一张大表就得接受几十分钟到几小时的重建代价。那有没有办法从物理上避免没有。这是存储引擎格式决定的。Oracle的VARCHAR2完全没这个问题因为VARCHAR2行内存的是实际数据长度实际数据字节不是按声明长度预留空间的声明从100扩到300已存在行的物理长度一点没变改字典就行。两边一对比MySQL是改定义改全表Oracle是改定义改字典这是存储设计的分水岭。3.3 DECIMAL精度扩展存储格式摆在那绕不开重建再来看标题里的主角DECIMAL。MySQL的DECIMAL是定长二进制存储存储占用由精度(M,D)直接决定。规则是每9位十进制数占4字节剩余部分按位数另占1到4字节。以DECIMAL(10,2)为例整数部分8位分成1组占4字节小数2位占1字节合计一行5字节。如果改成DECIMAL(12,2)整数部分10位拆成9位1位分别占4字节和1字节小数2位还是1字节合计一行6字节。看起来只多了1字节但千万行的表就是几千万字节的物理重写加上二级索引里的字段也要一起重建成本是巨大的。更关键的是DECIMAL精度扩展在MySQL 8.0的INSTANT支持列表里根本没有位置。你就算用ALGORITHMINSTANT显式指定MySQL也会报错说这个操作不支持INSTANT。它只能走INPLACE或COPY也就是必然重建表。这一点和Oracle的NUMBER又是天壤之别。我干过一件蠢事在一张6000万行的流水表上把金额字段从DECIMAL(10,2)改成DECIMAL(12,2)当时想着MySQL 8.0在线DDL应该没啥事就挑了个业务低峰直接ALTER。结果跑了40分钟期间一堆长事务占着MDL读锁ALTER卡在排队状态业务全部被堵住了最后只能忍痛kill掉DDL改用Percona的工具慢慢搞。4. 为什么Oracle改精度几乎不费吹灰之力4.1 NUMBER的变长存储与MySQL DECIMAL的定长存储Oracle的NUMBER类型在内部是变长存储的底层用类似科学计数法的格式指数尾数每两位十进制数字压缩成一个字节总的存储长度取决于这个值本身的有效位数。精度定义里的p只是一个约束上限改大它并不意味着数据行要重新编码。MySQL的DECIMAL则相反它是定长的每行按定义好的M和D分配好固定字节数。改大精度意味着每行的盒子尺寸变了必须把所有数据重新塞进新盒子所以必须重建。一句话总结Oracle的NUMBER是按值存MySQL的DECIMAL是按定义存。这决定了扩展精度这个动作在两边的代价完全不同。4.2 VARCHAR2实际数据长度不变所以只动字典Oracle的VARCHAR2也一样。行内记录的是该行实际存的字节数和真实数据声明长度只是数据字典里的一条规则。把VARCHAR2(100)改到VARCHAR2(300)已存在的数据行每一行的实际占用都没有变化Oracle只需要更新字典里允许的最大长度这个值剩下的什么都不用做。当然如果修改声明长度需要改变行内存储格式的情况比如要把普通VARCHAR2改成超长字符串相关的存储结构、或者缩小到比现有数据还小、或者改类型Oracle也会扫描验证那就不是秒级了。但单纯扩大声明长度/精度Oracle是真的快。4.3 Oracle 12c以后的DDL有什么新变化Oracle 12c开始DDL默认是事务性的也就是说DDL可以和DML处在同一个事务体系里失败可以回滚。这对改精度这类快速DDL影响不大但对改类型这类重型DDL体验改善很明显。Oracle原生还提供了DBMS_REDEFINITION在线重定义可以在业务不中断的情况下改表结构这相当于把MySQL要做的大量外部工具工作变成了数据库原生能力。所以拿Oracle做对比不是要吹Oracle多牛而是想说明MySQL的DDL成本高是因为它把列定义刻进了每一行数据的物理布局里Oracle把列定义当作元数据来管理日常变更自然就轻。理解了这一点你在MySQL上做精度扩展时就会有一个基本判断这事儿在MySQL里大概率是重量级操作不能想当然。5. 实操避坑手册大表精度扩展的完整操作流程这一节是真正能直接拿去用的。我在多次踩坑之后总结了一套流程不敢说100%保证不阻塞但至少能帮你避开90%的连环雷。5.1 操作前查清楚表多大、有没有长事务、会不会排队动手之前先把这几样查明白-- 1. 表行数和数据大小 SELECT table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb FROM information_schema.tables WHERE table_schema your_db AND table_name your_table; -- 2. 正在运行的事务重点看 trx_started 老不老 SELECT * FROM information_schema.innodb_trx\G -- 3. 这张表当前的MDL锁占用 SELECT * FROM performance_schema.metadata_locks WHERE object_schema your_db AND object_name your_table\G如果innodb_trx里有一个跑了很久的事务或者metadata_locks里有长期SHARED_READ锁千万别直接执行ALTER。这种时候执行DDL大概率是排队半小时起步。一个小技巧用MySQL 8.0的EXPLAIN ALTER TABLE预演一下要执行的语句看它到底走INSTANT、INPLACE还是COPY。预演不实际执行但能让你提前知道这条DDL的重量级EXPLAIN ALTER TABLE your_table MODIFY amount DECIMAL(14,2);如果结果显示需要重建表就按下面的思路走别拿生产环境赌运气。5.2 三种安全改法的取舍直连ALTER / pt-osc / gh-ost第一种表比较小百万行以下 低峰期直连ALTER。这种情况下走INPLACE重建其实也就是几分钟的事配合lock_wait_timeout设置一个几秒钟的等待上限能排队就排队等不到就放弃别硬扛。第二种表大且需要在线用pt-online-schema-change。这是Percona Toolkit里的明星工具。原理解起来不复杂先按新结构创建一张影子表在旧表上建触发器捕获增量变更然后分批把旧数据拷贝到新表最后通过RENAME TABLE原子切换。这个过程中旧表始终在线业务写入走触发器被同步到影子表切换瞬间业务基本无感。命令大概长这样pt-online-schema-change \ --alterMODIFY COLUMN amount DECIMAL(14,2) \ Dyour_db,tyour_table \ --host127.0.0.1 --port3306 \ --max-loadThreads_running50 \ --chunk-size1000 \ --execute注意几个前提表必须有主键或唯一键binlog必须开ROW格式表上不能有复杂的触发器。还有pt-osc本身会给业务写入带来一些额外负载因为触发器会对每个DML多做一些事所以最好加--max-load限制一下别把数据库IO打满。第三种用gh-ost。gh-ost是GitHub开源的在线表结构变更工具它的特点是不用触发器而是通过解析binlog来捕获增量变更对业务写入的影响通常比pt-osc小但部署和配置要复杂一些。如果你对工具链熟悉gh-ost是更优雅的选择如果不熟悉pt-osc是更稳妥的入门选择。我在生产环境真实跑过几次大表精度扩展最终结论是能不用原生ALTER就别用尤其是那种注定要走重建流程的DECIMAL精度扩展。用pt-osc虽然耗时也不短但业务是无感的比锁死强太多。5.3 执行过程中的监控与止损不管用哪种方式执行期间都要盯几个东西SHOW PROCESSLIST看有没有大量Waiting for table metadata lockinformation_schema.innodb_trx看有没有事务卡住iostat看磁盘IO是不是已经饱和了如果是pt-osc看它是不是在反复重试某个chunk。一旦发现MDL锁排队已经影响到业务止损动作要快优先考虑kill掉持锁的长事务或长查询如果找不到源头就直接kill掉DDL本身。MySQL 8.0的DDL是原子的kill掉一般不会留残表比5.7里那种留下一堆#sql-xxxx临时文件的情况干净多了。6. 从根上减少精度扩展的机会类型与设计最后说点治本的东西。我这些年最大的体会是在MySQL上少做DDL比学会做DDL更重要。精度扩展这件事Oracle可以先上线再说不够再改因为改起来的成本几乎为零。MySQL不行一次大表精度扩展动辄几十分钟到几小时还伴随阻塞风险。所以选MySQL当存储的话建表阶段就要把精度想清楚金额字段能预留到DECIMAL(18,2)就尽量别用DECIMAL(10,2)。你不能预测业务几年后会不会把单笔金额上限翻几倍但DECIMAL(18,2)基本能覆盖绝大多数交易系统的量级了如果业务允许也可以用最小货币单位存BIGINT比如分这样压根没有小数精度问题。坏处是以后如果想存更小单位厘、毫又会遇到类型变更所以在建模时要把单位也定死字符串长度同样如此VARCHAR(50)和VARCHAR(255)在utf8mb4下存储结构差异不大但前者未来扩到超过255字节时要付出全表重建的代价。与其日后痛苦不如初始就把长度拍够——当然也要注意别随手VARCHAR(5000)那可能把行长撑爆还要考虑最大行限制。再分享一个我自己的经验跨库迁移或者双写方案设计时别把反正以后可以在线改表当成默认前提。MySQL这边一条改精度的DDL是能写进故障复盘的那种操作。我在实际项目里给过一个建议如果预期这张表会超过千万行、而且字段定义有大概率要调整要么一开始就给足余量要么就把表设计成可以快速切换新表的结构比如按天分表而不是把希望寄托在一次不阻塞的在线DDL上。说回开头那次事故。后来我在MySQL上用了pt-osc花了大概一个半小时把那张订单表的金额精度从DECIMAL(10,2)扩到DECIMAL(14,2)中途业务完全无感。那个晚上之后我把MySQL精度扩展必先评估算法和MDL锁写进了团队的操作规范也把Oracle可以随心所欲改精度MySQL不能写进了新项目的技术选型清单。搞数据库就是这样很多坑不是躲不开而是你还没意识到它是个坑。