第9章 元数据分裂

我的妻子当Java及Oracle PL/SQL程序员已经好几年了。她提供了一个案例来展示什么样的数据库设计能使得工作变得更简单。

她的公司的销售部门使用的数据库中有一张叫做Customers的表，记录了和客户相关的信息，比如客户的业务类型，以及已经从这个客户那里获得了多少收入。

Metadata-Tribbles/intro/create-table.sql
CREATE TABLE Customers (
	customer_id NUMBER (9) PRIMARY KEY,
	contact_info VARCHAR (255),
	business_type VARCHAR (20),
	revenue NUMBER (9, 2)
);

但是销售部门需要按年来划分这些收入以便于跟踪每个客户的动态。他们决定增加一系列的列，每一列都按照年份来命名。

Metadata-Tribbles/intro/alter-table.sql
ALTER TABLE Customers ADD (revenue2002 NUMBER(9, 2));
ALTER TABLE Customers ADD (revenue2003 NUMBER(9, 2));
ALTER TABLE Customers ADD (revenue2004 NUMBER(9, 2));

接着他们输入不完整的数据，只包含那些他们有兴趣跟踪的客户。大多数的行中，他们将这些收入字段置空。开发人员怀疑他们会不会在这些几乎用不到的列中存些别的数据？

每一年，他们都往这张表中增加一个新列。有一个专门的数据库管理员负责维护Oracle的表空间。因此，每一年，他们都需要开一系列的会议，协调数据迁移和重新组织表空间，并且增加新列。最终，他们浪费了一堆时间和金钱。

随着数据量的增长，数据库的查询性能也会随之下降。即使查询结果可能只有很少的几千行，遍历表中积累的数据也可能使得整个查询的性能变得极差。即使使用了索引，但随着数据的增长，索引的作用变得非常有限。

本章的目标就是要优化数据库的结构来提升查询的性能以及支持表的平滑扩展。

在“星际迷航”中，tribbles是一种被当做宠物饲养的小巧的、毛茸茸的动物。tribbles最开始非常地吸引人，但很快它们就暴露出本性，开始了无节制的繁殖，然后管理泛滥的tribbles成了船上的一个严重问题。

你该把这些tribbles放哪儿？谁该为这些tribbles负责？要将所有的tribbles都收集起来要花多长时间？最终，柯克船长发现他的飞船及船员们无法正常工作，他不得不下令将“赶走tribbles”作为头等大事来处理。

根据经验，我们知道查询一张表时，其性能只和这张表中数据的条数相关，越少的记录，查询速度越快。于是我们推导出一个常见错误的结论：无论要做什么，我们必须让每张表存储的记录尽可能少。这就导致了本章的反模式的两种表现形式。

但是你不能不劳而获：为了要达成减少每张表记录数的目的，你不得不创建一些有很多列的表，或者创建很多很多表。但在这两个方案中，你会发现随着数据量的增长，会有越来越多的表或者列，因为新的数据迫使你创建新的Schema对象。

混淆元数据和数据

请注意，通过将年份追加在基本表名之后，我们其实是将数据和元数据标识合并在了一起。

这和我们早先在EAV和多态关联反模式中看到的混合数据和元数据的方式正好相反。在那些案例中，我们将元数据标识（列名和表名）当做字符串存储。

在多列属性和元数据分裂模式中，我们将数据的值存储在列名或者表名中。如果你使用任何这些反模式，你只会得到更多的问题。

要将数据拆分到不同的表中，需要一个规则来定义哪些数据属于哪些表。比如，可以根据date_reported中的年份进行拆分：

Metadata-Tribbles/anti/create-tables.sql
CREATE TABLE Bugs_2008 (...);
CREATE TABLE Bugs_2009 (...);
CREATE TABLE Bugs_2010 (...);

由于要将数据添加进数据库，于是根据要添加的数据的值选择合适的表就成了你的责任：

Metadata-Tribbles/anti/insert.sql
INSERT INTO Bugs_2010 (..., date_reported, ...) VALUES (..., '2010-06-01' , ...);

让我们快进到2011年1月1日。你的程序在添加新的Bug报告时不断的报错，因为你忘记添加一张叫做Bugs_2011的新表。

Metadata-Tribbles/anti/insert.sql
INSERT INTO Bugs_2011 (..., date_reported, ...) VALUES (..., '2011-02-20' , ...);

这意味着新的数据可能会需要新的元数据对象。这在SQL的设计中并不是常见的数据与元数据的关系。

