第6章-1 数据类型-CSDN博客

本文链接：https://blog.csdn.net/qq_35461948/article/details/146326137

上一篇：《第4章-5 linux 网络管理》，接着开始讲平时更为用到的内容。

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要运行的特定查询设计schema。这通常需要权衡各种因素。例如，反范式的schema可以加速某些类型的查询，但同时可能减慢其他类型的查询。添加计数器和汇总表是一个优化查询的好方法，但它们的维护成本可能很高。MySQL的某些独有的特性和实现细节对性能的影响也很大。

同样，schema也会随着时间的推移而变化——这是你了解如何存储和访问数据以及业务需求如何随时间而变化的结果，这意味着应该将修改schema作为一个常见事件来规划。在本章的后面部分，我们将介绍如何避免此活动成为组织的运维瓶颈。

本章和聚焦在索引优化的下一章，涵盖了特定于MySQL的schema设计。我们假设你已经知道如何设计数据库，所以本章既不会介绍如何入门数据库设计，也不会讲解数据库设计方面的深入内容。这一章关注的是MySQL数据库的设计，主要介绍的是MySQL数据库设计与其他关系数据库管理系统的区别。如果你需要学习数据库设计的基础知识，我们推荐Clare Churcher的Beginning Database Design（Apress出版社出版）一书。

本章内容是为接下来的两章做铺垫的。在这三章中，我们将讨论逻辑设计、物理设计和查询执行，以及它们之间的相互作用。这既需要关注全局，又需要关注细节，还需要理解整个系统以便弄清楚各个部分如何相互影响。如果在阅读完关于索引的第7章和关于查询优化的第8章之后再来回顾这一章，也许你会发现本章很有用。很多讨论的主题都不能孤立地考虑。

选择优化的数据类型

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

原则：

更小的通常更好

一般来说，尽量使用能够正确存储和表示数据的最小数据类型。更小的数据类型通常更快，因为它们占用的磁盘、内存和CPU缓存的空间更少，并且处理时需要的CPU周期也更少。

但也要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型范围是一个痛苦且耗时的操作。如果无法确定哪个数据类型是最好的，请选择你认为不会超过的最小数据类型。（如果系统不是很忙或存储的数据量不大，或者是在设计过程的早期阶段，也可以在之后轻松地修改数据类型）。

简单为好

简单数据类型的操作通常需要更少的CPU周期。例如，整型数据比字符型数据的比较操作代价更低，因为字符集和排序规则（collation）使字符型数据的比较更复杂。这里有两个例子：一个是应该将日期和时间存储为MySQL的内置类型而不是字符串类型，另外一个是应该用整型数据存储IP地址。稍后我们将专门讨论这个话题。

尽量避免存储NULL

即使应用程序本身并不需要存储NULL（缺失值），很多表也包含可为NULL的列，这是因为NULL可以是列的默认属性。通常情况下最好指定列为NOT NULL，除非明确需要存储NULL值。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中查找并修改这种情况，除非确定这会导致问题。

在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等。这通常是很简单的，但是我们会提到一些特殊的不是那么直观的案例。

下一步是选择具体类型。很多MySQL数据类型可以存储相同类型的数据，但在存储的值范围、表示的精度或者需要的物理空间（磁盘和内存）上存在着差异。相同大类型的不同子数据类型有时也有一些特殊的行为和属性。

例如，DATETIME和TIMESAMP列可以存储相同类型的数据：时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，还会根据时区变化，而且具有特殊的自动更新能力。

另一方面，TIMESTAMP允许的时间范围要小得多，有时候它的特殊能力会成为障碍。

本章只讨论基本的数据类型。MySQL为了兼容性支持很多别名，例如，INTEGER（映射到INT）、BOOL（映射到TINYINT）和NUMERIC（映射到DECIMAL）。它们都只是别名。这些别名可能令人不解，但不会影响性能。如果建表时采用数据类型的别名，然后用SHOW CREATE TABLE检查，会发现MySQL报告的是基本类型，而不是别名。

整数类型

有两种类型的数字：整数（whole number）和实数（real number，带有小数部分的数字）。如果存储整数，可以使用这几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT。它们分别使用8、16、24、32和64位存储空间。可以存储的值的范围从-2 （N-1）到2 （N-1） -1，其中N是存储空间的位数。

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

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

你的选择决定了MySQL在内存和磁盘中保存数据的方式。然而，整数计算通常使用64位的BIGINT整数。（一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算。）

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

实数类型

实数是带有小数部分的数字。然而，它们不仅适用于带小数的数字，也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。如果你需要知道浮点运算是怎么计算的，则需要研究平台的浮点数的具体实现方式。

