今天介紹 Merge 儲存引擎.作業系統會有檔案大小的限制,當使用
InnoDB 引擎時,可以將新的資料檔案加入 Tablespace,從而可以
使用超過作業系統檔案限制的空間. 當原本使用 MyISAM的Table,
隨著使用時間資料量愈來愈多, 因為 MyISAM 是使用單一檔案來存放
資料,若超出作業系統檔案限制, 就會出問題.MySQL提供了 Merge
儲存引擎,可以將數個 MyISAM 的 Table 合併使用,來突破此一
限制.以下將示範使用方法.

-- 建立 Table
CREATE TABLE myisam_merge1 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(65535)
) ENGINE=MyISAM;


-- 利用之前的 Memory Engine 的 filler, 與Stored Procedure, 產生 32768 筆紀錄
-- 因為 filler 已經定義過了,我們可以使用,不需再定義.

call prc_filler(32768);

-- 輸入資料到 myisam_merge1
INSERT INTO myisam_merge1(data)
SELECT LPAD('', 65520, '*')
FROM filler;
Query OK, 32768 rows affected, 23.37 sec


-- 察看 myisam_merge1 空間
SELECT table_name, table_rows, row_format,
        data_length / 1048576 AS 'Data(M)',
       index_length / 1048576 AS 'Index(M)'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'myisam_merge1';

+---------------+------------+------------+-----------+----------+
| table_name    | table_rows | row_format | Data(M)   | Index(M) |
+---------------+------------+------------+-----------+----------+
| myisam_merge1 |      32768 | Dynamic    | 2048.0000 |   0.3232 |
+---------------+------------+------------+-----------+----------+

-- 2048M = 2G, 剛好 2G, 還記得 XP 的 2G 檔案限制吧.
-- 雖然是使用 Linux 當操作平台, 但是選用 2G 的檔案, 貼近實際應用情境.

-- 建立同樣結構,第2個與第3個 Table
CREATE TABLE myisam_merge2 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(65535)
) ENGINE=MyISAM;

CREATE TABLE myisam_merge3 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(65535)
) ENGINE=MyISAM;

-- 將第2個 Table 也輸入 2G 的資料
INSERT INTO myisam_merge2(data)
SELECT LPAD('', 65520, '*')
FROM filler;

-- 建立使用 Merge 引擎的 Table
CREATE TABLE merge_all (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(65535)
) ENGINE=MERGE
UNION=(myisam_merge1, myisam_merge2, myisam_merge3)
INSERT_METHOD=LAST;

-- 查看 merge_all 的資料筆數
SELECT COUNT(1)
FROM merge_all;
+----------+
| COUNT(1) |
+----------+
|    65536 |
+----------+

-- 是 myisam_merge1 , 32768 筆 與 myisam_merge2 32768 筆 之和.
-- 查看空間
SELECT table_name, table_rows, row_format,
        data_length / 1048576 AS 'Data(M)',
       index_length / 1048576 AS 'Index(M)'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name IN ('myisam_merge1', 'myisam_merge2', 
                      'myisam_merge3', 'merge_all');

+---------------+------------+------------+-----------+----------+
| table_name    | table_rows | row_format | Data(M)   | Index(M) |
+---------------+------------+------------+-----------+----------+
| merge_all     |      65536 | Dynamic    | 4096.0000 |   0.0000 |
| myisam_merge1 |      32768 | Dynamic    | 2048.0000 |   0.3232 |
| myisam_merge2 |      32768 | Dynamic    | 2048.0000 |   0.3232 |
| myisam_merge3 |          0 | Dynamic    |    0.0000 |   0.0010 |
+---------------+------------+------------+-----------+----------+

-- 輸入一筆 64K 資料到 merge_all

INSERT INTO merge_all(data) VALUES (LPAD('', 65520, '*'));

-- 查看空間
SELECT table_name, table_rows, row_format,
        data_length / 1048576 AS 'Data(M)',
       index_length / 1048576 AS 'Index(M)'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name IN ('myisam_merge1', 'myisam_merge2', 'merge_all');

+---------------+------------+------------+-----------+----------+
| table_name    | table_rows | row_format | Data(M)   | Index(M) |
+---------------+------------+------------+-----------+----------+
| merge_all     |      65537 | Dynamic    | 4096.0625 |   0.0000 |
| myisam_merge1 |      32768 | Dynamic    | 2048.0000 |   0.3232 |
| myisam_merge2 |      32768 | Dynamic    | 2048.0000 |   0.3232 |
+---------------+------------+------------+-----------+----------+

-- 查看 myisam_merge3 的空間
SELECT table_name, table_rows, row_format,
        data_length / 1024 AS 'Data(K)',
       index_length / 1024 AS 'Index(K)'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'myisam_merge3';

+---------------+------------+------------+---------+----------+
| table_name    | table_rows | row_format | Data(K) | Index(K) |
+---------------+------------+------------+---------+----------+
| myisam_merge3 |          1 | Dynamic    | 64.0000 |   2.0000 |
+---------------+------------+------------+---------+----------+

-- 查看 myisam_merge3 的資料筆數
SELECT COUNT(1)
FROM myisam_merge3;
+----------+
| COUNT(1) |
+----------+
|        1 |
+----------+

由上面的實際操作,可以看出 Merge 引擎的用法.