良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询语句来设计 schema,这往往需要权衡各种因素。
例如,反范式的设计可以加快某些类型的查询,但同 时可能使另一些类型的査询变慢
。
比如添加计数表和汇总表是一种很好的优化査询的方式, 但这些表的维护成本可能会很高。MySQL独有的特性和实现细节对性能的影响也很大。
本章和聚焦在索引优化的下一章,覆盖了 MySQL特有的schema设计方面的主题。假设读者已经知道如何设计数据库,所以本章既不会介绍如何入门数据库设计,也不 会讲解数据库设计方面的深入内容。这一章关注的是MySQL数据库的设计,主要介绍 的是MySQL数据库设计与其他关系型数据库管理系统的区别。如果需要学习数据库设 计方面的基础知识,建议阅读 Clare Churcher 的 Beginning Database Design (Apress 出 版社)一书。
本章内容是为接下来的两个章节做铺垫。在这三章中,我们将讨论逻辑设计、物理设计 和査询执行,以及它们之间的相互作用。这既需要关注全局,也需要专注细节。还需要 理解整个系统以便弄清楚各个部分如何相互影响。如果在阅读完索引和查询优化章节后 再回头来看这一章,也许会发现本章很有用,很多讨论的议题不能孤立地考虑。
4.1选择优化的数据类型
MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管 存储哪种类型的数据,下面几个简单的原则都有助于做出更好的选择。
更小的通常更好。
一般情况下,应该尽量使用可以正确存储数据的最小数据类型注’。更小的数据类型通常更快
,因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。
但是要确保没有低估需要存储的值的范围
,因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的,就 选择你认为不会超过范围的最小类型。(如果系统不是很忙或者存储的数据量不多, 或者是在可以轻易修改设计的早期阶段,那之后修改数据类型也比较容易)O
简单就好
简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低, 因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。这里有两个例子: 一个是应该使用MySQL内建的类型注2而不是字符串来存储日期和时间,另外一个 是应该用整型存储IP地址。稍后我们将专门讨论这个话题。
尽量避免NULL
很多表都包含可为NULL (空值)的列,即使应用程序并不需要保存NULL也是如此, 这是因为可为NULL是列的默认属性注3。通常情况下最好指定列为NOT NULL
,除非真 的需要存储NULL值。
如果査询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在 MySQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MylSAM里甚至还可能导致固定大小的索引(例如只有一个整数列的 索引)变成可变大小的索引。
通常把可为NULL的列改为NOT NULL带来的性能提升比较小,所以(调优时)没有 必要首先在现有schema中査找并修改掉这种情况,除非确定这会导致问题。但是, 如果计划在列上建索引,就应该尽量避免设计成可为NULL的列。
当然也有例外,例如值得一提的是,InnoDB使用单独的位(bit)存储NULL值,所以对于稀疏数据注4有很好的空间效率。但这一点不适用于MyISAMo在为列选择数据类型时,第一步需要确定合适的大类型:数字、字符串、时间等。这通 常是很简单的,但是我们会提到一些特殊的不是那么直观的案例。
下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据,只是存储 的长度和范围不一样、允许的精度不同,或者需要的物理空间(磁盘和内存空间)不同。 相同大类型的不同子类型数据有时也有一些特殊的行为和属性。
例如,DATETIME和TIMESAMP列都可以存储相同类型的数据:时间和日期,精确到秒。DATETIME 一半的存储空间,
并且会根据时区变化,具有特殊的自 动更新能力。另一方面,TIMESTAMP允许的时间范围要小得多,有时候它的特殊能力会 成为障碍。
本章只讨论基本的数据类型。MySQL为了兼容性支持很多别名,例如INTEGER. BOOL, 以及NUMERIC它们都只是别名。这些别名可能令人不解,但不会影响性能。如果建表 时采用数据类型的别名,然后用SHOW CREATE TABLE检査,会发现MySQL报告的是基 本类型,而不是别名。
4.1.1整数类型
有两种类型的数字:整数(whole number)和实数(real number) o
如果存储整数,可以使用这几种整数类型:TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT0
分别使用8, 16, 24, 32, 64位存储空间。它们可以存储的值的范围从-2(n-1)到2(n-1)-1,其中N是 存储空间的位数。
整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。 例如TINYINT. UNSIGNED可以存储的范围是0255,而TINYINT的存储范围是-128127。
有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况 选择合适的类型。
你的选择决定MySQL是怎么在内存和磁盘中保存数据的。然而,整数计算一般使用 64位的BIGINT整数,即使在32位环境也是如此。(一些聚合函数是例外,它们使用 DECIMAL或DOUBLE进行计算)。
MySQL可以为整数类型指定宽度,例如INT(ll),对大多数应用这是没有意义的:它不 会限制值的合法范围,只是规定了 MySQL的一些交互工具(例如MySQL命令行客户端) 用来显示字符的个数。对于存储和计算来说,INT(l)和INT(29)是相同的。
4.1.2实数类型
实数是带有小数部分的数字。然而,它们不只是为了存储小数部分、也可以使用 DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型,也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算是 怎么计算的,则需要研究所使用的平台的浮点数的具体实现。
DECIMAL类型用于存储精确的小数。在MySQL 5.0和更高版本,DECIMAL类型支持精确 计算。MySQL 4.1以及更早版本则使用浮点运算来实现DECIAML的计算,这样做会因为 精度损失导致一些奇怪的结果。在这些版本的MySQL中,DECIMAL只是一个“存储类型”。
因为CPU不支持对DECIMAL的直接计算,所以在MySQL 5.0以及更高版本中,MySQL 服务器自身实现了 DECIMAL的高精度计算。相对而言,CPU直接支持原生浮点计算,所 以浮点运算明显更快。
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列,可以指定小数点前后所允许的 最大位数。这会影响列的空间消耗。MySQL 5.0和更高版本将数字打包保存到一个二进 制字符串中(每4个字节存9个数字)。例如,DECIMAL(18,9)小数点两边将各存储9个 数字,一共使用9个字节:小数点前的数字用4个字节,小数点后的数字用4个字节, 小数点本身占1个字节。
MySQL 5.0和更高版本中的DECIMAL类型允许最多65个数字。而早期的MySQL版本中 这个限制是254个数字,并且保存为未压缩的字符串(每个数字一个字节)。然而,这些(早 期)版本实际上并不能在计算中使用这么大的数字,因为DECIMAL只是一种存储格式; 在计算中DECIMAL会转换为DOUBLE类型。
有多种方法可以指定浮点列所需要的精度,这会使得MySQL悄悄选择不同的数据类型, 或者在存储时对值进行取舍。这些精度定义是非标准的,所以我们建议只指定数据类型, 不指定精度。 j
浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字 节存储。DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。和整数类型一样, 能选择的只是存储类型;MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用 DECIMAL——例如存储财务数据。但在数据量比较大的时候,可以考虑使用BIGINT代替 Hi9> decimal,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财 务数据精确到万分之一分,则可以把所有金额乘以一百万,然后将结果存储在BIGINT里, 这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
4.1.3字符串类型
MySQL支持多种字符串类型,每种类型还有很多变种。这些数据类型在4.1和5.0版本
发生了很大的变化,使得情况更加复杂。从MySQL 4.1开始,每个字符串列可以定义自 己的字符集和排序规则,或者说校对规则(collation)(更多关于这个主题的信息请参考 第7章)。这些东西会很大程度上影响性能。
VARCHAR 和 CHAR 类型
VARCHAR和CHAR是两种最主要的字符串类型。不幸的是,很难精确地解释这些值是怎么 存储在磁盘和内存中的,因为这跟存储引擎的具体实现有关。下面的描述假设使用的存 储引擎是InnoDB和/或者MylSAM。如果使用的不是这两种存储引擎,请参考所使用 的存储引擎的文档。
先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意,存储引擎存储CHAR或者 VARCHAR值的方式在内存中和在磁盘上可能不一样,所以MySQL服务器从存储引擎读 出的值可能需要转换为另一种存储格式。下面是关于两种类型的一些比较。
VARCHAR
VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型 更节省空间,因为它仅使用必要的空间(例如,越短的字符串使用越少的空间)。有 一种情况例外,如果MySQL表使用ROW_FORMAT=FIXED创建的话,每一行都会使用 定长存储,这会很浪费空间。
VARCHAR需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或 等于255字节,则只使用1个字节表示,否则使用2个字节。假设采用latinl字符集, 一个VARCHAR(1O)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节,因为需要2个字节存储长度信息。
VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在 UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。如果一个行占用 的空间增长,并且在页内没有更多的空间可以存储,在这种情况下,不同的存储引 擎的处理方式是不一样的。例如,MylSAM会将行拆成不同的片段存储,InnoDB 则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新 数据。
下面这些情况下使用VARCHAR是合适的:字符串列的最大长度比平均长度大很多; 列的更新很少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符 都使用不同的字节数进行存储。
在5.0或者更高版本,MySQL在存储和检索时会保留末尾空格。但在4.1或更老 的版本,MySQL会剔除末尾空格。
IrmoDB则更灵活,它可以把过长的VARCHAR存储为BLOB,我们稍后讨论这个问题。
CHAR
CHAR类型是定长的:MySQL总是根据定义的字符串长度分配足够的空间。当存储 CHAR值时,MySQL会删除所有的末尾空格(在MySQL 4.1和更老版本中VARCHAR 也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的,区 别只是在存储格式上)。CHAR值会根据需要采用空格进行填充以方便比较。
CHAR适合存储很短的字符串,或者所有值都接近同一个长度。两如,CHAR非常适 合存储密码的MD5值,因为这是一个定长的值。对于经常变更的数据,CHAR也比 VARCHAR更好,因为定长的CHAR类型不容易产生碎片。对于非常短的列,CHAR比 VARCHAR在存储空间上也更有效率。例如用CHAR(l)来存储只有Y和N的值,如果 采用单字节字符集注5只需要一个字节,但是VARCHAR(l)却需要两个字节,因为还有 一个记录长度的额外字节。
4.2 MySQL schema设计中的陷阱
虽然有一些普遍的好或坏的设计原则,但也有一些问题是由MySQL的实现机制导致的, 这意味着有可能犯一些只在MySQL下发生的特定错误。本节我们讨论设计MySQL的 schema的问题。这也许会帮助你避免这些错误,并且选择在MySQL特定实现下工作得更好的替代方案。
太多的列
MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式 拷贝数据,然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转 换成行数据结构的操作代价是非常高的。MylSAM的定长行结构实际上与服务器层 的行结构正好匹配,所以不需要转换。然而,MylSAM的变长行结构和InnoDB的 行结构则总是需要转换。转换的代价依赖于列的数量。当我们研究一个CPU占用非 常高的案例时,发现客户使用了非常宽的表(数千个字段),然而只有一小部分列会 实际用到,这时转换的代价就非常高。如果计划使用数千个字段,必须意识到服务 器的性能运行特征会有一些不同。
太多的关联
所谓的“实体■属性-值”(EAV)设计模式是一个常见的糟糕设计模式,尤其是在 MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表,但 是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。 事实上在许多关联少于61张表的情况下,解析和优化査询的代价也会成为MySQL 的问题。一个粗略的经验法则,如果希望査询执行得快速且并发性好,单个査询最 好在12个表以内做关联。
全能的枚举
注意防止过度使用枚举(ENUM)。下面是我们见过的一个例子:
CREATE TABLE ...(
COUNTRY ENUM('','1'.'2','3'....,'51')
这种模式的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'
如果这里真和假两种情况不会同时出现,那么毫无疑问应该使用枚举列代替集合列。
非此发明(Not Invent Here)的 NULL
我们之前写了避免使用NULL的好处,并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时,也不一定非得使用NULL也许可以使用0、某 个特殊值,或者空字符串作为代替。
但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULLO 在一些场景中,使用NULL可能会比某个神奇常数更好。从特定类型的值域中选择一 个不可能的值,例如用-1代表一个未知的整数,可能导致代码复杂很多,并容易引入bug,还可能会让事情变得一团糟。处理NULL确实不容易,但有时候会比它的替
代方案更好。
下面是一个我们经常看到的例子:
CREATE TABLE …( dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00*
伪造的全0值可能导致很多问题(可以配置MySQL的SQL_MODE来禁止不可能的日期, 对于新应用这是个非常好的实践经验,它不会让创建的数据库里充满不可能的值)。
值得一提的是,MySQL会在索引中存储NULL值,而Oracle则不会。
4.3范式和反范式
对于任何给定的数据通常都有很多种表示方法,从完全的范式化到完全的反范式化,以 及两者的折中。在范式化的数据库中,每个事实数据会出现并且只出现一次。相反,在反范式化的数据库中,信息是冗余的,可能会存储在多个地方。
4.3.1范式的优点和缺点
当为性能问题而寻求帮助时,经常会被建议对schema进行范式化设计,尤其是写密集的场景。这通常是个好建议。因为下面这些原因,范式化通常能够带来好处:
- 范式化的更新操作通常比反范式化要快。
- 当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据。
- 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
- 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。 还是前面的例子:在非范式化的结构中必须使用DISTINCT或者GROUP BY才能获得 一份唯一的部门列表,但是如果部门(DEPARTMENT)是一张单独的表,则只需要简 单的査询这张表就行了。
范式化设计的schema的缺点是通常需要关联。稍微复杂一些的査询语句在符合范式的 schema上都可能需要至少一次关联,也许更多。这不但代价昂贵,也可能使一些索引策 略无效。例如,范式化可能将列存放在不同的表中,而这些列如果在一个表中本可以属于同一个索引。
4.3.2反范式的优点和缺点
反范式化的schema因为所有数据都在一张表中,可以很好地避免关联。
如果不需要关联表,则对大部分査询最差的情况一一即使表没有使用索引——是全表扫 描。当数据比内存大时这可能比关联要快得多,因为这样避免了随机I/O
单独的表也能使用更有效的索引策略.
主要问题是关联,使得需要在一个索引命又排序又过滤。如果采用反范式化组织数据, 将两张表的字段合并一下,并且增加一个索引(account type, published),就可以不 通过关联写出这个查询。
4.3.3混用范式化和反范式化
范式化和反范式化的schema各有优劣,怎么选择最佳的设计?
事实是,完全的范式化和完全的反范式化schema都是实验室里才有的东西:在真实 世界中很少会这么极端地使用。在实际应用中经常需要混用,可能使用部分范式化的 ##### schema、缓存表,以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。在 MySQL 5.0和更新版本中,可以使用触发器更新缓存值,这使得实现这样的方案变得更 简单。
在我们的网站实例中,可以在user表和message表中都存储account_type字段,而不 用完全的反范式化。这避免了完全反范式化的插入和删除问题,因为即使没有消息的时 候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大,有利于高效地 获取数据。
但是现在更新用户的账户类型的操作代价就高了,因为需要同时更新两张表。至于这会 不会是一个问题,需要考虑更新的频率以及更新的时长,并和执行SELECT査询的频率进 行比较。
另一个从父表冗余一些数据到子表的理由是排序的需要。例如,在范式化的schema里通 过作者的名字对消息做排序的代价将会非常高,但是如果在message表中缓存author, name字段并且建好索引,则可以非常高效地完成排序。缓存衍生值也是有用的。如果需要显示每个用户发了多少消息(像很多论坛做的),可以 每次执行一个昂贵的子査询来计算并显示它;也可以在user表中建一个num messages 列,每当用户发新消息时更新这个值。
4.5加快ALTER TABLE操作的速度
MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表 结构操作的方法是用新的结构创建一个空表,从旧表中査出所有数据插入新表,然后删 除旧表。这样操作可能需要花费很长时间,如果内存不足而表又很大,而且还有很多索 引的情况下尤其如此。许多人都有这样的经验,ALTER TABLE操作需要花费数个小时甚 至数天才能完成。
MySQL 5.1以及更新版本包含一些类型的“在线”操作的支持,这些功能不需要在整个 操作过程中锁表。最近版本的InnoDB注”也支持通过排序来建索引,这使得建索引更快 并且有一个紧凑的索引布局。
一般而言,大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL 操作时有用的技巧,但这是针对一些特殊的场景而言的。对常见的场景,能使用的技巧 只有两种:一种是先在一台不提供服务的机器上执行ALTER TABLE操作,然后和提供服 务的主库进行切换;另外一种技巧是“影子拷贝气影子拷贝的技巧是用要求的表结构 创建一张和源表无关的新表,然后通过重命名和删表操作交换两张表。也有一些工具 可以帮助完成影子拷贝工作:例如,Facebook数据库运维团队(https://launchpad.net/ mysqlatfacebook)的 Konline schema changew 工具、Shlomi Noach 的 openark toolkit (http:// code.openark.org/),以及 Percona Toolkit (http://www.percona.com/software/) o 如果使 用Flexviews (参考4.4.1节),也可以通过其CDC工具执行无锁的表结构变更。
不是所有的ALTER TABLE操作都会引起表重建。
HOW STATUS显示这个语句做了 1 000次读和1 000次插入操作。换句话说,它拷贝了整 <3F| 张表到一张新表,甚至列的类型、大小和可否为NULL属性都没改变。
理论上,MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的文件中, 所以可以直接修改这个文件而不需要改动表本身。然而MySQL还没有采用这种优化的 方法,所有的MODIFY COLUMN操作都将导致表重建。
总结
良好的schema设计原则是普遍适用的,但MySQL有它自己的实现细节要注意。概括来 说,尽可能保持任何东西小而简单总是好的。MySQL喜欢简单,需要使用数据库的人 应该也同样会喜欢简单的原则:
- 尽量避免过度设计,例如会导致极其复杂査询的schema设计,或者有很多列的表设 计(很多的意思是介于有点多和非常多之间)。
- 使用小而简单的合适数据类型,除非真实数据模型中有确切的需要,否则应该尽可 能地避免使用NULL值。
- 尽量使用相同的数据类型存储相似或相关的值,尤其是要在关联条件中使用的列。
- 注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存。
- 尽量使用整型定义标识列。
- 避免使用MySQL已经遗弃的特性,例如指定浮点数的精度,或者整数的显示宽度。
- 小心使用ENUM和SET。虽然它们用起来很方便,但是不要滥用,否则有时候会变成 陷阱。最好避免使用BITO
范式是好的,但是反范式(大多数情况下意味着重复数据)有时也是必需的,并且能带 来好处。第5章我们将看到更多的例子。预先计算、缓存或生成汇总表也可能获得很大 的好处。Justin Swanhart的Flexviews工具可以帮助维护汇总表。
最后,ALTER TABLE是让人痛苦的操作,因为在大部分情况下,它都会锁表并且重建整 张表。我们展示了一些特殊的场景可以使用骇客方法;但是对大部分场景,必须使用其 他更常规的方法,例如在备机执行ALTER并在完成后把它切换为主库。本书后续章节会 有更多关于这方面的内容。
索引(在MySQL中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构。 这是索引的基本功能,除此之外,本章还将讨论索引其他一些方面有用的属性。
索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时,索引对性能的影响 愈发重要。在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但 当数据量逐渐增大时,性能则会急剧下降注七
不过,索引却经常被忽略,有时候甚至被误解,所以在实际案例中经常会遇到由糟糕索 引导致的问题。这也是我们把索引优化放在了靠前的章节,甚至比査询优化还靠前的原 因。
索引优化应该是对査询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数 量级,“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最 优”的索引经常需要重写査询,所以,本章和下一章的关系非常紧密。