Basic concepts of a sub-database and sub-table

1.1 depots

Deploy multiple databases on a node. Because when many clients connect to the client, the concurrency is high and the repository needs to be divided.Copy the code

Table 1.2

Divide a table into multiple tables. Because a single table in MySQL has 50 million pieces of data, the database performance is severely degraded.Copy the code

1.3 partition

  1. An overview of the
Partition technology is database internal technology, for our developers, a table partition is still a table, we develop operation table name has not changed. It simply divides the table into physical areas.Copy the code
  1. Why not use partitions
Limited by single machine factors (large number of connections, high throughput), although each partition is independent, but the total entry of the partition table is only one.Copy the code

Two sub – database sub – table middleware

The reason why we introduce middleware is that when we operate singleton databases, we use JDBC to connect to a given database and a given table. But when we divide the database into tables, our program will change the database from one to two or more, and the tables into two or more. We can't use the same library and the same table using the original JDBC technology. Therefore, we introduce tools similar to those written by others. Our business code is the same as before, and the utility class summarizes the operations of multiple libraries and multiple tables. To meet business requirements.Copy the code

2.1 Common Middleware

  1. Common middleware is divided into two types
Client mode Proxy modeCopy the code
  1. sharding-jdbc(client)

note

Introducing sharding-JDBC jar, introducing sharding-JDBC plug-in. Can replace JDBC operation database (multiple databases, multiple tables).Copy the code
  1. mycat(proxy)

note

Because MyCAT is deployed separately, the single-machine mode is not secure and requires multiple machine deployments.Copy the code

Three sub – database sub – table need to pay attention to the problems & problems generated analysis

3.1 Principle of separate database and table

  1. If you can, you can’t. Because the division of database and table leads to complex business logic and complex code writing.
  2. Decoupling requires the separation of some of the frequently used tables from the infrequently used tables.
  3. The amount of data is too large to meet normal service access, and the data is divided into tables and libraries.

3.2 Database and table architecture scheme

  1. Vertical depots
Database concurrency up, the amount of data is not very large, vertical branch library. After the database structure is different, the data is different.Copy the code

2. Horizontal sub-database sub-table

Not much concurrency, not much data. However, there are many fields in the table, and some fields are often queried, while others are not. So you need to split the fields. The I/O time of query is reduced.Copy the code

3. The level of depots

Large amount of concurrent, large amount of data, using horizontal branch library. Will a database, split several databases, database structure is the same, data is not the same.Copy the code

4. The level of table 

The amount of concurrency is small, and the amount of data in the tables in the database is large. Use horizontal subscale. Divide a table into several tables with the same data structure but different data in each table.Copy the code

3.3 Analysis of problems arising from database and table

  1. Distributed transaction problems occur
Previously data operations were performed in a database and could be resolved using database transactions. Now the operation may face more than one database, so the transaction of the database cannot work. Adopt piecewise commit transaction to achieve consistent solution.Copy the code
  1. Cross node join problem
In a database for multi-table associated query, directly use join query. After database and table partitioning, multiple tables are distributed to multiple nodes, and join associative query cannot be used only. Use field redundant scheme, or call other services, result merge.Copy the code
  1. Primary key duplication problem
Previously, primary key increment was used. After horizontal partitioning, the primary key of the data in the table is repeated, which is generated by uUID or snowflake algorithm.Copy the code
  1. Sorting paging problem on multiple nodes
The previous paging and sorting of a table can be done directly with limit and order by. Now that the data in a table is split into multiple tables, you need to retrieve the multiple table data before paging and sorting. For example, paging: 1000-1010 entries to fetch. Fetch the first 1010 data from all tables, then merge all data and page it out.Copy the code

Fourth, use Sharding-JDBC to process the sub-database sub-table

4.1 Sharding – JDBC overview

  1. An overview of the
Sharding-JDBC use, just introduce a JAR package can be used directly, can be understood as JDBC enhanced version. Sharding-jdbc also supports a variety of ORM frameworks and connection pooling for third parties. Using Sharing-JDBC to operate on multiple data sources, the business code is the same as before for JDBC to operate on a single database table. Don't worry about how many data sources are in any place, hand it over to Sharding-JDBC.Copy the code
  1. The basic concept

Logical table

Level divides a table into several tables and extracts a table name for the business code to call. The extracted table name is the logical table.Copy the code

The actual table

Table that exists on the hard disk after table partitioning.Copy the code

