mysql
                mysql-connector-java
                8.0.19
            

            
            
                org.apache.shardingsphere
                sharding-jdbc-spring-boot-starter
                4.1.1
            

            
                com.alibaba
                druid-spring-boot-starter
                1.2.8
            

            
                org.springframework.boot
                spring-boot-starter-jdbc
                2.5.3
            

            
                org.mybatis.spring.boot
                mybatis-spring-boot-starter
                2.2.0
            

## -----------shardingsphere---------------------
spring.shardingsphere.datasource.names = master,slave
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://ip.ip:3306/daname?autoReconnect=true&characterEncoding=UTF8&useUnicode=true&rewriteBatchedStatements=true&useSSL=false
spring.shardingsphere.datasource.master.username=admin
spring.shardingsphere.datasource.master.password=admin


#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
## >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.master.initialSize=5
spring.shardingsphere.datasource.master.minIdle=5
spring.shardingsphere.datasource.master.maxActive=20
## >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.master.maxWait=60000
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.master.timeBetweenEvictionRunsMillis=60000
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.master.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.master.validationQuery=SELECt 1 FROM DUAL
spring.shardingsphere.datasource.master.testWhileIdle=true
spring.shardingsphere.datasource.master.testonBorrow=false
spring.shardingsphere.datasource.master.testonReturn=false
# u6253u5F00PSCacheuFF0Cu5E76u4E14u6307u5B9Au6BCFu4E2Au8FDEu63A5u4E0APSCacheu7684u5927u5C0FuFF08u9632u706Bu5899u8FD9u4E00u5757u4E00u5B9Au4E0Du80FDu5F00uFF0Csqliteu4E0Du652Fu6301uFF0Cu4F1Au5BFCu81F4u5947u602Au7684u5F02u5E38uFF09
spring.shardingsphere.datasource.master.poolPreparedStatements=false
spring.shardingsphere.datasource.master.maxPoolPreparedStatementPerConnectionSize=20
# u914Du7F6Eu76D1u63A7u7EDFu8BA1u62E6u622Au7684filtersuFF0Cu53BBu6389u540Eu76D1u63A7u754Cu9762sqlu65E0u6CD5u7EDFu8BA1uFF0C'wall'u7528u4E8Eu9632u706Bu5899
spring.shardingsphere.datasource.master.filters=stat,wall,slf4j,config


#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave.url=jdbc:mysql://ip.ip:3306/daname?autoReconnect=true&characterEncoding=UTF8&useUnicode=true&rewriteBatchedStatements=true&useSSL=false
spring.shardingsphere.datasource.slave.username=admin
spring.shardingsphere.datasource.slave.password=admin

#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
## >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.slave.initialSize=5
spring.shardingsphere.datasource.slave.minIdle=5
spring.shardingsphere.datasource.slave.maxActive=20
## >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.slave.maxWait=60000
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.slave.timeBetweenEvictionRunsMillis=60000
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.datasource.slave.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.slave.validationQuery=SELECt 1 FROM DUAL
spring.shardingsphere.datasource.slave.testWhileIdle=true
spring.shardingsphere.datasource.slave.testonBorrow=false
spring.shardingsphere.datasource.slave.testonReturn=false
# u6253u5F00PSCacheuFF0Cu5E76u4E14u6307u5B9Au6BCFu4E2Au8FDEu63A5u4E0APSCacheu7684u5927u5C0FuFF08u9632u706Bu5899u8FD9u4E00u5757u4E00u5B9Au4E0Du80FDu5F00uFF0Csqliteu4E0Du652Fu6301uFF0Cu4F1Au5BFCu81F4u5947u602Au7684u5F02u5E38uFF09
spring.shardingsphere.datasource.slave.poolPreparedStatements=false
spring.shardingsphere.datasource.slave.maxPoolPreparedStatementPerConnectionSize=20
# u914Du7F6Eu76D1u63A7u7EDFu8BA1u62E6u622Au7684filtersuFF0Cu53BBu6389u540Eu76D1u63A7u754Cu9762sqlu65E0u6CD5u7EDFu8BA1uFF0C'wall'u7528u4E8Eu9632u706Bu5899
spring.shardingsphere.datasource.slave.filters=stat,wall,slf4j,config


#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave
spring.shardingsphere.props.sql.show=false
## -----------shardingsphere---------------------

以上是读写分离：

以下是分库分表：

转载：https://segmentfault.com/a/1190000020220240?utm_source=tag-newest

订单业务分析

表结构:

