一、选择优化的数据类型

MySQL支持多种数据类型，所以选择合适的数据类型非常重要。以下简单的原则将帮助你做出更好的选择。

通常越小越好。一般来说，应该尽可能使用能够正确存储数据的最小数据类型。较小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且需要更少的CPU周期来处理。但是请确保不要低估需要存储的值的范围，因为在模式的许多地方增加数据类型的范围是非常耗时和痛苦的操作。如果您不确定哪种数据类型最好，请选择您认为不会超出范围的最小类型。简单点。简单数据类型的操作通常需要更少的CPU周期。例如，整数比字符操作便宜，因为字符集和排序规则(定序)使字符比较比整数比较更复杂。这里有两个例子：一个是MySQL内置类型(2)应该用来存储日期和时间而不是字符串，另一个是整数应该用来存储IP地址。我们将在后面具体讨论这个话题。尽量避免空值。通常，最好指定该列不为空，除非您确实需要存储空值。如果查询包含可空列，MySQL优化起来就比较困难，因为可空列会使索引、索引统计和值比较变得更加复杂。可空列使用更多的存储空间，在MySQL中需要特殊处理。如果计划为列编制索引，则应尽量避免设计可为空的列。1.1整数类型

数字有两种：整数和实数。如果存储整数，可以使用这些整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别使用8、16、24、32和64位内存空间。

整数类型有一个可选的UNSIGNED属性，这意味着负值是不允许的，这可能是正数上限的两倍。比如TINYINT UNSIGNED可以存储在0 ~ 255的范围内，TINYINT可以存储在128 ~ 127的范围内。

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

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

1.2实数类型

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

FLOAT和DOUBLE类型支持使用标准浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。

CPU不支持DECIMAL的直接计算。在5.0及以后的版本中，MySQL服务器本身实现了DECIMAL的高精度计算。相对来说，CPU直接支持原生浮点计算，所以浮点计算明显更快。

定义列时，建议只指定数据类型，而不指定精度。

因为需要额外的空间和计算开销，所以DECIMAL——应该仅用于精确计算小数，例如存储财务数据。但是当数据量比较大的时候，可以考虑用BIGINT代替DECIMAL，根据小数位数把要存储的货币单位乘以相应的倍数。

1.3字符串类型

可变长字符串

VARCHAR类型用于存储可变长度字符串，这是最常见的字符串数据类型。它比固定长度类型节省更多的空间，因为它只使用必要的空间。

VARCHAR需要使用1或2个额外的字节来记录字符串的长度：如果列的最大长度小于或等于255个字节，将只使用1个字节；否则，将使用2个字节。

VARCHAR节省了存储空间，因此对性能也有帮助。但是，由于该行较长，更新时可能会变得比以前更长，从而导致额外的工作。

VARCHAR适用于以下情况：

字符串的最大长度远大于平均长度；列更新很少，所以碎片化不是问题；使用像UTF-8这样的复杂字符集，每个字符都存储在不同的字节数中。最好的策略是只分配真正需要的空间，不要太大方，因为较长的列会消耗更多的内存。

茶

CHAR类型是固定长度的：MySQL总是根据定义的字符串长度分配足够的空间。存储CHAR值时，MySQL会删除所有尾随空格。

CHAR适合存储短字符串，或者所有的值都接近相同的长度。例如，CHAR非常适合存储密码的MD5值，因为它是一个固定长度的值。

对于频繁变化的数据，CHAR也比VARCHAR好，因为固定长度的CHAR类型不容易分片。对于非常短的列，CHAR在存储空间上比VARCHAR更有效，因为VARCHAR需要一个额外字节的记录长度。

以及BLOB和TEXT类型。

BLOB和TEXT都是字符串数据类型，旨在存储大量数据，分别以二进制和字符模式存储。

实际上，它们属于两个不同的数据类型族：TINYTEXT和SMALLT。

EXT，TEXT，MEDIUMTEXT，LONGTEXT；对应的二进制类型是TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB 。 BLOB 是 SMALLBLOB的同义词，TEXT 是 SMALLTEXT的同义词。

MySQL对BLOB 和 TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length 字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用ORDER BY SUSTRING（column，length ） 。

使用枚举（ENUM）代替字符串类型

有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。除非能接受只在列表末尾添加元素，否则使用枚举不是个好主意。

1.4 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR 和 DATE。MySQL能存储的最小时间粒度为秒（MariaDB支持微秒级别的时间类型）。但是MySQL也可以使用微秒级的粒度进行临时运算，我们会展示怎么绕开这种存储限制。

MySQL 提供两种相似的日期类型，DATETIME 和 TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些场景，一个比另一个工作得好。

