MySQL数据类型优化方案

MySQL数据类型优化

我的博客: bdsnake.top

数据类型优化

优化的原则：

更小的通常更好

更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，处理时需要的CPU周期更少

简单就好

比如整形比字符型操作代价更低，用MySQL内建类型存储日期和时间而不是字符串等等

尽量避免NULL

如果查询中包含NULL的列，对于MySQL来说更难优化

整数类型

• TINYINT 8位
• SMALLINT 16位
• MEDIUMINT 24位
• INT 32位
• BIGINT 64位

可用UNSIGNED修饰，可以吧存储范围都变成>=0

实数类型

• FLOAT 4字节
• DOUBLE 8字节
• DECIMAL n字节

DECIMAL是定点数，可以进行更加精确的运算

DECIMAL(M,D) M表示精度，即数字总长度;D表示小数点后的位数 5.0+ 版本最多允许总长65的数字

字符串类型

VARCHAR和CHAR

VARCHAR：可变长字符串

与定长字符串相比，通常能节省更多空间，因为VARCHAR仅使用必要的空间

VARCHAR需要使用1~2个额外字节记录字符串的长度。 加入食用latin1字符集，CHAR(1)占用1个字节，VARCHAR(1)占用2个字节。所以如果存储定长度为1的字符，CHAR要多省一半的空间

VARCHAR节省了存储空间，对性能有帮助，但是由于行是变长的，如果频繁变长字符串就需要额外的工作

适合使用VARCHAR的情况：

• 字符串列的最大长度比平均长度大很多
• 列的更新很少，碎片不是问题
• 使用了像UTF-8这样复杂的字符集，字符使用不同的字节数存储

使用时注意：

慷慨是不明智的

比如用VARCHAR(5) 和VARCHAR(200)存储HELLO，空间开销是一样的，但是更长的列会消耗更多内存，因为MySQL通常会分配固定大小的内存块保存内部值，尤其是使用内存临时表进行排序或操作时会特别糟糕，利用磁盘临时表排序时也会变得同样糟糕

CHAR:定长字符串

CHAR类型适合存储定长的字符串，与VARCHAR想比无需存储记录字符串长度的空间

适合经常变更的数据，不易产生碎片

短列，因为VARCHAR占了额外的空间

适合使用CHAR的情况：

• 密码的MD5值，因为它是定长的值
• 经常需要变更的数据
• 非常短的列

BLOB和TEXT类型

为存储很大的数据设计的字符串数据类型，分别采用二进制和字符方式存储

实现方式：MySQL把BLOB和TEXT当做一个独立对象处理，当其值过大时，InnoDB会使用专门的外部存储区进行存储，每个值在行内用1-4个字节存储一个指针，在外部存储区存储实际值

排序只对没格列最前max_sort_length排序，优化排序可减小max_sort_length或者用order by SUSTRING

MySQL不能将BLOB 和 TEXT 列全部长度的字符串进行索引

使用枚举(ENUM)代替字符串类型

e ENUM (‘fish’,‘apple’,‘dog’)

ENUM实际存储为正数而不是字符串可以通过在数字上下 文环境检索看到这个双重属性：

select e+0 from enum_test

排序时会按照这个数字排序，而不是按照字符串排序，所以排序结果和插入顺序有关。想要实现按照字符串排序，可以使用FIELD 或者使用函数显式指定排序

建议阅读:MySQL 枚举类型八宗罪

日期和时间

DATETIME

能保存大范围值，从1001年到9999年，精度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中， 与时区无关。使用8个字节的存储空间。

默认情况下，MySQL以一种可排序的、无歧义的格式显 示DATETIME 值，例如“2008-01-16 22:37:08”。这是ANSI标准定义的 日期和时间表示方法。

TIMESTAMP

就像它的名字一样，TIMETAMP 类型保存了从1970年1月1日午 夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相 同。TIMESTAMP 只使用4个字节的存储空间，因此它的范围比 DATETIME 小得多：只能表示从1970年到2038年。MySQL提供了 FROM_UNIXTIME() 函数把Unix时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为Unix时间戳。

除了特殊行为之外，通常也应该尽量使用TIMESTAMP ，因为它比 DATETIME 空间效率更高。有时候人们会将Unix时间截存储为整数值， 但这不会带来任何收益。用整数保存时间截的格式通常不方便处理，所 以我们不推荐这样做。

位数据类型

BIT

可以使用BIT 列在一列中存储一个或多个true/false值。BIT（1） 定 义一个包含单个位的字段，BIT（2） 存储2个位，依此类推。BIT 列的 最大长度是64个位。

MySQL把BIT 当作字符串类型，而不是数字类型。当检索 BIT（1） 的值时，结果是一个包含二进制0或1值的字符串，而不 是ASCII码的“0”或“1”。然而，在数字上下文的场景中检索时，结 果将是位字符串转换成的数字。如果需要和另外的值比较结果，一 定要记得这一点。例如，如果存储一个值b’00111001’ （二进制值 等于57）到BIT（8） 的列并且检索它，得到的内容是字符码为57 的字符串。也就是说得到ASCII码为57的字符“9”。但是在数字上下 文场景中，得到的是数字57：这是相当令人费解的，所以我们认为应该谨慎使用BIT 类型。 对于大部分应用，最好避免使用这种类型。

如果想在一个bit的存储空间中存储一个true/false值，另一个方 法是创建一个可以为空的CHAR（0） 列。该列可以保存空值（NULL ） 或者长度为零的字符串（空字符串）。

SET

如果需要保存很多true/false值，可以考虑合并这些列到一 个SET 数据类型，它在MySQL内部是以一系列打包的位的集合来表 示的。这样就有效地利用了存储空间，并且MySQL有像 FIND_IN_SET() 和FIELD() 这样的函数，方便地在查询中使用。它 的主要缺点是改变列的定义的代价较高：需要ALTER TABLE ，这对 大表来说是非常昂贵的操作

可以用替代SET的方法，如在整数列上进行按位操作，如可以吧八个位包装到一个TINYINT中，这时一个TINYINT就能合并存储8个TRUE/FALSE

选择标识符

为标识列选择合适的数据类型非常重要。一般来说更可能用标识列与其他值进行比较（例如股关联操作中），或者通过标识列寻找其他列。标志列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。

• 整数类型通常是标识列最好的选择，因为它们很快并且可以适用AUTO_INCREMENT。如果只是少数的状态标识符可以选用TINYINT。
• ENUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题大。ENUM和SET列适合存储固定信息，这样就相对限制了可扩展性。
• 如果可能应该尽量避免使用字符串类型作为标识列，因为他们很耗空间，并且通常比数字类型慢。

对于完全“随机”的字符串也要多加注意，例如MD5(), SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这回导致INSERT以及一些SELECT语句变得慢。