With the rapid development of the Internet, the problem of massive data storage has been brought. For example, in the Internet of Things industry, every intelligent terminal collects and reports data every day and can produce tens or even hundreds of millions of data every day. Electricity industry on the Internet, or some O2O platform, also can produce every day tens of millions of orders, the scale of the data in the traditional relational database has been unable to support, so how to solve the problem of mass data storage and computing, distributed storage and distributed computing was introduced to the industry solutions, especially no ecological, The K-V database, document database, graph database and so on that I mentioned before are the mainstream distributed database solutions.

Even so, the relational database still has its irreplaceable characteristics, so the relational database is still the basic data platform of the core business, so the relational database will inevitably face the massive data processing problems brought by the increasing amount of data.

Performance problems caused by massive data in the Mysql database

At present, almost all Internet companies use mysql, the open source database. According to The Java Development Manual of Alibaba, when the number of rows in a single table exceeds 500W or the data capacity in a single table exceeds 2G, the query performance will be greatly affected. At this time, it is recommended to optimize the table.

If innodb_buffer_pool_size is sufficient, mysql can load all data into memory. If innodb_buffer_pool_size is sufficient, mysql can load all data into memory. Queries will not be a problem.

However, when a single-table database reaches a certain limit, its index cannot be stored in memory, resulting in disk I/O for subsequent SQL queries, resulting in performance degradation. Of course, this also has to do with the specific table structure design, which ultimately leads to memory limitations. Here, increasing hardware configuration may bring immediate performance improvements.

Innodb_buffer_pool_size contains data cache, index cache, and so on.

Mysql common optimization methods

Of course, the first optimization is based on Mysql itself. Common optimization methods include:

  • Add index, index is intuitive and the fastest way to optimize search efficiency.
  • Optimizations based on Sql statements, such as leftmost matching, indexing field queries, reducing the complexity of Sql statements, etc
  • Reasonable design of tables, such as conforming to the three normal form, or breaking the three normal form design for certain efficiency, etc
  • Optimization of database parameters, such as the number of concurrent connections, data flush policy, cache size adjustment
  • Database server hardware upgrade
  • Mysql all master and slave replication scheme, to achieve read and write separation

These common optimization methods are very good in the case of small amount of data, but when the amount of data reaches a certain bottleneck, conventional optimization methods can not solve the actual problem, so what to do?

Big data table optimization scheme

The most intuitive way to optimize big data tables is to reduce the amount of single table data, so common solutions are as follows:

  • Sub – table, large table disassembled small table.

  • The separation of hot and cold data, the so-called hot and cold data, is actually divided according to the access frequency. The data that is accessed more frequently is hot data, and the data that is accessed less frequently is cold data. The separation of hot and cold data is to separate these two types of data into different tables to reduce the size of the hot data table.

    In fact, in many places we can see similar implementation, such as to go to some websites to query orders or transaction records, the default is only allowed to query 1 to 3 months, 3 months before the data, basically we are rarely concerned about, less frequency of access, so you can save 3 months before the data into the cold storage.

  • Historical data archiving, in a nutshell, is to separate and archive the old data to ensure the effective life cycle of real-time database data.

In fact, these solutions are business-oriented solutions, not entirely technical solutions. Therefore, appropriate solutions need to be selected based on service characteristics.

Detail the sub-database sub-table

Depots table is a very common way for the optimization of the large amount of a single data table, its core idea is to put a large data table into multiple small tables, this process is also called the shard (data), the essence of it is a bit similar to traditional partition table in the database, such as mysql and oracle support mechanism for the partition table.

A shard contains a subset of the original total data set. This idea of divide and conquer is common in technologies such as multi-CPU, distributed architecture, distributed cache, etc., as in the case of Redis cluster, slot allocation is a kind of data sharding idea.

As shown in Figure 6-1, there are two ways to implement database subtables:

  • Horizontal split, based on table or field partition, table structure is different, there are single-library sub-table, there are multi-library sub-library.
  • Vertical split, based on data partition, the same table structure, different data, but also the same library of horizontal segmentation and multi-library segmentation.