Dynamic table

Logical and physical tables are not necessarily statically configured in configuration. For example, in a sharding scenario by date, the physical table name changes over time.Copy the code

Data nodes

Physical table nameCopy the code

The binding table

The master and slave tables are sharded using the same shard key. For example, order_base(order table) and order_detail(order detail table) are shard based on order IDS.Copy the code

Shard key

Manipulate data from different tables and libraries based on a field value in a table.Copy the code

Subdivision algorithm

Divide data into different tables and libraries according to a strategy based on shard keys. Supports large > < >= <= BETWEEN and in sharding.Copy the code

Shard strategy

Sharding key + Sharding algorithmCopy the code

4.2 Level table implementation

  1. Import sharding-JDBC dependencies

  2. Sharding – JDBC configuration

Import dependence

<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> < version > 4.0.0 - RC1 < / version > < / dependency >Copy the code

Configuring a Data Source

spring.shardingsphere.datasoure.names=m1 spring.shardingsphere.datasoure.m1.type=com.alibaba.druid.pool.DruidDatasource spring.shardingsphere.datasoure.m1.driver-class-name=com.mysql.jdbc.Driver Spring. Shardingsphere. Datasoure. M1. Url = JDBC: mysql: / / 127.0.0.1:3306 / order_db spring.shardingsphere.datasoure.m1.usename=root spring.shardingsphere.datasoure.m1.password=rootCopy the code

Configure the primary key generation mode

spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id
spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE
Copy the code

Configure data nodes (define logical and physical tables)

spring.shardingsphere.sharding.tables.t_order.actualDataNodes=m1.t_order_$->{1.. 2}Copy the code

Configuring sharding policies (sharding keys and sharding algorithms)

spring.shardingsphere.sharding.tables.t_order.tableStrategy.inline.shardingColumn=order_id
spring.shardingsphere.sharding.tables.t_order.tableStrategy.inline.algorithmExpression=t_order_${order_id % 2 + 1}
Copy the code
  1. View the SQL execution result

Insert multiple pieces of data to execute SQL Query data SQL 4. What to do with sharding-JDBC to get the user’s SQL

Parse SQL to obtain fragment values; Insert into the corresponding table according to the fragment value according to the fragment algorithm, such as the above fragmentation algorithm T_ORDER_ ${order_ID_ % 2 + 1}, insert into order_ID_2 table when order_ID is odd, insert into order_ID_1 table when order_id is even. Changing the shard value changes the SQL, and then executes the changed SQL. Merges the results of the execution.Copy the code

4.3 Horizontal repository

  1. The configuration file
/ / define the data source name spring. Shardingsphere. The datasource. Names = m1, m2 / / define several concrete according to the source spring.shardingsphere.datasource.m1.type=com.alibaba.pool.DruidDatasorce spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver Spring. Shardingsphere. The datasource. M1. Url = JDBC: mysql: / / 127.0.0.1:3306 / order_db_1? UseUnicode = true spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=root spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver Spring. Shardingsphere. The datasource. M1. Url = JDBC: mysql: / / 127.0.0.1:3306 / order_db_2? UseUnicode = true Spring. Shardingsphere. The datasource. M1. Username = root spring. Shardingsphere. The datasource. M1. Password = root / / configuration data node spring.shardingsphere.sharding.tables.t_order.actualDataNodes=m$->{1.. 2}.t_order$->{1... 2} / / define the primary key generation strategy. Spring shardingsphere. Sharding. Name t_order. KeyGenerator. Column = order_id Spring. Shardingsphere. Sharding. Tables. T_order. KeyGenerator. Type = SNOWFLAKE definition / / shard strategy + subdivision algorithm (divided) / / table spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=order_id spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=t_order_$->{order_id % 2 + 1} / / depots spring. Shardingsphere. Sharding. Name t_order. DatabaseStrategy. Inline. ShardingColumn = user_id spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=m$->{user_id % 2 + 1}Copy the code
  1. Viewing the Execution Result

4.4 Vertical branch library implementation

  1. An overview of the
Generally, a library is divided into several libraries according to the business, and each library has a different table structure and data. You can use Sharing-JDBC to work with multiple data sources (different databases). But many use distributed processing.Copy the code
  1. The configuration file
