The previous chapter has described the selection of sub-database and sub-table algorithm, this chapter mainly describes the selection of sub-database and sub-table technology

This paper is related to the previous chapter. If it is mentioned below, you can click sub-database sub-table algorithm scheme and Technology Selection (I).

Focuses on

  • Framework to compare
  • Sharding – JDBC, ZDAL code implementation example, if you need source code can be viewed in the following article
  • Primary key generation strategies can be read on demand

The author

Hid in Kelvin

Public account: Dizang Thinking

A common framework

In addition to native JDBC, common online subdatabase and subtable frameworks include: Dangdang Sharding – JDBC Alibaba. Cobar (is the Development of Alibaba (B2B) department) MyCAT (based on ali open source Cobar products developed) Ant Financial ZDAL (open source) Mogujie TSharding

Of course, there are many other frameworks proposed by their own companies, but these are the ones with the highest number of users. Among them, since the emergence of Cobar-based MyCAT, Zdal, few people use Cobar. ZDAL, while also open source, has very few articles and usage feedback, no support for MongoDb, and low communication activity.

So this article will compare sharding-JDBC and MyCAT, which are more active.

In addition to Sharding-JDBC, There is elastic- Job for timed task sharding

Comparative overview

Main indicators Sharding-jdbc Mycat zdal
The ORM support any any any
The transaction With weak XA, maximum effort delivery type flexible transaction BASE Bring their own weak XA With weak XA, maximum effort delivery type flexible transaction BASE
depots support support support
depots support Single library sub-tables are not supported support
The development of The development cost is high, the code invades big Low development cost, low code intrusion The development cost is not high and the configuration is clear
The company of affiliation dangdang Cobar secondary development based on Ali, community maintenance The ant gold dress
Database support any Oracle, SQL Server, Mysql, DB2, mongodb Do not support the mongo
active There are also many enterprises in recent years the use of new projects Community activity is high and some companies are already using it Active low
monitoring There is no There are
Reading and writing separation support support
data Less information, Github, official website, online discussion post More information, github, official website, Q group, books less
operations Low maintenance cost High maintenance costs Low maintenance cost
limit Some JDBC methods are not supported and SQL statements are restricted SQL statement Restrictions
The connection pool Druid version No request No request
Configure the difficulty general complex Relatively simple, read and write separation, separate table rules set less

Comparison of key indicators

1. Development, operation and maintenance costs

sharding-jdbc

  • Sharding-jdbc is a lightweight framework that does not run middleware in isolation. It provides functionality in the form of an engineering dependent JAR, which requires no additional deployment and can be understood as an enhanced JDBC driver.
  • For o&M and DBA personnel, there is no need to perceive code and sharding policy rules. O&m only needs to maintain and perform the migration of established tables and data.
  • This is a sharding-JDBC advantage over Mycat, reducing deployment costs and DBA learning costs.
  • For example, select * from A is changed to SELECT * from A_01 according to the rule. When the SQL is executed, the mysql server will send the select * from A_01 command.

MyCat

  1. MyCat is not a configuration in the business system code, but a middleware that runs independently, so the configuration is handed over to the DBA.
  2. For THE DBA, it is a layer of agent in front of mysql Server, mycat itself does not store data, data is stored on the back-end mysql, so data reliability and transactions are guaranteed by mysql.
  3. To reduce the risk of migrating data, the incremental migration algorithm proposed in the previous chapter (recommended reading) describes how sharding can reduce the risk. With MyCat, the DBA needs to configure incremental sharding rules several times, and each time you configure it, you need to restart it for a round of data migration. In fact, when MyCat is down, the system cannot query the database, and it still affects all users.
  4. However, sharding-JDBC implements routing rules in code, which can reduce the number of DBA operations and system restarts, thus reducing the impact on users.

It is recommended to read Section 5 of Chapter 1 to better understand the above 3-4 points sub-database sub-table algorithm scheme and technology selection (I)

  1. Proxy integrates big data thinking and separates OLTP and OLAP processing, which may be suitable for big data processing systems. After all, data work may not have a Java back-end system.

Summary: Sharding-JDBC incremental sharding and incremental migration of data are better, and MyCAT is more suitable for big data work

Note: Sharding-JDBC enhances some functions of the JDBC driver, but also restricts the use of some native JDBC interfaces. Specific limit reference: limit situation: dangdangdotcom. Making. IO/sharding – jd… This document now seems to be inaccessible

Example of MyCat Configuration Example 2

2. Ability to separate databases and tables

  • Another advantage of Sharding-JDBC is its ability to assign tables without having to assign tables.
  • MyCAT cannot be divided into multiple tables in a single database, but must be divided into multiple databases, which will cause the DBA to increase the number of machine nodes. Even if the number of machine nodes is not increased, the mysql Server instance will be added to the same machine. If the sharding-JDBC single database is divided into multiple tables, the DBA only needs to execute the establishment of table sentences.

3. The transaction

First, let’s talk about XA. Although XA multi-stage submission has a good guarantee for the integrity of distributed data, it will greatly affect application performance.

  • Sharding-jdbc and MyCat support weak XA. Weak XA means that the database after splitting is responsible for its own transaction commit and rollback, and there is no unified scheduler for centralized processing. The advantage of this is that it is naturally supported and has no impact on performance. However, if there is a problem, for example, the data of two libraries needs to be submitted, one is submitted successfully, and the other fails due to disconnection during the submission, the data inconsistency will occur, and this data inconsistency will be permanent.

  • Flexible transactions are a useful complement to weak XA. There are many types of flexible transactions. Sharding-JDBC mainly implements maximum effort delivery. The belief that a transaction will succeed through trial and error. If each transaction fails to execute, it will be recorded to the transaction library, and continue to try through asynchronous means until the transaction succeeds (you can set the number of attempts, if too many attempts still fail, it will be entered into the library and need manual intervention). On the way, the data will be inconsistent for a while, but eventually it will be consistent. In this way, the consistency of data can be achieved at the expense of strong consistency without compromising performance. The disadvantage of best-effort delivery transactions is that they are not flexible because they assume that the transaction must be successful and cannot be rolled back.

