高性能MySQL(2)——Schema与数据类型的优化

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计 schema,这往往需要权衡各种因素。

一、选择优化的数据类型

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

更小的通常更好更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
简单就好简单数据类型的操作通常需要更少的CPU周期。
尽量避免NULL 如果査询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在 MySQL里也需要特殊处理。

在为列选择数据类型时：

第一步需要确定合适的大类型：数字、字符串、时间等；

第二步是选择具体类型。很多MySQL的数据类型可以存储相同类型的数据，只是存储的长度和范围不一样、允许的精度不同，或者需要的物理空间不同（相同大类型的不同子类型数据有时也有一些特殊的行为和属性）。

1.1、整数类型

有两种类型的数字：整数和实数。

整数类型：

TINYINT 1字节、【-128，127】、【0，255】
SMALLINT 2字节、【-32768，32767】、【0，65535】
MEDIUMINT 3字节、【-2147483648，2147483647】、【0，4294967295】
INT 4字节、【-2147483648，2147483647】、【0，4294967295】
BIGINT 8字节、【-263，263-1】、【0，264-1】

整数类型有可选的UNSIGNED属性，表示不允许为负数，大致可以使得正数的上限提高一倍。

有符号和无符号具有相同的存储空间和性能，根据实际情况选择合适的类型。

Tips:整数计算一般使用 64位的BIGINT整数，即使在32位环境也是如此，不同的数据可以决定的只有MySQL是怎么在内存和磁盘中保存数据的。例如INT(11),对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具(例如MySQL命令行客户端) 用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的

2.2、实数类型

实数是带有小数部分的数字。

FLOAT 单精度，4字节
DOUBLE 双精度，8字节
DECIMAL 存储精确的小数

FLOAT和DOUBLE使用标准的浮点运算进行近似运算，如果需要知道浮点运算是怎么计算的，则需要研究所使用的平台的浮点数的具体实现。

DECIMAL支持精确计算，但CPU不支持对DECIMAL的直接计算，MySQL自己实现了DECIMAL的高精度计算，所以DECIMAL在性能上要弱一些。

Tips:DECIMAL需要额外的空间和计算消耗，当数据量比较大时，可以考虑使用BITINT来代替，将存储的数据根据小数的位数乘以相应的倍数即可。这样可以解决浮点类型计算不准确，DECIMAL计算开销太大的问题。

2.3、字符串类型

VARCHAR

VARCHAR用于存储可变长的字符，比定长更节省空间，越短的字符占用空间越少。但有一种例外：当表使用“ROW_FORMAT=FIXED”创建时，每一行都会使用定长存储，浪费空间。

VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集，一个VARCHAR(IO)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息

VARCHAR节省了存储空间，对性能也有帮助。但是由于行是变长的，所以UPDATE时可能比原来更长，这就需要MySQL为其额外再分配存储空间，导致UPDATE时开销比定长类型要大。

CHAR

CHAR类型是定长的，MySQL会根据定义的长度去分配存储空间，所以不会有VARCHAR进行UPDATE时的额外开销。 CHAR类型存储时，会自动去除末尾的空格，这一点需要注意。

CHAR适合存储短的，长度固定的字符，例如MD5值，UUID等…

由于UPDATE时没有额外的开销，对于经常变更的数据，CHAR的性能也比VARCHAR更好。

BLOB和TEXT类型

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

它们分别属于不同的数据类型家族：

字符类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。二进制类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

与其他类型不同，MySQL会将BLOB和TEXT当做单独的对象处理。

当值太大时，MySQL会使用专门的存储区域来存储数据，行内使用1~4字节来存储一个指针，指向对应的大文本字符。

BLOB和TEXT的不同之处在于：由于BLOB是二进制，所以没有字符集和排序规则，但是TEXT有。

即使TEXT有排序规则，MySQL对其进行排序时，也不会对整个文本进行排序，只会对前max_sort_length字节进行排序，可以通过修改max_sort_length进行配置。

MySQL不能将BLOB和TEXT全部长度的字符进行索引。

使用枚举（enum）代替字符串类型

枚举可以把一些不重复的字符串存储成一个预定义的集合，MySQL在存储枚举时非常紧凑，会根据列表值压缩到1到2个字节中。

