Schema与数据类型优化

比眉伴天荒 2023-01-04 10:19 141阅读 0赞

> 合抱之木，生于毫末；九层之台，起于垒土…

#### 前言： ####

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计 schema，这往往需要权衡各种因素。

例如：反范式的设计可以加快某些类型的查询，但同时可能使另一些类型的查询变慢。  
比如添加计数表和汇总表是一种很好的优化查询的方式，但这些表的维护成本会很高…

本文旨在讨论一些通常不坏的设计原则和思路

#### 正文 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTgxNTAwMQ_size_16_color_FFFFFF_t_70]

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

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

> 更小的通常更好

一般情况下，应该尽量使用可以正确存储数据的最小数据类型【例如：只需要存 0~200，tinyint unsigned 更好】

因为更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。

注：尽量确保没有低估需要存储的值的范围，因为在 schema 中的多个地方增加数据类型的范围是一个非常耗时和痛苦的事情

> 简单就好

简单数据类型的操作通常需要更少的 CPU 周期。

例如：整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂

两个例子：一个是应该使用 MySQL 内建的类型，而不是字符串来存储日期和时间，另外一个是应该用整型存储 IP 地址。

> 尽量避免 NULL

通常情况下最好指定列为 NOT NULL，除非真的需要存储 NULL 值

如果查询中包含可为 NULL 的列，对 MySQL来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。

可为 NULL 的列会使用更多的存储空间，在 MySQL 里页需要特殊处理。

当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在 MyISAM 里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引

注：通常把可为 NULL 的列改成 NOT NULL 带来的性能提升比较小，所以（调优时）没有必要首先考虑这么做，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。可以使用 0 ，空字符串 或者某个特殊值代替

值得一提的是：MySQL会在索引中存储 NULL 值， 而 Oracle 则不会

> 避免太多的列

MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM 的变长行结构和 InnoDB 的行结构总数需要转换。如果使用了非常宽（数千个字段）的表，然而只有一小部分列会实际用到，这时的代价就非常高。

> 避免太多的关联

关联太多，解析和优化查询的代价可能会成为 MySQL 的问题。

一个粗略的经验法则：如果希望查询执行得快速且并发性好，单个查询最好在 12 个表以内做关联

> 注意可变长字符串

可变长字符串在临时表和排序时可能导致悲观的按最大长度分配内存

###### 下一步是选择具体类型，很多 MySQL 的数据类型可以存储相同类型的数据（ 如：INT 和 YINYINT ），只是允许的长度和范围不一样、允许的精度不同，或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性（如：CHAR 和 VARCHAR ） ######

> 整数类型

如果存储整数，可以使用这几种整数类型：TINYINT 、SMALLINT 、MEDIUMINT 、INT 、BIGINT 。分别使用 8 、16 、24 、32 、64 位存储空间。他们可以存储的值的范围从 **-2(N-1)** 到 **2(N-1)-1**，其中 N 是存储空间的位数

提示：1 Byte（字节） = 8 bit（位） 1 bit 可以表示（1 / 0）两种状态【分别对应电平 高 / 低】

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

注：  
有符号和无符号类型使用相同的存储空间，并具有相同的性能。  
你的选择决定 MySQL 是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用 64 位的 BIGINT 整数，即使在 32 位环境也是如此。（一些聚合函数是例外，它们使用 DECIMAL 或 DOUBLE 进行计算）

指定整数类型的宽度，如 INT(11)。不会限制值的合法范围。对于存储和计算来说，INT(11) 和 INT(1) 是相同的。只有当 INT 字段类型设置为无符号且填充零  UNSIGNED ZEROFILL 时，当数值位数未达到设置的显示宽度时，会在数值前面补充零直到满足设定的显示宽度。

为什么会有无符号的限制呢？是因为 ZEROFILL  属性会隐式地将数值转为无符号型，因此不能存储负的数值

> 实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用 DECIMAL 存储比 BIGINT  还大的数。（不同的类型，存储的精度不同）

FOLAT  和 DOUBLE  类型支持使用标准的浮点运算进行 **近似计算**。

