MariaDB Spider 資料庫分庫分表實踐

痴者工良發表於2022-02-07

分庫分表

一般來說，資料庫分庫分表，有以下做法：

按雜湊分片：根據一條資料的標識計算雜湊值，將其分配到特定的資料庫引擎中；
按範圍分片：根據一條資料的標識（一般是值），將其分配到特定的資料庫引擎中；
按列表分片：根據某些欄位的標識，如果符合條件則分配到特定的資料庫引擎中。

分庫分表的做法有很多種，例如編寫程式碼庫，在程式中支援多資料庫，程式需要知道每個資料庫的地址，並要編寫程式碼進行支援；使用中介軟體將多個資料庫引擎連線起來，程式只需要知道中介軟體地址。

但是分庫分表後，因為任意兩個表可能在不同的資料庫例項中，兩個表進行連線查詢時，兩個資料庫例項之間的互動變得複雜起來，當叢集中的資料量較大時，便不能隨意 join 了，可能需要其他方式支撐聚合查詢。

分庫分表有優點有缺點，這裡就不再多說，先學會再打算。

MariaDB Server 是開源的，目前最流行的關係型資料庫之一，MariaDB 是從 Mysql 的分支開發而來，一直保持對 Mysql 的相容性。因為甲骨文的收購，MySQL 屬於 Oracle 所有，存在閉源的可能，以及逐漸商業化，變得不清真，於是 Mysql之父建立了 MariaDB，目的是完全相容 Mysql，並且開源、免費。

MariaDB 使用 Spider 外掛進行分庫分表的支援，Spider 儲存引擎是一個內建分片功能的儲存引擎。它支援分割槽和xa 事務，並允許處理不同 MariaDB 例項的表，就好像它們在同一個例項上一樣。

請參考資料：https://mariadb.com/kb/en/spider/

在這篇文章中，筆者將使用 MariaDB Spider 進行分庫分表的實踐。

部署 MariaDB 例項

為了更好地建立分庫分表實踐環境，這裡需要三個 “物理”資料庫，一個邏輯資料庫，即四個 MariaDB 例項。MariaDB 實際佔用的記憶體並不大，筆者 4G 記憶體的伺服器裝了 Kubernetes ，用 Docker 部署四個 MariaDB 資料庫，執行速度正常，對於我們測試練習 4G 記憶體足以。

四個資料庫的關係如圖：

其中，邏輯資料庫例項稱為 Spider Proxy Node，實際儲存資料的資料庫例項被稱為 Backend Node。

典型的 Spider 部署具有無共享的叢集架構。該系統適用於任何廉價的硬體，並且對硬體或軟體的特定要求最低。它由一組計算機組成，具有一個或多個 MariaDB 程式，稱為節點。

儲存資料的節點將被設計為Backend Nodes，並且可以是任何 MariaDB、MySQL、Oracle 伺服器例項，使用後端內可用的任何儲存引擎。

Docker 部署

如果機器不夠，使用虛擬機器部署便會顯得很麻煩，這裡筆者使用 Docker 快速部署練習。

參考資料：https://mariadb.com/kb/en/installing-and-using-mariadb-via-docker/

檢視 MariaDB 映象版本列表：https://hub.docker.com/_/mariadb/

直接建立四個資料庫例項，其中一個是 Spider 例項，例項使用埠區分。

docker run --name mariadbtest1 -e MYSQL_ROOT_PASSWORD=123456 -p 13306:3306 -d docker.io/library/mariadb:10.7

docker run --name mariadbtest2 -e MYSQL_ROOT_PASSWORD=123456 -p 13307:3306 -d docker.io/library/mariadb:10.7

docker run --name mariadbtest3 -e MYSQL_ROOT_PASSWORD=123456 -p 13308:3306 -d docker.io/library/mariadb:10.7

docker run --name mariadbspider -e MYSQL_ROOT_PASSWORD=123456 -p 13309:3306 -d docker.io/library/mariadb:10.7

接著，進入每個容器例項中，進入 /etc/mysql/mariadb.conf.d 目錄，修改50-server.cnf檔案，執行遠端訪問資料庫例項。由於容器中沒有 nano、vi 這些編輯命令，因此可以使用下面的命令快速替換檔案內容：

echo '
[server]
[mysqld]
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking
bind-address            = 0.0.0.0
expire_logs_days        = 10
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
[embedded]
[mariadb]
[mariadb-10.7]
' > 50-server.cnf

然後檢視每個容器的主機內 IP：

docker inspect --format='{{.NetworkSettings.IPAddress}}' mariadbtest1 mariadbtest2 mariadbtest3 mariadbspider

172.17.0.2
172.17.0.3
172.17.0.4
172.17.0.5

接著開啟名為 mariadbspider 的容器，在裡面按照 Spider 外掛：