有两种方式可以指定浮点列所需的精度，这可能会导致MySQL以静默方式选择不同的数据类型，或者在存储值时对其进行近似处理。这些精度说明符是非标准的，因此我们建议只指定数据类型，不指定精度。

浮点类型通常比DECIMAL使用更少的空间来存储相同范围的值。FLOAT列使用4字节的存储空间。DOUBLE占用8字节，比FLOAT具有更高的精度和更大的值范围。与整数类型一样，你只能选择存储类型；MySQL会使用DOUBLE进行浮点类型的内部计算。

由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL——例如，存储财务数据。但在一些大容量的场景，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据并精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

MySQL支持多种字符串数据类型，每种类型还有许多变体。每个字符串列可以有自己的字符集和该字符集的排序规则集。

VARCHAR和CHAR类型

VARCHAR和CHAR是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是如何存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。下面的描述假设使用的存储引擎是InnoDB。如果不是InnoDB，请参考所使用的存储引擎的文档。

先来看看VARCHAR和CHAR值通常是如何存储在磁盘上的。请注意，存储引擎在内存中存储CHAR或VARCHAR值的方式可能与在磁盘上存储该值的方式不同，并且服务器在从存储引擎检索该值时可能会将其转换为另一种存储格式。下面是关于两种类型的一些比较。

VARCHAR

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

VARCHAR需要额外使用1或2字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1字节表示，否则使用2字节。假设采用latin1字符集，一个VARCHAR（10）的列需要11字节的存储空间。VARCHAR（1000）的列则需要1002个字节，因为需要2字节存储长度信息。

VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变长度的，在更新时可能会增长，这会导致额外的工作。如果行的增长使得原位置无法容纳更多内容，则处理行为取决于所使用的存储引擎。例如，InnoDB可能需要分割页面来容纳行。其他一些存储引擎也许不在原数据位置更新数据。

下面这些情况使用VARCHAR是合适的：字符串列的最大长度远大于平均长度；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

InnoDB更为复杂，它可以将过长的VARCHAR值存储为BLOB。我们稍后再讨论。

CHAR

CHAR是固定长度的：MySQL总是为定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL删除所有尾随空格。如果需要进行比较，值会用空格填充。

CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们的长度总是相同的。对于经常修改的数据，CHAR也比VARCHAR更好，因为固定长度的行不容易出现碎片。对于非常短的列，CHAR也比VARCHAR更高效；设计为只保存Y和N的值的CHAR（1）在单字节字符集（请记住，字符串长度定义的不是字节数，是字符数。多字节字符集可能需要多个字节来存储1个字符）中只使用1字节，但VARCHAR（1）需要2字节，因为还有一个记录长度的额外字节。

CHAR类型的这些行为可能有一点让人难以理解，下面通过一个具体的例子来说明。首先，我们创建一张只有一个CHAR（10）列的表并且往里面插入一些值：

CREATE TABLE char_test(char_col CHAR(10));


INSERT INTO char_test(char_col)
VALUES ( 'string1 '), (' string2 '), ( 'string3 ');

当检索这些值的时候，会发现末尾的空格被截断了：

SELECT CONCAT("'", char_col, "'") FROM char_test;

如果用VARCHAR（10）字段存储相同的值，检索时会得到以下结果，其中string3末尾的空格并没有被删除：

CREATE TABLE varchar_test(char_col VARCHAR(10));


INSERT INTO varchar_test(char_col)
VALUES ( 'string1 '), (' string2 '), ( 'string3 '); 


SELECT CONCAT("'", char_col, "'") FROM varchar_test;

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串与常规字符串非常相似，但它们存储的是字节而不是字符。填充也不同：MySQL填充BINANRY用的是\0（零字节）而不是空格，并且在检索时不会去除填充值。（如果需要在检索后保持值不变，请小心使用BINARY类型，MySQL会使用\0将其填充到需要的长度）

当需要存储二进制数据，并且希望MySQL将值作为字节而不是字符进行比较时，这些类型非常有用。字节比较的优势不仅仅是大小写不敏感。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单得多，因此速度更快。

慷慨是不明智的。使用VARCHAR（5）和VARCHAR（200）存储'hello'的空间开销是一样的。那么使用更短的列有什么优势吗？事实证明有很大的优势。较大的列会使用更多的内存，因为MySQL通常会在内部分配固定大小的内存块来保存值。这对于使用内存临时表的排序或操作来说尤其糟糕。在利用磁盘临时表进行文件排序时也同样糟糕。

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

BLOB和TEXT类型

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

