MariaDB之Spider存儲引擎實踐詳解
本文探討使用MySQL/MariaDB的Spider存儲引擎通過使用單個表鏈接到多臺服務(wù)器的實例。
Spider是MariaDB提供的一個新存儲引擎，可以讓一個標(biāo)準(zhǔn)配置的MariaDB成為一個分布式數(shù)據(jù)。
雖然實現(xiàn)起來還是會有點復(fù)雜，但技術(shù)并不太難。本文就來給各位介紹Spider存儲引擎的工作與技術(shù)原理，并會提供一些實用案例。
MariaDB存儲引擎
存儲引擎是用于管理低級別數(shù)據(jù)訪問的代碼級別實現(xiàn)，它處理寫入和讀取數(shù)據(jù)，行鎖定，多版本控制以及事務(wù)處理等。
從MySQL版本開始，存儲引擎不斷發(fā)展，由表的基礎(chǔ)開始，定義一張表使用指定的存儲引擎(ENGINE Table)，創(chuàng)建后數(shù)據(jù)庫管理系統(tǒng)開始處理，比如表之間的連接，從一張表中選擇數(shù)據(jù)等。在MySQL和MariaDB中，創(chuàng)建完表后，仍然可以更改表的存儲引擎。
高能Spider
Spider，一款名為蜘蛛的存儲引擎，它提供的是從一臺MariaDB服務(wù)器訪問另一臺MariaDB服務(wù)器的方法，保存實際表數(shù)據(jù)的MariaDB服務(wù)器可以沒有任何Spider處理代碼，一臺普通的MySQL/MariaDB服務(wù)器即可。
可以在一臺MariaDB上配置Spider，通過使用Spider存儲引擎訪問常規(guī)的MySQL API通信協(xié)議就可以正常訪問另一臺MariaDB上的數(shù)據(jù)。
上圖中，我們看到，Spider僅在引用節(jié)點上安裝激活，目標(biāo)節(jié)點并不需要安裝Spider。即創(chuàng)建一個Spider表，這意味著我們定義了一張表，這張表包括目標(biāo)表中的相同一列或此列的子集以及引用的服務(wù)器。
安裝Spider存儲引擎
Spider已經(jīng)包含在MariaDB服務(wù)器中，并提供了一個安裝Spider的腳本，我們使用它來安裝。
如果將MariaDB以RPM方式安裝，Spider將安裝在/usr/share/mysql中，腳本名字是install_spider.sql。我們使用MariaDB的命令行source執(zhí)行即可：
- $ 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引擎已經(jīng)就緒，已經(jīng)可以開始使用。
Spider單表連接
來看一個基礎(chǔ)的例子：在目標(biāo)服務(wù)器上創(chuàng)建一張表。再次提示，目標(biāo)服務(wù)器不需要Spider，僅訪問遠端數(shù)據(jù)的服務(wù)器是必需的?，F(xiàn)在我們在目標(biāo)服務(wù)器上創(chuàng)建一張新表，不妨稱它為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)
接下來向該表中插入一些測試數(shù)據(jù)：
- 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)
我承認(rèn)這個不是瑪麗.居里夫人發(fā)明鐳一樣的驚喜實驗，但可以看到數(shù)據(jù)也已經(jīng)成功插入到剛才的新表中?，F(xiàn)在要做的事是從MariaDB服務(wù)器的另一個實例來訪問該表。當(dāng)Spider以普通用戶身份連接到遠程服務(wù)器，需要在同一臺MariaDB Server上創(chuàng)建一個用戶帳戶，并為它授予創(chuàng)建數(shù)據(jù)表的權(quán)限。
- 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)
下一步創(chuàng)建一個Server。如果之前你沒有用過Spider，也不會用到這個命令，它是用來連接MariaDB Server實例以及相關(guān)參數(shù)，該服務(wù)器是在MariaDB Server實例上定義的。
下面是訪問我們在上面創(chuàng)建的表(稱為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創(chuàng)建從Server1到Server2的鏈接了(我們也不必使用目標(biāo)表中的所有字段)。
- $ 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，沒有出現(xiàn)任何錯誤。接著我們來用SELECT獲取數(shù)據(jù)。
- MariaDB [spidertest]> SELECT * FROM customer;
- +----+------------+
- | id | name |
- +----+------------+
- | 1 | John Doe |
- | 2 | Bob Smith |
- | 3 | Jane Jones |
- +----+------------+
- 3 rows in set (0.006 sec)
我們看到了，命令運行也已經(jīng)成功。居里夫人鼓勵我繼續(xù)下一步，我們正在一步步接近目標(biāo)。
用于單表鏈接
即使是單表鏈接，也有不少用途，比如上述表的鏈接。在某些情況下，它只是用來復(fù)制單張表，可以用它來替換復(fù)制。
比如你有一張表，它在維護著一個數(shù)據(jù)庫實例，比如客戶數(shù)據(jù)信息，你希望從另一個MariaDB Server中的應(yīng)用程序可以引用該庫的CustomerID。
這種配置存在一些缺點。比如，性能是一個大問題，對于使用Spider表的每個語句，都要建立與服務(wù)器的連接，本地表與Spider表之間的聯(lián)接也可能很慢?？傮w來說，Spider存儲引擎性能很出色，本身并不瓶頸，還存在很大的調(diào)整空間。
第三個選擇是當(dāng)我們只有一張表時，比如日志表，我并不想與任何其它表混在一起用。那么，Spider表是實現(xiàn)此目標(biāo)的一種選擇。
使用Spider在多臺服務(wù)器上的狀態(tài)
比如你運行的MariaDB集群是一個主數(shù)據(jù)庫和多個輔助數(shù)據(jù)組成，我們可以將此集群的所有服務(wù)器視為一個單元。接著派Spider出場，在當(dāng)前場景下，有兩個要監(jiān)視的服務(wù)器，一個叫moe，另一個叫homer，moe是主服務(wù)器，我們還希望在此中看到兩個服務(wù)器的全局狀態(tài)。
在homer服務(wù)器上，我們基于infomation_schema.GLOBAL STATUS創(chuàng)建一個視圖，添加一個用來保存服務(wù)器名字的字段再保存到數(shù)據(jù)庫中。如下：
- CREATE OR REPLACE VIEW global_status_homer
- AS SElECT 'homer' host, gs.variable_name, gs.variable_value
- FROM information_schema.global_status gs;
我們在moe服務(wù)器上做相同的事情：
- CREATE OR REPLACE VIEW global_status_moe
- AS SElECT 'moe' host, gs.variable_name, gs.variable_value
- FROM information_schema.global_status gs;
接下來，創(chuàng)建主服務(wù)器moe到另一臺服務(wù)器homer的鏈接。在moe上我們創(chuàng)建如下命令：
- CREATE OR REPLACE SERVER homer FOREIGN DATA WRAPPER mysql
- OPTIONS(HOST '192.168.0.11', DATABASE 'mysql', PORT 10482, USER 'spider',
- PASSWORD 'spider');
如此就在moe上創(chuàng)建了一個新視圖，用來查看homer服務(wù)器的狀態(tài)。
- 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服務(wù)器上，現(xiàn)有一張表和一張視圖。每臺服務(wù)器都有一個視圖，具有相似的架構(gòu)，它們反映著整個集群的狀態(tài)。接下來把兩張表結(jié)合成一個視圖。
- 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;
然后，視圖會顯示整個集群的狀態(tài)。
- 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)
嗯，盡管這是一個簡單實例，但它卻很有用。如果服務(wù)器多于兩臺時，它會更有價值。
合并多個服務(wù)器表
在上面的例子中，我們研究了如何使用Spider表將具有相似內(nèi)容和相同結(jié)構(gòu)的表合并到一個視圖中，這在實際場合中有價值的應(yīng)用，比如有一個多實例的應(yīng)用程序，如某個由多個部門使用的ERP應(yīng)用，如果要從報表服務(wù)器做跨部門報告，則可以使用此模型訪問該應(yīng)用程序的全部實例。
Spider的替代解決方案是使用多源復(fù)制，但它需要在報表服務(wù)器中存儲更多冗余的數(shù)據(jù)，這便是Spider解決方案的優(yōu)勢。
Spider分表
分片(也可稱為分表)是Spider最常見的用例了。在前面我將一臺服務(wù)器的表映射到另外一臺服務(wù)器的表，也可以將Spider用于服務(wù)器上的分區(qū)表，每個分區(qū)都在單獨的服務(wù)器上，除此之外在實踐上并沒有什么區(qū)別。
盡管Spider可以在分區(qū)用例上做更多有趣的事情，對于這些特定用例Spider還有一些性能增強能力。
來舉一個簡單的示例，我們來展示使用兩個分片來設(shè)置分區(qū)，為了展示其原理，我們將使用customer表。在此實例中總共有三臺服務(wù)器，兩臺“數(shù)據(jù)服務(wù)器”，其中包含兩個分片數(shù)據(jù)和一臺服務(wù)器，而“Spider”服務(wù)器中沒有任何正在使用表的實際數(shù)據(jù)，而指向的是駐留在其它兩以服務(wù)器上的數(shù)據(jù)。
從最基礎(chǔ)開始，先創(chuàng)建在MariaDB Server2和Server3中使用的表(與Customer表相似，但也不完全相同)。這兩臺服務(wù)器均以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);
現(xiàn)在已經(jīng)創(chuàng)建兩個分表，接著來創(chuàng)建鏈接，從MariaDB Server1來訪問它們，我們在Server1上執(zhí)行類似于以下SQL語句，里面的端口、主機名以及帳號根據(jù)情況可以自己更換。
- 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');
接下來，我們將它和一個分區(qū)表綁定在一起，注意你可以在此使用任何合理的分區(qū)方案，我們只是選擇一個簡單的方案來說明這一點。
- 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
現(xiàn)在，我們在Server1上可以插入一些數(shù)據(jù)，并在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');
根據(jù)分區(qū)設(shè)置，1-3行將進入Server2，最后3行將進入Server3。我們會在本節(jié)完成之前提出來，分區(qū)分表是常見的MariaDB實景，沒有Spider存儲引擎，因此可以通過連接到Server2或Server3，逐個分表來訪問。
用于Spider的分片
分片的明顯優(yōu)勢是在處理大型數(shù)據(jù)集合時可以有效提高性能。除了以上功能外，Spider存儲引擎還有其它優(yōu)點，比如每個分片都可以單獨訪問普通的MariaDB Server，即可以按分片查看數(shù)據(jù)集，而不存在任何瓶頸，亦可以將其視為整體，同時使用Spider。
小結(jié)
本文介紹了Spider存儲引擎的一些基本用法，這些用法對開發(fā)者都是很有用的，相關(guān)文檔還可參考Spider的分片用例文檔：https://mariadb.com/docs/usage/sharding/,你會發(fā)現(xiàn)前兩種用法對于一些人會驚訝加啟發(fā)。
