通過這個 Node.js 和 MySQL 示例項目，我們將看看如何有效地處理 數(shù)十億行 占用 數(shù)百GB 存儲空間的數(shù)據(jù)。

本文的第二個目標(biāo)是幫助你確定 Node.js + MySQL 是否適合你的需求，并為實現(xiàn)此類解決方案提供幫助。

本文章使用的實際代碼 可以在 GitHub 上找到 。

為什么使用 Node.js 和 MySQL？

我們使用 MySQL 來存儲我們的 Node.js監(jiān)控和調(diào)試工具 用戶的分布式跟蹤數(shù)據(jù) Trace。

我們選擇了 MySQL，因為在決定的時候，Postgres 并不是很擅長更新行，而對于我們來說，更新不可變數(shù)據(jù)是不合理的。

大多數(shù)人認(rèn)為，如果有數(shù)百萬的數(shù)十億行，他們應(yīng)該使用一個 NoSQL 解決方案，如 Cassandra 或 Mongo。

不幸的是，這些解決方案不 符合ACID ，當(dāng)數(shù)據(jù)一致性非常重要時，這些解決方案就難以使用。

然而，通過良好的索引和適當(dāng)?shù)囊?guī)劃，MySQL 可以作為上面提到的 NoSQL 的一種替代方案，很適合這樣的任務(wù)。

MySQL 有幾個存儲引擎。 InnoDB 是默認(rèn)的，它功能最多。但是，應(yīng)該考慮到 InnoDB 表是不可變的，這意味著每個 ALTER TABLE 語句都將所有的數(shù)據(jù)復(fù)制到一個新的表中。 當(dāng)需要遷移已經(jīng)存在的數(shù)據(jù)庫時，這會更加糟糕。

如果你有名義值，每個都有很多關(guān)聯(lián)的數(shù)據(jù) —— 例如你的每個用戶都有數(shù)百萬個產(chǎn)品，并且你擁有大量用戶 —— 這可能是為每個用戶創(chuàng)建表格最簡單的方法，并給出如 <user_id>_<entity_name> 。 這樣可以顯著減少單個表的大小。

此外，在刪除帳戶的情況下，刪除用戶的數(shù)據(jù)是 O(1) 量級的操作。這是非常重要的，因為如果你需要從大表中刪除大量的值，MySQL可能會決定使用錯誤的索引或不使用索引。

因為不能使用索引提示 DELETE 會讓事情變得更復(fù)雜。你可能需要 ALTER 來刪除你的數(shù)據(jù)，但這意味著將每行復(fù)制到新表。

為每個用戶創(chuàng)建表格顯然增加了復(fù)雜性，但是當(dāng)涉及到刪除具有大量相關(guān)數(shù)據(jù)的用戶或類似實體時，這可能是一個有效的辦法。

但是，在進(jìn)行動態(tài)創(chuàng)建表之前，你應(yīng)該嘗試刪除塊中的行，因為它也可能有幫助，可以減少附加復(fù)雜性。當(dāng)然，如果你的添加數(shù)據(jù)速度比你刪除的速度更快，你可能會感覺上述解決方案是個坑。

但是，如果你的表在分離用戶后仍然很大，導(dǎo)致你還需要刪除過期的行呢？你添加數(shù)據(jù)速度仍然比你刪除的速度更快。 在這種情況下，你應(yīng)該嘗試使用 MySQL 內(nèi)置的表分區(qū)。 當(dāng)你需要通過按順序或連續(xù)遞增的值（例如創(chuàng)建的時間戳）來切割表時，它很方便。

MySQL 表分區(qū)

MySQL 中一個表的表分區(qū)將像多個表一樣工作，但你可以使用與之前相同的界面，不需要更多應(yīng)用程序的附加邏輯。這也意味著你可以像刪除表一樣刪除表分區(qū)。

這個 文檔 很好，但也很繁瑣（畢竟這不是一個簡單的話題），所以讓我們快速看一下如何創(chuàng)建一個表分區(qū)。

我們處理我們的分區(qū)的方式是從 Rick James 的文章中獲取的。他還深入探討了如何規(guī)劃你的數(shù)據(jù)表。

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 之后才是我們關(guān)注的焦點。

在 MySQL 中，你可以通過 RANGE ， LIST ， COLUMN ， HASH 和 KEY 進(jìn)行分區(qū)，你可以在 文檔 中找到它們。請注意，分區(qū)鍵必須是主鍵或任何唯一的索引。

from<date> 開始的那些語句含義應(yīng)該是不言自明的。每個分區(qū)都保存 created_at 列小于第二天的值。這也意味著從 from20120414 保留所有在 2012-04-15 以前的數(shù)據(jù)，所以這是執(zhí)行清理時我們將刪除的分區(qū)。

future 和 start 分區(qū)需要一些解釋： future 持有我們尚未定義日期的數(shù)據(jù)。如果我們不能及時重新分區(qū)， 2017-05-17 以后的所有數(shù)據(jù)都將儲存在 future ，確保我們不會丟失任何數(shù)據(jù)。 start 也是一個安全網(wǎng)。我們期望所有行都有一個 DATETIME 和 created_at 值，但是我們需要為可能的錯誤做好準(zhǔn)備。如果由于某種原因，有一行最終會出現(xiàn) NULL ，那么它將在 start 分區(qū)中，這表示我們需要進(jìn)行 debug。

當(dāng)你使用分區(qū)時，MySQL 將該數(shù)據(jù)保存在磁盤的不同部分，就像它們是獨立的表一樣，并根據(jù)分區(qū)鍵自動組織數(shù)據(jù)。

要考慮到的一些限制：

不支持查詢緩存。 分區(qū)的 InnoDB 表不支持外鍵。 分區(qū)表不支持 FULLTEXT 索引或搜索。

