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

This article introduces the relevant knowledge of "MySQL state table optimization is what". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Since the construction of the technology stack of the data source mart, in fact, the whole system has been constantly improving. We have basically reached an agreement on the export direction of data flow, that is, to achieve data delivery efficiency according to real-time standards as much as possible on the basis of ensuring data accuracy and stability. Therefore, the goal of the data source mart is not to simply deliver data, but to provide strong support for the middle and lower reaches of the service, and even provide reference and basis for real-time data flow.

At present, if the data of a table is to provide near-real-time data delivery standards, there are generally the following types of strategies:

1)Based on the self-increment ID mode, the incremental position of data can be quickly located according to the self-increment ID of the database, and the incremental synchronization of data can be basically realized. Of course, this mode has great limitations, requiring the table to contain the self-increment ID field, and there will be potential bottlenecks for the throughput of the database. At the same time, it is not suitable for the real-time flow of data in the cluster environment based on middleware.

2)Based on the time field synchronization mode, the synchronization of time fields is a classic method to achieve incremental synchronization of table data, which is also closely integrated with the business. However, the potential risk is that there may be multiple related time fields, and the synchronization customization degree is high. In addition, it is difficult to completely locate the data by using the incremental mode alone, or it needs the support of another dimension, such as self-increasing ID.

If a state table wants to realize real-time circulation and real-time delivery, the problem faced is actually more complicated.

Generally, the amount of data in this type of status table is huge, but it is likely that there is no field based on self-increasing ID (usually ID field based on service), and it is basically feasible to locate unique record content based on time field, but it is difficult to quickly locate unique record content. The most important point is that what we get through unique location is the changed value. The value before the change has been completely covered, so the definition of the change amount is more complicated.

At present, some of the bottleneck problems encountered mainly include:

When data is extracted by the middle and lower stream data service, although the data source is updated in real time, the subsequent data service is difficult to locate the incremental data. Through the above multiple dimensions are not appropriate, usually do data check can only helplessly use select count(*) from xxx this check mode, and to solve this problem the most direct solution is to provide the corresponding flow log program segment, if the development ability is strong this thing is better landing, and if the business risk is high, this thing to solve it is more troublesome.

The following is a compromise solution that can extract data changes and update synchronous data status in real time without requiring program modification code. The general design idea is based on real-time logging services, in this case Maxwell.

Let me explain briefly that if the data of a status table is to be delivered in real time, then in the data source mart, we can ensure that the data of the status table is replicated in real time without problems. Technically, it can be done completely. Whether it is based on the library level or filtered to the table level, it is operable.

Based on Maxwell's real-time log extraction, we can parse the real-time changes in the data in the table from the state table, and we can indirectly implement a billing table. For the middle and lower reaches, it is to indirectly convert the state table into a running log table, thus indirectly realizing real-time circulation and delivery.

"MySQL state table optimization is what" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.