BLACKHOLE儲存引擎總結

BLACKHOLE安裝

使用cmake原始碼安裝時指定-DWITH_BLACKHOLE_STORAGE_ENGINE 選項 可以啟用 BLACKHOLE 儲存引擎，使用預設編譯項可編譯BLACKHOLE，但是沒有安裝外掛，需要手工安裝。

mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

mysql> show variables like 'plugin_dir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| plugin_dir    | /db/mysql/lib/plugin/ |
+---------------+-----------------------+
1 row in set (0.00 sec)

mysql> system ls /db/mysql/lib/plugin/
adt_null.so    auth_test_plugin.so  ha_archive.so    ha_federated.so        qa_auth_client.so      semisync_master.so
auth.so        daemon_example.ini   ha_blackhole.so  libdaemon_example.so  qa_auth_interface.so  semisync_slave.so
auth_socket.so    debug             ha_example.so    mypluglib.so        qa_auth_server.so

mysql> INSTALL PLUGIN blackhole SONAME 'ha_blackhole.so';
Query OK, 0 rows affected (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

使用BLACKHOLE

建立BLACKHOLE表只會在資料庫生成.frm表定義結構。

mysql> use test;
Database changed

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
Empty set (0.00 sec)

mysql> system ls /db/mysql/data/test
db.opt    test.frm

BLACKHOLE總結

• BLACKHOLE支援所有型別的索引
• BLACKHOLE 表不儲存資料，如果複製基於SBR，語句可以記錄並在從庫執行；如果複製為RBR、MBR，UPDATE及DELETE操作將會跳過，不會記錄也從庫不執行。
• Insert觸發器可以正常使用，Update、Delete觸發器因為不儲存資料不能觸發，FOR EACH ROW 也不能觸發。
• BLACKHOLE 表Auto Increment欄位不會自動遞增,也不保留自增欄位的狀態
• 結合複製replicate-do和replicate-ignore規則，可使用BLACKHOLE當做一個分發主伺服器
• 可用來驗證轉儲檔案語法
• 測試binlog的開銷量，通過對比 BLACKHOLE 與 不啟動 binlog的效能
• 可能被用來查詢與儲存引擎自身不相關的效能瓶頸

參考：
http://dev.mysql.com/doc/refman/5.5/en/blackhole-storage-engine.html

整理自網路