The vertical resolution

There are two kinds of vertical split, one is the vertical split of a single library, the other is the vertical split of multiple databases.

Single library vertical sub-table

You are advised to limit the number of fields in a single table to 20 to 50. This limit is recommended because if the total length of fields and data exceeds a threshold, data will not be stored on one page and paging will occur, which degrades query performance.

Therefore, if there are too many fields in some business tables, we usually split the fields of one table into multiple tables by vertical splitting. As shown in Figure 6-2, an order table is vertically divided into one master order table and one order detail table.

In the Innodb engine, the maximum limit for single table fields is 1017

Reference: dev.mysql.com/doc/mysql-r…

Multi-library vertical sub-table

Multi-database vertical splitting is actually to split multiple tables in a database into multiple libraries according to certain latitude, as shown in Figure 6-3. This kind of split is also common in microservice architectures, which basically split the database according to the business latitude. This latitude also affects the split of microservices, which are basically separate from the database.

The biggest benefit of multi-library vertical split is the isolation of business data. The second is to ease the pressure of requests, originally all tables in a library, all requests will be hit to a database server, through the database split, can share requests, in this level to improve the throughput of the database.

Horizontal split

Vertical splitting does not solve the problem of large amount of data in a single table, so we also need to divide large table data into data fragments by horizontal splitting.

Horizontal segmentation can also be divided into two types, one is single library, the other is multi-library.

Single library level sub-table

As shown in Figure 6-4, a user table with 10000 data entries is split into four tables according to a certain rule. Each table has 2500 data entries.

Two cases:

In the bank’s transaction statement, all incoming and outgoing transactions need to be registered in this form. Because most of the time, customers only inquire the transaction data of the day and the transaction data within a month, we divide this form into three tables according to the frequency of use:

Current day table: Stores only current day data.

Monthly table: We run a scheduled task at night, and the previous day’s data is all migrated to the monthly table. Insert into SELECT, then delete.

History table: The data registered for more than 30 days is also migrated to the history table through scheduled tasks (the data in the history table is very large, so we create partitions on a monthly basis).

Expense table: The consumer finance company cooperates with offline merchants. After the loan is granted to the customer, the consumer finance company has to refund the fees to the merchant, or call the commission, which will generate a lot of data of expenses every day. To facilitate management, we set up a monthly expense table, such as FEe_detail_201901…… Fee_detail_201912.

Note, however, that, like partitioning, while this approach can solve the performance problems of single-table queries to a certain extent, it does not solve the problems of single-table storage bottlenecks.

Multi-database level table

In fact, multi-database horizontal sub-table is similar to the comprehensive implementation scheme of sub-database and sub-table. Sub-table reduces the amount of data in a single table, and reduces the performance bottleneck of accessing a single database at the level of sub-database, as shown in Figure 6-5.

Common horizontal table strategy

Branch library is more concerned with the degree of business coupling, that is, each library should put the table, is determined by the degree of business coupling, this in the early stage of domain modeling will be taken into account, so the problem is not big, only after the branch library brought other problems, we will analyze in the follow-up content.

In terms of dividing tables, there are more questions to consider, that is, what kind of strategy should we use to divide tables horizontally? Here is the need to involve the table strategy, the following is a brief introduction to several of the most common sharding strategy.

Hash module sharding

Hash sharding is to obtain a hash value through the hash algorithm of a field in the table, and then determine the data to be placed in the sharding by modular calculation, as shown in Figure 6-6. This approach is ideal for random read and write scenarios, where data from a large table can be randomly distributed across multiple small tables.

Hash mod problem

Hash modulus calculation have a serious problem, assuming that according to the quantity of current data table and growth condition, we put a big table is split into four small table, seems to meet the current needs, but after a period of time after the operation, four tables found enough to need to add 4 table to store, in this case, you need to the original data for overall migration, This process is very cumbersome.

In order to reduce the impact of data migration in this way, we usually use a consistent hash algorithm.

Consistent hash algorithm

