MySQL-Schema与数据类型优化

良好的数据表设计是高性能的基石,应该根据系统需要执行的查询语句来设计 Schema,这往往需要需要考虑很多的因素,例如使用烦范式设计可以加快某些类型的查询,但同时也会使另一些查询变慢,为列创建索引也可以提高查询效率,添加计数表和汇总表可以优化查询速度,但是这会增加维护这些表的成本,当然这些都需要根据实际的情况来选择。让我们先一起了解下设计 Schema 时对数据类型的选择优化。

选择优化的数据类型

MySQL 支持很多的数据类型,合理的选择数据类型由助于提高性能,以下有几个建议的原则:

更小的数据类型

更小的数据类型占据更小的磁盘、内存和 CPU,但是要确保能够存储之后需要存储的值,否则后续修改会很痛苦,因为对大表修改表结构是很耗时间的操作。

使用简单的数据类型

简单的数据类型耗费更少的 CPU 资源,如整型的操作就比字符操作代价更低(字符集的校对规则比整型更加复杂),例如使用 MySQL 内建的类型存储日期时间而不是使用字符串存储日期时间,使用整型存储 IP 地址而不是使用字符串存储。

尽量避免NULL

可为 NULL 的列使得索引、索引统计和值比较变得更加复杂。可为 NULL 的列使用索引时会使用更多的存储空间,在 MySQL 中需要特殊的处理,每个索引需要一个额外的字节,如果要在某一列上建立索引,尽量设计成 NOT NULL。把可以 NULL 的列变 NOT NULL 带来的性能提示较小,对于大表如果确定 NULL 值会带来问题才会选择去优化它。
例如在 where 子条件查询中对字段进行 NULL 判断 (如 where num is null ),会导致引擎放弃索引而进行全表扫描,但是如果将 NULL 判断更改为 where num = 0 引擎则会使用索引进行扫描。

数据类型

MySQL 支持多种的数据类型,了解这些数据类型有助于我们合理的设计 Scheme,提高查询的性能。

整数类型

MySQL 整数类型支持的整数类型如下:

类型 存储空间
TINYINT 8位
SMALLINT 16位
MEDIUMINT 24位
INT 32位
BIGINT 64位
  1. 可以添加无符号关键字,大致可以使正数的范围提高一倍,如 TINYINT UNSIGNED 的存储范围为 0 ~ 255,TINYINT 则是 -128 ~ 127。
  2. MySQL 可以为正数指定宽度,如 INT(11),但是他不会改变值的范围,对于存储和计算来说 INT(1) 和 INT(20) 都是一样的。
  3. 对于 1,2 这样的数字字符建议直接使用整型数据存储,比使用 CHAR(1) 的效率要高。

关于 IPv4 地址的存储,有人喜欢使用 VARCHAR(15) 来存储,但是 IP 地址实际上是 32 位的无符号整数,用小数点分成四段只是方便人们阅读容易,所以使用无符号整数来存储,MySQL 提供了 INET_ATON() 和 INTE_NTOA() 函数在这两种表示方法间转换。

实数类型

类型 存储空间
FLOAT 4字节(32位)
DOUBLE 8字节(64位)
DECIMAL - -

FLOAT 和 DOUBLE 支持使用标准的浮点运算进行近似运算,这会导致精度的损失,DECIMAL 用于存储精确的小数,因为 DECIMAL 需要额外的计算空间和计算开销,除非需要对小数进行精确计算时才使用 DECIMAL 类型,例如存储财务数据,但是在数据量大的情况下可以考虑 BIGINT 来替代。

字符串类型

MySQL 支持的字符串类型有 VARCHAR、CAHR、NVARCHAR、NCHAR。

