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

This article mainly introduces "why partition tables are hardly used in MySQL". In daily operation, I believe that many people have doubts about why partition tables are hardly used in MySQL. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "why partition tables are hardly used in MySQL". Next, please follow the editor to study!

In Oracle, it is natural to use partitioned tables, and it is common to have a database capacity of 500G and a size of more than 5T.

But in the use of MySQL, we hardly use partition tables. Today, some students communicate with each other in the group, so I sort it out according to my understanding. On the whole, most of the functions of Oracle basically exist in the MySQL partition table, including the fine-grained management of some partitions.

Therefore, if we start from the function alone, it is really difficult to find a very direct reason to reject the partition table.

I think it is mainly due to the difference in usage patterns. The main reason why we do not use it is to avoid excessive storage in a single database, and it is relatively troublesome to change partition tables. On the MySQL side, our goal is to make the database smaller and lighter, maybe more TP. At present, we exclude the design of partition tables and clearly write them into the development specifications. If according to data types, status tables, pipelining tables and configuration tables Among these three types, only the data of the pipeline log table are recommended to be stored in the form of the periodic table, which is convenient to expand at any time, and the change of the table structure is also convenient for the change mode of Tip1.

On this basis, the problem can be transformed into whether to use a partitioned table or a single table to store data. We have investigated this problem, and at present, some changes in query complexity are basically acceptable, and the risk coverage is smaller (the program side cannot completely guarantee that SQL will not be able to scan the whole table.) at present, we have realized the automatic expansion of the daily table and the lunar table in the periodic table (daily table, lunar table, weekly table, chronological table, quarterly table), and have taken over the automatic management of more than 300 periodic tables.

In addition, in the data flow system, the mode of the partition table is not friendly to the data warehouse system. If ETL extracts data directly, it basically needs to make some trade-offs in the part of the filtering conditions, and the impact is still relatively great.

Question1: why is it common for Oracle partition tables to use MySQL but not recommended? it's questionable.

Because it is two different databases, using MySQL as Oracle, there will be a lot of unsatisfactory places. It is normal for Oracle to pass T in a single database, TP+AP is very strong, native HTAP support, AP in MySQL is much weaker, and T in a single database is not recommended. Our capacity planning is currently designed according to the capacity specification of 300G, which can basically achieve hot and cold data separation and avoid excessive data growth at the design level.

Question2: what is the relationship between the daily table and the lunar table? Is the lunar table a joint query of a daily table or a data mirror?

At present, there is no direct relationship between the daily table and the lunar table, that is, it is selected by a comprehensive evaluation according to the business dimension, including the amount of data. If there is a small amount of business data and more range queries, the lunar table is recommended. If the amount of data is large and the amount of data is large, and there will be a change operation, it is generally recommended that the daily table is the daily table. The ratio of our daily table to the lunar table is about 20:1.

Question3: these are all planned during the previous system architecture design? Are there any cases of post-transformation? Will it be very difficult to promote research and development?

I think this is not a preliminary plan, but an iterative improvement. One of the benefits we provide is that after the transformation into a daily table, we will do the expansion and data cleaning of the daily table, and the business is very happy. In the past, there may be a mode of manually maintaining Excel lists or some metadata configuration to record the expansion of tables of different businesses, and there is a feeling of manual bookkeeping, if DBA or business classmates forget. Basically, it was a data failure.

So we wrote about automatic management services, including periodic table management of stand-alone and cluster middleware, basically without manual intervention.

The big pain points for the business are how to expand the table (sometimes forgetting that the consequences are quite serious), data cleaning (it is painful to follow the delete schema if the table is not dismantled) and table change (the Table1 schema is acceptable for the business and completely controllable for DBA)

Summary:

We do not use partition tables, on the one hand, for business needs, on the other hand, we provide periodic tables to solve business pain points, so it is also a hit-and-hit strategy.

At this point, the study on "why partition tables are hardly used in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.