Schema与数据类型优化

发表于 更新于 分类于
这章关注MySQL数据库的设计，主要介绍MySQL数据库设计与其他关系型数据库管理系统的区别。

选择优化的数据类型

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

  • 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，占用更少的磁盘、内存和CPU缓存，处理时需要的CPU周期更少。但是要在保证没有低估需要存储的值的范围的前提下，如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。
  • 简单就好：简单数据类型的操作通常需要更少的CPU周期。有两个例子：一个应该使用MySQL内建的类型而不是字符串来存储日期和时间；另外一个是应该用整型存储IP地址。
  • 尽量避免NULL：很多表都包含可唯NULL的列，这是因为NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。因为如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都复杂。可为NULL的列会使用更多的存储空间，在MySQL中也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外字节，在MyISAM里甚至还可能会导致固定大小的索引变成可变大小的索引。

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

例如DATETIME和TIMESTAMP都可以存储时间和日期，精确到秒，然而TIMESTAMP只是用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。但是另一方面，TIMESTAMP允许的时间范围要小得多。

整数类型

有两种类型的数字：整数和实数，如果存储整数，可以使用这几种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT，分别使用8、16、24、32、64位存储空间。

整数类型又可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT的存储范围是-128 ~ 127，而TINYINT UNSIGNED可以存储的范围是0 ~ 255。

有符号和无符号类型使用相同的存储空间，并且具有相同的性能。整数计算一般使用64位的BIGINT整数，即使在32位环境下也是如此。

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

实数类型

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

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。

DECIMAL类型用于存储精确的小数，支持精确计算。因为CPU不支持对DECIMAL的直接计算，所以MySQL服务器自身实现了DECIMAL的高精度计算，相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数，这会影响列的空间消耗。MySQL中将数字打包到一个二进制字符串中（每4个字节存9个数字），例如：DECIMAL（18，9）小数点两边各存储9个数字，一共使用9个字节：小数点前的数字同4个字节，小数点后的数字用4个字节，小数点本身占1个字节。DECIMAL类型允许最多65个数字，因为DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE类型。

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

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL，例如存储财务数据。在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

VARCHAR和CHAR两种最主要的字符串类型，存储的具体方式与存储引擎的实现有关，所以下面描述的内容，假设存储引擎是InnoDB或者MyISAM。

VARCHAR类型

VARCHAR用于存储可变长字符串，是最常见的字符串数据类型。它更加节省空间，因为它仅使用必要的空间（即越短的字符串使用越少的空间）。但是如果表使用ROW_FORMAT = FIXED创建的话每一行都会使用定长存储，这样很浪费空间。

VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

由于行是变长的，在UPDATE时可能使行变得更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

下面这些情况下使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

MySQL在存储和检索时会保留末尾空格，但是在4.1或者老版本中则会剔除末尾空格。InnoDB则更为灵活，它可以把过长的VARCHAR存储为BLOB。

CHAR类型

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除所有的末尾空格。

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

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串，二进制字符串存储的是字节码而不是字符，填充也不一样：MySQL填充BINARY采用的是“\0”而不是空格，在检索时也不会去掉填充。二进制的优势并不仅仅体现在大小写敏感上，MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。

在使用VARCHAR时，更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作是会特别糟糕，所以最好的策略是只分配真正需要的空间。

BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT和LONGTEXT；二进制类型是TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB是SMALLBLOB的同义词，TEXT是SMALLTEXT的同义词。

MySQL把每个BLOB和TEXT值当作一个独立的对象处理，存储引擎在存储时通常会做特殊处理，当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后再外部存储区域存储实际的值。

BLOB没有排序规则或字符集，而TEXT类型有字符集和排序规则。MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前面max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分的字符，则可以减小max_sort_length的配置，或者使用ORDER BY SUBSTRING(column,length)。

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

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

有时可以使用枚举代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。

MySQL在内部会将每个值在列表中的位置保存为整数，所以实际存储为整数，而不是字符串。所以使用数字作为ENUM枚举常量，这种双重性很容易导致混乱。而且枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。因此一种绕过这种限制的方式是按照需要的顺序来定义枚举列，另外也可以在查询中使用FIELD()函数显示地指定排序顺序，但是这会导致MySQL无法利用索引消除排序。

枚举最不好的地方就是：字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE，除非能接受只在列表末尾添加元素。

因此一个通用的设计实践：在查找表时采用整数主键而避免采用基于字符串的值进行关联。ENUM可以让表的大小缩小，因此它可以更加节省内存。

日期和时间类型

MySQL能存储的最小时间粒度为秒，但是MySQL也可以使用微秒级的粒度进行临时运算：可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

MySQL提供两种相似的日期类型：DATETIME和TIMESTAMP。

  • DATETIME：这个类型能保存大范围的值，从1001到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值。
  • TIMESTAMP：TIMESTAMP类型保存了从1970年1月1日以来的秒数，他和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，因此它的范围比DATETIME小得多：只能从1970年到2038年。**MySQL按照DATETIME的方式格式化TIMESTAMP的值，但是这仅仅是显示格式上的区别，TIMESTAMP的存储格式在各个版本都是一样的。TIMESTAMP显式的值也依赖于时区。默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间，再插入一行记录时，MySQL默认也会更新第一个TIMESTAMP列的值。TIMESTAMP列默认为NOT NULL。**

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

位数据类型

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

  • BIT：可以使用BIT列在一列中存储一个或多个true/false值。BIT(1)定义一个包含单个位的字段，BIT(2)存出2个位，以此类推。BIT列的最大长度位64个位。BIT在MyISAM中会打包存储所有的BIT列，所以更加节省空间；但是在InnoDB中，为每个BIT列使用一个足够存储的最小整数类型来存放，所以不能节省空间。MySQL把BIT当作字符串类型，而不是数字类型。所以对于大部分应用，最好避免使用这种类型。
  • SET：它在MySQL内部以一系列打包的位的集合来表的，这样就有效的利用了存储空间。可以使用一个整数包装一系列的位。

