A solution to the database sub-table of relational databases

A solution to the database sub-table of relational databases

When a relational database library or a single table reaches a certain level of data, a performance bottleneck occurs for a single node. The usual practice is to consider the sub-database table.

Why do you want to divide?

The Sub-Library reduces the load of the single point machine and the table, which improves the efficiency of the data operation, especially the write operation.

How to divide?

By the number of segments:

(1) user_id for the distinction, 1~1000 corresponding db1,1001~2000 corresponding DB2, and so on;
Pros: can be partially migrated
Cons: Uneven data distribution

(2) Hash modulus:

Hash the user_id (or use the value of the user_id if the user_id is numeric), then use a specific number, such as the application needs to cut a database into 4 databases, we use 4 this number to User_ ID of the hash value of the modulo operation, that is user_id%4, so that each operation there are four possible: The result is 1 when the corresponding DB1; the result is 2 when the corresponding DB2; the result is 3 corresponds to DB3; The result is 0 corresponds to DB4, This allows the data to be distributed to 4 DB in a very uniform way.
Advantages: Uniform Data distribution
Cons: Data migration is cumbersome and cannot be apportioned according to machine performance

(3) Save the database configuration in the authentication library

is to set up a DB, this DB separately save user_id to DB mapping, each time you access the database to query the database first to obtain the specific DB information, and then we need to do the query operation.
Advantages: Strong flexibility, one-on-one relationship
Cons: More than one query before each query, performance is greatly compromised
These are the three ways that we choose in the usual development, and some of the more complex projects may be mixed with these three ways. Through the above description, we have a simple understanding and understanding of the rules of the sub-Library. Of course, there will be better and more perfect way to divide the library, but also need our continuous exploration and discovery.

The Distributed data scheme provides the following functions:

(1) To provide the sub-library rules and routing rules (routerule abbreviation RR), the above description mentioned in the three segmentation rules directly embedded in the system, the specific embedding method in the next part of the detailed description and discussion;
(2) Introduce the concept of cluster (Group) to ensure the high availability of data;
(3) Introduce load Balancing strategy (loadbalancepolicy short lb);
(4) The cluster node availability detection mechanism is introduced to detect the availability of a single point machine in order to ensure the correct implementation of LB strategy, so as to guarantee the high stability of the system.
(5) The introduction of read/write separation to improve the data query speed;

MySQL Large cluster solution

Cobar using documentation (available as a MySQL large cluster solution)

http://blog.csdn.net/shagoo/article/details/8191346

A solution to the database sub-table of relational databases