SQL自学指南（20）：使用大型数据库

《构建企业级推荐系统》作者，公众号「数据与智能」主理人，9年推荐系统实战经验，持续输出推荐系统、大数据、机器学习、AI等硬核技术文章

早期数据库的硬盘容量是以兆字节为单位的，所以数据量不会很多，通常也很容易管理数据库。但如今随着硬盘容量的提升（到了15TB），一个现代磁盘阵列可以存储超过4PB的数据，云存储空间的容量基本是无限的。随着数据量的不断增长，关系数据库面临着各种各样的挑战，有些公司采取某些策略（如分区、集群和分片）通过跨多个存储层和服务器分布数据以继续使用关系数据库。还有些公司使用Hadoop等大数据平台以处理海量数据。本章将介绍其中一些策略，并会重点介绍扩展关系数据库的技术。

数据库表究竟什么时候会变得“太大”？不同的人可能会有不同的答案。但大多数人都会同意，当一个表超过几百万行时，下列任务会变得更加困难和耗时：

• 需要扫描整个表的查询的执行

• 索引创建/重建

• 数据存档/删除

• 生成表/索引统计信息

• 表的重定位（例如，移动到不同的表空间）

• 数据库备份

当数据库很小时，可以正常完成这些工作，但是随着更多数据的积累，这些工作会变得相当耗时，然后会由于有限的管理时间窗口而产生问题，导致任务失败。防止发生管理问题的最佳方法是在初次建表的时候将大型表拆分为多个部分，也就是分区（partition），尽管也可以在建表之后再对表进行分区，但是相对而言会比较麻烦。管理任务可以在单个分区上执行，并且通常是并行执行的，有些任务可以完全跳过一个或多个分区。

表分区是在20世纪90年代末由Oracle引入的概念，自此之后，每个主流数据库服务器都增加了表分区和索引的功能。对一个表进行分区时，会创建两个或多个表分区，每个分区的定义完全相同，但数据子集不相互重叠。例如，包含销售数据的表可以使用销售日期相关列按月份进行分区，也可以使用州/省代码按地理区域进行分区。

一旦将表分区了，表本身就变成了一个虚拟的概念。分区用于保存数据，所有索引都建立在分区中的数据上。但是，数据库用户仍然可以在不知道表已分区的情况下与表进行交互。这在概念上类似于视图，因为用户与作为接口而不是实际表的模式（Schema）对象交互。虽然每个分区必须具有相同的模式定义（列、列类型等），但每个分区有几个不同的管理功能：

• 分区可以存储于不同的表空间，表空间可以位于不同的物理存储层上；

• 可以使用不同的压缩方案对分区进行压缩；

• 可以删除某些分区的本地索引（稍后将详细介绍）；

• 表统计信息可以在某些分区上冻结，而在其他分区上定时刷新；

• 单个分区可以固定在内存中或存储在数据库的闪存层中。

因此，表分区允许用户灵活地进行数据存储和管理，同时仍然向用户社区呈现单个表的简单性。

在大多数关系数据库中可用的分区方案是水平分区（horizontal partitioning），它只将整行分配给一个分区。表也可以垂直分区，这涉及到将列集分配给不同的分区，但这必须手动完成。在对表进行水平分区时，必须选择分区键（partition key），该键是用于将行分配给特定分区的列。在大多数情况下，表的分区键由一列组成，分区函数（partitioning function）被用于该列以确定每一行应该存储在哪个分区中。

如果分区表有索引，你就可以指定某个特定索引是保持完整（称为全局索引），还是拆分成多个部分并且每个分区都有自己的索引（称为局部索引）。全局索引跨表的所有分区，对于未指定分区键值的查询非常有用。例如，假设你的表在sale_date列上分区，用户执行以下查询：

SELECT sum FROM sales WHERE geo_region_cd = 'US'

