本文摘录总结自《高性能MySQL》(第三版),将以每章一篇文章的方式带大家读这本数据库经典之作。总结精华,帮大家快速抓住重点信息,节省宝贵时间。
这章概念性东西比较多,可能有点枯燥。但讲了很多底层原理,坚持读下来还是会有一些收获的。
100
多位经验丰富的开发者参与,在 Github 上获得了近1000
个star
的全栈全平台开源项目想了解下吗? 项目地址:github.com/cachecats/c…
MySQL 支持的数据类型非常多,选择正确的数据类型至关重要。下面的几个简单原则有助于做出更好的选择。
有两种类型的数字:整数(whole number)和实数(real number)。如果存储整数,可以使用这几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间。
整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0~255,而TINYINT的存储范围是−128~127。
有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。
MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1) 和 INT(20)是相同的。
实数是带有小数部分的数字。然而,它们不只是为了存储小数部分;也可以使用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型,也支持不精确类型。
FLOAT 和 DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。
CPU不支持对DECIMAL的直接计算,5.0以及更高版本中,MySQL服务器自身实现了DECIMAL的高精度计算。相对而言,CPU直接支持原生浮点计算,所以浮点运算明显更快。
定义列的时候建议只指定数据类型,不指定精度。
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。
VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间。
VARCHAR需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。
VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。
下面这些情况下使用VARCHAR是合适的:
最好的策略是只分配真正需要的空间,不要太慷慨,因为更长的列会消耗更多的内存。
CHAR类型是定长的:MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时,MySQL会删除所有的末尾空格。
CHAR适合存储很短的字符串,或者所有值都接近同一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。
对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。对于非常短的列,CHAR 比 VARCHAR 在存储空间上更有效率,因为 VARCHAR 还需要一个记录长度的额外字节。
BLOB 和 TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
实际上,它们分别属于两组不同的数据类型家族:字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT;对应的二进制类型是TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB 。 BLOB 是 SMALLBLOB的同义词,TEXT 是 SMALLTEXT的同义词。
MySQL对BLOB 和 TEXT列进行排序与其他类型是不同的:它只对每个列的最前max_sort_length 字节而不是整个字符串做排序。如果只需要排序前面一小部分字符,则可以减小max_sort_length的配置,或者使用ORDER BY SUSTRING(column,length ) 。
有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
枚举最不好的地方是,字符串列表是固定的,添加或删除字符串必须使用ALTER TABLE。除非能接受只在列表末尾添加元素,否则使用枚举不是个好主意。
MySQL可以使用许多类型来保存日期和时间值,例如YEAR 和 DATE。MySQL能存储的最小时间粒度为秒(MariaDB支持微秒级别的时间类型)。但是MySQL也可以使用微秒级的粒度进行临时运算,我们会展示怎么绕开这种存储限制。
MySQL 提供两种相似的日期类型,DATETIME 和 TIMESTAMP。对于很多应用程序,它们都能工作,但是在某些场景,一个比另一个工作得好。
这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。 默认情况下,MySQL以一种可排序的、无歧义的格式显示DATETIME值,例如“2008-01-16 22:37:08”。这是ANSI标准定义的日期和时间表示方法。
就像它的名字一样,TIMETAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间,因此它的范围比DATETIME小得多:只能表示从1970年到2038年。
TIMESTAMP显示的值也依赖于时区。MySQL服务器、操作系统,以及客户端连接都有时区设置。
有必要强调一下这个区别:如果在多个时区存储或访问数据,TIMESTAMP 和 DATETIME的行为将很不一样。前者提供的值与时区有关系,后者则保留文本表示的日期和时间。
在插入数据时如果没有指定值,会自动填充为当前时间。
TIMESTAMP 默认为 NOT NULL。
通常应该尽量使用 TIMESTAMP,因为它比 DATETIME 空间效率更高。 如果需要存储比秒更小粒度的日期和时间值,可以使用BIGINT类型存储微秒级别的时间截,或者使用DOUBLE存储秒之后的小数部分。这两种方式都可以,或者也可以使用MariaDB替代MySQL。
为标识列(identifier column)选择合适的数据类型非常重要。
标识列也可能在另外的表中作为外键使用,所以为标识列选择数据类型时,应该选择跟关联表中的对应列一样的类型。混用不同类型可能导致性能问题,即使没有性能影响,在比较操作时隐式的类型转换也可能导致很难发现的错误。
在可以满足值的范围的需求,并且预留未来增长空间的前提下,应该选择最小的数据类型。下面是一些小技巧:
某些类型的数据并不直接与内置类型一致。这里有两个例子:
虽然有一些普遍的好或坏的设计原则,但也有一些问题是由MySQL的实现机制导致的,这意味着有可能犯一些只在MySQL下发生的特定错误。本节我们讨论设计MySQL的schema的问题。这也许会帮助你避免这些错误,并且选择在MySQL特定实现下工作得更好的替代方案。
MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配,所以不需要转换。然而,MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。当我们研究一个CPU占用非常高的案例时,发现客户使用了非常宽的表(数千个字段),然而只有一小部分列会实际用到,这时转换的代价就非常高。如果计划使用数千个字段,必须意识到服务器的性能运行特征会有一些不同。
所谓的“实体-属性-值”(EAV)设计模式是一个常见的糟糕设计模式,尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表,但是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。事实上在许多关联少于61张表的情况下,解析和优化查询的代价也会成为MySQL的问题。一个粗略的经验法则,如果希望查询执行得快速且并发性好,单个查询最好在12个表以内做关联。
注意防止过度使用枚举(ENUM)。下面是我们见过的一个例子:
CREATE TABLE ... (
country enum('','0','1','2',...,'31')
这种模式的schema设计非常凌乱。这么使用枚举值类型也许在任何支持枚举类型的数据库都是一个有问题的设计方案,这里应该用整数作为外键关联到字典表或者查找表来查找具体值。但是在MySQL中,当需要在枚举列表中增加一个新的国家时就要做一次ALTER TABLE操作。在MySQL 5.0以及更早的版本中ALTER TABLE是一种阻塞操作;即使在5.1和更新版本中,如果不是在列表的末尾增加值也会一样需要ALTER TABLE。
枚举(ENUM)列允许在列中存储一组定义值中的单个值,集合(SET)列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。这是一个例子:
CREATE TABLE ... (
is_default set ('Y','N') NOT NULL default 'N'
如果这里真和假两种情况不会同时出现,那么毫无疑问应该使用枚举列代替集合列。
我们之前写了避免使用NULL的好处,并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时,也不一定非得使用NULL。也许可以使用0、某个特殊值,或者空字符串作为代替。
但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。在一些场景中,使用NULL可能会比某个神奇常数更好。从特定类型的值域中选择一个不可能的值,例如用−1代表一个未知的整数,可能导致代码复杂很多,并容易引入bug,还可能会让事情变得一团糟。处理NULL确实不容易,但有时候会比它的替代方案更好。
对于任何给定的数据通常都有很多种表示方法,从完全的范式化到完全的反范式化,以及两者的折中。在范式化的数据库中,每个事实数据会出现并且只出现一次。相反,在反范式化的数据库中,信息是冗余的,可能会存储在多个地方。
当为性能问题而寻求帮助时,经常会被建议对schema进行范式化设计,尤其是写密集的场景。这通常是个好建议。因为下面这些原因,范式化通常能够带来好处:
范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联,也许更多。这不但代价昂贵,也可能使一些索引策略无效。例如,范式化可能将列存放在不同的表中,而这些列如果在一个表中本可以属于同一个索引。
反范式化的schema因为所有数据都在一张表中,可以很好地避免关联。 如果不需要关联表,则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多,因为这样避免了随机 I/O 。
单独的表也能使用更有效的索引策略。
范式化和反范式化的schema各有优劣,怎么选择最佳的设计?
事实是,完全的范式化和完全的反范式化schema都是实验室里才有的东西:在真实世界中很少会这么极端地使用。在实际应用中经常需要混用,可能使用部分范式化的schema、缓存表,以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。在MySQL 5.0和更新版本中,可以使用触发器更新缓存值,这使得实现这样的方案变得更简单。
好啦,本章的内容就到这里啦,我们下期见~