字段数据类型优化

---

MySQL学习系列

---

基本原则

• 更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。 更小的数据 类型通常更快， 因为它们占用更少的磁盘、 内存和 CPU 缓存， 并且处理时需要 的 CPU 周期也更少。

但是要确保没有低估需要存储的值的范围， 因为在的多个地方增加数据类型 的范围是一个非常耗时和痛苦的操作。 如果无法确定哪个数据类型是最好的， 就 选择你认为不会超过范围的最小类型。

• 简单就好

简单数据类型的操作通常需要更少的 CPU 周期。 例如， 整型比字符操作代 价更低， 因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。 比如 应该使用 MySQL 内建的类型而不是字符串来存储日期和时间。

• 尽量避免 NULL

很多表都包含可为 NULL(空值)的列， 即使应用程序并不需要保存 NULL 也是 如此， 这是因为可为 NULL 是列的默认属性。 通常情况下最好指定列为 NOT NULL， 除非真的需要存储 NULL 值。

如果查询中包含可为 NULL 的列， 对 MySQL 来说更难优化， 因为可为 NULL 的列使得索引、 索引统计和值比较都更复杂。 可为 NULL 的列会使用更多的存储 空间， 在 MySQL 里也需要特殊处理。 当可为 NULL 的列被索引时， 每个索引记录 需要一个额外的字节。

通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小， 所以（调优 时） 没有必要首先在现有 schema 中查找并修改掉这种情况， 除非确定这会导致 问题。 但是， 如果计划在列上建索引， 就应该尽量避免设计成可为 NULL 的列

---

INT/整数类型

存储整数,可以使用这几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间,也就是1、2、3、4、8个字节。它们可以存储的值的范围请自行计算。

同时整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如TINYINTUNSIGNED可以存储的范围是0 ~ 255,而TINYINT的存储范围是-128 ~127。

有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。

MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的,它不会限制值的合法范围,只是规定了MySQL的一些交互工具（例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。

在选择上,遵循着更小的通常更好的原则,在业务许可的情况下,尽量选择位数小的。

---

实数类型

实数是带有小数部分的数字。MySQL既支持精确类型的存储DECIMAL类型，也支持不精确类型存储FLOAT和DOUBLE类型。

DECIMAL类型用于存储精确的小数，本质上MySQL是以字符串形式存放的。所以CPU不支持对DECIMAL的直接计算，所以在MySQL中自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字)。例如，DECIMAL(18,9)小数点两边将各存储9个数字，一共使用9个字节:小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。

MySQL5.0和更高版本中的DECIMAL类型允许最多65个数字。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储，DOUBLE占用8个字节，所以DOUBLE比FLOAT有更高的精度和更大的范围。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务或金融数据，在精度不敏感和需要快速运算的时候，选择FLOAT和DOUBLE。

但在数据量比较大的而且要求精度时，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

---

字符串类型

MysQL 支持多种字符串类型， 包括 VARCHAR 和 CHAR 类型、 BLOB 和 TEXT类型、 ENUM（枚举） 和 SET 类型。

VARCHAR 和 CHAR 类型

VARCHAR 和 CHAR 是两种最主要的字符串类型。

• VARCHAR

VARCHAR 类型用于存储可变长字符串， 是最常见的字符串数据类型。 它比定长类型更节省空间， 因为它仅使用必要的空间（例如， 越短的字符串使用越少的空间)。

在内部实现上， 既然是变长， VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度， 如果列的最大长度小于或等于 255 字节,则只使用 1 个字节表示,否则使用 2 个字节。

VARCHAR 节省了存储空间， 所以对性能也有帮助。 但是， 由于行是变长的，在 UPDATE 时新值比旧值长时， 使行变得比原来更长， 这就肯能导致需要做额外的工作。 如果一个行占用的空间增长， 并且在页内没有更多的空间可以存储， 在这种情况下， MyISAM 会将行拆成不同的片段存储， InnoDB 则需要分裂页来使行可以放进页内。

• CHAR

CHAR 类型是定长的， MySQL 总是根据定义的字符串长度分配足够的空间。当存储 CHAR 值时， MySQL 会删除所有的末尾空格， CHAR 值会根据需要采用空格进行填充以方便比较。

• 小结

在 CHAR 和 VARCHAR 的选择上， 这些情况下使用 VARCHAR 是合适的：

字符串列的最大长度比平均长度大很多﹔ 列的更新很少； 使用了像 UTF-8 这样复杂的字符集， 每个字符都使用不同的字节数进行存储。

CHAR 适合存储很短的字符串， 或者所有值定长或都接近同一个长度。 例如，CHAR 非常适合存储密码的 MD5 值， 因为这是一个定长的值。 对于经常变更的数据， CHAR 也比 VARCHAR 更好， 因为定长的 CHAR 类型不容易产生碎片。