实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT；二进制类型是TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

与其他数据类型不同，MySQL把每个BLOB和TEXT值当作一个具有自己标识的对象来处理。存储引擎通常会专门存储它们。当BLOB和TEXT值太大时，InnoDB会使用独立的“外部”存储区域，此时每个值在行内需要1～4字节的存储空间，然后在外部存储区域需要足够的空间来存储实际的值。

BLOB和TEXT家族之间的唯一区别是，BLOB类型存储的是二进制数据，没有排序规则或字符集，但TEXT类型有字符集和排序规则。

MySQL对BLOB和TEXT列的排序与其他类型不同：它只对这些列的最前max_sort_length字节而不是整个字符串做排序。如果只需要按前面少数几个字符排序，可以减小max_sort_length服务器变量的值。

MySQL不能将BLOB和TEXT数据类型的完整字符串放入索引，也不能使用索引进行排序。

在数据库中存储图像？在过去，某些应用程序接受上传的图像并将其作为BLOB数据存储在MySQL数据库中，这是很常见的。这种方法便于将应用程序的数据保存在一起；但是，随着数据大小的增长，修改schema等操作会由于BLOB数据的大小而变得越来越慢。

如果可以避免的话，不要在数据库中存储像图像这样的数据。相反，应该将它们写入单独的对象数据存储，并使用该表来跟踪图像的位置或文件名。

使用枚举代替字符串类型

有时可以使用ENUM（枚举）列代替常规的字符串类型。ENUM列可以存储一组预定义的不同字符串值。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到1或者2字节中。在内部会将每个值在列表中的位置保存为整数。这里有一个例子：

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


INSERT INTO enum_test(e)
VALUES('fish'),('dog'),('apple');

上面三行实际上存储的是整数，而不是字符串。可以通过在数值上下文中检索看到这个双重属性：

SELECT e + 0 FROM enum_test;

如果使用数字作为ENUM常量，这种双重属性很容易导致混乱，例如，ENUM（'1'，'2'，'3'）。尽量避免这么做。

另一个令人惊讶的事情是，ENUM字段是根据内部整数值排序的，而不是根据字符串本身：

SELECT e  FROM enum_test ORDER BY e;

可以通过按照需要的顺序指定ENUM成员来解决这个问题。也可以在查询中使用FIELD() 函数显式地指定排序顺序，但这会导致MySQL无法利用索引消除排序：

SELECT e  FROM enum_test ORDER BY  FIELD(e, 'apple', 'dog', 'fish');

SELECT e  FROM enum_test ORDER BY  FIELD(e, 'fish', 'dog', 'apple');

如果是按字母顺序定义的值，就没有必要这么做了。

MySQL将每个枚举值存储为整数，并且必须进行查找以将其转换为字符串表示，因此ENUM列有一些开销。这些开销通常可以被ENUM列的小尺寸所抵消，但并不总是如此。

特别是，将CHAR/VARCHAR列联接到ENUM列可能比联接到另一个CHAR/VARCHAR列更慢。

为了说明这一点，我们对一个应用程序中MySQL执行表联接的速度进行了基准测试。该表有一个相当大的主键：

CREATE TABLE webservicecalls (
  days DATE NOT NULL,
  account SMALLINT NOT NULL,
  service VARCHAR(10) NOT NULL,
  methods VARCHAR( 50)NOT NULL,
  calls INT NOT NULL,
  items INT NOT NULL,
  tine FLOAT NOT NULL,
  cost DECIMAL(9,5)NOT NULL,
  updated DATETIME,
  PRIMARY KEY (days, account, service, methods)
)ENGINE=INNODB;

这个表大约有11万行，只有10MB大小，所以可以完全载入内存。service列包含5个不同的值，平均长度为4个字符，method列包含71个值，平均长度为2 0个字符。

我们复制一下这个表，并将service列和methods列转换为ENUM类型，如下所示：

CREATE TABLE webservicecalls_enum (
  days DATE NOT NULL,
  account SMALLINT NOT NULL,
  service ENUM('list', 'page', 'statistics') NOT NULL,
  methods ENUM('get', 'post', 'put', 'delete') NOT NULL,
  calls INT NOT NULL,
  items INT NOT NULL,
  tine FLOAT NOT NULL,
  cost DECIMAL(9,5)NOT NULL,
  updated DATETIME,
  PRIMARY KEY (days, account, service, methods)
)ENGINE=INNODB;

然后我们测试用主键列进行联接操作的性能，下面是所使用的查询语句：

SELECT SQL_NO_CACHE COUNT(*)
FROM webservicecalls
JOIN webservicecalls 
USING(days, account, service, nethod);

