良好的逻辑设计和物理设计是高性能的基石， 应该根据系统将要执行的查询语句来设计schema, 这往往需要权衡各种因素。 例如， 反范式的设计可以加快某些类型的查询， 但同时可能使另一些类型的查询变慢。比如添加计数表和汇总表是一种很好的优化查询的方式，但这些表的维护成本可能会很高。 MySQL独有的特性和实现细节对性能的影响也很大。
4.1选择优化的数据类型
MySQL支持的数据类型非常多， 选择正确的数据类型对于获得高性能至关重要。 不管存储哪种类型的数据， 下面几个简单的原则都有助于做出更好的选择。
更小的通常更好
一般情况下，应该尽最使用可以正确存储数据的最小数据类型。更小的数据类型通常更快， 因为它们占用更少的磁盘、 内存和CPU缓存， 并且处理时需要的CPU周期也更少。
但是要确保没有低估需要存储的值的范围， 因为在schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。 如果无法确 定哪个数据类型是最好的， 就选择你认为不会超过范围的最小类型。（如果系统不是很忙或者存储的数据量不多， 或者是在可以轻易修改设计的早期阶段， 那之后修改数据类型也比较容易）。
简单就好
简单数据类型的操作通常需要更少的CPU周期。 例如， 整型比字符操作代价更低， 因为字符集和校对规则（排序规则 ）使字符比较比整型比较更复杂。 这里有两个例子：一个是应该 使用MySQL内建的类型注 2 而不是字符串来存储日期和时间， 另外一个是应该用整型存储IP地址。 稍后我们将专门讨论这个话题。
尽量避免NULL
很多表都包含可为NULL (空值）的列， 即使应用程序并不需要保存NULL 也是如此， 这是因为可为NULL 是列的默认属性。 通常情况下最好指定列为NOT NULL, 除非真的需要存储NULL 值。
如果查询中包含可为NULL 的列， 对MySQL来说更难优化， 因为可为NULL 的列 使得索引、 索引统计和值比较都更复杂。 可为 N ULL的列会使用更多的存储空间， 在MySQL里也需要特殊处理。 当可为NULL的列被索引时， 每个索引记录需要一个额 外的字节， 在MyISAM 里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
通常把可为NULL 的列改为NOT NULL 带来的性能提升比较小， 所以（调优时）没有 必要首先在现有schema中查找井修改掉这种情况，除非确定这会导致问题。 但是， 如果计划在列上建索引， 就应该尽量避免设计成可为 NULL 的列。
当然也有例外， 例如值得一提的是， lnnoDB 使用单独的位 (bit) 存储NULL 值， 所以对于稀疏数据 有很好的空间效率。 但这一点不适用于MyISAM。
在为列选择数据类型时， 第一步需要确定合适的大类型：数字、 字符串、 时间等。 这通常是很简单的， 但是我们会提到一些特殊的不是那么直观的案例。
下一步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据， 只是存储的长度和范围不一样、允许的精度不同， 或者需要的物理空间（磁盘和内存空间）不同。相同大类型的不同子类型数据有时也有一些特殊的行为和属性。
4.1.1整数类型
有两种类型的数字：整数(whole number) 和实数(real number)。 如果存储整数， 可以使用这几种整数类型：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 是怎么在内存和磁盘中保存数据的。 然而， 整数计算一般使用64 位的 BIGINT 整数， 即使在 32 位环境也是如此。（ 一些聚合函数是例外， 它们使用DECIMAL 或 DOUBLE 进行计算）。
MySQL 可以为整数类型指定宽度， 例如 INT(11), 对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL 的一些交互工具（例如 MySQL 命令行客户端）用来显示字符的个数。 对于存储和计算来说， INT(l) 和 INT(20) 是相同的。
4.1.2实数类型
实数是带有小数部分的数字。 然而， 它们不只是为了存储小数部分，也可以使用DECIMAL 存储比 BIGINT 还大的整数。 MySQL 既支持精确类型，也支持不精确类型。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。 如果需要知道浮点运算是怎么计算的， 则需要研究所使用的平台的浮点数的具体实现。
DECIMAL类型用于存储精确的小数。 在MySQL5.0和更高版本，DECIMAL类型支持精确 计算。MySQL4.1以及更早版本则使用浮点运算来实现DECIAML的计算， 这样做会因为精度损失导致一些奇怪的结果。在这些版本的MySQL中，DECIMAL只是一个“存储类型”。
因为CPU不支持对DECIMAL的直接计算，所以在MySQL5.0以及更高版本中，MySQL 服务器自身实现了DECIMAL的高精度计算。 相对而言，CPU直接支持原生浮点计算， 所 以浮点运算明显更快。
浮点和DECIMAL类型都可以指定精度。 对于DECIMAL列， 可以指定小数点前后所允许的 最大位数。这会影响列的空间消耗。MySQL5.0和更高版本将数字打包保存到一个二进制字符串中 （每4个字节存9个数字）。 例如，DECIMAL(18,9)小数点两边将各存储9个数字， 一共使用9个字节：小数点前的数字用4个字节， 小数点后的数字用4个字节， 小数点本身占1个字节。
MySQL 5.0和更高版本中的DECIMAL类型允许最多65个数字。 而早期的MySQL版本中这个限制是254个数字，并且保存为未压缩的字符串（每个数字一个字节）。然而，这些（早期） 版本实际上并不能在计算中使用这么大的数字， 因为DECIMAL只是一种存储格式；在计算中DECIMAL会转换为DOUBLE类型。
有多种方法可以指定浮点列所需要的精度， 这会使得MySQL悄悄选择不同的数据类型，或者在存储时对值进行取舍。 这些精度定义是非标准的，所以我们建议只指定数据类型，不指定精度。
浮点类型在存储同样范围的值时， 通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样， 能选择的只是存储类型； MySQL使用DOUBLE作为内部浮点计算的类型。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL—例如存储财务数据。 但在数据最比较大的时候， 可以考虑使用BIGINT代替DECIMAL, 将需要存储的货币单位根据小数的位数乘以 相应的倍数即可。 假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后 将结果存储在BIGINT里， 这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。
4.1.3字符串类型
MySQL支持多种字符串类型， 每种类型还有很多变种。 这些数据类型在4.1和5.0版本发生了很大的变化，使得情况 更加复杂。从MySQL4.1开始，每个字符串列可以定义自己的字符集和排序规则，或者说校对规则(collation)(更多关于这个主题的信息请参考第7章）。 这些东西会很大程度上影响性能。
VARCHAR和CHAR类型
VARCHAR和CHAR是两种最主要的字符串类型。 不幸的是，很难精确地解释这些值是怎么存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。下面的描述假设使用的存储引擎是 InnoDB和／或者MyISAM。如果使用的不是这两种存储引擎，请参考所使用的存储引擎的文档。
先看看VARCHAR和CHAR值通常在磁盘上怎么存储。请注意，存储引擎存储CHAR或者VARCHAR值的方式在内存中和在磁盘上可能不一样，所以MySQL服务器从存储引擎读出的值可能需要转换为另一种存储格式。下面是关于两种类型的一些比较。
VARCHAR
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如， 越短的字符串使用越少的空间）。有一种情况例外，如果MySQL 表使用ROW_FORMAT =FIXED创建的话，每一行都会使用定长存储，这会很浪费空间。
VARCHAR需要使用1 或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255 字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。
VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。其他一些存储引擎也许从不在原数据位置更新数据。
下面这些情况下使用VARCHAR是合适的： 字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题，使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。在5.0 或者更高版本，MySQL 在存储和检索时会保留末尾空格。但在4.1 或更老的版本，MySQL会剔除末尾空格。
CHAR
CHAR类型是定长的： MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格（在 MySQL4.1 和更老版本中VARCHAR也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区别只是在存储格式上）。CHAR值会根据 需要采用空格进行填充以方便比较。 CHAR适合存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR 更好，因为定长的 CHAR类型不容易产生碎片。对于非常短的列，CHAR比 VARCHAR在存储空间上也更有效率。例如用 CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有 一个记录长度的额外字节。
数据 如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和 变长的字符串。Memory引擎只支持定长的行，即使有变长字段也会根据最大长度分配最大空间。不过， 填充和截取空格的行为在不同存储引擎都是一样的， 因为这是在 MySQL 服务器层进行处理的。
与 CHAR 和 VARCHAR 类似的类型还有 BINARY 和 VARBINARY, 它们存储的是二进制字符串。 二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。 填充也不一样：MySQL 填充 BINARY采用的是 \0 (零字节）而不是空格， 在检索时也不会去掉填充值
当需要存储二进制数据， 井且希望 MySQL 使用字节码而不是字符进行比较时， 这些类型是非常有用的。 二进制比较的优势井不仅仅体现在大小写敏感上。 MySQL 比较BINARY字符串时， 每次按一个字节， 并且根据该字节的数值进行比较。 因此， 二进制比 较比字符比较简单很多， 所以也就更快。
使用 VARCHAR(5) 和 VARCHAR(200) 存储'hello'的空间开销是一样的。 那么使用更 短的列有什么优势吗？
事实证明有很大的优势。 更长的列会消耗更多的内存， 因为 MySQL 通常会分配固定大小的内存块来保存内部值。 尤其是使用内存临时表进行排序或操作时会特别糟糕。 在利用磁盘临时表进行排序时也同样糟糕。
所以最好的策略是只分配真正需要的空间。
4.1.4日期和时间类型
MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。MySQL能存储的最小时间粒度为秒(MariaDB支持微秒级别的时间类型）。 但是MySQL 也可以使用微秒级的粒度进行临时运算， 我们会展示怎么绕开这种存储限制。
大部分时间类型 都没有替代品， 因此没有什么是最佳选择的问题。 唯一 的问题是保存日期和时间的时候需要做什么。 MySQL提供两种相似的日期类型： DATE TIME和 TIMESTAMP。 对于很多应用程序，它们都能工作， 但是在某些场景， 一个比另一个工作得好。 让我们来看一下。
DATETIME
这个类型能保存大范围的值， 从1001年到9999年， 精度为秒。 它把日期和时间封装到格式为YYYY MMDDHHMMSS的整数中，与时区无关。使用8 个字节的存储空间。
默认情况下，MySQL 以一种可排序的、 无歧义的格式显示 DATETIME 值，例如"2008-01-16 22:37:08"。这是ANSI标准定义的日期和时间表示方法。
TIMESTAMP
就像它的名字一样，TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准 时间） 以来的 秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此 它的范围比DATETIME 小得多：只能表示从1970年到2038年。MySQL提供了 FROM_ UNIXTIME ()函数把Unix时间戳转换为日期，井提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。
4.2 MySQL schema设计中的陷阱
虽然有一些普遍的好或坏的设计原则， 但也有一些问题是由MySQL的实现机制导致的，这意味着有可能犯一些只在MySQL下发生的特定错误。本节我们讨论设计MySQL的schema的问题。这也许会帮助你避免这些错误， 井且选择在MySQL特定实现下工作得更好的替代方案。
太多的列
MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据， 然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MylSAM的定长行结构实际上与服务器层的行结构正好匹配， 所以不需要转换。然而，MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖千列的数量。当我们研究一个CPU占用非常高的案例时， 发现客户使用了非常宽的表（数千个字段）， 然而只有一小部分列会实际用到， 这时转换的代价就非常高。如果计划使用数千个字段， 必须意识到服务器的性能运行特征会有一些不同。
太多的关联
所谓的“实体－属性－值"(EAV)设计模式是一个常见的糟糕设计模式， 尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。事实上在许多关联少千61张表的情况下， 解析和优化查询的代价也会成为MySQL的问题。一个粗略的经验法则， 如果希望查询执行得快速且井发性好， 单个查询最好在12个表以内做关联。
全能的枚举
注意防止过度使用枚举(ENUM)。用枚举值类型也许在任何支持枚举类型的数据库都是一个有问题的设计方案， 这里应该用整数作为外键关联到字典表或者查找表来查找具体值。但是在MySQL中， 当需要在枚举列表中增加一个新的国家时就要做一次ALTER TABLE 操作。在MySQL5.0以及更早的版本中ALTER TABLE 是一种阻塞操作1 即使在5.1和更新版本中， 如果不是在列表的末尾增加值也会一样需要ALTER TABLE (我们将展示一些骇客式的方法来避免阻塞操作，但是这只是骇客的玩法， 别轻易用在生产环境中）。
变相的枚举
枚举(ENUM)列允许在列中存储一组定义值中的单个值， 集合(SE T)列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱.
非此发明(Not Invent Here)的NULL
我们之前写了避免使用NULL 的好处，井且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值” 到表中时， 也不一定非得使用NULL。也许可以使用0、某个特殊值， 或者空字符串作为代替。但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL。在一些场景中， 使用NULL 可能会比某个神奇常数更好。从特定类型的值域中选择一个不可能的值， 例如用-1代表一个未知的整数， 可能导致代码复杂很多，井容易引入bug, 还可能会让事情变得一团糟。处理NULL确实不容易，但有时候会比它的替代方案更好。
4.4缓存表和汇总表
有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表（特别是为满足检索的需求时）。如果能容许少最的脏数据，这是非常好的方法，但是有时确实没有选择的余地（例如，需要避免复杂、昂贵的实时更新操作）。
术语“缓存表” 和“汇总表“ 没有标准的含义。我们用术语“缓存表” 来表示存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢） 数据的表（例如， 逻辑上冗余的数据）。而术语“汇总表”时，则保存的是使用GROUP BY语句聚合数据的表（例如，数据不是逻辑上冗余的）。也有人使用术语“ 累积表(Roll-Up Table)"称呼这些表。因为这些数据被“ 累积” 了。
仍然以网站为例， 假设需要计算之前24小时内发送的消息数。在一个很繁忙的网站不可能维护一个实时精确的计数器。作为替代方案，可以每小时生成一张汇总表。这样也许一条简单的查询就可以做到，井且比实时维护计数器要高效得多。缺点是计数器并不是100%精确。
如果必须获得过去24小时准确的消息发送数量（没有遗漏），有另外一种选择。以每小时汇总表为基础，把前23个完整的小时的统计表中的计数全部加起来， 最后再加上开始阶段和结束阶段不完整的小时内的计数。
不管是哪种方法——不严格的计数或通过小范围查询填满间隙的严格计数——都比计算 message 表的所有行要有效得多。这是建立汇总表的最关键原因。实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE操作有影响，所以一般不希望创建这样的索引。计算最活跃的用户或者最常见的 ”标签” 是这种操作的典型例子。
缓存表则相反，其对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构，跟普通OLTP操作用的表有些区别。
例如，可能会需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表。一个有用的技巧是对缓存表使用不同的存储引擎。例如，如果主表使用InnoDB, 用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，井且可以做全文搜索。有时甚至想把整个表导出My SQL, 插入到专门的搜索系统中获得更高的搜索效率，例如Lucene或者 Sphinx搜索引擎。
在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引（这会更加高效）。
4.6总结
良好的schema设计原则是普遍适用的，但MySQL有它自己的实现细节要注意。 概括来说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单的原则：
• 尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。
• 使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用 NULL 值。
• 尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
• 注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
• 尽扯使用整型定义标识列。
• 避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
• 小心使用 ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。 最好避免使用 BIT。
范式是好的，但是反范式（大多数情况下意味着重复数据） 有时也是必需的，并且能带来好处。 第5章我们将看到更多的例子。 预先计算、 缓存或生成汇总表也可能获得很大 的好处。Justin Swanhart的Flexviews工具可以帮助维护汇总表。
最后，ALTER TABLE 是让人痛苦的操作，因为在大部分情况下，它都会锁表并且重建整张表。 我们展示了一些特殊的场景可以使用骇客方法，但是对大部分场景，必须使用其他更常规的方法，例如在备机执行ALTER井在完成后把它切换为主库。 本书后续章节会 有更多关千这方面的内容。
网友评论