表设计之数据类型优化

1. 概述

MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

2. 基本原则

2.1 越小越好

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

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

2.2 越简单越好

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

2.3 避免 NULL

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

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

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

3. 整数类型

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

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

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

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

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

4. 实数类型

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

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

浮点和 DECIMAL 类型都可以指定精度。对于 DECIMAL 列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。

MySQL 5.0 和更高版本将数字打包保存到一个二进制字符串中（每 4 个字节存 9 个数字)。例如，DECIMAL(18,9) 小数点两边将各存储 9 个数字，一共使用 9 个字节：小数点前的数字用 4 个字节，小数点后的数字用 4 个字节，小数点本身占 1 个字节。

MySQL 5.0 和更高版本中的 DECIMAL 类型允许最多 65 个数字。浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。FLOAT 使用 4 个字节存储，DOUBLE 占用 8 个字节，所以 DOUBLE 比 FLOAT 有更高的精度和更大的范围。

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

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

5. 字符串类型

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

5.1 VARCHAR

VARCHAR 类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间)。在内部实现上，既然是变长，VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度，如果列的最大长度小于或等于 255 字节，则只使用 1 个字节表示，否则使用 2 个字节。

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

5.2 CHAR

CHAR 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。

当存储 CHAR 值时，MySQL 会删除所有的末尾空格，CHAR 值会根据需要采用空格进行填充以方便比较。

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

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

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

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

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

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

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

5.3 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)，或者使用自己的应用程序逻辑来计算散列值。

6. 日期和时间类型

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 存储秒之后的小数部分。

7. 命名规范

1、数据库、表、字段的命名要遵守可读性原则，尽可能少使用或者不使用缩写；

2、表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑；

说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝；

3、表名不使用复数名词 ；

4、数据库、表、字段的命名禁用保留字，如 desc、range、match 之类 ；

5、对象的名字应该能够描述它所表示的对象 ，例如：

• 表的名称应该能够体现表中存储的数据内容，最好是遵循“业务名称_表的作用”；

• 库名与应用名称尽量一致。

6、主键索引名为 pk_ 字段名，唯一索引名为 uk_ 字段名，普通索引名则为 idx_ 字段名；

7、表达是与否概念的字段，应该使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）。