Shulou(Shulou.com)06/02 Report--

This article is about how to use Sharding-Jdbc to achieve mysql sub-database sub-table, the editor thinks it is very practical, so share it with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

A brief introduction to the implementation of mysql Sub-Library and Table by Sharding-Jdbc

Database sub-database sub-table is different from read-write separation, sub-database sub-table is to build the same table in multiple libraries and different tables in the same database, and find the implementation according to random or hash and other ways. The purpose of read-write separation is to solve the lack of read-write performance of the database. The master library master is used for write operation, the slave library slave for read operation, and the master database data synchronization through binglog. The implementation of database sub-database and sub-table can be realized by itself, or it can be realized by mycat and sharding-jdbc.

The concept of interruption

(1) olap and oltp online transaction processing OLTP (on-line transaction processing), online analytical processing OLAP (On-Line Analytical Processing). OLTP is the main application of traditional relational database, which mainly deals with basic and daily transactions, such as bank transactions. OLAP is the main application of data warehouse system, supporting complex analysis operations, focusing on decision support, and providing intuitive and easy-to-understand query results.

(2) the self-increasing ID of distributed database is not self-increasing. Paging queries in distributed databases need to be implemented using insertion time.

(3) explain command, explain shows how mysql uses indexes to process select statements and join tables. It can help select better indexes and write more optimized query statements. Just add it before the select statement.

Sharding-Jdbc introduction

Sharding-jdbc is an open source client agent for Dangdang. Sharding-jdbc includes library slicing and read-write separation functions. There is no intrusive code for the application, almost no changes, compatible with the mainstream orm framework, mainstream database connection pool. At present, the incubation project shardingSphere, which belongs to apache, is developing rapidly. Sharding-jdbc implementation of read-write separation can not achieve master-slave database data synchronization

Sharding-Jdbc use (Spring boot)

(1) create sharding-jdbc project and database 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 'primary key ID' primary key, user_id bigint (11) null comment' user ID', order_id bigint (11) null comment 'order ID'); create table order1 (id bigint (11) not null comment' primary key ID' primary key, user_id bigint (11) null comment 'user ID', order_id bigint (11) null comment' order ID')

(2) add dependencies

Io.shardingsphere sharding-jdbc-spring-boot-starter 3.0.0 com.alibaba druid-spring-boot-starter 1.1.13 mysql mysql-connector-java runtime org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.0

(3) configuration file

Spring.application.name=sharding-jdbc#mybatismybatis.configuration.map-underscore-to-camel-case=truemybatis.mapper-locations=classpath:mapper/*Mapper.xml# whether the same name is allowed to be registered when registering Invalid spring.main.allow-bean-definition-overriding=true# all master / slave libraries sharding.jdbc.datasource.names=dsmaster0,dsmaster1,dsmaster0slave0 in configuration center Dsmaster1slave0#dsmaster0sharding.jdbc.datasource.dsmaster0.type=com.alibaba.druid.pool.DruidDataSourcesharding.jdbc.datasource.dsmaster0.driver-class-name=com.mysql.cj.jdbc.Driversharding.jdbc.datasource.dsmaster0.url=jdbc:mysql://ailijie.top:3306/ds_master_0?useSSL=falsesharding.jdbc.datasource.dsmaster0.username=rootsharding.jdbc.datasource.dsmaster0.password=#slave for ds_master_0sharding.jdbc.datasource.dsmaster0slave0.type=com.alibaba.druid.pool.DruidDataSourcesharding.jdbc.datasource.dsmaster0slave0.driver-class-name=com.mysql. Cj.jdbc.Driversharding.jdbc.datasource.dsmaster0slave0.url=jdbc:mysql://ailijie.top:3306/ds_master_0_slave_0?useSSL=falsesharding.jdbc.datasource.dsmaster0slave0.username=rootsharding.jdbc.datasource.dsmaster0slave0.password=#dsmaster1sharding.jdbc.datasource.dsmaster1.type=com.alibaba.druid.pool.DruidDataSourcesharding.jdbc.datasource.dsmaster1.driver-class-name=com.mysql.cj.jdbc.Driversharding.jdbc.datasource.dsmaster1.url=jdbc:mysql://ailijie.top:3306/ds_master_1?useSSL=falsesharding.jdbc.datasource. Dsmaster1.username=rootsharding.jdbc.datasource.dsmaster1.password=#slave for ds_master_1sharding.jdbc.datasource.dsmaster1slave0.type=com.alibaba.druid.pool.DruidDataSourcesharding.jdbc.datasource.dsmaster1slave0.driver-class-name=com.mysql.cj.jdbc.Driversharding.jdbc.datasource.dsmaster1slave0.url=jdbc:mysql://ailijie.top:3306/ds_master_1_slave_0?useSSL=falsesharding.jdbc.datasource.dsmaster1slave0.username=rootsharding.jdbc.datasource.dsmaster1slave0.password=# sub-library rule sharding.jdbc.config.sharding.default-database-strategy. Inline.sharding-column=user_idsharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=dsmaster$ {user_id% 2} # split table rule 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_idsharding.jdbc.config.sharding.tables.order.table-strategy.inline.algorithmExpression=order$ {order_id% 2} # uses id as the Distributed primary key sharding.jdbc.config.sharding.tables.order.key-generator-column-name=user_id# logical master-slave library name and actual master-slave library mapping relationship # sharding.jdbc.config.sharding.master-slave-rules.ds0.master-data-source-name=dsmaster0# is separated by a comma # 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) entity class Order

[@ Data] (https://my.oschina.net/difrik)[@Builder](https://my.oschina.net/u/1245189)@NoArgsConstructor@AllArgsConstructorpublic class Order implements Serializable {private static final long serialVersionUID = 427226138907372838L; private Long id; private Integer userId; private Integer orderId;}

(4) controller external interface

[@ Slf4j] (https://my.oschina.net/slf4j)@RequestMapping("sharding")@RestControllerpublic 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 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 orders = orderMapper.queryByUserId (userId); log.info ("user: {}", orders) } @ RequestMapping ("deleteById") public void deleteById (@ RequestParam Long id) {orderMapper.deleteById (id); log.info ("user: {}", id);}} above is how to use Sharding-Jdbc to implement mysql library and table. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.