用 Node + MySQL 处理 100G 数据

通过这个 Node.js 和 MySQL 示例项目，我们将看看如何有效地处理 数十亿行 占用 数百GB 存储空间的数据。

本文的第二个目标是帮助你确定 Node.js + MySQL 是否适合你的需求，并为实现此类解决方案提供帮助。

本文章使用的实际代码 可以在 GitHub 上找到 。

为什么使用 Node.js 和 MySQL？

我们使用 MySQL 来存储我们的 Node.js监控和调试工具 用户的分布式跟踪数据 Trace。

我们选择了 MySQL，因为在决定的时候，Postgres 并不是很擅长更新行，而对于我们来说，更新不可变数据是不合理的。

大多数人认为，如果有数百万的数十亿行，他们应该使用一个 NoSQL 解决方案，如 Cassandra 或 Mongo。

不幸的是，这些解决方案不 符合ACID ，当数据一致性非常重要时，这些解决方案就难以使用。

然而，通过良好的索引和适当的规划，MySQL 可以作为上面提到的 NoSQL 的一种替代方案，很适合这样的任务。

MySQL 有几个存储引擎。 InnoDB 是默认的，它功能最多。但是，应该考虑到 InnoDB 表是不可变的，这意味着每个 ALTER TABLE 语句都将所有的数据复制到一个新的表中。 当需要迁移已经存在的数据库时，这会更加糟糕。

如果你有名义值，每个都有很多关联的数据 —— 例如你的每个用户都有数百万个产品，并且你拥有大量用户 —— 这可能是为每个用户创建表格最简单的方法，并给出如 <user_id>_<entity_name> 。 这样可以显著减少单个表的大小。

此外，在删除帐户的情况下，删除用户的数据是 O(1) 量级的操作。这是非常重要的，因为如果你需要从大表中删除大量的值，MySQL可能会决定使用错误的索引或不使用索引。

因为不能使用索引提示 DELETE 会让事情变得更复杂。你可能需要 ALTER 来删除你的数据，但这意味着将每行复制到新表。

为每个用户创建表格显然增加了复杂性，但是当涉及到删除具有大量相关数据的用户或类似实体时，这可能是一个有效的办法。

但是，在进行动态创建表之前，你应该尝试删除块中的行，因为它也可能有帮助，可以减少附加复杂性。当然，如果你的添加数据速度比你删除的速度更快，你可能会感觉上述解决方案是个坑。

但是，如果你的表在分离用户后仍然很大，导致你还需要删除过期的行呢？你添加数据速度仍然比你删除的速度更快。 在这种情况下，你应该尝试使用 MySQL 内置的表分区。 当你需要通过按顺序或连续递增的值（例如创建的时间戳）来切割表时，它很方便。

MySQL 表分区

MySQL 中一个表的表分区将像多个表一样工作，但你可以使用与之前相同的界面，不需要更多应用程序的附加逻辑。这也意味着你可以像删除表一样删除表分区。

这个 文档 很好，但也很繁琐（毕竟这不是一个简单的话题），所以让我们快速看一下如何创建一个表分区。

我们处理我们的分区的方式是从 Rick James 的文章中获取的。他还深入探讨了如何规划你的数据表。

CREATE TABLE IF NOT EXISTS tbl ( id INTEGER NOT NULL AUTO_INCREMENT, data VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id, created_at) ) PARTITION BY RANGE (TO_DAYS(created_at)) ( start VALUES LESS THAN (0), from20170514 VALUES LESS THAN (TO_DAYS(’2017-05-15’)), from20170515 VALUES LESS THAN (TO_DAYS(’2017-05-16’)), from20170516 VALUES LESS THAN (TO_DAYS(’2017-05-17’)), future VALUES LESS THAN MAXVALUE );

PARTITION BY RANGE 之后才是我们关注的焦点。

在 MySQL 中，你可以通过 RANGE ， LIST ， COLUMN ， HASH 和 KEY 进行分区，你可以在 文档 中找到它们。请注意，分区键必须是主键或任何唯一的索引。

from<date> 开始的那些语句含义应该是不言自明的。每个分区都保存 created_at 列小于第二天的值。这也意味着从 from20120414 保留所有在 2012-04-15 以前的数据，所以这是执行清理时我们将删除的分区。

future 和 start 分区需要一些解释： future 持有我们尚未定义日期的数据。如果我们不能及时重新分区， 2017-05-17 以后的所有数据都将储存在 future ，确保我们不会丢失任何数据。 start 也是一个安全网。我们期望所有行都有一个 DATETIME 和 created_at 值，但是我们需要为可能的错误做好准备。如果由于某种原因，有一行最终会出现 NULL ，那么它将在 start 分区中，这表示我们需要进行 debug。

