MySQL之Schema与数据类型优化(四)

Schema与数据类型优化

日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE.MySQL能存储的最小时间粒度为秒(MariaDB支持微秒级别的时间类型)。但是MySQL也可以使用微秒级别的粒度进行临时运算，接下来会展示如何绕开这种存储限制。大部分时间类型都没有替代品，因此没有什么事最佳选择的问题。唯一的问题是保存日期和时间的时候需要做什么。MySQL提供两种相似的日期类型:DATETIME和TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些场景，一个比另一个工作得好。

• 1.DATETIME
  这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。默认情况下，MySQL以一种可排序的、无歧义的格式显式DATETIME值，例如"2008-01-16 22:37:08"。这是ANSI标准帝国一的日期和时间表示方法。
• 2.TIMESTAMP
  就像它的名字一样，TIMESTAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多:只能表示1970年到2038年。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把七日转换为Unix时间戳。MySQL4.1以及更新的版本按照DATETIME的方式格式化TIMESTAMP的值，但是MySQL4.0以及更老的版本不会在各个部分之间显式任何标点符号。这仅仅是显式格式上的区别，TIMESTAMP的存储格式在各个版本都是一样的。
  TIMESTAMP显式地值也依赖于时区。MySQL服务器、操作系统，以及客户端连接都有时区设置。因此，存储值为0地TIMESTAMP在美国东部时区显式为"1969-12-31 19:00:00"，与格林尼治时间差5个小时。有必要强调一下这个区别:如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。
  TIMESTAMP也有DATETIME没有的特殊属性。默认情况下，如果插入时没有指定第一个TIMESTAMP的值，MySQL则设置这个列的值为当前时间(TIMESTAMP的行为规则比较复杂，并且在不同的MySQL版本里会变动，所以你应该验证数据库的行为是你需要的。一个好的方式是修改完TIMESTAMP列后用SHOW CREATE TABLE命令检查输出)。在插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值(除非在UPDATE语句中明确指定了值)。你可以配置任何TIMESTAMP列插入和更新行为。最后，TIMETSAMP列默认为NOT NULL，这也和其他的数据类型不一样。

除了特殊行为之外，通常也应该尽量使用TIMESTAMP,因为它比DATETIME空间效率更高。有时候人们会将Unix时间戳存储为整数值，但这不会带来任何收益。用整数保存时间戳的格式通常不方便处理，所以不推荐这样做。如果需要存储比秒更小粒度的日期和时间值怎么办呢？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式:可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分，这两种方式都可以，或者也可以使用MariaDB替代MySQL

位数据类型

MySQL有少数 集中存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

• 1.BIT
  在MySQL5.0之前，BIT是TINYINT的同义词。但是在MySQL5.0以及更新版本，这是一个特性完全不同的数据类型。可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段,BIT(2)存储2个位，依此类推，BIT列的最大长度是64个位。BIT的行为因存储引擎而异。MyISAM会打包存储所有的BIT列，所以17个单独的BIT列只需要17个位存储(假设没有可为NULL的列)，这样MyISAM只适用3个字节就能存储17个BIT列。其他存储引擎例如Memory和InnoDB，为每个BIT列使用一个豿存储的最小整数类型来存放，所以不能节省存储空间。
  MySQL把BIT当作字符串类型，而不是数字类型。当检索BIT(1)的值时，结果是一个包含二进制0或1值得字符串，而不是ASCII码的"0"或"1".然而，在数字上下文的场景中检索时，结果将时位字符串转换成的数字。如果需要和另外的值比较结果，一定要记得这一点。例如，如果存储一个值b’00111001’(二进制值等于57)到BIT(8)的列并且检索它，得到的内容是字符码为57的字符串。也就是说得到00111001。但是在数字上下文场景中，得到的是数字57:

