第 4 章 Schema与数据类型优化
良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schema，这往往需要权衡各种因素。例如，反范式的设计可以加快某些类型的查询，但同时可能使另一些类型的查询变慢。比如添加计数表和汇总表是一种很好的优化查询的方式但这些表的维护成本可能会很高。
4.1 选择优化的数据类型
不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。
-
更小的逋常更好。
- 一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
- 但是要确保没有低估需要存储的值的范围
-
简单就好
- 简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型比较更复杂。
- 这里有两个例子：一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外一个是应该用整型存储IP地址。
-
尽量避免NULL
- 很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。
- 如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。
在为列选择数据类型时，要遵循如下顺序
- 选择确定合适的大类型：数字、字符串、时间等
- 选择具体的类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同。
MySQL为了兼容性支持很多别名，例如 INTEGER、B00L，以及 NUMERIC它们都只是别名。这些别名可能令人不解，但不会影响性能。
4.1.1 整数类型
如果存储整数，可以使用这几种整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8，
16,24,32,64位存储空间。
整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。
但是在__运算__时，整数计算一般使用64位的 BIGINT整数，即使在32位环境也是如此。
MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。
4.1.2 实数类型
实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还大的整数。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数， DECIMAL类型支持精确计算。
因为CPU不支持对DECIMAL的直接计算，MySQL服务器自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。
浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。 MySQL将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，DECIMAL(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。MySQL5.0和更高版本中的DECIMAL类型允许最多65个数字。
有多种方法可以指定浮点列所需要的精度，这会使得MySQL悄悄选择不同的数据类型，或者在存储时对值进行取舍。这些精度定义是非标准的，所以对于浮点数我们建议只指定数据类型不指定精度。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。但是对于浮点计算，MySQL统一使用DOUBLE作为内部浮点计算的类型。
4.1.3 字符串类型
VARCHAR和CHAR是两种最主要的字符串类型。不幸的是，很难精确地解释这些值是怎么存储在磁盘和内存中的，因为这跟存储引擎的具体实现有关。
4.1.3.1 VARCHAR类型
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。VARCHAR需要使用1或2个额外字节记录字符串的长度：假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002个字节，因为需要2个字节存储长度信息。
VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。
下面这些情况下使用VARCHAR是合适的
- 字符串列的最大长度比平均长度大很多
- 列的更新很少，所以碎片不是问题
- 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储
此外
- MySQL在存储和检索时会保留末尾空格。
- InnoDB则更灵活，它可以把过长的
VARCHAR存储为BLOB
4.1.3.2 CHAR类型
CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格
CHAR适合存储的类型
- 所有值都接近同一个长度。例如，
CHAR非常适合存储密码的MD5值，因为这是一个定长的值。 - 对于经常变更的数据，
CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。 - 对于非常短的列，
CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。
与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。
二进制优势体现在大小写敏感上。并且二进制比较比字符比较简单很多，所以也就更快。
4.1.3.3 BLOB和TEXT类型
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
实际上，它们分别属于两组不同的数据类型家族：字符类型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT, LONGTEXT；对应的二进制类型是TINYBLOB,SMALLBLOB,BLOB, MEDTUMBLOB,LONGBLOB。BL0B是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。
与其他类型不同， MySQL把每个BLOB和TEXT值当作一个独立的对象处理，会使用专门的外部村储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。
BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。
MySQL不能将BLOB和TEXT列全部长度的宇符串进行索引，也不能使用这些索引消除排序。
4.1.3.4 使用枚举（ENUM）代替字符串类型
有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
此外要注意
- 如果使用数字作为
ENUM枚举常量，这种双重性很容易导致混乱，例如ENUM('1','2','3')。建议尽量避免这么做。 - 另外一个让人吃惊的地方是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的
- 枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用
ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意 - 在特定情况下，把
CHAR/VARCHAR列与枚举列进行关联可能会比直接关联CHAR/VARCHAR列更慢。
4.1.4 日期和时间类型
MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。MySQL能存储的最小时间粒度为秒。
MySQL提供两种相似的日期类型：DATETIME和TIMESTAMP。
4.1.4.1 DATETIME
这个类型能保存大范围的值，从1001年到999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。
4.1.4.2 TIMESTAMP
就像它的名字一样，TIMETAMP类型保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间因此它的范围比DATETIME小得多：只能表示从1970年到2038年。 MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。
TIMESTAMP显示的值依赖于时区。MySQL服务器、操作系统，以及客户端连接都有时区设置。如果在多个时区存储或访问数据，TIMESTAMP和DATETIME的行为将很不一样。前者提供的值与时区有关系，后者则保留文本表示的日期和时间。
TIMESTAMP也有DATETIME没有的特殊属性。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值， MySQL则把这个列的值设置为当前时间。
除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高
4.1.5 位数据类型
MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。
4.1.5.1 BIT
可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位，依此类推。BIT列的最大长度是64个位。
但是由于一些原因，我们认为应该谨慎使用BIT类型。对于大部分应用，最好避免使用这种类型。
如果想在一个bit的存储空间中存储一个true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值（NULL）或者长度为零的字符串（空字符串）。
4.1.5.2 SET
如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。这样就有效地利用了存储空间，并且MYSQL有像FIND_IN_SET()和FIELD()这样的函数，方便地在查询中使用。
4.1.6 选择标识符
为标识符(如表中的id列，就是常见的标识符)选择合适的数据类型非常重要。一般来说更有可能用标识列与其他值进行比较（例如，在关联操作中），或者通过标识列寻找其他列。标识列也可能在另外的表中作为外键使用，所以为标识列选择数据类型时，应该选择跟关联表中的对应列一样的类型。
下面是几种标识符的选择
- 整数类型：整数通常是标识列最好的选择，因为它们很快并且可以使用
AUTO_INCREMENT。 ENUM和SET类型：对于标识列来说，ENUM和SET类型通常是一个糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的- 字符串类型：如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。
4.1.7 特殊类型数据
IPV4地址实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。 MySQL提供INET_ATON()和INET_NI0A()函数在这两种表示方法之间转换。
4.2 MySQL schema 设计中的陷阱
下面列举一些在MySQL中不好的设计
4.2.1 太多的列
MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的变长行结构和InnoDB的行结构则总是需要转换。转换的代价依赖于列的数量。当我们研究一个CPU占用非常高的案例时，发现客户使用了非常宽的表（数千个字段），然而只有一小部分列会实际用到，这时转换的代价就非常高。
4.2.2 太多的关联
EAV的定义可以参看这里
所谓的“实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作。 MySQL限制了每个关联操作最多只能有61张表，但是EAV数据库需要许多自关联。我们见过不少EAV数据库最后超过了这个限制。
一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。
4.2.3 全能的枚举
注意防止过度使用枚举（ENUM）。下面是我们见过的一个例子
1 | CREATE TABLE ...( |
这种模式的schema设计非常凌乱。这么使用枚举值类型也许在任何支持枚举类型的数据库都是一个有问题的设计方案，这里应该用整数作为外键关联到字典表或者查找表来查找具体值。
4.2.4 变相的枚举
枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。这是一个例子：
1 | CREATE TABLE ...( |
如果这里真和假两种情况不会同时出现，那么毫无疑问应该使用枚举列代替集合列。
4.2.5 比NULL更差的不可能值
当确实需要表示未知值时也不要害怕使用NULL。在一些场景中，使用NULL可能会比某个神奇常数更好。从特定类型的值域中选择个不可能的值，例如用-1代表一个未知的整数，可能导致代码复杂很多，并容易引入bug，还可能会让事情变得一团糟。处理NULL确实不容易，但有时候会比它的替代方案更好。
4.3 范式与反范式
对于任何给定的数据通常都有很多种表示方法，从完全的范式化到完全的反范式化，以及两者的折中。在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。
4.3.1 范式的优点和缺点
范式化通常能够带来好处：
- 范式化的更新操作通常比反范式化要快。
- 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
- 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
- 很少有多余的数据意味着检索列表数据时更少需要
DISTINCT或者GROUP BY语句
但是范式化也有缺点
- 范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。
4.3.2 反范式的优点与缺点
反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。
但这会导致信息的冗余，同样的数据可能存在于很多地方。
4.3.3 混用范式化与反范式化
事实是，完全的范式化和完全的反范式化 schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的schema、缓存表，以及其他技巧。
4.4 计数器表
如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效，并且可以使用本节展示的一些更高级的技巧。
假设有一个计数器表，只有一行数据，记录网站的点击次数：
1 | CREATE TABLE hit_counter( |
网站的每次点击都会导致对计数器进行更新：
1 | UPDATE hit_counter SET cnt = cnt + 1; |
问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁。这会使得这些事务只能申行执行。要获得更高的并发更新性能，也可以将计数器保存在多行中，每次随机选择一行进行更新。这样做需要对计数器表进行如下修改
1 | CREATE TABLE hit_counter( |
然后预先在这张表增加10行数据。现在选择一个随机的槽（slot）进行更新
1 | UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100; |
4.5 更快地读，更慢地写
为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询的负担，也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧：虽然写操作变得更慢了，但更显著地提高了读操作的性能。