1、优先选择符合存储需要的最小的数据类型
原因
列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少,在遍历时所需要的 IO 次数也就越多, 索引的性能也就越差
方法
1)将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据。
mysql 提供了两个方法来处理 ip 地址:
inet_aton 把 ip 转为无符号整型(4-8 位)
inet_ntoa 把整型的 ip 转为地址
插入数据前,先用 inet_aton 把 ip 地址转为整型,可以节省空间。显示数据时,使用 inet_ntoa 把整型的 ip 地址转为地址显示即可。
2)对于非负型的数据(如自增 ID、整型 IP)来说,要优先使用无符号整型来存储
因为:无符号相对于有符号可以多出一倍的存储空间
SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295
VARCHAR(N)中的 N 代表的是字符数,而不是字节数
使用 UTF8 存储 255 个汉字 Varchar(255)=765 个字节。过大的长度会消耗更多的内存
2、避免使用 TEXT、BLOB 数据类型,最常见的 TEXT 类型可以存储 64k 的数据
建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
Mysql 内存临时表不支持 TEXT、BLOB 这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行。
而且对于这种数据,Mysql 还是要进行二次查询,会使 sql 性能变得很差,但是不是说一定不能使用这样的数据类型。
如果一定要使用,建议把 BLOB 或是 TEXT 列分离到单独的扩展表中,查询时一定不要使用 select * 而只需要取出必要的列,不需要 TEXT 列的数据时不要对该列进行查询。
TEXT 或 BLOB 类型只能使用前缀索引
因为 MySQL 对索引字段长度是有限制的,所以 TEXT 类型只能使用前缀索引,并且 TEXT 列上是不能有默认值的。
3、避免使用 ENUM 类型
修改 ENUM 值需要使用 ALTER 语句
ENUM 类型的 ORDER BY 操作效率低,需要额外操作
禁止使用数值作为 ENUM 的枚举值
4、尽可能把所有列定义为 NOT NULL
原因:
索引 NULL 列需要额外的空间来保存,所以要占用更多的空间;
进行比较和计算时要对 NULL 值做特别的处理
5、使用 TIMESTAMP(4 个字节)或 DATETIME 类型(8 个字节)存储时间
TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
TIMESTAMP 占用 4 字节和 INT 相同,但比 INT 可读性高
超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储。
经常会有人用字符串存储日期型的数据(不正确的做法):
缺点 1:无法用日期函数进行计算和比较
缺点 2:用字符串存储日期要占用更多的空间
6、同财务相关的金额类数据必须使用 decimal 类型
非精准浮点:float,double
精准浮点:decimal
Decimal 类型为精准浮点数,在计算时不会丢失精度。占用空间由定义的宽度决定,每 4 个字节可以存储 9 位数字,并且小数点要占用一个字节。可用于存储比 bigint 更大的整型数据。
来源: UP 技术控 ,作者 conan5566