The hash mod algorithm mentioned above is actually used to hash the target table or target database. Once the number of the target table or database changes, all data will need to be migrated. In order to reduce the impact of such large-scale data, the consistent hash algorithm is introduced.

In simple terms, consistent hashing organizes the entire hash space into a virtual circle, as shown in Figure 6-7. Suppose that the value space of a hash function H is 0-2^32^-1 (that is, the hash value is a 32-bit unsigned integer), what does it mean?

That is, we form a virtual circle with the numbers 0-2^32^-1. The point directly above the circle represents 0, and the first point to the right of 0 represents 1, and so on, 2, 3, 4, 5, 6… All the way to 2 to the 32 to the minus 1, which means the first point to the left of 0 is 2 to the 32 to the minus 1. We call this circle of two to the thirty-second points a hash ring.

So what does the consistent hash algorithm have to do with the virtual ring above? Going back to the previous example of modulo hash, let’s say we have four tables, table_1, table_2, table_3, table_4. In a consistent hash algorithm, modulo hash is not performed directly on these four tables, but on 2^32^.

Hash (table number) % 2 ^ ^ 32

The result must be an integer between 0 and 2^32^-1. The target table is marked at the corresponding position of the number, as shown in Figure 6-8. The four tables fall into a certain position of the hash ring after modulo extraction.

Ok, so far we’ve associated the target table with the hash ring, so we need to save a piece of data to one of the target tables. How do we do that? As shown in Figure 6-9, when adding a piece of data, you can also obtain a target value by modulo operation of the Hash and hash ring. Then, you can search for the nearest target table clockwise based on the position of the hash ring where the target value resides and store the data in the target table.

The advantage of consistent hash is that the hash operation is not directly directed to the target table, but to the hash ring. The advantage of this is that when a table needs to be deleted or added, the change of data is local, not global. For example, suppose we find that we need to add a table table_04, as shown in Figure 6-10. Adding a table does not affect the other four tables that have already generated data, and the previously fragmented data does not need to be changed at all.

If a node needs to be deleted, only the data of the node itself will be affected, and the data of the front and back tables will not be affected at all.

Hash ring deflection

There is a problem with the above design. Theoretically, our goal is to evenly distribute the table in the whole hash ring, but the actual situation may be as shown in Figure 6-11. The problem with skewing the hash ring is that a large amount of data is stored in the same table, but the data distribution is extremely uneven.

To solve this problem, it is necessary to ensure that the target nodes are evenly distributed throughout the hash ring, but there are only four real nodes. How can they be evenly distributed? The simplest method is to copy each of the four nodes and scatter them into the hash ring. The cloned node is called a virtual node. Multiple virtual nodes can be created based on actual requirements, as shown in Figure 6-12.

Sharding by scope

Sharding by scope, which is based on the business characteristics of the data table, is split according to a certain scope. This scope has many meanings, such as:

  • Time range, such as when we created the data and saved a table for each month. Time-based partitioning can also be used to separate hot and cold data. The earlier the data is accessed, the less frequent the data is.
  • Region generally refers to geographical location. For example, a table stores data from all parts of the country. If the data volume is large, multiple tables can be divided according to region.
  • Data range, for example, by the data range of a field.

Figure 6-7 shows how to split data by data range.

Range shard will ultimately is to choose an appropriate shard keys, the appropriate from business requirements, such as smart home before a student is doing, they are selling hardware devices, these devices will collect report data to the server, when the data from the nationwide unified stored in a table, the data volume has reached the level, So this kind of scene is better split by city and region.

Sub-database sub-table actual combat

In order to let you understand the database table and the actual operation, we through a simple case to demonstrate. See the springboot-split-table-example project for the code

Suppose there is a user table with the following fields.

This table provides functions such as registration, login, query, and modification.

The specific business situation of the table is as follows (it should be noted that before dividing the table, it is necessary to understand the use of the table at the business level, and then decide what kind of scheme to use, otherwise it is a hooligan to design a technical scheme without business)

