Partitioning

如何最好地將 Google ngram 儲存在數據庫中？

  • September 7, 2011

幾天前我下載了google onegrams，已經是海量數據了。我將 10 個包中的第一個插入 mysql，現在我有一個 4700 萬條記錄的數據庫。

我想知道如何最好地將 Google ngrams 儲存在數據庫中。我的意思是，如果您不使用一克，但例如兩克或三克，則數量會大得多。我可以在一個數據庫中儲存 5 億條記錄並使用它，還是應該將其拆分到不同的表中？

一個應該拆分多少條記錄後，應該如何最好地拆分它（考慮到 twograms 有 100 個文件，因此可能有大約 50 億條記錄）？是否建議使用 MySQL 水平分區或建構自己的分區（例如通過單詞的第一個字元 => twograms_a）。

我必須對我的第一個答案做出很多改變我開始這個！

USE test
DROP TABLE IF EXISTS ngram_key;
DROP TABLE IF EXISTS ngram_rec;
DROP TABLE IF EXISTS ngram_blk;
CREATE TABLE ngram_key
(
   NGRAM_ID UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
   NGRAM VARCHAR(64) NOT NULL,
   PRIMARY KEY (NGRAM),
   KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
CREATE TABLE ngram_rec
(
   NGRAM_ID UNSIGNED BIGINT NOT NULL,
   YR SMALLINT NOT NULL,
   MC SMALLINT NOT NULL,
   PC SMALLINT NOT NULL,
   VC SMALLINT NOT NULL,
   PRIMARY KEY (NGRAM_ID,YR)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE ngram_blk
(
   NGRAM VARCHAR(64) NOT NULL,
   YR SMALLINT NOT NULL,
   MC SMALLINT NOT NULL,
   PC SMALLINT NOT NULL,
   VC SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
BEGIN
   DECLARE NEW_ID BIGINT;

   INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
   SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
   INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.YR,NEW.MC,NEW.PC,NEW.VC);
END; $$
DELIMITER ;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30,vc=vc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30,pc=pc+30;
INSERT INTO ngram_blk VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
UPDATE ngram_rec SET yr=yr+1,mc=mc+30;
SELECT * FROM ngram_key;
SELECT * FROM ngram_rec;
SELECT A.ngram NGram,B.yr Year,B.mc Matches,B.pc Pages,B.vc Volumes FROM 
ngram_key A,ngram_rec B
WHERE A.ngram='rolando angel edwards'
AND A.ngram_id=B.ngram_id;

用於年份資訊的表格要小得多，但要保留原始 ngram 的密鑰要大得多。我還增加了測試數據量。您可以將其直接剪切並粘貼到 MySQL 中。

警告

只需刪除 ROW_FORMAT ，它就會變得動態並將 ngram_key 表壓縮得更小。

磁碟空間指標

nrgram_rec 每行 17 個

字節 ngram_id 8 個字節（最大無符號值 18446744073709551615

$$ 2^64 - 1 $$)

4 個 smallints 的 8 個字節（每個 2 個字節）

1 個字節 MyISAM 內部刪除標誌

ngram_rec 的索引條目 = 10 字節 (8 (ngram_id) + 2 (yr))

4700 萬行 X 每行 17 字節 = 07.99 億字節 = 761.98577 MB

4700 萬行 X 每行 12 字節 = 05.64 億字節 = 537.85231 MB

4700 萬行 X 每行 29 字節 = 13.63 億字節 = 1.269393 GB

50 億行 X 每行 17 字節 = 0850 億字節 = 079.1624 GB

50 億行 X 每行 12 字節 = 0600 億字節 = 055.8793 GB

50 億行 X 每行 29 字節 = 1450 億字節 = 135.0417 GB

ngram_key 有 73 個字節 ngram 有 64 個字節（ROW_FORMAT=FIXED 將 varchar 設置為 char） ngram_id 有 8 個字節 1 個字節 MyISAM 內部刪除標誌

ngram_key 的 2 個索引條目 = 64 字節 + 8 字節 = 72 字節

4700 萬行 X 每行 073 字節 = 34.31 億字節 = 3.1954 GB

4700 萬行 X 每行 072 字節 = 33.84 億字節 = 3.1515 GB

4700 萬行 X 每行 145 字節 = 68.15 億字節 = 6.3469 GB

50 億行 X 每行 073 字節 = 3650 億字節 = 339.9327 GB

50 億行 X 每行 072 字節 = 3600 億字節 = 335.2761 GB

50 億行 X 每行 145 字節 = 7250 億字節 = 675.2088 GB

這是一個非常瘋狂的建議

將所有 ngram 轉換為 32 個字元的 MD5 密鑰

該表將包含任何大小（最多 255 個字元）、1-gram、2-gram 等的所有 ngram。

use test
DROP TABLE ngram_node;
DROP TABLE ngram_blackhole;
CREATE TABLE ngram_node
(
 NGRAM_KEY  CHAR(32) NOT NULL,
 NGRAM_YEAR SMALLINT NOT NULL,
 M_COUNT    SMALLINT NOT NULL,
 P_COUNT    SMALLINT NOT NULL,
 V_COUNT    SMALLINT NOT NULL,
 PRIMARY KEY   (NGRAM_KEY,NGRAM_YEAR)
) ENGINE=MyISAM
PARTITION BY KEY(NGRAM_KEY)
PARTITIONS 256;
CREATE TABLE ngram_blackhole
(
 NGRAM      VARCHAR(255) NOT NULL,
 NGRAM_YEAR SMALLINT NOT NULL,
 M_COUNT    SMALLINT NOT NULL,
 P_COUNT    SMALLINT NOT NULL,
 V_COUNT    SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blackhole FOR EACH ROW
BEGIN
   INSERT INTO ngram_node VALUES (MD5(NEW.NGRAM),NEW.NGRAM_YEAR,NEW.M_COUNT,NEW.P_COUNT,NEW.V_COUNT);
END; $$
DELIMITER ;
INSERT INTO ngram_blackhole VALUES
('rolando',1965,31,29,85),
('pamela',1971,33,21,86),
('dominique',1996,30,18,87),
('diamond',1998,13,28,88),
('rolando edwards',1965,31,29,85),
('pamela edwards',1971,33,21,86),
('dominique edwards',1996,30,18,87),
('diamond edwards',1998,13,28,88),
('rolando angel edwards',1965,31,29,85),
('pamela claricia edwards',1971,33,21,86),
('dominique sharlisee edwards',1996,30,18,87),
('diamond ashley edwards',1998,13,28,88);
SELECT * FROM ngram_node;

我之所以選擇 256 個分區，是因為 MD5 函式返回 16 個不同的字元（都是十六進制數字）。前兩個字節是 16 X 16, 256。

這是我的 Windows 7 桌面上 MySQL 5.5.11 的結果

mysql> use test
Database changed
mysql> DROP TABLE ngram_node;
Query OK, 0 rows affected (0.22 sec)

mysql> DROP TABLE ngram_blackhole;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE ngram_node
   -> (
   ->   NGRAM_KEY  CHAR(32) NOT NULL,
   ->   NGRAM_YEAR SMALLINT NOT NULL,
   ->   M_COUNT    SMALLINT NOT NULL,
   ->   P_COUNT    SMALLINT NOT NULL,
   ->   V_COUNT    SMALLINT NOT NULL,
   ->   PRIMARY KEY    (NGRAM_KEY,NGRAM_YEAR)
   -> ) ENGINE=MyISAM
   -> PARTITION BY KEY(NGRAM_KEY)
   -> PARTITIONS 256;
Query OK, 0 rows affected (0.36 sec)

mysql> CREATE TABLE ngram_blackhole
   -> (
   ->   NGRAM      VARCHAR(255) NOT NULL,
   ->   NGRAM_YEAR SMALLINT NOT NULL,
   ->   M_COUNT    SMALLINT NOT NULL,
   ->   P_COUNT    SMALLINT NOT NULL,
   ->   V_COUNT    SMALLINT NOT NULL
   -> ) ENGINE=BLACKHOLE;
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blackhole FOR EACH ROW
   -> BEGIN
   ->  INSERT INTO ngram_node VALUES (MD5(NEW.NGRAM),NEW.NGRAM_YEAR,NEW.M_COUNT,NEW.P_COUNT,NEW.V_COUNT);
   -> END; $$
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> INSERT INTO ngram_blackhole VALUES
   -> ('rolando',1965,31,29,85),
   -> ('pamela',1971,33,21,86),
   -> ('dominique',1996,30,18,87),
   -> ('diamond',1998,13,28,88),
   -> ('rolando edwards',1965,31,29,85),
   -> ('pamela edwards',1971,33,21,86),
   -> ('dominique edwards',1996,30,18,87),
   -> ('diamond edwards',1998,13,28,88),
   -> ('rolando angel edwards',1965,31,29,85),
   -> ('pamela claricia edwards',1971,33,21,86),
   -> ('dominique sharlisee edwards',1996,30,18,87),
   -> ('diamond ashley edwards',1998,13,28,88);
Query OK, 12 rows affected (0.18 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ngram_node;
+----------------------------------+------------+---------+---------+---------+
| NGRAM_KEY                        | NGRAM_YEAR | M_COUNT | P_COUNT | V_COUNT |
+----------------------------------+------------+---------+---------+---------+
| 2ca237192aaac3b3a20ce0649351b395 |       1996 |      30 |      18 |      87 |
| 6f7fd3368170c562604f62fb4e92056d |       1965 |      31 |      29 |      85 |
| fb201333fef377917be714dabd3776d9 |       1971 |      33 |      21 |      86 |
| 4f79e21800ed6e30be4d1cb597f910c6 |       1971 |      33 |      21 |      86 |
| 9068e0de9f3fd674d4fa7cbc626e5888 |       1998 |      13 |      28 |      88 |
| 8a18abe90f2612827dc3a215fd1905d3 |       1965 |      31 |      29 |      85 |
| be60b431a46fcc7bf5ee4f7712993e3b |       1996 |      30 |      18 |      87 |
| c8adc38aa00759488b1d759aa8f91725 |       1996 |      30 |      18 |      87 |
| e80d4ab77eb18a4ca350157fd487d7e2 |       1965 |      31 |      29 |      85 |
| 669ffc150d1f875819183addfc842cab |       1971 |      33 |      21 |      86 |
| b685323e9de65080f733b53b2305da6e |       1998 |      13 |      28 |      88 |
| 75c6f03161d020201000414cd1501f9f |       1998 |      13 |      28 |      88 |
+----------------------------------+------------+---------+---------+---------+
12 rows in set (0.00 sec)

mysql>

請注意，我將 1-gram、2-gram 和 3-gram 載入到同一張表中，但您不知道哪個 MD5 屬於哪個 ngram。因此，所有的 ngram 都可以改裝到這張表中。只要記住插入到 ngram_blackhole 表中，剩下的就為你完成了。

無論是哪個 ngram，都必須使用 ngram 的 MD5() 查詢 ngram_node 表。

mysql> select * from ngram_node where ngram_key=MD5('rolando edwards');
+----------------------------------+------------+---------+---------+---------+
| NGRAM_KEY                        | NGRAM_YEAR | M_COUNT | P_COUNT | V_COUNT |
+----------------------------------+------------+---------+---------+---------+
| 6f7fd3368170c562604f62fb4e92056d |       1965 |      31 |      29 |      85 |
+----------------------------------+------------+---------+---------+---------+
1 row in set (0.05 sec)

如果您希望將 1-gram、2-gram 和 3-gram 分離到單獨的儲存庫中，只需在黑洞表上創建另一個表、另一個黑洞表和另一個觸發器以插入到另一個表中。

此外，如果您的 ngram 長於 255（如果您正在執行 7-gram 或 8-gram），則只需增加 ngram_blackhole 表中 NGRAM 列的 VARCHAR 大小。

試一試 ！！！

更新

在問題中，據說有 4700 萬行被載入到 mysql 中。對於我建議的表格佈局，請注意以下幾點：

ngram_node 是每行 41 個字節： 32 用於 NGRAM_KEY

8 用於數字（每個 SMALLINT 2）

1 用於內部 MyISAM DELETED 標誌

每個主鍵索引條目將是 34 字節

32 用於 NGRAM_KEY

2 用於 NGRAM_YEAR

4700 萬行 X 每行 41 字節 = 19.27 億字節，約 1.79466 GB。

4700 萬行 X 每個索引條目 34 字節 = 15.98 億字節，大約 1.48825 GB。

MyISAM 表的消耗總量應該約為 3.28291 GB。

該問題還提到載入 50 億行。

50 億行 X 每行 41 字節 = 2050 億字節，大約 190.9211 GB。

50 億行 X 每個索引條目 34 字節 = 1700 億字節，大約 158.3248 GB。

MyISAM 表的總消耗量應約為 349.2459 GB。

請注意，MyISAM 表中使用的空間的增長率是線性的，因為主鍵大小不變。您現在可以基於此對磁碟空間進行一些規劃。

引用自：https://dba.stackexchange.com/questions/2402

相關問答

Database-Design

使用 MariaDB 數據庫進行數據輪換

  • September 14, 2022
檢視問答
Postgresql

優化查詢以在分區表中跨多天獲取數據

  • August 4, 2022
檢視問答
Mysql

是否可以創建一個 MySQL 分區，然後通過另一列創建一個子分區？

  • July 26, 2022
檢視問答
Sql-Server

觸發分區表和性能

  • July 7, 2022
檢視問答
Postgresql

Postgresql 分區查詢的好處

  • April 13, 2022
檢視問答
Oracle

Oracle - 大表歸檔

  • April 10, 2022
檢視問答