通过这个 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 是否适合你的需求，并帮助你实现解决方案。

小手一抖，资料全有。长按二维码关注京程一灯，阅读更多技术文章和业界动态。