由于此查询的sale_date列中没有过滤条件，因此服务器需要搜索每个分区以查找美国总销售额。但是，如果在geo_region_cd列上创建了一个全局索引，那么服务器就可以使用该索引快速查找包含美国销售额的所有行。

虽然每种数据库都有自己特殊的分区功能，但是下面几节将介绍适用于大多数数据库的常见分区方法。

范围分区是第一种被实现的划分方法，并且它仍是应用最广泛的分区方法之一。虽然范围分区可以用于几种不同的列类型，但最常见的用法是按日期范围拆分表。例如，可以使用sale_date列对名为sales的表进行分区，以便将每周的数据存储在不同的分区中：

mysql> CREATE TABLE sales -> -> ) -> PARTITION BY RANGE ) -> , -> PARTITION s2 VALUES LESS THAN , -> PARTITION s3 VALUES LESS THAN , -> PARTITION s4 VALUES LESS THAN , -> PARTITION s5 VALUES LESS THAN , -> PARTITION s999 VALUES LESS THAN -> );Query OK, 0 rows affected

此语句创建六个不同的分区，其中有五个分别用于2020年的前五周，还有一个用于名为s999的分区，用于保存2020年第五周之后的所有行数据。对于这个表，yearweek（sale_date）表达式视作分区函数，sale_date列视作分区键。要查看分区表的元数据，可以使用information_schema数据库中的partitions表：

mysql> SELECT partition_name, partition_method, partition_expression -> FROM information_schema.partitions -> WHERE table_name = 'sales' -> ORDER BY partition_ordinal_position;+----------------+------------------+-------------------------+| PARTITION_NAME | PARTITION_METHOD | PARTITION_expression |+----------------+------------------+-------------------------+| s1 | RANGE | yearweek || s2 | RANGE | yearweek || s3 | RANGE | yearweek || s4 | RANGE | yearweek || s5 | RANGE | yearweek || s999 | RANGE | yearweek |+----------------+------------------+-------------------------+6 rows in set

要在sales表上执行的管理任务之一是生成新分区以保存将来的数据（防止数据被添加到maxvalue分区）。不同的数据库使用不同的方式处理此问题，但在MySQL中，你可以使用alter table命令的reorganize partition子句将s999分区继续划分为三部分：

ALTER TABLE sales REORGANIZE PARTITION s999 INTO , PARTITION s7 VALUES LESS THAN , PARTITION s999 VALUES LESS THAN );

重新执行前面的元数据查询，现在将看到八个分区：

mysql> SELECT partition_name, partition_method, partition_expression -> FROM information_schema.partitions -> WHERE table_name = 'sales' -> ORDER BY partition_ordinal_position;+----------------+------------------+-------------------------+| PARTITION_NAME | PARTITION_METHOD | PARTITION_expression |+----------------+------------------+-------------------------+| s1 | RANGE | yearweek || s2 | RANGE | yearweek || s3 | RANGE | yearweek || s4 | RANGE | yearweek || s5 | RANGE | yearweek || s6 | RANGE | yearweek || s7 | RANGE | yearweek || s999 | RANGE | yearweek |+----------------+------------------+-------------------------+8 rows in set

接下来，让我们向表中添加几行数据：

mysql> INSERT INTO sales -> VALUES -> , -> ;Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0

表中现在有两行数据，但是它们被插入了哪些分区呢？想知道答案，让我们使用from子句的partition子句来计算每个分区中的行数：

mysql> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION ;+-----------------------+| partition_rowcount |+-----------------------+| # of rows in S1 = 0 || # of rows in S2 = 1 || # of rows in S3 = 0 || # of rows in S4 = 0 || # of rows in S5 = 1 || # of rows in S6 = 0 || # of rows in S7 = 0 || # of rows in S999 = 0 |+-----------------------+8 rows in set

结果表明，一行数据插入了分区S2，另一行数据插入了分区S5。要查询指定分区，需要了解分区模式，不过它们通常用于管理类型的活动，所以用户不太可能执行这种类型的查询。

