分庫(kù)分表

一般來(lái)說(shuō)，數(shù)據(jù)庫(kù)分庫(kù)分表，有以下做法：

• 按哈希分片：根據(jù)一條數(shù)據(jù)的標(biāo)識(shí)計(jì)算哈希值，將其分配到特定的數(shù)據(jù)庫(kù)引擎中；
• 按范圍分片：根據(jù)一條數(shù)據(jù)的標(biāo)識(shí)（一般是值），將其分配到特定的數(shù)據(jù)庫(kù)引擎中；
• 按列表分片：根據(jù)某些字段的標(biāo)識(shí)，如果符合條件則分配到特定的數(shù)據(jù)庫(kù)引擎中。

分庫(kù)分表的做法有很多種，例如編寫代碼庫(kù)，在程序中支持多數(shù)據(jù)庫(kù)，程序需要知道每個(gè)數(shù)據(jù)庫(kù)的地址，并要編寫代碼進(jìn)行支持；使用中間件將多個(gè)數(shù)據(jù)庫(kù)引擎連接起來(lái)，程序只需要知道中間件地址。

但是分庫(kù)分表后，因?yàn)槿我鈨蓚€(gè)表可能在不同的數(shù)據(jù)庫(kù)實(shí)例中，兩個(gè)表進(jìn)行連接查詢時(shí)，兩個(gè)數(shù)據(jù)庫(kù)實(shí)例之間的交互變得復(fù)雜起來(lái)，當(dāng)集群中的數(shù)據(jù)量較大時(shí)，便不能隨意 join 了，可能需要其他方式支撐聚合查詢。

分庫(kù)分表有優(yōu)點(diǎn)有缺點(diǎn)，這里就不再多說(shuō)，先學(xué)會(huì)再打算。

MariaDB Server 是開源的，目前最流行的關(guān)系型數(shù)據(jù)庫(kù)之一，MariaDB 是從 Mysql 的分支開發(fā)而來(lái)，一直保持對(duì) Mysql 的兼容性。因?yàn)榧坠俏牡氖召?gòu)，MySQL 屬于 Oracle 所有，存在閉源的可能，以及逐漸商業(yè)化，變得不清真，于是 Mysql之父創(chuàng)建了 MariaDB，目的是完全兼容 Mysql，并且開源、免費(fèi)。

MariaDB 使用 Spider 插件進(jìn)行分庫(kù)分表的支持，Spider 存儲(chǔ)引擎是一個(gè)內(nèi)置分片功能的存儲(chǔ)引擎。它支持分區(qū)和xa 事務(wù)，并允許處理不同 MariaDB 實(shí)例的表，就好像它們?cè)谕粋€(gè)實(shí)例上一樣。

請(qǐng)參考資料：https://mariadb.com/kb/en/spider/

在這篇文章中，筆者將使用 MariaDB Spider 進(jìn)行分庫(kù)分表的實(shí)踐。

部署 MariaDB 實(shí)例

為了更好地創(chuàng)建分庫(kù)分表實(shí)踐環(huán)境，這里需要三個(gè) “物理”數(shù)據(jù)庫(kù)，一個(gè)邏輯數(shù)據(jù)庫(kù)，即四個(gè) MariaDB 實(shí)例。MariaDB 實(shí)際占用的內(nèi)存并不大，筆者 4G 內(nèi)存的服務(wù)器裝了 Kubernetes ，用 Docker 部署四個(gè) MariaDB 數(shù)據(jù)庫(kù)，運(yùn)行速度正常，對(duì)于我們測(cè)試練習(xí) 4G 內(nèi)存足以。

四個(gè)數(shù)據(jù)庫(kù)的關(guān)系如圖：

其中，邏輯數(shù)據(jù)庫(kù)實(shí)例稱為 Spider Proxy Node，實(shí)際存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)庫(kù)實(shí)例被稱為 Backend Node。

典型的 Spider 部署具有無(wú)共享的集群架構(gòu)。該系統(tǒng)適用于任何廉價(jià)的硬件，并且對(duì)硬件或軟件的特定要求最低。它由一組計(jì)算機(jī)組成，具有一個(gè)或多個(gè) MariaDB 進(jìn)程，稱為節(jié)點(diǎn)。

存儲(chǔ)數(shù)據(jù)的節(jié)點(diǎn)將被設(shè)計(jì)為Backend Nodes，并且可以是任何 MariaDB、MySQL、Oracle 服務(wù)器實(shí)例，使用后端內(nèi)可用的任何存儲(chǔ)引擎。

Docker 部署

如果機(jī)器不夠，使用虛擬機(jī)部署便會(huì)顯得很麻煩，這里筆者使用 Docker 快速部署練習(xí)。

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

查看 MariaDB 鏡像版本列表：https://hub.docker.com/_/mariadb/

直接創(chuàng)建四個(gè)數(shù)據(jù)庫(kù)實(shí)例，其中一個(gè)是 Spider 實(shí)例，實(shí)例使用端口區(qū)分。

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

