先说个我实际遇到的事。前两年帮一个创业团队做线上数据库巡检他们的用户表已经接近 500 万行库不算大可每次按状态筛选用户都要几百毫秒晚上跑统计任务时主库 CPU 直接顶满。我打开建表语句一看问题比想象中更基础状态位用的是varchar(50)里面存的是0、1这种单字符用户名用varchar(500)性别用varchar(20)存“男”“女”。索引没建错SQL 也没写得多烂纯粹是 MySQL 数据类型选得太随意把存储、内存和索引空间一起拖下水。这类问题在真实项目里太常见了。很多人以为数据类型只是“能不能装下这个值”的问题实际上它直接影响行记录体积、索引页大小、排序临时文件、事务锁竞争甚至主从复制延迟。这篇文章不打算把手册抄一遍而是从选型角度把 MySQL 的数值型、字符串、日期时间、JSON、枚举这些类型拆开讲清楚结合我实际做过的表结构评审和线上改造案例顺便把踩过的坑也都列出来。适合刚学 MySQL 的同学也适合写了好几年业务代码、想回头把表结构查漏补缺的工程师。1. 数据类型选型为什么直接决定你的库能撑多久1.1 一个看似“够用”的字段实际在放大几十倍开销先说状态字段。业务上只需要存 0、1、2 三个值大部分人图省事直接varchar(50)或者干脆用varchar(20)。在 utf8mb4 字符集下一个英文字符最多占用 4 字节存一个0也要 5 字节左右4 字节字符数据 1 字节长度前缀如果哪天业务代码不小心把状态拼成ACTIVE那就是 25 字节。而如果一开始就用TINYINT UNSIGNED固定 1 字节。500 万行数据仅这一个字段在常见的“短字符串”场景下就可能差出 20 倍以上的数据体量。InnoDB 默认页大小 16KB数据页里能放的记录数直接决定扫描效率。同样的查询条件一个能用 2000 个数据页读完另一个要 40000 页Buffer Pool 命中率天差地别最后体现出来的就是查询从几十毫秒变成几百毫秒。我经常和人说一句话MySQL 性能调优里一半的工作其实在很早之前的建表阶段就已经决定了。后面加索引、改 SQL 都是在为当初“随手写一个 varchar(100)”买单。1.2 存储体积放大后CPU、内存、主从全都跟着遭殃存储体积不只是“多占点磁盘”这么简单它会连锁影响好几个环节索引体积InnoDB 的二级索引每个索引页里都保存着索引列值和主键值。主键越大、索引列越长B 树叶子页能装的条目就越少树的高度和叶子页数量都会增加。排序和分组ORDER BY、GROUP BY、DISTINCT这类操作要把字段放进 sort buffer。列越宽一次能排序的行数就越少超出内存后 MySQL 会落到磁盘临时表速度直接掉一个数量级。事务与锁InnoDB 行锁实际锁的是索引记录数据页内记录越多同一热点页上的并发事务冲突概率越低反之行越宽同一个 16KB 页里容纳的记录越少热点页的竞争更明显。主从复制binlog 在 row 格式下记录的是变更后的完整行镜像。字段越长一次UPDATE写进 binlog 的字节越多从库应用日志的速度也跟着变慢。所以建表时的一个小决定最终会传导到 CPU、内存、IO、网络各个环节。这也是为什么我坚持在项目起步阶段就做表结构评审而不是等慢查询日报出来再补救。2. 数值类型INT(11) 不是限制长度很多人理解错了2.1 整数类型全家桶与选型习惯MySQL 的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 五种差别只在字节数和范围。我用得最多的判断标准是“先算业务上限再选最小可用类型”。类型字节有符号范围无符号范围典型用途TINYINT1-128 到 1270 到 255状态码、开关位SMALLINT2-32768 到 327670 到 65535年份、端口号MEDIUMINT3-8388608 到 83886070 到 16777215中等计数INT4-2147483648 到 21474836470 到 4294967295常规主键、数量BIGINT8极大极大雪花 ID、超大数据量主键有个习惯值得改主键不要一上来就BIGINT。行数不超过 21 亿的表INT UNSIGNED完全够用。有人觉得“反正现在磁盘便宜用 BIGINT 保险”但主键会出现在每一个二级索引的叶子节点里主键越大所有索引的体积都跟着变大。小项目无所谓上了千万行之后这个差异会明显反映在缓存命中率上。UNSIGNED不会改变存储字节数只是把取值区间往正向平移。比如TINYINT UNSIGNED依然是 1 字节范围变成 0 到 255。主键、状态位这类没有负数语义的字段我习惯都加上UNSIGNED。2.2 走遍全网都在问的 INT(11)显示宽度陷阱很多刚接触 MySQL 的同学都会困惑为什么id INT(11)而另一个表是INT(4)是不是长度限制不是。INT(11)和INT(4)的存储占用完全一样都是 4 字节能存的范围都是 -2147483648 到 2147483647。括号里的数字是“显示宽度”只在配合ZEROFILL时生效。比如INT(4) ZEROFILL存入 12查询结果是0012不写ZEROFILL这个宽度纯属视觉安慰。MySQL 8.0.17 开始已经弃用了整数类型的显示宽度网上一堆老教程还在说int(11)是长度限制属于流传最广的历史误解之一。如果你想确认自己的表到底建成了什么样直接执行SHOW CREATE TABLE your_table\G看实际的列定义比凭记忆推断靠谱得多。另外要注意ZEROFILL会默认给列加上UNSIGNED属性这是我曾经踩过的一个隐藏坑。2.3 FLOAT、DOUBLE、DECIMAL精度才是选型的核心数值类型里最容易出事故的是小数。FLOAT 和 DOUBLE 是浮点数本质是二进制近似存储就像科学计数法能表示很大范围但精度有限DECIMAL 是定点数按十进制精确存储就像记账本每一分钱都明明白白。经典事故就是WHERE price 0.1查不到数据。0.1 在二进制里是一个无限循环小数FLOAT/DOUBLE 存下来的其实是近似值直接等值比较会失败。所以业务上的金额、余额、费率、单价请一律用DECIMAL永远不要用 FLOAT 或 DOUBLE。DECIMAL(P, S)里 P 是总位数S 是小数位数。比如DECIMAL(10, 2)表示总长 10 位、小数 2 位最大可存 99999999.99约 1 亿以内。大部分订单金额场景够用涉及汇率、利息、账务核算的系统我习惯往上提比如DECIMAL(20, 6)前面留足整数位后面避免累计计算时精度被截断。还有一点应用层接收 DECIMAL 时不要随手转成 float 或 double。Java 里要用 BigDecimalPython 里注意 Decimal 和 float 的隐式转换否则数据库里算得好好的一进业务代码精度就丢了这种 bug 排查起来非常隐蔽。3. 字符串类型CHAR、VARCHAR、TEXT 的边界和隐性开销3.1 字符集先决定“一个字符等于多少字节”字符串类型最容易被忽略的前置条件是字符集。MySQL 里VARCHAR(n)的 n 是“字符数”不是字节数但底层存储完全按字节算。不同字符集下一个字符占用的字节数完全不同。latin11 字符 1 字节gbk1 字符 2 字节utf8mb3即老utf81 字符最多 3 字节utf8mb41 字符最多 4 字节MySQL 8.0 默认字符集已经是utf8mb4这也是我推荐全库统一的方案既能存中文也能存 emoji不会出现“明明数据库支持却存不进去”的怪问题。注意utf8mb4_0900_ai_ci是 8.0 默认排序规则5.7 时代大家更常用utf8mb4_unicode_ci这两者在 JOIN 比较时可能产生排序规则冲突后面第七章会专门讲。理解“字符数、字节数、内容长度”三者的关系不只是 DBA 的事。前端用 JavaScript 的string.length按 UTF-16 码元数计算后端用 Java 的length()按 UTF-16 char 计算MySQL 按字符或字节计算口径不一致就可能导致超长截断、校验失败。3.2 CHAR 与 VARCHAR 的存储规则以及一行的 65535 字节上限CHAR 是定长字符串最长 255 字符。存入的内容不足定义长度时尾部用空格补齐查询时会去掉尾部空格。适合长度几乎固定的字段比如固定长度的状态码、MD5 值、身份证号。VARCHAR 是变长字符串额外用 1 或 2 字节记录实际长度不超过 255 字符用 1 字节超过则用 2 字节。VARCHAR 理论上最大可以到 65535 字符但实际被“行最大 65535 字节”的限制卡死。我做过一个实验一张表只有一列VARCHAR(20000) DEFAULT CHARSET utf8mb4直接报Row size too large。原因是 20000 字符乘以 4 字节等于 80000 字节超过了整行硬限制。按公式粗略算utf8mb4 下单列 VARCHAR 上限大约是(65535 - 2) / 4 16383字符如果表里还有其他列这个值还要继续缩水。实操中的建议是不要写那种明显超出业务边界的长度。varchar(255)能覆盖大多数短文本但“昵称”“姓名”这类字段给varchar(50)或varchar(64)就足够了varchar(500)给“简介”也勉强说得通至于“备注”动不动varchar(2000)就要想想是不是应该用 TEXT 更合适。3.3 TEXT 和 BLOB看着方便代价在你看不见的地方TEXT 系列是真正的大对象类型TINYTEXT 最大 255 字节TEXT 最大 64KBMEDIUMTEXT 最大 16MBLONGTEXT 最大 4GB。BLOB 对应二进制版本规则类似。InnoDB 在默认的 DYNAMIC 行格式下会把较大的 TEXT/BLOB 完整内容放到溢出页行内只保留一个 20 字节左右的指针。这意味着什么查询这一行很快但要真正读取大文本内容时InnoDB 需要跳转到溢出页做随机 IO而且不同记录的内容可能散落在不同页面。实际开发中我遇到最多的三个坑TEXT 列不能有默认值建表时想给空字符串默认值会直接报错。ORDER BY、DISTINCT对 TEXT 排序开销巨大容易把临时表打到磁盘。对 TEXT/BLOB 建索引必须指定前缀长度例如ALTER TABLE article ADD INDEX idx_content (content(100))而且前缀索引无法用于覆盖索引。所以文章正文这类大字段我建议要么单独拆表存储要么列表页只查摘要列不要习惯性SELECT *把 16MB 的内容全部拖出来。4. 日期时间类型DATETIME、TIMESTAMP 的使用陷阱4.1 两类时间类型的本质差异MySQL 里最常用的时间是 DATETIME 和 TIMESTAMP很多人凭感觉二选一但其实它们的语义差别很大。维度DATETIMETIMESTAMP存储5.6.4不含小数秒5 字节4 字节范围1000-01-01 到 9999-12-311970-01-01 到 2038-01-19时区不随会话时区转换按会话 time_zone 自动转换适用场景业务时间、出生日期、历史数据日志、统计、短周期数据TIMESTAMP 有一个著名的“2038 年问题”它的上限到 2038-01-19 就结束了。如果系统里有出生日期、合同期限、长期有效的业务时间千万不要用 TIMESTAMP否则又得做一轮大表改造。从存储空间看DATETIME 比 TIMESTAMP 多 1 字节500 万行也就差 5MB这个差异在实际运维中可以忽略。真正的关键差别是时区语义TIMESTAMP 在写入和读取时都会按会话的time_zone参数做 UTC 换算DATETIME 则“存什么就是什么”不做任何转换。4.2 时区事故现场同一张表同一列读出不同时间我处理过一起线上事故应用服务器在 JVM 里默认 Asia/Shanghai数据库服务器却配成了 UTC。表里用的是 TIMESTAMP应用插入后显示本地时间好像是对的但换了一台应用服务器之后新老数据差了 8 小时导致订单统计全部错乱。排查起来并不复杂用一条 SQL 就能定位问题SELECT global.time_zone, session.time_zone, NOW();但问题在于很多团队不把时区当回事默认“反正都能显示时间”。我的建议是三条服务器时区、MySQLtime_zone、连接串参数、应用时区必须统一比如全部固定成Asia/Shanghai。核心业务时间统一用 DATETIME 存 UTC 时间应用层负责展示转换数据库只当容器。日志、埋点这类短周期数据可以用 TIMESTAMP享受自动换算的便利。还有一个和索引高度相关的小技巧不要在索引列上套函数。WHERE DATE(create_time) CURDATE()这种写法往往让索引失效改成范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:004.3 时间字段的默认值以及自动更新时间从 MySQL 5.6.5 开始DATETIME 也可以设置DEFAULT CURRENT_TIMESTAMP并支持ON UPDATE CURRENT_TIMESTAMP。我的标准建表写法是CREATE TABLE user_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, status TINYINT UNSIGNED NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;这里有两个实用心得第一让数据库生成创建时间比应用层传时间更可靠因为数据库时间是单一来源多个应用实例之间的时钟漂移不会影响数据第二updated_at依赖ON UPDATE CURRENT_TIMESTAMP但如果你在代码里显式 UPDATE 了它自动更新就不会生效所以应用层尽量只改业务字段不要手动动这个列。5. JSON、ENUM 和 BOOLEAN特殊类型的适用边界5.1 JSON 类型的价值以及“别把它当万能字段”MySQL 5.7.8 开始提供原生 JSON 类型在此之前大家只能把 JSON 塞进 TEXT然后在应用层解析。原生 JSON 有几个明确优势插入时数据库会校验 JSON 合法性内部以二进制格式存储读取时不用重复解析文本支持JSON_EXTRACT、-、-、JSON_CONTAINS等函数。但 JSON 不是灵丹妙药。我见过有人把用户的所有属性都塞进一个 JSON 字段美其名曰“灵活、免改表”结果查询时到处JSON_EXTRACT索引又建不上最后变成全表扫描。这是一种反模式。如果要在 JSON 字段上建立检索路径正确姿势是用生成列CREATE TABLE product ( id INT PRIMARY KEY, attr JSON, price DECIMAL(10,2) GENERATED ALWAYS AS ( JSON_UNQUOTE(JSON_EXTRACT(attr, $.price)) ) STORED, KEY idx_price (price) );这样既保留了 JSON 的灵活性又能让price上的查询走普通索引。另一个容易被忽略的坑是更新成本。修改 JSON 列的任何一部分InnoDB 都需要把整个 JSON 文档重新编码写入不能只改一个 key。所以高频更新的字段不要放 JSONJSON 适合低频写入的扩展属性、透传报文、配置快照。5.2 ENUM、SET 和 BOOLEAN方便背后的扩展成本ENUM 定义了一组固定取值底层按成员索引存储实际占用 1 到 2 字节看起来非常节省。但有两个隐藏问题。第一ENUM 的排序是按定义顺序不是字符串字典序。比如ENUM(apple,banana,cherry)排序结果是 apple、banana、cherry而不是按字母序这很容易让人困惑。第二修改枚举成员需要执行 ALTER TABLE比如把ENUM(a,b)改成ENUM(a,b,c)涉及表结构重建和数据校验在几百万行的表上会引发锁和复制延迟。枚举状态如果确定几年内不会变化比如性别枚举可以用如果是一个订单状态机今天待支付、明天已退款、后天又冒出一个“售后中”请老老实实用TINYINT UNSIGNED代码层做常量映射。SET 类型是一个字段存多个选项底层用位图最多 64 个成员。它适合“标签”型需求但关系型数据库里这种场景通常拆关联表更清晰。我的建议是能不用就不用。再说 BOOLEANMySQL 没有真正的布尔类型BOOLEAN和BOOL都是TINYINT(1)的别名。所以is_active BOOLEAN NOT NULL DEFAULT TRUE实际存的是 0 或 1查询时用is_active 1最直观。6. 一张覆盖高频业务场景的选型速查表6.1 从业务字段到数据类型的对照清单下面这张表是我做表结构评审时常用的对照参考。它不是唯一标准但能覆盖大部分常见业务字段。字段类型场景推荐数据类型说明自增主键BIGINT UNSIGNED / INT UNSIGNED行数预估超 21 亿用 BIGINT否则 INT 足够订单号/业务单号BIGINT 或 VARCHAR(32)纯数字用 BIGINT含字母用 VARCHAR别混用状态/审核状态TINYINT UNSIGNED配合代码常量映射不要用字符串金额/余额DECIMAL(10,2) 或 DECIMAL(20,6)永远不用 FLOAT/DOUBLE手机号VARCHAR(20)不要用 BIGINT会丢前导 0身份证号CHAR(18) 或 VARCHAR(18)注意最后一位可能是 X昵称/姓名VARCHAR(50) 或 VARCHAR(64)没必要用 500邮箱/URLVARCHAR(255)作为登录名时注意前缀索引IP 地址VARCHAR(45)兼容 IPv6 长度文章正文MEDIUMTEXT / LONGTEXT建议独立表存储创建时间DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP用数据库时间更新时间DATETIME ON UPDATE CURRENT_TIMESTAMP应用层不要手动更新软删除标记TINYINT(1) 或 DATETIME NULL简单标记用 TINYINT需要时间线用 DATETIME关于敏感字段比如手机号、身份证号我还想多说一句即便数据类型选对了存储时也要按团队规范做加密或者脱敏不要在库里明文存全量证件信息这是基本的数据安全意识。还有一件事值得提醒MySQL 端的数据类型和语言侧的类型映射要提前对齐。比如 Java 读 DECIMAL 应该用 BigDecimal读 DATETIME 用 LocalDateTime读 TINYINT(1) 用 Boolean 或 IntegerPython 侧读取 DECIMAL 也要小心别被转成 float。很多线上问题不是 SQL 写错而是客户端解析时类型对不上数值悄悄变了精度。6.2 空值默认值一个约定省掉无数 bug表结构评审时我几乎会把所有“允许 NULL”的列都过一遍。NULL 在 MySQL 里有几个特殊行为COUNT(column)会忽略 NULL 值和COUNT(*)结果可能不一样。WHERE column ! 1不会返回 NULL 行三值逻辑容易写出隐蔽 bug。NULL 列在索引和统计信息上的表现不如定值明确。所以我的标准是能用NOT NULL DEFAULT就用。数值列默认 0字符串列默认空串时间列默认CURRENT_TIMESTAMP这样应用代码不用到处判空统计结果也稳定。真正允许 NULL 的通常只有“最后登录时间”“删除时间”这类语义上允许“从未发生”的字段但查询时一定要显式写IS NULL或IS NOT NULL不要依赖隐式逻辑。6.3 排序规则 collation 对 JOIN 的隐性破坏字符串类型除了字符集还有一个容易忽略的维度是排序规则 collation。它决定字符串比较时大小写是否敏感、排序用什么规则也直接影响 JOIN。报错长这样Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT)常见成因是老表用了latin1_swedish_ci后来新表用了utf8mb4_unicode_ci两边 JOIN 时排序规则不一致MySQL 拒绝隐式转换。处理办法有两个一是统一全库字符集和排序规则ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;二是临时在查询里显式指定SELECT * FROM a JOIN b ON a.nick b.nick COLLATE utf8mb4_unicode_ci;我的习惯是建库的时候统一成一种规则表级和列级不再单独覆盖个别字段需要区分大小写比如用户名登录校验单独指定utf8mb4_bin即可。专栏里经常看到有人问“为什么明明有索引却 JOIN 很慢”其实有不少就是排序规则不一致导致 MySQL 没法直接走索引比较只能逐行转换后再比对代价极高。7. 我做线上大表类型改造的踩坑记录7.1 直接 ALTER 大表等于给线上埋雷真实案例某个订单明细表已经 300GB状态字段要从varchar(20)改成TINYINT。当时执行了一句很常见的ALTER TABLE orders MODIFY COLUMN status TINYINT UNSIGNED NOT NULL DEFAULT 0;结果主库写入被长时间阻塞从库延迟飙到十几分钟最后只能 kill 掉。原因就是在 MySQL 5.7 下这种类型变更大概率需要重建表整个过程会持有严苛的表级元数据锁业务写入全部排队。如果一定要在线做大表类型改造我的首选是 Percona Toolkit 的pt-online-schema-change或者 GitHub 的gh-ost。它们通过建影子表、分批拷贝数据、触发器或 binlog 同步增量来平滑切换命令大致长这样pt-online-schema-change \ --alter MODIFY COLUMN status TINYINT UNSIGNED NOT NULL DEFAULT 0 \ Dapp,torders,h127.0.0.1,P3306,uop,pxxx \ --chunk-size1000 --max-lag3执行前一定要确认三件事磁盘空间够不够通常要预留接近原表大小的空余、测试环境有没有用同量级数据压过、变更窗口内有没有全链路监控。8.0 里“加列”这类操作支持ALGORITHMINSTANT会快很多但“改列类型”依然不能想当然还是按大表流程走一遍最稳。7.2 一次字符集改造凌晨两点被 JOIN 报错拉起来还有一次是字符集改造引发的事故。一个活动列表要和用户表 JOINMySQL 直接报Illegal mix of collations。查看建表语句发现用户表一直是utf8mb4_general_ci而新活动表建的时候默认成了utf8mb4_0900_ai_ci。两边都是 utf8mb4但排序规则不同照样不能直接比较。我当时的止血操作是在 JOIN 条件里显式转SELECT ... FROM activity a JOIN user u ON a.uid u.id WHERE u.nickname a.nickname COLLATE utf8mb4_unicode_ci;后续才在凌晨窗口统一了全库排序规则。这里提醒一点执行ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4这类语句时MySQL 会按旧字符集解释原有字节再转为新字符集。如果原表字符集本来就是错的比如 latin1 里存了正确的中文字节转换后可能出现“乱码恢复乱码”的二次事故。我的经验是先导出一批样例列用HEX()对比转换前后的十六进制确认无误再跑全表。顺带补一个冷门坑存储过程入参、函数返回值的数据类型也要跟着表一起改。比如列从 INT 改成 BIGINT存储过程入参还是 INT插入超出范围的数据时就会报out of range。表结构不是“表自己”的事应用层映射、存储过程、下游同步任务都要一起对齐。7.3 用 information_schema 给库做一次“体检”如果你接手了一个老项目又不想一行行读建表语句可以用 information_schema 快速摸清底细。下面几个 SQL 我每次巡检都会跑一遍。先找出超长字符列SELECT table_schema, table_name, column_name, data_type, character_maximum_length FROM information_schema.columns WHERE table_schema NOT IN (mysql,information_schema,performance_schema,sys) AND data_type IN (varchar,char) ORDER BY character_maximum_length DESC;重点看那些varchar(500)、varchar(2000)的列逐一确认业务是否真的需要。再看自增列的类型SELECT table_schema, table_name, column_name, column_type FROM information_schema.columns WHERE extra auto_increment;对照每张表的行数预估值判断主键是否已经逼近 INT 上限需要提前升级 BIGINT。最后看字符集不统一的业务表SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema NOT IN (mysql,information_schema,performance_schema,sys) AND table_collation NOT LIKE utf8mb4%;体检的目的是排出优先级不在同一天改所有表。我的习惯是先改“字符集旧 大 varchar 接近上限的自增主键”这三类高风险项每改完一张表都要观察主从延迟和慢查询曲线。我个人的体会是接手一套新系统的第一件事不是翻业务代码而是先跑这几个 SQL 把 schema 搂一遍。数据类型问题通常在系统运行一年后才集中爆发爆发时就是高 CPU、高磁盘、主从延迟这类硬故障。与其等故障上门不如把选型规则写进团队的建表评审规范里从第一张表就开始卡住。