Client: The foreground traffic is large, mainly involving two types of requests:

  • The user login is c-oriented and has high requirements on availability and consistency. The user information is queried using login_name, email, and phone. 1% of the requests belong to this type
  • User information query: After a successful login, users can query user information by uid. 99% of users can query user information by UID.

Operation end: it is mainly used for information access in the operation background, which needs to support paging query based on gender, mobile phone number, registration time, user nickname, etc. Since it is an internal system, the traffic volume is low and the availability consistency is not high.

Level table according to UID

Since 99% of requests are based on Uids for user information queries, it’s no surprise that we chose to use Uids for horizontal tabulation. Here, we use the hash modulo method of UID to divide tables. The specific implementation is shown in Figure 6-9. Consistent hash modulo operation is performed according to UID to obtain the target table for storage.

Duplicate the user_info tables according to Figure 6-9 and rename them 01 to 04, as shown in Figure 6-10.

How do I generate globally unique ids

When the above actions are completed, it is necessary to start the implementation of the ground. Here, it is necessary to consider the correct route to the target data table when adding, modifying and deleting data, followed by the migration of old data.

Old data migration, generally we write a script or a program, the old table data query out, and then according to the table rules re-routing distribution to the new table, here is not very complex, we will not expand the description, we focus on the data add/modify/delete route.

Before implementation, we need to think about a very important problem, is in a single table, we use incrementing a primary key to guarantee the uniqueness of the data, but if the data is split into four tables, each table by increasing its own primary key rules, can repeat id problem, that is to say, increasing the primary key is not globally unique.

One thing we need to know is that user_Info is split into multiple tables, but it should still be a complete data whole. When ids are duplicated, the uniqueness of the data is lost, so we need to think about how to generate a globally unique ID.

How to implement globally unique IDS

The feature of globally unique ids is that they can be guaranteed to be unique, so based on this feature, we can easily find many solutions.

  • Database increment ID (define global table)
  • UUID
  • Atomic increment of Redis
  • Twitter – Snowflake algorithm
  • Meituan leaf
  • MongoDB的ObjectId
  • Baidu’s UidGenerator

Features of distributed ids

  • Uniqueness: Ensures that the generated ID is globally unique.
  • Sequential increment: Ensures that the generated ID is sequential increment for a user or service.
  • High availability: Ensure that ids are generated correctly at all times.
  • With time: ID contains time, a glance at the past to know which day of data

Database auto-increment scheme

Create a sequence table in the database and use the increment ID in the database table to generate a global ID for the data of other services. Then each time you need to use the ID, you can directly obtain the ID from this table.

CREATE TABLE `uid_table`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `business_id` int(11)  NOT NULL.PRIMARY KEY (`id`) USING BTREE,
	UNIQUE (business_type) 
) 
Copy the code

In the application, each time the following code is called, an incrementing ID is continuously obtained.

begin;
REPLACE INTO uid_table (business_id) VALUES (2);
SELECT LAST_INSERT_ID();
commit;
Copy the code

Where, replace into is to delete the original same data every time, and add 1 at the same time, to ensure that we get a self-increasing ID every time

The advantage of this scheme is very simple, it also has disadvantages, that is, the pressure on the database is relatively large, and it is best to deploy a DB independently, and independent deployment will increase the overall cost, this is in the Leaf of the United States of America designed a very clever design scheme, later on

Advantages:

  • Very simple, using the function of the existing database system, low cost, DBA professional maintenance.
  • The MONOtonic increment of the ID can realize some services that have special requirements on the ID.

Disadvantages:

  • Strongly depends on DB. When DB is abnormal, the whole system is unavailable, which is a fatal problem. Configuring master/slave replication can increase availability as much as possible, but data consistency cannot be guaranteed in special cases. Inconsistencies during primary/secondary switchover may cause repeated numbers.
  • The ID sending performance bottleneck is limited to the read and write performance of a single MySQL server.

UUID

The UUID format is xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx 8-4-4-4-12, consisting of 36 characters. It is a 128bit binary character string converted to hexadecimal 32 characters and then concatenated with four -.

