高性能MySQL读书笔记(4)--Schema与数据类型优化

第四章：Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石

Schema是什么？

在数据库中，schema是数据库的组织和结构。模式中包含了schema对象，可以是表(table)、列(column)、数据类型(data type)、视图(view)、存储过程(stored procedures)、关系(relationships)、主键(primary key)、**外键(**foreign key)等

4.1 选择优化的类型数据

几个简单的原则

1. 更小的通常最好：应该尽量使用可以正确存储数据的最小数据类型（例如只需要存0~200，tinyint unsigned更好），因为占用更少的磁盘，内存，CPU缓存。但是要确保没有低估存储的值的范围，后期要修改的话很麻烦
2. 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如：整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型更复杂
3. 尽量避免NULL：如果查询中包含可为NULL的列，对MySQL来说更难优化，因为NULL的列使得索引，索引统计和值都更复杂

4.1.1 整数类型

整数有可选的UNSIGNED属性，表示不允许负值，这大概可以使正数的上限提高一倍

有符号和无符号类型使用相同的存储空间，并具有相同的性能

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

4.1.2 实数类型

实数是带有小数部分的数字，它们不仅为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的正数。MySQL既支持精确类型，也支持不精确类型。

DECIMAL类型用于存储精确的小数，可以指定精度。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储，DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。

由于需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用，例如存储财务数据。

4.1.3 字符串类型

每个字符串可以定义自己的字符集和排序规则，这些东西很大程度上影响性能。

VARCHAR和CHAR是两种主要的字符串类型

VARCHAR

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型，它比定长类型更节省空间，因为它仅使用必要的空间。但是 需要使用1或者2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节。

VARCHAR节省了存储空间，但是update时可能会使行变得更长。

如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，不同的存储引擎的处理方式是不一样的。例如MyISAM会将行拆分成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

下面这些情况下使用VARCHAR是合适的：

1. 字符串列的最大长度比平均长度大很多，列的更新很少，所以碎片不是问题

CHAR

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有末尾空格，CHAR值会根据需要采用空格进行填充以方便比较

CHAR适合存储很短的字符串，或者所有值都接近同一个长度：对于经常变更的数据，CHAR比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR更有效率

与CHAR和VARCHAR类似的数据类型还有BINARY和VARBINARY，它们存储的是二进制字符串。二进制字符串存储的是字节码而不是字符，填充也不一样，MySQL填充BINARY采用的是\0（零字节）而不是空格，在检索时也不会去掉填充值。

慷慨是不明智的

使用VARCHAR(5)和VARCHAR(200)存储‘hello’的空间开销是一样的，那么使用更短的列有什么优势吗?

事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟心，再利用磁盘临时表进行排序时也同样糟糕，所以最好的策略是只分配真正需要的空间。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。

实际上，他们属于两组不同的数据类型家族：字符类型是TINYTEXT，SMALLTEXT，TEXT，MEDIUMTEXT，LONGTEXT，对应的二进制类型是TINYBLOB，SMALLBLOB，BLOB，MEDIUMBLOB，LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

当BLOB和TEXT太大时，InnoDB会使用专门的存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

磁盘临时表和文件排序

因为Memory引擎不支持BLOB和TEXT类型。所以，如果查询使用了BLOB或TEXT列并且需要使用隐式临时表，将不得不使用MyISAM磁盘临时表。

使用枚举代替字符串类型

有时候可以使用枚举列待敌常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。

枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE

转换列为枚举型之后，关联变得很快，并且可以让表的大小压缩成1/3

4.1.4 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。MySQL能存储的最小时间粒度为秒（MariaDB支持微秒级别的时间类型）

DATETIME

这个类型能保存大范围的值，从1001年到9999年，精度为秒。他把时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间

TIMESTAMP

保存了从1970年1月1日午夜以来的描述，只使用4个字节的存储空间，它的范围比DATETIME小得多，只能表示从1970年到2038年

TIMESTAMP现实的值依赖于时区

默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间

除了特殊行为之外，通常也应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

如果小存储比秒更小粒度的日期和时间值怎么办？MySQL目前没有提供合适的数据类型，但是可以使用自己的存储格式：可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分。

4.1.5 位数据类型

这些位数据类型从技术上说都是字符串类型

BIT

在MySQL5.0之前，BIT是TINYINT的同义词。可以使用BIT存储一个或多个true/false值，BIT(1)定义一个包含单个位的字段，以此类推。BIT列最大长度是64位。

当检索BIT(1)的值时，结果是一个包含二进制0或1值的字符串，这是相当让人费解的，所以我们认为因该谨慎使用BIT类型。对于大部分应用，最好避免使用这种类型。

