用 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 PARTITION\n`
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 是否适合你的需求，并帮助你实现解决方案。
往期精选文章 |
|---|
使用虚拟dom和JavaScript构建完全响应式的UI框架 |
扩展 Vue 组件 |
使用Three.js制作酷炫无比的无穷隧道特效 |
一个治愈JavaScript疲劳的学习计划 |
全栈工程师技能大全 |
WEB前端性能优化常见方法 |
一小时内搭建一个全栈Web应用框架 |
干货：CSS 专业技巧 |
四步实现React页面过渡动画效果 |
让你分分钟理解 JavaScript 闭包 |
小手一抖，资料全有。长按二维码关注京程一灯，阅读更多技术文章和业界动态。