apt update
apt install mariadb-plugin-spider

虛擬機器部署

這裡需要四個虛擬機器，每個虛擬機器都需要先安裝 MariaDB 資料庫引擎以及一些工具包。

可參考：https://mariadb.com/kb/en/spider-installation/

首先在每個虛擬安裝 MariaDB Community Server，即資料庫引擎。

如果使用虛擬機器部署安裝，需要替換國內映象源，以便快速下載需要的包， Centos 伺服器，可以直接以下命令快速更新映象源，如果是 Debain 系列，可自行查詢對應的映象源。

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
#清除快取
yum clean all
#生成新的快取
yum makecache

接著，配置 MariaDB 官方的軟體包儲存庫：

sudo yum install wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "fd3f41eefff54ce144c932100f9e0f9b1d181e0edd86a6f6b8f2a0212100c32c mariadb_repo_setup" | sha256sum -c -
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup --mariadb-server-version="mariadb-10.7"

再次更新映象源快取：

#清除快取
yum clean all
#生成新的快取
yum makecache

安裝 MariaDB 社群伺服器和軟體包依賴項：

sudo yum install MariaDB-server MariaDB-backup

接著，配置允許遠端訪問資料庫。

MariaDB 的配置檔案都在 /etc/my.cnf 中，開啟 /etc/my.cnf.d/ 目錄後，修改 server.cnf 檔案，允許遠端訪問。找到 bind-address 屬性，去掉 # 。

#bind-address=0.0.0.0
↓
bind-address=0.0.0.0

如需瞭解每個配置的作用，請參考資料： https://mariadb.com/docs/deploy/community-spider/#configuration

修改密碼。因為裸機部署的資料庫，本身沒有密碼，所以需要手動配置。

開啟終端，執行以下命令：

mysql -u root -p

set password for root @localhost = password('123456');

然後執行 quit; 退出資料庫操作終端。

如果提示 root 不存在，則請使用 mysql -u mysql -p ，密碼為空，直接按下Enter鍵即可。如果不行，則參考：https://www.whuanle.cn/archives/1385

然後重啟資料庫例項：

systemctl restart mariadb
systemctl status mariadb

接著檢查防火牆配置，或執行 sudo iptables -F 清理防火牆配置。

MariaDB 配置

MariaDB 配置檔案中，部分主要屬性的說明如下如下：

欄位	說明
bind_address	繫結訪問地址
max_connections	最大連線數
thread_handling	設定 MariaDB 社群伺服器如何處理客戶端連線的執行緒
log_error	錯誤日誌輸出檔案

MariaDB 基礎維護命令：

說明	命令
啟動	sudo systemctl start mariadb
停止	sudo systemctl stop mariadb
重新啟動	sudo systemctl restart mariadb
在啟動期間啟用	sudo systemctl enable mariadb
啟動時禁用	sudo systemctl disable mariadb
狀態	sudo systemctl status mariadb

檢查每個例項

部署資料庫後，需要連線每個資料庫進行測試，以便檢查資料庫是否正常。

配置 Spider

開啟 mariadbspider 資料庫例項，執行以下命令，載入 spider 外掛，將其設定為 Spider 資料庫例項。

INSTALL SONAME 'ha_spider';

執行命令查詢是否已經啟動 Spider 外掛：
SELECT * FROM mysql.plugin;

請參考資料：https://mariadb.com/kb/en/spider-installation/

遠端表

MariaDB Spider 模式已經搭建好了，這裡開始進行實踐。

在這個模式中，Spider 中的一個表對應一個資料庫例項中的同名資料庫的同名表，即資料庫名稱系統，表名稱相同。

首先在三個資料庫例項中，建立一個測試資料庫，名稱為 test1 ，然後執行命令建立表：

CREATE TABLE s(
  id INT NOT NULL AUTO_INCREMENT,
  code VARCHAR(10),
  PRIMARY KEY(id));

然後在 mariadbspider 例項中，執行命令，建立邏輯表，並將這個表繫結到 mariadbtest1 例項中。

CREATE TABLE s(
  id INT NOT NULL AUTO_INCREMENT,
  code VARCHAR(10),
  PRIMARY KEY(id)
)
ENGINE=SPIDER 
COMMENT 'host "172.17.0.2", user "root", password "123456", port "3306"';

注意替換你的 IP，另外注意埠，如果是容器訪問容器，直接使用 3306。

如果沒有配置好，資料庫不對應等，可能會出現：
> 1046 - No database selected
> 時間: 0.062s

然後在 mariadbspider 中，插入四條資料：

INSERT INTO s(code) VALUES ('a');
INSERT INTO s(code) VALUES ('b');
INSERT INTO s(code) VALUES ('c');
INSERT INTO s(code) VALUES ('d');

