Sharding-JDBC之分库分表与读写分离
一、分库分表
什么是分库分表
分库分表是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。
分库分表的方式
分库分表包括分库和分表两个部分，在生产中通常包括：垂直分库、水平分库、垂直分表、水平分表四种方式。
垂直分表：
把表的字段按访问频次、是否是大字段的原则拆分为多个表，既能使业务清晰，还能提升部分性能。
垂直分库：
把多个表按业务进行分类，分别存放在不同的库，从而将压力分散至不同的数据库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，但是需要解决跨库带来的所有复杂问题。
水平分库：
把一个表的数据(按数据行:某个字段或某几个字段根据某种规则)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。但是它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题。
水平分表：
把一个表的数据(按数据行:某个字段或某几个字段根据某种规则)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，能小幅提升性能，仅仅作为水平分库的一个补充优化。
分库分表的技术模式
中间件Proxy模式
Proxy属于中间层方案，在应用程序和MySQL之间搭建一层Proxy。中间层介于应用程序与数据库间，
需要做一次转发，基于JDBC协议转发，如Mycat
Client 客户端模式
Client模式属于客户端直连方案。此方案的优势在于轻便，对兼容性、性能以及DBA影响小。 如当当
网的sharding-jdbc，阿里的TDDL
二、初识Sharding-JDBC
官网: https://shardingsphere.apache.org/index_zh.html
Sharding-JDBC概述
Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。
Sharding-JDBC的核心功能为数据分片和读写分离 ，通过Sharding-JDBC，应用可以透明的使用 jdbc访问已经分库分表、读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布。
Sharding-JDBC基本概念
逻辑表：
逻辑表是指一组具有相同逻辑和数据结构表的总称。如：订单表t_order 拆分成 t_order_0,t_order1…t_orderN。t_order就可以表示为逻辑表，在应用程序中操作的就是逻辑表。
真实表：
数据库中真实存在的物理表，即t_order_0 到 t_order_N
数据节点：
数据节点是分库分表中一个不可再分的最小数据单元（表），它由数据源名称和数据表组成，如：db1.t_order_1
绑定表：
指分片规则一致的主表和子表。绑定表之间的分区键完全相同，则此两张表互为绑定表关系。
如:product_info,product_descript,它们都是按 product_id 字段分片，因此两张表互为绑定表关系。如：在垂直分表时,一张表拆分成两张表,如果不配置绑定表关系，就会出现笛卡尔积关联查询
广播表：
存在于所有的分片数据源中的表，表结构和表中的数据在每个数据库中均完全一致。一般是为字典表或者配置表 t_config，某个表一旦被配置为广播表，只要修改某个数据库的广播表，所有数据源中广播表的数据都会跟着同步。
分片键：
用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。
如: 将 t_order 表分片以后,当执行一条SQL时，通过对字段order_id取模的方式决定，这条数据该在哪个数据库中的哪个 表中执行，此时 order_id 字段就是 t_order 表的分片健。
分片算法：
通过分片算法将数据分片，在实际开发中,会用 >=、<=、>、<、BETWEEN 和 IN 等条件作为分片规则，自定义分片逻辑进行分片。
由于分片算法和业务实现紧密相关，因此Sharding-JDBC是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。自定义的算法要实现Sharding-jdbc 提供的接口
sharding-jdbc提供了4种分片算法接口
1、精确分片算法 PreciseShardingAlgorithm
精确分片算法用于单个字段作为分片键，有 = 与 IN 条件的分片，在标准分片策略下使用。
2、范围分片算法 RangeShardingAlgorithm
范围分片算法用于单个字段作为分片键，有BETWEEN AND、>、<、>=、<= 等条件的分片，在标准分片策略下使用。
3、复合分片算法 ComplexKeysShardingAlgorithm
复合分片算法用于多个字段作为分片键的分片操作，同时获取到多个分片健的值，根据多个字段处理业务逻辑。在复合分片策略下使用。
4、Hint分片算法 HintShardingAlgorithm
Hint分片算法不使用任何的分片键和分片策略，将 SQL 路由到目标数据库和表，通过手动干预指定SQL的目标数据库和表信息，也叫强制路由。
自定义分片算法:
自定义分片算法实现按月分表
@Slf4j
public class ShardingAlgorithmMonth implements PreciseShardingAlgorithm<Date> {
/**
* 执行分片策略
* @param collection 候选表集合
* @param preciseShardingValue 精确分片值:任务的执行时间
* @return 数据路由到的表名称
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
String node = null;
try {
DateFormat dateFormat = new SimpleDateFormat("yyyy_M");
String dateStr = dateFormat.format(preciseShardingValue.getValue());
for (String nodeCandidate : collection) {
if (nodeCandidate.endsWith(dateStr)) {
node = nodeCandidate;
break;
}
}
} catch (Exception e) {
log.error("sharding-sphere doSharding exception {}", e.getMessage());
}
return node;
}
}
# 指定t_order表的分片策略,使用标准分片策略,以及自定义分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=cn.ybzy.demo.conf.ShardingAlgorithmMonth
分片策略:
分片策略由分片算法和分片健组合而成，正可用于分片操作的是分片键 + 分片算法, 分片算法做具体的数据分片逻辑。
分片策略分类
Sharding分片策略继承自ShardingStrategy，提供了5种分片策略：
1、标准分片策略 StandardShardingStrategy
标准分片策略适用于单分片键，此策略支持 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 两个分片算法。
其中PreciseShardingAlgorithm是必选的，用于处理 = 和 IN 的分片。
RangeShardingAlgorithm是可选的，用于处理BETWEEN AND， >， <，>=，<= 条件分片，如果不配置RangeShardingAlgorithm，SQL中的条件等将按照全库路由处理。
2、复合分片策略 ComplexShardingStrategy
复合分片策略，同样支持对 SQL语句中的 =，>， <， >=， <=，IN和 BETWEEN AND 的分片操作。不同的是它支持多分片键，具体分配片细节完全由应用开发者实现。
3、行表达式分片策略 InlineShardingStrategy
行表达式分片策略，支持对 SQL语句中的 = 和 IN 的分片操作，但只支持单分片键。这种策略通常用于简单的分片，不需要自定义分片算法，可以直接在配置文件中接着写规则。
t_order_$->{t_order_id % 2} 代表 t_order 对其字段 t_order_id取模，拆分成2张表，而表名分别是t_order_0 到 t_order_1。
4、Hint分片策略 HintShardingStrategy
Hint分片策略，对应上边的Hint分片算法，通过指定分片健而非从 SQL中提取分片健的方式进行分片的策略。
5.不分片的策略 NoneShardingStrategy
如果某张表不需要分库分表，就可以不指定分库分表策略，让这张表的数据直接落到指定的数据源中即可
分布式主键：
通过在客户端生成自增主键替换以数据库原生自增主键的方式，做到分布式主键无重复。
ApacheShardingSphere 内置了UUID、SNOWFLAKE 两种分布式主键⽣成器，默认使⽤雪花算法（snowflake）⽣成64bit的⻓整型数据。不仅如此它还抽离出分布式主键⽣成器的接口，⽅便我们实现⾃定义的⾃增主键⽣成算法。
Sharding-JDBC执行过程
当Sharding-JDBC接受到一条SQL语句时，会陆续执行 SQL解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 =>结果归并 ，最终返回执行结果。
引入依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
规则配置
Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置，开发者可根据场景选择适合的配置方式。配置是整个Sharding-JDBC的核心，是Sharding-JDBC中唯一与应用开发者打交道的模块。配置模块也是Sharding-JDBC的门户，通过它可以快速清晰的理解Sharding-JDBC所提供的功能。
创建DataSource
通过ShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource，ShardingDataSource实现自JDBC的标准接口DataSource。然后即可通过DataSource选择使用原生JDBC开发，或者使用JPA, MyBatis等ORM工具。
三、水平分表
水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中
优点：
1.优化了单一表数据量过大而产生的性能问题
2.避免 IO争抢并减少锁表的几率
创建数据库/表
CREATE DATABASE order_db CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
```java
DROP TABLE
IF
EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',
`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',
`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',
`status` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY ( `order_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE
IF
EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',
`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',
`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',
`status` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY ( `order_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
配置分片规则
分片规则配置是sharding-jdbc进行对分库分表操作的重要依据，配置内容包括：数据源、主键生成策略、分片策略等。
1.properties配置
server.port=8888
spring.application.name = sharding-jdbc
server.servlet.context-path = /
#sharding-jdbc分片规则配置
#数据源 起名:db1
spring.shardingsphere.datasource.names = db1
spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456
# 指定t_order表的数据分布情况，配置数据节点 分布在db1.t_order_1，db1.t_order_2 t_order:逻辑表非真实表,任意符合场景即可 db1.t_order_$->{1..2}: db1.t_order_1,db1.t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db1.t_order_$->{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE,SNOWFLAKE是一种分布式自增算法，保证id全局唯一 order_id:主键ID SNOWFLAKE:雪花片算法
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法 分表策略的表达式: t_order_$->{order_id % 2 + 1}=>> order_id基数时:t_order2 偶数时:t_order1
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
#数据字段的映射,驼峰映射
mybatis.configuration.map-underscore-to-camel-case = true
#日志配置信息
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding = debug
logging.level.druid.sql = debug
2.yaml配置
server:
port: 8888
servlet:
context-path: /
spring:
application:
name: sharding-jdbc
shardingsphere:
#数据源 起名:db1
datasource:
names: db1
db1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/order_db?useUnicode=true
username: root
password: 123456
#指定t_order表的数据分布情况，配置数据节点
sharding:
tables:
t_order:
actualDataNodes: m1.t_order_$->{1..2}
#指定t_order表的分片策略,片策略包括分片键和分片算法
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_$->{order_id % 2 + 1}
#指定t_order表的主键生成策略为SNOWFLAKE
keyGenerator:
type: SNOWFLAKE
column: order_id
#打开sql输出日志
props:
sql:
show: true
#驼峰映射
mybatis:
configuration:
map-underscore-to-camel-case: true
# 日志配置
logging:
level:
root: info
org.springframework.web: info
com.itheima.dbsharding: debug
druid.sql: debug
3.Java配置
使用配置类需要在SpringBoot启动类中屏蔽使用spring.shardingsphere配置项的类，否则该配置类会读取配置文件，从而报错。
@SpringBootApplication(exclude = {SpringBootConfiguration.class})
@Configuration
public class ShardingJdbcConfig {
/**
* 定义数据源集合
*
* @return
*/
Map<String, DataSource> createDataSourceMap() {
DruidDataSource dataSource1 = new DruidDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setUrl("jdbc:mysql://localhost:3306/order_db?useUnicode=true");
dataSource1.setUsername("root");
dataSource1.setPassword("123456");
Map<String, DataSource> result = new HashMap<>();
result.put("db1", dataSource1);
return result;
}
/**
* 定义主键生成策略
*
* @return
*/
private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "order_id");
return result;
}
/**
* 定义t_order表的分片策略
*
* @return
*/
TableRuleConfiguration getOrderTableRuleConfiguration() {
TableRuleConfiguration result = new TableRuleConfiguration("t_order", "m1.t_order_$->{1..2}");
result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$->{order_id % 2 + 1}"));
result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
return result;
}
/**
* 定义sharding-Jdbc数据源
*
* @return
* @throws SQLException
*/
@Bean
DataSource getShardingDataSource() throws SQLException {
//创建sharding-jdbc配置对象
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
//添加使用定义的分片策略
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
//创建配置类对象,添加一条配置信息
Properties properties = new Properties();
properties.put("sql.show", "true");
//创建ShardingDataSource
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
}
}
4.xml方式配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:sharding="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<context:annotation-config/>
<!--定义数据源-->
<bean id="db1" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/order_db?useUnicode=true"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</bean>
<!--定义分库策略-->
<sharding:inline-strategy id="tableShardingStrategy" sharding-column="order_id"
algorithmexpression="t_order_$->{order_id % 2 + 1}"/>
<!--定义主键生成策略-->
<sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="order_id"/>
<!--定义sharding-Jdbc数据源-->
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="db1">
<sharding:table-rules>
<sharding:table-rule logic-table="t_order" table-strategyref="tableShardingStrategy"
key-generator-ref="orderKeyGenerator"/>
</sharding:table-rules>
</sharding:sharding-rule>
</sharding:data-source>
</beans>
编写代码
@Mapper
@Component
public interface OrderDao {
/**
* 新增订单
*
* @param price
* @param userId
* @param status
* @return
*/
@Insert("insert into t_order(price,user_id,status)values(#{price},#{userId},#{status})")
int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);
/**
* 修改订单
*
* @param orderId
* @param price
* @param userId
* @return
*/
@Update("update t_order_1 SET price=#{price},user_id=#{userId} WHERE order_id=${orderId}")
int updateOrder(@Param("orderId") Long orderId, @Param("price") BigDecimal price, @Param("userId") Long userId);
/**
* 根据ids查询order
*
* @param orderIds
* @return
*/
@Select("<script>" +
"select * from t_order t where t.order_id in <foreach collection='orderIds' open='(' separator=',' close=')' item='id'> #{id} </foreach>" +
"</script>")
List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);
}
执行测试
@RunWith(SpringRunner.class)
@SpringBootTest(classes = {ShardingJdbc.class})
public class OrderDaoTest {
@Autowired
OrderDao orderDao;
@Test
public void insertOrder() {
for (int i = 1; i < 10; i++) {
orderDao.insertOrder(new BigDecimal(i), 1L, "SUCCESS");
}
}
@Test
public void updateOrderById() {
Long id = 564780984948490240L;
int i = orderDao.updateOrder(id, new BigDecimal(1000), 2L);
System.out.println(i);
}
@Test
public void selectOrderbyIds() {
List<Long> ids = new ArrayList<>();
ids.add(564780984948490240L);
ids.add(564780986995310593L);
List<Map> maps = orderDao.selectOrderbyIds(ids);
for (Map map : maps) {
System.out.println(map);
}
}
}
order_id为奇数的被插入到t_order_2表，为偶数的被插入到t_order_1表
传入参数,根据id找到对应表修改order信息
根据传入order_id的奇偶不同，sharding-jdbc分别去不同的表查询数据
四、水平分库
水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上
优点：
解决了单库大数据，高并发的性能瓶颈。
缺点：
由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大提升了系统复杂度。
创建数据库/表
创建数据 : order_db1与order_db2
CREATE DATABASE order_db1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
每个库创建表 : t_order_1与t_order_2
DROP TABLE
IF
EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',
`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',
`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',
`status` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY ( `order_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE
IF
EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',
`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',
`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',
`status` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY ( `order_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
配置分片规则
分库需要配置两个数据源，分库需要配置分库策略，和分表策略类似，通过分库策略实现数据操作针对分库的数据库进行操作
#分库策略，将一个逻辑表映射到多个数据源
spring.shardingsphere.sharding.tables.<逻辑表名称>.database‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值
#分表策略，将一个逻辑表映射为多个实际表
spring.shardingsphere.sharding.tables.<逻辑表名称>.table‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值
#数据源
spring.shardingsphere.datasource.names =db1,db2
spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/order_db1?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456
spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3306/order_db2?useUnicode=true
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456
# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作db1数据源，否则操作db2。
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = db$->{user_id % 2 + 1}
# 指定t_order表的数据分布情况，配置数据节点 t_order:逻辑表非真实表,任意符合场景即可 db$->{1..2}=>> db1或db2 t_order_$->{1..2}=> t_order_1 或 t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db$->{1..2}.t_order_$->{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE order_id:主键ID SNOWFLAKE:雪花片算法
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 指定t_order表的分片策略，分片策略包括分片键和分片算法 t_order_$->{order_id % 2 + 1}=>> order_id基数时:t_order2 偶数时:t_order1
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{order_id % 2 + 1}
执行测试
插入测试
@Insert("insert into t_order(price,user_id,status)values(#{price},#{userId},#{status})")
int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);
@Test
public void insertOrder(){
for(int i=1;i<10;i++){
orderDao.insertOrder(new BigDecimal(i),Long.parseLong(i+""),"SUCCESS");
}
}
根据user_id的奇偶不同，数据分别插入到db2与db1，order_id也同样根据奇偶不同插入到t_order_1与t_order_2
查询测试(无分片键)
@Select("<script>" +
"select * from t_order t where t.order_id in <foreach collection='orderIds' open='(' separator=',' close=')' item='id'> #{id} </foreach>" +
"</script>")
List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);
@Test
public void testSelectOrderbyUserAndIds(){
List<Long> ids = new ArrayList<>();
ids.add(374121806463762432L);
// ids.add(373897037306920961L);
List<Map> maps = orderDao.selectOrderbyUserAndIds(4L,ids);
System.out.println(maps);
}
查询奇数订单，根据分片策略t_order_KaTeX parse error: Expected group after ‘_’ at position 339: …单，根据分片策略t_order_̲->{order_id % 2 + 1}计算得出t_order_1与t_order_2，但因没有指定查询user_id，即没有使用分片键user_id，所以sharding-jdbc将广播路由到每个数据结点，即会查询两个数据库，每个数据库查询2次
查询测试(含分片键)
@Select("<script>" +
"select * from t_order t where t.order_id in <foreach collection='orderIds' open='(' separator=',' close=')' item='id'> #{id} </foreach>" + "and user_id=#{userId}" +
"</script>")
List<Map> selectOrderByUserAndIds(@Param("userId") Long userId, @Param("orderIds") List<Long> orderIds);
@Test
public void selectOrderByUserAndIds(){
List<Long> ids = new ArrayList<>();
ids.add(564863163246313472L);
List<Map> maps = orderDao.selectOrderByUserAndIds(1L,ids);
for (Map map : maps) {
System.out.println(map);
}
}
分片键user_id为1，根据分片策略dbKaTeX parse error: Expected group after ‘_’ at position 72: …> 根据分片策略t_order_̲->{order_id % 2 + 1}计算得出t_order_1,sharding-jdbc将sql路由到t_order_1
@Test
public void selectOrderByUserAndIds(){
List<Long> ids = new ArrayList<>();
ids.add(564863163246313472L);
ids.add(564863164945006593L);
List<Map> maps = orderDao.selectOrderByUserAndIds(2L,ids);
for (Map map : maps) {
System.out.println(map);
}
}
分片键user_id为2，根据分片策略dbKaTeX parse error: Expected group after ‘_’ at position 72: …> 根据分片策略t_order_̲->{order_id % 2 + 1}计算得出t_order_1与t_order_2,sharding-jdbc将sql路由到t_order_1与t_order_2
五、垂直分库
垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用
优点:
1.业务层面解耦,业务分类更清晰
2.针对不同业务的数据进行不同管理、维护、扩展
3.提升了一定的IO性能，降低了单机硬件资源的瓶颈
4.将表按业务分类，分布在不同数据库，数据库在不同服务器上，多个服务器共同分摊压力
缺点：
单表数据量过大
创建数据库/表
CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
DROP TABLE
IF
EXISTS `t_user`;
CREATE TABLE `t_user` (
`user_id` BIGINT ( 20 ) NOT NULL COMMENT '用户id',
`user_name` VARCHAR ( 255 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
`user_type` CHAR ( 1 ) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY ( `user_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
配置分片规则
#数据源
spring.shardingsphere.datasource.names =db3
spring.shardingsphere.datasource.db3.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db3.url = jdbc:mysql://localhost:3306/user_db?useUnicode=true
spring.shardingsphere.datasource.db3.username = root
spring.shardingsphere.datasource.db3.password = 123456
# 指定t_user表的数据分布情况，配置数据节点 固定分配至db3的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db3.t_user
#t_user分表策略,分片策略包括分片键和分片算法 固定分配至t_user真实表
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user
执行测试
插入测试
@Insert("insert into t_user(user_id, user_name,user_type) value(#{userId},#{userName},#{userType})")
int insertUser(@Param("userId") Long userId, @Param("userName") String userName, @Param("userType") String userType);
@Test
public void insertUser(){
for (int i = 1 ; i<10; i++){
Long id =Long.parseLong(i+"");
userDao.insertUser(id,"姓名"+ id,i%2==0 ? "男" : "女");
}
}
查询测试
@Select({"<script>",
" select * from t_user t where t.user_id in <foreach collection='userIds' item='id' open='(' separator=',' close=')'> #{id} </foreach>",
"</script>"
})
List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds);
@Test
public void selectUserbyIds(){
List<Long> userIds = new ArrayList<>();
userIds.add(1L);
userIds.add(2L);
List<Map> maps = userDao.selectUserbyIds(userIds);
for (Map map : maps) {
System.out.println(map);
}
}
六、垂直分表
垂直分表是将一个表按照字段分成多表，每个表存储其中一部分字段。
拆分原则:
1. 把不常用的字段单独放在一张表;
2. 把text，blob等大字段拆分出来放在附表中;
3. 经常组合查询的列放在一张表中;
优点:
避免IO争抢并减少锁表的几率
缺点:
只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。
创建数据库/表
创建数据库product_db
CREATE DATABASE product_db CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
创建product_info_1与product_info_2两张表
DROP TABLE
IF
EXISTS `product_info_1`;
CREATE TABLE `product_info_1` (
`product_info_id` BIGINT ( 20 ) NOT NULL COMMENT 'id',
`product_name` VARCHAR ( 100 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
`price` DECIMAL ( 10, 0 ) NULL DEFAULT NULL COMMENT '商品价格',
PRIMARY KEY ( `product_info_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
创建product_descript_1与product_descript_2两张表
DROP TABLE
IF
EXISTS `product_descript_2`;
CREATE TABLE `product_descript_2` (
`id` BIGINT ( 20 ) NOT NULL COMMENT 'id',
`product_info_id` BIGINT ( 20 ) NULL DEFAULT NULL COMMENT '所属商品id',
`descript` LONGTEXT CHARACTER
SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
PRIMARY KEY ( `id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
配置分片
spring.shardingsphere.datasource.names = db
spring.shardingsphere.datasource.db.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db.url = jdbc:mysql://localhost:3306/product_db?useUnicode=true
spring.shardingsphere.datasource.db.username = root
spring.shardingsphere.datasource.db.password = 123456
# product_info数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = db.product_info_$->{1..2}
# product_info分表策略
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column = product_info_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression = product_info_$->{product_info_id%2+1}
# product_info主键生成策略
spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id
spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE
#product_descript数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = db.product_descript_$->{1..2}
#product_descript分表策略
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column = product_info_id
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression = product_descript_$->{product_info_id % 2 + 1}
#product_descript主键生成策略
spring.shardingsphere.sharding.tables.product_descript.key-generator.column=id
spring.shardingsphere.sharding.tables.product_descript.key-generator.type=SNOWFLAKE
# 设置product_info,product_descript为绑定表
spring.shardingsphere.sharding.binding-tables[0] = product_info,product_descript
新增查询
public void createProduct(ProductInfo product);
@Override
@Transactional
public void createProduct(ProductInfo productInfo) {
ProductDescript productDescript =new ProductDescript();
productDescript.setDescript(productInfo.getDescript());
productDao.insertProductInfo(productInfo);
//将商品信息id设置到productDescript
productDescript.setProductInfoId(productInfo.getProductInfoId());
productDao.insertProductDescript(productDescript);
}
/**
* 添加商品信息
* 需要指定主键生成规则
* @param productInfo
* @return
*/
@Insert("insert into product_info(product_name,price) " +
" values (#{productName},#{price})")
@Options(useGeneratedKeys = true,keyProperty = "productInfoId",keyColumn = "product_info_id")
int insertProductInfo(ProductInfo productInfo);
/**
* 添加商品描述信息
* 需要指定主键生成规则
* @param productDescript
* @return
*/
@Insert("insert into product_descript(product_info_id,descript) " +
" value(#{productInfoId},#{descript})")
@Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")
int insertProductDescript(ProductDescript productDescript);
@Test
public void createProduct(){
for (int i=1;i<10;i++){
ProductInfo productInfo = new ProductInfo();
productInfo.setProductName("商品名称"+i);
productInfo.setPrice(new BigDecimal(i));
productInfo.setDescript("商品描述"+i);
productService.createProduct(productInfo);
}
}
使用sharding-jdbc提供的全局主键生成方式：雪花算法，生成全局业务唯一主键product_info_id。保证product_info_id为偶数的数据插入product_info_1与product_descript_1,为奇数的数据插入product_info_2与product_descript_2
普通查询
List<ProductInfo> selectProductByIds(List<Long> productIds);
@Override
public List<ProductInfo> selectProductByIds(List<Long> ids) {
return productDao.selectProductByIds(ids);
}
@Select("<script>" +
"select i.*,d.descript from product_info i join product_descript d on i.product_info_id = d.product_info_id where i.product_info_id in <foreach collection='productIds' open='(' separator=',' close=')' item='id'> #{id} </foreach> order by product_info_id desc" +
"</script>")
List<ProductInfo> selectProductByIds(@Param("productIds") List<Long> productIds);
@Test
public void selectProductByIds(){
List<Long> productIds = new ArrayList<>();
productIds.add(565306560192970753L);
productIds.add(565306560155222016L);
List<ProductInfo> productInfos = productService.selectProductByIds(productIds);
for (ProductInfo productInfo : productInfos) {
System.out.println(productInfo);
}
}
分页查询
public List<ProductInfo> selectProduct(int page, int pageSize);
@Override
public List<ProductInfo> selectProduct(int page, int pageSize) {
int start = (page - 1) * pageSize;
return productDao.selectProductList(start,pageSize);
}
@Select("select i.*,d.descript from product_info i join product_descript d on i.product_info_id = d.product_info_id order by product_info_id desc limit #{start},#{pageSize}")
List<ProductInfo> selectProduct(@Param("start")int start, @Param("pageSize") int pageSize);
public void selectProduct(){
List<ProductInfo> productInfos = productService.selectProduct(1, 5);
for (ProductInfo productInfo : productInfos) {
System.out.println(productInfo);
}
}
七、公共表
公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。
创建公共表
在springboot配置文件中涉及使用的数据库中添加t_dict表
CREATE TABLE `t_dict` (
`dict_id` BIGINT ( 20 ) NOT NULL COMMENT '字典id',
`type` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典类型',
`code` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典编码',
`value` VARCHAR ( 50 ) CHARACTER
SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典值',
PRIMARY KEY ( `dict_id` ) USING BTREE
) ENGINE = INNODB CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
指定公共表
指定t_dict为公共表
spring.shardingsphere.sharding.broadcast‐tables=t_dict
执行测试
插入测试
@Insert("insert into t_dict(dict_id,type,code,value) value(#{dictId},#{type},#{code},#{value})")
int insertDict(@Param("dictId") Long dictId, @Param("type") String type, @Param("code") String code, @Param("value") String value);
@Test
public void insertDict(){
dictDao.insertDict(1L,"role","role_1","1号管理员");
dictDao.insertDict(2L,"role","role_2","2号管理员");
}
对t_dict表进行插入操作，被广播至所有数据源
修改测试
@Update("update t_dict set type=#{type},code=#{code},value=#{value} where dict_id = #{dictId}")
int updateDict(@Param("type") String type, @Param("code") String code, @Param("value") String value, @Param("dictId") Long dictId);
@Test
public void updateDict(){
dictDao.updateDict("role","1","test",2L);
}
关联查询测试
@Select({"<script>",
" select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in <foreach collection='userIds' item='id' open='(' separator=',' close=')'> #{id} </foreach>",
"</script>"
})
List<Map> selectUserByIdsAndCode(@Param("userIds") List<Long> userIds, @Param("code") String code);
@Test
public void selectUserByIdsAndCode(){
List<Long> userIds = new ArrayList<>();
userIds.add(6L);
userIds.add(8L);
List<Map> maps = userDao.selectUserByIdsAndCode(userIds,"1");
for (Map map : maps) {
System.out.println(map);
}
}
删除测试
@Delete("delete from t_dict where dict_id = #{dictId}")
int deleteDict(@Param("dictId") Long dictId);
@Test
public void deleteDict(){
dictDao.deleteDict(1L);
}
对t_dict表进行删除操作，被广播至所有数据源
八、Sharding-JDBC读写分离
Sharding-JDBC提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用。Sharding-JDBC不提供主从数据库的数据同步功能，需要采用其他机制支持。
Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库，提供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群
要实现Sharding-JDBC的读写分离，首先要进行MySQL主从同步配置 || 其他方式实现同步关系。
添加同步数据库
具体配置参考MySQL主从同步配置
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\Mysql\mysql-master
# 设置mysql数据库的数据的存放目录
datadir=D:\Mysql\mysql-master\data
#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=1
#设置需要同步的数据库,不配置则同步全部数据库
binlog-do-db=user_db
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
规则配置
#数据源 db1:主库 db2:从库
spring.shardingsphere.datasource.names =db1,db2
spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/user_db?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456
spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3307/user_db?useUnicode=true
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456
# 主库从库逻辑数据源定义 db为user_db
spring.shardingsphere.sharding.master-slave-rules.db.master-data-source-name=db1
spring.shardingsphere.sharding.master-slave-rules.db.slave-data-source-names=db2
#t_user分表策略，固定分配至db的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db.t_user
写入测试
@Insert("insert into t_user(user_id, user_name,user_type) value(#{userId},#{userName},#{userType})")
int insertUser(@Param("userId") Long userId, @Param("userName") String userName, @Param("userType") String userType);
@Test
public void insertUser(){
for (int i = 1 ; i<10; i++){
Long id =Long.parseLong(i+"");
userDao.insertUser(id,"姓名"+ id,i%2==0 ? "1" : "2");
}
}
插入数据，数据全部写入db1主库，在同步到db2从库。
查询测试
@Select({"<script>",
" select * from t_user t where t.user_id in <foreach collection='userIds' item='id' open='(' separator=',' close=')'> #{id} </foreach>",
"</script>"
})
List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds);
@Test
public void selectUserByIdsAndCode(){
List<Long> userIds = new ArrayList<>();
userIds.add(3L);
userIds.add(8L);
List<Map> maps = userDao.selectUserbyIds(userIds);
for (Map map : maps) {
System.out.println(map);
}
}
查询数据，查询db2从库
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/137074.html