CREATE TABLE `order_0` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `money` int(11) NOT NULL,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL,
  `modified_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_id` (`order_id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4

问题分析:

由于是单  key 业务, 只需要通过  uid 来查询, 则按照  uid 来做分表因子

分库中间件

经过一些权衡, 还是准备使用  shardingsphere ,社区活跃度也比较不错

开始使用

jdk 8 , shardingsphere 版本 4.0.0-RC2

路由规则

• 分 2 个库, 一个库 4 张表
• 路由计算, 库: uid % 2个库
• 路由计算, 表: uid / 2个库 % 4张表 (取商后再取余, 这里要注意取商有小数需要强转为整型后再取余)

uid	uid % 2 (库)	uid / 2 % 4 (表)
1	1	0
2	0	1
3	1	1
4	0	2
5	1	2
6	0	3
7	1	3
8	0	0

MySQL 结构

• 先创建两个库, db0 和 db1
• 把下面的结构分别导入两个库中

CREATE TABLE `order_0` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `money` int(11) NOT NULL,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL,
  `modified_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_id` (`order_id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4
CREATE TABLE `order_1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `money` int(11) NOT NULL,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL,
  `modified_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_id` (`order_id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4
CREATE TABLE `order_2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `money` int(11) NOT NULL,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL,
  `modified_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_id` (`order_id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4
CREATE TABLE `order_3` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `money` int(11) NOT NULL,
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL,
  `modified_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_id` (`order_id`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4

spring boot 的 pom.xml

    org.springframework.boot
    spring-boot-starter-web




    org.projectlombok
    lombok
    true




    com.alibaba
    druid
    1.1.12




    mysql
    mysql-connector-java
    runtime




    org.mybatis.spring.boot
    mybatis-spring-boot-starter
    1.3.2



    org.apache.shardingsphere
    sharding-jdbc-spring-boot-starter
    4.0.0-RC2



    org.springframework.boot
    spring-boot-starter-test
    test

spring boot 的 application-sharding-databases.properties

spring.shardingsphere.datasource.names=db0,db1

spring.shardingsphere.datasource.db0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db0.url=jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.db0.username=root
spring.shardingsphere.datasource.db0.password=root

spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root

# 是否显示SQL语句
spring.shardingsphere.props.sql.show=true

# 配置数据库和表
spring.shardingsphere.sharding.tables.order.actual-data-nodes=db$->{0..1}.order_$->{0..1}
# 配置根据哪个字段选择数据库
spring.shardingsphere.sharding.tables.order.database-strategy.inline.sharding-column=uid
# 配置选择哪个数据库的规则
spring.shardingsphere.sharding.tables.order.database-strategy.inline.algorithm-expression=db$->{uid % 2}
# 配置选择根据哪个字段选择表
spring.shardingsphere.sharding.tables.order.table-strategy.inline.sharding-column=uid
# 配置选择哪个表的规则
spring.shardingsphere.sharding.tables.order.table-strategy.inline.algorithm-expression=order_$->{(Integer)(uid / 2) % 4}

启动 java web 程序

需要注意, 查询订单详情需要带上用户 uid, 用来路由到对应的库和表

• 创建订单: http://localhost:8080/order/create?uid=1
• 订单列表:http://localhost:8080/order/lists?uid=1&page=1&limit=1
• 订单详情:http://localhost:8080/order/detail?uid=1&orderId=373591446294364161

 JED 弹性数据库

1、分片库 创建 不分片表   分片语句在表创建语句前

set @sharding = 'order_1 singleShard'  //order表不分片，singleShard是固定值

CREATE TABLE `order` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `order_id` bigint(20) unsigned NOT NULL, `uid` bigint(20) unsigned NOT NULL, `money` int(11) NOT NULL, `status` tinyint(3) unsigned NOT NULL DEFAULT '0', `create_time` datetime NOT NULL, `modified_time` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_order_id` (`order_id`), KEY `idx_uid` (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4

2、分片库 创建 分片表  分片语句在表创建语句前、全局唯一主键在表创建语句后（如果不设置，每个分片的自增id都从1开始）

分片键不能更新，分片键不能出现在update set 的字段中  注意！！！！！

set @sharding = 'order uid hash' // order表根据uid进行分片

CREATE TABLE `order` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `order_id` bigint(20) unsigned NOT NULL, `uid` bigint(20) unsigned NOT NULL, `money` int(11) NOT NULL, `status` tinyint(3) unsigned NOT NULL DEFAULT '0', `create_time` datetime NOT NULL, `modified_time` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_order_id` (`order_id`), KEY `idx_uid` (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4

set @sequence = 'order  id' //标明order 的id全局唯一，各个分片不会重复

数据库中间件 Mycat+SpringBoot完成分库分表

Mycat就是一个透明的中间件，程序按普通sql数据库处理即可，具体使用见文章 ： Mycat

  Druid简介

Druid 是阿里巴巴开源平台上一个数据库连接池实现，结合了 C3P0、DBCP 等 DB 池的优点，同时加入了日志监控。
Druid 可以很好的监控 DB 池连接和 SQL 的执行情况，天生就是针对监控而生的 DB 连接池。
Druid已经在阿里巴巴部署了超过600个应用，经过一年多生产环境大规模部署的严苛考验。
Spring Boot 2.0 以上默认使用 Hikari 数据源，可以说 Hikari 与 Driud 都是当前 Java Web 上最优秀的数据源，我们来重点介绍 Spring Boot 如何集成 Druid 数据源，如何实现数据库监控。