分库分表原则
数据库切分根据其切分类型，可以分为两种方式：垂直切分和水平切分
数据库三大范式
范式是关系数据库理论的基础，也是我们设计数据库结构过程中索要遵循的规范和指导方法，常用的三大范式
第一范式（1NF）
强调的是列的原子性，即列不能再细化分成其他多列
第二范式（2NF）
除了符合第一范式外，还有两个部分。
一：表中必须有一个主键
二：没有包含在主键中得列，必须完全依赖于主键。
第三范式（3NF）
除了符合第二范式外，还要符合非主键必须直接依赖主键，而不是依赖于其他非主键的列，不存在依赖传递。
垂直（纵向）切分（专库专表）
垂直分切常见有垂直分库和垂直分表两种。
垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。
垂直分表是根据数据库中的列进行，某个表字段较多，将不常用或者字段长度较大的字段拆分到扩展表中。
水平（横向）切分（同一数据多个表或库）
水平切分分为库内分表和分库分表
根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果
库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决
水平切分后，同一张表会出现在多个数据库或表中，几个库或表的内容不同。几种典型的数据分片规则：
根据数值范围
按照时间区间或ID区分来切分。
根据数值取模
一般采用hash取模的分切方式。例如：将表根据某个字段切分到四份库中，余数为0放第一个库，余数为1放到第二个，以此类推。如果查询条件带有该字段，则可明确定位到相应库去查询
分库分表带来的问题
- 事务一致性问题
在提交事务时需要协调多个节点，推后了提交事务的时间点，延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高 - 跨界点关联查询join问题
数据可能分布在不同的节点上，此时join带来的问题就比较麻烦了，考虑到性能，尽量避免使用join查询。 - 跨节点分页、排序、函数问题
跨节点多库进行查询时，会出现limit分页、order by排序等问题
需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户
如果取得页数很大，情况则变得复杂很多，因为各分片节点中的数据可能是随机的，为了排序的准确性，需要将所有节点的前N页数据都排序好做合并，最后再进行整体的排序，这样的操作时很耗费CPU和内存资源的，所以页数越大，系统的性能也会越差 - 全局主键避重问题
在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。 - 数据迁移、扩容问题
MyCat（proxy模式）
修改配置文件
server.xml
<!--mycat的账号密码-->
<user name="mycat">
<property name="password">mycat</property>
<!--添加两个mycat逻辑库：user,pay-->
<property name="schemas">user,pay</property>
</user>
连接方式和mysql相同，默认的 Mycat 端口是 8066
schema.xml
<!-- user对应的逻辑库,userNode逻辑库对应的分片 -->
<schema name="user" checkSQLschema="false" sqlMaxLimit="100" dataNode="userNode" />
<!-- userDB对应的数据库，host 实际的物理库配置地址 -->
<dataNode name="userNode" dataHost="host" database="userDB" />
<schema name="pay" checkSQLschema="false" sqlMaxLimit="100" dataNode="pay" >
<!-- 指定order表 所在的数据库位置 -->
<table name="order" dataNode="pay1,pay2" rule="rule1"/>
</schema>
<dataNode name="pay1" dataHost="host" database="pay1" />
<dataNode name="pay2" dataHost="host" database="pay2" />
<!--
balance =0：不开启读写分离，所有读操作都发送到当前可用的writeHost
balance =1：双主双从模式，只有一台writeHost写，其他的全部参与读操作的负载均衡
balance =2：所有读操作随机发生再 读主机和写主机 上
balance =3：所有读请求随机发送到 readHost
-->
<dataHost name="host" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native">
<!-- 通过向数据库执行select 1这个简单的sql语句判断是否保持链接。 -->
<heartbeat>select 1</heartbeat>
<!-- 写主机 -->
<writeHost host="hostM1" url="192.168.0.2:3306" user="root" password="root" >
<!-- 读主机 -->
<readHost host="hostS2" url="192.168.0.3:3306" user="root" password="root" />
</writeHost>
<writeHost host="hostM2" url="192.168.0.4:3306" user="root" password="root" />
</dataHost>
- table ：实际表配置
- name：表名称
- dataNode：dataNode分区名称
- rule：对应规则名称
- dataNode：分区信息
- name：dataNode分区名称
- dataHost：dataHost名称
- database：指定连接的物理库名称
- dataHost：数据库信息
- name：dataHost名称
rule.xml
<mycat:rule xmlns:mycat="http://org.opencloudb/">
<tableRule name="sharding-by-hour">
<rule>
<columns>createTime</columns>
<algorithm>sharding-by-hour</algorithm>
</rule>
</tableRule>
<function name="sharding-by-hour" class="org.opencloudb.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property>
</function>
</mycat:rule >
tableRule
- name 为schema.xml 中table 标签中对应的 rule=“sharding-by-hour” ,也就是配置表的分片规则，
- columns 是表的切分字段： createTime 创建日期。
- algorithm 是规则对应的切分规则：映射到function 的name。
function
- function 配置是分片规则的配置。
- name 为切分规则的名称，名字任意取，但是需要与tableRule 中匹配。
- class 是切分规则对应的切分类，写死，需要哪种规则则配置哪种，例如本例子是按小时分片：org.opencloudb.route.function.LatestMonthPartion
- property 标签是切分规则对应的不同属性，不同的切分规则配置不同。
ER表：分表通过创建关联查询
<table name="customer" dataNode="dn1,dn2" rule="sharding-by-intfile">
<!--
customer主表，orders子表
name：子表的名称
joinKey：子表的字段
parentKey：主表被关联的字段
primaryKey：子表的主键
-->
<childTable name="orders" joinKey="customer_id" parentKey="id" primaryKey="id"/>
</table>
全局序列：数据库配置方式
创建 MYCAT_SEQUENCE 表存放序列号，当作插入数据的主键
- name 序列号名称
- current_value 当前 value
- increment 增长步长! 可理解为 mycat 在数据库中一次读取多少个 sequence. 当这些用完后, 下次再从数据库中读取。
第一步创建表
创建表
CREATE TABLE MYCAT_SEQUENCE (name VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(name)) ENGINE=InnoDB;
插入数据
<!--设置序列号从100000开始，每次重新获取步长为100-->
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES (‘GLOBAL’, 100000, 100);
创建三个相关函数
1. 获取当前 sequence 的值 (返回当前值,增量)
DROP FUNCTION IF EXISTS mycat_seq_currval;
DELIMITER
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf-8
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval=“-999999999,null”;
SELECT concat(CAST(current_value AS CHAR),“,”,CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval;
END
DELIMITER;
2. 设置 sequence 值
DROP FUNCTION IF EXISTS mycat_seq_setval;
DELIMITER
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64) CHARSET utf-8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
DELIMITER;
3. 获取下一个 sequence 值
DROP FUNCTION IF EXISTS mycat_seq_nextval;
DELIMITER
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf-8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
DELIMITER;
第二步修改配置
sequence_db_conf.properties
<!--
USER_SEQ：schema.xml中，schema 的name或table的name
test_dn1：dataNode 名称
-->
USER_SEQ=test_dn1
server.xml
<system><property name="sequnceHandlerType">1</property></system>
<!--
sequnceHandlerType 需要配置为 1，表示使用数据库方式生成 sequence。
-->
插入数据时
<!-- 通过next value for MYCATSEQ_GLOBAL获取序列号当主键-->
insert into table(id,name) values(next value for MYCATSEQ_GLOBAL,‘test’)
ShardingSphere（推荐）
有三款产品
- ShardingSphere-JDBC
- 使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动
- ShardingSphere-Proxy
- 透明化的数据库代理端，可直接当做 MySQL/PostgreSQL 使用。
- ShardingSphere-Sidecar（TODO）
ShardingSphere-JDBC
sharding-JDBC不是做分库分表，目的是用来简化对分库分表之后数据相关操作：数据分片和读写分离
水平分表
springboot + mybaitsPlus + Sharding-JDBC + Druid连接池
根据存入数据id是否为偶数去分表
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<!--mysql依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--sharding依赖-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<!--mybatisPlus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
</dependencies>
创建数据库course_db
创建表course_1和course_2，分别存入id为偶数和id为奇数的数据
CREATE TABLE `course_1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`c_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT 'userid',
`status` smallint(1) NOT NULL DEFAULT '0' COMMENT '状态（0正常 1冻结）',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;
# 配置真实数据源
spring.shardingsphere.datasource.names=ds0
# 配置数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
# 配置 course表规则,
#其中course为自定义名称
#行表达式标识符 $->{...}，$是占位符，括号里边的是值
#表示ds0数据库中的course_1和course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds0.course_$->{1..2}
#指定course表里面主键字段，及主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# 配置分表策略
#指定分区字段
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
#分区字段取模，能被2整除等于0，否则等于1，+1 后等于1和2，
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id%2 + 1}
#显示sql
spring.shardingsphere.props.sql.show=true
策略配置5X官方写法
# 配置分表策略
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=table_inline
spring.shardingsphere.rules.sharding.sharding-algorithms.table_inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table_inline.props.algorithm-expression=t_order_${order_id % 2}
代码中使用一个实体类Course，对应数据库两张表（course_1,course_1）会报错
Consider renaming one of the enabling overriding by setting spring.main.allow-bean-definition-overriding=true
按照提示在配置文件中设置
spring.main.allow-bean-definition-overriding=true
因为配置中已经设置了主键生成策略是雪花算法，所以插入数据时，主键都会根据雪花算法计算出来。
查询也会根据
代码
---------- Course --------------------
@Data
public class Course {
private Long id;
private String cName;
private Integer userId;
private Integer status;
}
---------- CourseMapper --------------------
@Repository
public interface CourseMapper extends BaseMapper<Course> {
}
----------- ShradingjdbcApplication --------------------
@SpringBootApplication
@MapperScan("com.example.shradingjdbc.mapper")
public class ShradingjdbcApplication {
public static void main(String[] args) {
SpringApplication.run(ShradingjdbcApplication.class, args);
}
}
------------------ShradingjdbcApplicationTests --------------------
@SpringBootTest
class ShradingjdbcApplicationTests {
@Autowired
CourseMapper courseMapper;
@Test
void contextLoads() {
for (int i = 0; i < 10; i++) {
Course course = new Course();
course.setCName("ceshi+" + i);
course.setUserId(i);
courseMapper.insert(course);
}
}
@Test
void test1(){
List<Course> courses = courseMapper.selectList(new QueryWrapper<>());
for (Course cours : courses) {
System.out.println(cours);
}
}
}
水平分库
两个数据库ds0,ds1，每个数据库都有两个表t_order0，t_order1
# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=
# 配置第 2 个数据源
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=
# 配置 t_order 表规则
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
# 配置分库策略
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=database_inline
# 配置分表策略
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=table_inline
# 省略配置 t_order_item 表规则...
# ...
# 配置 分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.database_inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database_inline.props.algorithm-expression=ds_${user_id % 2}
spring.shardingsphere.rules.sharding.sharding-algorithms.table_inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table_inline.props.algorithm-expression=t_order_${order_id % 2}
垂直分库（专库专表）
所有user信息存储在user_db库的t_user表中
# 配置真实数据源
spring.shardingsphere.datasource.names=ds0
# 配置数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/user_db
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=12356
# 配置 t_user表规则,
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user
#指定course表里面主键字段，及主键生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 配置分表策略
#指定分区字段
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
#分表策略
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user
操作公共表
每个数据库都创建一个相同的表t_dict
公共表三个字段：主键id，类型key，类型值value
#设置公共表
spring.shardingsphere.sharding.broadcast-tables=t_dict
#主键生成策略
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
增删改 数据会向每个数据库的t_dict表中增删改数据
读写分离
spring.shardingsphere.datasource.names=m0,s0
# 配置第 1 个数据源
spring.shardingsphere.datasource.m0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.jdbc-url=jdbc:mysql://localhost:3306/m0
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=
# 配置第 2 个数据源
spring.shardingsphere.datasource.s0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.jdbc-url=jdbc:mysql://localhost:3306/s0
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=
#声明主从，并声明数据源为ds0
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slvave-data-source-name=s0
#假设只复制t_user，分表固定分配在t_user表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user
ShardingSphere-Proxy
下载解压，修改配置文件
server.yaml取消注释即可
authentication:
users:
root:
password: root
sharding:
password: sharding
authorizedSchemas: sharding_db
props:
max-connections-size-per-query: 1
acceptor-size: 16 # The default value is available processors count * 2.
executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
# LOCAL: Proxy will run with LOCAL transaction.
# XA: Proxy will run with XA transaction.
# BASE: Proxy will run with B.A.S.E transaction.
proxy-transaction-type: LOCAL
proxy-opentracing-enabled: false
proxy-hint-enabled: false
query-with-cipher-column: true
sql-show: false
check-table-metadata-enabled: false
config-sharding.yaml
- 复制mysql-connector-java-8.0.18.jar到lib目录
- 取消注释
schemaName: sharding_db
dataSourceCommon:
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_item_inline
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
按实际情况修改上边的配置。
- 通过bin目录脚本启动ShardingSphere
- 连接ShardingSphere操作数据库，连接方式和连接mysql相同。
扫一扫
2345
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