Five ways to generate UUID

  • Time-based UUID (DATe-time & MAC Address) : depends on the current time stamp and MAC address, ensuring global uniqueness. (Mac addresses are used, so the Mac address and generation time are exposed.)
  • UUID (date-time & group/ User ID) for distributed security Changes the first four bits of the version 1 timestamp to a POSIX UID or GID.
  • Namespace based UID-MD5 version (MD5 hash & Namespace). Generating MD5 hash based on the specified namespace/name is recommended.
  • Random-based UUID (pseudo-random number) : generates UUID based on random or pseudo-random number.
  • Namespace-based UID-SHA1 version (SHA-1 Hash & Namespace) : Change version 3 hash algorithm to SHA1

In Java, UUID based on MD5 algorithm and UUID based on random number are provided.

Advantages:

  • Local generation, no network consumption, simple generation, no high availability risk.

Disadvantages:

  • Not easy to store: UUID is too long, 16 bytes 128 bits, usually represented as a string of 36 lengths, which is not suitable for many scenarios.
  • Information insecurity: The algorithm that generates UUids based on MAC addresses can cause MAC addresses to leak, a vulnerability that was used to find the creator of Melissa’s virus.
  • Unordered query efficiency is low: The generated UUID is an unordered and unreadable string, so the query efficiency is low.
  • The mysql database is indexed by a b+ tree (b+ tree). Each insertion of a new UUID data in the mysql database will change the b+ tree (b+ tree). Because the UUID data is unordered, every insert of UUID data changes the primary key’s B + tree considerably, severely affecting performance

Snowflakes algorithm

SnowFlake is an open-source distributed ID generation algorithm for Twitter. The idea is to use a 64-bit long number as a globally unique ID. Snowflake algorithm is more common, in Baidu’s UidGenerator, Meituan Leaf, are useful to the implementation of snowflake algorithm.

Figure 6-11 shows the components of the Snowflake algorithm. The 64 bits consist of four parts.

  • The first part, the 1bit, is used to represent the sign bit, and ID is usually a positive number, so the sign bit is usually 0.

  • The second part, accounting for 41 bits: represents the time stamp, is the number of milliseconds of system time, but this timestamp is not the current system time, but the current system time-start time, the greater guarantee of the use of this ID generation scheme time!

  • So why do we need this timestamp, it’s for order, readability, I can look at it and I can guess when the ID was generated.

    41 digits can be 2 to the 41 to the minus 1,

    If used only to represent positive integers (positive numbers include 0 in computers), the range of values that can be represented is 0 to 2^41^-1, minus 1 because the range of values that can be represented starts at 0, not 1.

    This means that 41 bits represent 2^41^-1 milliseconds, which translates to (2^41^-1)/1000 * 60 * 60 * 24 *365=69 years, or 69 years

  • The third part is used to record the working machine ID, which contains 10 bits, meaning that the service can be deployed on up to 2^10 machines, or 1024 machines.

    The 10 bits can be divided into two 5 bits. The first 5 bits indicate the equipment room ID and the second 5 bits indicate the machine ID. This means that a maximum of 2^5 equipment rooms (32) can be supported.

  • The fourth part consists of 12 bits, which represents an increasing sequence for recording different ids generated within the same millisecond.

    So why do we need this serial number? Imagine, if it’s the same machine in the same millisecond, how can we make sure it’s unique, and in that case, we can use our serial number,

    The purpose is to ensure that the ID generated by the same machine in the same millisecond is unique, this is actually to meet the high concurrency of our ID, is to ensure that I come in the same millisecond concurrency scenario unique

    The largest positive integer that can be represented by 12 bits is 2^12-1=4095, which can be 0, 1, 2, 3,…. 4094 The 4095 numbers represent 4095 ID numbers generated in the same machine at the same time (millisecond).

    In base 12, the final value is 4095 if all 1s are used, which is the maximum number that can be stored in 12 bits.

DML operation of data after database and table

Ordering requires a global ID, so you need to add a field with a unique ID to the user_INFO table.

After the configuration is complete, introduce the signal method in the following code.

@Slf4j
@RestController
@RequestMapping("/users")
public class UserInfoController {