如果选择作为分区键的列包含状态代码（例如，CA、TX、VA等）、货币（例如，美元USD、欧元EUR、日元JPY等）或一些其他的枚举类型的值集，则可能需要使用列表分区，它允许指定分配给每个分区的值。例如，假设sales表包含geo_region_cd列，它包含以下值：

+---------------+--------------------------+| geo_region_cd | description |+---------------+--------------------------+| US_NE | United States North East || US_SE | United States South East || US_MW | United States Mid West || US_NW | United States North West || US_SW | United States South West || CAN | Canada || MEX | Mexico || EUR_E | Eastern Europe || EUR_W | Western Europe || CHN | China || JPN | Japan || IND | India || KOR | Korea |+---------------+--------------------------+13 rows in set

你可以将这些值按地区分组，并为每个区域创建分区，如下所示：

mysql> CREATE TABLE sales -> NOT NULL, -> amount DECIMAL -> ) -> PARTITION BY LIST COLUMNS -> , -> PARTITION EUROPE VALUES IN , -> PARTITION ASIA VALUES IN -> ); Query OK, 0 rows affected

该表有三个分区，每个分区包含一组两个或多个geo_region_cd值。接下来，让我们向表中添加几行数据：

mysql> INSERT INTO sales -> VALUES -> , -> , -> ; ERROR 1526 : Table has no partition for value from column_list

看起来好像出问题了，错误提示表明有一个地区代码没有分配给分区。查看create table语句，会发现忘了将Korea添加到asia分区中，我们可以使用alter table语句修复此问题：

mysql> ALTER TABLE sales REORGANIZE PARTITION ASIA INTO -> );Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0

好像起作用了，但还是检查一下元数据以确保Korea在asia分区中：

mysql> SELECT partition_name, partition_expression, -> partition_description -> FROM information_schema.partitions -> WHERE table_name = 'sales' -> ORDER BY partition_ordinal_position;+----------------+----------------------+---------------------------------+| PARTITION_NAME | PARTITION_expression | PARTITION_DEscriptION |+----------------+----------------------+---------------------------------+| NORTHAMERICA | `geo_region_cd` | 'US_NE','US_SE','US_MW','US_NW',|| | | 'US_SW','CAN','MEX' || EUROPE | `geo_region_cd` | 'EUR_E','EUR_W' || ASIA | `geo_region_cd` | 'CHN','JPN','IND','KOR' |+----------------+----------------------+---------------------------------+3 rows in set

Korea确实已被加入asia分区，所以我们现在可以成功地执行之前失败的数据插入操作了：

mysql> INSERT INTO sales -> VALUES -> , -> , -> ;Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0

虽然范围分区允许maxvalue分区自动存储没有映射到其他任何分区的所有行数据，但请记住，列表分区并不提供这样的溢出分区。因此，每当要添加另一个列值（例如，公司开始在澳大利亚销售产品）时，都需要修改分区定义，然后才能将具有新值的行添加到表中。

如果你的分区键列不适用于范围分区或列表分区，那么你还有第三个选项——可以尝试在一组分区上均匀地分布行，服务器通过对列值应用哈希函数来实现这一点。这种类型的分区被称为哈希分区（hash partitioning）。列表分区选择作为分区键的列只包含少量值，而哈希函数与之相反，在分区键列包含大量不同值时效果最佳。下面是sales表的另一个版本，不过使用的是四个哈希分区，这些分区是通过对cust_id列中的值进行哈希处理而生成的：

mysql> CREATE TABLE sales -> -> ) -> PARTITION BY HASH -> PARTITIONS 4 -> ;Query OK, 0 rows affected

将行添加到sales表时，它们将均匀地分布在四个分区中，我将其命名为H1、H2、H3和H4。为了看看它工作得有多好，让我们添加16行数据，每行的cust_id列的值不同：

