author 曹扬进
createTime 2022-01-26
updateTime 2022-04-21
2022-04-28: 培训结束
# MySQL Shardingsphere应用一
MySQL
# 数据库的分库分表
数据库是存放、永久化业务数据的不二选择。关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。
数据库分布式的核心无非就是数据切分，以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。
数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分。
# 垂直（纵向）切分
垂直切分常见有垂直分库和垂直分表两种。
垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。
垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。
垂直切分的优点：
- 解决业务系统层面的耦合，业务清晰
- 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
- 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈
缺点：
- 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
- 分布式事务处理复杂
- 依然存在单表数据量过大的问题（需要水平切分）
# 水平（横向）切分
当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。
水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。
库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。
水平切分的优点：
- 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
- 应用端改造较小，不需要拆分业务模块
缺点：
- 跨分片的事务一致性难以保证
- 跨库的join关联查询性能较差
- 数据多次扩展难度和维护量极大
# 水平分表案例
在P003项目中因为抄表数据量大用到水平切分中的库内分表，为此引入了Apache ShardingSphere (opens new window) 的数据库分库分表框架。
在P003项目中用到了 shardingsphere 的数据分片功能，先介绍数据分片的核心概念
表
表是透明化数据分片的关键概念。 Apache ShardingSphere 通过提供多样化的表类型，适配不同场景下 的数据分片需求。
逻辑表
相同结构的水平拆分数据库（表）的逻辑名称，是 SQL 中表的逻辑标识。例：订单数据根据主键尾数拆 分为 10 张表，分别是 t_order_0 到 t_order_9，他们的逻辑表名为 t_order。
真实表
在水平拆分的数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_9。
数据节点
数据分片的最小单元，由数据源名称和真实表组成。例： ds_0.t_order_0。
分片键
用于将数据库（表）水平拆分的数据库字段。例：将订单表中的订单主键的尾数取模分片，则订单主键 为分片字段。 SQL 中如果无分片字段，将执行全路由，性能较差。除了对单分片字段的支持， Apache ShardingSphere 也支持根据多个字段进行分片。
分片算法
用于将数据分片的算法，支持 =、 >=、 <=、 >、 <、 BETWEEN 和 IN 进行分片。分片算法可由开发者自行 实现，也可使用 Apache ShardingSphere 内置的分片算法语法，灵活度非常高。
引入的依赖
``
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
数据源配置 （P003采用单数据源配置）
``
shardingType: day #指定日冻结、负荷曲线、事件、日消费能量 数据表分表方式 day : 按日 month : 按月
spring:
profiles:
active: sharding${shardingType}
shardingsphere:
datasource:
names: app003
app003:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/app003?useUnicode=true&characterEncoding=UTF-8&characterSetResults=utf8&serverTimezone=Asia/Shanghai&useSSL=false&allowMultiQueries=true
username: root
password: xxxxxxxx
initial-size: 10
max-active: 32
min-idle: 10
max-wait: 60000
validation-query: SELECT 1
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
test-while-idle: true
test-on-borrow: false
test-on-return: false
keep-alive: true
props:
## 开启SQL显示，默认false
sql:
show: false
分片规则配置
``
spring:
shardingsphere:
sharding:
tables:
tbl_smart_meter_daily: #逻辑表名
actual-data-nodes: app003.tbl_smart_meter_daily ## 由数据源名 + 表名组成（参考Inline语法规则）
table-strategy: ## 分表策略，同分库策略
standard: ## 用于单分片键的标准分片场景
sharding-column: capture_time ## 分片列名称
precise-algorithm-class-name: com.lowan.amiserver.common.sharding.PreciseShardingDayTableAlgorithm
range-algorithm-class-name: com.lowan.amiserver.common.sharding.RangeShardingDayTableAlgorithm
这里已日冻结分片规则为例，指定 tbl_smart_meter_daily 为逻辑表，对日冻结数据的操作将从 tbl_smart_meter_daily_20211231日分表名 转换 tbl_smart_meter_daily逻辑名，极大地方便了业务SQL的编写。这里采用的是单分片键的同分库分表策略，以 capture_time 作为分片的依据。这里有两个自实现的按时间分表的策略 PreciseShardingDayTableAlgorithm 与 RangeShardingDayTableAlgorithm以支持=、>=、<=、>、<、BETWEEN 和 IN 进行分片。
``
public class PreciseShardingDayTableAlgorithm implements PreciseShardingAlgorithm<Timestamp> { //支持精确查询
@Value("${databasename}")
private String database;
@Override
public String doSharding(Collection<String> tableNames,
PreciseShardingValue<Timestamp> shardingValue) {
String tableName = "";
LocalDateTime values = shardingValue.getValue().toLocalDateTime();
String value = values.format(DateTimeFormatter.ofPattern("yyyyMMdd"));
for (String key : tableNames) {
TableMapper tableMapper = SpringContextUtils.getBean(TableMapper.class);
List<String> s = tableMapper.selectTable( key+"_"+value, database);
if (s != null && s.size()>0) {
tableName = s.get(0);
}
}
if (StringUtils.isBlank(tableName) && !CollectionUtils.isEmpty(tableNames)) {
tableName = tableNames.iterator().next();
}
return tableName;
}
}
``
public class RangeShardingDayTableAlgorithm implements RangeShardingAlgorithm<Timestamp> { //支持范围查询
@Value("${databasename}")
private String database;
@Override
public Collection<String> doSharding(final Collection<String> tableNames,
final RangeShardingValue<Timestamp> shardingValue) {
Set<String> result = new LinkedHashSet<>();
LocalDateTime lower = shardingValue.getValueRange().lowerEndpoint().toLocalDateTime();
LocalDateTime upper = shardingValue.getValueRange().upperEndpoint().toLocalDateTime();
String lowerValue = lower.format(DateTimeFormatter.ofPattern("yyyyMMdd"));
String upperValue = upper.format(DateTimeFormatter.ofPattern("yyyyMMdd"));
while(Integer.parseInt(lowerValue) <= Integer.parseInt(upperValue)){
for (String each : tableNames) {
TableMapper tableMapper = SpringContextUtils.getBean(TableMapper.class);
List<String> s = tableMapper.selectTable(each+"_"+lowerValue, database);
if (s != null && s.size()>0) {
result.add(s.get(0));
}
}
lowerValue = nextDay(lowerValue, 1, ChronoUnit.DAYS, "yyyyMMdd");
}
if (CollectionUtils.isEmpty(result) && !CollectionUtils.isEmpty(tableNames)) {
result.add(tableNames.iterator().next());
}
return result;
}
}
业务代码中的插入语句
insert into tbl_smart_meter_daily (dev_addr, day_record, capture_time, grid_id, consumer_code,poc_point,
datas, create_time)
values
(#{item.devAddr,jdbcType=VARCHAR}, #{item.dayRecord}, #{item.captureTime,jdbcType=TIMESTAMP}, #{item.gridId},
#{item.consumerCode,jdbcType=VARCHAR}, #{item.pocPoint}, #{item.datas,jdbcType=VARCHAR},
now())
业务代码中的查询语句
select
consumer_code consumerCode,
datas datas,
capture_time captureTime,
dev_addr devAddr,
poc_point pocPoint
from tbl_smart_meter_daily
where
dev_addr = #{devAddr}
and capture_time between #{start} and #{end}
对数据库多个实际数据表的增删改查操作的SQL语句改为了对逻辑表的语句，这里把对 tbl_smart_meter_daily_yyyyMMdd 的按日分表操作转换为对 tbl_smart_meter_daily 的操作，相当于把多张分表当成了一张单独的表，简化了业务逻辑。