MariaDB之Spider存儲引擎實踐詳解

作者：海洋之心 2020-03-17 09:21:20

存儲存儲軟件 MariaDB

本文探討使用MySQL/MariaDB的Spider存儲引擎通過使用單個表鏈接到多臺服務器的實例。Spider是MariaDB提供的一個新存儲引擎，可以讓一個標準配置的MariaDB成為一個分布式數據。

本文探討使用MySQL/MariaDB的Spider存儲引擎通過使用單個表鏈接到多臺服務器的實例。

Spider是MariaDB提供的一個新存儲引擎，可以讓一個標準配置的MariaDB成為一個分布式數據。

雖然實現起來還是會有點復雜，但技術并不太難。本文就來給各位介紹Spider存儲引擎的工作與技術原理，并會提供一些實用案例。

[[318892]]

MariaDB存儲引擎

存儲引擎是用于管理低級別數據訪問的代碼級別實現，它處理寫入和讀取數據，行鎖定，多版本控制以及事務處理等。

從MySQL版本開始，存儲引擎不斷發展，由表的基礎開始，定義一張表使用指定的存儲引擎(ENGINE Table)，創建后數據庫管理系統開始處理，比如表之間的連接，從一張表中選擇數據等。在MySQL和MariaDB中，創建完表后，仍然可以更改表的存儲引擎。

高能Spider

Spider，一款名為蜘蛛的存儲引擎，它提供的是從一臺MariaDB服務器訪問另一臺MariaDB服務器的方法，保存實際表數據的MariaDB服務器可以沒有任何Spider處理代碼，一臺普通的MySQL/MariaDB服務器即可。

可以在一臺MariaDB上配置Spider，通過使用Spider存儲引擎訪問常規的MySQL API通信協議就可以正常訪問另一臺MariaDB上的數據。

上圖中，我們看到，Spider僅在引用節點上安裝激活，目標節點并不需要安裝Spider。即創建一個Spider表，這意味著我們定義了一張表，這張表包括目標表中的相同一列或此列的子集以及引用的服務器。

安裝Spider存儲引擎

Spider已經包含在MariaDB服務器中，并提供了一個安裝Spider的腳本，我們使用它來安裝。

如果將MariaDB以RPM方式安裝，Spider將安裝在/usr/share/mysql中，腳本名字是install_spider.sql。我們使用MariaDB的命令行source執行即可：

$ mysql -u root 
Welcome to the MariaDB monitor. Commands end with ; or \g. 
Your MariaDB connection id is 2835 
Server version: 10.4.6-MariaDB-log MariaDB Server 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
MariaDB> source /usr/share/mysql/install_spider.sql

腳本運行完成，使用SHOW ENGINES命令來查看所有安裝的存儲引擎：

MariaDB> SHOW ENGINES; 
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ 
 
| Engine | Support | Comment | Transactions | XA | Savepoints | 
 
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ 
 
| SPIDER | YES | Spider storage engine | YES | YES | NO | 
 
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | 
 
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | 
 
| Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO | 
 
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | 
 
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | 
 
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | 
 
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | 
 
| CSV | YES | Stores tables as CSV files | NO | NO | NO | 
 
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+ 
 
9 rows in set (0.001 sec)

可以看到Spider引擎已經就緒，已經可以開始使用。

Spider單表連接

來看一個基礎的例子：在目標服務器上創建一張表。再次提示，目標服務器不需要Spider，僅訪問遠端數據的服務器是必需的。現在我們在目標服務器上創建一張新表，不妨稱它為Sever2:

$ mysql -u root -S /tmp/mariadb2.sock -u root 
 
Welcome to the MariaDB monitor. Commands end with ; or \g. 
 
Your MariaDB connection id is 11 
 
Server version: 10.4.8-MariaDB MariaDB Server 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
 
 
 
 
MariaDB [(none)]> CREATE DATABASE spidertest; 
 
Query OK, 1 row affected (0.001 sec) 
 
MariaDB [(none)]> use spidertest; 
 
Database changed 
 
MariaDB [spidertest]> CREATE TABLE customer( 
 
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
 
name VARCHAR(200) NOT NULL, 
 
address VARCHAR(255) NOT NULL); 
 
Query OK, 0 rows affected (0.539 sec)

接下來向該表中插入一些測試數據：

