基于P2P万信金融--day08 万信金融项搭建用户发标&审核标的逻辑整体项目

在接下来得项目中因为业务需要用到了分库分表操作，这个操作，非常得重要

1.1 为什么分库分表

在P2P平台中，标的信息和投标信息做为平台基础业务数据存在。随着平台的发展，这些数据可能会越来越多，甚至达到亿级。以MySQL为例，单库数据量在5000万以内性能比较好，超过阈值后性能会随着数据量的增大而明显降低。单表的数据量超过1000w，性能也会下降严重。这就会导致查询一次所花的时间变长，并发操作达到一定量时可能会卡死，甚至把系统给拖垮，因此我们的P2P平台需要解决这个性能瓶颈问题。

我们是否可以通过提升服务器硬件能力来提高数据处理能力？能，但是这种方案很贵，并且提高硬件是有上限的。那我们能不能把数据分散在不同的数据库中，使得单一数据库和表的数据量变小，从而达到提升数据库操作性能的目的？ 可以，这就是数据库分库分表。

分库分表就是把较大的数据库和数据表按照某种策略进行拆分。目的在于：降低每个库、每张表的数据量，减小数据库的负担，提高数据库的效率，缩短查询时间。另外，因为分库分表这种改造是可控的，底层还是基于RDBMS，因此整个数据库的运维体系以及相关基础设施都是可重用的。

 

1.2 分库分表的方式

1.2.1 垂直分表

用户在电商平台流览商品时，首先看到的是商品的基本信息，如果对该商品感兴趣时才会继续查看该商品的详细描述。因此，商品基本信息的访问频次要高于商品详细描述信息，商品基本信息的访问效率要高于商品详细描述信息(大字段)。 由于这两种数据的特性不一样，因此考虑将商品信息表拆分如下：

 

这种拆分就叫垂直分表。垂直分表定义：将一个表的字段分散到多个表中，每个表存储其中一部分字段。垂直分表带来的提升是：

1. 减少IO争抢，减少锁表的几率，查看商品详情的与商品概述互不影响

2. 充分发挥高频数据的操作效率，对商品概述数据操作的高效率不会被操作商品详情数据的低效率所拖累。

一般来说，某业务实体中的各个数据项的访问频次是不一样的，部分数据项可能是占用存储空间比较大的BLOB或是TEXT，例如上例中的商品描述字段。所以，当数据量很大时，可以将表按字段拆分，将热门字段、冷门字段分开放置在不同表中。垂直切分带来的性能提升，主要集中在热门数据的操作效率上，而且磁盘争用情况减少。通常我们按以下原则进行垂直拆分:

• 把不常用的字段单独放在一张表

• 把text，blob等大字段拆分出来单独放在一张表

• 经常组合查询的字段单独放在一张表中

 

1.2.2 垂直分库

通过垂直分表，数据库性能得到了一定程度的提升，但是还没有达到要求，并且磁盘空间也快不够了，因为数据还是始终存放在一台服务器。库内垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻数据库的压力来说，作用有限，大家还是竞争同一个物理机的CPU、内存、网络IO、磁盘。

以电商平台为例，可以把原有的SELLER_DB(卖家库)，拆分为PRODUCT_DB(商品库)和STORE_DB(店铺库)，并把这两个库分散到不同服务器上，如下图所示：

 

 

由于商品信息与商品描述业务耦合度较高，因此一起被存放在PRODUCT_DB(商品库)；而店铺信息相对独立，因此单独被存放在STORE_DB(店铺库)，这就叫垂直分库。

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，从而达到多个服务器共同分摊压力的效果。垂直分库带来的提升是：

• 解决业务层面的耦合，业务清晰

• 能对不同业务的数据进行分级管理、维护、监控、扩展等

• 高并发场景下，垂直分库在一定程度上可以提升IO、数据库连接数、单机硬件资源的性能

 

1.2.3 水平分库

经过垂直分表和垂直分库后，数据库性能问题就完全解决了？假设某电商平台发展迅猛，PRODUCT_DB(商品库)单库存储数据已经超出预估。假设目前该平台有8w店铺，每个店铺平均有150个不同规格的商品，再算上增长，那商品数量就会达到1500w+级别，并且PRODUCT_DB(商品库)属于访问非常频繁的资源，性能瓶颈再次出现。

能再次垂直分库吗？从业务角度分析，目前已经无法再次垂直拆分。于是我们又想了一个办法，判断商品ID是奇数还是偶数，然后把商品信息分别存放到两个数据库中。也就是说，要操作某条数据，先分析这条数据的商品ID，如果商品ID为奇数，将此操作映射至RRODUCT_DB1(商品库1)；如果商品ID为偶数，将操作映射至RRODUCT_DB2(商品库2)，这就叫水平分库。

 

水平分库是把同一个表的数据按一定规则拆分到不同的数据库中，每个库可以放在不同的服务器上。它带来的提升是：

• 解决了单库大数据，高并发的性能瓶颈。

• 按照合理拆分规则拆分，join操作基本避免跨库。<!--例子中商品信息、商品描述表都使用商品ID字段拆库，可避免跨库join-->

• 提高了系统的稳定性及可用性。<!--稳定性体现在IO冲突减少，锁定减少，可用性指某个库出问题，部分可用-->

当一个应用难以再细粒度的垂直切分，或切分后数据量行数仍然巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平分库了，经过水平切分的优化，往往能解决单库存储量及性能瓶颈。但由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大增加了系统复杂度。

 

