mysql scheme优化_《高性能MySQL》学习笔记四Schema与数据类型优化

很有用的一章，将为接下来两个章节铺垫，这三个章节中将 讨论逻辑设计、物理设计和查询执行，以及它们之间的相互作用。

选择优化的数据类型

更小的通常更好：应该尽量使用可以正确存储数据的最小数据类型(例如只需要存0-200，使用tinyint unsigned更好)。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。同时要确保没有低估需要存储的值得范围，因为要在schema中多个地方添加数据类型的范围是一个非常耗时和痛苦的操作。

简单就好：简单数据类型的操作通常需要更少的CPU周期，例如整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型比较更复杂。要尽量使用MySQL内建的类型(data、time、datatime)而不是字符串来存储时间和日期或者应该用整型存储IP地址。

尽量避免NULL：NULL是列的默认属性，通常最好指定列为NOT NULL，除非真的需要存储NULL。一般查询中包含可NULL的列，对MySQL来说更难优化，因为可谓NULL的列使得索引、索引统计和值比较都变得更复杂，而且可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当NULL列被索引时每个索引记录需要一个额外的字节，所以如果计划在列上建索引，就应该尽量避免设计成为可为NULL的列。值得一提的是，对于InnoDB来说，它使用单独的位来存储NULL值，这对于稀疏数据(多NULL少NOT NULL)有很好的空间效率。

在为列选择数据类型时，第一步需要确定合适的大类型：数字、字符串、时间等，然后下一步是选择具体类型。很多MySQL数据类型可以存储相同类型的数据，但是其存储的长度和范围不一样、允许的精度不同、需要的物理空间也不同。例如，DATATIME和TIMESTAMP都可以存储时间和日期，精确到秒，然而TIMESTAMP就只有DATATIME一半的存储空间，并且会根据时区变化，具有特殊自动更新能力，但是其时间范围要小很多。

整数类型

有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用8、16、24、32、64位存储空间，整型类型有可选UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。

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

实数类型

带有小数部分的数字。MySQL既支持精确类型也支持不精确类型。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算会损失精度；DECIAML类型用于存储精确的小数，使用MySQL服务器自身实现高精度的计算，因此相对来说运算较慢。

浮点和DECIMAL以下都可以指定精度，对于DECIMAL列，可以指定小数点前后所允许的最大位数，这会影响列的空间消耗。因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。

字符串类型

支持多种字符串类型，每个类型还有很多变种。

VARCHAR：用于存储可变长的字符串，是最常见的字符串数据类型。它比定长类型更节省空间，但如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行就是定长存储，这会很浪费空间。VARCHAR要使用1或2个额外字节记录字符串的长度，如果列的最大长度不大于255字节就只使用1个字节表示，否则使用2个字节。由于行是变长的，在UPDATE时当行变得比原来更长时就会导致更多的额外工作(MyISAM将行拆分成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内，其他引擎可能从不在原数据位置更新)。VARCHAR(5)和VARCAHR(200)存储‘hello’的空间开销是一样的，但是其内存消耗却相差很大，更长的列会消耗更多的内存，故最好是只分配需要的空间。

CHAR：是定长的，MySQL通过定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格(如果存储“str  ”就会删除后面的空格)，为方便比较CHAR值会根据需要采用空格进行填充。CHAR适合存储很短的字符串，或者所有值都接近同一个长度，例如就很适合存储MD5值。CHAR值不容易产生碎片，如果使用CHAR(1)存储Y和N的值就只需要一个字节，但是VARCHAR(1)却需要两个字节因为还有一个字节记录长度。

BINARY和VARBINARY：存储的是二进制字符串，与常规的字符串相似，但二进制字符串存储的是字节码而不是字节，填充也不一样，MySQL填充BINARY采用的是\0(零字节)而不是空格而且检索时也不会去掉填充值。所以如果需要检索时保持值不变，则需要特别小心BINARY这个类型。

BLOB和TEXT类型：存储很大的数据而设计的字符串数据类型，分表采用二进制和字符方式存储。MySQL会把每个BLOB和TEXT值当作一个独立的对象处理，存储引擎在存储时通常会做特殊处理，当值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，这时每个值在行内都需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。BLOB和TEXT的区别在于BLOB类型粗才能的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。MySQL对这两种类型的排序不是比较所有的字符串而是对每个列的最前max_sort_length字节进行排序。