Note: Another flexible transaction type is TCC, or Try Confirm Cancel. You can control the commit or rollback of a transaction through a transaction manager, closer to the original transaction, but still final consistency. The disadvantage is that the business code needs to implement the interface of Try Confirm Cancel by itself, which has a certain impact on existing services. Sharding-jdbc does not support TCC.

4. Monitor

Why monitor, because the above transaction is weak XA, maximum effort delivery type, in fact, there is still a probability of failure.

  • MyCat will monitor the page and monitor the heartbeat between MyCat and Mysql Server, which can be seen by operations personnel
  • Sharding-jdbc does not monitor whether the transaction is finally executed, and may need to rewrite the source code. If a sharding fails to execute, send a message, pin, etc. Example of MyCat monitoring configuration

5. Statement restrictions

  • There are limitations to using like queries for sharding-JDBC branch tables. Currently, Shariding-JDBC does not support shard keys in LIKE statements, but like statements that do not contain shard keys can be executed correctly. As for like performance issues, which are database related, Shariding-JDBC is just about parsing SQL and routing to the correct data source. If the SQL does not include shard keys, all libraries and tables will be queried, regardless of whether there is a like.
  • MyCat has no limits

6. Compare Ant Financial’s Zdal

Compared with ZDAL, the configuration of Sharding-JDBC is similar, but Sharding-JDBC provides Java, SpringBoot, YML, Spring namespace mode, and has official website and Gitee website maintenance. More active users than Zdal.

Sharding- JDBC sub-library sub-table integration mybatis-plus development sample

The code sample details the following key development points. The source code can be found at my Gitee address sharding-jdbc-example.

Sharding – JDBC sharding development key points: 0. Introduction of key dependency 2019.10 latest version 4.0.0-RC2

         <dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-namespace</artifactId>
			<version>4.0.0 - RC2</version>
		</dependency>
Copy the code
  1. Configure the basic data source, sub-database sub-table strategy in XML, DbShardingAlgorithm, TbShardingAlgorithm needs to be implemented in Java code.
< - depots rule object classes - > < bean id = "preciseModuloDatabaseShardingAlgorithm" Class = "com. Dizang. Sharding. Config. Algorithm. DbShardingAlgorithm" / > < - table object class rules - > < bean id="preciseModuloTableShardingAlgorithm" class="com.dizang.sharding.config.algorithm.TbShardingAlgorithm" /> <sharding:standard-strategy id="databaseStrategy" sharing-column ="user_id" Precise algorithm - ref = "preciseModuloDatabaseShardingAlgorithm" / > < - depots based on key - > < sharding: standard - the strategy id="tableShardingStrategy" sharding-column="user_id" precise-algorithm-ref="preciseModuloTableShardingAlgorithm" /> <sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="id" /> <sharding:data-source Id ="shardingDataSource"> <sharding: data-source-names="ds0, Ds1 "> <sharding:table-rules> <-- logical table name -> <sharding:table-rule logic-table="t_user" actual-data-nodes="ds$->{0.. 1}.t_user_$->{0.. Table strategy-ref="tableShardingStrategy" table strategy-ref="tableShardingStrategy" Key-generator-ref ="orderKeyGenerator" /> </sharding:table-rules> < <sharding:binding-table-rules> <sharding:binding-table-rule logic-tables="t_user" /> </sharding:binding-table-rules> <sharding:broadcast-table-rules> <sharding:broadcast-table-rule table="t_order" /> </sharding:broadcast-table-rules> </sharding:sharding-rule> </sharding:data-source> <--sqlSessionFactory configure shardingDataSource datasource -> <bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean"> <property name="dataSource" ref="shardingDataSource"/> <property name="mapperLocations" value="classpath*:mapper/*.xml"/> </bean>Copy the code
  1. Java code depots strategy We need to inherit SingleKeyDatabaseShardingAlgorithm separate rule class, rewrite the routing rules in effect at the time of equal equal to, greater than, less than
public class DbShardingAlgorithm implements PreciseShardingAlgorithm<Long>{  

    @Override
    public String doSharding(Collection<String> databaseNames, PreciseShardingValue<Long> shardingValue) {
        for (String each : databaseNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                returneach; }}return null; }}Copy the code
  1. Java code table strategy We need to inherit SingleKeyTableShardingAlgorithm separate rule class, rewrite the routing rules in effect at the time of equal equal to, greater than, less than
public class TbShardingAlgorithm implements PreciseShardingAlgorithm<Long>{  

    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
        for (String each : tableNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                returneach; }}// return shardingValue.getLogicTableName()+(shardingValue.getValue() % 2);
        throw newUnsupportedOperationException(); }}Copy the code

Zdal specific code implementation recommended reading

Zdal sub – library sub – table introduction, super detailed step – by – step build a simple Zdal framework

Welcome to attention

My public account: Tibet thinking

My Gitee: Underground Collection Kelvin gitee.com/dizang-kelv…

Sharding-jdbc:

Sharding-JDBC source code parsing collection

Sharding-JDBC source code analysis – SQL rewrite

Native JDBC read and write separation