How can I expedite the write operation of the master database? Sub-table Principle! Divide the table horizontally!

1. How can I expedite the write operation of the master database? Sub-table Principle! Divide the table horizontally! Reduce the table lock time!!!

2. Since the Sub-table, it is important to pay attention to the rules of the table! At the code level, the code layer knows which table to insert/select the data to.

3. To know on which table the data is being manipulated at the code level! Sub-table rules,

So the table rules to be designed with code, and the database of the table rules corresponding!!! The same!!! Same sub-table rules!!

4. What are the rules of the table? There are different strategies, the common ones are in accordance with 01234556789, account Mantissa 0 corresponds to 0 table, 1 corresponds to 1 table.

The table can also be divided according to the business. As per user or commodity.

The specific table rule, which is called the dimension. According to what dimension to divide the table.

If the user buys the commodity, need to save the transaction record, if according to the latitude of the user table, each user's transactions are saved in the same table, so it is very convenient to find a user's purchase situation, but the purchase of a product is likely to be distributed in more than one table, find it more troublesome. Conversely, according to the commodity dimension of the table, can be very convenient to find the purchase of this item, but to find out the buyer's transaction record is more troublesome.

5. Once the table is divided, you should also pay attention to the separate table storage location problem. Because only the same database can make federated queries. (a database server can have multiple databases, multiple databases)

So try to keep separate tables in a database to facilitate joint queries!!!!

2. Problems with Federated queries

Federated queries are basically not possible because the associated tables may not be in the same database.

6. Cross-database sub-tables are also prone to cross-database transaction operations and are inefficient across database transaction operations.

So when a single table is horizontally dividing multiple tables, be aware that the split tables remain in the same database.

To avoid the problem of federated queries or cross-database transactions!!!!

The 7.Mysql provides a mechanism for read and write separation, all writes must correspond to master, the read operation can be performed on master and slave machines, and Slave is identical to the structure of master, a master can have multiple slave,

So basically in the master writes the database to split the good table, then slave the database and the main structure

8. All the write operation is first on the master, and then update to the slave, so the synchronization from master to slave machine has a certain delay, when the system is very busy, the delay problem will be more serious, the increase in the number of slave machines will also make this problem more serious.

In addition, it can be seen that master is the bottleneck of the cluster, when too many write operations can seriously affect the stability of master, if master hangs, the entire cluster will not work properly.

So, 1. When reading the pressure is very large, you can consider adding slave machine fractional solution, but when the slave machine to achieve a certain amount of the sub-Library should be considered.

2. When writing pressure is very high, it is necessary to carry out the library operation.

How can I expedite the write operation of the master database? Sub-table Principle! Divide the table horizontally!