/ / configuration database name spring. Shardingsphere. The datasource. Names = m1, s1 / / configure the data source spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver Spring. Shardingsphere. The datasource. M1. Url = JDBC: mysql: / / 127.0.0.1:3306 / order_db spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=root spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.jdbc.Driver Spring. Shardingsphere. The datasource. S1. Url = JDBC: mysql: / / 127.0.0.1:3306 / user_db Spring. Shardingsphere. The datasource. S1. The username = root spring. Shardingsphere. The datasource. S1. The password = root / / configuration data node spring.shardingsphere.sharding.tables.t_user.actualDataNodes=s1.t_user Spring. Shardingsphere. Sharding. Tables. T_order. ActualDataNodes = m1. T_order / / configuration primary key generation spring.shardingsphere.sharding.tables.t_user.keyGenerator.column=user_id spring.shardingsphere.sharding.tables.t_user.keyGenerator.type=SNOWFLAKE spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id Spring. Shardingsphere. Sharding. Tables. T_order. KeyGenerator. Type = SNOWFLAKE / / configuration subdivision strategy spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=order_id spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=t_order spring.shardingsphere.sharding.tables.t_user.databaseStrategy.inline.shardingColumn=user_id spring.shardingsphere.sharding.tables.t_user.databaseStrategy.inline.algorithmExpression=t_userCopy the code
  1. Viewing the Execution Result

4.5 Read/Write Separation

  1. What is read-write separation
The program writes data to a database, and reads data to another database. Use the data synchronization function of mysql to synchronize data from the write database to the read database.Copy the code

2. Benefits of read-write separation

A large amount of data is written to the database, resulting in row locks, which reduces query performance. Therefore, separating read from write can improve database performance.Copy the code
  1. The solution

note

Achieving read/write separation is fairly simple in two steps. Step 1, implement database data synchronization - change MySQL configuration file implementation. The second step is to implement the call of multiple data sources according to the sharding-JDBC configuration of the database and table.Copy the code
  1. Setting Data Synchronization

// Change the configuration file of the master library

# open log log - bin = mysql - bin # set up the service id, master-slave services cannot be consistent server - id = 1 # Settings need to be synchronized database binlog - do - db = user_db # shielding system library synchronous binlog - ignore - db = mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schemaCopy the code

// Change the configuration file of the slave library

Log-bin =mysql-bin Server-id =2 # Set replicate_wild_do_table=user_db.% # Mask replicate_wild_ignore_table=mysql.% Replicate_wild_ignore_table =information_schema.% replicate_wild_ignore_table=performance_schema.% There is a file auto-.cnf in the data directory of the primary and secondary MySQL databases. The service needs to be restarted.Copy the code

// Execute the command in the primary library

GRANT replication SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED by 'db_sync'; FLUSH PRIVILEGES; SHOW MASTER STATUS;Copy the code

// Execute the command from the library

STOP SLAVE; Alter secondary library to master library CHANGE MASTER TO master_host='localhost', master_user='db_sync', master_password='db_sync', master_log_file='mysql-bin.000001', master_log_pos=592; START SLAVE; If Slave_IO_Running and Slave_SQL_Runing are both yes, the synchronization is successful. If they are not yes, check the error_Log and check the related exception SHOW SLAVE STATUSCopy the code
  1. Master/slave replication implements configuration files
