Version number: ShardingSphere-5.0.0-beta

Preparation (source code download)

#Conclusion the gitcloneFile name too long
git config --global core.longpaths true

#cloning
git clone [email protected]:apache/shardingsphere.git

#Tag code based on the modificationGit checkout -b beta_test 5.0.0-betaCopy the code

concept

Positioned as a lightweight Java framework that provides additional services in Java’s JDBC layer. It uses the client directly connected to the database, in the form of JAR package to provide services, without additional deployment and dependence, can be understood as an enhanced VERSION of THE JDBC driver, fully compatible with JDBC and various ORM frameworks.

  • Works with any JDBC-based ORM framework, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.
  • Support any third party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
  • Support for any database that implements the JDBC specification. Currently supports MySQL, Oracle, SQLServer, PostgreSQL and any database that complies with the SQL92 standard.

Depots table

background

Data sharding refers to dividing data stored in a single database into multiple databases or tables according to a certain dimension to improve performance bottlenecks and availability. The effective method of data sharding is to divide database and table into relational database.

differentiate

  1. Both sub-database and sub-table can effectively avoid the query bottleneck caused by the amount of data exceeding the tolerable threshold
  2. Branch libraries can be used to effectively disperse the access to a single point of the database
  3. Distributed transactions involving cross-library update operations are often complicated
  4. Split tables do not relieve database stress, but they do provide the possibility of converting distributed transactions to local transactions as much as possible

Break up way

Vertical fragmentation

According to the way of business separation, vertical sharding, also known as vertical split, its core idea is dedicated library. Before the split, a database consisted of multiple tables, each corresponding to a different business. After the split, the tables are grouped according to the business and distributed to different databases, thus spreading the pressure to different databases.

According to business needs, the user table and order table are vertically shard to different databases

Derivative problem

  • Vertical sharding often requires architectural and design adjustments. Generally speaking, it is too late to cope with the rapid change of Internet business requirements;
  • There is no real solution to a single point of bottleneck. Vertical split can alleviate the problems caused by data volume and visits, but it cannot cure them

Level of fragmentation

Horizontal sharding is also called horizontal splitting. As opposed to vertical sharding, it no longer categorizes data according to business logic. Instead, it divides data into multiple libraries or tables according to certain rules through a certain field (or fields), with each shard containing only a portion of the data.

The primary key shard

According to primary key sharding, records with even primary keys are put into library 0 (or table), and records with odd primary keys are put into library 1 (or table).

advantages

Theoretically, it breaks through the bottleneck of single machine data processing, and expands relatively freely. It is a standard solution of database and table

Reading and writing separation

define

The database is split into a master and a slave, with the master handling transactional add, delete and change operations and the slave handling query operations. Read/write separation According to SQL semantic analysis, read and write operations are routed to master and slave libraries respectively.

User table read/write separation

Horizontal contrast sharding

  • The data content of the data node is consistent
  • The data content of each data node in horizontal sharding is different
  • Horizontal sharding and read/write separation are used together to improve system performance

challenge

  1. Inconsistent data
    • Data consistency across multiple master libraries
    • Data consistency issues between master and slave libraries
  2. The operation, operation and maintenance of databases become more complex. When the database and table are used together with read and write separation, the complex topology between applications and database clusters is as follows:

Data encryption

define

Data encryption refers to the deformation of some sensitive information through encryption rules to achieve reliable protection of sensitive privacy data. Data involving customer security or some sensitive commercial data, such as id number, mobile phone number, card number, customer number and other personal information, shall be encrypted according to the regulations of relevant departments.

The business scenario

  1. When new services are launched, the security department stipulates that sensitive user information, such as bank and mobile phone numbers, should be encrypted and stored in the database, and then decrypted during use. Because it is a new system, there is no stock data cleaning problem, so the implementation is relatively simple.
  2. The plaintext has been stored in the database for online services. All of a sudden, relevant departments need to encrypt and rectify the online business. This scenario typically deals with three issues:
    • How to encrypt historical data, that is, wash data.
    • How to encrypt the new data and store it in the database without changing the business SQL and logic; In use, and then decrypted out.
    • How to realize the migration between plaintext and ciphertext data in a safer, seamless and transparent way.

challenge

  • If the encryption scenario changes, the self-maintained encryption system needs to be reconstructed or modified
  • For services that have been online, it is difficult to achieve seamless encryption transformation in a transparent, secure and low-risk manner without modifying service logic and SQL

The sample run

Separate library and separate table & separate read and write

spring.shardingsphere.datasource.names=ds

spring.shardingsphere.datasource.ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url=jdbc:mysql://localhost:3306/demo_ds? serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=

spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds.t_order_$->{0.. 1}
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=t-order-inline

spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=snowflake

spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=ds.t_order_item_$->{0.. 1}
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=t-order-item-in line

spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.column=order_item_id
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-name=snowflake

spring.shardingsphere.rules.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.rules.sharding.broadcast-tables=t_address

spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.props.algorithm-expression=t_order_$->{order_id % 2}
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-item-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-item-inline.props.algorithm-expression=t_order_item_$-> {order_id % 2}

spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=123
Copy the code

Data encryption

spring.shardingsphere.datasource.names=ds

spring.shardingsphere.datasource.ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url=jdbc:mysql://localhost:3306/demo_ds? serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=

spring.shardingsphere.rules.encrypt.encryptors.name-encryptor.type=AES
spring.shardingsphere.rules.encrypt.encryptors.name-encryptor.props.aes-key-value=123456abc
spring.shardingsphere.rules.encrypt.encryptors.pwd-encryptor.type=AES
spring.shardingsphere.rules.encrypt.encryptors.pwd-encryptor.props.aes-key-value=123456abc

spring.shardingsphere.rules.encrypt.tables.t_user.columns.user_name.cipher-column=user_name
spring.shardingsphere.rules.encrypt.tables.t_user.columns.user_name.encryptor-name=name-encryptor
spring.shardingsphere.rules.encrypt.tables.t_user.columns.pwd.cipher-column=pwd
spring.shardingsphere.rules.encrypt.tables.t_user.columns.pwd.encryptor-name=pwd-encryptor

spring.shardingsphere.props.query-with-cipher-comlum=true
spring.shardingsphere.props.sql-show=true
Copy the code

TODO

  1. Set up 4 databases on 2 servers, 2 primary and 2 secondary
  2. Docker to establish
  3. Build the database to build tables, their own demo