MySQL在内部会将列中的枚举值保存为整数，在.frm文件中保存一个“数字->字符串”的映射关系，通过数字快速的查找到具体的枚举值。

枚举字段排序时，并不会按照给定的字符串排序，而是根据内部的整数排序，所以建议列举枚举时按照预想的顺序给出。

日期和时间类型

MySQL提供了多种类型来保存时间和日期，例如：YEAR、DATE、DATETIME。 MySQL能存储的最小时间粒度为秒（有的第三方存储引擎支持微秒）。

MySQL提供了两种相似的事件类型：DATETIME和TIMESTAMP。

DATETIME

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

TIMESTAMP

保存了从1970年1月1日凌晨以来的秒数，和UNIX时间戳相同。使用4个字节来保存，比DATETIME节省空间，具有更高的性能。但是范围比DATETIME要小得多，只能存储1970年到2038年。

TIMESTAMP显示的值依赖于时区，MySQL服务器，操作系统以及客户端的连接都有时区的设置。

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

如果需要存储比秒更小粒度的时间，MySQL目前没有提供合适的数据类型，可以考虑使用BIGINT来存储微秒级别的时间戳。

2.4、位数据类型

可以使用BIT列存储一个或多个true/false值，BIT(1)包含单个位的字段，最多可包含64个位。

MySQL将BIT当做字符串类型，而不是数字类型。当查询BIT(1)时，结果是一个包含二进制0或1的字符串，而不是ASCII码中的“0”或“1”。

BIT列进行比较时，MySQL会将位字符串转换为十进制数字进行比较。例如：‘111’ = 7。

对于大部分应用，最好慎用BIT类型。

2.5、选择标识符

为标识列选择合适的数据类型十分重要。

一般来说标识列很可能用来在不同的表之间进行比较，甚至作为外键来使用。合适的数据类型可以提升系统的整体性能，减少数据比较的系统开销。

一旦选定了类型，一定要确保关联表中也是相同的数据类型，混用不同的数据类型会带来很多麻烦。例如：将字符串与整形做比较，会导致严重的性能问题。

一般来说，在没有特殊要求的情况下，整型通常是标识列最好的选择，因为它很快，而且可以自动递增。

如果可以的话，应该尽量避免使用字符串当做标识列，它很消耗空间，而且比整型慢。

很多人喜欢用随机的字符串来作为标识列，例如：UUID。由于生成的字符没有规律，会导致INSERT和SELECT语句变得很慢：

插入的值会随机的写入到索引的不同位置，使得INSERT更慢。这会导致页分裂，磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
SELECT语句变慢，因为逻辑上相邻的数据会分布在磁盘的不同地方。
随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问“局部性原理”失效。缓存无法命中，加载到内存中也是徒劳。

Tips：如果需要使用UUID当做标识列，那么应该移除“-”这种没有意义的字符。最好的解决方案是：用UNHEX()将UUID转换为16位的二进制数据，没有字符集，没有排序，而且占用更少的磁盘空间，可以很好的提升性能。

2.6、特殊类型数据

有些类型的数据并不直接与MySQL的内置类型一致，微秒型的时间戳就是个例子。

还有例如：IPv4地址，应该使用无符号的整数来保存，而非字符串。 MySQL内置的函数INET_ATON和INET_NTOA可以很好的转换。

二、Schema设计中的陷阱

虽然有一些普遍的好或坏的设计原则，但也有一些问题是由MySQL的实现机制导致的，这意味着有可能犯一些只在MySQL下发生的特定错误。

了解讨论设计MySQL的 schema的问题。这也许会帮助我们避免这些错误，并且选择在MySQL特定实现下工作得更好的替代方案。

