这一章主要是讲表字段的一些优化和陷阱。
字段优化的一般原则:
- 占用空间越小越好。
这样磁盘、内存、CPU缓存占用空间少,且需要CPU处理的周期也越少。
比如能用tinyint就别用int - 类型越简单越好
这样CPU周期数也越少。比如用int代替字符,因为字符有字符集和排序规则等,更复杂。这里有两个例子:一个是应该将日期和时间存储为MySQL的内置类型而不是字符串类型,另外一个是应该用整型而不是字符串存储IP地址。
用varchar(15)和int对比,只需要4字节存IPV4地址,节约12字节(就是12字节,因为varchar需要额外空间保存长度)。因为IP实际上就是数字,为了便于查看,才用点号分成的四部分。 - 尽量避免存NULL
如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。这个优化效果不明显,可以忽略。MySQL会对NULL值进行索引,而Oracle则不会。
整数、实数
int(11)和int(20)在计算和存储上没有差别,只是在一些客户端上显示不同的字符个数而已。
实数一般可以用float、double、decimal。float和double使用的空间更少,但是高精度时不准确,所以不推荐指定精度。比如float(5,2) :最多表示5个数字(包括整数和小数个数),小数最多两位。所以整数最多占3位,剩下的2位是给小数用的。
比如1234不合法,因为变为1234.00超过了5位。
在对小数进行精确计算时才使用DECIMAL——例如,存储财务数据。
一些大容量的场景,可以考虑使用BIGINT代替DECIMAL。 比如把数字乘以一百万,
(比如12345.6789元,乘以100万就是12345678900)然后用bigint存储,既可以避免float的精度问题和decimal精确计算代价高的问题。
字符
varchar和char。
varchar:变长。适合存变动少的、字符串列的最大长度远大于平均长度;使用了UTF-8复杂的字符集
char:定长。适合存贮短的字符串 或者 长度差不多的字符串。
分析:
- varchar是变长的,定义varchar(50),比如之前存的是“ab”,后面改成了“abcddd…”这样可能导致他在原本的存储位置上不够,可能导致页的分裂啥的。
- “字符串列的最大长度远大于平均长度”。比如名字,平均4个字符左右,我们可以指定varchar(50),这样比char(50)更好。
- 在 UTF-8 编码中,每个字符的长度不是固定的,而是根据字符的 Unicode 码点来决定的。一些字符可能只需要 1 个字节来存储,而一些特殊字符或 Unicode 码点较高的字符可能需要 2、3 或更多个字节来存储。
- varchar需要额外的1~2个字节来保存长度。比如varchar(10),实际需要11个字节。varchar(256),实际需要12个字节,其中2个字节用来保存长度。
注意,当存储CHAR值时,MySQL删除所有尾随空格。如果需要进行比较,值会用空格填充。
blob和text
BLOB和TEXT用来存储大的数据,分别采用二进制和字符方式存储。text有字符集和排序规则。
MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引,也不能使用索引进行排序。
使用枚举代替(不推荐)
当可以穷举时,可以用枚举代替。一般更节约空间。在存储时,会根据定义的位置存为数字。所以不要枚举数字ENUM(‘1’,‘2’,‘3’)。
执行下面语句,看到结果:
CREATE TABLE TEST_ENUM (
ID TINYINT PRIMARY KEY,
ANIMAL ENUM('TIGER','BIRD','LION')
);
INSERT INTO TEST_ENUM(ID,ANIMAL) VALUES(1,'LION'),(2,'BIRD'),(3,'TIGER');
SELECT * FROM TEST_ENUM ORDER BY ANIMAL;
注意enum和varchar去联接时,会变慢。远不如与自身类型的去连接。
通常会使用主键作为连接,避免使用字符串进行连接。
如图,QPS为1.7和1.8,说明二者连接慢。
日期
datetime和timestamp。
timestamp省空间,但是会遇到2038年问题。
一般还是推荐使用datetime。具体看是否满足需要。
其他可替代方案:
- 用date 和time两个字段存储。
- 用int存。比如20240729155029,表示2024-07-29 15:50:29。或者可以存时间戳。
- 用字符串存。(不推荐。原因见开头)
位压缩数据类型(不推荐)
bit和set.从技术上来说都是字符串类型。
bit每一位可以表示true或false。实际存的是二进制01的字符串。注意在数字上下文中,会变为数字。如图,bit里面存的是二进制00111001。10进制下是57,ASCII表示的是9。所以不推荐使用。
set一个字段可以表示多位true或false。他的一个用法:
比如有一个权限字段,读写删权限。创建该字段时:author set(read,delete,write)
查读权限的时候可以select * from xx where find_in_set(read,author)
。
用一个tinyint也可以实现。
read,1﹤﹤0,0001
write,1﹤﹤1,0010
delete,1﹤﹤2,0100
多个权限时,直接相加就行。
查询读权限时可以按位与where author & 1。
查询读写权限where author & 1 & 2.
那set和二进制有啥区别呢?
改变set的枚举值时,需要alter table语句,而二进制不需要。但是二进制可能不方便查看
参考:高性能mysql第四版 -第6章 schema设计与管理
作者:WKP9418
本文地址:https://blog.csdn.net/qq_43179428/article/details/140754556