如果想在一个bit的存储空间中存储true/false值，另一个方法是创建一个可以为空的CHAR(0)列。该列可以保存空值(NULL)或者长度为零的字符串。

SET

如果需要保存很多true/false值，可以考虑合并成一个SET数据类型。主要缺点时改变列的定义的代价较高，需要ALTER TABLE。一般来说也无法通过索引查找

4.1.6 选择标识符(identifier)

为标识列选择合适的数据类型非常重要。一般来说更有可能用标识列与其他值进行比较，或者通过标识列寻找其他列。

一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括想UNSIGNED这样的属性。混用不同数据类型可能导致性能问题和错误。

在可以满足值得范围的需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。

小技巧：

• 整数类型：整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT
• ENUM和SET类型：糟糕的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能没有问题的。不过还是要避免这样做
• 字符串类型：如果可能，尽量避免，因为他们很消耗空间，尤其在MyISAM表里使用字符串作为标识列时要特别小心，默认字符串使用压缩索引，这会导致查询最多有6倍的性能下降。对于MD5()，SHA1()或者UUID()产生的字符串，会任意分布在很大的空间内，会导致一些语句变得很慢

当心自动生成的schema

因为会导致严重的性能问题。有些程序存储任何东西都会使用很大的VARCHAR列，或者对需要在关联时比较的列使用不同的数据类型。

ORM对象关系映射系统是另一种常见的性能噩梦。一些ORM系统会存储任意类型的数据到任意类型的后端数据存储中。

4.1.7 特殊类型数据

某些类型的数据并不直接与内置类型一致，例如低于秒级精度的时间戳。

4.2 MySQL schema设计中的陷阱

• 太多的列：MySQL的存储引擎API工作室需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。MyISAM的定长行结构实际上与服务器层的行结构正好匹配，所以不需要转换。
• 太多的关联：所谓的“实体-属性-值”（EAV）设计模式是一个常见的糟糕设计模式，尤其是在MySQL下不能靠谱地工作。MySQL限制了每个关联操作最多只能有61张表，但EAV数据库需要许多自关联。如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联
• 全能的枚举：注意防止过度使用枚举
• 变相的枚举：枚举列允许在列中存储一组定义值中的单个值，集合(SET)列则允许在列中存储一组定义值中的一个或多个值
• 非此发明的NULL：即使存储一个事实上的空值到表中时，也不一定非得使用NULL。不过当确实需要表示未知值时也不要害怕使用NULL

4.3 范式和反范式

对于任何给定的数据通常都有很多种表示方法，从完全的范式化到完全的反范式化，以及两者的折中

4.3.1 范式的优点和缺点

范式的好处是：

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

缺点是通常需要一次或很多次的关联，这不仅代价昂贵，而且可能使一些索引策略无效

4.3.2 反范式的优点和缺点

反范式的schema因为所有数据都在一张表中，可以很好地避免关联

4.3.3 混用范式化和反范式化

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列

4.4 缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表。

我们用术语“缓存表”来表示存储那些可以比较简单地从schema其他表获取数据地表。而术语“汇总表”，则保存的是使用GROUP BY语句聚合数据的表。

缓存表对优化搜索和检索查询语句很有效，这些查询语句经常需要特殊的表和索引，跟普通的OLTP操作用的表有些区别

4.4.1 物化视图

许多数据库管理系统都提供了一个被称作物化视图的功能。物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。

视图组成部分：

• 变更数据抓取(CDC)功能，可以读取服务器的二进制日志并且解析相关行的变更
• 一系列可以帮助创建和管理视图的定义的存储过程
• 一些可以应用变更到数据库中的物化视图的工具

4.4.2 计数器表

如果在表中保存计数器，则在更新计数器时可能碰到并发问题。所以可以创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小而且快。

4.5 加快ALTER TABLE操作的速度

MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表中，然后再删除旧表。这样操作可能需要花费很长时间。一般而言，大部分ALTER TABLE操作将导致MySQL服务中断，常用的技巧有两种：

1. 一种先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
2. 另一种是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表

不是所有ALTER TABLE操作都会引起表重建，理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件上，所以可以直接修改这个文件而不需要改动表本身

4.5.1 只修改.frm文件

下面这些操作是有可能不需要重建表的：

• 移除一个列的AUTO_INCREMENT属性
• 增加，移除或更改ENUM和SET常量

基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件

4.5.2 快速创建MyISAM索引

一个常用的技巧是先禁用索引，载入数据，然后重新启用索引

这个技巧能够发挥作用，是因为构建索引的工作被延迟到数据完全载入之后，这个时候已经可以通过排序来构建索引了，并且使得索引书的碎片更少，更紧凑。

不幸的是，这个办法对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效