如果分別開啟三個例項，你會發現，插入的資料只會出現在 mariadbtest1 中出現，因為這個表只繫結了它。你還可以在 mariadbspider 上對這個表進行增刪查改，所有操作都會同步到對應資料庫例項中。

基準效能測試

SysBench 是一個模組化、跨平臺和多執行緒的基準測試工具，支援 Windows 和 Linux，用於評估對於在高負載下執行資料庫的系統非常重要的作業系統引數。這個基準測試套件的想法是，在不設定複雜的資料庫基準或甚至根本不安裝資料庫的情況下，快速獲得系統效能的印象。它可以測試出：

檔案 i/o 效能
排程器效能
記憶體分配和傳輸速度
POSIX 執行緒實現效能
資料庫伺服器效能(OLTP 基準)

專案地址：https://github.com/akopytov/sysbench

Linux 可以直接安裝二進位制包。

Debian/Ubuntu

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench

RHEL/CentOS:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

Fedora:

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash	
sudo dnf -y install sysbench

Arch Linux:
```
sudo pacman -Suy sysbench
```

sysbench 命令格式：

sysbench <TYPE> --threads=2 --report-interval=3 --histogram --time=50 --db-driver=mysql --mysql-host=<HOST> --mysql-db=<SCHEMA> --mysql-user=<USER> --mysql-password=<PASSWORD> run

首先，在當前特定資料庫下建立模擬資料：

sysbench oltp_read_write --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=123.123.123.123 --mysql-port=13309  --mysql-db=test1 prepare

sysbench 1.0.18 (using system LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

接著執行測試：

sysbench oltp_read_write --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=123.123.123.123 --mysql-port=13309  --mysql-db=test1 run

SQL statistics:
    queries performed:
        read:                            112
        write:                           32
        other:                           16
        total:                           160
    transactions:                        8      (0.80 per sec.)
    queries:                             160    (15.96 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0273s
    total number of events:              8

Latency (ms):
         min:                                 1244.02
         avg:                                 1253.36
         max:                                 1267.87
         95th percentile:                     1258.08
         sum:                                10026.85

Threads fairness:
    events (avg/stddev):           8.0000/0.00
    execution time (avg/stddev):   10.0269/0.00

或者每 3 秒生成一次直方圖：

sysbench oltp_read_write --threads=2 --report-interval=3 --histogram --time=50 --table-size=1000000 --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=123.123.123.123 --mysql-port=13309  --mysql-db=test1 run

清理模擬生成的資料：

sysbench oltp_read_write --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-host=123.123.123.123 --mysql-port=13309  --mysql-db=test1 cleanup

sysbench 跑測試時，可選引數如下：

使用–time=<SECONDS>執行固定時間
使用–events=0對執行的查詢不設定限制
使用–db-ps-mode=disable禁用準備好的語句
使用–report-interval=<SECONDS>獲取繪圖點
用--histogram得到一個直方圖

sysbench 有三個過程或執行模式：

prepare：為需要它們的測試執行準備操作，例如在磁碟上為fileio 測試建立必要的檔案，或填充測試資料庫以進行資料庫基準測試。
run：執行使用testname 引數指定的實際測試。此命令由所有測試提供。
cleanup：在建立一個的測試中測試執行後刪除臨時資料。

你也可以參考筆者的另一篇文章，使用別的方法做基準測試：https://www.whuanle.cn/archives/1388

加入後端資料庫

在遠端表一節中，我們是在建立表的時候，再繫結一個資料庫例項，其實也可以提前配置多個資料庫例項到 Spider 中，下面是在 Spider 中執行的配置命令：

CREATE SERVER mariadbtest1 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '172.17.0.2', 
  DATABASE 'test1',
  USER 'root',
  PASSWORD '123456',
  PORT 3306
);

CREATE SERVER mariadbtest2 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '172.17.0.3', 
  DATABASE 'test1',
  USER 'root',
  PASSWORD '123456',
  PORT 3306
);

CREATE SERVER mariadbtest3 
  FOREIGN DATA WRAPPER mysql 
OPTIONS( 
  HOST '172.17.0.4', 
  DATABASE 'test1',
  USER 'root',
  PASSWORD '123456',
  PORT 3306
);

雜湊分片

在這一小節中，我們將一個表進行分片，在插入資料時，資料自動分片到三個資料庫例項中。

在三個資料節點資料庫中，在 test1 資料庫下，執行命令，建立表：

CREATE  TABLE shardtest
(
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  k int(10) unsigned NOT NULL DEFAULT '0',
  c char(120) NOT NULL DEFAULT '',
  pad char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY k (k)
)

此時，三個資料庫例項都具有相同的表。

然後在 mariadbspider 例項中，執行命令，建立邏輯表，並將此表通過切片的模式，連線到三個資料庫例項中。