    @Autowired
    IUserInfoService userInfoService;
    SnowFlakeGenerator snowFlakeGenerator=new SnowFlakeGenerator(1.1.1);
    @PostMapping("/batch")
    public void user(@RequestBody List<UserInfo> userInfos){
        log.info("begin UserInfoController.user");
        userInfoService.saveBatch(userInfos);
    }

    @PostMapping
    public void signal(@RequestBody UserInfo userInfo){
        Long bizId=snowFlakeGenerator.nextId();
        userInfo.setBizId(bizId);
        String table=ConsistentHashing.getServer(bizId.toString());
        log.info("UserInfoController.signal:{}",table); MybatisPlusConfig.TABLE_NAME.set(table); userInfoService.save(userInfo); }}Copy the code

In addition, a Mybatis interceptor should be added to intercept and replace the user_INFO table to realize dynamic table routing.

@Configuration
public class MybatisPlusConfig {
    public static ThreadLocal<String> TABLE_NAME = new ThreadLocal<>();

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        Map<String, TableNameHandler> tableNameHandlerMap = new HashMap<>();
        tableNameHandlerMap.put("user_info", (sql, tableName) -> TABLE_NAME.get());
        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(tableNameHandlerMap);
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        returninterceptor; }}Copy the code

At this point, a basic repository and table walkthrough is complete, but the problem is still not completely solved.

Non-shard key query

We fragment the user_info table based on biz_id, which means that if we want to query the data of a table, we must find the corresponding table using biz_id route.

If the query field is not the shard key (i.e. not biz_ID), for example, in this case, the operator will query according to the name, phone number, gender and other fields, at this time we do not know which table to query these information.

The mapping between non-sharded keys and sharded keys is established

Biz_id -> biz_id -> biz_id -> biz_id -> biz_id -> biz_id -> biz_id -> biz_id -> biz_id -> biz_id -> biz_id Then biz_id is used to locate the target table.

The mapping table has only two columns, which can be used to create a large amount of data. When the amount of data is too large, the mapping table can also be split horizontally. At the same time, this mapping relationship is actually k-V key-value pair relationship, so we can use k-V cache for storage to improve performance.

Also, because the frequency of changes in this mapping relationship is low, the cache hit ratio is high and the performance is good.

The client database is separated from the operation database

Run the query may be beyond the single field mapping to query, may involve some more complex queries, and paging query, the query itself influence the performance of the database, is likely to affect the client for the operation of the users table, commonly so mainstream solution is to separate the two libraries.

Since the operation end does not have high requirements for data consistency and availability and does not need to access the database in real time, we can synchronize the data of the user table on the C end to the user table on the operation end, and the user table can directly look up the table in full without the need for sub-table operations.

Of course, if the performance of the operation side is too slow, we can also use ElasticSearch to meet the requirements of complex queries in the background.

Problems encountered in practical applications

In practice, we don’t always think of splitting the table in the future, so we often have to start thinking about this problem when the amount of data has reached a certain bottleneck.

Therefore, the biggest difficulty of dividing database and dividing table is not the method of splitting, but how to choose the proper splitting method according to the actual business situation in the database that has been running for a long time, and how to think about the data migration scheme before splitting. Moreover, the system still needs to remain available throughout the data migration and unassembly process.

A sub-table of a running table is typically divided into three phases.

In phase one, both old and new libraries are written

Since the old table certainly did not take into account the design of future sub-tables, and some models may need to be optimized as the business iterates, a new table is designed to hold the old data, and several things need to be done in this process

  • Double write database table, the old database table and the new database table write data synchronously, the success of the transaction is subject to the old model, the query also go to the old model
  • Check data through scheduled tasks to make up differences
  • Migrate historical data to the new model through scheduled tasks

Phase two, the new model prevails

In the second stage, the historical data has been navigated and the verification data is ok.

  • Data is still double-written, but transaction success and queries are subject to the new model.
  • Check data in scheduled tasks to make up data differences

Phase three, end double write

