目标：支持可扩展性

    随着数据量增长，数据库的查询性能也会随之下降，哪怕查询结果只是很少的几千行，遍历表中积累的数据也可能使整个查询性能变得极差，即使使用了索引，随着数据量增大，索引的作用也变得非常有限。
    本章目标就是要优化数据库的结构来提升查询性能以及表的平滑扩展。

反模式：克隆表与克隆列

    根据经验，我们知道查询一张表的性能和这张表的数据条数有关，越少的记录，查询速度更快。于是我们会想办法将每张表存储的记录尽可能少。这就导致本章反模式的两种表现。

  1. 将一张很长的表拆分成多张较小的表，使用表中某一个特定的数据字段来给这些拆分出来的表命名。（比如按条数拆分表）
  2. 将一个列拆分成多个子列，使用别的列中的不同值给拆分出来的列命名。（比如按年份拆分列）

    为了减少每张表的记录数，你不得不创建一些有很多列的表，或者创建很多很多表。但在这两个方案中，你会发现数据量的增长，会有越来越多的表或者列，迫使你创建新的schema对象。

不断产生新表

    如果你按照年份来查分表

1
2
3
create table bug_2008(...);
create table bug_2009(...);
create table bug_2010(...);

    在保存数据时，你要操心，什么时候改插入哪张表。

1
insert into bug_2010(..., date_report, ...) values(..., '2010-01-01', ...);

    在快进入到2010-01-01时，你的程序还有可能报错，原因是你忘记创建一张叫做bug_2010的表。

管理数据完整性

    你统计2010年的bug数量时，发现有一部分数据出现了在bug_2009的表中，导致统计数据不正确。这时你需要在每一张表中都声明一个check约束。

1
2
3
4
create table bug_2009(
	...
	date_report date check (extract(year from date_report) = 2009)
);
同步数据

    某一天，你需要修改bug的日期，在数据库中存储的日期是2010-01-03，但顾客实际是在一周前（2009-12-27），使用传真报告的错误。你必须先修改bug_2010，再插入bug_2009，再删除bug_2010.

1
2
3
4
5
6
7
update bug_2010 set date_report = '2009-12-27' where bug_id = 1234;

insert into bug_2009(..., date_report) values(
	select * from bug_2010 where bug_id = 1234
);

delete from bug where bug_id = 1234;

    使用以上sql，你还可能会遇到，在bug_2010中bug_id已经存在bug_2009中，此时你该怎么选择。

确保唯一性

    如果同步数据中bug_id重复，难倒你了，你可能会想到创建另外一张表，用来生成bug_id

1
2
3
4
5
6
7
8
9
create table bug_id_generator(
	bug_id serial primary key
);

insert into bug_id_generator(bug_id) value(default);
rollback;

insert into bug_2010(bug_id, ....)
values(last_insert_id(), ....);
跨表查询

    不可避免，你一定有需求要查询所有的数据。此时你只能选择union 将所有的数据联合起来。

1
2
3
4
5
6
7
select count(1) from (
	select * from bug_2008
		union all
	select * from bug_2009
		union all
	select * from bug_2010
) as temp;
同步元数据

    如果你要添加一列作为每个bug解决的时间，你需要同步之前创建的所有表，不然你在使用union语句会报错，或者你不使用通配符(*)，而是一个一个列出你要查询的列。

管理引用完整性

    使用拆分表，你不能建立一个外键约束，是因为sql是不支持的。

1
2
3
4
create table comment (
	bug_id bigint unsiged not null,
	foreign key(bug_id) references bug_???(bug_id)
);

    同时关联查询也是如此，如果你要统计某一个用户提交的所有bug数

1
2
3
4
5
6
7
8
select * from account a 
join (
	select * from bug_2008
	union all
	select * from bug_2009
	union all
	select * from bug_2010
) t on a.account_id = t.report_id

如何识别反模式

当出现以下情况时，可能是反模式

  1. 我们需要每……创建一张表或者列？
  2. 数据库支持的最大数量的表或者列是多少？
  3. 发现程序添加记录失败了：因为王佳伟新的一年添加新表了
  4. 如何查询很多张表？每张表的列都是一样的。
    【如果你需要查询很多结构一样的表，就应该讲数据全部存储在一个表中，使用一个额外的属性列来分组数据】
  5. 如何将表名称作为一个表里传递？在查询是需要根据辩分动态的生成这些表名称。

合理使用反模式

    手动分隔表的一个合理使用场景是“归档数据”—–将历史数据从日常使用的数据库中移除。通常在过期数据的查询变的非常稀少的情况下，才会进行如此的操作。
    如果你没有同时查询当前数据和历史数据的需求，将老数据从当前活动的表转移到其他地方是很合适的操作。
    将数据归档到与当前表结构相兼容的新表中，既能支持偶尔做数据分析时的查询，同时能让日常数据查询边的非常高效。

解决方案：分区并标准化

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

水平分区

    水平分区（或者叫分片）：根据数据行来对表进行拆分。同时不用担心那些分隔表所带来的缺陷。仅需要定义一些规则来拆分一张逻辑表，数据库会为你管理余下所有的工作。物理上来说，表的确的被拆分了，但你依旧可以向查询单一表那样执行SQL查询语句。

1
2
3
4
5
6
7
-- mysql 5.1
create table bug(
	bug_id serial primary key,
	...
	date_report date
) partition by hash(year(date_report)) 
partitions 4;

    以上分割数据库的方式和这章最开始讲到的方式类似，根据date_report列的年份对数据拆分，但是它不用人工维护。实际存储数据的物理表在本例子中被固定设置为4张，当记录的年份跨度超过4年，某一个分区将用来存储多于一年。年份跨度不断增长，这样的现象也会不断重演。你不必添加新的分区，除非分区里面的数据量变得非常巨大，让你觉得需要重新分区。
    注意：分区在sql标准中没有定义，每个不用的数据库实现这一功能都是非标准的。

垂直分区

    鉴于水平分区是根据行来拆分，那么垂直分区就是根据列来拆分，将不经常用到的列或者非常庞大的列拆分出来。
    BLOB或者TEXT类型的列可能是非常大的，有些数据库默认做了拆分存储，如果你查询不包含BLOB或者TEXT速度是非常快的。
    我们开发可以创建一张额外的表来保存这些庞大的列，多出一列用来关联之前的表。比如product表，要将安装文件保存到数据库。（例子比较极端）

1
2
3
4
5
create table product_install_image(
	product_id bugint unsiged primary key,
	install_image blob,
	foreign key product_id references product(product_id);
);

    还有值得注意，在mysql的MyISAM引擎中，使用固定长度的列是最高效的，比如说char类型比varchar类型要高效。

解决元数据分裂

    我们在第8章解决过类似的问题，解决元数据分裂的方案就是创建关联表。

1
2
3
4
5
6
7
create table product_history(
	product_id bigint,
	year smallint,
	big_fix int,
	primary key(product_id, year),
	foreign key (product_id) references product(product_id)
);

    使用每一个产品、每一列记录一年的bug修复数量，还不如使用多行、仅用一列记录修复的bug数量。

结论

    别让数据繁衍元数据