太多的列 MySQL存储引擎工作时，需要在服务器层和存储引擎层之间做行缓冲格式拷贝数据，然后在服务器层之间将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。如果单张表的列太多，就应该要考虑做表的拆分。
太多的关联 MySQL限制了每个关联最多只能61张表，一个粗略的经验法则：如果希望査询执行得快速且并发性好,单个查询最好在12张表以内做关联。
全能的枚举注意防止过度使用枚举(ENUM)，在MySQL 5.0以及更早的版本中ALTER TABLE是一种阻塞操作；即使在5.1和更新版本中，如果不是在列表的末尾增加值也会一样需要ALTER TABLE 。
Not Invent Here 的 NULL 我们之前写了避免使用NULL的好处，并且建议尽可能地考虑替代方案。即使需要存储一个事实上的“空值”到表中时，也不一定非得使用NULLO也许可以使用0、某个特殊值，或者空字符串作为代替。但是遵循这个原则也不要走极端。当确实需要表示未知值时也不要害怕使用NULL在一些场景中，使用NULL可能会比某个神奇常数更好。

三、范式和反范式

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

设计关系型数据库时，需要遵从不同的规范，设计合理的关系型数据库，不同的规范被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余约小。在关系型数据库中有六中范式：第一范式（1NF），第二范式（2NF），第三范式（3NF），BCNF，第四范式（4NF），第五范式(5NF)。一般数据库设计到第三范式就行了

这里简单介绍一下三大范式：

第一范式确保数据表中每列（字段）的原子性。如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
第二范式在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。
第三范式在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关。通过第三张表（中间表）来建立用户表和角色表之间的关系，同时又符合范式化的原则，就可以称为第三范式。
反范式化反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。

3.1、范式的优点和缺点

当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处：

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

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的査询语句在符合范式的 schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

3.2、反范式的优点和缺点

反范式的优点：

可以很好地避免关联。
如果不需要关联表，对大部分查询最差情况，即没有使用索引，全表扫描。当数据币内存大时这可能比关联要快很多，这样避免了随机I/O。

在真实环境中很少会极端地使用范式化或者反范式化的schema。而是可能使用部分范式化的schema、缓存表、以及其它技巧。最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定的列。

3.3、混用范式化和反范式化

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？

事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。在实际应用中经常需要混用，可能使用部分范式化的 schema、缓存表，以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在 MySQL 5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。

四、加快ALTER TABLE操作的速度

MySQL 对于大表的ALTER操作是非常慢的，因为 MySQL 对于ALTER操作的的方法是创建一个新结构的表，然后将旧结构表中的数据复制过去，最后将旧表删除。如此操作对于海量数据的表来说花费的时间是非常长的。

一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL 操作时有用的技巧，但这是针对一些特殊的场景而言的。对常见的场景，能使用的技巧只有两种：

一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；

另外一种技巧是影子拷贝，影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

4.1、只修改.frm文件

如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重建表。

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

移除（不是增加）一个列的AUTO_INCREMENT属性。
增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字串值。

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

创建一张有相同结构的空表，并进行所需要的修改（例如增加ENUM常量）。
执行FLUSH TABLES WITH READ L0CKo这将会关闭所有正在使用的表，并且禁止任何表被打开。
交换**.frm**文件.
执行UNLOCK TABLES来释放第2步的读锁。

4.2、快速创建MylSAM索引

为了高效地载入数据到MylSAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引，这个技巧能够发挥作用，是因为构建索引的工作被延迟到数据完全载入以后，这个时候已经可以通过排序来构建索引了。这样做会快很多，并且使得索引树注”的碎片更少、更紧凑。

不幸的是，这个办法对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效。 MylSAM会在内存中构造唯一索引，并且为载入的每一行检査唯一性。一旦索引的大小<33 超过了有效内存大小，载入操作就会变得越来越慢。

下面是操作步骤：

用需要的表结构创建一张表，但是不包括索引。注：如果使用的是LOAD DATA FILE,并且要载入的表是空的，MylSAM也可以通过排序来构造索引。
载入数据到表中以构建.M阳文件。
按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的斤％和.心以文件。
获取读锁并刷新表。
重命名第二张表的为“和文件，让MySQL认为是第一张表的文件。
释放读锁。
使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。

五、总结

良好的schema设计原则是普遍适用的，但MySQL有它自己的实现细节要注意。概括来说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单的原则：

尽量避免过度设计，例如会导致极其复杂査询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。
使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
尽量使用整型定义标识列。
避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BITO

范式是好的，但是反范式（大多数情况下意味着重复数据）有时也是必需的，并且能带来好处。