高性能MySQL (一)：Schema与数据类型优化

阳光穿透心脏的1/2处 2022-03-16 08:04 250阅读 0赞

本文摘录总结自《高性能MySQL》(第三版)，将以每章一篇文章的方式带大家读这本数据库经典之作。总结精华，帮大家快速抓住重点信息，节省宝贵时间。

这章概念性东西比较多，可能有点枯燥。但讲了很多底层原理，坚持读下来还是会有一些收获的。

> `100` 多位经验丰富的开发者参与，在 Github 上获得了近 `1000` 个 `star` 的全栈全平台开源项目想了解下吗？  
> 项目地址：[github.com/cachecats/c…][github.com_cachecats_c]

## 一、选择优化的数据类型 ##

MySQL 支持的数据类型非常多，选择正确的数据类型至关重要。下面的几个简单原则有助于做出更好的选择。

*  更小的通常更好
    
    一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
    
    但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。
 *  简单就好
    
    简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。这里有两个例子：一个是应该使用MySQL内建的类型(2)而不是字符串来存储日期和时间，另外一个是应该用整型存储IP地址。稍后我们将专门讨论这个话题。
 *  尽量避免NULL
    
    通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。
    
    如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。

### 1.1 整数类型 ###

有两种类型的数字：整数（whole number）和实数（real number）。如果存储整数，可以使用这几种整数类型：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类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。

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

VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。

下面这些情况下使用VARCHAR是合适的：

*  字符串列的最大长度比平均长度大很多；
 *  列的更新很少，所以碎片不是问题；
 *  使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

最好的策略是只分配真正需要的空间，不要太慷慨，因为更长的列会消耗更多的内存。

#### CHAR ####

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

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

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

#### BLOB 和 TEXT 类型 ####

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

实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT，SMALLTEXT，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 特殊数据类型 ###

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

1.  低于秒级精度的时间戳
    
    前面也介绍了，建议使用 BIGINT 类型存储时间戳。
2.  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和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。

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

--------------------

## 全栈全平台开源项目 CodeRiver ##

CodeRiver 是一个免费的项目协作平台，愿景是打通 IT 产业上下游，无论你是产品经理、设计师、程序员或是测试，还是其他行业人员，只要有好的创意、想法，都可以来 CodeRiver 免费发布项目，召集志同道合的队友一起将梦想变为现实！

CodeRiver 本身还是一个大型开源项目，致力于打造全栈全平台企业级精品开源项目。涵盖了 React、Vue、Angular、小程序、ReactNative、Android、Flutter、Java、Node 等几乎所有主流技术栈，主打代码质量。

目前已经有近 `100` 名优秀开发者参与，github 上的 `star` 数量将近 `1000` 个。每个技术栈都有多位经验丰富的大佬坐镇，更有两位架构师指导项目架构。无论你想学什么语言处于什么技术水平，相信都能在这里学有所获。

通过 `高质量源码 + 博客 + 视频`，帮助每一位开发者快速成长。

项目地址：[github.com/cachecats/c…][github.com_cachecats_c 1]

--------------------

您的鼓励是我们前行最大的动力，欢迎点赞，欢迎送小星星✨ ~

[github.com_cachecats_c]: https://link.juejin.im/?target=https%3A%2F%2Fgithub.com%2Fcachecats%2Fcoderiver
[github.com_cachecats_c 1]: https://link.juejin.im?target=https%3A%2F%2Fgithub.com%2Fcachecats%2Fcoderiver