mysql分库分表概念及原理、ShardingSphere实现mysql集群分库分表&读写分离

爱被打了一巴掌 2024-03-27 14:24 18阅读 0赞

## 一：分库分表概念 ##

### 1.1 为什么要对数据库进行分表 ###

索引的极限：单表数据量达到几十万或上百万以上，使用索引性能提升也不明显。  
分表使用门槛：单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。  
分表适用场景：分库分表主要用于应对当前互联网常见的两个场景——大数据量和高并发。

### 1.2 什么是分库分表 ###

分库分表方案是对关系型数据库数据存储和访问机制的一种补充。  
分库：将一个库的数据拆分到多个相同的库中，访问的时候访问一个库  
分表：把一个表的数据放到多个表中，操作对应的某个表就行

### 1.3 分库分表的方式 ###

#### 1.3.1 垂直拆分 ####

*  1）垂直分表  
    也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。 一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。
 *  2）垂直分库  
    垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。为什么？ 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。

数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

#### 1.3.2 水平拆分 ####

*  1）水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。

*  2）水平分库分表

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

3）水平分库分表切分规则

1.  RANGE：从0到10000一个表，10001到20000一个表；
2.  HASH取模：一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
3.  地理区域：比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
4.  时间：按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

### 1.4 分表所造成的问题 ###

1）join 操作  
水平分表后，数据分散在多个表中，如果需要与其他表进行 join 查询，需要在业务代码或者数据库中间件中进行多次 join 查询，然后将结果合并。  
2）count() 操作  
水平分表后，虽然物理上数据分散到多个表中，但某些业务逻辑上还是会将这些表当作一个表来处理。例如，获取记录总数用于分页或者展示，水平分表前用一个 count() 就能完成的操作，在分表后就没那么简单了。常见的处理方式有两种：count() 相加和记录数表  
3）order by 操作  
水平分表后，数据分散到多个子表中，排序操作无法在数据库中完成，只能由业务代码或者数据库中间件分别查询每个子表中的数据，然后汇总进行排序。

## 二：ShardingSphere ##

### 2.1 官网地址 ###

`https://shardingsphere.apache.org/document/current/cn/overview/`

### 2.2 什么是 ShardingSphere ###

Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

Apache ShardingSphere 设计哲学为 Database Plus，旨在构建异构数据库上层的标准和生态。 它关注如何充分合理地利用数据库的计算和存储能力，而并非实现一个全新的数据库。 它站在数据库的上层视角，关注它们之间的协作多于数据库自身。

### 2.3 快速入门 ###

#### 2.3.1 ShardingSphere-JDBC ####

#### 应用 ####

1）应用场景  
Apache ShardingSphere-JDBC 可以通过 Java 和 YAML 这 2 种方式进行配置，开发者可根据场景选择适合的配置方式。  
2）使用限制  
目前仅支持 JAVA 语言  
3）前提条件  
开发环境需要具备 Java JRE 8 或更高版本。

#### 操作步骤 ####

1）引入 maven 依赖

<dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core</artifactId>
        <version>${latest.release.version}</version>
    </dependency>

注意：请将 $\{latest.release.version\} 更改为实际的版本号。  
2）创建 YAML 配置文件

# JDBC 逻辑库名称。在集群模式中，使用该参数来联通 ShardingSphere-JDBC 与 ShardingSphere-Proxy。
    # 默认值：logic_db
    databaseName (?):
    mode:
    dataSources:
    rules:
    - !FOO_XXX
        ...
    - !BAR_XXX
        ...
    props:
      key_1: value_1
      key_2: value_2

3）以 spring boot 为例，编辑 application.properties

# 配置 DataSource Driver
    spring.datasource.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver
    # 指定 YAML 配置文件
    spring.datasource.url=jdbc:shardingsphere:classpath:xxx.yaml

#### 2.3.2 ShardingSphere-Proxy ####

我们就使用ShardingSphere-Proxy完成数据库的分库分表

## 三：ShardingSphere-Proxy ##

### 3.1 应用场景 ###

![在这里插入图片描述][c069ce26200948a3badf5f491109dad1.png]  
ShardingSphere-Proxy 的定位为透明化的数据库代理，理论上支持任何使用 MySQL、PostgreSQL、openGauss 协议的客户端操作数据，对异构语言、运维场景更友好。

### 3.2 使用限制 ###

ShardingSphere-Proxy 对系统库/表（如 information\_schema、pg\_catalog）支持有限，通过部分图形化数据库客户端连接 Proxy 时，可能客户端或 Proxy 会有错误提示。可以使用命令行客户端（mysql、psql、gsql 等）连接 Proxy 验证功能。

### 3.3 前提条件 ###

使用 Docker 启动 ShardingSphere-Proxy 无须额外依赖。 使用二进制分发包启动 Proxy，需要环境具备 Java JRE 8 或更高版本。

### 3.4 操作步骤 ###

#### 3.4.1 下载 ####