当你使用分区时，MySQL 将该数据保存在磁盘的不同部分，就像它们是独立的表一样，并根据分区键自动组织数据。

要考虑到的一些限制：

不支持查询缓存。 分区的 InnoDB 表不支持外键。 分区表不支持 FULLTEXT 索引或搜索。

还有 更多的限制 ，但是在 RisingStack 采用分区表之后，我们感触最大的一个限制是。

如果要创建新分区，则需要重新组织一个现有分区，并将其分解以满足你的需求：

ALTER TABLE tbl REORGANIZE PARTITION future INTO ( from20170517 VALUES LESS THAN (TO_DAYS(’2017-05-18’)), from20170518 VALUES LESS THAN (TO_DAYS(’2017-05-19’)), PARTITION future VALUES LESS THAN MAXVALUE );

删除分区需要一个 alter table，尽管它会让你感觉你是在删除一个表：

ALTER TABLE tbl DROP PARTITION from20170517, from20170518;

你可以看到，你必须在语句中包括分区的实际名称和描述。 它们不能由 MySQL 动态生成，所以你必须在应用程序逻辑中处理它。这就是我们接下来的内容。

Node.js 和 MySQL 的表分区示例

我们来看看实际的解决方案。对于这里的示例，我们将使用 knex ，它是为 JavaScript 而生的查询构建器。如果你熟悉 SQL，应该对代码感觉很熟悉。

首先，我们创建表：

const dedent = require(’dedent’) const _ = require(’lodash’) const moment = require(’moment’) const MAX_DATA_RETENTION = 7 const PARTITION_NAME_DATE_FORMAT = ’YYYYMMDD’ Table.create = function () { return knex.raw(dedent CREATE TABLE IF NOT EXISTS ${tableName} ( id INTEGER NOT NULL AUTO_INCREMENT, data VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id, created_at) ) PARTITION BY RANGE ( TO_DAYS(created_at)) ( PARTITION start VALUES LESS THAN (0), ${Table.getPartitionStrings()} PARTITION future VALUES LESS THAN MAXVALUE ); ) } Table.getPartitionStrings = function () { const days = _.range(MAX_DATA_RETENTION - 2, -2, -1) const partitions = days.map((day) => { const tomorrow = moment().subtract(day, ’day’).format(’YYYY-MM-DD’) const today = moment().subtract(day + 1, ’day’).format(PARTITION_NAME_DATE_FORMAT) return PARTITION from${today} VALUES LESS THAN (TO_DAYS(’${tomorrow}’)), }) return partitions.join(’n’) }

它实际上是我们前面看到的相同的语句，但是我们必须动态地创建分区的名称和描述。这就是为什么我们创建了 getPartitionStrings 方法。

第一行是：

const days = _.range(MAX_DATA_RETENTION - 2, -2, -1)

MAX_DATA_RETENTION - 2 = 5 创建从 5 到 -2（最后一个值排除）-> [ 5, 4, 3, 2, 1, 0, -1 ] 的序列，然后从当前时间中减去这些值，并创建分区名称的（ today ）及其限制（ tomorrow ）。顺序是至关重要的，因为在语句中分区值不会增长时 MySQL 会抛出错误。

MySQL 和 Node.js 大规模数据删除示例

现在我们来看一下数据删除。你可以 在这里 看到整个代码。

第一种方法， removeExpired 获取当前分区的列表，然后将其传递给 repartition 。