1.2.4 水平分表

数据库能水平拆分，那数据表是不是也可以呢？我们尝试把某PRODUCT_DB(商品库)内的表，进行了一次水平拆分：

 

与水平分库的思路类似，不过这次拆分的目标是表，商品信息及商品描述被分成了两套表。如果商品ID为奇数，将此操作映射至商品信息1表；如果商品ID为偶数，将操作映射至商品信息2表，这就叫水平分表。水平分表是在同一个数据库内，把同一个表的数据按一定规则拆分到多个表中。它带来的提升是：

• 优化单一表数据量过大而产生的性能问题

• 避免IO争抢并减少锁表的几率

库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。但由于同一个表的数据被拆分为多张表，也需要额外进行数据操作的路由工作，因此增加了系统复杂度。

1.2.5 小结

• 垂直分表：可以把一个宽表的字段按访问频次、业务耦合松紧、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。

• 垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。

• 水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题。

• 水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库分表方案。

1.3 分库分表带来的问题

分库分表有效的缓解了大数据、高并发带来的性能和压力，也能突破网络IO、硬件资源、连接数的瓶颈，但同时也带来了一些问题。

1.3.1 事务一致性问题

由于分库分表把数据分布在不同库甚至不同服务器，不可避免会带来分布式事务问题，我们需要额外编程解决该问题。

1.3.3 跨节点分页、排序和聚合函数

跨节点多库进行查询时，limit分页、order by排序以及聚合函数等问题，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序。例如，进行水平分库后的商品库，按ID倒序排序分页，取第一页：

1.3.4 主键避重

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。

2 Sharding-JDBC

2.1 概览

Sharding-JDBC是当当网研发的开源分布式数据库中间件。从 3.0 开始，Sharding-JDBC更名为 Sharding-Sphere，之后该项目进入Apache孵化器，4.0之后的版本为Apache版本。

ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于Java同构、异构语言、容器、云原生等各种多样化的应用场景。

咱们目前只需关注Sharding-JDBC，它定位为轻量级Java框架，在Java的JDBC层提供额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

• 适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。

• 适用于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。

• 适用于任意支持JDBC规范的数据库，如：MySQL，Oracle，SQLServer和PostgreSQL。

• 

接下来就是配置shardingJDBC得分库分表在本机系统中得配置过程操作

2.3.4 搭建数据库环境

2.3.4.1 MySQL主从同步

1. 为了能在一台电脑上(本机)演示出主从架构，复制本机原有mysql一份，例如：复制D:\mysql-5.7.25(作为主库) 到 D:\mysql-5.7.25-s1(作为从库), 修改主、从库的配置文件(my.ini)。

   主库配置：

[mysqld]
#开启日志
log-bin = mysql-bin
#设置服务id，主从不能一致
server-id = 1
#设置需要同步的数据库
binlog-do-db=store_db
binlog-do-db=product_db_1
binlog-do-db=product_db_2
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema

从库配置：

[mysqld]
#设置3307端口
port = 3307
# 设置mysql数据库的数据的存放目录(该目录不一定在mysql安装目录下)
datadir=D:\mysql-5.7.25-s1\data
#开启日志
log-bin = mysql-bin
#设置服务id，主从不能一样
server-id = 2
#设置需要同步的数据库
replicate_wild_do_table=store_db.%
replicate_wild_do_table=product_db_1.%
replicate_wild_do_table=product_db_2.%
#屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

然后在命令行窗口(以管理员身份运行)中将从库安装为windows服务，注意配置文件位置： D:\mysql-5.7.25-s1\bin> mysqld install mysqls1 --defaults-file="D:\mysql-5.7.25-s1\my.ini"

由于从库是从主库复制过来的，因此里面的数据完全一致，可使用原来的账号、密码登录, 现在重启主库和从库。

请注意，从库数据(data)目录下有个文件auto.cnf，也要与主库不一样，建议直接删除掉，重启服务后将会重新生成。

#切换至主库bin目录，登录主库
mysql -h localhost -uroot -p123
#授权主从复制专用账号
GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';
#刷新权限
FLUSH PRIVILEGES;
#确认位点 记录下文件名以及位点
show master status;

设置从库向主库同步数据、并检查链路

#切换至从库bin目录，登录从库
mysql -h localhost -P3307 -uroot -p123
#修改从库指向到主库，使用上一步记录的文件名以及位点
CHANGE MASTER TO 
 master_host = 'localhost',
 master_user = 'db_sync',
 master_password = 'db_sync',
 master_log_file = 'mysql-bin.000001',
 master_log_pos = 592;

#执行该命令前，一定要重启主库和从库服务
show slave status\G
#执行该命令后，确认Slave_IO_Runing以及Slave_SQL_Runing两个状态位是否为“Yes”，如果不为Yes，请检查error_log，然后排查相关异常。

#注意：如果之前此从库已有主库指向，需要先执行以下命令清空
STOP SLAVE IO_THREAD FOR CHANNEL '';
reset slave all;

 

 

2.3.4.2 初始化数据库

登录并连接主库，然后执行如下脚本：

1. 执行store_db.sql创建store数据库和store_info表

2. 执行product_db_1.sql创建product_db_1数据库和其中的四张表

3. 执行product_db_2.sql创建product_db_2数据库和其中的四张表

 

此时观察从库，我们会发现从库中已经存在上述数据库和表，说明主从数据同步已经发挥了作用。

下面开始是发标操作：