mysql> INSERT INTO sales -> VALUES -> , , -> , , -> , , -> , , -> , , -> , , -> , , -> , ;Query OK, 16 rows affected Records: 16 Duplicates: 0 Warnings: 0

如果哈希函数能够很好地均匀分布行，那么理想情况下，我们应该在四个分区中的每个分区中看到四行数据：

mysql> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION UNION ALL -> SELECT concat) partition_rowcount -> FROM sales PARTITION ;+---------------------+| partition_rowcount |+---------------------+| # of rows in H1 = 4 || # of rows in H2 = 5 || # of rows in H3 = 3 || # of rows in H4 = 4 |+---------------------+4 rows in set

假设只插入了16行数据，这是一个非常好的分布，并且随着行数的增加，只要cust_id列有相当多的不同值，每个分区应该包含接近25%的行。

如果你需要更细粒度地控制如何将数据分配给分区，那么可以使用复合分区（composite partitioning），它允许用户对同一个表使用两种不同类型的分区。对于复合分区，第一个分区方法定义分区，第二个分区方法定义子分区。下面是一个示例，再次使用sales表，同时使用范围分区和哈希分区：

mysql> CREATE TABLE sales -> -> ) -> PARTITION BY RANGE ) -> SUBPARTITION BY HASH -> -> , -> PARTITION s2 VALUES LESS THAN -> , -> PARTITION s3 VALUES LESS THAN -> , -> PARTITION s4 VALUES LESS THAN -> , -> PARTITION s5 VALUES LESS THAN -> , -> PARTITION s999 VALUES LESS THAN -> -> );Query OK, 0 rows affected

共有6个分区，每个分区有4个子分区，总共有24个子分区。接下来，让我们重新插入前面示例中用于哈希分区的16行：

mysql> INSERT INTO sales -> VALUES -> , , -> , , -> , , -> , , -> , , -> , , -> , , -> , ;Query OK, 16 rows affected Records: 16 Duplicates: 0 Warnings: 0

查询sales表时，可以从其中一个分区检索数据，在这种情况下，可以从与该分区关联的四个子分区检索数据：

mysql> SELECT * -> FROM sales PARTITION ;+---------+---------+----------+------------+--------+| sale_id | cust_id | store_id | sale_date | amount |+---------+---------+----------+------------+--------+| 5 | 56 | 1 | 2020-01-20 | 1.60 || 15 | 472 | 1 | 2020-01-25 | 2.60 || 3 | 17 | 5 | 2020-01-19 | 1.30 || 7 | 122 | 4 | 2020-01-21 | 1.80 || 13 | 346 | 2 | 2020-01-24 | 2.40 || 9 | 179 | 5 | 2020-01-22 | 2.00 || 11 | 263 | 1 | 2020-01-23 | 2.20 |+---------+---------+----------+------------+--------+7 rows in set

因为表是子分区的，所以还可以从单个子分区检索数据：

mysql> SELECT * -> FROM sales PARTITION ;+---------+---------+----------+------------+--------+| sale_id | cust_id | store_id | sale_date | amount |+---------+---------+----------+------------+--------+| 7 | 122 | 4 | 2020-01-21 | 1.80 || 13 | 346 | 2 | 2020-01-24 | 2.40 |+---------+---------+----------+------------+--------+2 rows in set

此查询仅从s3分区的s3_h3子分区中检索数据。

分区的主要优点之一是就是在不与整个表交互的情况下只与一个分区交互。例如，如果你的表在sales_date列上执行了范围分区，然后执行了一个包含过滤条件的查询，比如WHERE sales_date BETWEEN '2019-12-01' AND '2020-01-15'，服务器将检查表的元数据以确定该查询实际上涉及哪些分区。这个概念称为分区修剪（partition pruning），它是表分区的最大优点之一。