還有 更多的限制 ，但是在 RisingStack 采用分區(qū)表之后，我們感觸最大的一個限制是。

如果要創(chuàng)建新分區(qū)，則需要重新組織一個現(xiàn)有分區(qū)，并將其分解以滿足你的需求：

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 );

刪除分區(qū)需要一個 alter table，盡管它會讓你感覺你是在刪除一個表：

ALTER TABLE tbl DROP PARTITION from20170517, from20170518;

你可以看到，你必須在語句中包括分區(qū)的實際名稱和描述。 它們不能由 MySQL 動態(tài)生成，所以你必須在應(yīng)用程序邏輯中處理它。這就是我們接下來的內(nèi)容。

Node.js 和 MySQL 的表分區(qū)示例

我們來看看實際的解決方案。對于這里的示例，我們將使用 knex ，它是為 JavaScript 而生的查詢構(gòu)建器。如果你熟悉 SQL，應(yīng)該對代碼感覺很熟悉。

首先，我們創(chuàng)建表：

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’) }

它實際上是我們前面看到的相同的語句，但是我們必須動態(tài)地創(chuàng)建分區(qū)的名稱和描述。這就是為什么我們創(chuàng)建了 getPartitionStrings 方法。

第一行是：

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

MAX_DATA_RETENTION - 2 = 5 創(chuàng)建從 5 到 -2（最后一個值排除）-> [ 5, 4, 3, 2, 1, 0, -1 ] 的序列，然后從當(dāng)前時間中減去這些值，并創(chuàng)建分區(qū)名稱的（ today ）及其限制（ tomorrow ）。順序是至關(guān)重要的，因為在語句中分區(qū)值不會增長時 MySQL 會拋出錯誤。

MySQL 和 Node.js 大規(guī)模數(shù)據(jù)刪除示例

現(xiàn)在我們來看一下數(shù)據(jù)刪除。你可以 在這里 看到整個代碼。

第一種方法， removeExpired 獲取當(dāng)前分區(qū)的列表，然后將其傳遞給 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 維護(hù)的 information_schema.partitions 表中選擇所有當(dāng)前存在的分區(qū)。

然后我們創(chuàng)建該表應(yīng)該存在的所有分區(qū)。如果 A 是存在的分區(qū)集合， B 是應(yīng)該存在的分區(qū)集合

partitionsToBeCreated = B A

partitionsToBeDropped = A B

getPartitionsThatShouldExist 創(chuàng)建集合 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 }

分區(qū)對象的創(chuàng)建與 CREATE TABLE ... PARTITION BY RANGE 非常相似。檢查我們即將創(chuàng)建的分區(qū)是否比當(dāng)前最舊的分區(qū)更舊，這一點至關(guān)重要：可能需要隨時間更改 dataRetention 。

以下情況為例：

假設(shè)你的用戶開始保留 7 天的數(shù)據(jù)，但可以選擇將其升級到 10 天。開始時，用戶用以下順序覆蓋分區(qū)天數(shù)： [ start, -7, -6, -5, -4, -3, -2, -1, future ] 。一個月左右，用戶決定升級。在這種情況下，丟失的分區(qū)是 [ -10, -9, -8, 0 ] 。

在清理時，當(dāng)前的腳本會嘗試重新組織 future 分區(qū)，使其在當(dāng)前腳本 之后 附加它們。

在最開始時創(chuàng)建比 -7 天更老的分區(qū)是沒有意義的，因為那些數(shù)據(jù)注定是被拋棄的，并且還會導(dǎo)致如下的一個分區(qū)列表 [ start, -7, -6, -5, -4, -3, -2, -1, -10, -9, -8, 0, future ] ，由于不是單調(diào)增加，因此 MySQL 會拋出錯誤，清理將失敗。

MySQL的 TO_DAYS(date) 函數(shù)計算從公元元年（ 0 年）1 月 1 日以來的天數(shù)，所以我們用 JavaScript 計算這個天數(shù)。

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

現(xiàn)在我們有必須刪除的分區(qū)和必須創(chuàng)建的分區(qū)，我們先為新的一天創(chuàng)建我們的新分區(qū)。

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 ); }

我們只需準(zhǔn)備一個創(chuàng)建新分區(qū)的語句。

我們每小時運行這個腳本，以確保沒有任何遺漏，我們能夠每天至少執(zhí)行一次清理。

所以首先檢查一下是否有一個要創(chuàng)建的分區(qū)。這只應(yīng)該在第一次運行時發(fā)生，然后剩余 23 次都不會發(fā)生。

我們還必須刪除過時的分區(qū)。

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 + ’;’ }

此方法創(chuàng)建了我們之前看到的 ALTER TABLE ... DROP PARTITION 語句。

最后，為重組做好了一切的準(zhǔn)備。

const statement = dedent ${Table.reorganizeFuturePartition(partitionsToBeCreated)} ${Table.dropOldPartitions(partitionsToBeDropped)} return knex.raw(statement) 總結(jié)

如你所見，與流行的觀點相反，當(dāng)你處理大量數(shù)據(jù)時，可以使用符合 ACID 的 DBMS 解決方案（如MySQL），因此你不一定需要放棄事務(wù)數(shù)據(jù)庫的功能。

符合 ACID 的 DBMS 解決方案（如 MySQL）可用于處理大量數(shù)據(jù)。

但是，表分區(qū)有很多限制，這意味著你將無法使用 InnoDB 提供的所有功能來保持?jǐn)?shù)據(jù)的一致性。你可能還無法使用外鍵和 FULLTEXT 搜索來處理應(yīng)用程序邏輯。

我希望這篇文章可以幫助你確定 MySQL 是否適合你的需求，并幫助你實現(xiàn)解決方案。

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