【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。

你可能感兴趣的