Mysql 性能调优二：Schema设计

schema设计

整数类型

整数就是不带小数点的类型

• 如果存储整数，可以使用这几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT或者BIGINT。它们分别使用8、16、24、32和64位存储空间。

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

• 整数计算有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据数据实际范围选择合适的类型。你的选择决定了MySQL在内存和磁盘中保存数据的方式。然而，整数计算通常使用64位的BIGINT整数。（一些聚合函数是例外，它们使用DECIMAL或DOUBLE进行计算。）

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

总结：
选择合适的整数数据类型（考虑长度、unsigned、尽可能的满足要求的最低长度）

实数类型

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

• FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算
• 有两种方式：（FLOAT(M,D) 或 DOUBLE(M,D)）可以指定浮点列所需的精度，这可能会导致Mysql以静默方式选择不同的数据类型，或者在存储时对其进行近似处理。这些精度说明符是非标准的，因此我们建议只指定数据类型，不指定精度
• 浮点类型通常比DECIMAL使用更少的空间来存储相同范围的值。FLOAT列使用4字节的存储空间。DOUBLE占用8字节，比FLOAT具有更高的精度和更大的值范围。**与整数类型，你只能选择存储类型；**Mysql会使用DOUBLE进行浮点类型的内部计算。
• 由于额外的空间和计算成本，应该尽量只对小树进行精确计算时才使DECIMAL-例如，存储财务数据。但是在一些大容量的场景，可以考虑用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据并精确到万一之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里，这样可以同时避免浮点计算不精确和DECIMAL 精确计算代价高的问题

总结：

1. FLOAT和DOUBLE相比DECIMAL使用更少的存储空间，因此在大数据量存储且精度要求不高时，优先考虑浮点类型
2. DECIMAL适用于需要高精度计算的场景，特别是财务数据
3. 不建议为FLOAT和DOUBLE指定精度，因为这可能导致MySQL以静默方式选择不同的数据类型，或者在存储值时对其进行近似处理
4. 在特定场景下可以用BIGINT代替DECIMAL，通过调整单位来存储精确值

字符串类型

VARCHAR

• VARCHAR用于存储可变长度的字符串，是最常见的字符串数据类型。它比固定长度的类型更节省空间，因为它仅使用必要的空间（即，更少的空间用于存储更短的值
• VARCHAR需要额外使用1或2字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1字节表示，否则使用2字节。假设采用latin1字符集，一个VARCHAR（10）的列需要11字节的存储空间。VARCHAR（1000）的列则需要1002个字节，因为需要2字节存储长度信息
• VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变长度的，在更新时可能会增长，这会导致额外的工作。如果行的增长使得原位置无法容纳更多内容，则处理行为取决于所使用的存储引擎。例如，InnoDB可能需要分割页面来容纳行。其他一些存储引擎也许不在原数据位置更新数据
• VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变长度的，在更新时可能会增长，这会导致额外的工作。如果行的增长使得原位置无法容纳更多内容，则处理行为取决于所使用的存储引擎。例如，InnoDB可能需要分割页面来容纳行。其他一些存储引擎也许不在原数据位置更新数据
• InnoDB更为复杂，它可以将过长的VARCHAR值存储为BLOB

CHAR

• CHAR是固定长度的：MySQL总是为定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL删除所有尾随空格。如果需要进行比较，值会用空格填充。
• CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们的长度总是相同的。对于经常修改的数据，CHAR也比VARCHAR更好，因为固定长度的行不容易出现碎片。对于非常短的列，CHAR也比VARCHAR更高效；设计为只保存Y和N的值的CHAR（1）在单字节字符集[1]中只使用1字节，但VARCHAR（1）需要2字节，因为还有一个记录长度的额外字节

总计：

1. VARCHAR用于存储可变长度的字符串，适用于大多数字符串存储需求，需要一定要设置最大长度
2. CHAR用于存储固定长度的字符串。比如MD5哈希值、状态码等。
3. BLOB或者TEXT适用于存储大数据类型，BLOB二进制，TEXT用于大文本数据
   

日期和时间类型

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时间戳。

总结：

1. DATETIME：适合需要大范围时间（精确到微秒）和时区无关的数据。
2. TIMESTAMP：适合需要时区相关的数据和存储空间有限的场景

位压缩数据类型

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

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

总结：
可以使用SET 来用位 控制某些属性的true/false

JSON数据类型

使用JSON作为系统之间的交换数据的格式正变得越来越普遍。Mysql有原生的JSON数据类型，可以方便地直接在表中的JSON结构进行操作。坏处：违法范式。好处：更新字段很方便

使用如下示例进行测试：

下面来比较数据大小：

sql版本使用3个16kb的页来存储，JSON版本则使用了5个16k的页来存储。JSON使用了更多的空间来定义json的额外字段（大括号、放括号、冒号等）

在测试过后，
在查询性能上，JSON版本相比SQL版本还是慢上一些，最终性能还是便捷要自己做取舍

总结：

1. JSON：存储成本更高、查询效率低，更适合字段频繁更新
2. 非JSON：存储成本较低，查询效率高，更新字段相比没有那么容易
   

选择标识符

整数类型

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

ENUM和SET类型

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

字符串类型

如果可能，应避免使用字符串类型作为标识符的数据类型，因为它们很消耗空间，而且通常比整数类型慢。
对于完全“随机”的字符串要非常小心，如MD5()、SHA1()或UUID()生成的字符串。这些函数生成的新值会任意分布在很大的空间内，这会减慢INSERT和某些类型的SELECT查询的速度：

• 因为插入的值会写到索引的随机位置，所以会使得INSERT查询变慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片
• 因为插入的值会写到索引的随机位置，所以会使得INSERT查询变慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片
• 对于所有类型的查询，随机值都会导致缓存的性能低下，因为它们会破坏引用的局部性，而这正是缓存的工作原理。如果整个数据集都是“热的”，那么将任何特定部分的数据缓存到内存中都没有任何好处，而且如果工作集比内存大，缓存就会出现大量刷新和不命中

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

特殊数据类型

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

schema设计陷阱

太多的列

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

太多的连接

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

“全能”的枚举

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

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

更加应该使用字典表，而不是ENUM

“变相”的枚举

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

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

NULL不是虚拟值

我们之前说过避免使用NULL的好处，并且建议尽可能考虑其他选择。即使需要在表中存储事实上的“空值”，也可能不需要使用NULL。也许可以使用0、特殊值或空字符串作为代替。但是遵循这个原则也不要走极端。当需要表示未知值时，不要太害怕使用NULL。在某些情况下，使用NULL比使用某个虚拟常数更好。从受约束类型的域中选择一个值，例如使用-1表示一个未知的整数，可能会使代码复杂化，容易引入bug，并通常会把事情搞得一团糟。处理NULL并不容易，但通常比其他替代方案更好。
但是遵循这个原则也不要走极端。当需要表示未知值时，不要太害怕使用NULL。在某些情况下，使用NULL比使用某个虚拟常数更好。从受约束类型的域中选择一个值，例如使用-1表示一个未知的整数，可能会使代码复杂化，容易引入bug，并通常会把事情搞得一团糟。处理NULL并不容易，但通常比其他替代方案更好
下面是我们经常看到的一个例子：

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