关于mysql:MySQL优化表设计与数据类型优化

良好的逻辑设计和物理设计是高性能的基石，应该依据零碎将要执行的查问语句来设计 schema，这往往须要衡量各种因素。例如，反范式的设计能够放慢某些类型的查问，但同时可能使另一些类型的查问变慢。比方增加计数表和汇总表是一种很好的优化查问的形式，但这些表的保护老本可能会很高。MySQL 独有的个性和实现细节对性能的影响也很大。

抉择优化的数据类型

MySQL 反对的数据类型十分多，抉择正确的数据类型对于取得高性能至关重要。不论存储哪种类型的数据，上面几个简略的准则都有助于做出更好的抉择。

更小的通常更好

个别状况下，应该尽量应用能够正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且解决时须要的 CPU 周期也更少。

简略就好

简略数据类型的操作通常须要更少的 CPU 周期。例如，整型比字符操作代价更低，因为字符集和校对规定（排序规定）使字符比拟比整型比拟更简单。这里有两个例子：一个是应该应用 MySQL 内建的类型而不是字符串来存储日期和工夫，另外一个是应该用整型存储 IP 地址。

尽量避免 NULL

如果查问中蕴含可为 NULL 的列，对 MySQL 来说更难优化，不应用 NULL 的理由有：

1. 所有应用 NULL 值的状况，都能够通过一个有意义的值的示意，这样有利于代码的可读性和可维护性，并能从束缚上加强业务数据的规范性。
2. NULL 值到非 NULL 的更新无奈做到原地更新，更容易产生索引决裂，从而影响性能。(null -> not null 性能晋升很小，除非确定它带来了问题，否则不要当成优先的优化措施)
3. NULL 值在 timestamp 类型下容易出问题，特地是没有启用参数 explicit_defaults_for_timestamp。
4. NOT IN、!= 等负向条件查问在有 NULL 值的状况下返回永远为空后果，查问容易出错。
5. NULL 会使索引、索引统计和值比拟都更加简单，并且在 MyISIM 中须要额定一个字节的存储空间。

在为列抉择数据类型时，第一步须要确定适合的大类型：数字、字符串、工夫等，下一步是抉择具体类型。很多 MySQL 的数据类型能够存储雷同类型的数据，只是存储的长度和范畴不一样、容许的精度不同，或者须要的物理空间（磁盘和内存空间）不同。雷同大类型的不同子类型数据有时也有一些非凡的行为和属性。

整数类型

如果存储整数，能够应用这几种整数类型：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。有符号和无符号类型应用雷同的存储空间，并具备雷同的性能，因而能够依据理论状况抉择适合的类型。

注：IP 地址实际上是 32 位无符号整数，应该用 INT 存储，MySQL 提供 INETATON 和 INETNTOA 两个转换 IP 地址的函数。

实数类型

实数是带有小数局部的数字。然而，它们不只是为了存储小数局部，也能够应用 DECIMAL 存储比 BIGINT 还大的整数。MySQL 既反对准确类型，也反对不准确类型。FLOAT 和 DOUBLE 类型反对应用规范的浮点运算进行近似计算，如果须要晓得浮点运算是怎么计算的，则须要研究所应用的平台的浮点数的具体实现。DECIMAL 类型用于存储准确的小数，在 MySQL 5.0 和更高版本，DECIMAL 类型反对准确计算。

浮点和 DECIMAL 类型都能够指定精度。对于 DECIMAL 列，能够指定小数点前后所容许的最大位数，这会影响列的空间耗费，MySQL 5.0 和更高版本将数字打包保留到一个二进制字符串中（每 4 个字节存 9 个数字）。例如，DECIMAL（18,9）小数点两边将各存储 9 个数字，一共应用 9 个字节：小数点前的数字用 4 个字节，小数点后的数字用 4 个字节，小数点自身占 1 个字节。

浮点类型在存储同样范畴的值时，通常比 DECIMAL 应用更少的空间。FLOAT 应用 4 个字节存储。DOUBLE 占用 8 个字节，相比 FLOAT 有更高的精度和更大的范畴。

因为须要额定的空间和计算开销，所以应该 尽量只在对小数进行准确计算时才应用 DECIMAL——例如存储财务数据。但 在数据量比拟大的时候，能够思考应用 BIGINT 代替 DECIMAL，将须要存储的货币单位依据小数的位数乘以相应的倍数即可，这样能够同时防止浮点存储计算不准确和 DECIMAL 准确计算代价高的问题。

字符串类型

VARCHAR 和 CHAR 类型

因为当初基本上所有的 MySQL 数据库应用的都是 InnoDB 存储引擎，而且应用的字符集都是 utf8 或者 utf8mb4 这样的多字节字符集。在这种状况下，varchar 和 char 类型都须要应用 1~2 个额定字节去存储字符串的长度，此时 char 相比 varchar 曾经不具备任何的劣势，所以 举荐所有的字符串类型都应用 varchar。

BLOB 和 TEXT 类型

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

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

MySQL 不能将 BLOB 和 TEXT 列全副长度的字符串进行索引，也不能应用这些索引打消排序。同时因为 Memory 引擎不反对 BLOB 和 TEXT 类型，所以，如果查问应用了 BLOB 或 TEXT 列并且须要应用隐式长期表，将不得不应用磁盘长期表。