我们用VARCHAR和ENUM列以不同的组合进行联接，结果如表6-1所示。（这里显示的速度是相对的，因为CPU、内存和其他硬件的速度会随着时间的变化而变化）

表6-1：联接VARCHAR和ENUM列的速度

测试	QPS
VARCHAR 联接VARCHAR	2.6
VARCHAR 联接ENUM	1.7
ENUM 联接VARCHAR	1.8
ENUM联接ENUM	3.5

从上面的结果可以看到，当把列都转换成ENUM以后，联接变得很快。但是当VARCHAR列和ENUM列进行联接时则慢很多。在本例中，只要不是必须让ENUM和VARCHAR列进行联接，那么将这些列转换为ENUM就是一个好主意。通常的设计实践是使用带有整数主键的“查找表”，以避免在联接中使用字符串。

然而，将列转换为ENUM类型还有另一个好处：根据SHOW TABLE STATUS输出结果中的Data_length列，发现将这两列转换为ENUM会使表变小约三分之一。在某些情况下，即使必须将ENUM列联接到VARCHAR列，这也可能是有益的。而且，转换后主键也只有原来的一半大小了，因为这是InnoDB表，如果表中有其他索引，减少主键大小也会使这些非主键索引小得多。

虽然ENUM类型在存储值的方式上非常有效，但更改ENUM中的有效值会导致需要做schema变更。如果你没有一个健壮的系统来支持自动schema变更（本章后面会进行描述），那么如果ENUM经常更改，这种操作需求可能会带来很大的不便。在后面的schema设计中，我们还会提到“枚举值过多”的反例。

日期和时间类型

MySQL中有很多数据类型用以支持各种各样的日期和时间值，比如YEAR和DATE。

MySQL可以存储的最小时间粒度是微秒。大多数时间类型都没有其他选择，因此不存在哪一种是最佳选择的问题。唯一的问题是，当需要同时存储日期和时间时该怎么做。

MySQL提供了两种非常相似的数据类型来实现这一需求：DATETIME和TIMESTAMP。对于许多应用程序来说，两者都可以，但在某些场景，一个比另一个更好。我们来看一下。

DATETIME

这种类型可以保存大范围的数值，从1000年到9999年，精度为1微秒。它以YYYYMMDDHHMMSS格式存储压缩成整数的日期和时间，且与时区无关。这需要8字节的存储空间。

默认情况下，MySQL以可排序、无歧义的格式显示DATETIME值，例如，2008-01-16 22：37：08。这是ANSI表示日期和时间的标准方式。

TIMESTAMP

顾名思义，TIMESTAMP类型存储自1970年1月1日格林尼治标准时间（GMT）午夜以来经过的秒数——与UNIX时间戳相同。TIMESTAMP只使用4字节的存储空间，所以它的范围比DATETIME小得多：只能表示从1970年到2038年1月19日。MySQL提供FROM_UNIXTIME()函数来将UNIX时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为UNIX时间戳。

时间戳显示的值依赖于时区。MySQL服务器、操作系统和客户端连接都有时区设置。因此，存储值0的TIMESTAMP在美国东部标准时间（EST）中显示为1969-12-31 19：00：00，与格林尼治标准时间（GMT）差5小时。有必要强调一下这个区别：如果存储或访问多个时区的数据，TIMESTAMP和DATETIME的行为将很不一样。前者保留与所使用时区相关的值，而后者保留日期和时间的文本表示。

TIMESTAMP还有DATETIME没有的特殊属性。默认情况下，当插入一行记录时没有指定第一个TIMESTAMP列的值，MySQL会将该列的值设置为当前时间。（TIMESTAMP的行为规则很复杂，并且在不同的MySQL版本中会发生变化，因此你应该验证数据库的行为是否符合需要。在对TIMESTAMP列进行更改后，通常最好检查SHOW CREATE TABLE命令的输出）

当更新一行记录时没有指定第一个TIMESTAMP列的值，MySQL默认也会将该列的值更新为当前时间。可以为任何TIMESTAMP列配置插入和更新行为。最后，TIMESTAMP列在默认情况下为NOT NULL，这也和其他的数据类型不一样。

将日期和时间存储为整数？

DATETIME和TIMESTAMP都迫使你处理服务器和客户端上的时区，虽然TIMESTAMP比 DATETIME更节省空间（4字节与8字节的区别，忽略分数秒支持），但它也会遇到2038年的问题。

最终，存储日期和时间归结为以下几件事：

● 需要支持前后多大范围的日期和时间？

● 存储空间对这些数据有多重要？