类似地，如果执行的查询包含到分区表的连接，并且查询包含分区列上的条件，那么服务器可以排除与查询数据无关的所有分区，这称为分区连接（partition-wise joins）。与分区修剪类似，它只包括与包含查询相关的数据分区。

从管理的角度来看，分区的主要好处之一是能够快速删除无用数据。例如，财务数据可能需要在线保留七年。如果一个表已根据事务日期进行分区，那么可以删除所有包含超过七年的数据的分区。分区表的另一个管理优势是能够同时在多个分区上执行更新，这可以大大减少扫描表中每一行所需的时间。

有了足够的存储空间和合理的分区策略，你就可以在一个关系数据库中存储大量数据了。但若要处理数千个并发用户，或者在一夜之间生成数万个报表，又会发生什么呢？即使你有足够的数据存储，也可能没有足够的CPU、内存或网络带宽。解决该问题的一个可能的方案是集群（clustering），它允许多个服务器充当单个数据库。

尽管有几种不同的集群体系结构，但这里我指的是共享磁盘/共享缓存配置，其中集群中的每台服务器都可以访问所有磁盘，并且集群中的任何其他服务器都可以访问缓存在一台服务器中的数据。使用这种类型的体系结构，应用程序服务器可以连接到集群中的任何一个数据库服务器，一旦发生故障，连接会自动地将故障转移到群集中的另一个服务器。使用8-服务器集群，你应该就能处理大量的并发用户和相关的查询/报告/任务了。

在商业数据库供应商中，Oracle是这一领域的领导者，世界上许多最大的公司都使用Oracle Exadata平台来托管由数千个并发用户访问的超大数据库。然而，即使是这个平台也不能满足最大公司的需求，因此激励了谷歌、Facebook、亚马逊和其他公司的创新。

假设你被一家新的社交媒体公司聘为数据架构师，并被告知预计大约有10亿用户，每个用户平均每天将产生3.7条消息，而且数据必须是无限期可用的。在执行了一些预算估计之后，你认为将在不到一年的时间内耗尽可用的最大的关系数据库平台。一种可能的解决办法是：不仅对单个表进行分区，而且还要对整个数据库进行分区。这种方法称为分片（sharding），它将数据跨多个数据库进行分区（shards），因此它类似于表分区，但规模更大，复杂性更高。如果你要为这家社交媒体公司使用这种策略，那么你可能会决定采用100个独立数据库，每个数据库承载大约1000万用户的数据。

分片是一个复杂的主题，由于这是一本介绍性的书，因此我不会详细讨论它。以下是分片需要解决的几个问题：

• 你需要选择一个分片键（sharding key），用于确定要连接到哪个数据库的值；

• 虽然大型表将被划分为多个部分，将单独的行分配给单个分片，但可能需要将较小的引用表复制到所有分片，并且需要定义一种策略，以便修改引用数据并将更改传播到所有分片；

• 如果单个分片太大（例如，社交媒体公司现在有20亿用户），你将需要增加更多分片并在分片之间重新分配数据；

• 当你要更改模式时，需要有一个跨所有分片进行部署更改的策略，以便所有模式保持同步；

• 如果应用程序逻辑需要访问存储在两个或多个分片中的数据，那么需要设定一种策略以跨多个数据库进行查询，以及如何跨多个数据库实现事务。

这看起来挺复杂的，不过它也的确挺复杂。自21世纪末开始，许多公司开始寻找新的方法。下一节将介绍在关系数据库领域之外处理超大数据集的其他策略。

在思考权衡了分片的利弊之后，假设你（社交媒体公司的数据架构师）打算研究其他方法。与其试图开辟自己的道路，不如回顾一下其他处理大量数据的公司（亚马逊、谷歌、Facebook和Twitter等）所做的工作。这些公司以及其他公司开创的一系列技术加在一起被冠以“大数据”（big data）的称号，这是一种行业流行语，但有几种定义方式。定义大数据边界的一种方法是“3 Vs”：

