文章目录
一、分库实战
1.环境搭建
-
通过docker启动一个新的MySQL服务:
docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=mysql1 mysql:latest
-
进入容器:
docker exec -it mysql1 bash
-
只显示bash-4.4,在容器中输入:
cp /etc/skel/.bash* /root/
然后exit退出,再次进入容器 -
进入到mysql命令行:
mysql -uroot -p123456
-
进入mysql数据库,查看用户信息:
use mysql
,update user set host="%" where user ="root";
-
如果root用户的host字段不是%,则修改为任意主机都可以连接root用户:
update user set host="%" where user ="root";
-
如果mysql为8.0.4以上版本,则修改plugin密码机制为mysql_native_password并指定密码:
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-
使用SQLyog连接该MySQL服务,其他可视化工具也可以:
-
创建两个数据库:
-
在两个数据库中都创建position和position_detail表:
CREATE TABLE `position` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `name` varchar(256) DEFAULT NULL, `salary` varchar(50) DEFAULT NULL, `city` varchar(256) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `position_detail` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `pid` bigint(11) NOT NULL DEFAULT '0', `description` text DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-
创建一个空Maven项目,如下:
-
在pom.xml中添加依赖:
<dependencies> <!-- springboot的对象映射框架 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> <version>2.7.3</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> <version>2.7.3</version> </dependency> <!-- springboot测试依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <version>2.7.3</version> </dependency> <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.30</version> </dependency> <!-- springboot整合sharding-jdbc依赖 --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> </dependency> </dependencies>
2.分库业务的实现
-
在java根目录下,创建包com.zzx.entity,在该包下创建实体类Position:
@Data @Entity @Table(name="position") public class Position implements Serializable { @Id @Column(name="id") private Long id; @Column(name="name") private String name; @Column(name="salary") private Double salary; @Column(name="city") private String city; }
在字段id上加@GeneratedValue(strategy = GenerationType.IDENTITY) ,表示主键自增
-
在java根目录下,创建包com.zzx.repository,在该包下创建接口PositionRepository:
public interface PositionRepository extends JpaRepository<Position,Long> { }
-
在com.zzx包下创建Springboot主启动类RunBoot(直接创建springboot项目的话,名字是项目名加Application):
@SpringBootApplication public class RunBoot { public static void main(String[] args) { SpringApplication.run(RunBoot.class,args); } }
-
在resource目录下创建主配置文件application.properties:
#指定Sharding-JDBC配置文件的名称 spring.profiles.active=sharding-database #将sharding-jdbc执行的sql文件打印出来 spring.shardingsphere.props.sql.show=true
-
在resource目录下创建sharding-jdbc的配置文件application-sharding-database.properties:
#datasource spring.shardingsphere.datasource.names=ds0,ds1 #ds0 spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.126.21:3306/zzx1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.ds0.username=root spring.shardingsphere.datasource.ds0.password=123456 #ds1 spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.126.21:3306/zzx2?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=123456 #sharding-database spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds${id%2}
3.分库测试
-
在Test根目录下,创建Test测试类TestPosition:
@SpringBootTest(classes = RunBoot.class) public class TestPosition { @Resource private PositionRepository positionRepository; @Test public void testAdd() { for (int i = 1; i <= 20; i++) { Position position = new Position(); position.setId((long)i); position.setName("zzx"+i); position.setSalary(2000d); position.setCity("shenzhen"); positionRepository.save(position); } } }
-
执行后第二个数据库的position表,如下
即完成了分库操作
4.主键生成器(雪花算法)
-
将position实体类的id字段修改为如下,即加上主键自增的策略:
@Id @Column(name="id") @GeneratedValue(strategy = GenerationType.IDENTITY) //自增策略 private Long id;
-
在application-sharding-database.properties配置文件中,添加如下配置:
#主键生成器 spring.shardingsphere.sharding.tables.position.key-generator.column=id spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
即使用雪花算法生成主键,字段为id。
雪花算法:使用一个 64 bit 的 long 型的数字作为全局唯一 ID。在分布式系统中的应用十分广泛,且 ID 引入了时间戳,基本上保持自增的。
-
在PositionTest测试类中,修改Test类:
@Test public void testAdd() { for (int i = 1; i <= 20; i++) { Position position = new Position(); //position.setId((long)i); position.setName("zzx"+i); position.setSalary(2000d); position.setCity("shenzhen"); positionRepository.save(position); } }
-
此时第二个数据库为
5.拆表与分库
-
配置从表position_detail跟随主表position:
#position_detail spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=pid spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds${pid%2} #主键生成器 spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE
即配置从表的分库策略,以及主键生成
-
在com.zzx.entity包下,创建实体类PositionDetail:
@Data @Entity @Table(name ="position_detail") public class PositionDetail implements Serializable { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name ="pid") private Long pid; @Column(name="description") private String description; }
-
在com.zzx.repository包下,创建接口PositionDetailRepository:
public interface PositionDetailRepository extends JpaRepository<PositionDetail,Long> { }
继承JpaRepository,并传入两个参数,第一个是实体类对象,另一个是主键类型。
-
在PositionTest测试类中,创建一个新的测试方法:
@Resource private PositionDetailRepository positionDetailRepository; @Test public void testAdd2() { for (int i = 1; i <= 20; i++) { PositionDetail positionDetail = new PositionDetail(); positionDetail.setPid((long)i); positionDetail.setDescription("description"+i); positionDetailRepository.save(positionDetail); } }
此时一个数据库中的position.id与与postion_detail.pid相同时,从表跟随主表的配置就成功了。
-
接下来进行一个关联查询测试,在PositionDetailRepository接口中添加:
@Query(nativeQuery = true,value = "SELECT p.id,p.name,p.salary,p.city,pd.description FROM position p JOIN position_detail pd ON p.id=pd.pid WHERE p.id= :id") Object findPositionById(@Param("id")long id);
-
在PositionTest测试类中,创建一个新的测试方法:
@Test public void testQuery() { Object o = positionDetailRepository.findPositionById(2); Object[] position = (Object[]) o; for (int i = 0; i < 5; i++) { System.out.println(position[i]); } }
如果不用Object,则需要创建一个类来映射查询到的数据。
6.广播表
-
使用SQLyog在两个数据库中创建city表
CREATE TABLE `city` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `name` varchar(256) DEFAULT NULL, `province` varchar(256) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-
在com.zzx.entity包下,创建实体类City:
@Entity @Data @Table(name="city") public class City implements Serializable { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(name = "name") private String name; @Column(name = "province") private String province; }
-
在com.zzx.repository包下,创建一个接口CityRepository:
public interface CityRepository extends JpaRepository<City,Long> { }
-
在application-sharding-database.properties配置文件中,添加如下配置(即广播表city的配置):
# Broadcast(广播表) 相当于mycat的全局表 spring.shardingsphere.sharding.broadcast-tables=city spring.shardingsphere.sharding.tables.city.key-generator.column=id spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
-
在PositionTest测试类中,创建一个新的测试方法:
@Resource private CityRepository cityRepository; @Test public void testBroadcast() { City city = new City(); city.setName("shenzhen"); city.setProvince("guangdong"); cityRepository.save(city); }
此时两个数据库都有这条数据时,即配置成功。
二、读写分离实战
1.读写分离与数据分片概念
- 读写分离是通过主从配置的方式,将查询请求均匀的分散到多个数据副本,进一步的提升系统的处理能力。
- 主库:添加、更新以及删除数据操作所使用的的数据库。
- 从库:查询数据操作所使用的数据库。
- 主从架构:读写分离的目的是高可用、读写扩展。主从库内容相同,根据SQL语义进行路由。
- 分库分表架构:数据分片,目的是读写扩展、数据扩容,库和表内容不同,根据分片配置进行路由。将水平分片和读写分离联合使用,能够更加有效的提升系统性能。
- 读写分离虽然可以提升系统的吞吐量和可用性,但同时也带来了数据不一致问题,包括多个主库之间的数据一致性,以及主库与从库的数据一致性问题。并且会使得应用开发和对数据库的操作与运维变得更加复杂。
2.配置主从架构
-
配置mysql主节点:
server-id=21 log_bin=mysql_bin_log character_set_server=utf8 binlog_do_db=sxt_his binlog-ignore-db=performance_schema binlog-ignore-db=information_schema
此时mysql得先有这些具体数据库才能配置
-
配置mysql从节点:
server-id=22 log_bin=mysql_bin_log character_set_server=utf8 default_time_zone=+8:00 symbolic-links=0 log-error=/var/log/mysqld.log
-
修改密码机制:
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-
查看主库状态:
show master status;
-
进入MySQL从库,指定主库master:
change master to master_host='192.168.126.21',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql_bin_log.000002',master_log_pos=157;
-
启动从库的主从复制:
start slave;
-
查看从库主从复制的状态:
show slave status \G;
3.读写分离架构剖析
读写分离方案:
- 分库+读写分离
在数据量不多的情况下,可以将数据库进行读写分离,以应对高并发的需求,通过水平扩展从库,来缓解查询的压力。 - 分表+读写分离
在数据量达到500W时,这时数据量预估千万级别,可以将数据进行分表存储。 - 分库分表+读写分离
在数据量继续扩大,这时可以考虑分库分表,将数据存储在不同数据库的不同表中。
透明化读写分离所带来的影响,让使用方尽量像使用一个数据库一样使用使用主从数据库集群,是ShardingSphere读写分离模块的主要设计目标。
核心功能,提供一主多从的读写分离配置。仅支持单主库,可以支持独立使用,也可以配置分库分表使用。
- 独立使用读写分离,支持SQL透传。即不需要SQL改写流程。
对于分布式实例,会对SQL进行语法解析,有一定的限制,如果用户想在某个set中获取单个节点数据,或在指定节点执行SQL,可以使用SQL透传的功能。即后面加-c。
SQL透传,即语法不解析,直接透传。
- 同一线程且同一数据库连接内,能保证数据一致性。如果有写入操作,后续的读操作均从主库读取。基于Hint的强制主库路由。可以强制路由走主库查询实时数据,避免主从同步数据延迟。
不支持项
- 主库和从库的数据同步
- 主库和从库的数据同步延迟
- 主库双写或多写
- 跨主库和从库之间的事务的数据不一致。建议在主从架构中,事务中的读写均用主库操作。
4.读写分离实战
-
在resources目录下创建一个主从配置文件application-master-slave.properties:
# datasource spring.shardingsphere.datasource.names=master,slave0 # master spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.126.21:3306/zzx1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.master.username=root spring.shardingsphere.datasource.master.password=123456 # slave0 spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://192.168.126.22:3306/zzx1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.slave0.username=root spring.shardingsphere.datasource.slave0.password=123456 # master-slave spring.shardingsphere.masterslave.name=datasource spring.shardingsphere.masterslave.master-data-source-name=master spring.shardingsphere.masterslave.slave-data-source-names=slave0 spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN # 主键生成器 # city spring.shardingsphere.sharding.tables.city.key-generator.column=id spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
配置读写分离,查询时使用轮询的负载均衡算法;配置使用读写分离的表,并对表配置主键生成器及算法。
-
在主配置文件application.properties中指定配置文件的名称:
#指定Sharding-JDBC配置文件的名称 spring.profiles.active=master-slave
-
在Test目录下创建测试类TestMasterSlave,添加如下代码:
@SpringBootTest(classes = RunBoot.class) public class TestMasterSlave { @Resource private CityRepository cityRepository; @Test public void testAdd() { City city = new City(); city.setName("shenzhen"); city.setProvince("guangdong"); cityRepository.save(city); } }
因为配置时,设置仅复制master指定数据库的记录binlog_do_db=sxt_his。所以此时主从复制对其他数据库没有作用,只有主库存在数据,需要去配置指定数据库。
总结:
- Sharding-JDBC分库,首先需要引入Sharding-JDBC相关依赖,其次是在配置文件中配置Sharding-JDBC。
因为使用JPA的ORM框架,所以采用在repository接口继承JpaRepository类,并传入实体类以及主键类型。在使用JPA时,只需要@Resouce注入对应的接口类,直接引用即可。 - sharding-jdbc读写分离方案有三种,分库+读写分离、分表+读写分离和分库分表+读写分离。
sharding-jdbc的核心功能有,提供一主多从的读写分离配置,仅支持单主库;当独立使用读写分离时,支持SQL透传;同一线程且同一数据库连接内,可以保证数据一致性。
事务中的读写均用主库操作。