# sharding-jdbc

**Repository Path**: MissJin/sharding-jdbc

## Basic Information

- **Project Name**: sharding-jdbc
- **Description**: mysql+springboot + shardingjdbc分库分表，读写分离的Demo
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2019-11-12
- **Last Updated**: 2021-03-03

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# sharding-jdbc

#### 项目介绍
***sharding-jdbc 使用demo***
> 1.整合了springboot+mybatis+pageHelper \
> 2.实现了`分表`-`读写分离`

#### 数据库脚本
```
# 创建network
docker network create mysql
# 创建master-mysql
docker run --network mysql --name master-mysql -v /home/lbz/test_mysql/master-mysql/mysql.cnf:/etc/mysql/mysql.cnf -v /home/lbz/test_mysql/master-mysql/data:/var/lib/mysql -p 13307:3306 -e MYSQL_ROOT_PASSWORD=missjin -d mysql:5.7.24 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
# 创建slaver-mysql
docker run --network mysql --name slaver-mysql -v /home/lbz/test_mysql/slaver-mysql/mysql.cnf:/etc/mysql/mysql.cnf -v /home/lbz/test_mysql/slaver-mysql/data:/var/lib/mysql -p 13308:3306 -e MYSQL_ROOT_PASSWORD=missjin -d mysql:5.7.24 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
```

#### 分库分表-读写分离

#### sql脚本
```sql
CREATE TABLE `user_0` (
  `user_id` int(11) NOT NULL COMMENT 'ID',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '密码',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
  `gender` int(255) DEFAULT NULL COMMENT '性别0未知1男2女',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `user_1` (
  `user_id` int(11) NOT NULL COMMENT 'ID',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '密码',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
  `gender` int(255) DEFAULT NULL COMMENT '性别0未知1男2女',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_temp` (
  `user_id` int(11) NOT NULL COMMENT 'ID',
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` int(255) DEFAULT NULL COMMENT '012',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

```
### 测试500万的数据量，性能如何

#### 1.定义一个存储过程，用于mock大数据
```sql
# 存储过程的定义
# 开启，不过重启会失效（This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)）
# 这个修改只对当前有效，重启服务器会失效，
# 可修改配置文件mysql.cnf 加入 log-bin-trust-function-creators=1 
set global log_bin_trust_function_creators=1;
# (1)定义生成字符串
delimiter $$
drop function if exists rand_string $$
create function rand_string(n int) returns varchar(255)
begin
    declare char_str varchar(100) default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n DO
        set return_str = concat(return_str,substring(char_str,floor(1+rand()*52),1));
        set i = i + 1;
    end while;
    return return_str;
end $$



# (2)定义生成的数字
delimiter $$
drop function if exists rand_num $$
create function rand_num() returns int(5)
begin
    declare i int default 0;
    set i = floor(100 + rand() * 10);
    return i;
end $$

delimiter ;
select rand_num(),rand_string(5);





# (3) 定义生成指定区间id的数据
# call insert_user_temp(10,5000000); // 生成500万的数据耗时27分钟,我的电脑的问题
# insert into user_0  select * from user_temp where user_id%2 = 0;
# insert into user_1  select * from user_temp where user_id%2 != 0;
delimiter $$
drop procedure if exists insert_user_temp $$
create procedure insert_user_temp(in start_num int(10),in max_num int (10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i + 1;
        insert into user_temp(user_id, username, password, name, gender, remark, create_time) values (start_num + i, rand_string(6),'123456', rand_string(6), floor(rand()*3), null, now());
        until i = max_num
    end repeat;
    commit;
end $$
delimiter ;
```
### 相关图片
![数据库图片](images/db.jpg)


### 总结 [参考官网说明](https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc)
- `优点`
> 
1. shardingJdbc 很平滑的对已有的业务做分表处理
2. 很友好的支持CRUD
3. 分页
4. 支持ACID（事务）
5. 性能，比起在一个db中有500万的数据，查询效率偏差很小

- `缺点` [不支持的功能](https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/unsupported-items/)
> 
1. 不支持`union`