先说个亲身经历。去年我接手一张 MySQL 订单宽表整表 48 个字段里面塞了三个 LONGTEXT 分别存买家备注、卖家留言和一段物流协议原文字符集还是从 latin1 时代一路迁过来的老 utf8。表里两千多万行每次拉订单列表光是读行数据就要扫好几 MB接口响应随随便便超过三秒。后来我就是按“宽表必拆、大字段必 TEXT、字符集需精算”这三条挨个下刀把字段收到 22 个大字段全部拆到扩展表并收敛成 TEXT/JSON字符集统一成 utf8mb4慢查询直接从日均三百多条降到个位数。这篇不打算讲虚的。我会把这套口诀背后的 InnoDB 存储原理拆开把每一步该怎么做、参数怎么算、坑在哪里都摆出来。适合正在维护 OLTP 业务表、被慢查询折磨的开发也适合刚入门但想从一开始就把表设计做对的同学。1. 宽表必拆凭什么“必”1.1 宽表拖垮性能的根因行变大了页就废了InnoDB 的读写最小单位是页默认 16KB。B树的叶子页装的就是一行行数据一页能装多少行直接决定你用同样的内存、同样的磁盘 IO 能处理多少数据。假设一行数据占用 4KB那一页只能放 4 行。如果单行压到 1KB一页能放 16 行。对一张 2000 万行的表来说前者需要 500 万个叶子页后者只需要 125 万个叶子页。范围查询、全表扫描、二级索引回表所有操作的 IO 次数都会按这个比例放大。更头疼的是缓存命中率。Buffer Pool 就那么点大假设给你 8GB宽表场景下可能只够缓存 200 万行左右热点数据频繁被挤出磁盘 IO 直线上升。我见过一张表行平均 12KB每页只能塞 1 行等于每读一行就做一次随机 IO这种表不慢才怪。还有一个硬性限制MySQL 对单行数据的总长度限制是 65535 字节TEXT/BLOB 这类溢出字段另算。字段一多稍微几个 VARCHAR 就可能把行宽推高。早期我建表时甚至直接报过Row size too large那已经不是性能问题了是物理上就建不出来。怎么判断一张表“宽不宽”我给自己定的标准很简单字段数超过 25~30 个行内实际字节数除 TEXT/BLOB 外超过 8KB也就是页大小的一半大量字段平时根本不出现在 SELECT、WHERE、ORDER BY 里纯粹“躺在表里占地方”。命中两条就该考虑拆了。1.2 垂直拆分的三种姿势别上来就咔咔乱切庖丁解牛讲究顺着骨缝下刀拆表也一样。宽表不是无脑横切一刀起码有三种常用姿势。第一种是核心表 扩展表把主键作为唯一关联键做成 1:1。适合那种“高频查核心信息、低频查附属信息”的场景。以用户表为例拆成user_core和user_profile_ext前者放 id、手机号、状态、注册时间、最近登录时间后者放头像 URL、个性签名、性别、生日、收货地址 JSON。日常登录鉴权只碰核心表谁也不会每次登录都去读一串地址 JSON 和签名字段这就是顺着骨缝切。第二种是冷热分离。一个订单表可能有 40 个字段其中status、amount、create_time、pay_time是热点buyer_remark、seller_note、logistics_snapshot、invoice_info是冷数据。拆的时候让热点字段留在主表冷字段全进扩展表。注意这里“冷”不是永远不读而是读取频率和主字段差好几个数量级。第三种是行内再分域。比如一个用户相关的表基础信息、身份认证信息、营销偏好信息混在一起但它们生命周期、更新频率、敏感程度都不同。这种按领域拆成多张 1:1 表在后续权限控制、归档、脱敏上也省事得多。具体拆完最容易被坑的是查询。以前一条 SELECT 搞定拆完之后要是无脑 JOIN 回去那是白拆。我的习惯是业务侧先查主表等到真需要展示扩展字段时再按主键批量查扩展表绝大多数场景都是“先出列表点击详情再加载附加信息”。扩展表的数据可以不常驻缓存查询频率低热点数据也容易淘汰整体 IO 反而更可控。1.3 什么时候不该拆这块肉别下刀拆表不是银弹。如果是做数据分析、报表聚合、大屏展示宽表反而是主流——OLAP 场景下列式存储、预聚合、并行扫描都是另一套逻辑这时候刻意拆成 1:1 只会让查询越来越难写。哪怕是 OLTP如果一张表虽然字段多但每次写入都是整行覆盖、查询也经常要全字段返回拆了反而多一次 JOIN 和分布式事务成本。我遇到过一张配置表二十多个字段一次性全查全更新这种就没必要拆。所以“宽表必拆”的正确理解是宽且访问模式分化才必须拆宽但访问模式统一可以先优化存储和索引。以下几个问题先问自己这张表哪些字段真正参与高频查询哪些字段是低频但必须随主记录存在拆完之后业务代码改动成本能不能接受评估清楚了再动手。2. 大字段和 TEXT存的是数据也是指针2.1 InnoDB 的大字段溢出机制先搞清楚数据到底放哪MySQL 里处理大字段绕不开“行溢出”这个概念。InnoDB 的行格式常见的有 COMPACT 和 DYNAMICMySQL 5.7 之后默认 DYNAMIC8.0 也是两者对大字段的处理逻辑不一样。COMPACT 行格式下TEXT/BLOB 类型的前 768 字节会存在主数据页的行记录里剩下部分存到“溢出页”行内再留一个 20 字节的指针指向溢出页。也就是说哪怕你只 SELECT 一个 id 字段只要这一行里有个大 TEXT读取时也可能把那前 768 字节一并从页里捞出来白白浪费 IO。DYNAMIC 行格式则干脆得多变长大字段完全放到溢出页主数据页的行记录里只保留一个 20 字节的指针。这样查询小字段时根本不碰大字段所在的数据页IO 开销少一大截。这个机制解释了为什么“大字段必 TEXT”是有道理的。VARCHAR 超过一定长度后你会碰到 65535 字节的行长度限制而且它本质上是期望尽量内联存储的。TEXT 系列TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT从设计上就允许外溢配合 DYNAMIC 行格式至少不会因为一个大字段把整行拖进溢出页地狱。但注意TEXT 也有自己的问题。第一TEXT 不能有默认值第二普通索引不能直接建在 TEXT 上必须用前缀索引第三频繁更新 TEXT 字段时溢出页的随机写开销非常大比更新一个普通 varchar 贵得多。实操时我给大字段设过几个前提不参与 WHERE / GROUP BY / ORDER BY、不需要建普通索引、更新频率低。三点都满足才批准进 MySQL。有一条不满足就要认真考虑是不是该拆出去、放 ES、放对象存储或者干脆只在库里存个文件路径。2.2 聚集 key 和大字段为什么会打架有个知识点很多人容易忽略InnoDB 的二级索引叶子节点不存行的物理地址它存的是“索引键值 主键值”。查询时先通过二级索引找到主键值再用主键回聚集索引查整行。听起来挺绕但这是理解“不能同时包含聚集 key 和大字段”这句话的关键。聚集索引的 key 就是主键。如果主键本身是个大字段比如拿一长串文本当主键或者用 varchar(64) 存 UUID那么表里所有二级索引的每一个叶子节点都得跟着存一份这么大的主键。涨的不是一点半点是成倍膨胀。我拆过一张表原来主键是 varchar(36) 的 UUID后来改成 BIGINT 自增同一个二级索引大小直接缩到原来的四分之一。所以“大字段不要进 keykey 一定要短”是铁律。新表主键一律建议 BIGINT 自增或者用雪花算法生成 BIGINT总之这辈子别再把 UUID 字符串当主键。实在必须用 UUID也建议转成 BINARY(16) 存能省不少空间。说到“大字段必 TEXT”和“聚集 key 不能是大字段”本质上是一条线大字段要么不建索引要么用前缀索引。前缀索引就是只对字段前 N 个字符建立索引比如idx_remark(remark(50))。它能解决“TEXT 不能建索引”的问题但代价是只能做前缀匹配不能做完整覆盖也不能用来排序。选 N 的时候要算一下区分度比如SELECT COUNT(DISTINCT LEFT(remark, 30)) AS c30, COUNT(DISTINCT LEFT(remark, 60)) AS c60, COUNT(*) AS total FROM order_ext;如果 LEFT 30 和 LEFT 60 的区分度差不多就没必要建 60 的前缀越短越省索引空间。这个操作算是大字段索引设计里性价比最高的一步。2.3 大字段存储的最终建议按场景分层很多人一听到“大字段必 TEXT”就真的把所有长文本往 TEXT 里塞也不管业务怎么读。我给一个我一直在用的决策框架。如果大字段是“必须随主记录存在、不参与查询、偶尔更新、展示时全量读出”放进扩展表 对应等级的 TEXT 或者 JSON。比如用户备注、订单备注。JSON 类型在 MySQL 5.7 之后有专门的二进制格式和函数支持比塞在 VARCHAR 里强得多。注意 JSON 底层也是用 TEXT/LONGTEXT 存储的别误以为 JSON 是无限制的万能类型。如果大字段是“内容很大、但必须支持搜索”别放 MySQL放 Elasticsearch。MySQL 只存一个 ID搜索服务里面建索引两边通过 ID 关联。这是我最常用的组合。如果大字段是“文件内容、图片、日志原文”那更不该进 MySQL对象存储加数据库存路径才是主流。硬塞进 LONGTEXT 的表备份恢复、迁移、磁盘 IO 全都会跟着遭殃。最后提醒一个所有大字段都会面临的问题碎片。频繁 UPDATE 一个 TEXT 字段InnoDB 会产生大量碎片页表空间越来越大但实际有效数据占比越来越低。我的习惯是定期检查information_schema.tables的data_length和data_free如果data_free超过 10%挑业务低谷执行 OPTIMIZE TABLE。但注意这个操作会锁表量大的表要谨慎安排时间窗口。3. 字符集精算一字之差差出十万八千里3.1 一个 utf8 引发的血案伪 utf8、emoji 和锟斤拷MySQL 里的 utf8 有一个历史包袱它最多只能存 3 字节的字符真正的 Unicode 有些字符需要 4 字节才能表达比如 emoji、部分生僻汉字。所以 MySQL 后来推出了 utf8mb4。所谓“utf8”在 MySQL 语境里其实是 utf8mb3是阉割版。这就造成一个经典事故应用层传过来一个笑脸表情MySQL 直接报Incorrect string value: \xF0\x9F\x98\x80因为 4 字节的字符在 utf8mb3 下根本塞不进去。乱码的链路更复杂。一套 MySQL 从客户端到存储层要过 5 个字符集关卡client、connection、database/table/column、results。任何一关没对齐就会出乱码。常见的“锟斤拷”乱码本质上就是 UTF-8 编码的字节流被当成 GBK 解码后的产物属于二级乱码。排查乱码的思路永远是先确认“写入时是什么字节流、读出时按什么字符集解释”。所以“字符集需精算”的第一条金律就是在 MySQL 8.0 里老老实实用默认的 utf8mb4在 5.7 里手动把库、表、列全部指定成 utf8mb4连接层也统一。别信“现在数据没乱码就不用管”这种话等 emoji 或者生僻字出现的时候数据已经脏了。3.2 字符集字节数怎么算从 767 和 3072 说起字符集的第一个精算是存储空间。varchar(n) 里的 n 是字符数不是字节数。utf8mb4 下每个字符最多占 4 字节所以 varchar(255) 最坏要占 255×4 1020 字节。如果一张表有 30 个这种字段光行宽就轻松超过 8KB宽表就是这么“喂”出来的。第二个精算是索引长度。InnoDB 索引键有长度上限老版本是 767 字节MySQL 8.0 默认 3072 字节要求 DYNAMIC/COMPRESSED 行格式。utf8mb4 下一个索引列如果要建完整索引在 767 字节限制下最多只能有 191 个字符——因为 191×4 764 字节加上变长字段的长度字节刚好卡在 767 上。而 utf8mb3 是 3 字节255×3 765这也就是为什么老项目里 varchar(255) 能建索引、改 utf8mb4 之后却报Specified key was too long的原因。在 3072 字节限制下utf8mb4 可以支持到 767 字符左右768×4 3072但变长字段还要多占字节所以实际安全值是 767。这个数字对联合索引尤其重要。举个具体例子。假设有个联合索引idx_mobile_name(mobile, name)其中name是 varchar(200)字符集 utf8mb4在 8.0 下name 最多占用 200×4 800 字节加上变长长度 2 字节、可空标志 1 字节总共约 803 字节单字段没超 3072整个索引也没超没问题。在 5.7 老配置下如果单列索引上限还是 767name 单独就超了直接建索引失败。所以“字符集需精算”不是嘴上算算而是要真的知道自己的索引占了多大字节、会不会撞上限。我每次设计索引都会拿笔列一遍字符集字节数 × 字符数 变长字段额外字节心里有个底。第三个精算是排序规则。utf8mb4 下面有一堆 collation常见的是 utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_0900_ai_ci。general_ci 快但排序精度低unicode_ci 更准确但慢一点0900_ai_ci 是 MySQL 8.0 的默认支持更细的 Unicode 规则和声调/重音不敏感比较。中文场景下utf8mb4_unicode_ci 和 0900_ai_ci 都够用但要注意 join 时两边 collation 不一致容易出现Illegal mix of collations报错。3.3 字符集统一成一盘棋四层检查我整理过一张字符集检查清单每次上线前照着走一遍基本能避免乱码和索引超限Server 层SHOW VARIABLES LIKE character_set_server8.0 默认 utf8mb45.7 常要显式改Database 和 Table 层建库建表时显式声明DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci不要依赖全局默认值Column 层查information_schema.columns看有没有历史遗留的 latin1、utf8mb3 字段Connection 层业务侧执行SET NAMES utf8mb4或者 JDBC URL 里显式配置 characterEncodingUTF-8、connectionCollationutf8mb4_unicode_ci 之类的参数。迁移的时候也要小心。ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4能转换列的数据但会锁表大表要在低谷期分批做更稳的做法是先建新表用INSERT ... SELECT分批导数据最后切换这样每一步都可回滚。备份文件里如果写了SET NAMES latin1恢复时又没改照样给你创造出一堆乱码来。总之字符集这东西不是设一次就完事它是贯穿建库、连接、备份恢复全链路的。4. 案例复盘一张 48 字段订单表的整改实录4.1 原始症状和拆解思路表order_info有 48 个字段单行平均约 9KB哪怕是只查 id、status、amountInnoDB 也得把整行读进内存。买家端订单列表接口每页 20 条理论上只需几百字节的字段实际却要扫将近 200KB 的行数据再加上排序和回表慢得理所当然。我在拆之前先做了一件事打开 slow log 和 performance_schema统计近一周那些慢查询到底查了哪些字段、过滤和排序用了哪些字段。结果表明buyer_remark、logistics_snapshot、seller_note、invoice_info这些字段一周下来被 SELECT 的频次不到热点字段的百分之一。这就是最清晰的骨缝。拆表动作分三步新建order_main表放高频字段主键沿用 order_id字符集 utf8mb4新建order_ext表放低频大字段主键同样是 order_idbuyer_remark用 TEXTlogistics_snapshot用 JSON写一次性迁移脚本把数据从旧表按主键插入两张新表中间用事务保证一致性。迁移脚本大致长这样CREATE TABLE order_main ( order_id BIGINT NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, ... PRIMARY KEY (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE order_ext ( order_id BIGINT NOT NULL, buyer_remark TEXT NULL, logistics_snapshot JSON NULL, invoice_info TEXT NULL, PRIMARY KEY (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; INSERT INTO order_main (order_id, status, amount, create_time, ...) SELECT order_id, status, amount, create_time, ... FROM order_old; INSERT INTO order_ext (order_id, buyer_remark, logistics_snapshot, invoice_info) SELECT order_id, buyer_remark, logistics_snapshot, invoice_info FROM order_old;迁移完成后原来的订单列表 SQL 只查 order_main字段从 48 个降到 18 个。单行字节数从 9KB 降到不到 2KB同样大小的 Buffer Pool 能缓存的行数翻了四倍多。列表接口 P95 延迟从 2.8 秒降到了 180 毫秒。4.2 高频报错速查表一半以上都能对号入座这里把我踩过、以及帮别人排查过的五类典型问题整理成一张速查表按症状、原因、对策排列症状常见原因排查思路与对策写入或读出乱码显示问号或口字形连接字符集或列字符集不对常见于 latin1/utf8mb3 混用查SHOW VARIABLES LIKE character_set%统一改为 utf8mb4业务侧执行SET NAMES utf8mb4存 emoji 报Incorrect string value: \xF0\x9F...列是 utf8mb3无法存 4 字节字符ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4确认连接层也使用 utf8mb4建索引报Specified key was too long索引键超过 767/3072 字节上限utf8mb4 下 varchar 长度过大减少索引字符数或改用前缀索引确认行格式是 DYNAMIC 并启用大索引支持大字段频繁更新后表空间暴涨TEXT/BLOB 更新产生碎片溢出页反复分配刷information_schema.tables看 data_free低谷期OPTIMIZE TABLE业务侧减少无谓的整行 UPDATE带大字段的宽表查询极慢行数不多但耗时很高单行过长导致一页只能放一两行IO 放大、缓存命中率低垂直拆分冷热字段大字段拆到扩展表查询侧避免 SELECT 大字段这些问题的共同点在于都不是上线当天炸出来的而是数据量涨到一定程度才开始疼。所以与其在出了问题之后加班不如在建表阶段就想清楚字符集、字段分组和索引边界。4.3 设计自检清单贴在工位上比背口诀有用最后给一份我一直在用的表设计自检清单。新表上线前逐条过一遍字段数是否超过 25冷热访问模式是否明显分化如果都命中垂直拆分有没有字段长度超过 255 字符、又不参与查询的大文本有就放到扩展表的 TEXT/JSON 里别让它在主表里拖累行宽有没有字段要建索引但字符集是 utf8mb4 且长度不明先算字节数是否超过索引上限必要时前缀索引主键是不是短整型UUID、长字符串做聚集 key 的全部否决库、表、列、连接四层字符集是否全部显式声明为 utf8mb4不要指望全局默认值尤其要警惕 5.7 老环境大字段是否会被频繁 UPDATE如果是重新评估是不是该挪到更合适的存储位置。这套流程看着简单但每条背后都是存储引擎层面的硬约束。我见过太多表读性能上不来第一反应就是加索引、加缓存、加机器却从来没人想过一行 9KB 的数据根本不配出现在一张 OLTP 主表里。宽表拆掉、大字段归位、字符集精算三步做下来很多时候根本不需要动硬件。最后再分享一个小技巧拆完表别急着删老表让它以“只读归档表”的形式多保留一两个业务周期。我上次整改前就留了一手后来果然有业务方找来说某个冷字段没接上直接从归档表补数据没造成任何事故。这种稳妥的过渡策略比什么华丽的技术方案都管用。