最好的解决方案是尽量避免应用 BLOB 和 TEXT 类型。如果切实无奈防止，有一个技巧是在所有用到 BLOB 字段的中央都应用 SUBSTRING（column，length）将列值转换为字符串（在 ORDER BY 子句中也实用），这样就能够应用内存长期表了。然而要确保截取的子字符串足够短，不会使长期表的大小超过 max_heap_table_size 或 tmp_table_size，超过当前 MySQL 会将内存长期表转换为 MyISAM 磁盘长期表。

日期和工夫类型

DATETIME 类型能保留 1001 年到 9999 年范畴的值，精度为秒，与时区无关，应用 8 个字节的存储空间。TIMESTAAMP 类型保留了格林尼治规范工夫以来的秒数，只能示意 1970 年到 2038 年范畴的值，显示的值依赖时区，应用 4 个字节的存储空间。

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

注：MySQL5.6.4 版本开始反对比秒更小的存储粒度，格局为 工夫类型（如 timestamp）（n），n 最大为 6。

标识列数据类型抉择

整数通常是标识列最好的抉择，因为它们很快并且能够应用 AUTO_INCREMENT。

如果可能，应该防止应用字符串类型作为标识列，例如 MD5()、SHA1()或者 UUID()产生的字符串。因为它们很耗费空间，并且通常比数字类型慢。这些函数生成的新值会任意散布在很大的空间内，这会导致 INSERT 以及一些 SELECT 语句变得很慢：

• 因为插入值会随机地写到索引的不同地位，所以使得 INSERT 语句更慢。这会 导致页决裂、磁盘随机拜访，以及对于聚簇存储引擎产生聚簇索引碎片。
• SELECT 语句会变得更慢，因为逻辑上相邻的行会散布在磁盘和内存的不同中央。
• 随机值导致缓存对所有类型的查问语句成果都很差，因为会使得缓存赖以工作的拜访局部性原理生效。如果整个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有益处；如果工作集比内存大，缓存将会有很多刷新和不命中。

不好的 Schema 设计实际

过多的列

MySQL 的存储引擎 API 工作时须要在服务器层和存储引擎层之间通过行缓冲格局拷贝数据，而后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是十分高的，转换的代价依赖于列的数量。

过多的关联

如果查问中存在过多的关联，那么解析和优化查问的代价会成为 MySQL 的问题。一个粗略的教训法令，如果心愿查问执行得疾速且并发性好，单个查问最好在 12 个表以内做关联。

适度应用 ENUM

如果列的值可能会在当前裁减，那么就应该防止应用 ENUM 类型。在 MySQL 5.1 和更新版本中，如果不是在列表的开端增加值会须要 ALTER TABLE，对于大表来说会导致重大的性能问题。

范式和反范式

范式的长处：

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

范式的毛病：

1. 通常须要关联。
2. 范式化可能将列寄存在不同的表中，这样会使某些索引生效。

混用范式化和反范式化

范式化和反范式化的 schema 各有优劣，怎么抉择最佳的设计？事实是，在理论利用中常常须要混用，可能应用局部范式化的 schema、缓存表，以及其余技巧。最常见的反范式化数据的办法是复制或者缓存，在不同的表中存储雷同的特定列。

在某些须要特定的查问条件和排序的状况下，能够在父表中冗余一些字段到子表。例如有 user 表和 message 表，要查问付费用户最近 10 条数据，齐全范式化查问的效率较低下，能够在 message 表中冗余账户类型的字段并建设好索引，这将十分高效。不过更新账户类型的时候须要更新两张表。这时 须要思考更新的频率及时长，来和查问的频率作比拟，而后做出取舍。

缓存衍生值也是有用的。如果须要显示每个用户发了多少音讯（像很多论坛做的），能够每次执行一个低廉的子查问来计算并显示它，也能够在 user 表中建一个 num_messages 列，每当用户发新音讯时更新这个值。

延长浏览：
对关系型数据库五个范式的了解
如何了解关系型数据库的常见设计范式？

缓存表和汇总表

有时晋升性能最好的办法是在同一张表中保留衍生的冗余数据。然而，有时也须要创立一张齐全独立的汇总表或缓存表（特地是为满足检索的需要时）。如果能答应大量的脏数据，这是十分好的办法，然而有时的确没有抉择的余地（例如，须要防止简单、低廉的实时更新操作）。

术语“缓存表”和“汇总表”没有规范的含意。咱们用术语“缓存表”来示意存储那些能够比较简单地从 schema 其余表获取（然而每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据）。而术语“汇总表”时，则保留的是应用 GROUP BY 语句聚合数据的表（例如，数据不是逻辑上冗余的）。也有人应用术语“累积表（Roll-Up Table）”称说这些表。因为这些数据被“累积”了。

以网站为例，假如须要计算之前 24 小时内发送的音讯数。在一个很忙碌的网站不可能保护一个实时准确的计数器。作为代替计划，能够每小时生成一张汇总表。这样兴许一条简略的查问就能够做到，并且比实时保护计数器要高效得多。毛病是计数器并不是 100%准确。

如果必须取得过来 24 小时精确的音讯发送数量（没有脱漏），有另外一种抉择。以每小时汇总表为根底，把前 23 个残缺的小时的统计表中的计数全副加起来，最初再加上开始阶段和完结阶段不残缺的小时内的计数。

当然，更好的办法是应用内存数据库来实现这个计数器，例如 Redis。