mysql 分区表详解

时间：2022-03-14 01:49

项目中要一张库表实现 list分区。并且支持多种数据库。 oracle 很顺利，只是mysql 听说5.1版本就已经支持了，可是试了很多个版本，都不行，后来查到原因是要5.5 以上版本分区才支持 varchar，可是试到 5.6 都一直报错： "VALUES value for partition ‘P_XX‘ must have type INT"

后来才发现、原来没有查对资料，走了弯路。是LIST COLUMNS才支持 varchar类型的分区字段。

mysql 官方文档 http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

译文：

The intuitive part: partition by non-integer columns：

任何使用过分区的人应该都遇到过不少问题，特别是面对非整数列分区时，MySQL 5.1只能处理整数列分区，如果你想在日期或字符串列上进行分区，你不得不使用函数对其进行转换。

MySQL 5.5中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词。我们假设有这样一个表：

CREATE TABLE expenses (  
  expense_date DATE NOT NULL,  
  category VARCHAR(30),  
  amount DECIMAL (10,3)  
);

如果你想使用MySQL 5.1中的分区类型，那你必须将类型转换成整数，需要使用一个额外的查找表，到了MySQL 5.5中，你可以不用再进行类型转换了，如：

ALTER TABLE expenses  
PARTITION BY LIST COLUMNS (category)  
(  
  PARTITION p01 VALUES IN ( ‘lodging‘, ‘food‘),  
  PARTITION p02 VALUES IN ( ‘flights‘, ‘ground transportation‘),  
  PARTITION p03 VALUES IN ( ‘leisure‘, ‘customer entertainment‘),  
  PARTITION p04 VALUES IN ( ‘communications‘),  
  PARTITION p05 VALUES IN ( ‘fees‘)  
);

这样的分区语句除了更加易读外，对数据的组织和管理也非常清晰，上面的例子只对category列进行分区。

在MySQL 5.1中使用分区另一个让人头痛的问题是date类型（即日期列），你不能直接使用它们，必须使用YEAR或TO_DAYS转换这些列，如：

 1 /* 在MySQL 5.1中*/  
 2 CREATE TABLE t2  
 3 (  
 4   dt DATE 
 5 )  
 6 PARTITION BY RANGE (TO_DAYS(dt))  
 7 (  
 8   PARTITION p01 VALUES LESS THAN (TO_DAYS(‘2007-01-01‘)),  
 9   PARTITION p02 VALUES LESS THAN (TO_DAYS(‘2008-01-01‘)),  
10   PARTITION p03 VALUES LESS THAN (TO_DAYS(‘2009-01-01‘)),  
11   PARTITION p04 VALUES LESS THAN (MAXVALUE));  
12  
13 SHOW CREATE TABLE t2 \G  
14 *************************** 1. row ***************************  
15        Table: t2  
16 Create Table: CREATE TABLE `t2` (  
17   `dt` date DEFAULT NULL 
18 ) ENGINE=MyISAM DEFAULT CHARSET=latin1  
19 /*!50100 PARTITION BY RANGE (TO_DAYS(dt))  
20 (PARTITION p01 VALUES LESS THAN (733042) ENGINE = MyISAM,  
21  PARTITION p02 VALUES LESS THAN (733407) ENGINE = MyISAM,  
22  PARTITION p03 VALUES LESS THAN (733773) ENGINE = MyISAM,  
23  PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

看上去非常糟糕，当然也有变通办法，但麻烦确实不少。使用YEAR或TO_DAYS定义一个分区的确让人费解，查询时不得不使用赤裸列，因为加了函数的查询不能识别分区。

但在MySQL 5.5中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单。

/*在MySQL 5.5中*/  
CREATE TABLE t2  
(  
  dt DATE 
)  
PARTITION BY RANGE COLUMNS (dt)  
(  
  PARTITION p01 VALUES LESS THAN (‘2007-01-01‘),  
  PARTITION p02 VALUES LESS THAN (‘2008-01-01‘),  
  PARTITION p03 VALUES LESS THAN (‘2009-01-01‘),  
  PARTITION p04 VALUES LESS THAN (MAXVALUE));  
 
SHOW CREATE TABLE t2 \G  
*************************** 1. row ***************************  
       Table: t2  
Create Table: CREATE TABLE `t2` (  
  `dt` date DEFAULT NULL 
) ENGINE=MyISAM DEFAULT CHARSET=latin1  
/*!50500 PARTITION BY RANGE  COLUMNS(dt)  
(PARTITION p01 VALUES LESS THAN (‘2007-01-01‘) ENGINE = MyISAM,  
 PARTITION p02 VALUES LESS THAN (‘2008-01-01‘) ENGINE = MyISAM,  
 PARTITION p03 VALUES LESS THAN (‘2009-01-01‘) ENGINE = MyISAM,  
 PARTITION p04 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */

在这里，通过函数定义和通过列查询之间没有冲突，因为是按列定义的，我们在定义中插入的值是保留的。

多列分区

COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区，你可能在官方文档中已经看到了一些例子，如：

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)  
)

同样还有PARTITION BY RANGE COLUMNS (a,b,c)等其它例子。由于我很长时间都在使用MySQL 5.1的分区，我对多列分区的含义不太了解，LESS THAN (10,10)是什么意思？如果下一个分区是LESS THAN (10,20)会发生什么？相反，如果是(20,30)又会如何？

所有这些问题都需要一个答案，在回答之前，他们需要更好地理解我们在做什么。

开始时可能有些混乱，当所有分区有一个不同范围的值时，实际上，它只是在表的一个列上进行了分区，但事实并非如此，在下面的例子中：

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)  
);

它和前面的表p1不一样，如果你在表p1中插入（10,1,1），它将会进入第一个分区，相反，在表p1_single中，它将会进入第二个分区，其原因是(10,1)小于(10,10)，如果你仅仅关注第一个值，你还没有意识到你在比较一个元组，而不是一个单一的值。

现在我们来分析一下最难懂的地方，当你需要确定某一行应该放在哪里时会发生什么？你是如何确定类似(10,9) < (10,10)这种运算的值的？答案其实很简单，当你对它们进行排序时，使用相同的方法计算两条记录的值。

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

如果有三列，表达式会更长，但不会更复杂。你首先在第一个项目上测试小于运算，如果有两个或更多的分区与之匹配，接着就测试第二个项目，如果不止一个候选分区，那还需要测试第三个项目。

下图所显示的内容表示将遍历三条记录插入到使用以下代码定义的分区中：

(10,10),

(10,20),

(10,30),

(10, MAXVALUE)

gxlsystem.com,布布扣