DATETIME

这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。 默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值，例如“2008-01-16 22:37:08”。这是ANSI标准定义的日期和时间表示方法。

TIMESTAMP

就像它的名字一样，TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多：只能表示从1970年到2038年。

TIMESTAMP显示的值也依赖于时区。MySQL服务器、操作系统，以及客户端连接都有时区设置。

有必要强调一下这个区别：如果在多个时区存储或访问数据，TIMESTAMP 和 DATETIME的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。

TIMESTAMP 的特殊属性：

在插入数据时如果没有指定值，会自动填充为当前时间。

TIMESTAMP 默认为 NOT NULL。

通常应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

如果需要存储比秒更小粒度的日期和时间值，可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分。这两种方式都可以，或者也可以使用MariaDB替代MySQL。

1.5 选择标识符（identifier）

为标识列（identifier column）选择合适的数据类型非常重要。

标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。混用不同类型可能导致性能问题，即使没有性能影响，在比较操作时隐式的类型转换也可能导致很难发现的错误。

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

整数类型整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT 。ENUM 和 SET 类型对于标识列来说 ENUM 和 SET 类型通常是比较糟糕的选择，应尽量避免用这种类型。字符串类型字符串类型很消耗空间，且通常比数字类型慢，所以也应避免使用字符串作为标识列。对于完全“随机”的字符串也需要多加注意，例如MD5()、SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢。如果存储UUID值，则应该移除“-”符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY（16）列中。检索时可以通过HEX()函数来格式化为十六进制格式。

1.6 特殊数据类型

某些类型的数据并不直接与内置类型一致。这里有两个例子：

低于秒级精度的时间戳前面也介绍了，建议使用 BIGINT 类型存储时间戳。IPv4 地址人们经常使用VARCHAR（15）列来存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON() 和 INET_NTOA()函数在这两种表示方法之间转换。

二、MySQL schema设计中的陷阱

虽然有一些普遍的好或坏的设计原则，但也有一些问题是由MySQL的实现机制导致的，这意味着有可能犯一些只在MySQL下发生的特定错误。本节我们讨论设计MySQL的schema的问题。这也许会帮助你避免这些错误，并且选择在MySQL特定实现下工作得更好的替代方案。

太多的列

MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。然而，MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。当我们研究一个CPU占用非常高的案例时，发现客户使用了非常宽的表（数千个字段），然而只有一小部分列会实际用到，这时转换的代价就非常高。如果计划使用数千个字段，必须意识到服务器的性能运行特征会有一些不同。

太多的关联

所谓的“实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。事实上在许多关联少于61张表的情况下，解析和优化查询的代价也会成为MySQL的问题。一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

全能的枚举

注意防止过度使用枚举（ENUM）。下面是我们见过的一个例子：

CREATE TABLE ... ( country enum('','0','1','2',...,'31') 复制代码

这种模式的schema设计非常凌乱。这么使用枚举值类型也许在任何支持枚举类型的数据库都是一个有问题的设计方案，这里应该用整数作为外键关联到字典表或者查找表来查找具体值。但是在MySQL中，当需要在枚举列表中增加一个新的国家时就要做一次ALTER TABLE操作。在MySQL 5.0以及更早的版本中ALTER TABLE是一种阻塞操作；即使在5.1和更新版本中，如果不是在列表的末尾增加值也会一样需要ALTER TABLE。

变相的枚举

枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。这是一个例子：

CREATE TABLE ... ( is_default set ('Y','N') NOT NULL default 'N' 复制代码

如果这里真和假两种情况不会同时出现，那么毫无疑问应该使用枚举列代替集合列。

非此发明（Not Invent Here）的NULL

我们之前写了避免使用NULL的好处，并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时，也不一定非得使用NULL。也许可以使用0、某个特殊值，或者空字符串作为代替。

但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。在一些场景中，使用NULL可能会比某个神奇常数更好。从特定类型的值域中选择一个不可能的值，例如用−1代表一个未知的整数，可能导致代码复杂很多，并容易引入bug，还可能会让事情变得一团糟。处理NULL确实不容易，但有时候会比它的替代方案更好。

三、范式和反范式

对于任何给定的数据通常都有很多种表示方法，从完全的范式化到完全的反范式化，以及两者的折中。在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

3.1 范式的优点和缺点

当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处：

范式化的更新操作通常比反范式化要快。当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

3.2 反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。 如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机 I/O 。

单独的表也能使用更有效的索引策略。

3.3 混用范式化和反范式化

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？

事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。

好啦，本章的内容就到这里啦，我们下期见~

作者：solocoder 链接：https://juejin.im/post/5c488039f265da61553b23c5