mysql> INSERT INTO bittest VALUES(b'00111001');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT CONCAT(a,' ') AS 'a ', a, a+0 FROM bittest;
+----+----------+-----+
| a  | a        | a+0 |
+----+----------+-----+
| 9  | 00111001 |  57 |
+----+----------+-----+
1 row in set (0.08 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.44    |
+-----------+
1 row in set (0.08 sec)

这是相当令人费解的，所以我们认为应当谨慎使用使用BIT类型。对于大部分应用，最好避免使用这种类型。如果想在一个bit的存储空间中个存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存控制(NULL)或者长度为零的字符串(空字符串)

• 2.SET
  如果需要保存很多的true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以以一系列打包的位的集合来表示的。这样就有效地利用了存储空间，并且MySQL有像FIND_IN_SET()和FIELD()这样的函数，方便地在查询中使用。它的主要缺点是改变列的定义的代价较高:需要ALTER TABLE,这对达标来说非常昂贵的操作。一半来说，也无法在SET列上通过索引查找。
• 3.在整数列上进行按位操作
  一种替代SET的方式使用一个整数包装一系列的位。例如，可以把8个位包装到一个TINYINT中，并且按位操作来使用。可以在应用中为每个位定义名称常量来简化这个工作。比起SET,这种办法主要的好处在于可以不使用ALTER TABLE改变字段代表的"枚举"值，缺点是查询语句更难写，并且更难理解(当第5个bit位被设置时是什么意思?)一些人非常适应这种方式，也有一些人不适应，所以是否采用这种技术取决于个人的偏好。

例子

• 举个例子
  一个包装位的应用的例子是保存权限的访问控制表(ACL)。每个位或者SET元素代表一个值，例如CAN_READ、CAN_WRITE，或者CAN_DELETE。如果使用SET列，可以让MySQL在列定义里存储位到值得映射关系；入股哦使用整数列，则可以在应用代码里存储这个对应关系。这是使用SET列时的查询:

mysql> CREATE TABLE acl(perms SET ('CAN_READ', 'CAN_WRITE','CAN_DELETE') NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM acl WHERE FIND_IN_SET('CAN_READ', perms);
+---------------------+
| perms               |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+
1 row in set (0.07 sec)

如果用整数来存储，则可以参考下面的例子:

mysql> CREATE TABLE ack_number(perms TINYINT UNSIGNED NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO ack_number(perms) VALUES(@CAN_READ + @CAN_DELETE);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT perms FROM ack_number WHERE perms & @CAN_READ;
+-------+
| perms |
+-------+
|     5 |
+-------+
1 row in set (0.07 sec)

这里我们使用MySQL变量来定义值

选择标识符(identifier)

为表示列(identifier column)选择合适的数据类型非常重要。一般来说更有可能用标识符与其他值进行比较(例如，在关联操作中)，或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应当选择跟关联表中的对应列一样的类型(在相关的表中使用相同的数据类型是个好注意，因为这些列很可能在关联中使用)。
当选择标识列的类型时，不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。例如，MySQL在内部使用整数存储ENUM和SET类型，然后在做比较操作时转换为字符串。一旦选定了一种类型，要确保在所有关联表种都是用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性(如果使用的是InnoDB存储引擎，将不能在数据类型不是完全匹配的情况下创建外键，否则会有保存信息:“ERROR 1005(HY000):Can’t create table”,这个信息可能让人迷惑不解，这个问题在MySQL邮件组也经常有人抱怨(但奇怪的是，在不同长度的VARCHAR列上创建外键又是可以的))混用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现的错误。这种错误可能会很久以后才突然出现，那时候可能都已经忘记是在比较不同的数据类型。
在可以满足值得范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型/；例如有一个state_id列存储美国各州的名字(这是关联到另一张存储名字的表的ID)就不需要几千或几百万个值，所以不需要使用INT。TINYINT足够存储，而且比INT少了3个字节。如果用这个值作为其他表的外键，3个字节可能导致很大的性能差异。下面是一些小技巧

• 1.整数类型
  整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT
• 2.ENUM和SET类型
  对于标识列来说，ENUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态"定义表"来说可能是没有问题的。ENUM和SET列适合存储固定信息，例如有序的状态、产品类型、人的性别。举个例子，如果使用枚举字段来定义产品类型，也许会设计一张以这个枚举字段为主键的查找表(可以在查找表种增加一些列来保存描述性质的文本，这样就能够生成一个术语表，或者为网站的下拉菜单提供有意义的标签)。这时，使用枚举类型作为标识列是可行的，但是大部分情况下都要避免这么做。
• 3.字符串类型
  如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通过常比数字类型慢。尤其是在MyISAM表里使用字符串作为标识列时要特别小心。MyISAM默认对字符串使用压缩索引，这回导致查询慢得多。最多会有6倍的性能下降。
• 4.对于完全"随机"的字符串也需要多加注意
  例如MD5()、SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢(另一方面，对一些有很多写的特别大的表，这总伪随机值实际上可以帮助消除热点)。
  3.1 因为插入值会随机地写到索引的不同为止，所以使得INSERT语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引刷碎片。
  3.2 SELECT语句会变得很慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方
  3.3 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得 缓存赖以工作的访问局部性原理失效。如果整个数据集都是一样"热"，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中
  如果存储UUID，则应该移除"-"符号，或者更好的做法是，用UNHEX()函数转换UUID值伪16字节的数字，并且存储在一个BINARY(16)列种。检索时可以通过HEX()函数来格式化十六进制格式。
  UUID()生成的值与加密散列函数例如SHA1()生成的值有不同的特征:UUID虽然分布也不均匀，但还是有一定顺序的。尽管如此，但还是不如递增的整数好用