MariaDB [spidertest]> INSERT INTO customer VALUES(NULL, 'John Doe', '1 Main Street'); 
 
Query OK, 1 row affected (0.309 sec) 
 
MariaDB [spidertest]> INSERT INTO customer VALUES(NULL, 'Bob Smith', '45 Elm Street'); 
 
Query OK, 1 row affected (0.092 sec) 
 
MariaDB [spidertest]> INSERT INTO customer VALUES(NULL, 'Jane Jones', 
 
'18 Second Street'); 
 
Query OK, 1 row affected (0.094 sec)

我承認這個不是瑪麗.居里夫人發明鐳一樣的驚喜實驗，但可以看到數據也已經成功插入到剛才的新表中。現在要做的事是從MariaDB服務器的另一個實例來訪問該表。當Spider以普通用戶身份連接到遠程服務器，需要在同一臺MariaDB Server上創建一個用戶帳戶，并為它授予創建數據表的權限。

MariaDB [spidertest]> CREATE USER 'spider'@'192.168.0.11' IDENTIFIED BY 'spider'; 
 
Query OK, 0 rows affected (0.236 sec) 
 
MariaDB [spidertest]> GRANT ALL ON spidertest.* TO 'spider'@'192.168.0.11'; 
 
Query OK, 0 rows affected (0.238 sec) 
 
 
 
MariaDB [spidertest]> GRANT ALL ON mysql.* TO 'spider'@'192.168.0.11'; 
 
Query OK, 0 rows affected (0.238 sec)

下一步創建一個Server。如果之前你沒有用過Spider，也不會用到這個命令，它是用來連接MariaDB Server實例以及相關參數，該服務器是在MariaDB Server實例上定義的。

下面是訪問我們在上面創建的表(稱為Server1)

$ mysql -u root -S /tmp/mariadb1.sock -u root 
 
Welcome to the MariaDB monitor. Commands end with ; or \g. 
 
Your MariaDB connection id is 12 
 
Server version: 10.4.8-MariaDB MariaDB Server 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
 
 
MariaDB [none]> CREATE SERVER Server2 FOREIGN DATA WRAPPER mysql 
 
OPTIONS(HOST '192.168.0.11', DATABASE 'spidertest', PORT 10482, 
 
USER 'spider', PASSWORD 'spider'); 
 
Query OK, 0 rows affected (0.233 sec)

好的，下面就可以用Spider創建從Server1到Server2的鏈接了(我們也不必使用目標表中的所有字段)。

$ mysql -u root -S /tmp/mariadb1.sock -u root 
 
Welcome to the MariaDB monitor. Commands end with ; or \g. 
 
Your MariaDB connection id is 33 
 
Server version: 10.4.8-MariaDB MariaDB Server 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
 
 
MariaDB [(none)]> DROP DATABASE IF EXISTS spidertest; 
 
Query OK, 0 rows affected, 1 warning (0.000 sec) 
 
MariaDB [(none)]> CREATE DATABASE spidertest; 
 
Query OK, 1 row affected (0.001 sec) 
 
MariaDB [(none)]> use spidertest; 
 
Database changed 
 
MariaDB [spidertest]> CREATE TABLE customer( 
 
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
 
name VARCHAR(200) NOT NULL) ENGINE=Spider 
 
COMMENT = 'wrapper "mysql", srv "Server2"'; 
 
Query OK, 0 rows affected (0.132 sec)

OK，沒有出現任何錯誤。接著我們來用SELECT獲取數據。

MariaDB [spidertest]> SELECT * FROM customer; 
 
+----+------------+ 
 
| id | name | 
 
+----+------------+ 
 
| 1 | John Doe | 
 
| 2 | Bob Smith | 
 
| 3 | Jane Jones | 
 
+----+------------+ 
 
3 rows in set (0.006 sec)

我們看到了，命令運行也已經成功。居里夫人鼓勵我繼續下一步，我們正在一步步接近目標。

用于單表鏈接

即使是單表鏈接，也有不少用途，比如上述表的鏈接。在某些情況下，它只是用來復制單張表，可以用它來替換復制。

比如你有一張表，它在維護著一個數據庫實例，比如客戶數據信息，你希望從另一個MariaDB Server中的應用程序可以引用該庫的CustomerID。