使用枚举(ENUM)代替字符串类型：可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中，形成映射关系。但是其排序规则是按照键的大小排序的，这点不是太好。最不好的地方是字符串列表是固定的，添加或者删除其他不同的字符串必须使用DDL语句，对于那些可能改变的字符串并不是一个好办法。除非你非常确定只有这几个值，否则还不如使用TINYINT来的好，(别告诉我性别只有男女，要是你的BOSS后面要你加个保密，加个人妖什么的。。)

日期和时间类型

DATATIME：能保存大范围的值，从1001年到9999年，精确到秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储空间。

TIMESTAMP：保存了时间戳，范围是1970到2038年，精确到秒，该类型与时区相关。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则会设置这个列为当前时间，当更新记录时也会默认更新第一个TIMESTAMP列的值。

除了特殊行为外，尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。TIMESTAMP的行为规则比较复杂，并且在不同的MySQL版本里会变动，所以使用SHOW CREATE TABLE命令检查输出是有必要的。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据，所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

BIT：可以存储一个或多个true/false值。不同的存储类型的行为不同，MyISAM会打包存储所有的BIT列，所以17个单独的BIT列只使用3个字节就能存储；其他类型Memory和InnoDB，为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省存储空间。MySQL把BIT当作字符串类型而不是数字类型，在检索BIT的值时结果应该是一个二进制字符串，如果在数字上下文中可以得到数字(a+0),可根据二进制输出对应的ASCII(ascii(a))之类的。

SET：如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的，这样就有效的利用了存储空间，和ENUM一样它改变列的定义的代价要使用DDL语句，代价高，一般来说也无法在SET列上通过索引检索。

在整数列上进行按位操作：一种代替SET的方法是使用一个整数包装一系列的位，比如可以把8个位包装到一个TINYINT中，并且可以按位操作来使用。这样可以不使用DDL语句，但是更难理解。

选择标识符

为标识列(identifier column，又称自增长列)选择合适的数据类型非常重要。一般来说更可能用标识列与其他值进行比较，或者通过标识列寻找其他列，或者作为关联列。所以应该选择跟关联表中的对应列一样的类型。

选择标识列的类型时不仅仅需要考虑存储类型，还需要考虑MySQL对这种类型怎么执行计算和比较。一旦选定了一种类型，要确保在所有的关联表中都使用同样的类型，混用的话可能导致性能问题，或者在比较操作时造成很难发现的错误。

推荐使用整数类型来作为标识列的类型，因为它们很快而且可自增。使用ENUM和SET类型或者使用字符串类型都是一个糟糕的选择，前者只能固定值导致限定多，或者非常消耗空间且速度慢。

特殊类型数据

某些类型的数据并不直接与内置类型一致。比如低于秒级精度的时间戳，或者使用VARCHAR来存储IPv4地址等等都是一些习惯导致的性能问题。

MySQL schema设计中的陷阱

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

太多的列：MySQL的存储引擎API工作时需要在服务层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价非常高，所以应该避免写太多的列。(提取热点的列，分表保存不怎么用到的列)

太多的关联：所谓的“实体-属性-值”(EAV)设计模式是一个常见的糟糕设计模式，尤其在MySQL下不能靠谱地工作，因为它会关联很多个表，而MySQL的上限是61张表。但如果希望查询执行速度快并且并发性好，单个查询最好在12个表内做关联(当然越少越好，一般4个关联就很多了，如果有需要最好在业务端拆分成多个sql来查询比较好)。

全能的枚举：防止过度使用枚举ENUM。(别用枚举了。。)

变相的枚举：改用ENUM时不要用SET，ENUM在列中只允许存储单个值，SET可以存储多个值，要清楚两个类型的优劣。

NULL问题：之前说过要避免使用NULL，建议尽可能考虑替代方案，一些“空值”实际上可以用0或者空字符串代替。但也不能走极端，当确实需要表示未知值时也不要害怕用NULL，在一些场景下可能使用NULL会更好，如果设置特殊值表示空值可能带来更大的麻烦时就不要吝啬使用NULL。

范式和反范式

对于任何给定的数据通常有很多表示方法，从完全范式化到完全反范式化，以及两者的折中。在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在很多地方。

当为性能问题而寻求帮助时经常会被建议对schema进行范式化设计，尤其是写密集的场景。范式化带来的好处：范式化的更新操作通常比反范式化要快

当数据较好地范式化时，就只有很少或者没有重复数据，因此只要修改更少的数据