假设你的老板打算计算这一年中Bug的数量，但他得到的数字并不正确。检查了相关数据和程序之后，你发现有一部分2010年的Bug被误写入了Bugs_2009这张表。如下的查询语句应该每次都返回空结果，如果不是的话，那么就有麻烦了：

Metadata-Tribbles/anti/data-integrity.sql
SELECT * FROM Bugs_2009 WHERE date_reported NOT BETWEEN '2009-01-01' AND '2009-12-31' ;

没有任何办法自动地对数据和相关表名做限制，但可以在每张表中都声明一个CHECK的约束：

Metadata-Tribbles/anti/check-constraint.sql
CREATE TABLE Bugs_2009 (   -- other columns
   date_reported DATE CHECK (EXTRACT(YEAR FROM date_reported) = 2009)
);
CREATE TABLE Bugs_2010 (   -- other columns
   date_reported DATE CHECK (EXTRACT(YEAR FROM date_reported) = 2010)
);

注意当你创建Bugs_2011时要记得修改CHECK约束里的值。如果你犯错了，就可能创建出一张拒绝接受正确值的表。

某一天，你的客户支持部分析师想要改变Bug的日期。在数据库中存储的日期是2010-01-03，但顾客实际是在一周以前，即2009-12-27，使用传真报告的错误。你可以使用一个简单的UPDATE语句来修改这个值：

Metadata-Tribbles/anti/anomaly.sql
UPDATE Bugs_2010 SET date_reported = '2009-12-27' WHERE bug_id = 1234;

但这个修正使得Bugs_2010表中的某一条记录变成了无效记录。你需要先从这张表中移除这条记录然后插入到另一张表中，在少数情况下，一个简单的UPDATE语句就会造成这样的异常状况。

Metadata-Tribbles/anti/synchronize.sql
INSERT INTO Bugs_2009 (bug_id, date_reported, ...)
   SELECT bug_id, date_reported, ...
   FROM Bugs_2010
   WHERE bug_id = 1234;
DELETE FROM Bugs_2010 WHERE bug_id = 1234;

需要确保所有被分割出来的表中的主键都是唯一的。如果你需要从一张表中移动一条记录到另一张表中，需要保证被移动记录的主键值不会和目标表中的主键记录冲突。

如果使用一个支持序列化对象的数据库，那么可以使用一个简单的序列来确保主键值在所有的分割表中都是唯一的。对于那些只支持单表ID唯一的数据库产品来说，实现这样的功能变得很不优雅。你不得不定义一张额外的表来存储产品主键的值：

Metadata-Tribbles/anti/id-generator.sql
CREATE TABLE BugsIdGenerator (bug_id SERIAL PRIMARY KEY);
INSERT INTO BugsIdGenerator (bug_id) VALUES (DEFAULT);
ROLLBACK;
INSERT INTO Bugs_2010 (bug_id, . . .)
   VALUES (LAST_INSERT_ID(), . . .);

不可避免地，你的老板肯定会需要查询多张表中的数据。比如，他可能会要求查询所有的Bug总数，不管是哪一年报告的。你可以使用UNION将所有分割表联合起来得到一个重构过的Bug集合并将其作为一个衍生表进行查询：

Metadata-Tribbles/anti/union.sql
SELECT b.status, COUNT(*) AS count_per_status FROM (
   SELECT * FROM Bugs_2008 UNION SELECT * FROM Bugs_2009 UNION SELECT * FROM Bugs_2010
) AS b GROUP BY b.status;

年复一年，你创建了越来越多的表，比如Bugs_2011，你需要不断地更新程序代码来引入这些新创建的表。

你的老板要求你在表中增加一列用以记录解决每个Bug所用的时间。

Metadata-Tribbles/anti/alter-table.sql
ALTER TABLE Bugs_2010 ADD COLUMN hours NUMERIC(9,2);

如果将表进行了拆分，那么这个新列仅仅被应用到你选择的那张表上。其他所有的表都不包含这个新列。

如果使用前一段描述的那个UNION查询所有的分割表，就会碰到一个新问题：当多张表具有相同的列时才能使用UNION。如果多张表的列不完全相同，你就必须指明所有表都同时拥有的列，而不可以再使用“*”通配符。

如果一个关联表，比如Comments引用了Bugs，这个关联表就不能声明一个外键了。一个外键必须指定单个表，但在这个案例中父表被拆分成很多个表。