接著，進(jìn)入每個(gè)容器實(shí)例中，進(jìn)入 /etc/mysql/mariadb.conf.d 目錄，修改50-server.cnf文件，運(yùn)行遠(yuǎn)程訪問(wèn)數(shù)據(jù)庫(kù)實(shí)例。由于容器中沒有 nano、vi 這些編輯命令，因此可以使用下面的命令快速替換文件內(nèi)容：

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

然后查看每個(gè)容器的主機(jī)內(nèi) 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

虛擬機(jī)部署

這里需要四個(gè)虛擬機(jī)，每個(gè)虛擬機(jī)都需要先安裝 MariaDB 數(shù)據(jù)庫(kù)引擎以及一些工具包。

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

首先在每個(gè)虛擬安裝 MariaDB Community Server，即數(shù)據(jù)庫(kù)引擎。

如果使用虛擬機(jī)部署安裝，需要替換國(guó)內(nèi)鏡像源，以便快速下載需要的包， Centos 服務(wù)器，可以直接以下命令快速更新鏡像源，如果是 Debain 系列，可自行查找對(duì)應(yīng)的鏡像源。

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

接著，配置 MariaDB 官方的軟件包存儲(chǔ)庫(kù)：

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 社區(qū)服務(wù)器和軟件包依賴項(xiàng)：

sudo yum install MariaDB-server MariaDB-backup

接著，配置允許遠(yuǎn)程訪問(wèn)數(shù)據(jù)庫(kù)。

MariaDB 的配置文件都在 /etc/my.cnf 中，打開 /etc/my.cnf.d/ 目錄后，修改 server.cnf 文件，允許遠(yuǎn)程訪問(wèn)。找到 bind-address 屬性，去掉 # 。

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

如需了解每個(gè)配置的作用，請(qǐng)參考資料： https://mariadb.com/docs/deploy/community-spider/

修改密碼。因?yàn)槁銠C(jī)部署的數(shù)據(jù)庫(kù)，本身沒有密碼，所以需要手動(dòng)配置。

打開終端，執(zhí)行以下命令：

mysql -u root -p

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

然后執(zhí)行 quit; 退出數(shù)據(jù)庫(kù)操作終端。

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

然后重啟數(shù)據(jù)庫(kù)實(shí)例：

systemctl restart mariadb
systemctl status mariadb

接著檢查防火墻配置，或執(zhí)行 sudo iptables -F 清理防火墻配置。

MariaDB 配置

MariaDB 配置文件中，部分主要屬性的說(shuō)明如下如下：

MariaDB 基礎(chǔ)維護(hù)命令：

檢查每個(gè)實(shí)例

部署數(shù)據(jù)庫(kù)后，需要連接每個(gè)數(shù)據(jù)庫(kù)進(jìn)行測(cè)試，以便檢查數(shù)據(jù)庫(kù)是否正常。

配置 Spider

打開 mariadbspider 數(shù)據(jù)庫(kù)實(shí)例，執(zhí)行以下命令，加載 spider 插件，將其設(shè)置為 Spider 數(shù)據(jù)庫(kù)實(shí)例。

INSTALL SONAME 'ha_spider';

執(zhí)行命令查詢是否已經(jīng)啟動(dòng) Spider 插件：

SELECT * FROM mysql.plugin;

請(qǐng)參考資料：https://mariadb.com/kb/en/spider-installation/

遠(yuǎn)程表

MariaDB Spider 模式已經(jīng)搭建好了，這里開始進(jìn)行實(shí)踐。

在這個(gè)模式中，Spider 中的一個(gè)表對(duì)應(yīng)一個(gè)數(shù)據(jù)庫(kù)實(shí)例中的同名數(shù)據(jù)庫(kù)的同名表，即數(shù)據(jù)庫(kù)名稱系統(tǒng)，表名稱相同。

首先在 三個(gè)數(shù)據(jù)庫(kù)實(shí)例中，創(chuàng)建一個(gè)測(cè)試數(shù)據(jù)庫(kù)，名稱為 test1 ，然后執(zhí)行命令創(chuàng)建表：

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

然后在 mariadbspider 實(shí)例中，執(zhí)行命令，創(chuàng)建邏輯表，并將這個(gè)表綁定到 mariadbtest1 實(shí)例中。

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，另外注意端口，如果是容器訪問(wèn)容器，直接使用 3306。

如果沒有配置好，數(shù)據(jù)庫(kù)不對(duì)應(yīng)等，可能會(huì)出現(xiàn)：

> 1046 - No database selected
> 時(shí)間: 0.062s

然后在 mariadbspider 中，插入四條數(shù)據(jù)：

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

如果分別打開三個(gè)實(shí)例，你會(huì)發(fā)現(xiàn)，插入的數(shù)據(jù)只會(huì)出現(xiàn)在 mariadbtest1 中出現(xiàn)，因?yàn)檫@個(gè)表只綁定了它。你還可以在 mariadbspider 上對(duì)這個(gè)表進(jìn)行增刪查改，所有操作都會(huì)同步到對(duì)應(yīng)數(shù)據(jù)庫(kù)實(shí)例中。

基準(zhǔn)性能測(cè)試