[https://dlcdn.apache.org/shardingsphere/5.3.1/apache-shardingsphere-5.3.1-shardingsphere-proxy-bin.tar.gz][https_dlcdn.apache.org_shardingsphere_5.3.1_apache-shardingsphere-5.3.1-shardingsphere-proxy-bin.tar.gz]

#### 3.4.2 引入依赖 ####

1）如果后端连接 PostgreSQL 或 openGauss 数据库，不需要引入额外依赖。  
2）如果后端连接 MySQL 数据库，请下载 mysql-connector-java-5.1.47.jar 或者 mysql-connector-java-8.0.11.jar，并将其放入 %SHARDINGSPHERE\_PROXY\_HOME%/ext-lib 目录。  
![在这里插入图片描述][79ccaa1f8dc64ec78451ae1d02acf332.png]

#### 3.4.3 配置server.yaml ####

![在这里插入图片描述][d10982ff4cd84ab8ba7a1d416156940f.png]  
1）添加认证信息

authority:
     users:
       - user: root@%
         password: root
       - user: sharding
         password: sharding
     privilege:
       type: ALL_PERMITTED

2）配置属性

props:
    #  max-connections-size-per-query: 1
     kernel-executor-size: 16  # 线程数大小
    #  proxy-frontend-flush-threshold: 128  # The default value is 128.
    #  proxy-hint-enabled: false
     sql-show: false   #是否展示sql

#### 3.4.4 配置config-sharding.yaml（分库分表） ####

1）配置数据源

databaseName: sharding_db
    #
    dataSources:
     ds_0:
       url: jdbc:mysql://192.168.0.1:3316/demo_ds_0
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
     ds_1:
       url: jdbc:mysql://192.168.0.1:3316/demo_ds_1
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1

2）定义分库分表规则

rules:
    - !SHARDING
     tables:
       t_order:
         actualDataNodes: ds_${
        0..1}.t_order_${
        0..1}
         tableStrategy:
           standard:
             shardingColumn: order_id
             shardingAlgorithmName: t_order_inline
         keyGenerateStrategy:
           column: order_id
           keyGeneratorName: snowflake
         auditStrategy:
           auditorNames:
             - sharding_key_required_auditor
           allowHintDisable: true
       t_order_item:
         actualDataNodes: ds_${
        0..1}.t_order_item_${
        0..1}
         tableStrategy:
           standard:
             shardingColumn: order_id
             shardingAlgorithmName: t_order_item_inline
         keyGenerateStrategy:
           column: order_item_id
           keyGeneratorName: snowflake
     bindingTables:
       - t_order,t_order_item
     defaultDatabaseStrategy:
       standard:
         shardingColumn: user_id
         shardingAlgorithmName: database_inline
     defaultTableStrategy:
       none:
     defaultAuditStrategy:
       auditorNames:
         - sharding_key_required_auditor
       allowHintDisable: true

#### 3.4.5 配置config-readwrite-splitting.yaml（读写分离） ####

1）配置主库和从库

databaseName: sharding_db
    #
    dataSources:
     master_0_ds:
       url: jdbc:mysql://127.0.0.1:3316/demo_ds_0?serverTimezone=UTC&useSSL=false
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
     slave_ds_0:
       url: jdbc:mysql://127.0.0.1:3317/demo_ds_0?serverTimezone=UTC&useSSL=false
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
    
     master_1_ds:
       url: jdbc:mysql://127.0.0.1:3316/demo_ds_1?serverTimezone=UTC&useSSL=false
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
     slave_ds_1:
       url: jdbc:mysql://127.0.0.1:3317/demo_ds_1?serverTimezone=UTC&useSSL=false
       username: root
       password: root
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1

2）定义规则

rules:
    - !READWRITE_SPLITTING
     dataSources:
       ms_ds1:
         staticStrategy:
           writeDataSourceName: master_0_ds
           readDataSourceNames:
             - slave_ds_0
         loadBalancerName: random
    
       ms_ds2:
         staticStrategy:
           writeDataSourceName: master_1_ds
           readDataSourceNames:
             - slave_ds_1
         loadBalancerName: random
     loadBalancers:
       random:
         type: RANDOM

#### 3.4.6 修改服务器mysql主节点和从节点的配置 ####

1）停掉mysql的主从节点  
`docker stop mysql-master mysql-slaver-01`  
2）修改master配置文件  
`cd /data/mydata/mysql/master/conf/`  
`vi my.cnf`  
添加如下配置

binlog-do-db=demo_ds_0
    binlog-do-db=demo_ds_1

3）修改slaver配置文件  
`cd /data/mydata/mysql/slaver/conf/`  
`vi my.cnf`  
添加如下配置

binlog-do-db=demo_ds_0
    binlog-do-db=demo_ds_1

启动主库和从库  
`docker start mysql-master mysql-slaver-01`  
4）在主库和从库之中新建demo\_ds\_0和demo\_ds\_1库

#### 3.4.7 连接ShardingSphere-Proxy ####

1）在bin目录下进入cmd  
2）用3388端口启动  
`start.bat 3388`  
3）连接用户名和密码在server配置文件中，已经配置  
![在这里插入图片描述][97e4549c187642caa6f7eda6fd46cba3.png]

[c069ce26200948a3badf5f491109dad1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/bf4500ed472b4757ba77a5205cb9c0c7.png
[https_dlcdn.apache.org_shardingsphere_5.3.1_apache-shardingsphere-5.3.1-shardingsphere-proxy-bin.tar.gz]: https://dlcdn.apache.org/shardingsphere/5.3.1/apache-shardingsphere-5.3.1-shardingsphere-proxy-bin.tar.gz
[79ccaa1f8dc64ec78451ae1d02acf332.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/686b44479f22447aa4fb307eb61f240d.png
[d10982ff4cd84ab8ba7a1d416156940f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7fc9708ae2cd4fd1bca745e0476e2bf4.png
[97e4549c187642caa6f7eda6fd46cba3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e5142d7bbf474e27928d33b14081e952.png