CREATE TABLE test1.shardtest
(
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  k int(10) unsigned NOT NULL DEFAULT '0',
  c char(120) NOT NULL DEFAULT '',
  pad char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY k (k)
) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest"'
 PARTITION BY KEY (id) 
(
 PARTITION pt1 COMMENT = 'srv "mariadbtest1"',
 PARTITION pt2 COMMENT = 'srv "mariadbtest2"',
 PARTITION pt3 COMMENT = 'srv "mariadbtest3"' 	
) ;

然後開啟 https://github.com/whuanle/write_share_database，找到 分片測試資料.sql 這個檔案，裡面有很多模擬資料。

你可以觀察到，三個資料庫例項的資料是不同的。

根據值範圍分片

分片方式的選擇在於 PARTITION BY 屬性，例如雜湊分片是根據一個鍵進行計算的，則配置命令為 PARTITION BY KEY (id)，如果是根據值範圍分片，則是 PARTITION BY range columns (<欄位名稱>)。

) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest"'
 PARTITION BY range columns (k)
(
 PARTITION pt1 values less than (5000) COMMENT = 'srv "mariadbtest1"',
 PARTITION pt2 values less than (5100) COMMENT = 'srv "mariadbtest2"'
 PARTITION pt3 values less than (5200) COMMENT = 'srv "mariadbtest3"'
) ;

根據列表分片

根據列表分片，一般是某個欄位，可以將資料劃分為不同型別，可以根據這個欄位的內容對資料進行分組。

) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest"'
 PARTITION BY list columns (k)
(
 PARTITION pt1 values in ('4900', '4901', '4902') COMMENT = 'srv "mariadbtest1"',
 PARTITION pt2 values in ('5000', '5100') COMMENT = 'srv "mariadbtest2"'
 PARTITION pt3 values in ('5200', '5300') COMMENT = 'srv "mariadbtest3"'
) ;

當資料的 k 欄位，值是 4900 、4901 或 4902 時，將被分片到 mariadbtest1 例項中。

貝聊億級資料庫分庫分表實踐
2017-08-15
資料庫
分散式資料庫中介軟體 MyCat | 分庫分表實踐
2020-08-20
分散式資料庫
基於代理的資料庫分庫分表框架 Mycat實踐
2018-12-19
資料庫框架
資料庫分庫分表
2016-11-04
資料庫
Docker實現Mariadb分庫分表、讀寫分離
2019-04-21
Docker
3.1 MYSQL分庫分表實踐
2020-01-12
MySql
【乾貨】分庫分表最佳實踐
2021-09-10
資料庫怎麼分庫分表
2019-11-12
資料庫
大資料資料庫讀寫分離分庫分表
2021-01-01
大資料資料庫
分庫分表插入資料
2020-12-09
資料庫分庫分表的總結
2019-02-16
資料庫
Mycat分庫分表的簡單實踐
2017-09-05
[資料庫][分庫分表]分庫分表之後，id主鍵如何處理
2019-02-19
資料庫
Java實戰：教你如何進行資料庫分庫分表
2021-07-28
Java資料庫
《資料儲存》之《分庫，分表》
2021-07-20
oracle分表效率,資料庫分庫分表是什麼，什麼情況下需要用分庫分表
2022-09-12
Oracle資料庫
分庫分表中介軟體的高可用實踐
2020-09-16
MySQL資料庫之分庫分表方案
2019-07-24
MySql資料庫
關係型資料庫分庫分表系列之一
2022-01-05
資料庫
實現報表資料分庫儲存
2019-12-18
分庫分表系列：分庫分表的前世今生
2022-03-19
淺談高效能資料庫叢集——分庫分表
2018-07-03
資料庫
報表資料分庫儲存
2019-12-10
MySQL 資料庫之網際網路常用分庫分表方案
2020-02-08
MySql資料庫
分散式資料庫中介軟體的實現原理介紹一：分庫分表
2018-07-03
分散式資料庫
分庫分表注意
2019-03-18
MySQL分庫分表
2015-10-30
MySql
徹底搞清MySQL分庫分表（垂直分庫，垂直分表，水平分庫，水平分表）
2021-02-01
MySql
效能優化之資料庫篇5-分庫分表與資料遷移
2021-07-29
優化資料庫
資料庫分庫分表之後，如何解決事務問題？
2020-07-06
資料庫
強！分庫分表與分散式資料庫技術選項分析
2022-12-08
分散式資料庫
百億級資料分庫分表後怎麼分頁查詢？
2022-12-05
徹底搞清分庫分表（垂直分庫，垂直分表，水平分庫，水平分表）
2020-10-27
mycat配置分庫分表
2021-06-29
Mycat分庫分表配置
2021-02-04
分庫分表總結
2020-12-09
Mycat分庫分表（一）
2020-11-15
常用分庫分表方案
2021-10-26