数据库分库分表和读写分离区别,分库分表是在多个库建相同的表和同一个库建不同的表,根据随机或者哈希等方式查找实现。读写分离是为了解决数据库的读写性能不足,使用主库master进行写操作,从库slave进行读操作,通过binglog实现主被库数据的同步。
实现数据库分库分表可以自己实现,也可以使用mycat和sharding-jdbc实现。
(1)olap和oltp联机事务处理OLTP(on-line transaction processing)、联机分析处理OLAP(On-Line Analytical Processing)。OLTP是传统的关系型数据库的主要应用,主要是基本的、日常的事务处理,例如银行交易。OLAP是数据仓库系统的主要应用,支持复杂的分析操作,侧重决策支持,并且提供直观易懂的查询结果。
(2)分布式数据库的自增ID不是自增的。分布式数据库分页查询需要使用插入时间实现。
(3)explain命令,explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。在select语句前面加上就可以。
sharding-jdbc是当当网开源的一款客户端代理中间价。sharding-jdbc包含分库分片和读写分离功能。对应用的代码没有侵入型,几乎没有任何改动,兼容主流orm框架,主流数据库连接池。目前属于apache的孵化项目shardingSphere,发展迅猛。 sharding-jdbc实现实现读写分离不能实现主从库数据同步
(1)创建sharding-jdbc项目和数据库 ds_master_0,ds_master_1,ds_master_0_slave_0,ds_master_1_slave_0
create table order0 ( id bigint(11) not null comment '主键ID' primary key, user_id bigint(11) null comment '用户ID', order_id bigint(11) null comment '订单ID' ); create table order1 ( id bigint(11) not null comment '主键ID' primary key, user_id bigint(11) null comment '用户ID', order_id bigint(11) null comment '订单ID' );
(2)添加依赖
<dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>3.0.0</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.13</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency>
(3)配置文件
spring.application.name=sharding-jdbc #mybatis mybatis.configuration.map-underscore-to-camel-case=true mybatis.mapper-locations=classpath:mapper/*Mapper.xml #当注册遇到相同名字是否允许被注册,在配置中心无效 spring.main.allow-bean-definition-overriding=true #所有主从库 sharding.jdbc.datasource.names=dsmaster0,dsmaster1,dsmaster0slave0,dsmaster1slave0 #dsmaster0 sharding.jdbc.datasource.dsmaster0.type=com.alibaba.druid.pool.DruidDataSource sharding.jdbc.datasource.dsmaster0.driver-class-name=com.mysql.cj.jdbc.Driver sharding.jdbc.datasource.dsmaster0.url=jdbc:mysql://ailijie.top:3306/ds_master_0?useSSL=false sharding.jdbc.datasource.dsmaster0.username=root sharding.jdbc.datasource.dsmaster0.password= #slave for ds_master_0 sharding.jdbc.datasource.dsmaster0slave0.type=com.alibaba.druid.pool.DruidDataSource sharding.jdbc.datasource.dsmaster0slave0.driver-class-name=com.mysql.cj.jdbc.Driver sharding.jdbc.datasource.dsmaster0slave0.url=jdbc:mysql://ailijie.top:3306/ds_master_0_slave_0?useSSL=false sharding.jdbc.datasource.dsmaster0slave0.username=root sharding.jdbc.datasource.dsmaster0slave0.password= #dsmaster1 sharding.jdbc.datasource.dsmaster1.type=com.alibaba.druid.pool.DruidDataSource sharding.jdbc.datasource.dsmaster1.driver-class-name=com.mysql.cj.jdbc.Driver sharding.jdbc.datasource.dsmaster1.url=jdbc:mysql://ailijie.top:3306/ds_master_1?useSSL=false sharding.jdbc.datasource.dsmaster1.username=root sharding.jdbc.datasource.dsmaster1.password= #slave for ds_master_1 sharding.jdbc.datasource.dsmaster1slave0.type=com.alibaba.druid.pool.DruidDataSource sharding.jdbc.datasource.dsmaster1slave0.driver-class-name=com.mysql.cj.jdbc.Driver sharding.jdbc.datasource.dsmaster1slave0.url=jdbc:mysql://ailijie.top:3306/ds_master_1_slave_0?useSSL=false sharding.jdbc.datasource.dsmaster1slave0.username=root sharding.jdbc.datasource.dsmaster1slave0.password= #分库规则 sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=dsmaster${user_id % 2} #分表规则 sharding.jdbc.config.sharding.tables.order.actual-data-nodes=dsmaster${0..1}.order${0..1} sharding.jdbc.config.sharding.tables.order.table-strategy.inline.shardingColumn=order_id sharding.jdbc.config.sharding.tables.order.table-strategy.inline.algorithmExpression=order${order_id % 2} #使用id作为分布式主键 sharding.jdbc.config.sharding.tables.order.key-generator-column-name=user_id #逻辑主从库名和实际主从库映射关系 #sharding.jdbc.config.sharding.master-slave-rules.ds0.master-data-source-name=dsmaster0 #用逗号分隔 #sharding.jdbc.config.sharding.master-slave-rules.ds0.slave-data-source-names=dsmaster0 #sharding.jdbc.config.sharding.master-slave-rules.dsmaster1.masterDataSourceName=dsmaster1 #sharding.jdbc.config.sharding.master-slave-rules.dsmaster1.slaveDataSourceNames=dsmaster1slave0
(5)实体类 Order
@Data @Builder @NoArgsConstructor @AllArgsConstructor public class Order implements Serializable { private static final long serialVersionUID = 427226138907372838L; private Long id; private Integer userId; private Integer orderId; }
(4)controller外部接口
@Slf4j @RequestMapping("sharding") @RestController public class ShardingController { @Autowired private OrderMapper orderMapper; @RequestMapping public String helloShardin(){ return "hello Sharding-jdbc"; } @RequestMapping("insert") public void insert(@RequestParam Integer orderId, @RequestParam Integer userId) { Order order = Order.builder() .orderId(orderId).userId(userId).build(); orderMapper.insert(order); Long id = order.getId(); log.info("Generated Key--id:" + id); } @RequestMapping("queryAll") public void findAll() { List<Order> orders = orderMapper.queryAll(); log.info("user:{}", orders); log.info("user:{}",orders.size()); } @RequestMapping("getById") public void getById(@RequestParam Long id) { Order order = orderMapper.queryById(id); log.info("user:{}", order); } @RequestMapping("getByUserId") public void getByUserId(@RequestParam Long userId) { List<Order> orders = orderMapper.queryByUserId(userId); log.info("user:{}", orders); } @RequestMapping("deleteById") public void deleteById(@RequestParam Long id) { orderMapper.deleteById(id); log.info("user:{}", id); } }
使用sharding-jdbc没有侵入性,不会影响业务代码。
可以使用Springboot的配置文件规范进行配置。
有问题,请留言!
个人博客地址 欢迎访问