表字段优化

这一章主要是讲表字段的一些优化和陷阱。
字段优化的一般原则：

1. 占用空间越小越好。
   这样磁盘、内存、CPU缓存占用空间少，且需要CPU处理的周期也越少。
   比如能用tinyint就别用int
2. 类型越简单越好
   这样CPU周期数也越少。比如用int代替字符，因为字符有字符集和排序规则等，更复杂。这里有两个例子：一个是应该将日期和时间存储为MySQL的内置类型而不是字符串类型，另外一个是应该用整型而不是字符串存储IP地址。
   用varchar（15）和int对比，只需要4字节存IPV4地址，节约12字节（就是12字节，因为varchar需要额外空间保存长度）。因为IP实际上就是数字，为了便于查看，才用点号分成的四部分。
3. 尽量避免存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：定长。适合存贮短的字符串 或者 长度差不多的字符串。
分析：

1. varchar是变长的，定义varchar（50），比如之前存的是“ab”，后面改成了“abcddd…”这样可能导致他在原本的存储位置上不够，可能导致页的分裂啥的。
2. “字符串列的最大长度远大于平均长度”。比如名字，平均4个字符左右，我们可以指定varchar(50)，这样比char(50)更好。
3. 在 UTF-8 编码中，每个字符的长度不是固定的，而是根据字符的 Unicode 码点来决定的。一些字符可能只需要 1 个字节来存储，而一些特殊字符或 Unicode 码点较高的字符可能需要 2、3 或更多个字节来存储。
4. 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