這種配置存在一些缺點。比如，性能是一個大問題，對于使用Spider表的每個語句，都要建立與服務器的連接，本地表與Spider表之間的聯接也可能很慢。總體來說，Spider存儲引擎性能很出色，本身并不瓶頸，還存在很大的調整空間。

第三個選擇是當我們只有一張表時，比如日志表，我并不想與任何其它表混在一起用。那么，Spider表是實現此目標的一種選擇。

使用Spider在多臺服務器上的狀態

比如你運行的MariaDB集群是一個主數據庫和多個輔助數據組成，我們可以將此集群的所有服務器視為一個單元。接著派Spider出場，在當前場景下，有兩個要監視的服務器，一個叫moe，另一個叫homer，moe是主服務器，我們還希望在此中看到兩個服務器的全局狀態。

在homer服務器上，我們基于infomation_schema.GLOBAL STATUS創建一個視圖，添加一個用來保存服務器名字的字段再保存到數據庫中。如下：

CREATE OR REPLACE VIEW global_status_homer 
 
AS SElECT 'homer' host, gs.variable_name, gs.variable_value 
 
FROM information_schema.global_status gs;

我們在moe服務器上做相同的事情：

CREATE OR REPLACE VIEW global_status_moe 
 
AS SElECT 'moe' host, gs.variable_name, gs.variable_value 
 
FROM information_schema.global_status gs;

接下來，創建主服務器moe到另一臺服務器homer的鏈接。在moe上我們創建如下命令：

CREATE OR REPLACE SERVER homer FOREIGN DATA WRAPPER mysql 
 
OPTIONS(HOST '192.168.0.11', DATABASE 'mysql', PORT 10482, USER 'spider', 
 
PASSWORD 'spider');

如此就在moe上創建了一個新視圖，用來查看homer服務器的狀態。

CREATE OR REPLACE TABLE global_status_homer(host varchar(2048), variable_name VARCHAR(64), variable_value VARCHAR(64)) ENGINE=Spider COMMENT='wrapper "mysql", srv "homer"';

在homer服務器上，現有一張表和一張視圖。每臺服務器都有一個視圖，具有相似的架構，它們反映著整個集群的狀態。接下來把兩張表結合成一個視圖。

CREATE OR REPLACE VIEW global_status_all AS 
 
SELECT host, variable_name, variable_value 
 
FROM global_status_homer 
 
UNION 
 
SELECT host, variable_name, variable_value 
 
FROM global_status_moe;

然后，視圖會顯示整個集群的狀態。

CREATE OR REPLACE VIEW global_status_total AS 
 
SELECT variable_name, SUM(variable_value) sum, MAX(variable_value) max, 
 
MIN(variable_value) min 
 
FROM global_status_all 
 
GROUP BY variable_name;

來測試下，它能不能正常工作：

MariaDB [mysql]> SELECT * FROM global_status_total WHERE variable_name LIKE 'open%'; 
 
+--------------------------+------+------+------+ 
 
| variable_name | sum | max | min | 
 
+--------------------------+------+------+------+ 
 
| OPENED_FILES | 629 | 477 | 152 | 
 
| OPENED_PLUGIN_LIBRARIES | 1 | 1 | 0 | 
 
| OPENED_TABLES | 112 | 75 | 37 | 
 
| OPENED_TABLE_DEFINITIONS | 125 | 95 | 30 | 
 
| OPENED_VIEWS | 85 | 43 | 42 | 
 
| OPEN_FILES | 132 | 76 | 56 | 
 
| OPEN_STREAMS | 0 | 0 | 0 | 
 
| OPEN_TABLES | 77 | 46 | 31 | 
 
| OPEN_TABLE_DEFINITIONS | 83 | 49 | 34 | 
 
+--------------------------+------+------+------+ 
 
9 rows in set (0.029 sec)

嗯，盡管這是一個簡單實例，但它卻很有用。如果服務器多于兩臺時，它會更有價值。

合并多個服務器表

在上面的例子中，我們研究了如何使用Spider表將具有相似內容和相同結構的表合并到一個視圖中，這在實際場合中有價值的應用，比如有一個多實例的應用程序，如某個由多個部門使用的ERP應用，如果要從報表服務器做跨部門報告，則可以使用此模型訪問該應用程序的全部實例。