● 需要支持分数秒吗？

● 在MySQL中处理日期、时间和时区，还是在代码中处理？

通过将日期和时间存储为UNIX纪元（即自1970年1月1日以来的秒数），以协调世界时（UTC）的形式，可避免MySQL处理的复杂性，这一做法越来越流行。使用带符号的32位INT，可以表达直到2038年的时间。使用无符号的32位INT，可以表达直到2106年的时间。如果使用64位，还可以超出这些范围。

就像关于操作系统、编辑器和标签与空间的流行讨论一样，如何存储这组特定的数据可能更多的是一种观点，而不是最佳实践。需要考虑的是，这对于你的用例来说是否可行。

位压缩数据类型

MySQL有几种使用值中的单个位来紧凑地存储数据的类型。所有这些位压缩类型，不管底层存储和处理方式如何，从技术上来说都是字符串类型。

BIT

可以使用BIT列存储一个或多个true/false值。BIT(1) 定义一个包含1位的字段，BIT(2) 存储2位的字段，依此类推；BIT列的最大长度为64位。InnoDB将每一列存储为足够容纳这些位的最小整数类型，所以使用BIT列不会节省任何存储空间。

MySQL在处理时会将BIT视为字符串类型，而不是数字类型。当检索BIT(1) 的值时，结果是一个包含二进制值0或1的字符串，而不是ASCII码的“0”或“1”。但是，如果在数字上下文中检索该值，则会将BIT字符串转换为数字。如果需要将结果与另外的值进行比较，一定要记得这一点。

例如，如果将值b'00111001'（二进制数相当于57）存储到BIT(8) 列中并检索它，则将得到包含字符码为57的字符串。这恰好是“9”的ASCII字符代码。但在数字上下文场景中，得到的将会是数字57：

CREATE TABLE bittest(a BIT(8));
INSERT INTO bittest VALUES(b'00111001');
SELECT a, a + 0 FROM bittest;

这可能会让人非常困惑，因此我们建议谨慎使用BIT类型。对于大多数应用来说，最好避免使用这种类型。

如果想在1位的存储空间中存储true/false值，另一个方法是创建一个可为空的CHAR（0）列。该列可以存储空值（NULL）或长度为零的值（空字符串）。这在实践中是可行的，但可能对使用数据库中该数据的其他人来说是难以理解的，并且使编写查询变得困难。除非你非常注重节省空间，否则我们仍然建议使用TINYINT。

SET

如果需要存储多个true/false值，可以考虑使用MySQL原生的SET数据类型，可以将多列组合成一列，这在MySQL内部是以一组打包的位的集合来表示的。这样可以更有效地利用存储空间，MySQL具有FIND_IN_SET()和FIELD()等函数，使其易于在查询中使用。

整数列上的位操作

SET的另一种替代方法是使用整数作为二进制位的打包集合。例如，可以在TINYINT中打包8位，并使用逐位操作符对它们进行操作。可以在应用程序代码中为每个位定义命名常量来简化这一过程。

与SET相比，这种方法的主要优点是可以在不使用ALTER TABLE的情况下更改字段表示的“枚举”。缺点是查询更难编写和理解（当设置第5位时是什么意思）。有些人喜欢位操作，有些人则不喜欢，所以是否想尝试这种技术很大程度上取决于个人的偏好。

一个封装位的应用示例是保存权限的访问控制列表（ACL）。每个位或SET元素代表一个值，例如CAN_READ、CAN_WRITE或CAN_DELETE。如果使用SET列，可以让MySQL在列定义中存储位到值的映射；如果使用整数列，则可以在应用程序代码中存储这个映射。

下面是使用SET列的查询：

