分库分表与分布式主键
分库分表Apache ShardingSphere
为什么要分库分表
- mysql索引采用 B + Tree
- 系统从硬盘读取数据到内存是以磁盘块(block)为基本单位,Mysql的innodb引擎有页的概念,默认每页大小为16KB(show variables like ‘innodb_page_size’; 可通过该命令查看),磁盘块往往没有达到16KB,innodb每次会读取若干连续磁盘块达到16KB,即读取是以页(16KB)为单位
- 索引一般为4个字节或8个字节,主键一般使用bigint 8字节,即1页可以读取16KB/(8+6)=1170个主键,叶子节点存放数据(按单行1KB算,存16个),深度为3(innodb默认根节点在内存中,读取硬盘进行2次I/O操作)的话,能存储1170117016≈2000W数据,再多数据会增加深度,影响查询效率
- 总上分库分表,增加效率
分库分表使用分类
- Sharding JDBC 代码层面维护库与表的关系
- Sharding Proxy 代理方式,正常使用SQL,经过Proxy转化后操作
- K8S 直接使用newSQL(如TIDB)
Sharding-JDBC | Sharding-Proxy | |
---|---|---|
数据库 | 任意 | MySQL |
连接消耗数 | 高 | 低 |
异构语言 | 仅 Java | 任意 |
性能 | 损耗低 | 损耗略高 |
无中心化 | 是 | 否 |
静态入口 | 无 | 有 |
Sharding JDBC
- 执行sql脚本,创建库和表(库放在不同服务器上可提供瓶颈)
CREATE DATABASE `myshop-0` DEFAULT CHARACTER SET utf8 ;
USE `myshop-0`;
CREATE TABLE tb_order_0 (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, order_id BIGINT(20) NOT NULL, user_id BIGINT(20) NOT NULL);
CREATE TABLE tb_order_1 (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, order_id BIGINT(20) NOT NULL, user_id BIGINT(20) NOT NULL);
CREATE TABLE tb_order_item_0 (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id BIGINT(20) NOT NULL, order_id BIGINT(20) NOT NULL, order_item_id BIGINT(20) NOT NULL);
CREATE TABLE tb_order_item_1 (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id BIGINT(20) NOT NULL, order_id BIGINT(20) NOT NULL, order_item_id BIGINT(20) NOT NULL);
CREATE DATABASE `myshop-1` DEFAULT CHARACTER SET utf8 ;
USE `myshop-1`;
CREATE TABLE tb_order_0 (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, order_id BIGINT(20) NOT NULL, user_id BIGINT(20) NOT NULL);
CREATE TABLE tb_order_1 (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, order_id BIGINT(20) NOT NULL, user_id BIGINT(20) NOT NULL);
CREATE TABLE tb_order_item_0 (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id BIGINT(20) NOT NULL, order_id BIGINT(20) NOT NULL, order_item_id BIGINT(20) NOT NULL);
CREATE TABLE tb_order_item_1 (id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id BIGINT(20) NOT NULL, order_id BIGINT(20) NOT NULL, order_item_id BIGINT(20) NOT NULL);
- 添加依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<!-- MySQL 驱动的版本号必须是 5.1.48 -->
<version>5.1.48</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC3</version>
</dependency>
- 配置分库分表规则
application.yml
spring:
main:
allow-bean-definition-overriding: true
application:
name: sharding-jdbc
shardingsphere:
# 属性配置
props:
# 是否开启 SQL 显示,默认值: false
sql:
show: true
# 数据源配置,可配置多个
datasource:
# 本案例中配置了两个数据源,分别对应刚才创建的两个 MySQL 容器
names: db0,db1
db0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.150:3306/myshop-0?useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong&useSSL=false
username: root
password: '123456'
hikari:
minimum-idle: 5
idle-timeout: 600000
maximum-pool-size: 10
auto-commit: true
pool-name: MyHikariCP
max-lifetime: 1800000
connection-timeout: 30000
connection-test-query: SELECT 1
db1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.150:3306/myshop-1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Hongkong&useSSL=false
username: root
password: '123456'
hikari:
minimum-idle: 5
idle-timeout: 600000
maximum-pool-size: 10
auto-commit: true
pool-name: MyHikariCP
max-lifetime: 1800000
connection-timeout: 30000
connection-test-query: SELECT 1
# 分片规则配置
sharding:
# 绑定表规则列表
binding-tables: tb_order,tb_order_item
# 默认数据库分片策略,同分库策略
default-database-strategy:
inline:
# 分片算法行表达式,需符合 groovy 语法
# 此处根据 user_id 分片
# 如果 user_id 为奇数则落入奇数库即 db1 匹配的数据源
# 如果 user_id 为偶数则落入偶数库即 db0 匹配的数据源
algorithm-expression: db$->{user_id % 2}
# 分片列名称
sharding-column: user_id
# 数据分片规则配置,可配置多个
tables:
# 逻辑表名称
tb_order:
# 由数据源名 + 表名组成,以小数点分隔
actual-data-nodes: db$->{0..1}.tb_order_$->{0..1}
# 分表策略,同分库策略
table-strategy:
inline:
# 此处根据 order_id 分片
# 如果 order_id 为奇数则落入奇数表即 tb_order_1
# 如果 order_id 为偶数则落入偶数表即 tb_order_0
algorithm-expression: tb_order_$->{order_id % 2}
# 分片列名称
sharding-column: order_id
tb_order_item:
actual-data-nodes: db$->{0..1}.tb_order_item_$->{0..1}
table-strategy:
inline:
algorithm-expression: tb_order_item_$->{order_id % 2}
sharding-column: order_id
mybatis:
mapper-locations: classpath:mapper/*.xml
- 其余和原有操作一样,即可分库分表,但是会产生主键冲突问题,因此需要分布式主键
Sharding Proxy
分布式主键
为什么使用分布式主键
多数据源时,会产生主键冲突问题
分布式主键方案
UUID
优点: 性能高,本地生产,没有网络消耗
缺点: Mysql官方建议主键越短越好(UUID 36字符串长度),在Innodb引擎下,会引起数据频繁移动,验证影响性能。
SNOWFLAKE
雪花算法是由 Twitter 公布的分布式主键生成算法,它能够保证不同进程主键的不重复性,以及相同进程主键的有序性。
美团Leaf是基于雪花算法的一个分布式主键中间件。