范式化的表通常小，可以更好地放在内存里，所以执行操作会更快。

很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY

但是也有很明显的缺点就是要关联，一些复杂查询语句可能要关联多个表，这不但代价昂贵而且可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

反范式化的schema因为所有数据都在一张表中可以很好避免关联，如果不需要关联，那么当数据比内存大时，即使使用全表扫描都比关联要快的多。因为避免了随机I/O，而全表扫描基本上是顺序I/O。

既然都有优缺点，在实际应用中通常是混用模式，可能使用部分范式化的schema、缓存表、以及其他技巧。最常见的反范式化的方法是复制冗余或者缓存。

缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索需要时)。如果能容许少量的脏数据，这是个非常好的方法。

一般缓存表存一些可以比较简单地从一些关联表中获取的数据(单次获取会比较慢)，而汇总表一般保存的是使用GROUP BY语句聚合数据的表。

有时候需要很多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时需要创建一张只包含主表中部分列的缓存表，一个有效的技巧是对缓存表使用不同的存储引擎，例如主表使用InnoDB，那么用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文搜索。

物化视图

物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种策略刷新和更新。MySQL并不原生支持物化视图，而是使用Flexviews外部实现物化视图。对比传统的维护汇总表和缓存表，Flexviews通过提取对源表的更改，可以增量地重新计算物化视图的内容。这意味着不需要通过查询原始数据来更新视图。这样计算增量数据比从源表中读取数据的效率要高很多。

计数器表

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意，这样可以使计数器表小且快。使用独立的表可以帮助避免查询缓存失效，并且可以使用一些高级技巧，比如计数的时候可以多增加几行计数，或者隔一段时间增加一行计数，这样update就不会因为锁行(表)而导致并发效率低。

为了提高读的速度，有时候相应的会增加写的负担，也需要额外的维护任务。这些都是设计高性能数据库时，要承担的代价。

加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样的操作可能需要花费很长的时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。有时候需要花数个小时甚至数天才能完成。

一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。一般情况下回使用两种技巧：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另一种就是“影子拷贝”，影子拷贝是用要求的表结构创建一张和源表无关的新表，然后通过重命名和三标操作交换两张表。

不是所有的ALTER TABLE操作都会引起表重建，有两种方法可以改变或者删除一个列的默认值，一种是跳过创建新表的步骤，直接在.frm文件中修改列的默认值，即直接修改文件而不需要改动表本身；另一种是通过ALTER COLUMN的操作来该表列的默认值，这个语句会直接修改.frm文件而不涉及表数据。

只修改.frm文件

直接修改.frm文件是很快的，但是MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让MySQL做一些其他类型的修改二不用重建表。但是这些技巧需要承担风险的，所以在执行之前先确保做了数据备份。

这些操作时可能不需要重建表的：1、移除(不是增加)一个列的AUTO_INCREMENT属性。2、增加、移除或者更改ENUM和SET常量，如果移除的是一件有行数据用到其值得常量，查询将会返回一个空字串值。

基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，像这样：1、创建一张有相同结构的空表，并进行所需要的修改(例如增加ENUM常量)。

2、执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。

3、交换.frm文件

4、执行UNLOCK TABLES来释放第2步的读锁。

快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，一个常用的技巧是先禁用索引、载入数据，然后重新启用索引。

mysql>altertabletest.load_datadisablekeys;

–load thedata

mysql>altertabletest.loadenablekeys;

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

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

InnoDB有一个类似的技巧，这依赖于InnoDB的快速在线索引创建功能。先删除所有的非唯一索引，然后增加新的列，最后重新创建删除掉的索引。也可以使用前面说的ALTER TABLE的骇客方法来加速这个操作，但需要多做一些工作并承担一定风险。这对从备份中载入数据是很有用的，例如，当已经知道所有数据都是有效并且没有必要做唯一性检查时就可以这么来操作。

下面的步骤用于快速创建MyISAM表索引(记得备份数据)：1、用需要的表结构创建一张表，但不包括索引。

2、载入数据到表中以创建.MYD文件。

3、按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm和.MYI文件。

4、获取读锁并刷新表。

5、重命名第二张表的.frm和.MYI文件，让MySQL人为是第一张表的文件。

6、释放读锁。

7、使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。

这个操作步骤对大表来说会快很多。

总结

良好的schema设计原则是普遍适用的，但MySQL有自己的实现细节要注意。概况来说，尽可能保持任何东西小且简单总是好的。