CREATE TABLE acl(
perms SET( 'CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL);


INSERT INTO acl(perms)
VALUES ('CAN_READ,CAN_DELETE');


SELECT perms FROM acl 
WHERE FIND_IN_SET('CAN_READ', perms);

如果使用整数列，则可以参考下面的例子：

SET @CAN_READ :=1 << 0, @CAN_WRITE := 1 << 1, @CAN_DELETE := 1 << 2;


CREATE TABLE acl2 (perms TINYINT UNSIGNED NOT NULL DEFAULT 0);


INSERT INTO acl2(perms)VALUES(@CAN_READ + @CAN_DELETE);


SELECT perms FROM acl2 WHERE perms & @CAN_READ;

这里我们使用了MySQL变量来定义值，也可以在代码中使用常量来代替。

JSON数据类型

使用JSON作为系统之间交换数据的格式正变得越来越普遍。MySQL有原生的JSON数据类型，可以方便地直接在表中的JSON结构部分进行操作。纯粹主义者可能会认为，在数据库中存储原始JSON是一种反范式，因为理想情况下，schema应该是JSON中具体字段的表示。新手在查看JSON数据类型时，可能会发现这是避免创建和管理独立字段的捷径。

哪种方法更好在很大程度上是主观的，但我们将客观地展示一个使用示例，并比较查询速度和数据大小。

我们的样本数据是由NASA提供的202颗近地小行星和彗星的发现清单（参见链接24 https://oreil.ly/2oZX9）。测试的版本是MySQL 8.0.22，运行在一个4核、16GB内存的虚拟机上。数据示例如下：

[
	{
		"designation": "419880(2011 AH37)",
		"discovery_ date": "2011-01-07T00:00:00.000",
		"h_mag": "19.7",
		"moid_au": "0.035",
		"q_au_1": "0.84",
		"q_au_2": "4.26",
		"period_yr": "4.06",
		"i_deg": "9.65",
		"pha": "Y",
		"orbit_class": "Apollo"
	}
]

这份数据是关于名称、发现日期及收集的有关实体的数据，包括数字和文本字段。

首先，我们以JSON格式来获取数据集，并将其转换为每个条目一行。这是一个看起来相

对简单的schema：

DESC asteroids_json;

其次，我们将该JSON用合适的数据类型将字段转换为列。可以得到以下schema：

DESC asteroids_sql;

下面来比较数据大小：

SHOW TABLE STATUS;

可以看到，这个例子中的SQL版本使用了3个16KB的页来存储，JSON版本则使用了5个16KB的页。这并不令人惊讶。JSON数据类型将使用更多空间来存储用于定义JSON的额外字符（大括号、方括号、冒号等）以及空格。在这个小例子中，可以通过将JSON转换为特定的数据类型来优化数据存储的大小。

在一些有效的用例中，数据大小可能不是那么重要。接下来看看如何衡量两者之间的查询延迟？

要使用SQL语句选择单列的所有内容，语法很简单：

SELECT designation FROM asteroids_sql;

在第一次运行这个查询时，InnoDB的缓冲池中没有缓存，我们得到了1.14毫秒的结果。第二次执行时，已经在内存中进行了缓存，则只要0.44毫秒。

对于JSON，可以用如下方式访问JSON结构中的字段：

SELECT json_data->'$.designation' FROM asteroids_json;

类似地，第一次未缓存时需要1.13毫秒，缓存后的执行时间约为0.80毫秒。在这个执行速度下，我们预计会有一个合理的变化——我们讨论的是VM环境中数百微秒的差异。在我们看来，这两个查询执行得相当快，值得注意的是，JSON版本的查询仍然是SQL版本的两倍长。

即便如此，访问特定行将会怎么样呢？对于单行查找，可以利用索引的优势：

ALTER TABLE asteroids_sql ADD INDEX (designation);

当进行单行查找时，SQL版本的运行时间为0.33毫秒，JSON版本的运行时间为0.58毫秒，SQL版本具有优势。这很容易解释：使用索引使得InnoDB只返回1行，而不是202行。不过，将索引查询与全表扫描进行比较是不公平的。为了公平竞争，我们可以使用虚拟列的特性来提取designation值，然后针对虚拟列创建索引：

ALTER TABLE asteroids_json 
ADD COLUNN designation VARCHAR(30) GENERATED 
ALMAYS AS(json_data->"$.designation")，
ADD INDEX (designation);

这给了我们一个JSON表的schema，看起来像下面这样：

DESC asteroids_json;

这个schema从json_data列生成一个虚拟列并创建了索引。现在，我们重新运行单行查找，以使用索引列而不是JSON列路径操作符（->）。由于字段数据在JSON中被引用，所以我们还需要搜索SQL中引用的数据：

SELECT * FROM asteroids_json WHERE designation='"(2010 GW62)"';

这个查询在0.4毫秒内执行完成，非常接近SQL版本的0.33毫秒。

从前面的简单测试用例来看，我们使用的表空间总量似乎是使用SQL列而不是存储原始JSON文档的主要驱动因素。使用SQL列的速度仍然更好。总的来说，决定使用原生SQL还是JSON取决于在数据库中存储JSON的便捷性是否大于性能。如果每天访问这些数据数百万次或数十亿次，速度差异就会累加起来。

选择标识符

一般来说，标识符是引用行及通常使其唯一的方式。例如，如果你有一个关于用户的表，可能希望为每个用户分配一个数字ID或唯一的用户名。此字段可能是主键中的部分或全部。

为标识符列选择合适的数据类型非常重要。与其他列相比，更有可能将标识符列与其他值（例如，在联接中）进行比较，并使用它们进行查找。标识符列也可能在其他表中作为外键，因此为标识符列选择数据类型时，应该与联接表中的对应列保持一致。（正如我们在本章前面演示的，在关联表中使用相同的数据类型是一个好主意，因为这些列很可能在联接中使用。）

在为标识符列选择类型时，不仅需要考虑存储类型，还需要考虑MySQL如何对该类型执行计算和比较。例如，MySQL在内部将ENUM和SET类型存储为整数，但在字符串上下文中进行比较时，会将它们转换为字符串。

选择类型后，要确保在所有相关表中使用相同的类型。类型应该完全匹配，包括UNSIGNED等属性。（如果使用InnoDB存储引擎，除非数据类型完全匹配，否则可能无法创建外键，对应的错误消息是“ERROR 1005（HY000）：Can't create table”。这个信息可能让人困惑，具体取决于上下文，MySQL邮件列表中经常会出现相关问题。（奇怪的是，可以在不同长度的VARCHAR列之间创建外键。））混合不同的数据类型可能导致性能问题，即使没有性能影响，在进行比较操作时，隐式类型转换也可能会产生难以发现的错误。甚至在很久以后，当你忘记正在比较不同类型的数据时，这些问题可能会突然出现。

在可以满足值的范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。例如，如果有一个state_id列存储美国各州的名字，则不需要数千或数百万个值，因此不要使用INT，TINYINT就足够了，它比INT少3字节。如果在其他表中使用此值作为外键，3字节可能会产生很大的性能差异。这里有一些小建议。

整数类型

整数通常是标识符的最佳选择，因为它们速度快，并且可以自动递增。AUTO_INCREMENT是一个列属性，可以为新的行自动生成一个整数类型的值。例如，计费系统可能需要为每个客户生成新发票，使用AUTO_INCREMENT意味着生成的第一张发票是1，第二张是2，依此类推。请注意，应该确保选择适合预期数据增长的整数大小，与整数意外耗尽有关的系统停机事故可不止发生一次。

ENUM和SET类型

对于标识符来说，ENUM和SET类型通常是糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。ENUM和SET列适用于保存订单状态或产品类型等信息。

举个例子，如果使用ENUM字段来定义产品类型，可能会设计一张以这个ENUM字段为主键的查找表。（可以在查找表中添加描述性文本的列，以生成术语表，或者在网站上的下拉菜单中提供有意义的标签。）在这种情况下，使用ENUM类型作为标识符是可行的，但是大部分情况下都要避免这么做。

字符串类型

如果可能，应避免使用字符串类型作为标识符的数据类型，因为它们很消耗空间，而且通常比整数类型慢。

对于完全“随机”的字符串要非常小心，如MD5()、SHA1()或UUID()生成的字符串。这些函数生成的新值会任意分布在很大的空间内，这会减慢INSERT和某些类型的SELECT查询的速度： (另一方面，对于一些有很多写入的非常大的表，这种伪随机值实际上可以帮助消除“热点”)

● 因为插入的值会写到索引的随机位置，所以会使得INSERT查询变慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。

● SELECT查询也会变慢，因为逻辑上相邻的行会广泛分布在磁盘和内存中。

● 对于所有类型的查询，随机值都会导致缓存的性能低下，因为它们会破坏引用的局部性，而这正是缓存的工作原理。如果整个数据集都是“热的”，那么将任何特定部分的数据缓存到内存中都没有任何好处，而且如果工作集比内存大，缓存就会出现大量刷新和不命中。

如果存储通用唯一标识符（UUID）值，则应该删除破折号，或者更好的做法是，使用UNHEX()函数将UUID值转换为16字节的数字，并将其存储在一个BINARY（16）列中。可以使用HEX()函数以十六进制格式检索值。

当心自动生成的schema

我们已经讨论了最重要的有关数据类型的注意事项（一些涉及严重的性能问题，另一些则影响较小），但我们还没有提到自动生成schema的坏处。

写得不好的schema迁移程序和自动生成schema的程序可能会导致严重的性能问题。有些程序存储任何数据都会使用很大的VARCHAR字段，或者对将在联接中进行比较的列使用不同的数据类型。如果schema是自动生成的，一定要反复检查确认没有问题。

对象关系映射（ORM）系统（以及使用它们的“框架”）通常是另一个性能“噩梦”。其中一些ORM系统会将任意类型的数据存储到任意类型的后端数据存储中，这通常意味着其没有设计使用更优的数据存储。有时ORM系统会为每个对象的每个属性使用单独的行来存储，甚至使用基于时间戳的版本控制，导致每个属性有多个版本存在！

这种设计对开发者很有吸引力，因为这使得他们可以用面向对象的方式工作，而不需要考虑数据是如何存储的。然而，“对开发人员隐藏复杂性”的应用程序通常不能很好地扩展。我们建议在用性能交换开发人员的效率之前仔细考虑，并始终在实际的大型数据集上进行测试，这样就不会太晚才发现性能问题。

特殊数据类型

某些类型的数据并不直接对应于可用的内置类型。IPv4地址就是一个很好的例子。人们通常使用VARCHAR（15）列来存储IP地址。然而，它们实际上是32位无符号整数，而不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易，所以应该将IP地址存储为无符号整数。MySQL提供了INET_ATON()和INET_NTOA()函数来在这两种表示形式之间进行转换。使用的空间从VARCHAR（15）的约16字节缩减到无符号32位整数的4字节。如果你担心数据库的可读性，不想继续使用函数查看行数据，请记住MySQL有视图，可以使用视图来简化数据查看的复杂性。

MySQL schema设计中的陷阱

尽管设计原则有好有坏，但MySQL的实现方式会带来一些问题，这意味着你也可能会犯MySQL特有的错误。本节讨论我们在MySQL schema设计中观察到的问题。它可能会帮助你避免这些错误，并让你选择更适合MySQL具体实现的替代方案。

太多的列

MySQL的存储引擎API通过在服务器和存储引擎之间以行缓冲区格式复制行来工作；然后，服务器将缓冲区解码为列。将行缓冲区转换为具有解码列的行数据结构的操作代价是非常高的。InnoDB的行格式总是需要转换的。这种转换的成本取决于列数。当调查一个具有非常宽的表（数百列）的客户的高CPU消耗问题时，我们发现这种转换代价可能会变得非常昂贵，尽管实际上只使用了几列。如果计划使用数百列，请注意服务器的性能特征会有所不同。

太多的联接

所谓的实体属性值（entity attribute value，EAV）设计模式是一种被普遍认为糟糕的设计模式的典型案例，尤其是在MySQL中效果不佳。MySQL限制每个联接有61个表，而EAV模式设计的数据库需要许多自联接。我们已经看到不少E AV模式设计的数据库最终超过了这个限制。然而，即使联接数远小于61，规划和优化查询的成本对MySQL来说也会成为问题。一个粗略的经验法则是，如果需要以高并发性快速执行查询，那么每个查询最好少于十几个的表。

全能的枚举

要小心过度使用ENUM。下面是我们看到的一个例子：

CREATE TABLE ... (
country enum('0', '1','2',.. . ,'31')

schema中大量地散布着这种模式。在任何具有枚举值类型的数据库中，这可能是一个值得商榷的设计决策，因为它实际上应该是一个整数，会被设计为“字典”或“查找”表的外健。

变相的枚举

ENUM列允许在列中保存一组已定义值中的单个值。SET列则允许在列中保存一组已定义值中的一个或多个值。有时很容易混淆。这里有一个例子：

CREATE TABLE ...(
is_default set('Y', 'N') NOT NULL default 'N';

如果这里真和假两种情况不会同时出现，那么毫无疑问应该使用ENUM列而不是SET列。

NULL不是虚拟值

我们之前说过避免使用NULL的好处，并且建议尽可能考虑其他选择。即使需要在表中存储事实上的“空值”，也可能不需要使用NULL。也许可以使用0、特殊值或空字符串作为代替。

但是遵循这个原则也不要走极端。当需要表示未知值时，不要太害怕使用NULL。在某些情况下，使用NULL比使用某个虚拟常数更好。从受约束类型的域中选择一个值，例如使用-1表示一个未知的整数，可能会使代码复杂化，容易引入bug，并通常会把事情搞得一团糟。处理NULL并不容易，但通常比其他替代方案更好。

下面是我们经常看到的一个例子：

CREATE TABLE ...(
dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'

这个虚假的全0值可能会导致很多问题（可以将MySQL的SQL_MODE配置为不允许使用无意义的日期，对于尚未创建满是坏数据的数据库的新应用程序来说，这是一个特别好的实践）。

还有一个相关的细节，MySQL会对NULL值进行索引，而Oracle则不会。

到这里我们已经讨论了很多关于数据类型、如何选择数据类型以及不做什么的实用建议，接下来让我们继续讨论另一个好的、迭代式的设计：schema管理。

上一篇：《第4章-5 linux 网络管理》

下一篇：《第6章-2 schema管理》