分库分表随记

背景

1.数据量过大单数据库磁盘IO次数过多影响性能
2.并发请求连接数过多打垮数据库

InnoDB推荐数据量

InnoDB存储引擎最小储存单元是页，一页大小就是16k。B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据，B+树结构图如下：

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

• 如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16

• 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是 8+6=14 字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。

同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，大概可以存放两千万左右的记录。B+树高度一般为1-3层，如果B+到了4层，查询的时候会多查磁盘的次数，SQL就会变慢。

因此单表数据量超过千万，就需要考虑分表，也有说法数据量达到500w就考虑分表

分库

1.垂直分库：按业务分，比如电商系统分出用户库，订单库，商品库等等
2.水平分库：将现有数据切分到不同数据库，每个服务器具有相同的库和表

分表

1.垂直分表：一个表的字段过多（几十上百）管理起来混乱，如果经常用select *更是会增加IO次数降低性能，因此取出某个字段做垂直分表的主键，将那些不常用的字段放入其他的表存储

2.水平分表：单表数据量过大，按照某种规则将数据切分到多张表中存储

水平分库分表策略

• range：将主键按范围划分到不同表中，例如0 ~ 1000w，1000w ~ 2000w …
  优点：易于扩容，不需要数据迁移
  缺点：容易造成热点数据问题，比如某段时间一直访问1000w ~ 2000w那部分的数据
• hash：取模的方式映射到不同的表中
  优点：不存在热点数据问题
  缺点：新增表时，历史数据要做迁移
• range + hash混合策略：range分库，hash分表

分库分表后产生的问题

• 事务问题：分库分表后，假设两个表在不同的数据库，那么本地事务已经无效，需要使用分布式事务。
• 跨库关联：跨节点Join的问题可以分两次查询实现
• 排序问题：跨节点的count,order by,group by以及聚合函数等问题：可以分别在各个节点上得到结果后在应用程序端进行合并。
• 分页问题：1.后端汇聚各个节点查询数据再分页；2.前端做分页，传来pageNo和pageSize，在各个节点执行分页，最后汇聚给前端
• 分布式ID：UUID（占用空间过大且不连续）或者雪花算法（推荐）

ShardingSphere中间件使用

在application.properties中配置：

# shardingjdbc分片策略
# 配置数据源，给数据源起名称,
# 水平分库，配置两个数据源
spring.shardingsphere.datasource.names=g1
 
# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true
 
#配置第一个数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.g1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.g1.url=jdbc:mysql://localhost:3306/guor?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.g1.username=root
spring.shardingsphere.datasource.g1.password=root
 
#指定数据库分布情况，数据库里面表分布情况
spring.shardingsphere.sharding.tables.course.actual-data-nodes=g1.student_$->{1..2}
 
# 指定student表里面主键id 生成策略  SNOWFLAKE
spring.shardingsphere.sharding.tables.student.key-generator.column=id
spring.shardingsphere.sharding.tables.student.key-generator.type=SNOWFLAKE
 
# 指定表分片策略  约定id值偶数添加到student_1表，如果cid是奇数添加到student_2表
spring.shardingsphere.sharding.tables.student.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.student.table-strategy.inline.algorithm-expression=student_$->{id % 2 + 1}
 
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true