First, why to separate the database and table

In the software era, traditional applications have such a characteristic: the amount of visits and data are relatively small, and a single database and a single table can fully support the entire business. With the development of the Internet and the rapid expansion of user scale, the requirements for the system are getting higher and higher. Therefore, the performance problems of the traditional MySQL single-library single-table architecture are exposed. There are several factors that affect database performance:

  • The amount of data

The performance of MySQL single database is better when the data volume is less than 50 million. When the data volume exceeds the threshold, the performance deteriorates as the data volume increases. When the amount of data in a single MySQL table ranges from 500w to 1000w, the performance is better. When the amount exceeds 1000w, the performance deteriorates.

  • disk

Because the disk space of a single service is limited, if all requests access the same node under concurrent pressure, it will have a significant impact on disk IO.

  • Database connection

Database connection is a very rare resource. If there is data related to users, goods and orders in a database, database connection may become a bottleneck when a large number of users operate at the same time.

In order to improve performance, so we must solve the above several problems, it is necessary to introduce sub-database sub-table, of course, in addition to sub-database sub-table, there are other solutions, that is, NoSQL and NewSQL, NoSQL is mainly MongoDB, NewSQL is represented by TiDB.

Two, the principle of partition sub-database sub-table

1, what is partition, table, library

(1) Partition

It is to divide the data of a table into N blocks. Logically, it is only a table, but the bottom layer is composed of N physical blocks. Partition implementation is relatively simple, and mysql, Oracle and other databases can easily support it.

(2) sub-table

It is to decompose a table into N entity tables with independent storage space according to certain rules. When the system reads and writes, it needs to obtain the corresponding word representation according to the defined rules, and then operate it.

(3) Separate libraries

Once the table is divided, there will be more and more tables in a library. Comparing the whole database to a library, a table is a book. When looking up something in a book, the efficiency of the search is reduced if the search is not divided into chapters. Similarly, in a database it is partitioning.

2. When to use partitions?

The query speed of a table is too slow to be used.

  • SQL optimized
  • Large amount of data
  • The data in the table is segmented
  • Operations on data often involve only some data, not all of it

The most common partition method is to partition by time. One of the biggest advantages of partitioning is that it can be very efficient to clean up historical data.

(1) The implementation of partition

Mysql5 has support for partitions since 5.1.

(2) Partition type

MySQL currently supports RANGE, LIST, HASH, and KEY partitions.

(3) RANGE partition instance

Allocates multiple rows to a partition based on column values belonging to a given contiguous interval. The most common is based on the time field. Partition-based columns should preferably be of integer type, if date type can be converted to integer using a function. In this example, the to_days function is used.

CREATE TABLE my_range_datetime(
    id INT,
    hiredate DATETIME
) 
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20171202') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20171203') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20171204') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20171205') ),
    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20171206') ),
    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20171207') ),
    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20171208') ),
    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20171209') ),
    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20171210') ),
    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171211'), PARTITION p11 VALUES LESS THAN (MAXVALUE));Copy the code

3. When are the watches divided?

The query speed of a table is too slow to be used.

  • SQL optimized
  • Large amount of data
  • It slows down when frequently inserted or syndicated queries

After a table is divided, the concurrent capability of a single table is improved, disk I/O performance is improved, and write operation efficiency is improved

(1) The implementation of sub-table

It needs to combine with relevant middleware and business system to cooperate with migration and upgrading, which requires a large workload.

Three, the introduction of problems after the database and table

1. Distributed transaction problem

If we do a vertical branch or horizontal branch, it will inevitably involve the problem of cross-library SQL execution, which causes the Internet community’s old difficult problem -” distributed transaction “. So how do you solve this problem? 1. Use distributed transaction middleware 2. Use MySQL’s own cross-library transaction Consistency scheme (XA), but performance is about 10 times slower than single-library. 3. Can you avoid cross-library operations (such as putting users and goods in the same library)

2. Cross-library join problems

After the database is divided into tables, the associated operations between tables will be limited. We cannot join tables in different sub-databases or with different granularity of sub-tables. As a result, the services that can be completed by one query may need to be completed by multiple queries. Rough workaround: Global tables: base data, copies for all libraries. Field redundancy: So that some fields do not need to be queried by join. System layer assembly: query all separately, and then assemble, more complex.

3. Problems of horizontal expansion

When we use HASH module to divide tables, we may need to add tables dynamically for increasing data volume. In this case, we need to consider the problem of data migration caused by reHash.

4. The problem of combination and sorting of result sets

Because we store the data in different libraries and tables, when we query the specified data list, the data comes from different sub-libraries or sub-tables, which will inevitably lead to the problem of result set combination and sorting. If every query requires sorting, merging, and so on, performance will suffer significantly. Go cache may be a way!

Fourth, the design of sub-database and sub-table middleware

Sub-tables are divided into single database sub-tables (table names are different) and multi-database sub-tables (table names are the same). No matter which strategy is used, you still need to implement routing and formulate routing rules by yourself. Open source sub-database sub-table middleware and non-invasive application design can be considered, such as TDDL of Taobao.

The sub-database and sub-table middleware can be divided into two types:

  • CLIENT mode;
  • The PROXY pattern;

CLIENT mode represents TDDL of Ali and Sharding-JDBC of open source community (The 3.x version of Sharding-JDBC, namely sharding-Sphere, has supported proxy mode).

The structure is as follows:

PROXY mode represents Cobar of Ali and MyCAT of non-governmental organizations. The structure is as follows:

Either CLIENT mode or PROXY mode. The core steps are the same: SQL parsing, rewriting, routing, execution, and result merging.

Five, sub-library sub-table commonly used middleware

At present, there are basically the following kinds of applications.

  • TDDL
  • Sharding-jdbc
  • Mycat
  • Cobar

1, TDDL

Taobao team development, belongs to the client layer scheme. Supports basic CRUD syntax and read-write separation, but does not support join, multi-table query, and other syntax.

2, Sharding – JDBC

Dangdang open source, belongs to the client layer scheme, has been renamed ShardingSphere. SQL syntax support is also more, without too many restrictions, support sub-database sub-table, read and write separation, distributed ID generation, flexible transactions (maximum effort service type transactions, TCC transactions).

3, Cobar

Ali B2B team development and open source, belongs to the proxy layer scheme, is between the application server and database server. The application accesses the Cobar cluster through the JDBC driver. Cobar decompresses the SQL according to the SQL and repository rules, and then distributes the SQL to different database instances in the MySQL cluster for execution.

4, Mycat

Based on Cobar transformation, belongs to the proxy layer scheme, supports perfect functions, active community.

Common sub-table and sub-library common strategies

  1. Distribute hash(Object)%N evenly (for simple architectures).
  2. Assign according to weight and poll evenly.
  3. Allocation by service.
  4. Allocation based on the consistent hash algorithm (applicable to cluster architecture, adding or deleting nodes in a cluster does not cause data loss, facilitating data migration).

7. Global ID generation strategy

1. Automatically grow columns

Advantages: The database comes with functions, ordered, good performance. Disadvantages: single library single table no harm, sub – library sub – table if there is no planning, ID may be repeated.

One solution is to set the increment offset and step size.

  • Let’s say there are 10 sub-tables
  • Level Optional: SESSION(SESSION level), GLOBAL(GLOBAL level)
  • SET @@SESSION.auto_increment_offset = 1; ## The start value ranges from 1 to 10
  • SET @@SESSION.auto_increment_increment = 10; ## Step increment

If this solution is adopted, existing data needs to be migrated to the new owning fragment during capacity expansion.

The other is the global ID mapping table.

  • Create an ID key for each table in global Redis to record the current maximum ID of the table;
  • Each time you apply for an ID, it increases by 1 and is returned to the application.
  • Redis is persisted to the global database periodically.

2. UUID(128-bit)

A number generated on a machine that is guaranteed to be unique to all machines in the same space and time. Typically, platforms provide an API for generating UUID.

UUID A string of 36 bytes in length separated by four hyphens (-). For example, 550E8400-E29B-41D4-A716-446655440000.

UUID factors include: Ethernet card address, nanosecond time, chip ID code, and many possible numbers. Uids are a standard. There are several of them, the most common being Microsoft’s Globals Unique Identifiers.

  • Advantages: simple, unique in the world;
  • Disadvantages: Large storage and transmission space, disorderly, poor performance.

3. Emma Watson

Combine GUID(10 bytes) and time (6 bytes) to achieve ordered effect and improve index performance.

The Snowflake algorithm

Snowflake is an open source distributed ID generation algorithm for Twitter that results in long(64bit) values. It is characterized by no coordination of nodes, roughly ordered by time, and no duplication of nodes in the whole cluster. The default composition of this value is as follows (the three parts outside the sign bit allow personalization) :

  • 1bit: The sign bit, always 0(to ensure a positive value).
  • 41bit: milliseconds (69 years available);
  • 10bit: node ID(5-bit DATA center + 5-bit node ID, 32 x 32 = 1024 nodes)
  • 12bit: serial number (Each node supports 4096 ids per millisecond, equivalent to 4.09 million QPS. If the ID is reversed within the same time, it will wait until the next millisecond)

Eight, elegant implementation of dynamic expansion of sub-database sub-table

Elegant design Capacity expansion and reduction means that the cost of capacity expansion and reduction is low and data migration is fast.

Logical database and table partitioning can be used instead of physical database and table partitioning. To expand or reduce capacity, you only need to change logical databases and tables to physical databases and tables.

A practice is to use 32 * 32 to divide the database into tables, that is, 32 libraries, 32 tables in each library, a total of 1024 tables, according to a certain ID first according to the number of database 32 mode route to the library, and then according to the number of tables in a library 32 mode route to the table.

In the beginning, this library might be a logical library, built on top of a mysql service, such as a mysql server with 16 databases.

If you want to split it later, it’s just a matter of constantly migrating between libraries and mysql instances. Move mysql server libraries to another server, say 8 libraries per server, so instead of 2 mysql servers there will be 4 mysql servers. Our system only needs to configure the two new servers.

For example, you can scale up to 32 database servers, and each database server is a library. If not enough? You can scale up to 1024 database servers, with one library and one table per database server. Because 1024 tables at most.

In this way, there is no need to write your own code to do data migration, but the DBA does need to do some database table migration work, but it is much more efficient than you write your own code, extract data derivative data.

Even if you want to reduce the number of libraries, it is very simple, in fact, is a multiple of the capacity can be reduced, and then modify the routing rules.

Reference documentation

Shardingsphere.apache.org depth understanding Sharding – JDBC

Follow the public account: Architecture evolution, get first hand technical information and original articles