• Volume（容量/体积）

在大数据中，容量通常意味着数十亿或万亿个数据点。

• Velocity（速度）

这是数据到达速度的度量。

• Variety（多样化）

这意味着数据不但可以是结构化的（如关系数据库中的行和列），也可以是非结构化的（如电子邮件、视频、照片、音频文件等）。

因此，描述大数据的一种方法是：被设计用于处理大量不同格式数据的系统。下面几节简要介绍了过去15年间发展起来的一些大数据技术。

Hadoop最好定义为一个生态系统（ecosystem），或者一组协同工作的技术和工具。Hadoop的一些主要组件包括：

• Hadoop分布式文件系统（Hadoop Distributed File System ）

顾名思义，HDFS支持跨大量服务器的文件管理。

• MapReduce

这项技术通过将一个任务分解成许多小块来处理大量的结构化和非结构化数据，这些小块可以在许多服务器上并行运行。

• YARN

这是一个用于HDFS的资源管理器和作业调度程序。

将这些技术结合在一起使用，可以将数百台甚至数千台服务器上的文件存储和处理视作一个单一的逻辑系统。虽然Hadoop已被广泛使用，但是使用MapReduce查询数据通常需要一个程序员来执行，这导致了几个SQL接口的开发（包括Hive、Impala和Drill）。

在关系型数据库中，数据通常必须符合预定义的模式，该模式由存储数字、字符串、日期等的列组成的表组成。但是，如果不知道数据的结构，或者已知结构但是结构经常变动，又会如何呢？对于许多公司来说，可以使用XML或JSON等格式将数据和模式定义合并到文档中，然后将文档存储在数据库中，这样一来，就可以无需修改模式，将各种类型的数据存储在同一个数据库中，所以存储会变得更容易，但同时会给查询和分析工具带来负担，使其无法理解存储在文档中的数据。

文档数据库是NoSQL数据库的一个子集，NoSQL数据库通常使用简单的键值机制来存储数据。例如，使用MongoDB这样的文档数据库，你可以使用客户ID作为键来存储包含客户所有数据的JSON文档，其他用户可以读取文档中存储的模式来理解其中存储的数据。

在大数据出现之前，大多数公司都必须建立自己的数据中心来存储整个企业使用的数据库、web和应用服务器。随着云计算的出现，你可以选择将数据中心外包给Amazon Web Services（AWS）、Microsoft Azure或Google Cloud等平台。将服务托管在云端的最大好处之一是即时可伸缩性，它允许你快速增加或减少运行服务所需的计算能力。刚创立的公司喜欢使用这些平台，因为他们无需预先考虑服务器、存储、网络或软件许可证的花销，因而可以直接开始编写代码。

就数据库而言，快速查看AWS的数据库和分析产品可以有如下选择：

• 关系数据库（MySQL、Aurora、PostgreSQL、MariaDB、Oracle和SQL Server）

• 内存数据库（ElastiCache）

• 数据仓库数据库（Redshift）

• NoSQL数据库（DynamoDB）

• 文档数据库（documentDB）

• 图形数据库（Neptune）

• 时间序列数据库（TimeStream）

• Hadoop（EMR）

• 数据湖（Lake Formation）

虽然关系数据库在2000年代中期之前一直占据主导地位，但显而易见，企业现在正在混合和匹配各种平台，而且随着时间的推移，关系数据库可能就不那么流行了。

数据库正变得越来越大，但与此同时，存储、集群和分区技术也越来越强大。无论采用哪种技术，处理大量数据都是相当具有挑战性的。无论是使用关系数据库、大数据平台还是各种数据库服务器，SQL都在不断发展，以便于从各种技术中检索数据。在本书的最后一章中，我将演示如何使用SQL引擎来查询以多种格式存储的数据。

了解更多推荐系统、大数据、机器学习、AI等硬核技术，可以关注我的知乎，或同名微信公众号