《高性能MySql》笔记二

---

1、字段类型设计原则

• smaller is usually better.（更小的通常更好）
• simple is good.(简单就好)
• avoid null if possible.(尽量避免 NULL) 注：NULL列改成非NULL，对性能提升有限。

2、数字类型

整数/whole numbers：

类型	位数	范围
inyint	8 bits	-2^(8-1) ~ 2^(8-1)-1
smallint	16 bits	-2^(16-1) ~ 2^(16-1)-1
mediumint	24 bits	-2^(24-1) ~ 2^(24-1)-1
int	32 bits	-2^(32-1) ~ 2^(32-1)-1
bigint	64 bits	-2^(64-1) ~ 2^(64-1)-1

实数/real numbers

1. float 和 double 使用标准的浮点数运算计算出近似值
2. float 32 bits，double 64 bits
3. decimal 是精确的实数，没有精度损失

注意事项：

1. MySQL 进行整数运算使用的是 bigint，float 和 double 的运算使用的是 double
2. decimal 能够提供精确运算可以用于金融数字，但是运算开销大。建议把小数扩大一定倍数后，按整数对待

3、字符串

对于字符串，不同的存储引擎实现方式不同，下面都是 innodb 的行为。（MyISAM engine：MySql 8.0 已经移除，不再支持）

变长字符串/varchar：

1. 额外使用 1～2个字节存储字符串长度，超过 255 时使用两个字节记录
2. varchar 节省存储空间，但是被更改时，一行数据的长度发生变化，会产生碎片或者页面分裂，导致额外的处理
3. varchar 适用场景：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这个的复杂的字符集，每个字符都使用了不同的字节数进行存储。
4. MySQL 在处理 varchar 时会对它占用的内存进行悲观假设，varchar 的最大长度不要超过实际需要

定长字符串/char：

1. 适用于短小的定长字符串，譬如密码的 md5 数值
2. 通常比 varchar 更高效，并且对修改友好

变长字节/varbinary：

1. 和 varchar 类似，存放变长的二进制字符串

定长字节/binary：

1. 和 char 类似，存放二进制字符串

注意事项：

1. binary 字符串的比较操作比 char 字符串更高效
2. 如果 varchar(5) 满足需求，不要使用 varchar(200），MySQL 内部处理会对需要的内存进行悲观假设，譬如临时表。

4、 文本

文本被作为一个单独对象处理，innodb 会把大的文本单独存放。

字符文本/text：

tinytext
smalltext
text  等同于 smalltext
mediumtext
longtext

二进制文本/blob：

tinyblob
smallblob
blob 等同于 smallblob
mediumblob
longblob

注意事项：

1. 对文本进行比较，MySQL 只比较最前面 max_sort_length 个字符
2. 不能建立索引
3. 不要把图片存放成 blob，建议存到磁盘上，在数据库里记录位置

5、时间

datetime：

1. 时间范围广 1000～9999年，精度毫秒，8 字节，日期部分 YYYYMMDDHHMMSS 存储为整数，和时区分开存放
2. 跨时区使用时，显示存入的字面时间，不转换

timestamp：

1. 1970 年开始的秒数，GMT 时间，精度为秒，4 字节，最多到 2038年1月19日
2. 跨时区使用时，显示在当前时区的时间，会转换

注意事项：

1. 为了避免 timestamp 的 2038 问题，直接用整数类型存放时间戳，unsigned 32-bit 支持到 2106 年

6、 主键的选择

1. 设定主键后，其它表引用主键时，一定要用同样的定义，避免额外转换开销或转换错误
2. 主键类型占用的空间应尽可能小
3. 优先选择整数类型，避免使用字符串
4. 避免使用完全随机数，随机会导致数值分布空间大，数值分布不可预测，影响缓存加载，增加插入和查询开销
5. 如果要存放UUID，转换成16字节的数字存放

注意事项：

1. ORM 以及一些框架自动生成的表定义，很可能使用了不合理的字段类型

7、 其它注意事项

1. 表的列数如果有上百行，会带来大量的 cpu 开销，即使只使用几列，innodb 也需要解析所有数据列
2. 太多表 join 会成为严重问题，MySQL 设置的上限为 61 张表