基於ShardingJDBC實現資料庫讀寫分離

2022-01-08 強哥分享乾貨

在大多數業務場景下，當我們的數據量達到一定數量級TB級或者PB級的時候，對數據的讀寫操作將會變得相當困難，這也將會造成系統性能瓶頸，系統的吞吐量將會明顯降低。想要解決這一瓶頸問題，可能最簡單的有三種解決方案：

增加單個資料庫節點的CUP和內存數量；

採用分布式資料庫，對數據進行分庫分表；

採用簡單的讀寫分離技術，降低單個資料庫節點的負荷；

在上一篇文章ShardingJDBC實現多資料庫節點分庫分表 中，我將大致講解了一下如何實現對資料庫進行分庫分表操作，本篇文章我們將大致講解下如何通過ShardingJDBC對資料庫進行讀寫分離操作。當然，關於讀寫分離的技術在實施的過程中有以下幾種方法：

基於公有雲（阿里雲）的基礎設施服務天然支持資料庫的讀寫分離，只需對服務做簡單的配置即可；

基於原始碼的硬編碼方式，採用雙數據源（datasource）；

基於中間件的方式，例如mycat等開源中間件；

基於數據源代理jar包+配置項的方式，例如：shardingsphere等；

下面我們將通過一個例子大概講解下，ShardingJDBC是如何實現資料庫的讀寫分離，文末將添附原始碼地址。

ShardingJDBC只能實現數據的讀寫分離技術，它不能實現資料庫的主從複製功能，所以資料庫的主從複製功能需要採用另外的技術實現，基於公有雲資料庫或者自己託管資料庫對資料庫進行主從複製配置。

一，首先我們採用Docker快速搭建一套資料庫的主從複製環境，也可用參考這篇文章MySQL資料庫讀寫分離技術實踐 實施

Master資料庫配置文件如下：

root@ubuntu18:~# cat master.cnf
[mysqld]
# master server id
server-id = 1
# bin log
log_bin = mysql-master-bin

Slave資料庫配置文件如下：

root@ubuntu18:~# cat slave.cnf
[mysqld]
# slave server id
server-id = 2
# bin log
log_bin = mysql-slave-bin
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1

分別啟動兩個Docker資料庫：

root@ubuntu18:~# docker run --name master -p 3310:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:master
d34d8767560b3f08e3eb4205ac80388456c546dde524c9c2707496f22e9c5523

root@ubuntu18:~# docker run --link master:master --name slave -p 3320:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:slave
664857039ab3a933ff7b12be50be36244ea14712d6dc2b495c815510acef7844

root@ubuntu18:~# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
664857039ab3 mysql:slave "docker-entrypoint.s…" 5 seconds ago Up 4 seconds 33060/tcp, 0.0.0.0:3320->3306/tcp slave
d34d8767560b mysql:master "docker-entrypoint.s…" 39 seconds ago Up 38 seconds 33060/tcp, 0.0.0.0:3310->3306/tcp master

master資料庫暴露3310埠；

slave資料庫暴露3320埠；

配置Master資料庫：

mysql> create user 'repl'@'%' identified by 'repl-pwd';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+++----+---+----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+++----+---+----+
| mysql-master-bin.000003 | 855 | | | |
+++----+---+----+
1 row in set (0.00 sec)

mysql> alter user 'repl'@'%' identified by 'repl-pwd' password expire never;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'repl'@'%' identified with mysql_native_password by 'repl-pwd';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置Slave資料庫：

mysql> change master to master_host='master', master_user='repl', master_password='repl-pwd', master_log_file='mysql-master-bin.000003', master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000003
Read_Master_Log_Pos: 1640
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 1869
Relay_Master_Log_File: mysql-master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1640
Relay_Log_Space: 2078
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 4143f5a8-0a10-11eb-8197-0242ac110003
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)

二，準備一張表結構如下：

create database ds;
use ds;
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE IF NOT EXISTS `t_order`
(
`order_id` INT UNSIGNED,
`order_name` VARCHAR(255) NOT NULL,
`order_date` VARCHAR(255) NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

三，新建一個SpringBoot項目：

ShardingJDBC配置項如下：

debug: true
spring:
shardingsphere:
datasource:
names: master,slave
master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://10.0.0.8:3310/ds
username: root
password: root

slave:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://10.0.0.8:3320/ds
username: root
password: root

masterslave:
load-balance-algorithm-type: round_robin
name: ms
master-data-source-name: master
slave-data-source-names: slave
props:
sql:
show: true
management:
health:
db:
enabled: false

ShardingJDBC支持一主多從的場景，也就是說可以有多個slave資料庫，但是只能存在一個master資料庫；

從庫負載均衡算法類型，可選值：ROUND_ROBIN，RANDOM；

本例中我們採用一主一從的情況；

master資料庫採用3310埠，slave資料庫採用3320埠；

DAO層：

@Repository
public interface OrderRepository extends JpaRepository<OrderEntity, Integer> {
}

實體類：

@Data
@Entity
@Table(name = "t_order")
public class OrderEntity {
@Id
@Column(name = "order_id")
private int id;
@Column(name = "order_name")
private String name;
@Column(name = "order_date")
private String date;
}

Service層：

@Service
public class ShardingReadWriteService {
@Autowired
private OrderRepository orderRepository;

public void save(int i) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
OrderEntity orderEntity = new OrderEntity();
orderEntity.setId(i);
orderEntity.setName(UUID.randomUUID().toString());
orderEntity.setDate(sdf.format(new Date()));
orderRepository.save(orderEntity);
}

public List<OrderEntity> list() {
return orderRepository.findAll();
}
}