Spider的替代解決方案是使用多源復制，但它需要在報表服務器中存儲更多冗余的數據，這便是Spider解決方案的優勢。

Spider分表

分片(也可稱為分表)是Spider最常見的用例了。在前面我將一臺服務器的表映射到另外一臺服務器的表，也可以將Spider用于服務器上的分區表，每個分區都在單獨的服務器上，除此之外在實踐上并沒有什么區別。

盡管Spider可以在分區用例上做更多有趣的事情，對于這些特定用例Spider還有一些性能增強能力。

來舉一個簡單的示例，我們來展示使用兩個分片來設置分區，為了展示其原理，我們將使用customer表。在此實例中總共有三臺服務器，兩臺“數據服務器”，其中包含兩個分片數據和一臺服務器，而“Spider”服務器中沒有任何正在使用表的實際數據，而指向的是駐留在其它兩以服務器上的數據。

從最基礎開始，先創建在MariaDB Server2和Server3中使用的表(與Customer表相似，但也不完全相同)。這兩臺服務器均以root用戶身份運行。

CREATE DATABASE IF NOT EXISTS spidertest; 
 
CREATE TABLE spidertest.customer( 
 
id INT NOT NULL PRIMARY KEY, 
 
name VARCHAR(200) NOT NULL, 
 
address VARCHAR(255) NOT NULL);

現在已經創建兩個分表，接著來創建鏈接，從MariaDB Server1來訪問它們，我們在Server1上執行類似于以下SQL語句，里面的端口、主機名以及帳號根據情況可以自己更換。

CREATE OR REPLACE SERVER Server2 FOREIGN DATA WRAPPER mysql 
 
OPTIONS(HOST '192.168.0.11', DATABASE 'spidertest', PORT 10482, 
 
USER 'spider', PASSWORD 'spider'); 
 
CREATE OR REPLACE SERVER Server3 FOREIGN DATA WRAPPER mysql 
 
OPTIONS(HOST '192.168.0.11', DATABASE 'spidertest', PORT 10483, 
 
USER 'spider', PASSWORD 'spider');

接下來，我們將它和一個分區表綁定在一起，注意你可以在此使用任何合理的分區方案，我們只是選擇一個簡單的方案來說明這一點。

CREATE TABLE spidertest.customer(id INT NOT NULL PRIMARY KEY, 
 
name VARCHAR(200) NOT NULL, 
 
address VARCHAR(255) NOT NULL) ENGINE=Spider 
 
COMMENT 'wrapper "mysql", table "customer"' 
 
PARTITION BY RANGE(id) ( 
 
PARTITION p0 VALUES LESS THAN (1000) COMMENT = 'srv "Server2"', 
 
PARTITION p1 VALUES LESS THAN (2000) COMMENT = 'srv "Server3

現在，我們在Server1上可以插入一些數據，并在Server2和Server3看到它們的顯示。

INSERT INTO customer VALUES(1, 'Larry', 'Main Street 1'); 
 
INSERT INTO customer VALUES(2, 'Ed', 'Main Street 1'); 
 
INSERT INTO customer VALUES(3, 'Bob', 'Main Street 1'); 
 
INSERT INTO customer VALUES(1001, 'Monty', 'Main Street 1'); 
 
INSERT INTO customer VALUES(1002, 'David', 'Main Street 1'); 
 
INSERT INTO customer VALUES(1003, 'Allan', 'Main Street 1');

根據分區設置，1-3行將進入Server2，最后3行將進入Server3。我們會在本節完成之前提出來，分區分表是常見的MariaDB實景，沒有Spider存儲引擎，因此可以通過連接到Server2或Server3，逐個分表來訪問。

用于Spider的分片

分片的明顯優勢是在處理大型數據集合時可以有效提高性能。除了以上功能外，Spider存儲引擎還有其它優點，比如每個分片都可以單獨訪問普通的MariaDB Server，即可以按分片查看數據集，而不存在任何瓶頸，亦可以將其視為整體，同時使用Spider。

小結

本文介紹了Spider存儲引擎的一些基本用法，這些用法對開發者都是很有用的，相關文檔還可參考Spider的分片用例文檔：https://mariadb.com/docs/usage/sharding/,你會發現前兩種用法對于一些人會驚訝加啟發。

責任編輯：武曉燕來源： 21CTO