SysBench 是一個(gè)模塊化、跨平臺(tái)和多線程的基準(zhǔn)測(cè)試工具，支持 Windows 和 Linux，用于評(píng)估對(duì)于在高負(fù)載下運(yùn)行數(shù)據(jù)庫(kù)的系統(tǒng)非常重要的操作系統(tǒng)參數(shù)。這個(gè)基準(zhǔn)測(cè)試套件的想法是，在不設(shè)置復(fù)雜的數(shù)據(jù)庫(kù)基準(zhǔn)或甚至根本不安裝數(shù)據(jù)庫(kù)的情況下，快速獲得系統(tǒng)性能的印象。它可以測(cè)試出：

• 文件 i/o 性能
• 調(diào)度器性能
• 內(nèi)存分配和傳輸速度
• POSIX 線程實(shí)現(xiàn)性能
• 數(shù)據(jù)庫(kù)服務(wù)器性能(OLTP 基準(zhǔn))

項(xiàng)目地址：https://github.com/akopytov/sysbench

Linux 可以直接安裝二進(jìn)制包。

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

首先，在當(dāng)前特定數(shù)據(jù)庫(kù)下創(chuàng)建模擬數(shù)據(jù)：

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'...

接著運(yùn)行測(cè)試：

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

清理模擬生成的數(shù)據(jù)：

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 跑測(cè)試時(shí)，可選參數(shù)如下：

• 使用–time=<SECONDS>運(yùn)行固定時(shí)間
• 使用–events=0對(duì)執(zhí)行的查詢不設(shè)置限制
• 使用–db-ps-mode=disable禁用準(zhǔn)備好的語(yǔ)句
• 使用–report-interval=<SECONDS>獲取繪圖點(diǎn)
• 用--histogram得到一個(gè)直方圖

sysbench 有三個(gè)過(guò)程或執(zhí)行模式：

1. prepare：為需要它們的測(cè)試執(zhí)行準(zhǔn)備操作，例如在磁盤上為fileio 測(cè)試創(chuàng)建必要的文件，或填充測(cè)試數(shù)據(jù)庫(kù)以進(jìn)行數(shù)據(jù)庫(kù)基準(zhǔn)測(cè)試。
2. run：運(yùn)行使用testname 參數(shù)指定的實(shí)際測(cè)試。此命令由所有測(cè)試提供。
3. cleanup：在創(chuàng)建一個(gè)的測(cè)試中測(cè)試運(yùn)行后刪除臨時(shí)數(shù)據(jù)。

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

加入后端數(shù)據(jù)庫(kù)

在遠(yuǎn)程表一節(jié)中，我們是在創(chuàng)建表的時(shí)候，再綁定一個(gè)數(shù)據(jù)庫(kù)實(shí)例，其實(shí)也可以提前配置多個(gè)數(shù)據(jù)庫(kù)實(shí)例到 Spider 中，下面是在 Spider 中執(zhí)行的配置命令：

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
);

哈希分片

在這一小節(jié)中，我們將一個(gè)表進(jìn)行分片，在插入數(shù)據(jù)時(shí)，數(shù)據(jù)自動(dòng)分片到三個(gè)數(shù)據(jù)庫(kù)實(shí)例中。

在三個(gè)數(shù)據(jù)節(jié)點(diǎn)數(shù)據(jù)庫(kù)中，在 test1 數(shù)據(jù)庫(kù)下，執(zhí)行命令，創(chuàng)建表：

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)
)

此時(shí)，三個(gè)數(shù)據(jù)庫(kù)實(shí)例都具有相同的表。

然后在 mariadbspider 實(shí)例中，執(zhí)行命令，創(chuàng)建邏輯表，并將此表通過(guò)切片的模式，連接到三個(gè)數(shù)據(jù)庫(kù)實(shí)例中。

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，找到 分片測(cè)試數(shù)據(jù).sql 這個(gè)文件，里面有很多模擬數(shù)據(jù)。

你可以觀察到，三個(gè)數(shù)據(jù)庫(kù)實(shí)例的數(shù)據(jù)是不同的。

根據(jù)值范圍分片

分片方式的選擇在于 PARTITION BY 屬性，例如哈希分片是根據(jù)一個(gè)鍵進(jìn)行計(jì)算的，則配置命令為 PARTITION BY KEY (id)，如果是根據(jù)值范圍分片，則是 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"'
) ;

根據(jù)列表分片

根據(jù)列表分片，一般是某個(gè)字段，可以將數(shù)據(jù)劃分為不同類型，可以根據(jù)這個(gè)字段的內(nèi)容對(duì)數(shù)據(jù)進(jìn)行分組。

) 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"'
) ;

當(dāng)數(shù)據(jù)的 k 字段，值是 4900 、4901 或 4902 時(shí)，將被分片到 mariadbtest1 實(shí)例中。

到此這篇關(guān)于MariaDB Spider 數(shù)據(jù)庫(kù)分庫(kù)分表實(shí)踐的文章就介紹到這了,更多相關(guān)MariaDB Spider 分庫(kù)分表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！