Optimization Series | Game Data Sheet split Optimization classic case

1. Purpose
Through the comparison test, the performance comparison between the TABC of a data table is analyzed and the feasibility of the splitting scheme is determined.

2. Methods
Compare and test the two types before and after the split scheme.
At the same time, two types of updates are used for each test:
1. Similar to the original, the update of all fields in the data table is multiple
2. Updates to all fields in the data table are completed once

3. Environment
This test uses online actual data import. TABC table total of 1.32 million records, the full table total 100G.
Regroup all the fields to ensure that the actual row length of each table is no higher than 8KB, split into 3 sub-tables, the size is:
TABC_1.IBD 796M
TABC_2.IBD 10.2G
TABC_3.IBD 8.4G

Then the full table is randomly updated, and each round of testing is done concurrently with the stored procedure in the script, with a maximum of 10 concurrent processes.
Restart the mysqld before each test without additional pressure to ensure a fair environment.

4. Results

	Full table	Sub-table
Multiple update	02:56:49	00:16:06
One update	00:54:02	00:08:56

5. Conclusion
From the test results, it is obvious that the concurrency update efficiency is much higher than the score table, and if multiple updates to the TBLC table can be merged together, it can be greatly improved.

It is therefore recommended that:
1. Implementation of the same separation programme for other tables similar to the TBLC table;
2. Updating the data table is best done at once, instead of multiple fields, turning one transaction into multiple transactions;

The principle of the table:
1. Detach the text field from the original table;
2. Re-group the separated text fields to ensure that the actual row length of each table is no higher than 8KB;
3. If possible, do not use text, the actual length of less than 255, directly into the varchar;

--------------------------------------Split Line--------------------------------------

http://zhishuedu.comTraining is a professional quality training brand jointly launched by senior MySQL expert Ye Jinlong and Wu Bingxi, with MySQL dba combat optimization and Python devops Development course, which is the most conscientious and The most quality training courses.

This article is from the "Lao Ye teahouse" blog, please be sure to keep this source http://imysql.blog.51cto.com/1540006/1879732

Optimization Series | Game Data Sheet split Optimization classic case