Metadata-Tribbles/anti/foreign-key.sql
CREATE TABLE Comments (
	comment_id SERIAL PRIMARY KEY,
	bug_id BIGINT UNSIGNED NOT NULL,
	FOREIGN KEY (bug_id) REFERENCES Bugs_ ???? (bug_id)
);

分割表即使作为一张关联表而不是父表，同样可能引起一些问题。比如，Bugs.reported_by引用了Accounts表。如果你想要查询一个给定的人所报告的所有Bug，不管哪一年的，你需要使用像下面的这个查询：

Metadata-Tribbles/anti/join-union.sql
SELECT * FROM Accounts a JOIN (
     SELECT * FROM Bugs_2008
     UNION ALL
     SELECT * FROM Bugs_2009
     UNION ALL
     SELECT * FROM Bugs_2010
   ) t ON (a.account_id = t.reported_by)

列也可能根据元数据分裂。你可以创建一个含有很多列的表，这些列按照它们的类别扩展，就像我们在本章最开始看到的那个故事一样。

我们的Bug数据库中可能碰到的另一个事例是：有一张表记录了项目指标的概要信息，其中的每一列存储了一个小计。具体来说，在如下这张表中，增加一个bugs_fixed_2011列只是时间问题：

Metadata-Tribbles/anti/multi-column.sql
CREATE TABLE ProjectHistory (
	bugs_fixed_2008 INT,
	bugs_fixed_2009 INT,
	bugs_fixed_2010 INT
);

如下的描述可能就是元数据分裂反模式在你的数据库中繁衍生长的暗示。

手动分割表的一个合理使用场景是归档数据——将历史数据从日常使用的数据中移除。通常在过期数据的查询变得非常稀少的情况下，才会进行如此的操作。

如果你没有同时查询当前数据和历史数据的需求，将老数据从当前活动的表转移到其他地方是很合适的操作。

将数据归档到和当前表结构相兼容的新表中，既能支持偶尔做数据分析时的查询，同时也能让日常数据查询变得非常高效。

WordPress.com使用的分区数据库设计

在2009年的MySQL大会上，我和Barry Abrahamson一起吃饭，他是WordPress.com的数据库架构师，而WordPress.com是一个很流行的博客服务。

Barry说他开始做博客主机服务时，将所有客户的数据存在一个数据库中，毕竟每个博客站点的数据量并不是很大。在当时，单个数据库便于管理是个很好的理由。

网站最初建立的时期，这样的设计工作得很好，但很快就发展成大规模的数据库操作。现在他们在300台数据库服务器上存储7百万博客数据。每台服务器为一部分用户服务。

Barry添加新的服务器时，对存储着所有用户博客信息的单一数据库进行拆分是非常痛苦的事情。通过将每个用户的数据分开存储到单独的数据库中，Barry发现将一个用户的数据从一台服务器转移到另一台服务器变得异常简单。由于用户总是在不断地流动，有些用户的博客流量非常高，而另一些用户的博客则相对比较冷清，Barry所负责的不断调整服务器之间的负载均衡工作就变得很重要。

备份并恢复一个中等规模的数据库比操作一个存储着TB级数据的数据库要方便得多。比如，如果一个用户打电话来说由于输入了错误的数据，他们的数据现在变得一团糟，Barry该怎么恢复这个用户的数据，如果所有用户的数据都存于同一个巨大的数据库中？

尽管将数据对象模型化并将整个对象中的所有东西映射到一个单独的数据库中的做法没有错，但合理地将大小超过临界值的数据库拆分开能简化数据库管理的工作。

当一张表的数据量变得非常巨大时，除了手动拆分这张表，还有更好的办法来提升查询性能。这些方法就包括了水平分区、垂直分区以及使用关联表。

你可以使用一种称为水平分区或者分片的数据库特性来分割大数据量的表，同时又不用担心那些分割表所带来的缺陷。你仅需要定义一些规则来拆分一张逻辑表，数据库会为你管理余下的所有事情。物理上来说，表的确是被拆分了，但你依旧可以像查询单一表那样执行SQL查询语句。

定义每个表拆分的方式是非常灵活的。比如，使用MySQL5.1所支持的分区特性，你可以在CREATE TABLE语句中将分区规则作为可选参数。

Metadata-Tribbles/soln/horiz-partition.sql
CREATE TABLE Bugs (
   bug_id SERIAL PRIMARY KEY,   -- other columns
   date_reported DATE
) PARTITION BY HASH (YEAR(date_reported))
   PARTITIONS 4;