VARCHAR

  1. VARCHAR 用于存储可变长度字符串,它只是用必要的空间,比定长类型更省空间。
  2. VARCHAR 需要额外的 1 个或者 2 个字节记录字符串的长度,如果列的最大长度大于等于 255 字节没需要 1 个额外的字节表示,否则需要 2 个额外的字节。
  3. 在更新 VARCHAR 列时,如果长度超过了设置的最大可变长度,MyISAM 会将行拆成不同的片段存储,而 InnoDB 则需要分裂页来使行可以放进页内,这会导致碎片问题。
  4. 还有一点要注意的是,尽管 VARCHAR(5) 和 VARCHAR(200) 存储 5 个长度的字符串时消耗的空间是一样的,但是使用内存临时表进行排序的时候或操作的时候会消耗更多的内存资源。

CHAR

  1. CHAR 类型是定长的,并且 MySQL 在存储 CHAR 类型的时候会自动的去掉行尾的空字符串,VARCHAR 却不会去掉这些空字符。
  2. CHAR 适合存储短的或者长度都差不多的字符串,例如 MD5 的值,对于单个字符串,CHAR(1) 也要比 VARCHAR(1) 要好,因为 VARCHAR(1) 还要一个额外的字节保存数据的长度。

BLOB 和 TEXT 类型

BOLB 和 TEXT 都是为存储很大的数据设计的,具体的类型如下表,注意 SMALLBOLB 和 BOLB,SMALLTEXT 和 TEXT 是一样的。

类型 存储空间 存储类型
TINYBOLB 255字节 二进制
SMALLBOLB 65535字节 二进制
BOLB 65535字节 二进制
MEDIUMBOLB 16777215字节 二进制
LONGBOLB 4294967295字节 二进制
TINYTEXT 255字符 字符
SMALLTEXT 65535字符 字符
TEXT 65535字符 字符
MEDIUMTEXT 16777215字符 字符
LONGBOTEXT 4294967295字符 字符
  1. 当 BOLB 和 TEXT 值太大时,InnoDB 会使用外部的存储区域存储,内部的存储区则会需要存储 1 ~ 4 个字节存储一个指针,通过指针到外部区域获取实际的值。
  2. MySQL 对 BOLB 和 TEXT 列进行排序是与其他类型不同,只对每个列的最前 max_sort_length 个字节进行排序,如果只需要排序前面的一小部分可以减小 max_sort_length 的值或者使用 ORDER BY SUSTRING(column, length) 来进行排序。

日期时间类型

MYSQL 中保存时间的相关类型有 YEAR、MONTH、DAY、HOUR、DATETIME、TIMESTAMP 等等,主要说一说 DATETIME 和 TIMESTAMP 的区别。

TIMESTAMP

  1. 占 4 个字节,能表示的时间范围为 1970 年到 2038 年;
  2. 默认不能为 NULL;
  3. 与时区设置有关;
  4. 不能使用 MySQL 提供的时间函数;
  5. 修改了记录中的字段会自动更新第一个 TIMESTAMP 列的值 ( 可以设置 DEFAULT CURRENT_TIMESTAMP 不自动更新 );
  6. 条件范围搜索可以方便的使用 BETWEEN;
  7. 适合记录数据的最后修改时间和需要大量时间范围查询的表;

DATETIME

  1. 占 8 个字节,能表示的时间范围为 1001 年到 9999 年;
  2. 值可以为 NULL;
  3. 与时区设置无关;
  4. 可以很方便使用 MySQL 提供的时间函数;

有人喜欢将日期时间存储为整数值,但是我个人喜欢使用 DATETIME,一是存储整数值的收益不大,DATETIME 底层也是使用整型数值存储,效率并没有提高多少; 二是 DATETIME 是一种可排序的,直接显示出标准的时间格式,易于查看,利于维护和排查问题,整型数字需要进行转换;最后一点,DATETIME 可以使用 MySQL 提供的一系列时间函数;

单个查询如果希望执行速度快且并发性好,一次查询关联的表最好不要超过 12 张表,所以设计 Schema 的时候,表之间的关系要考虑清楚。

参考资料

本文结束,感谢您的阅读!