smartsrh

用 Node + MySQL 处理 100G 数据

smartsrh · 2017-06-11翻译 · 503阅读 原文链接

通过这个 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 中,你可以通过 RANGELISTCOLUMNHASHKEY 进行分区,你可以在文档 中找到它们。请注意,分区键必须是主键或任何唯一的索引。

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

futurestart 分区需要一些解释: future持有我们尚未定义日期的数据。如果我们不能及时重新分区,2017-05-17 以后的所有数据都将储存在 future,确保我们不会丢失任何数据。start也是一个安全网。我们期望所有行都有一个 DATETIMEcreated_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 是否适合你的需求,并帮助你实现解决方案。

相关文章