数据库的设计，决定后期对数据库的操作，和性能的优化难度；
数据库结构设计不合理，那后期开发对数据库的操作和优化难度更高。
但也不是说，数据库结构设计好了之后，后期就不再进行优化了；正如 MySQL 的版本一般，每次的更新都会带有一些新的变化，也会对某些过去版本出现的问题进行优化。

这篇文章最主要讲述的是对 MySQL 数据库数据结构中的数据类型的选择。
主要参考来源为 《高性能 MySQL》 这本书 第 4 章

数据类型

MySQL 是一个关系型数据库管理系统，定义的每一个列都需要一个数据类型，可以理解为类似 Java 定义变量一般，都需要指定类型。
我们一起来看看 MySQL 都有哪些数据类型(表格数据来自网络)：

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 字节 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07		 YYYYMMDD HHMMSS 混合日期和时间值，时间戳
CHAR 0-255字节 — — 定长字符串
VARCHAR 0-65535 字节 — — 变长字符串
TINYBLOB 0-255字节 — — 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 — — 短文本字符串
BLOB 0-65 535字节 — — 二进制形式的长文本数据
TEXT 0-65 535字节 — — 长文本数据
MEDIUMBLOB 0-16 777 215字节 — — 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 — — 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 — — 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 — — 极大文本数据

数据类型的选择

MySQL 支持的数据类型有非常多，选择正确的数据类型对于获得高性能至关重要。
那么应该如何选择数据类型呢？
以下是列举几个比较简单的选择原则：

  1. 更小的通常更好
    1. 一般情况下尽量使用可以正确存储数据的最小数据类型。
    2. 更小的数据类型占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期更小
  2. 简单就好
    1. 简单的数据类型的操作通常需要更少的 CPU 周期
  3. 尽量避免 NULL
    1. 如果查询中包含可为 NULL 的列，会使得索引、索引统计和值变得更复杂
    2. 可为 NULL 的列会使用更多的存储空间，在 MySQL 里也要特殊处理
    3. 可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在 MyISAM(存储引擎) 里甚至还可能导致固定大小的索引变成可变大小的索引

很多 MySQL 的数据类型可以存储相同的类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。

整数类型

根据上表，整数类型为：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT；分别使用 1 、 2 、 3 、 4、 8 个字节，分别使用 8 , 16 , 24 , 32 , 64 为存储空间。

它们的存储的值的范围从 -2( N - 1 ) 到 2( N - 1 ) - 1 ,其中 N 为存储空间的位数。

整型数值的定义可以使用 UNSIGNED 无符号整型属性，这大致可以使正数的上限提高一倍。

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

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

实数类型

实数是带有小数部分的数字，类型为 FLOAT , DOUBLE , DECIMAL ;

它们不只是为了存储小数部分；也可以使用 DECIMAL 存储比 BIGINT 还大的整数。

MySQL 既支持精确类型，也支持不精确类型。

有多种方法可以指定浮点列所需要的精度，这会使得 MySQL 选择不同的数据类型，或者在存储时进行取舍。这些精度定义是非标准的，所以建议只指定数据类型，而不制定精度。

DECIMAL 类型最多允许 65 个数字；因为 DECIMAL 只是一种存储格式，在计算中 DECIMAL 会转换为 DOUBLE 类型。

MySQL 使用 DOUBLE 为内部浮点计算类型。

字符串类型

MySQL 支持多种字符串类型，每种类型还有很多的变种。每个字符串列可以定义自己的字符集和排序规则，或者说是校对规则。这些东西会很大程度上影响性能。

VARCHAR 和 CHAR 类型

  1. VARCHAR

    1. VARCHAR 类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。
    2. VARCHAR 需要使用 1 或者 2 个额外字节记录字符串的长度。
    3. VARCHAR 节省了存储空间，对性能提升有所帮助。但由于行是变长的，在 UPDATE 时可能使行变得比原来的长，这就导致需要做额外的工作。（如果一个行占用的空间增长，并且页内没有更多的空间可以存储的情况下，不同的引擎处理方式是不一样的——需参考存储引擎的工作原理）

  2. CHAR

    1. CAHR 类型是定长的，MySQL 根据定义的长度分配足够的空间
    2. 存储值时，MySQL 会将末尾的所有空格删除

注：更长的列列会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值。

BLOB 和 TEXT 类型

大型数据存储类型：BLOB 和 TEXT ，分别采用二进制和字符方式存储。

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

