Implementation of MySQL table sharding

MySQL table sharding is used to process massive databases. The following describes how to implement MySQL table sharding.

When dealing with a table with massive data, we need to perform table sharding. I am a table by month, that is, a table is created every month.

For example, table_2010_04, table_2010_05, table_2010_06... You can split tables based on time and different fields and types. My monthly table sharding is because I only need to query the daily data, do not need to query the data of the month, and do not need to query several tables at the same time (union), so the query speed is still very fast, however, if you need union queries for many tables, the speed may be slow. At that time, you have to reconsider the table sharding method.

At the beginning of each month, an operation is required to create a new table in the unit of month to store data for the past month. Of course, the name of a table remains unchanged, this table is used to temporarily store data for a new month.

I now have a table named my_table, which has always existed. For example, in June 3, the data in June is temporarily stored in this table. At this time, I need to change the name of my_table,

Rename table my_table to table_2010_05; at this time, the my_table table is renamed, but the original my_table table still exists, except that all data is stored on the table table_2010_05, this is the benefit of using rename for batch processing. A large amount of data does not need to be exported or imported during the transfer process, so the efficiency will be very low, in addition, the pressure on the database will be high,

After renaming the my_table table, you need to re-create the my_table table, CREAT my_table (.......)

In principle, table_2010_05 stores all data in January. However, this operation was performed only because it was No. 3. Therefore, it must have been stored on the table table_2010_05 in January, then we need to re-search the data from No. 1 to No. 3 from this table and insert the data to my_table for temporary storage in July)

Insert into my_table (field1, field2 ....) SELECT field1, field2 .... FROM my_table_2010_05 WHERE filed3> = "2010-06-01 "; in this way, the data from No. 1 to No. 3 will be saved to my_table, which temporarily stores the data for the month, and the table will be processed repeatedly by the beginning, the benefit of the my_table table remains unchanged is that the SQL statement inserted each time does not need to be changed, but the time needs to be determined during the query to determine the specific table to be searched, because we have already performed the table sharding operation.

In this way, such an operation is repeated at the beginning of each month, so that the sub-tables can be smoothly implemented and such an operation is required between inserts. alter table my_table MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 15000; the advantage of this operation is to ensure that the table stores a large amount of data larger than 2 GB. At present, my table in one month stores more than 11 GB of data, so it is necessary to perform table sharding error, you can perform table sharding based on your actual situation. If the data volume is small, you do not need to perform this operation)
 

 

How to copy data from a MySQL table to a new table

Implementation of MySQL multi-Table Deletion

Advantages and disadvantages of MySQL independent tablespace

Case sensitivity of mysql Databases

Restoration of MySQL MyISAM Table Structure