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

This article is to share with you about how to achieve sub-table processing in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

How to realize the processing of MySQL subtable

For example, table_2010_04,table_2010_05,table_2010_06. You can divide the table according to your needs, according to time and different fields and types. I divide the table by month because I only need to query the daily data, do not need to check the month data, do not need several tables at the same time (union) query, so the query speed is still very fast, but if you need many tables of union query speed may be slow, then you have to reconsider the way of sub-table.

At the beginning of each month, an operation is carried out to create a new table in monthly units to store the data of the past month. Of course, the name of a table is always the same, and this table is used to temporarily store the data of the new month.

I now have a table called my_table, and this table exists all the time. For example, it is June 3, and the data for June is temporarily stored in this table. At this time, you need to change the name of my_table first.

Renametablemy_tabletotable_2010_05 At this time, the my_table table was renamed, but the original my_table table still exists, but all the data is already stored on the table_2010_05 table, which is the advantage of batch processing with rename. A large amount of data does not need to be exported and imported when it is transferred, which will be very inefficient and put great pressure on the database.

How to realize the processing of MySQL subtable

After renaming the my_table table, you need to recreate the my_table table, CREATmy_table (.)

In principle, table_2010_05 stores data from May, but since this operation is only carried out on the 3rd, the data from June 1 to 3 must still be stored in the table_2010_05 table. It is necessary to re-find the data from this table and insert it into my_table (June data is temporarily stored).

Insertintomy_table (field1,field2....) SELECTfield1,field2....FROMmy_table_2010_05WHEREfiled3 > = "2010-06-01" In this way, the data from the 1st to the 3rd are stored in the my_table, which temporarily stores the June data, and at the beginning of July, the table has to be processed by repeated operations. The advantage of my_table this table is that the sql statement inserted each time does not need to be changed, but when you query, you need to judge the time to determine which table to look for, because we have already done the table splitting operation.

In this way, such an operation is repeated at the beginning of each month, and the sub-table is successfully realized, and such an operation is also carried out between inserts, ALTERTABLEmy_tableMAX_ROWS=1000000000AVG_ROW_LENGTH=15000. The advantage of this operation is to ensure that the table stores a large amount of data (more than 2G). At present, more than 11G of data is stored in my table in a month, so it is very necessary to make a sub-table error. we can divide the table according to their own actual situation (if the amount of data is very small, then there is no need to do this operation).

The above is how to achieve sub-table processing in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow 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.