数据库优化之数据类型优化

一. 预估上限，更小优先

· 原因

        1. 更小的数据类型占用更少的磁盘、内存、CPU缓存、CPU周期。

        2. 无符号和有符号类型的存储消耗、性能消耗是一致的，但同级别的无符号类型能存储的正数大小上限大约是有符号类型的2倍。

        3. DECIMAL类型相比FLOAT和DOUBLE占用更多的存储和计算资源。

        4. 数据库在进行内存/磁盘临时表排序等操作时，通常会分配固定大小的内存来保存内部值。

· 原则

        1. 尽量在建表期评估字段可能出现的上限大小或上限长度。例如预期某字段存储200内的无符号整形，则tinyint unsigned是最适合的数据类型。

        2. 若预估存储的数据全为正数，则优先使用大小最合适的无符号类型。

        3. 尽量不使用DECIMAL类型存储小数。存储需要非常精确的数字时（如存储金融、财务数据时），可以先约定倍数，然后使用BIGINT类型存储。例如某财务数据要求精确到万分之一，则可以把所有金额乘以100万，然后存储到BIGINT类型的列中。这样可以同时避免浮点类型存储/计算不精确和DECIMAL精确计算代价高的问题。

        4. 使用VARCHAR/VARBINARY时设置合理的长度范围，避免数据库执行操作时的资源无效消耗。例如使用VARCHAR(5)和VARCHAR(200)存储'hello'，尽管存储消耗相等，但在内部排序等操作的时候VARCHAR(200)消耗的资源远多于VARCHAR(5)。

· 参考

整数类型储值范围参考

类型	最小值	最大值	存储空间(位)
TINYINT	-128	127	8
TINYINT UNSIGNED	0	255	8
SMALLINT	-32768	32767	16
SMALLINT UNSIGNED	0	65535	16
MEDIUMINT	-8388608	8388607	24
MEDIUMINT UNSIGNED	0	16777215	24
INT	-2147483648	2147483647	32
INT UNSIGNED	0	4294967295	32
BIGINT	-2^63	2^63 - 1	64
BIGINT UNSIGNED	0	2^64 - 1	64

---

二. 优先使用简单的内建类型代替复杂类型，整数优先

· 原因

        1. 由于字符集、校对规则、排序规则等因素，字符串类型消耗的资源代价远超整数类型。

        2. 很多场景下会将随机生成的字符串作为标志符，如MD5()、SHA1()、UUID()生成的字符串。这类字符串会任意分布在很大的空间内，从而导致INSERT及部分SELECT语句效率变得很低。因为

                (1) 插入的值会随机写到索引的不同位置，会使得INSERT语句变慢，导致页分裂、磁盘随机访问、碎片问题等。

                (2) SELECT语句变慢是因为逻辑上相邻的行为分布在磁盘和内存的不同地方。

                (3) 随机值导致缓存对所有类型的查询语句效果都很差。

· 原则

        1. 尽量使用内建时间类型存储时间而非使用字符串类型。

        2. 尽量使用数字类型存储IP而非使用字符串，并通过INET_ATON()、INET_NTOA()转换。

        3. 部分场景下可使用整数（自定义标识）/SET/ENUM类型存储有限字符串集合，避免无效冗余。

        4. 将随机生成的字符串作为标识符时，可采用整数化、二进制化的方案进行存储。例如用UNHEX()函数将UUID值转换成16字节的数字进行存储（存储在BINARY(16)的列中，检索时通过HEX()函数将其格式化为16进制的格式）

· 注意事项

        1. 尽量避免使用数字作为ENUM/SET的枚举常量，索引值与枚举常量均为数字类型容易导致混乱；例如ENUM('1', '2', '3')

        2. ENUM/SET适合作为静态定义表存储固定的信息，如有序的状态、产品类型、性别等；具备拓展性，但不易删、改之前定义的项。如果经常需要修改定义信息，则可以考虑弃用ENUM/SET等内建类型，改用整数作为自定义标识。

---

三. 尽量避免设计可为NULL的字段

· 原因

        1. 可为NULL的列会使索引、索引统计、值比较等操作变得复杂。

        2. 可为NULL的列占用更多存储空间；当其被索引时，每个索引记录占用1个额外的字节（在MyISAM中还会导致固定大小的索引变成可变大小的索引）。

---

四. CHAR/VARCHAR、BINARY/VARBINARY的选择

· 区别

        1. CHAR、BINARY类型是定长存储，VARCHAR、VARBINARY类型是变长存储。变长的优势在于存储长度灵活，但是在某些场景下有额外的开销（例如A本来的长度为5，某次UPDATE使其长度变为100，在这个过程中数据库需要重新为其分配存储空间，并解决存储碎片问题）。

        2. 数据库为VARCHAR、VARBINARY类型的数据分配额外的1-2个字节，用于长度，因此在某些场景下滥用变长类型反而会导致占用更多的存储空间。

        3. 定长类型会进行填充和截断操作，其中CHAR类型的填充和截断字符为空格' '，BINARY的填充和截断字节为零字节'\0'。例如将"string   "存储至CHAR(10)中，检索结果为"string"。某些业务下应该通过字符转换或改用变长类型来避免这种情况的发生。

· 适用场景

        变长类型: VARCHAR、VARBINARY

                1. 存储对象的最大长度比平均长度大很多

                2. 列(长度)更新不频繁，不用担心碎片问题。

                3. 使用了UTF-8等复杂的字符集，每个字符都使用不同的长度进行存储。

                4. 想要避免意料之外的截断。

        · 定长类型: CHAR、BINARY

                1. 存储对象长度较短(短值进行定长存储相对变长存储更省空间，因为不需要额外存储长度)。

                2. 存储对象长度均接近某一值（如MD5值）。

                3. 列(长度)更新频繁。

---

五. BOLB/TEXT类型优化

· 原因

        数据库针对BLOB/TEXT类型的排序方案是独特的，即默认只对每个列的最前max_sort_length而非整个存储对象进行排序。

· 方案

        1. 将存储对象的排序因子写在数据的头部，并尽量保证简短。

        2. 设置合理的max_sort_length，或在排序时使用ORDER BY SUBSTRING(column, length)进行预切割。

---

六. 时间类型

· DATATIME

        1. 占用8字节，存储格式为YYYYMMDDHHMMSS、与时区无关的整数。

        2. 存储范围为1001年 - 9999年。

        3. 优势在于无"2038年"问题。

· TIMESTAMP

        1. 占用4字节，存储时间戳整数；可用FROM_UNIXTIME()和UNIX_TIMESTAMP()转换。

        2. 存储范围为1970 - 2038年。

        3. 优势在于资源占用低。

· 微妙存储

        1. MariaDB支持微妙存储。

        2. 其他DB可以使用BIGINT类型存储微妙级别的时间戳或使用DOUBLE类型存储小数部分。