高性能MySQL第4章读书笔记

这一章的内容对于开发来说尤为重要，我们开发时经常要设计数据库的表，那么如何使用合适的数据库结构就非常重要了。

选择优化的数据类型

MySQL支持非常多的数据类型，下面有几个原则帮助我们做出更好的选择。

• 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型占用更少磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更短。小的空间可以帮助我们减少IO的压力。但是一定要确保没有低估需要存储的值的范围
• 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符串操作代价更低，因为字符集和排序规则比整型更复杂。例如我们应该使用MySQL内建的类型而不是字符串来存储时间，用过使用整型存储IP地址
• 尽量避免NULL：如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当呗索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引变成可变大小的索引。但是，通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优）没有必要首先在现有schema中查找并修改这种情况，除非确定这会导致问题，不过如果这一列是索引列，就应该尽量避免设计成可NULL的列。InnoDB使用单独的位来存储NULL值，所以对于稀疏数据有很好的空间效率。但这一点并不适用MyISAM。

原则过后则是步骤，在为列选择数据类型时，有两步可以作为参考：

1. 需要确定合适的大类型：数字、字符串、时间等。
2. 选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间不同。

DATETIME和TIMESAMP列都可以存储相同类型的时间和日期，都可以精确到秒，然而后者只使用前者一半的存储空间。不过后者在转换成时间时需要考虑到时区的问题（在多时区应用中需要注意），前者就不太存在这个问题了。

然后**MySQL为了兼容性支持很多别名，例如INTEGER,BOOL,NUMERIC.他们都是别名，如果使用SHOW CREATE TABLE检查，会发现MySQL报告的是基本类型，而不是别名。

整数类型

整数类型	占用空间（位）
TINYINT	8
SMALLINT	16
MEDIUMINT	24
INT	32
BIGINT	64

整数类型有可选的UNSIGNED属性，你的选择决定MySQL是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用64位的BIGINT整数，即使是在32位的环境也是如此。（除了一些聚合函数，他们使用DECIMAL或DOUBLE进行计算）

MySQL可以为整数类型类型指定宽度，例如INT（11），对大多数应用是没有意义的，它不会限制值得合法范围，只是规定了MySQL得交互工具用来显示字符的个数。对于存储和计算，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数部分的数字。也可以用DECIMAL存储比BIGINT还大的整数。MySQL既支持精确类型，也支持不精确类型。这里主要讨论FLOAT，DOUBLE和DECIMAL类型。