上例中分割数据库的方式和这章最开始讲到的方法类似，根据date_reported列里的年份对数据进行拆分。然而，这么做的优势在于，相比于手动拆分表，你永远不用担心数据会放入错误的分割表中，即使date_reported列的值更新了。而且，你不必引用所有的分割表就能对Bugs表进行整体的查询操作。

实际存储数据的物理表在本例中被固定设置为4张。当记录的年份跨度超过4年，某一个分区将用来存储多于一年的数据。年份跨度不断增长，这样的现象也会不断重演。你不必添加新的分区，除非分区里的数据量变得非常巨大，让你觉得需要重新分区。

分区在SQL标准中并没有定义，因此每个不同的数据库实现这一功能的方式都是非标准的。对应的术语、语法和明确的特性定义在不同的数据库中有非常大的区别。然而，某些形式的分区如今已经被很大一部分数据库所支持了。

鉴于水平分区是根据行来对表进行拆分的，垂直分区就是根据列来对表进行拆分。当某些列非常庞大或者很少使用的时候，对表进行按列拆分会比较有优势。

BLOB类型和TEXT类型的列的大小是可变的，可能非常大。为了提高存储和查询的性能，有些数据库自动地将这些类型的列和表中其他的列分开进行存储。如果你进行一个不包含BLOB或者TEXT类型的查询，就可以更高效地获取其他的列。但如果使用一个通配符“*”来进行查询，数据库会返回这张表中所包含的所有列，包括那些类型为BLOB或者TEXT的列。

比如说，在缺陷数据库中，我们可能会在Products表中为每个单独的产品存储一份安装文件。这种文件都是自解压的，在Windows上的后缀通常为 .exe，在Mac上后缀为 .dmg。这种文件通常都很大，但BLOB类型的列可以存储庞大的二进制数据。

从逻辑上讲，安装文件是Products表的一个属性，但在绝大多数针对这张表的查询中，安装程序通常是不需要的。如果你有使用通配符“*”进行查询的习惯，那么将如此大的文件存储在Products表中，而且又不经常使用，很容易就会在查询时遗漏这一点，从而造成不必要的性能问题。

正确的做法是将BLOB列存在另一张表中，和Products表分离但又与其相关联。让这张BLOB表的主键同时作为一个指向Products表的外键，用以确保每个产品最多有一条与之对应的安装包记录。

Metadata-Tribbles/soln/vert-partition.sql
CREATE TABLE ProductInstallers (   product_id      BIGINT UNSIGNED PRIMARY KEY,   installer_image BLOB,   FOREIGN KEY (product_id) REFERENCES Products(product_id));

之前的例子虽然比较极端，但它确实展示了将一些列从某一张表中分离出来的优势。比如，在MySQL的MyISAM存储引擎中，对一个所有行的大小都是固定的表是最高效的。VARCHAR是一个可变长数据类型，因此，只要表中出现一个这样类型的字段，那就无法享受固定长度的查询速度优势。如果你将所有可变长字段都存储在分离的表中，对主表的查询效率就能有所提高（即使只有一点点）。

Metadata-Tribbles/soln/separate-fixed-length.sql
CREATE TABLE Bugs (
   bug_id SERIAL PRIMARY KEY, -- fixed length data type
   summary CHAR(80),           -- fixed length data type
   date_reported DATE,               -- fixed length data
   type reported_by BIGINT UNSIGNED,    -- fixed length data type
   FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
);
CREATE TABLE BugDescriptions (
   bug_id BIGINT UNSIGNED PRIMARY KEY,
   description  VARCHAR(1000),      -- variable length data type
   resolution VARCHAR(1000)       -- variable length data type
   FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

和我们在 第8章 多列属性 中使用的解决方案类似，解决元数据分裂的改进方案就是创建关联表。

Metadata-Tribbles/soln/create-history-table.sql
CREATE TABLE ProjectHistory (
	project_id BIGINT,
	YEAR SMALLINT,
	bugs_fixed INT,
	PRIMARY KEY (project_id, YEAR),
	FOREIGN KEY (project_id) REFERENCES Projects (project_id)
);

使用每行一个项目、每一列记录一年的Bug修复数量，还不如使用多行、仅用一列记录修复的Bug数量。如果你这样定义表，就不需要为随后的年份增加新列，随着时间的增长，你可以为每个项目存储任意数量的记录。