用 Node + MySQL 处理 100G 数据
GeoMosher
7年前
<p>通过这个 Node.js 和 MySQL 示例项目，我们将看看如何有效地处理 <strong>数十亿行</strong> 占用 <strong>数百GB</strong> 存储空间的数据。</p> <p>本文的第二个目标是帮助你确定 Node.js + MySQL 是否适合你的需求，并为实现此类解决方案提供帮助。</p> <p>本文章使用的实际代码 <a href="/misc/goto?guid=4959749822760776077" rel="nofollow,noindex">可以在 GitHub 上找到</a> 。</p> <h2>为什么使用 Node.js 和 MySQL？</h2> <p>我们使用 MySQL 来存储我们的 <a href="/misc/goto?guid=4959749822852014026" rel="nofollow,noindex">Node.js监控和调试工具</a> 用户的分布式跟踪数据 Trace。</p> <p>我们选择了 MySQL，因为在决定的时候，Postgres 并不是很擅长更新行，而对于我们来说，更新不可变数据是不合理的。</p> <p>大多数人认为，如果有数百万的数十亿行，他们应该使用一个 NoSQL 解决方案，如 Cassandra 或 Mongo。</p> <p>不幸的是，这些解决方案不 <a href="/misc/goto?guid=4958988928256894195" rel="nofollow,noindex">符合ACID</a> ，当数据一致性非常重要时，这些解决方案就难以使用。</p> <p>然而，通过良好的索引和适当的规划，MySQL 可以作为上面提到的 NoSQL 的一种替代方案，很适合这样的任务。</p> <p>MySQL 有几个存储引擎。 InnoDB 是默认的，它功能最多。但是，应该考虑到 InnoDB 表是不可变的，这意味着每个 ALTER TABLE 语句都将所有的数据复制到一个新的表中。 <em>当需要迁移已经存在的数据库时，这会更加糟糕。</em></p> <p>如果你有名义值，每个都有很多关联的数据 —— 例如你的每个用户都有数百万个产品，并且你拥有大量用户 —— 这可能是为每个用户创建表格最简单的方法，并给出如 <user_id>_<entity_name> 。 <strong>这样可以显著减少单个表的大小。</strong></p> <p>此外，在删除帐户的情况下，删除用户的数据是 O(1) 量级的操作。这是非常重要的，因为如果你需要从大表中删除大量的值，MySQL可能会决定使用错误的索引或不使用索引。</p> <p>因为不能使用索引提示 DELETE 会让事情变得更复杂。你可能需要 ALTER 来删除你的数据，但这意味着将每行复制到新表。</p> <p>为每个用户创建表格显然增加了复杂性，但是当涉及到删除具有大量相关数据的用户或类似实体时，这可能是一个有效的办法。</p> <p>但是，在进行动态创建表之前，你应该尝试删除块中的行，因为它也可能有帮助，可以减少附加复杂性。当然，如果你的添加数据速度比你删除的速度更快，你可能会感觉上述解决方案是个坑。</p> <p>但是，如果你的表在分离用户后仍然很大，导致你还需要删除过期的行呢？你添加数据速度仍然比你删除的速度更快。 <strong>在这种情况下，你应该尝试使用 MySQL 内置的表分区。</strong> 当你需要通过按顺序或连续递增的值（例如创建的时间戳）来切割表时，它很方便。</p> <h2>MySQL 表分区</h2> <p>MySQL 中一个表的表分区将像多个表一样工作，但你可以使用与之前相同的界面，不需要更多应用程序的附加逻辑。这也意味着你可以像删除表一样删除表分区。</p> <p>这个 <a href="/misc/goto?guid=4959749822967661227" rel="nofollow,noindex">文档</a> 很好，但也很繁琐（毕竟这不是一个简单的话题），所以让我们快速看一下如何创建一个表分区。</p> <p>我们处理我们的分区的方式是从 <a href="/misc/goto?guid=4959555285055762927" rel="nofollow,noindex">Rick James</a> 的文章中获取的。他还深入探讨了如何规划你的数据表。</p> <pre> <code class="language-sql">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 );</code></pre> <p>PARTITION BY RANGE 之后才是我们关注的焦点。</p> <p>在 MySQL 中，你可以通过 RANGE ， LIST ， COLUMN ， HASH 和 KEY 进行分区，你可以在 <a href="/misc/goto?guid=4959749822967661227" rel="nofollow,noindex">文档</a> 中找到它们。请注意，分区键必须是主键或任何唯一的索引。</p> <p>from<date> 开始的那些语句含义应该是不言自明的。每个分区都保存 created_at 列小于第二天的值。这也意味着从 from20120414 保留所有在 2012-04-15 以前的数据，所以这是执行清理时我们将删除的分区。</p> <p>future 和 start 分区需要一些解释： future 持有我们尚未定义日期的数据。如果我们不能及时重新分区， 2017-05-17 以后的所有数据都将储存在 future ，确保我们不会丢失任何数据。 start 也是一个安全网。我们期望所有行都有一个 DATETIME 和 created_at 值，但是我们需要为可能的错误做好准备。如果由于某种原因，有一行最终会出现 NULL ，那么它将在 start 分区中，这表示我们需要进行 debug。</p> <p>当你使用分区时，MySQL 将该数据保存在磁盘的不同部分，就像它们是独立的表一样，并根据分区键自动组织数据。</p> <p>要考虑到的一些限制：</p> <ul> <li>不支持查询缓存。</li> <li>分区的 InnoDB 表不支持外键。</li> <li>分区表不支持 FULLTEXT 索引或搜索。</li> </ul> <p>还有 <a href="/misc/goto?guid=4959749823095486062" rel="nofollow,noindex">更多的限制</a> ，但是在 <a href="/misc/goto?guid=4959749823174712532" rel="nofollow,noindex">RisingStack</a> 采用分区表之后，我们感触最大的一个限制是。</p> <p>如果要创建新分区，则需要重新组织一个现有分区，并将其分解以满足你的需求：</p> <pre> <code class="language-sql">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 );</code></pre> <p>删除分区需要一个 alter table，尽管它会让你感觉你是在删除一个表：</p> <pre> <code class="language-sql">ALTER TABLE tbl DROP PARTITION from20170517, from20170518;</code></pre> <p>你可以看到，你必须在语句中包括分区的实际名称和描述。 它们不能由 MySQL 动态生成，所以你必须在应用程序逻辑中处理它。这就是我们接下来的内容。</p> <h2>Node.js 和 MySQL 的表分区示例</h2> <p>我们来看看实际的解决方案。对于这里的示例，我们将使用 <a href="/misc/goto?guid=4959615068640697402" rel="nofollow,noindex">knex</a> ，它是为 JavaScript 而生的查询构建器。如果你熟悉 SQL，应该对代码感觉很熟悉。</p> <p>首先，我们创建表：</p> <pre> <code class="language-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') }</code></pre> <p>它实际上是我们前面看到的相同的语句，但是我们必须动态地创建分区的名称和描述。这就是为什么我们创建了 getPartitionStrings 方法。</p> <p>第一行是：</p> <pre> <code class="language-sql">const days = _.range(MAX_DATA_RETENTION - 2, -2, -1)</code></pre> <p>MAX_DATA_RETENTION - 2 = 5 创建从 5 到 -2（最后一个值排除）-> [ 5, 4, 3, 2, 1, 0, -1 ] 的序列，然后从当前时间中减去这些值，并创建分区名称的（ today ）及其限制（ tomorrow ）。顺序是至关重要的，因为在语句中分区值不会增长时 MySQL 会抛出错误。</p> <h3>MySQL 和 Node.js 大规模数据删除示例</h3> <p>现在我们来看一下数据删除。你可以 <a href="/misc/goto?guid=4959749823282535692" rel="nofollow,noindex">在这里</a> 看到整个代码。</p> <p>第一种方法， removeExpired 获取当前分区的列表，然后将其传递给 repartition 。</p> <pre> <code class="language-sql">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) }</code></pre> <p>首先，我们从 MySQL 维护的 information_schema.partitions 表中选择所有当前存在的分区。</p> <p>然后我们创建该表应该存在的所有分区。如果 A 是存在的分区集合， B 是应该存在的分区集合</p> <p>partitionsToBeCreated = B \ A</p> <p>partitionsToBeDropped = A \ B</p> <p>getPartitionsThatShouldExist 创建集合 B</p> <pre> <code class="language-sql">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 }</code></pre> <p>分区对象的创建与 CREATE TABLE ... PARTITION BY RANGE 非常相似。检查我们即将创建的分区是否比当前最旧的分区更旧，这一点至关重要：可能需要随时间更改 dataRetention 。</p> <p>以下情况为例：</p> <p>假设你的用户开始保留 7 天的数据，但可以选择将其升级到 10 天。开始时，用户用以下顺序覆盖分区天数： [ start, -7, -6, -5, -4, -3, -2, -1, future ] 。一个月左右，用户决定升级。在这种情况下，丢失的分区是 [ -10, -9, -8, 0 ] 。</p> <p>在清理时，当前的脚本会尝试重新组织 future 分区，使其在当前脚本 <em>之后</em> 附加它们。</p> <p>在最开始时创建比 -7 天更老的分区是没有意义的，因为那些数据注定是被抛弃的，并且还会导致如下的一个分区列表 [ start, -7, -6, -5, -4, -3, -2, -1, -10, -9, -8, 0, future ] ，由于不是单调增加，因此 MySQL 会抛出错误，清理将失败。</p> <p>MySQL的 TO_DAYS(date) 函数计算从公元元年（ 0 年）1 月 1 日以来的天数，所以我们用 JavaScript 计算这个天数。</p> <pre> <code class="language-sql">Table.getMysqlDay = function (momentDate) { return momentDate.diff(moment([ 0, 0, 1 ]), 'days') }</code></pre> <p>现在我们有必须删除的分区和必须创建的分区，我们先为新的一天创建我们的新分区。</p> <pre> <code class="language-sql">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 ); }</code></pre> <p>我们只需准备一个创建新分区的语句。</p> <p>我们每小时运行这个脚本，以确保没有任何遗漏，我们能够每天至少执行一次清理。</p> <p>所以首先检查一下是否有一个要创建的分区。这只应该在第一次运行时发生，然后剩余 23 次都不会发生。</p> <p>我们还必须删除过时的分区。</p> <pre> <code class="language-sql">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 + ';' }</code></pre> <p>此方法创建了我们之前看到的 ALTER TABLE ... DROP PARTITION 语句。</p> <p>最后，为重组做好了一切的准备。</p> <pre> <code class="language-sql">const statement = dedent ${Table.reorganizeFuturePartition(partitionsToBeCreated)} ${Table.dropOldPartitions(partitionsToBeDropped)} return knex.raw(statement)</code></pre> <h2>总结</h2> <p>如你所见，与流行的观点相反，当你处理大量数据时，可以使用符合 ACID 的 DBMS 解决方案（如MySQL），因此你不一定需要放弃事务数据库的功能。</p> <p>符合 ACID 的 DBMS 解决方案（如 MySQL）可用于处理大量数据。</p> <p>但是，表分区有很多限制，这意味着你将无法使用 InnoDB 提供的所有功能来保持数据的一致性。你可能还无法使用外键和 FULLTEXT 搜索来处理应用程序逻辑。</p> <p>我希望这篇文章可以帮助你确定 MySQL 是否适合你的需求，并帮助你实现解决方案。</p> <p> </p> <p>来自：http://www.zcfy.cc/article/node-js-mysql-example-handling-100-x27-s-of-gigabytes-of-data-risingstack-3130.html</p> <p> </p>