MySQL Sub-Library sub-table "Turn" Http://blog.itpub.net/29254281/viewspace-1819422/MySQL the scheme of processing large-scale business data is generally a sub-database.
Vertical splits are generally selected at the beginning.
such as e-commerce website, may be in accordance with household appliances, books, mother and child and other commodity classification to split.
The benefit of this is that the split is simple and does not corrupt the database transaction.
But as the business grew, the order data table, which was categorized by books, had reached the scale of 10 T.
You need to think about horizontal splitting. The data from a logical table is stored separately to a different database server.
The advantage of horizontal splitting is that
Multiple database servers share the burden of CPU, memory, and network bandwidth.
Multiple database servers share the burden of backup and recovery.
Disadvantage is
Corrupted native database transactions. If you are using distributed transactions, you can slow down database performance.
Increased the burden of operations management. The original pipe a server on the line, now have to manage a bunch of servers.
Three main ways to split horizontally
1.Hash split. For example, follow the UserId mod 64. Distributing data on 64 servers
2. Scope Split. For example, each server plans to hold 100 million of the data, first write the data to server A. Once server A is full, the data is written to Server B, and so on.
The advantage of this approach is that it is easy to extend. Data is evenly distributed across servers.
3. routing table. Customize the distribution mode.
The content of Geomechanics teacher SACC2015.
The final consistency is achieved by means of transaction compensation.
Is the structure after the horizontal split. Suppose user A transfers 100 yuan to B.
Because the horizontal split destroys the original transaction. A transfer of the business, may encounter the following several circumstances.
In the first case, the application of the write queue timeout causes the message to be re-sent. The result is a original transfer of $100 to B. The result was a transfer of $200.
In the second case, the application successfully writes the message to the queue, but the queue server hangs. The result is a to B transfer failure.
In the third case, the middle tier (the consumer in the queue) takes the message out and modifies the account balance of a, but the library of user A is hung, causing the transaction to fail. The result is a to B transfer failure.
In the fourth case, the middle tier has successfully modified user A's account balance, but when the B user balance is modified, User B's database is hung up. The result is User A's money is deducted, but User B's money does not increase.
Fifth case. The middle tier gets the message from the queue, but it's not processed, and the middle layer itself hangs up.
Final consistency.
1. Apply the business log of this transaction to the database of the business log, and do not commit
Then, send two messages to the queue. One message is User A-100, and the other message is User B + 100.
Ensure that two messages are queued successfully, commit the transaction for the business log, and obtain the global transaction ID (tran_id). Once there are any exceptions, rollback the transaction.
The transaction is committed and the application can be returned directly. Prompts the user to complete the transaction.
2. Middle-tier Gets the message. Connect to User A's database first.
Queries the Business Log table (Tran_log), without which the global transaction ID is not processed. (Verify that the global transaction is in process only)
Queries the message Log table (Msg_log), which is not processed if there is a record. (Prevents message time-outs from being re-sent)
Then, start the transaction.
First update User A, minus 100 yuan.
Write the message Log table and record the processing
The last commit transaction.
3. Middle tier Connect User B's database and do the same.
Check Tran_log and Msg_log every 5 minutes. If there is an inconsistency, the transaction is compensated.
When Mr. Lui said that, I think tran_log should be in the following position, and then as the master of the various underlying libraries.
With MySQL asynchronous replication, copy the Tran_log to the library where User A and B reside.
But asked Mr. Lui in person, he said they did not do so. Because the amount of data in this tran_log is also very large.
They put Tran_log in the bottom of the library, but I can't figure out how to do it.
Unfortunately, the environment did not allow me to be more inquisitive.
But it's also interesting to keep your own thinking.
If I design in this way, later problems will be more, one is tran_log data volume is huge. In addition, the database where the Tran_log resides is prone to bottlenecks.
Reference Document: Http://www.cnblogs.com/aigongsi/archive/2013/01/25/2875731.html
MySQL Sub-Library sub-table
