通過這個 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 PARTITIONn 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 是否適合你的需求，并幫助你實現解決方案。

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