/ / define the data source spring. Shardingsphere. The datasource. Names = m0, m1, m2, s0 / / data source s0 spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s0.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.s0.url=jdbc:mysql://localhost:3307/user_db?useUnicode=true Spring. Shardingsphere. The datasource. S0. Username = root spring. Shardingsphere. The datasource. S0. Password = root / / data source 0 spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m0.driver-class-name =com.mysql.jdbc.Driver spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?useUnicode=true Spring. Shardingsphere. The datasource. M0. Username = root spring. Shardingsphere. The datasource. M0. Password = root / / data source 1 spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver-class-name =com.mysql.jdbc.Driver Spring. Shardingsphere. The datasource. M1. Url = JDBC: mysql: / / 118.31.18.203:3306 / order_db_1? UseUnicode = true Spring. Shardingsphere. The datasource. M1. Username = root spring. Shardingsphere. The datasource. M1. Password = root / / data source 2 spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver-class-name =com.mysql.jdbc.Driver Spring. Shardingsphere. The datasource. M2. Url = JDBC: mysql: / / 118.31.18.203:3306 / order_db_2? UseUnicode = true Spring. Shardingsphere. The datasource. M2. The username = root spring. Shardingsphere. The datasource. M2. The password = root / / configuration data node spring.shardingsphere.sharding.tables.t_order.DataNodes=m$->{1.. 2}.t_order_${1.. 2} spring.shardingsphere.sharding.master-slave-rules.ds0.masterDataSourceName=m0 spring.shardingsphere.sharding.master-slave-rules.ds0.slaveDataSourceNames=s0 Spring. Shardingsphere. Sharding. Tables. T_user. ActualDataNodes = ds0. T_user / / configuration primary key generation strategy spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE spring.shardingsphere.sharding.tables.t_user.keyGenerator.column=user_id Spring. Shardingsphere. Sharding. Tables. T_user. KeyGenerator. Type = SNOWFLAKE / / configuration subdivision strategy spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=order_id spring.shardingsphere.sharding.tables.t_user.databaseStrategy.inline.algorithmExpression=t_order_$->{order_id%2+1} spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=user_id spring.shardingsphere.sharding.tables.t_user.databaseStrategy.inline.algorithmExpression=m$->{user_id%2+1} spring.shardingsphere.sharding.tables.t_user.tableStrategy.inline.shardingColumn=user_id spring.shardingsphere.sharding.tables.t_user.tableStrategy.inline.algorithmExpression=t_userCopy the code

4.6 Horizontal sub-database sub-table plus master/slave replication actual combat

  1. Prepare resources

In a database, there are two horizontal branches order_DB_1 and order_DB_2. And replicate the database.Copy the code
  1. Configuration file writing
# configuration data source spring. Shardingsphere. The datasource. Names = m1, m2, s1, s2 # true data source configuration spring.shardingsphere.datasource.m1.type=com.alibaba.pool.DruidDataSource Spring. Shardingsphere. The datasource. M1. Url = JDBC: mysql: / / 127.0.0.1:3306 / order_db_1? UseUnicode = true spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jbdc.Driver spring.shardingsphere.datasource.m1.username=root spring.shardingsphere.datasource.m1.password=root spring.shardingsphere.datasource.s1.type=com.alibaba.pool.DruidDataSource Spring. Shardingsphere. The datasource. S1. Url = JDBC: mysql: / / 127.0.0.1:3307 / order_db_1? UseUnicode = true spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.jbdc.Driver spring.shardingsphere.datasource.s1.username=root spring.shardingsphere.datasource.s1.password=root spring.shardingsphere.datasource.m2.type=com.alibaba.pool.DruidDataSource Spring. Shardingsphere. The datasource. M2. Url = JDBC: mysql: / / 127.0.0.1:3306 / order_db_2? UseUnicode = true spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.jbdc.Driver spring.shardingsphere.datasource.m2.username=root spring.shardingsphere.datasource.m2.password=root spring.shardingsphere.datasource.s2.type=com.alibaba.pool.DruidDataSource Spring. Shardingsphere. The datasource. S2. Url = JDBC: mysql: / / 127.0.0.1:3307 / order_db_2? UseUnicode = true spring.shardingsphere.datasource.s2.driver-class-name=com.mysql.jbdc.Driver Spring. Shardingsphere. The datasource. S2. The username = root spring. Shardingsphere. The datasource. S2. The password = root # master-slave binding spring.shardingsphere.sharding.master-slave-rules.ds1.masterDataSourceName=m1 spring.shardingsphere.sharding.master-slave-rules.ds1.slaveDataSourceName=s1 spring.shardingsphere.sharding.master-slave-rules.ds2.masterDataSourceName=m2 Spring. Shardingsphere. Sharding. Master - slave - rules. Ds2. SlaveDataSourceName = s2 # set data node (several database tables, Configuration of several data nodes) spring. Shardingsphere. Sharding. Name t_order. ActualDataNodes = ds $- > {1.. 2}.t_order_$->{1.. 2} # primary key generation spring. Shardingsphere. Sharding. Name t_order. KeyGenerator. Column = order_id Spring. Shardingsphere. Sharding. Tables. T_order. KeyGenerator. Type = SNOWFLAKE # set subdivision strategy / / table spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardingColumn=order_id spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=t_order_$->{order_id % 2 + 1} / / depots spring. Shardingsphere. Sharding. Name t_order. DatabaseStrategy. Inline. ShardingColumn = user_id spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algorithmExpression=ds$->{user_id % 2 + 1}Copy the code