有张用户扩展信息表几千万行数据文件大概 120GB。重新设计了一遍字段类型同样的数据装进去只有 84GB少了 30%查询也快了。变的不是数据是列的定义。InnoDB 每行数据的实际结构InnoDB 的行格式COMPACT/DYNAMIC现在默认 DYNAMIC里每行的存储比你看到的字段多一些东西变长字段长度列表每个 VARCHAR、TEXT 类型的字段需要 1 到 2 个字节记录这个字段实际存了多少字节的数据。即使这个 VARCHAR 字段存的是空字符串也需要 1 字节记”长度为 0”。NULL 标志位每个可以为 NULL 的字段占 1 个比特。30 个可以为 NULL 的字段就是 4 个字节ceil(30⁄8) 4每行都要带着。行头5 字节存了记录类型、删除标记、下一条记录的指针等。事务 ID 回滚指针6 7 13 字节InnoDB 的 MVCC 需要。所以一张有主键的表除了真正的业务数据每行至少有 18 字节的固定开销再加变长字段的长度记录和 NULL 位图。可空列的隐藏成本如果一张表有 50 个可以为 NULL 的字段每行的 NULL 位图是 7 字节ceil(50⁄8)。哪怕这 50 个字段里实际上 40 个都存了值、没有 NULL这 7 字节照样存在用来标记”哪些是 NULL”。不是说不能用 NULL——NULL 有 NULL 的语义该用就用。但有很多列设计时随手写了NULL实际上永远不会存 NULL 值纯粹是习惯。这些列改成NOT NULL DEFAULT VARCHAR或者NOT NULL DEFAULT 0数字NULL 位图占用的比特位减少数据量大时节省的空间相当可观。数据类型选小不选大-- 用户状态0-正常、1-禁用、2-注销 status TINYINT NOT NULL DEFAULT 0 -- 1 字节 -- vs status INT NOT NULL DEFAULT 0 -- 4 字节 -- vs status VARCHAR(10) NOT NULL DEFAULT normal -- 6-8 字节加上长度前缀TINYINT 存 0/1/2完全够用1 字节。INT 是 4 字节BIGINT 是 8 字节如果字段值范围就是 0 到 127用 INT 是浪费了 3 字节乘以几千万行就是几百 MB。VARCHAR 存状态更是不经济不仅字段值本身要占字节还需要 1-2 字节的长度前缀加上 NULL 位图。状态、类型、标志这类枚举字段用 TINYINT 或 SMALLINT不用 VARCHAR。-- 金额字段 amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00 -- 5 字节 -- vs amount VARCHAR(20) NOT NULL DEFAULT 0.00 -- 实际存储 长度前缀金额用 DECIMAL 存储精确且紧凑。VARCHAR 存金额的做法能工作但每行要多几个字节还要应用层做格式转换不值得。看表的真实存储情况SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND((data_length index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.TABLES WHERE table_schema your_database ORDER BY total_mb DESC;哪张表最占空间一眼看清楚。看某张表每行平均多少字节SELECT ROUND(data_length / table_rows, 1) AS bytes_per_row FROM information_schema.TABLES WHERE table_schema your_db AND table_name your_table;算一下这张表有多少字段、什么类型理论上每行最少要多少字节和实际值对比。如果实际值明显比理论值大很多要么有很多 VARCHAR 字段存着很长的数据要么字段类型选得太宽松。实际操作时注意直接ALTER TABLE改字段类型或 NOT NULL对大表来说会重建整张表可能需要几小时并影响线上服务。生产环境用 gh-ost 或者 pt-online-schema-change 做在线变更不锁表。改字段类型还要确认应用层代码TINYINT 改完 ORM 映射是否对NOT NULL 改完现有代码里有没有显式传 null 的地方。存储优化通常不是关键路径上的工作但在 SSD 很贵、数据量很大、或者查询扫描大量行的场景下节省下来的存储和 IO 会体现在响应时间上。看一眼information_schema.TABLES里最大的几张表再看一眼建表语句通常能找到明显的优化点。