选择标识符(identifier)

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

  • 整数类型：整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。
  • ENUM和SET：对于标识列来说，ENUM和SET类型通常是一个糟糕的选择，所以大部分情况下都要避免这么做。
  • 字符串类型：应该尽量避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。

特殊数据类型

人们通常使用VARCHAR（15）列来存储IP地址，然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们容易阅读，所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

Schema设计中的陷阱

有一些问题是由MySQL的实现机制导致的，所以这里讨论一下MySQL的schema的设计上的问题。

  • 太多的列：MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后再服务器曾将缓冲解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价非常高，转换的代价依赖于列的数量。如果一个非常宽的表，然而只有一小部分列会实际用到，这时转换的代价就非常高。
  • 太多的关联：关联操作会导致MySQL解析和优化查询的代价非常高。MySQL限制了每个关联操作最多只能有61张表，如果希望查询执行得快速且并发性能好，单个查询最好在12个表以内做关联。
  • 全能的枚举：注意防止过度使用枚举（ENUM）。
  • 变相的枚举：枚举列允许在列中存储一组定义值中的单个值，集合列则允许在列中存储一组定义值中的一个或多个值，有时候这可能比较容易导致混乱。
  • 非此发明的NULL：之前说过建议尽可能地考虑替代NULL的方案，例如可以使用0、某个特殊值或空字符串作为替代。但是遵循这个原则也不要走极端，当确实需要表示未知值时也不要害怕使用NULL。值得一提的是，MySQL会在索引中存储NULL值，而Oracle不会。

范式和反范式

范式的优点和缺点

优点：

  • 范式化的更新操作通常比反范式化要快。
  • 当数据较好地范式化时，就只有很少或者没有重复度的数据，所以只需要修改更少的数据。
  • 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
  • 很少有多余的数据意味着检索列表数据会更少需要DISTINCT或者GROUP BY语句，而在非范式化的结构中必须使用DISTINCT或者GROUP BY才能获得一份唯一的列表。

缺点：

  • 范式化的设计表的缺点是通常需要关联，这不但代价昂贵，也可能使一些索引策略无效。

反范式的优点和缺点

优点：

  • 反范式化的schema因为所有数据都在一张表中，可以更好地放在内存里，所以执行操作会更快。
  • 如果不需要关联表，则对大部分查询最差的情况————即使表没有索引————是全表扫描。可以更好地放在内存里，所以执行操作会更快。
  • 单独的表也能使用更有效的索引策略。

缺点见范式化的优点部分。

混用范式化和反范式化

完全的范式化和完全的反范式化schema都是实验室才有的东西，在真实世界中很少会这么极端地使用。在实际应用中经常需要混用。

最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL5.0和更新版本中，可以使用触发器更新缓存值。

缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据，然而有时也需要创建一张完全独立的汇总表或缓存表（特别是为满足检索到需求时）。

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

实时计算统计值是很昂贵的操作，因为要么需要扫描表中大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE操作有影响，所以一般不希望创建这样的索引。而缓存表则相反，其对优化搜索和检索查询语句很有效。

一个有用的技巧是对缓存表使用不同的存储引擎。例如，如果主表使用InnoDB，用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文搜索。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引，这会更高效。

当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用，这就需要通过使用“影子表”来实现。“影子表”指的是一张在真实的表背后创建的表，当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。而且如果新表有问题，则可以很容易地进行快速回滚操作。

物化视图

物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。在第七章会详细探讨物化方法。

计数器表

如果在应用中保存计数器，则在更新计数器时可能碰到并发问题。这时可以创建一张独立的表存储计时器，这样可使计数器表小并且快，使用独立的表可以避免查询缓存失效。

然而问题在于，对于任何想要更新这一行的事务来说，这条记录都有一个全局的互斥锁。这会使这些事务只能串行执行。要获得更高的并发性能，也可以将计数器保存在多行中，每次随机选择一行进行更新操作。

更快的读，更慢的写

为了提升读查询的速度，经常会需要建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表。这些方法会增加写查询到负担，但是虽然写操作变慢了，但更显著地提高了读操作的性能。

加快ALTER TABLE操作的速度

MySQL执行大部分修改表结构操作的方法是用新的结构常见一个空表，从旧表中查出所有数据插入新表，然后删除旧表。问题是这样的操作可能需要很长时间。

一般而言，大部分ALTER TABLE操作将导致MySQL服务中断，但是能使用两种技巧进行改善：

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

不是所有的ALTER TABLE操作都会引起表重建。例如修改一个列的默认值：

1
2
ALTER TABLE film
MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

这个语句会拷贝整张表到一张新表，但是甚至列的类型、大小和可否为NULL属性都没改变。因为所有的MODIFY COLUMN 操作都会导致表重建。另一种方法是通过ALTER COLUMN操作来改变列的默认值：

1
2
ALTER TABLE film
ALTER COLUMN rental_duration SET DEFAULT 5;

这个语句会直接修改.frm文件而不涉及表数据。所以这个操作是非常快的。

只修改.frm文件

从上面的例子可以看出修改表的.frm文件是很快的。下面这些操作是有可能不需要重建表的：

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

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

快速创建MyISAM索引

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

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

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

因此在InnoDB中有一个类似的技巧：先删除所有非唯一索引，然后增加新的列，最后重新创建删除掉的索引。Percona Server可以自动完成这些操作步骤。

总结

总之来说，尽可能保持任何东西小而简单总是好的。MySQL喜欢简单，需要使用数据库的人也会同样喜欢简单的原则：

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