BLOB 和 TEXT 不同的是：

  1. BLOB 类型存储的值时二进制数据，没有排序规则或字符集
  2. TEXT 类型有排序规则和字符集

MySQL 对 BLOB 和 TEXT 列进行排序与其他的类型不同，MySQL 只对最前 max_sort_length 字节而不是整个字符串进行排序，可通过 ORDER BY SUSSTRING(column, length)

注：MySQL 不能将 BLOB 和 TEXT 列全部长度的字符串进行索引，也不能使用这些索引消除排序。

使用枚举代替字符串类型

枚举列看可以将一些不重复的字符串存储成一个预定义的集合。

MySQL 在存储枚举时非常的紧凑，会根据列表值的数量压缩到一个或者两个字节中。

MySQL 在内部会将每个值在列表中的位置保存为整数，并且在表的 .frm 文件中保存“数字-字符串”映射关系的“查找表”。

使用枚举列会使得数据库表大小减小。

注：
枚举字符串列表是固定的，故添加或删除字符串必须使用 ALTER TABLE。所以对于经常变换的字符串列表，不该使用枚举类型。
枚举列与字符串 CHAR/VARCHAR 列关联会比使用两个枚举列关联速度要慢

日期和时间类型

MySQL 能够存储的最小时间粒度为秒（MariaDB支持微秒级别的事件类型）。

MySQL 提供两种时间类型：

  1. DATETIME
    • 保存大范围的值，从 1001 年到 9999 年，精度为秒。
    • 时间日期封装格式为 YYYYMMDDHHMMSS 的整数
    • 与时区无关
    • 存储空间为 8 个字节
  2. TIMESTAMP
    • 保存从 1970 年 1 月 1 日午夜（格林尼治标准时间）以来的秒数，它和 UNIX 时间戳相同。
    • 存储空间为 4 个字节
    • 保存范围为 1970 年到 2038 年
    • 与时区有关
    • 比 DATETIME 空间效率更高

位数据类型

MySQL 提供两种位数据类型：

  1. BIT
    1. 可以使用 BIT 列在一列中存储一个或者多个 true | false 值
    2. 最大长度为 64 个位
    3. MySQL 将 BIT 当成是字符串类型，而不是数字类型
    4. 存储的值为二进制字串
  2. SET
    1. 在 MySQL 内部是以一系列打包的位的集合来表示
    2. 缺点
      1. 改变列的代价较高，需要 ALTER TABLE；
      2. 无法在 SET 列上使用索引
选择标识符

标识列可能在另外的表中作为外键使用，所以为标识列选择数据类型是，应该选择跟关联表中的对应列一样的类型。

选择标识列的类型时，要考虑存储类型，还要考虑 MySQL 对这种类型怎么执行计算和比较。

在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。

注：一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确的匹配，包括像 UNSIGNED 这样的属性。混用不同的数据类型可能导致性能问题，即使没有性能问题，在比较操作时隐式类型转换可能导致很难发现的错误。

  1. 整型类型
    1. 整数通常是标识列的最好的选择，因为它们很快并且可以使用 AUTO_INCREMENT
  2. ENUM 和 SET 类型
    1. 因为 ENUM 和 SET 类型定义值是比较固定的，并且修改的花销很大，所以对于标识列来说，这是一个不推荐的选择
  3. 字符串类型
    1. 字符串类型占用空间，并且比数字类型要慢得多
    2. 对于完全“随机”的字符串，如 MD5()、SHA1() 或者 UUID() 产生的字符串，这些函数生成的新值会任意分布在很大的空间内，这回导致 INSERT 以及 SELECT 语句变得很慢
      1. 因为插入值会随机地写索引在不同的位置，所以使得 INSERT 语句更慢。这回导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片
      2. SELECT 语句变得更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方
      3. 随机值导致混村对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的范文局部性原理失效。如果整个数据集都一样的“热”，那么缓存任何一部分特地给数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。

总结

数据库表的设计需要根据需求选择合适的数据类型，避免不同的数据类型间相互影响，导致影响数据库的性能。

  1. 尽量避免使用在对列进行数据变更时对整张表的修改的数据类型
  2. 尽量使用占用空间较小且适合的数据类型
  3. 尽量避免数据可为 NULL
  4. 在进行表关联时，尽量使关联的列使用同一种数据类型，避免不必要的转换开销

最后更新： 2019年11月30日 23:56

原始链接： https://maiyikai.github.io/2019/11/19/1574147663/

赏
× ~谢谢大爷~
打赏二维码