通過Junit進行測試：

@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingReadWriteTest {
@Autowired
private ShardingReadWriteService shardingReadWriteService;

@Test
public void save() {
for (int i = 0; i < 100; i++) {
shardingReadWriteService.save(i);
}
System.out.println("save done!");
}

@Test
public void list() {
List<OrderEntity> list = shardingReadWriteService.list();
System.out.println(JSONArray.fromObject(list));
System.out.println("total count:" + list.size());
}
}

在IDEA中運行插入數據單元測試效果如下：

在IDEA中運行查詢數據單元測試效果如下：

對比兩個測試結果，我們可以清楚的發現：

參考：

https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-spring-boot/#讀寫分離

demo源碼：

https://github.com/bq-xiao/sharding-jdbc-demo.git

不積跬步，無以至千裡；不積小流，無以成江海！

相關焦點

  • Sharding-jdbc的實戰入門之水平分表(一)
    >多個實例存在同一臺伺服器上，只是解決了資料庫最大連接數的問題，但是 io（資料庫數據是存儲在硬碟上，每次獲取都需要去硬碟把數據撈出來）,cpu 等伺服器資源瓶頸並沒有解決。= com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driverspring.shardingsphere.datasource.m1.url = jdbc:mysql:spring.shardingsphere.datasource.m1
  • Spring Boot 採用Sharding-JDBC 實現Mybaits的分庫分表功能
    業內其實也有很多比較成熟的解決方案，如：Cobar、Cobar-client、TDDL、Sharding-JDBC等，這次就拿當當網開源的Sharding-JDBC來實現基於Mybatis的分庫分表功能。
  • 分庫分表【Sharding-JDBC】入門與項目實戰
    作者：六點半起床https://juejin.im/post/5edc9f3ff265da76bc760726最近項目中不少表的數據量越來越大，並且導致了一些資料庫的性能問題。因此想藉助一些分庫分表的中間件，實現自動化分庫分表實現。調研下來，發現Sharding-JDBC目前成熟度最高並且應用最廣的Java分庫分表的客戶端組件。
  • 一文讀懂 MySQL 主從複製讀寫分離
    為什麼要採用主從複製讀寫分離的架構？如何實現？有什麼缺點？讓我們帶著這些問題開始這段學習之旅吧！主從複製、讀寫分離一般是一起使用的。目的很簡單，就是為了提高資料庫的並發性能。你想，假設是單機，讀寫都在一臺MySQL上面完成，性能肯定不高。如果有三臺MySQL，一臺mater只負責寫操作，兩臺salve只負責讀操作，性能不就能大大提高了嗎？
  • 分庫分表常見概念解讀+Sharding-JDBC實戰
    4、讀寫分離不難發現大部分主流的關係型資料庫都提供了主從架構的高可用方案，而我們需要實現 讀寫分離 + 分庫分表，讀庫與寫庫都要做分庫分表處理，後邊會有具體實戰案例。sharding-jdbc的兼容性也非常強大，適用於任何基於 JDBC 的 ORM 框架，如：JPA， Hibernate，Mybatis，Spring JDBC Template 或直接使用的 JDBC。完美兼容任何第三方的資料庫連接池，如：DBCP， C3P0， BoneCP，Druid， HikariCP 等，幾乎對所有關係型資料庫都支持。
  • 數據量大了一定要分表,分庫分表Sharding-JDBC入門與項目實戰
    最近項目中不少表的數據量越來越大，並且導致了一些資料庫的性能問題。因此想藉助一些分庫分表的中間件，實現自動化分庫分表實現。調研下來，發現Sharding-JDBC目前成熟度最高並且應用最廣的Java分庫分表的客戶端組件。
  • 基於springboot的mysql實現讀寫分離
    常用的有以下的實現方法:讀寫分離、加緩存、主從架構集群、分庫分表等，在網際網路應用中,大部分都是讀多寫少的場景,設置兩個庫,主庫和讀庫,主庫的職能是負責寫,從庫主要是負責讀,可以建立讀庫集群,通過讀寫職能在數據源上的隔離達到減少讀寫衝突、釋壓資料庫負載、保護資料庫的目的
  • 終於學會了 MySQL 主從配置和讀寫分離
    MySQL 讀寫分離的？（就不展開說了，因為再展開就露餡兒了，我也不會） ShardingSphere-JDBC 實現讀寫分離主從分離之後，就要做系統的讀寫分離了，寫操作走主節點，讀操作走從節點。接下來簡單演示一下 ShardingSphere-JDBC + Spring Boot + MyBatis 實現簡單的讀寫分離。
  • 利用 ShardingSphere-JDBC 實現分庫分表實踐
    =ds0,ds1    # 這是數據源0的配置    spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource    spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test
  • Sharding-jdbc教程:Mysql資料庫主從搭建
    這是系列文章Sharding-jdbc文章的第一篇，本篇文章主要講述如何搭建Mysql的主從。搭建環境為centos 7.5，資料庫版本為5.7。需要三臺虛擬機，一主兩從，讀者可以在自己的電腦上創建虛擬機，也可以在雲服務商買三臺，按小時計費，一小時幾毛錢，比較實惠。
  • 敏感數據,「一鍵脫敏」,Sharding Sphere 完美搞定
    在真實業務場景中，資料庫中經常需要存儲某些客戶的關鍵性敏感信息如：身份證號、銀行卡號、姓名、手機號碼等，此類信息按照合規要求，通常需要實現加密存儲以滿足合規要求。其基本原理是對用戶輸入的SQL進行解析攔截，並依靠用戶的脫敏配置進行SQL的改寫，從而實現對欄位的加密及加密欄位的解密。最終實現對用戶無感的加解密存儲、查詢。
  • SpringBoot + MyBatis + MySQL讀寫分離實踐!
    引言讀寫分離要做的事情就是對於一條SQL該選擇哪個資料庫去執行，至於誰來做選擇資料庫這件事兒，無非兩個，要麼中間件幫我們做，要麼程序自己做。因此，一般來講，讀寫分離有兩種實現方式。第一種是依靠中間件（比如：MyCat），也就是說應用程式連接到中間件，中間件幫我們做SQL分離；第二種是應用程式自己去做分離。
  • Sharding-Sphere 3.0.0.M4 正式發布 - OSCHINA - 中文開源技術...
    他們均提供標準化的數據分片、分布式事務和資料庫治理功能，可適用於如Java同構、異構語言、容器、雲原生等各種多樣化的應用場景。Sharding-Proxy支持資料庫多Schema結構。資料庫治理鏈路追蹤功能增強。
  • SpringBoot+MySQL實現讀寫分離就這麼簡單,幾步搞定!
    常用的有以下的實現方法:讀寫分離、加緩存、主從架構集群、分庫分表等，在網際網路應用中,大部分都是讀多寫少的場景,設置兩個庫,主庫和讀庫。本篇博文將聚焦讀寫分離,探討如何實現它。目錄主從同步的局限性：這裡分為主資料庫和從資料庫,主資料庫和從資料庫保持資料庫結構的一致,主庫負責寫,當寫入數據的時候,會自動同步數據到從資料庫；從資料庫負責讀,當讀請求來的時候,直接從讀庫讀取數據,主資料庫會自動進行數據複製到從資料庫中。
  • 一文快速入門分庫分表中間件 Sharding-JDBC(必修課)
    分片算法和分片策略的關係注意：sharding-jdbc 並沒有直接提供分片算法的實現，需要開發者根據業務自行實現。所以如果想在 JDBC 層面實現數據分片就必須對現有的 API 進行功能拓展，而 Sharding-JDBC 正是基於這種思想，重寫了 JDBC 規範並完全兼容了 JDBC 規範。
  • SpringBoot+MyBatis+MySQL讀寫分離(實例)
    引言讀寫分離要做的事情就是對於一條SQL該選擇哪個資料庫去執行，至於誰來做選擇資料庫這件事兒，無非兩個，要麼中間件幫我們做，要麼程序自己做。因此，一般來講，讀寫分離有兩種實現方式。第一種是依靠中間件（比如：MyCat），也就是說應用程式連接到中間件，中間件幫我們做SQL分離；第二種是應用程式自己去做分離。
  • 想用資料庫「讀寫分離」 請先明白「讀寫分離」解決什麼問題
    有一些技術同學可能對於「讀寫分離」了解不多，認為資料庫的負載問題都可以使用「讀寫分離」來解決。這其實是一個非常大的誤區，我們要用「讀寫分離」，首先應該明白「讀寫分離」是用來解決什麼樣的問題的，而不是僅僅會用這個技術。什麼是讀寫分離？
  • 資料庫常用架構方案
    一、資料庫架構原則二、常見的架構方案方案一：主備架構，只有主庫提供讀寫服務，備庫冗餘作故障轉移用jdbc:mysql://vip:3306/xxdb1、高可用分析： 高可用，主庫掛了，keepalive（只是一種工具）會自動切換到備庫。
  • Spring JDBC訪問關係型資料庫
    Spring JDBC訪問關係型資料庫吧。，它的作用是向資料庫user表中增加一條記錄('init', '123456')並獲取自增id。="close"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/>
  • java中資料庫:JDBC的使用
    JDBC是java中提供的一套資料庫編程API，它定義了一套用來訪問資料庫的標準Java類庫(位於java.sql和javax.sql