const _ = require(’lodash’) Table.removeExpired = function (dataRetention) { return Table.getPartitions() .then((currentPartitions) => Table.repartition(dataRetention, currentPartitions)) } Table.getPartitions = function () { return knex(’information_schema.partitions’) .select(knex.raw(’partition_name as name’), knex.raw(’partition_description as description’)) // description holds the day of partition in mysql days .where(’table_schema’, dbName) .andWhere(’partition_name’, ’not in’, [ ’start’, ’future’ ]) .then((partitions) => partitions.map((partition) => ({ name: partition.name, description: partition.description === ’MAX_VALUE’ ? ’MAX_VALUE’ : parseInt(partition.description) }))) } Table.repartition = function (dataRetention, currentPartitions) { const partitionsThatShouldExist = Table.getPartitionsThatShouldExist(dataRetention, currentPartitions) const partitionsToBeCreated = _.differenceWith(partitionsThatShouldExist, currentPartitions, (a, b) => a.description === b.description) const partitionsToBeDropped = _.differenceWith(currentPartitions, partitionsThatShouldExist, (a, b) => a.description === b.description) const statement = dedent ${Table.reorganizeFuturePartition(partitionsToBeCreated)} ${Table.dropOldPartitions(partitionsToBeDropped)} return knex.raw(statement) }

首先，我们从 MySQL 维护的 information_schema.partitions 表中选择所有当前存在的分区。

然后我们创建该表应该存在的所有分区。如果 A 是存在的分区集合， B 是应该存在的分区集合

partitionsToBeCreated = B A

partitionsToBeDropped = A B

getPartitionsThatShouldExist 创建集合 B

Table.getPartitionsThatShouldExist = function (dataRetention, currentPartitions) { const days = _.range(dataRetention - 2, -2, -1) const oldestPartition = Math.min(...currentPartitions.map((partition) => partition.description)) return days.map((day) => { const tomorrow = moment().subtract(day, ’day’) const today = moment().subtract(day + 1, ’day’) if (Table.getMysqlDay(today) < oldestPartition) { return null } return { name: from${today.format(PARTITION_NAME_DATE_FORMAT)}, description: Table.getMysqlDay(tomorrow) } }).filter((partition) => !!partition) } Table.getMysqlDay = function (momentDate) { return momentDate.diff(moment([ 0, 0, 1 ]), ’days’) // mysql dates are counted since 0 Jan 1 00:00:00 }

分区对象的创建与 CREATE TABLE ... PARTITION BY RANGE 非常相似。检查我们即将创建的分区是否比当前最旧的分区更旧，这一点至关重要：可能需要随时间更改 dataRetention 。

以下情况为例：

假设你的用户开始保留 7 天的数据，但可以选择将其升级到 10 天。开始时，用户用以下顺序覆盖分区天数： [ start, -7, -6, -5, -4, -3, -2, -1, future ] 。一个月左右，用户决定升级。在这种情况下，丢失的分区是 [ -10, -9, -8, 0 ] 。

在清理时，当前的脚本会尝试重新组织 future 分区，使其在当前脚本 之后 附加它们。

在最开始时创建比 -7 天更老的分区是没有意义的，因为那些数据注定是被抛弃的，并且还会导致如下的一个分区列表 [ start, -7, -6, -5, -4, -3, -2, -1, -10, -9, -8, 0, future ] ，由于不是单调增加，因此 MySQL 会抛出错误，清理将失败。

MySQL的 TO_DAYS(date) 函数计算从公元元年（ 0 年）1 月 1 日以来的天数，所以我们用 JavaScript 计算这个天数。

Table.getMysqlDay = function (momentDate) { return momentDate.diff(moment([ 0, 0, 1 ]), ’days’) }

现在我们有必须删除的分区和必须创建的分区，我们先为新的一天创建我们的新分区。

Table.reorganizeFuturePartition = function (partitionsToBeCreated) { if (!partitionsToBeCreated.length) return ’’ // there should be only one every day, and it is run hourly, so ideally 23 times a day it should be a noop const partitionsString = partitionsToBeCreated.map((partitionDescriptor) => { return PARTITION ${partitionDescriptor.name} VALUES LESS THAN (${partitionDescriptor.description}), }).join(’n’) return dedent ALTER TABLE ${tableName} REORGANIZE PARTITION future INTO ( ${partitionsString} PARTITION future VALUES LESS THAN MAXVALUE ); }

我们只需准备一个创建新分区的语句。

我们每小时运行这个脚本，以确保没有任何遗漏，我们能够每天至少执行一次清理。

所以首先检查一下是否有一个要创建的分区。这只应该在第一次运行时发生，然后剩余 23 次都不会发生。

我们还必须删除过时的分区。

Table.dropOldPartitions = function (partitionsToBeDropped) { if (!partitionsToBeDropped.length) return ’’ let statement = ALTER TABLE ${tableName}nDROP PARTITIONn statement += partitionsToBeDropped.map((partition) => { return partition.name }).join(’,n’) return statement + ’;’ }

此方法创建了我们之前看到的 ALTER TABLE ... DROP PARTITION 语句。

最后，为重组做好了一切的准备。

const statement = dedent ${Table.reorganizeFuturePartition(partitionsToBeCreated)} ${Table.dropOldPartitions(partitionsToBeDropped)} return knex.raw(statement) 总结

如你所见，与流行的观点相反，当你处理大量数据时，可以使用符合 ACID 的 DBMS 解决方案（如MySQL），因此你不一定需要放弃事务数据库的功能。

符合 ACID 的 DBMS 解决方案（如 MySQL）可用于处理大量数据。

但是，表分区有很多限制，这意味着你将无法使用 InnoDB 提供的所有功能来保持数据的一致性。你可能还无法使用外键和 FULLTEXT 搜索来处理应用程序逻辑。

我希望这篇文章可以帮助你确定 MySQL 是否适合你的需求，并帮助你实现解决方案。

来自：http://www.zcfy.cc/article/node-js-mysql-example-handling-100-x27-s-of-gigabytes-of-data-risingstack-3130.html