第四章 Schema与数据类型优化
4.1 选择优化的数据类型
MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据,下面几个简单的原则都有助于做出更好的选择:
- 更小的通常更好
一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快。因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。但是要确保没有低估需要存储的值的范围,因为在schema中的增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的,就选择不会超过范围的最小类型。 - 简单就好
简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。这里有两个例子:一个是应该使用MySQL内建的类型而不是字符串来存储时间和日期,另一个是应该用整型存储IP地址。 - 尽量避免NULL
很多表都包含可为NULL的列,即使应用程序并不需要保存NULL也是如此,这是因为可为NULL是列的默认属性(TIMESTAMP除外),然而通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。
如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引统计和值比较更加复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊的处理。当可为NULL的字段被索引时,每个索引记录需要一个额外的字节,在MyASIM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。
通常把可为NULL的列改为NOT NULL 带来的性能提升比较小,所以(调优时)没有必要首先在现有schema中查找并修改这种情况,除非确定这会导致问题。但是,如果计划在列上建索引,就应该尽量避免设计为NULL的列。当然也有一些例外,例如值得一提的是,InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据(很多值为NULL,只有少数行是非NULL)有很好的空间效率。但这一点不适用于MyISAM。
在为列选择数据类型时,第一步需要确定适合的大类型,如:数字、字符串、时间等。然后,才选择具体的类型,比如数字类型有:TINYINT、INT、BIGINT,字符串类型有:VARCHAR、CHAR,时间类型有:DATETIME、TIMESTAMP。如果我们要保存年龄,年龄是数字类型的,INT和BIGINT都可以保存,但通常情况下,INT就已经绰绰有余了。
很多MySQL的数据类型可以存储相同类型的数据,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间(磁盘和内存空间)不同。相同大类型的不同子类数据类型有时也有一些特殊的行为和属性。
例如,DATETIME和TIMESTAMP列都可以存储相同类型的数据:时间和日期,精确到秒。然而,TIMESTAMP只使用DATETIME一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力。另一方面,TIMESTAMP允许的时间范围要小得多,有时候它的特殊能力会成为障碍。
4.1.1 整数类型
有两种类型的数字:整数和实数。如果存储整数,可以使用这几种整数类型: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。
有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。
MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了MySQL的一些交互工具用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。
4.1.2 实数类型
实数是带有小数部分的数字。然而,它们不只是为了存储小数部分;也可以使用DEMICAL存储比BIGINT还要大的整数。MySQL既支持精确类型,也支持不精确类型。浮点数类型在存储同样范围的值时,通常比DEMICAL使用更少的空间。FLOAT使用4个字节,DOUBLE使用8个字节,DOUBLE比FLOAT有更高的精度和更大的范围。和整数类型一样,能选择的只是存储类型,MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DEMICAL——例如财务数据。但在数据量比较大的时候,可以考虑使用BIGINT代替DEMICAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分,则可以把所有的金额乘以一万,然后将结果存储在BIGINT中,这样可以同时避免浮点存储计算不精确和DEMICAL精确计算代价高的问题。
4.1.3 字符串类型
MySQL支持多种字符串类型,每种类型还有很多变种。VARCHAR和CHAR是两种最主要的字符串类型,但很难解释这些值是如何存储在磁盘和内存中,因为这跟存储引擎的实现有关。但我们可以介绍如果在存储引擎是InnoDB或MyISAM对字符串的存储。
VARCHAR
VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅适用必要的空间(例如,越短的字符串使用越少的空间)。有一种情况例外,如果MySQL表使用ROW_FORMAT=FIXED创建的话,每一行都会使用定长存储,这会很浪费空间。
VARCHAR需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。假设采用latin1字符集,一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节,因为需要2个字节存储长度信息。
VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,在这种情况下,不同的存储引擎的处理方式是不一样的。例如,MyISAM会将行拆成不同的片段存储,InnoDB则需要分裂页使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。
下面这些情况使用VARCHAR是合适的:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。
CHAR
CHAR类型是定长的:MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。CHA