By the third stage, the data has been fully migrated, so.

  • Double write is cancelled. All data needs to be saved to the new model, and no new data needs to be written to the old model.
  • If there are still parts of the old business that depend on the old model, wait until all the business has been transformed before dismantling the old model.

The problems brought by the sub-database sub-table

As well as bringing the benefits of improved performance, it also brings a lot of trouble.

Distributed transaction problem

After the separation of databases and tables, the transactions in one library become across multiple libraries. How to ensure the consistency of data across libraries is also a common problem. As shown in Figure 6-13, when users create an order, they need to save an order record in the order library and modify the inventory of goods in the inventory, which involves the consistency of cross-library transactions. That is, how can I guarantee that the current two transaction operations will either succeed or fail at the same time?

Cross-database queries

For example, when querying contract information, customer data should be associated. Since contract data and customer data are in different databases, we definitely cannot directly use join to make associated query.

We have several main solutions:

  • For example, when we query the contract table of the contract library, we need to associate the customer table of the customer library. We can directly put some frequently associated query customer fields into the contract table to avoid the problem of cross-library associated query in this way.
  • Data synchronization: For example, if the merchant system wants to query the product table of the product system, we simply create a product table in the merchant system and synchronize product data periodically through ETL or other means.
  • Global tables (broadcast tables), such as basic data, are used by many business systems. If we put them in the core system, each system will have to associate query. In this case, we can store the same basic data in all databases.
  • ER table (binding table), some of our table data has logical primary and foreign key relationship, for example, order table order_info, stores the total number of goods, goods amount; Order_detail is the price of each item, the number of items, and so on. Or a dependency, the relationship between a parent table and a child table. There are often associated query operations between them. If the data of the parent table and the data of the child table are stored in different databases, cross-library associated query is also troublesome. So can we drop the parent table and the data on the same node as the data belonging to the parent table? For example, if order_id=1001 is stored on node1, all its details are stored on node1. Data from order_id=1002 is stored on node2. All details of order_id=1002 are stored on node2.

The above ideas are to avoid cross-library associated query through reasonable data distribution. In fact, in our business, we should try not to use cross-library associated query. If this happens, we should analyze whether the business or data split is reasonable. If cross-library association is still required, the last option is used.

  • System layer assembly

In different database nodes to meet the requirements of the data query, and then reassembled, returned to the client.

Sorting, page turning, function calculation and other problems

Limit paging and order by sorting can occur when querying across multiple libraries. For example, if there are two nodes, node 1 stores an odd number of ids =1,3,5,7,9… ; Id =2,4,6,8,10…

Run select * from user_info order by id limit 0,10

You need to fetch 10 entries on each of the two nodes, then merge the data and reorder it.

When functions such as Max, min, sum and count are calculated, corresponding functions need to be performed on each shard first, and then the result sets of each shard are summarized and calculated again, and finally the results are returned.

Globally unique ID

The problem of globally unique ids, as mentioned earlier, after horizontal partitioning, we need to consider the problem of globally unique ID design.

The problem of multiple data sources

After the database is divided into tables, it is inevitable that an application will be configured with multiple data sources.

In addition, it is possible to design a read-write separation scheme at the database level, which enables an application to access multiple data sources and implements dynamic routing for read-write separation.

These problems exist in every application system and need to be solved, so in order to provide a unified solution to the problems related to database and table, many open source technologies are introduced.

Sub-database sub-table solution

At present, there are more middleware on the market, such as

  • Cobar, taobao’s open source sub-database sub-table component, is basically not maintained at present.
  • Sharding-sphere, a repository and table component of Dangdang Open Source, has been donated to Apache Foundation
  • Atlas, Qihoo 360 open source sub-database sub-table components, is not how to maintain
  • Mycat, an upgrade from Ali Cobar, is maintained by the open source organization.
  • Vitess, Google’s open source sub-database sub-table component

At present, many companies choose Mycat or Sharding-Sphere, so I will focus on the use and principle of Sharding-Sphere.

The choice of a similar technology is all about community activity, maturity of the technology, and matching functionality to current needs.