• FLOAT和DOUBLE：支持使用标准的浮点运算进行近似计算。可以指定精度，但是制定了好像并没有卵用。
• DECIMAL：CPU不支持对DECIMAL的直接计算，所以MySQL服务器自身实现了它的高精度计算。所以跟慢一些。DECIMAL可以指定精度，指定小数点前后所允许的最大位数。5.0以后的版本将数字打包保存到一个二进制字符串中。DECIMAL(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点占1个字节。目前5.0和更高版本中的DECIMAL类型允许最多65个数字。

所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，最后转换一下位数。这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

字符串类型

VARCHAR和CHAR是两种最主要的字符串类型。不过它们的具体实现怎么储存主要和存储引擎的具体实现有关。存储引擎存储的方式在内存中和在磁盘中可能不一样，所以MySQL从存储引擎读出的值可能需要转换为另一种存储格式。

VARCHAR

VARCHAR类型用于存储可变长字符串，它比定长类型更节省空间，因为它仅使用必要的空间。如果MySQL表使用ROW_FORMAT=FIXED创建的化，每一行都会使用定长存储，这会很浪费空间。

如果列的最大长度小于等于255字节，则使用1个字节额外记录字符串的长度。否则使用2个。例VARCHAR(10)需要使用11个字节，VARCHAR(1000)需要1002个。

VARCHAR节省了存储空间，对性能是有帮助的。但是由于行是变长的，在UPDATE时可能使行变得比原来更长，如果页内没有更多的空间可以存储。MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。InnoDB可以把过长的VARCHAR存储为BLOB。

什么时候使用是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片很少；使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR

CHAR类型是定长的，存储的时候，MySQL会删除所有的末尾空格。它很适合存储很短的字符串，或者所有值都接近同一个长度。例如非常适合存储密码的MD5值，因为它是定长的。对于经常变更的数据，CHAR页比VARCHAR更好，因为定长不容易产生碎片。或者我们用CHAR(1)来存储只有Y和N的值。

其他

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。存储的是字节码而不是字符，填充时使用\0(零字节)而不是空格。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较，因此会更快。

BLOB和TEXT类型

这两个 都是为了存储很大的数据而设计的字符串数据类型。分别采用二进制和字符方式存储。MySQL把它们都当作一个独立的对象处理。当值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值需要1-4个字节存储一个指针。

MySQL不能将它们全部长度的字符串进行索引，也不能使用这些索引消除排序。它只对每个列的最前max_sort_length字节排序。

BLOB和TEXT的区别是前者没有排序规则或字符集，TEXT类型有字符集和排序规则。

使用枚举代替字符串类型

有时候可以使用枚举代替常用的字符串类型。MySQL存储枚举时非常紧凑，会根据列表值得数量压缩到一个或者两个字节中，内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，不过可以在查询中使用FIELD()函数显式指定排序顺序，但这会导致MySQL无法利用索引消除排序。枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。因此会改变的字符串不建议使用枚举。

由于枚举保存为整数，所以必须进行查找才能转换为字符串，所以在特定情况下，把CHAR/VARCHAR列与枚举列进行关联可能会比直接关联CHAR/VARCHAR列更慢。不过就算如此，转换成枚举会减小存储的大小，可以缩小表的存储空间，某些情况下，即使出现ENUM和VARCHAR进行关联的情况，这也是值得的。因为转换后主键变小了，减少主键大小会使非主键索引也变得更小。

日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，最小时间粒度为秒。大部分时间类型都没有替代品，主要是对比DATETIME和TIMESTAMP，它们都可以用来表示年月日时分秒。

• DATETIME能保存大范围的值，从1001年-9999年，精度为秒。把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节。
• TIMESTAMP保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，只使用4个字节存储只能表示从1970-2038年的时间。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，则默认设置这个列的值为当前时间。TIMESTAMP列默认为NOT NULL。

我们应该尽量使用TIMESTAMP，因为空间效率更高。如果要存储比秒还小的时间，可以使用BIGINT来存储，或者使用DOUBLE存储秒之后的小数部分。除此之外，不建议把时间戳保存为整数值。

位数据类型

MySQL5.0之前，BIT和TINYINT是同义词。但是之后这是一个特征完全不同的数据类型。

可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义1个位，BIT(2)定义2个位。BIT列的最大长度是64个位。BIT的行为因存储引擎不同而不同。MyISAM会打包存储所有的BIT列，所以17个单独的BIT列只需要17位存储，只用3个字节就能存完。不过其他存储引起，例如Memory和InnoDB，为每个BIT列使用一个足够存储最小整数类型来存放，所以不能节省存储空间。

MySQL把BIT当作字符串类型，而不是数字类型。BIT(1)，结果是一个包含二进制0或1值的字符串，而不是ASCII码的“0”或“1”的字符（实际也是整数）。然而，在数字上下文的场景中检索时，结果将是位字符串转换成的数字。如果需要和另外的值比较结果，一定要记得这一点。例如：存储一个值b(00111001)—二进制等于57到BIT(8)的列并且检索它，得到的内容是字符码为57的字符串。也就是ASCII码中57对应的9。但是在数字上下文中得到的数字是57.这是相当令人费解的，所以我们认为应该谨慎使用BIT类型。

如果保存多个t/n（true或false）值，可以考虑合并到一个SET数据类型，并且可以使用FIND_IN_SET()和FIELD()这样的函数来找。它的缺点是改变列的定义代价较高：需要ALTER TABLE。

或者就是在整数列上进行按位操作：把8个位包装到一个TINYINT中，并且按位操作来使用，这样的好处是不用ALTER TABLE，缺点是非常难以理解。

选择标识符

这里我不是很理解啥意思，所以我的理解暂定为是主键或者外键的类型。所以这里分析得是不同数据类型当作主键或者外键时的影响。

• 整数类型：通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT
• ENUM和SET类型：通常是一个糟糕的选择，ENUM和SET适合存储固定信息，例如产品类型和人的性别。
• 字符串类型：如果可能，应该避免使用字符串类型作为标识列。

为什么字符串不是一个好的选择

1. 因为很消耗空间，并且通常比数字类型慢。MyISAM默认对字符串使用压缩索引，这会导致查询慢得多。
2. 完全随机的字符串也要注意，MD5(),SHA1(),UUID()产生的字符串会任意分布在很大的空间内，导致INSERT（插入值会随机写入到索引的不同位置，这会导致页分裂、硬盘随机访问，聚簇存储引擎产生聚簇索引碎片）和SELECT语句变得很慢（逻辑相邻的行却会分布在磁盘和内存的不同地方）。
3. 随机值导致缓存对所有类型的查询语句效果都很差，缓存赖于工作的访问局部性原理失效。如果整个数据集都同样容易访问，那么缓存任何一部分特定数据到内存都没有好处。

如果存储UUID，则应该移除”-“符号；或者更好的做法是，用UNHEX()转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时通过HEX()函数来格式化十六进制格式。UUID生成的值比SHA1()更好一丢丢，前者虽然分布也不均匀，但还是有顺序。例如存储IP地址时，不应该使用字符串，它们实际上是32位无符号整数。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

MySQL表设计数据结构中的陷阱

有一些问题是MySQL的实现机制导致的。

• 太多的列： MySQL的存储引擎API工作需要在服务器和存储引擎之间通过行缓冲格式拷贝数据，然后在服务层解码成各个列。定长行结构与服务器层正好匹配，所以不需要转换。变长行结构和InnoDB行结果需要转换。转换的代价依赖于列的数量，所以不应该创建太多列，假如创建1000列，却只有几行被用到，这样是不行的。
• 太多的关联：实体-属性-值设计模式是一个常见的糟糕设计模式，MySQL限制了每个关联操作最多61张表，不过如果希望查询执行得快速且并发性好，单个查询最好在12张表以内。
• 全能的枚举：方式过度使用枚举：例如存(“0”,”1”,”2”,…..”31”)这样得枚举
• 变相的枚举：使用集合来存储单个true和false是没有意义的，而且会导致混乱。因为true和false不会同时出现。
• 非此发明的NULL：虽然建议不要使用NULL，但是也不要走极端。有时候需要存储NULL还是得存储NULL，使用一个没有意义的默认值会引发更大的问题。例如用-1代表未知的整数，使用伪造的时间(‘0000-00-00 00:00:00’)来当作默认的时间。可以配置MySQL的SQL——MODE来禁止不可能的日期。

范式和反范式

什么是范式，在我之前的文章中有提到：数据库三大范式

对于设计数据库来说，完全的范式和完全的反范式都是不可取的。

范式的优点和缺点

• 范式化的更新操作通常比反范式更快
• 当数据范式化时，就只有很少或者没有重复数据，所以只需要修改更少数据
• 范式化的表通常更小，更好地放在内存中，执行操作会更快。
• 很少有多余地数据意味着检索列表数据时更少需要DISTINCT 或 GROUP BY语句

缺点：范式化地设计通常需要关联，这不但代价昂贵，也可能使一些索引策略无效，范式化可能将列放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

反范式的优点和缺点

• 不需要关联表，就算需要全表扫描也比关联操作要快，因为这样避免了随机I/O
• 使用更有效的索引策略

缺点：更新数据时要更多多个数据

缓存表和汇总表

混用范式和反范式是日常操作，有时候提升性能的最好方法是在同一张表中保存冗余的数据。

假如网站需要计算24小时内的发送消息，在一个非常繁忙的网站不可能维护一个实时精确的计数器。那么作为替代方法，可以每小时生成一张汇总表，最后加上开始阶段和结束阶段内的计数。

缓冲表需要优化搜索和检索语句。这些查询语句需要特殊的表和索引结构。可能会需要很多不同的索引组合来加速各种类型的查询。不过就目前来看，服务器的缓存可以使用Redis，不一定需要MySQL

加快ALTER TABLE操作的速度

MySQL执行大部分修改表结构操作的方法是：

1. 使用新的结构创建一个空表
2. 从旧表中查出所有数据插入新表
3. 删除旧表

上述操作，当表中数据量很大的情况下，如果内存不足，而且还有很多索引的情况下，这个操作需要花费数小时甚至数天才能完成。

那么怎么加速操作肯定也是从优化这些步骤出发。

• 影子拷贝：用要求的表结构创建一张和源无关的新表，然后通过重命名和删表操作交换两张表。
• 使用ALTER COLUMN操作来改变列的默认值。这个语句会直接修改.frm文件而不涉及表数据。
• 只修改.frm文件（非官方支持，需要非常谨慎且一定要做好备份文件的工作）
• 快速创建索引：先关闭索引，在大量插入数据之后重新启用索引。