02 mysql分库分表-CSDN博客

本文链接：https://blog.csdn.net/g759780748/article/details/119542157

1 为什么要分库分表

数据库中的数据量是在不断增加的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；另外，由于无法进行分布式部署，而一台服务器的资源(CPU、磁盘、内存、IO等)是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。

2 什么情况下可以考虑要分库分表

理论上说mysql单表数据达到5000W的时候性能才会下降，但是实际情况下有的可能数据到了300W就不行了，有的可能到了1000W-2000W性能还OK，所以我们要综合考虑硬件、业务、数据库参数、表结构设计等各方便考虑。另外，即使由于数据量的增加导致的性能下降，我们是建议先考虑索引优化，合理设置缓存，升级硬件，业务协调等手段，总之原则是能不分库分表就不分库分表。因为分库分表会带来很多烦恼。

3 分库分表带来的问题

事务支持，扩库/扩表后事务就成分布式的了，问题难度显然上升了一个级别
查询结果合并，这个看起来不难，但是把order by/limit/查询中不带分表字段等加上，要解决的问题还也不少
join，这个更难
分库？分表？还是分库分表？这个需要考虑并做一个决定
分完后能否合并？分容易，和很难

4 数据拆分的方式

垂直拆分：根据业务的维度，将原本一个库中的表拆分多个表，每个库中表与原有的结构不同
水平拆分：根据分片算法，将一个库拆分成多个库，每个库依旧保留原有的结构

拆分方式	优点	缺点
垂直拆分	1. 拆分后业务清晰，拆分规则明确 2. 系统之间进行整合或扩展容易 3. 按照成本、应用等级、应用的类型等将表放到不同的机器上，便于管理 4.便于实现动静分离、冷热分离的数据库表的设计模式 5. 数据维护简单	1. 部分业务表无法进行关联、只能通过接口的方式来解决，提高了系统的复杂度 2. 受每种业务不同的限制，存在单库性能瓶颈，对数据扩展和性能提升不友好 3. 事务处理复杂
水平拆分	1. 单裤单表的数据保持一定的量级，有助于性能的提高 2. 切分的表的结构相同，应用层改造较少，只需要增加路由规则即可 3. 提高了系统的稳定性和负载能力	1. 切分后数据是分散的，很难利用数据库的关联查询，跨库查询性能较差 2. 拆分规则难以抽象 3. 分片数据的一致性难以解决 4. 数据扩容的难度和维护量极大

综上所述，我们发现垂直拆分和水平拆分具有共同点：

存在分布式事务问题
存在跨节点join的问题
存在跨节点合并排序、分页的问题
存在多数据源管理的问题

垂直拆分更偏向于业务拆分的过程，在技术上我们更倾向于水平切分的方案；

5 mysql分库分表主流的解决方案

ShardingJDBC

Mycat

6 SpringBoot整合Sharding-Jdbc实现分库分表

1 Sharding-Jdbc介绍

1.1. 简介

定位为轻量级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 Architecture

1.2.水平分割

（1）水平分库

概念：以字段为依据，按照一定策略，将一个库中的数据拆分到多个库中。
结果：每个库的结构都一样；数据都不一样；所有库的并集是全量数据；
（2）水平分表

概念：以字段为依据，按照一定策略，将一个表中的数据拆分到多个表中。
结果：每个表的结构都一样；数据都不一样；所有表的并集是全量数据；

2 利用Sharding-Jdbc实现单库中分表

2.1 引入依赖

	    <!-- sharding-jdbc -->
		<dependency>
		    <groupId>org.apache.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
		    <version>4.1.1</version>
		</dependency>
		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
		<dependency>
		    <groupId>org.mybatis.spring.boot</groupId>
		    <artifactId>mybatis-spring-boot-starter</artifactId>
		    <version>2.2.0</version>
		</dependency>
		<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>druid</artifactId>
		    <version>1.2.6</version>
		</dependency>

2.2 创建数据库表

CREATE TABLE `t_address` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(64) DEFAULT NULL COMMENT '编码',
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `pid` varchar(64) NOT NULL DEFAULT '0' COMMENT '父id',
  `type` int(11) DEFAULT NULL COMMENT '1国家2省3市4县区',
  `lit` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

CREATE TABLE `t_user0` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user1` (
  `id` bigint(20) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `city_id` int(12) DEFAULT NULL COMMENT '城市',
  `sex` tinyint(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(32) DEFAULT NULL COMMENT '邮箱',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `password` varchar(32) DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.3 配置application.yml文件

分表的规则：按照sex字段%2，平均分配

server:
  port: 8080
mybatis:
 mapper-locations: classpath:mapper/*.xml
spring:
  shardingsphere:
    datasource:
      names: ds0
      # 数据源ds0
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding_jdbc_test?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
        username: root
        password: root
    sharding:
      props:
        sql.show: true
      tables:
        t_user:  #t_user表
          key-generator-column-name: id  #主键
          actual-data-nodes: ds0.t_user${0..1}    #数据节点,均匀分布
          table-strategy:  #分表策略
            inline: #行表达式
              sharding-column: sex
              algorithm-expression: t_user${sex % 2}  #按模运算分配

2.4 直接写好插入和查询的逻辑

	@PostMapping("/user/save")
	public String save() {
		for (int i = 0; i < 10; i++) {
			User user = new User();
			user.setId((long) i);
			user.setName("test" + i);
			user.setCityId(1 % 2 == 0 ? 1 : 2);
			user.setCreateTime(new Date());
			user.setSex(i % 2 == 0 ? 1 : 2);
			user.setPhone("11111111" + i);
			user.setEmail("xxxxx");
			user.setCreateTime(new Date());
			user.setPassword("eeeeeeeeeeee");
			userMapper.save(user);
		}
		return "success";
	}

	@GetMapping("/user/get/{id}")
	public User get(@PathVariable Long id) {
		User user = userMapper.get(id);
		System.out.println(user.getId());
		return user;
	}