MySQL database partition management details determine success or failure

In a MySQL database, different parts of a table are stored as separate tables in different places. Partitions are primarily used to solve problems where tables are stored in different locations. This is also the case in other databases. They call this type of data table a partitioned table. Partition management is critical for MySQL databases. It is directly related to the performance and security of the database. For zoning management, the author has only two words: details.

Detail one: Determine if the version you are using supports partitioning

In MySQL, not all database versions support partition management. The first thing for the database administrator to do is to confirm that the version they are using supports this feature. If supported, the partitioning feature can be used to improve system performance and data security in subsequent design and maintenance. Conversely, it is not. To determine whether the version you are using supports partitioning, you can do so through system commands. As shown in the following illustration:

If the above variable is displayed with a value of Yes, then it is fortunate to tell you that the version you are using supports partition management. If the value of this variable is blank, it means that the version you are using does not support partition management. The author recommends that the existing database version be upgraded.

For the partition management, I need to emphasize that the MySQL database and other databases in the partition implementation differences. For the MySQL database, the partition function is mainly used to control the partitioning. such as this may be a hash function, or a list of values. Although database administrators do not need to be very aware of their internal implementation mechanisms. But it needs to be clear that different partitioning types are used to select separate partition functions. Conversely, the feature of the partitioning function determines the characteristics of the selected partition. From this perspective, mastering the basic features of a function can help us better understand the differences between different types of partitions. In order to facilitate the database administrator according to the actual situation of the enterprise design reasonable zoning.

Detail two: The relationship between the storage engine and the partition

The storage engine is primarily used to store database data. Obviously, there is a close connection between this storage engine and the partition. This link is mainly reflected in the following aspects.

Typically, a database administrator can use any storage engine supported by the database server for a data table that has a partition created. In other words, partitions are transparent to the data engine. This is mainly because in the MySQL database, the partitioning engine runs in a separate layer. and can communicate with any of these layers. However, if you dig deeper, there is still a matter of detail to consider, that is, to pay attention to a restrictive rule. You must use the same storage engine for all partitions of the same partitioned table. Give a simple example. Now there is a datasheet with two data partitions, A and B respectively. At this point, if the database administrator uses MyISAM for partition A, then only the MyISAM can be used for partition B, and no other, such as InnoDB, can be used.

This passage seems to be a bit contradictory. In fact, we can sum it up as a sentence. That is, different partitioned tables can take the data engine supported by any database. However, for different partitions of the same data table, only the same storage engine can be used.

Finally, it is not clear from the information about the storage engine that the data table of its services supports zoning capabilities. That is, you cannot use the command show engines to determine whether the database supports partitioning. The database administrator can only use the first command above to determine the relevant information about the database partition.

Detail three: Partitions are a whole and cannot be segmented

A data table can be divided into multiple partitions according to the actual situation. But the partitioned partition is still a whole. What does that mean? The author gives an example, you can understand. Now there is a datasheet with records and indexes in the table. When partitioning is designed, it is not possible to partition only the data, not the index, or the index partition, not the data partition. It's like splitting a cake. The cake will have two floors down. When the cake is divided, it is divided into two layers. And not just a layer of cream. It is important to remember that partitions are partitioned at the same time for all the content in the datasheet, not for parts.

Also note that if you want to partition a table, you need to partition the entire table. It is not possible to partition parts. This is not allowed if you do not perform partitioning on the upper part of a table, but only the lower half.

Detail four: How to improve query efficiency in partitioning

The use of partition management can improve the efficiency of the query very well. The author here gives a case of a retail business. If there is a supermarket now, using the MySQL database. A supermarket, the annual sales record will be tens of millions of. A few years have accumulated, the amount of data is very large. Now if users need to query, last year a product sales, it is like a needle in a haystack, the speed will be very slow.

In this case, if the use of zoning management, will obviously improve the efficiency of the query. When designing a database, partitions can be divided according to time. Set up a separate partition for each year's data. When you inquire about the sales of a product for another 2010 years, the system will only look for the relevant content from the 2010 data partition and ignore other unrelated partitions to improve the efficiency of the data query because the WHERE condition statement is specified.

In practical work, the author also often divides a table into multiple partitions, and then places different partitions on different disks. At this point, multiple hard drives can be used to disperse data queries to obtain greater query throughput. If you have already used disk array 5 in the Enterprise database server , it is superfluous to take this action. If there is only one hard drive in the server, or if there is a hard drive but not the disk array , the author will be a number of partitions on how fast the practice of the hard disk, or quite worthy of recommendation. For example, in the case of retail companies. If the company's sales record of one year is hundreds of millions of articles. At this time to the billions of data for statistical analysis of the hard disk throughput is a great test. At this point we can partition the datasheet. If you can divide it into four districts a, B, C, D according to the quarter. The AC two partitions are then placed on the hard disk armor and the remaining BD two partitions are placed on the hard drive B. The system reads data from two hard disks at the same time while reading the whole year. With this design, the throughput of the hard disk can be increased by one fold (assuming management overhead is not considered). In this way, you can also improve the efficiency of your query. It's similar to disk array 5. It's just a different level of implementation.

After the partition is visible, some queries can be greatly optimized. This is mainly because users can use data that satisfies a given where statement to be stored only within a specific partition (such as 2010-year transaction data). So you don't have to look for other remaining partitions when you query. Although partitions can be modified after the partition table is created. Even if you don't take this into account at first, you can organize your data and partition the data tables later if you need to. But I do not agree to do so. Because the data is organized, it is as if the data has been clipped and copied again. This job consumes a lot of server resources over the record. For this reason, I suggest that in the database design, the administrator needs to be able to predict the amount of data in the future. If zoning management is necessary, do so early.

Of course, zoning does not apply to all businesses. If the enterprise's data is relatively small, or most of the data is one-time use, at this time using zoning, will not bring value to the enterprise. Instead, it increases the overhead of management.

Original link: http://tech.it168.com/a2011/0301/1161/000001161632.shtml

"Edit Recommendation"