DECIMAL 类型用于存储精确的小数，支持精确计算。因为 CPU 不支持对 DECIMAL 的直接计算，MySQL 服务器自身实现了 DECIMAL的高精度计算 【相对而言，CPU 直接支持原生浮点计算，所以浮点运算明显更快】。

DECIMAL类型的值实际存储在一个 二进制字符串 中（每4个字节存9个数字，以小数点分割，两边数字分开存储）。

例如 DECIMAL（18,2）。小数点左边存储16个数字（需8个字节）+ 右边存储2个数字（需4个字节）+ 小数点本身（占1个字节），则共需 13 字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL【例如：存储财务数据】。

在数据量比较大的时候，可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可，这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题

> 字符串类型

**VARCHAR 和 CHAR**

VARCHAR 和 CHAR 是两种最主要的字符串类型。

值得注意的是：存储引擎存储 CHAR 或者 VARCHAR 值的方式在内存中和在磁盘上可能不一样，所以 MySQL 服务器从存储引擎读出的值可能需要转换为另一种存储格式。

VARCHAR：

*  存储可变长字符串，通常比定长类型更节省空间
 *  如果存入的值末尾有空格，会保留
 *  需要额外字节记录字符串长度（列的最大长度 <= 255，需要额外一个字节；否则需要2 字节）

CHAR：

*  存储定长字符串。不容易产生碎片
 *  如果存入的值末尾有空格，会删除
 *  存储时末尾会填充空格到指定长度，检索时会去掉

PS：长度为字符数，如：CHAR(10)，表示存储 10 个字符，具体所需字节数根据所选字符集会有不同，例如 UTF-8（一个字符 = 3字节）

**BINARY 和 VARBINARY**

这两个类型用于存储二进制字符串。（二进制字符串存储的是字节码而不是字符。填充时也不一样，BINARY 采用的是 \\0 而不是 空格，在检索时也不会去掉填充值）

备注：二进制比较是逐字节比较，比字符简单，所以也就更快

**枚举（ ENUM ）**

可以把一些不重复的字符串存储成一个预定义的集合。MySQL 会根据列表值的数量压缩到一个或两个字节中。在 .frm 文件中会保存一个 “数字 - 字符串” 映射关系。MySQL实际存储的值为数字（数字为该字符串在列表中的位置，第一个位置则为 1 ）。可使用命令查看：select <字段名> + 0 from <table\_name> 。这使得 ENUM 列占用的空间比直接存储字符串要小。另外，使用 ENUM 进行表关联时的效率也比字符串列之间相关联来的快

枚举字段排序（order by ）也是按照内部存储的整数排序，而不是定义的字符串（所以对 enum 字段使用 order by 时，并不会根据字符串的 字符集排序）。当然，可以使用 FIELD() 函数显式的指定排序顺序，但这会导致无法利用索引消除排序

备注：枚举的字符串列表在创建时就已经固定，如果要添加或者删除字符串（除了在列表末尾添加字符串之外）就必须使用 LATER TABLE 。这在数据量大的时候会显得异常痛苦。

> 日期和时间类型

**DATETIME 和 TIMESTAMP**

DATETIME 和 TIMESTAMP 列都可以存储相同类型的数据：时间和日期，精确到秒。然而 TIMESTAMP（4字节） 只使用 DATETIME（8字节） 一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面， TIMESTAMP 允许的时间范围要小的多（ 1970 ~ 2038 ）。

建议：除了特殊行为之外，应该尽量使用 TIMESTAMP ，因为它比 DATETIME 空间效率更高。有时候人们也将时间戳作为整数存储，但是这相对于 TIMESTAMP 来说，没有任何优势。用整数保存时间反而还不好处理

另外，人们经常使用 VARCHAR(15) 来存储 IPv4 地址，然而，它们实际上是 32位无符号整数（用小数点将地址分成四段只是为了方便人们阅读），所以应该用无符号整数存储。【MySQL提供了 INET\_ATON() 和 INET\_NTOA() 两个方法对IP表示转换】

#### 参考文献 ####

《高性能MySQL（第3版）》

[MySQL官方文档][MySQL]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTgxNTAwMQ_size_16_color_FFFFFF_t_70]: /images/20221119/fffd2cf0789049e79e0be75b79c904fc.png
[MySQL]: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html