对于非常短的列， CHAR 比 VARCHAR 在存储空间上也更有效率。 例如用CHAR( 1)来存储只有 Y 和 N 的值， 如果采用单字节字符集只需要一个字节， 但是VARCHAR(1)却需要两个字节， 因为还有一个记录长度的额外字节。

另外， 使用 VARCHAR(5)和 VARCHAR(200)存储’hello’在磁盘空间上开销是一样的。 我们随便选择一个就好？ 应该使用更短的列， 为什么?

事实证明有很大的优势。 更长的列会消耗更多的内存， 因为 MySQL 通常会分配固定大小的内存块来保存内部值。 尤其是使用内存临时表进行排序或操作时会特别糟糕。 在利用磁盘临时表进行排序时也同样糟糕。

所以最好的策略是只分配真正需要的空间。

BLOB 和 TEXT 类型

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

与其他类型不同， MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。 存储引擎在存储时通常会做特殊处理。 当 BLOB 和 TEXT 值太大时， InnoDB 会使 用专门的“外部” 存储区域来进行存储， 此时每个值在行内需要 1~4 个字节存储 一个指针， 然后在外部存储区域存储实际的值。

BLOB 和 TEXT 家族之间仅有的不同是 BLOB 类型存储的是二进制数据， 没有 排序规则或字符集， 而 TEXT 类型有字符集和排序规则。

使用 BLOB 和 TEXT 要慎重：

(1） BLOB 和 TEXT 值会引起一些性能问题， 所以尽量避免使用 BLOB 和 TEXT 类型；

(2） 一定要用， 建议把 BLOB 或 TEXT 列分离到单独的表中；

(3） 在不必要的时候避免检索大型的 BLOB 或 TEXT 值。 例如， SELECT *查询 就不是很好的想法， 除非能够确定作为约束条件的 WHERE 子句只会找到所需要 的数据行。 否则， 很可能毫无目的地在网络上传输大量的值。 建议可以搜索索引 列， 决定需要的哪些数据行， 然后从符合条件的数据行中检索 BLOB 或 TEXT 值；

(4） 还可以使用合成的(Synthetic)索引来提高大文本字段(BLOB 或 TEXT)的查 询性能。 简单来说， 合成索引就是根据大文本字段的内容建立一个散列值， 并把 这个值存储在单独的数据列中， 接下来就可以通过检索散列值找到数据行了。 但 是， 要注意这种技术只能用于精确匹配的查询（ 散列值对于类似“<” 或“>=” 等范围搜索操作符是没有用处的)。 可以使用 MD5 函数生成散列值， 也可以使用 SHA1(或 CRC32)， 或者使用自己的应用程序逻辑来计算散列值。

必要时，使用枚举代替字符串

如果表中的字段的取值是固定几个字符串， 可以使用枚举列代替常用的字符串类型。

枚举列可以把一些不重复的字符串存储成一个预定义的集合。 MySQL 在存储枚举时非常紧凑， 会根据列表值的数量压缩到一个或者两个字节中， MySQL 在内部会将每个值在列表中的位置保存为整数， 这样的话可以让表的大小大为缩小。

CREATE TABLE enum_test(e ENUM(' fish', 'apple', 'dog') NOT NULL);
INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

但是要注意，
1） 因为枚举列实际存储为整数， 而不是字符串， 所以不要使用数字作为ENUM 枚举常量， 这种双重性很容易导致混乱， 例如 ENUN( ’ 1’,‘2’， ‘3’)。
2） 枚举字段是按照内部存储的整数而不是定义的字符串进行排序的， 所以尽量按照需要的顺序来定义枚举列。

---

日期和时间类型

MySQL 可以使用许多类型来保存日期和时间值， 例如 YEAR 和 DATE。 MySQL 能存储的最小时间粒度为秒。

大部分时间类型都没有替代品， 因此没有什么是最佳选择的问题。 唯一的问 题 MySQL 提供两种相似的日期类型:DATETIME 和 TIMESTAMP。 对于很多应用程 序， 它们都能工作， 但是在某些场景， 需要做些适当选择。

DATETIME

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

TIMESTAMP

TIMETAMP 类型保存了从 1970 年 1 月 1 日午夜（格林尼治标准时间)以来的秒数， 它和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节的存储空间， 因此它的范围比 DATETIME 小得多﹔ 只能表示从 1970 年到 2038 年。 TIMESTAMP 显示的值也依赖于时区。 从空间效率来说， 当然 TIMETAMP 比 DATETIME 更高。

如果需要存储比秒更小粒度的日期和时间值怎么办？ MySQL 目前没有提供合适的数据类型， 但是可以使用自己的存储格式： 可以使用 BIGINT 类型存储微秒级别的时间截， 或者使用 DOUBLE 存储秒之后的小数部分。

---