Shulou(Shulou.com)05/31 Report--

This article mainly shows you "When can the fields of MySQL database be split", the content is simple and easy to understand, and the organization is clear. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn "When can the fields of MySQL database be split".

In the maintenance of the database among the table vertical division is inevitable, basically at the beginning of the business quasi-adhere to 3NF is wise, of course, there can be some anti-normal design. However, it is suggested that the anti-normal form should be considered on the basis of 3NF as appropriate.

When you really want to split some tables, what fields do you want to split? Let's analyze it below.

After the new business is launched, TPS suddenly increases, and we don't know much about the new business at this time. And the problem has to be analyzed and solved.

Analysis of solution steps

Parse recently generated binlog files to find out which table and which field are operated on.

This is a tool of God Wu Bingxi's Parsebinlog.

This tool can parse the table operation.

The above tool can only parse the operation of a single binlog file. If you want to parse multiple files, you can use the author's tool pasrebinlog_stat.py.

pasrebinlog_stat.py is a tool for generating excel files by performing statistics on the data after parsebinlog parsing.

How to use it (there is a small explanation at the end of github): https://github.com/daiguadaidai/mysql-binlog-statistical.

Using the author's method statistics will generate 5 files:

ll

-rw-rw-r-- 1 manager manager 58191 Sep 6 17:18 format.txt

-rw-rw-r-- 1 manager manager 100352 Sep 6 17:18 sort_by_delete.xls

-rw-rw-r-- 1 manager manager 100352 Sep 6 17:18 sort_by_insert.xls

-rw-rw-r-- 1 manager manager 100352 Sep 6 17:18 sort_by_total.xls

-rw-rw-r-- 1 manager manager 100352 Sep 6 17:18 sort_by_update.xls

If you are interested in update operations, you can check sort_by_update.xls where are sorted in descending order of update operations.

Then go to format.txt to see which field updates trivial based on the table name you want to know.

View parsed files related to excel

For example, here I see the t1 table in the definition line in the sort_by_update.xls file, indicating that it has the largest total number of updates.

Then find the statistical format of t1 table in format.txt as follows:

Table `app_db`.` easy_channel_item`:

Type TOTAL opt: 440353

Type INSERT opt: 8049

Type DELETE opt: 1419

Type UPDATE opt: 430885

28 col : 517

23 col : 145

7 col : 379383

6 col : 46449

12 col : 2

13 col : 2

9 col : 21

8 col : 21

5 col : 4102

4 col : 3853

26 col : 3

27 col : 173

21 col : 136

24 col : 3

25 col : 116

It is clear from above that '6 col' and '7 col' operations take up most of the update operations.

By looking at the database table structure, you can see that these two fields are divided into price and inventory tables.

splitting fields

If you know which field in which table updates frequently, you can first strip the field from the table into a separate table. As for whether or not to open another library needs to see whether it will affect other major businesses (such as: order payment, etc.). If there is any influence, it should be dismantled to other libraries.

The main purpose of the disassembly is to allow each page to store more data, and will not allow the t1 table data to be stored in the cache for a longer time, and will not appear ordinary age out (does not solve the TPS high problem).

There are generally two ways to improve TPS

The first is to decentralize TPS, that is, to partition the table into different libraries (generally, there are too many things to consider. The amount of data is generally not considered).

Second: use products that offer higher TPS (redis is a good option here).

This way, rule out the first one.

Use the second:

More time experience value: generally use redis can provide TPS:3-5W more machine conditions have improved.

QPS:7-10W More machine conditions have improved.

For the case of our TPS 3-5W TPS redis generally competent

The main concern here is about persistence, which is what needs to be architecturally designed.

Redis itself has a persistent function, persistent once per second.

In fact, synchronization can tolerate short-term non-real-time phenomena. If redis fails (downtime or whatever), you can restart redis to resynchronize all data.

You can build a master-slave or cluster of redis, which can solve the problem of a redis outage.

According to some principles of database software design and reference to the spike architecture, redis data can be synchronized to MySQL from time to time in the background.

Steps can include:

Write relevant formatted data to log files first (the ability to provide message queues is preferable).

After writing the log successfully, the data will be operated on in redis. Make sure there's a database to check if something went wrong.

The above is "MySQL database when the field can be split" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.