关键词数据类型、INT、VARCHAR、DECIMAL、存储空间优化、数据库设计大家好我是小耶写功课只是为了我踩过的坑你们别再踩了刚做DBA那会儿我接过一个历史遗留库一张订单表600万行光表文件就占了80多个G。查了半天发现里头有个order_no字段——明明只有10位数字建表的人居然用了VARCHAR(255)。就这一个字段白白多占了将近30G。你可能觉得30G在现在不算什么。但数据库不是硬盘多出来的每一字节都会影响Buffer Pool命中率、扫描行数、IO吞吐。表越胖查询越慢备份越久恢复越悬。今天就把数据类型这件事掰开揉碎讲清楚。几个先搞明白的概念数据类型数据库用来规定一列能存什么格式数据的规则。就像容器的形状——圆的装球、方的装砖拿错了就塞不进去。存储空间不同类型在磁盘和内存里占的字节数不同。一个TINYINT只占1个字节一个BIGINT占8个字节。8倍的差距放到千万级数据上就是实打实的性能差距。隐式转换当比较的两个字段类型不一致时数据库会在底层悄悄做类型转换。这个动作会直接导致索引失效——这也是为什么类型选择不仅是存储问题还是查询性能问题。整数家族INT、TINYINT、SMALLINT、BIGINT整数类型是数据库里最基础的类型选错的情况也最多。各类型的容量和存储空间类型存储空间有符号范围无符号范围适用场景TINYINT1字节-128 ~ 1270 ~ 255状态码、是否标志SMALLINT2字节-32768 ~ 327670 ~ 65535年龄、评分MEDIUMINT3字节-8388608 ~ 83886070 ~ 16777215中等范围计数INT4字节-21亿 ~ 21亿0 ~ 42亿主键ID、外键BIGINT8字节-922亿亿 ~ 922亿亿0 ~ 1844亿亿雪花ID、时间戳实战场景场景一状态字段很多表里都有个status字段通常就3到5个枚举值-- 错误示范用INT存只有3个值的状态CREATETABLEorders(idINTAUTO_INCREMENTPRIMARYKEY,statusINTNOTNULLDEFAULT0-- 0待支付 1已支付 2已取消);这个status用TINYINT就够了从4字节变成1字节。一张500万行的表光这一个字段就省了15MB。-- 正确写法CREATETABLEorders(idINTAUTO_INCREMENTPRIMARYKEY,statusTINYINTNOTNULLDEFAULT0);场景二主键IDINT的上限是21亿多如果你的表日增10万条21亿够用57年。但对于一些高并发场景如果用了雪花算法生成ID那就得上BIGINT——雪花ID是64位整数塞进INT直接溢出报错。-- 雪花算法ID必须BIGINTCREATETABLEuser_logs(idBIGINTPRIMARYKEY,-- 雪花ID18位数字user_idINTNOTNULL,actionVARCHAR(50));场景三 UNSIGNED的取舍UNSIGNED可以把正数范围翻一倍。INT UNSIGNED上限变成42亿。如果你的字段永远不会有负数比如年龄、库存数量加上UNSIGNED能省出额外的容量空间CREATETABLEproducts(idINTAUTO_INCREMENTPRIMARYKEY,stockINTUNSIGNEDNOTNULLDEFAULT0-- 库存不可能为负);字符串家族VARCHAR vs CHAR核心区别CHAR是固定长度CHAR(10)不管你存的是a还是abcdefghij都占10个字符的空间。不足的部分用空格补齐。VARCHAR是可变长度VARCHAR(10)存a只占1个字符加1到2个字节的长度前缀存abcdefghij才占12个字节。维度CHARVARCHAR存储方式固定长度空格补齐可变长度按需分配实际占用声明长度实际长度 1~2字节查询速度略快不需要计算长度略慢需处理变长逻辑适用场景固定长度数据手机号、身份证长度不定的数据用户名、地址一个容易忽略的坑VARCHAR的长度声明很多人习惯性写VARCHAR(255)觉得反正用多少占多少声明大点总没错。这个理解对了一半。VARCHAR在存储时确实是按需分配但数据库在执行查询时会按照声明长度来分配内存中的临时缓冲区。也就是说你用VARCHAR(255)存一个平均只有20个字符的字段磁盘上省了但内存排序、临时表的开销并没有省。而且MySQL的单行最大长度限制是65535字节。如果你一张表里有十几个VARCHAR(255)碰到某些需要内存临时表的操作很容易撞到上限。建议按照业务上限来设。用户名VARCHAR(50)、邮箱VARCHAR(100)、地址VARCHAR(200)。别偷懒全写255。实测对比我用一张300万行的用户表做了对比测试方案字段定义表文件大小全表扫描耗时Aname VARCHAR(255)1.8 GB4.2 sBname VARCHAR(50)1.2 GB2.8 sCname CHAR(50)2.1 GB3.9 sB方案最优——VARCHAR 合理长度存储空间和查询速度都赢了。小数家族DECIMAL vs FLOAT vs DOUBLE什么时候用哪个类型存储方式精度适用场景DECIMAL(M,D)精确存储精确到指定位数金额、价格、税率FLOAT近似存储单精度约7位有效数字科学计算、传感器数据DOUBLE近似存储双精度约15位有效数字高精度科学计算金额字段必须用DECIMAL这是DBA圈里的铁律。原因很简单——FLOAT和DOUBLE是近似存储会出现精度丢失-- 用FLOAT存金额出事了CREATETABLEwallet_float(idINTPRIMARYKEY,balanceFLOAT(10,2));INSERTINTOwallet_floatVALUES(1,100.00);INSERTINTOwallet_floatVALUES(2,0.10);-- 查一下SELECTbalanceFROMwallet_floatWHEREid2;-- 结果0.1 看起来正常-- 但做计算的时候SELECT0.10.2;-- FLOAT结果0.30000000000000004 ← 问题暴露用DECIMAL就不会有这个问题-- 金额用DECIMAL精确到分CREATETABLEwallet_decimal(idINTPRIMARYKEY,balanceDECIMAL(10,2)-- 总共10位小数2位最大99999999.99);DECIMAL的存储空间DECIMAL是按需分配的每9位十进制数字占4个字节DECIMAL(M,D)存储空间DECIMAL(5,2)3字节DECIMAL(10,2)5字节DECIMAL(18,4)9字节一般来说金额字段用DECIMAL(10,2)够存到9999万DECIMAL(12,2)够存到99亿99%的业务场景足够了。其他常见类型的选择建议日期时间DATE3字节只存日期如生日、下单日期TIME3字节只存时间如营业时段DATETIME8字节日期时间如创建时间、更新时间TIMESTAMP4字节自动时区转换适合记录操作时间戳不需要毫秒的话别用DATETIME(6)8字节变不了但索引键变长了布尔值MySQL没有原生BOOLEAN用TINYINT(1)代替0表示false1表示true。JSONMySQL 5.7支持原生JSON类型比TEXT存JSON字符串更省空间还支持路径索引。但频繁查询JSON里的字段不如拆成独立列——JSON适合做扩展字段不适合做核心查询条件。快速决策表把上面的内容浓缩成一张表建表的时候直接查数据特征推荐类型举例是/否、状态码TINYINTstatus, is_deleted自增主键INT AUTO_INCREMENTid雪花算法IDBIGINTid年龄、数量SMALLINT / INT UNSIGNEDage, count手机号、身份证CHAR(11) / CHAR(18)phone, id_card用户名、标题VARCHAR(N)按实际设username, title金额、价格DECIMAL(10,2)price, amount百分比、权重DECIMAL(5,2)discount, weight日期不含时间DATEbirthday, order_date创建/更新时间DATETIME / TIMESTAMPcreated_at, updated_at科学测量数据DOUBLEtemperature, latitude扩展配置字段JSONextra_config, metadata小耶在手SQL不愁。还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~