企业官网建设流程全解析

先说个我实际遇到的事。前两年帮一个创业团队做线上数据库巡检，他们的用户表已经接近 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 五种，差别只在字节数和范围。我用得最多的判断标准是“先算业务上限，再选最小可用类型”。

有个习惯值得改：主键不要一上来就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 里要用 BigDecimal，Python 里注意 Decimal 和 float 的隐式转换，否则数据库里算得好好的，一进业务代码精度就丢了，这种 bug 排查起来非常隐蔽。

3. 字符串类型：CHAR、VARCHAR、TEXT 的边界和隐性开销

3.1 字符集先决定“一个字符等于多少字节”

字符串类型最容易被忽略的前置条件是字符集。MySQL 里VARCHAR(n)的 n 是“字符数”，不是字节数，但底层存储完全按字节算。不同字符集下，一个字符占用的字节数完全不同。

• latin1：1 字符 = 1 字节
• gbk：1 字符 = 2 字节
• utf8mb3（即老utf8）：1 字符最多 3 字节
• utf8mb4：1 字符最多 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 最大 64KB，MEDIUMTEXT 最大 16MB，LONGTEXT 最大 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，很多人凭感觉二选一，但其实它们的语义差别很大。

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:00'

4.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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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。所以高频更新的字段不要放 JSON，JSON 适合低频写入的扩展属性、透传报文、配置快照。

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 个成员。它适合“标签”型需求，但关系型数据库里这种场景通常拆关联表更清晰。我的建议是能不用就不用。

再说 BOOLEAN：MySQL 没有真正的布尔类型，BOOLEAN和BOOL都是TINYINT(1)的别名。所以is_active BOOLEAN NOT NULL DEFAULT TRUE实际存的是 0 或 1，查询时用is_active = 1最直观。

6. 一张覆盖高频业务场景的选型速查表

6.1 从业务字段到数据类型的对照清单

下面这张表是我做表结构评审时常用的对照参考。它不是唯一标准，但能覆盖大部分常见业务字段。

关于敏感字段比如手机号、身份证号，我还想多说一句：即便数据类型选对了，存储时也要按团队规范做加密或者脱敏，不要在库里明文存全量证件信息，这是基本的数据安全意识。

还有一件事值得提醒：MySQL 端的数据类型和语言侧的类型映射要提前对齐。比如 Java 读 DECIMAL 应该用 BigDecimal，读 DATETIME 用 LocalDateTime，读 TINYINT(1) 用 Boolean 或 Integer；Python 侧读取 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" \ D=app,t=orders,h=127.0.0.1,P=3306,u=op,p=xxx \ --chunk-size=1000 --max-lag=3

执行前一定要确认三件事：磁盘空间够不够（通常要预留接近原表大小的空余）、测试环境有没有用同量级数据压过、变更窗口内有没有全链路监控。8.0 里“加列”这类操作支持ALGORITHM=INSTANT，会快很多，但“改列类型”依然不能想当然，还是按大表流程走一遍最稳。

7.2 一次字符集改造，凌晨两点被 JOIN 报错拉起来

还有一次是字符集改造引发的事故。一个活动列表要和用户表 JOIN，MySQL 直接报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、高磁盘、主从延迟这类硬故障。与其等故障上门，不如把选型规则写进团队的建表评审规范里，从第一张表就开始卡住。