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

一：分库分表概念

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 应用场景

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

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 目录。

3.4.3 配置server.yaml

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配置文件中，已经配置