Multi-column partitions in MySQL 5.5

For MySQL 5.5DatabaseMulti-column partitioningThis is what we will introduce in this article. We will introduce this part through an instance. Let's take a look at it!

Multi-column partitioning

The COLUMNS keyword now allows the string and date COLUMNS to be defined as partition COLUMNS, and multiple COLUMNS to define a partition. You may have seen some examples in the official documentation, such:

 
 

  
  
CREATE TABLE p1 (    
  
  
  a INT,    
  
  
  b INT,    
  
  
  c INT   
  
  
)    
  
  
PARTITION BY RANGE COLUMNS (a,b)    
  
  
(    
  
  
  PARTITION p01 VALUES LESS THAN (10,20),    
  
  
  PARTITION p02 VALUES LESS THAN (20,30),    
  
  
  PARTITION p03 VALUES LESS THAN (30,40),    
  
  
  PARTITION p04 VALUES LESS THAN (40,MAXVALUE),    
  
  
  PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)    
  
  
);    
  
  
 
  
  
CREATE TABLE p2 (    
  
  
  a INT,    
  
  
  b INT,    
  
  
  c INT   
  
  
)    
  
  
PARTITION BY RANGE COLUMNS (a,b)    
  
  
(    
  
  
  PARTITION p01 VALUES LESS THAN (10,10),    
  
  
  PARTITION p02 VALUES LESS THAN (10,20),    
  
  
  PARTITION p03 VALUES LESS THAN (10,30),    
  
  
  PARTITION p04 VALUES LESS THAN (10,MAXVALUE),    
  
  
  PARTITION p05 VALUES LESS THAN (MAXVALUE,MAXVALUE)    
  
  
)  
 
 

There are also other examples of partition by range columns (a, B, c. Because I have been using MySQL 5.1 for a long time, I don't know much about the meaning of Multi-column partitions. What does LESS THAN () mean? What will happen if the next partition is less than (10, 20? On the contrary, what if it is (20, 30?

All these questions require an answer. Before answering these questions, they need to better understand what we are doing.

At the beginning, it may be a bit confusing. When all partitions have a value of different ranges, in fact, they only partition a column in the table, but this is not the case. In the following example

 
 

  
  
CREATE TABLE p1_single (    
  
  
  a INT,    
  
  
  b INT,    
  
  
  c INT   
  
  
)    
  
  
PARTITION BY RANGE COLUMNS (a)    
  
  
(    
  
  
  PARTITION p01 VALUES LESS THAN (10),    
  
  
  PARTITION p02 VALUES LESS THAN (20),    
  
  
  PARTITION p03 VALUES LESS THAN (30),    
  
  
  PARTITION p04 VALUES LESS THAN (40),    
  
  
  PARTITION p05 VALUES LESS THAN (MAXVALUE)    
  
  
);  
 
 

It is different from the previous table p1. If you insert 10, 1, 1) in table p1, it will enter the first partition. On the contrary, in the p1_single table, it will enter the second partition because (10, 1) is smaller than (10, 10). If you only focus on the first value, you have not realized that you are comparing a tuple, instead of a single value.

Now let's analyze the most difficult part. What happens when you need to determine where a line should be placed? How do you determine the value of an operation similar to (10, 9) <(10, 10? The answer is actually very simple. When you sort them, use the same method to calculate the values of the two records.

 
 

  
  
a=10    
  
  
b=9    
  
  
(a,b) < (10,10) ?    
  
  
# evaluates to:    
  
  
(a < 10)    
  
  
OR   
  
  
((a = 10) AND ( b < 10))    
  
  
# which translates to:    
  
  
(10 < 10)    
  
  
OR   
  
  
((1010 = 10) AND ( 9 < 10))  
 
 

If there are three columns, the expression will be longer, but not more complex. First, test the smaller than calculation on the first project. If two or more partitions match the calculation, then test the second project. If there are more than one candidate partition, test the third project.

The introduction of MySQL 5.5 Multi-column partitioning is here. I hope this introduction will help you.

Source: http://lujia35.iteye.com/blog/718899.