mysql 分区表

目录

分区表简介

分区表的应用场景

分区表的限制

分区表的原理

分区表的引擎层行为

分区策略研究

分区表的类型

范围分区

列表分区

列分区

hash分区

key分区

子分区

如何使用分区表

在使用分区表的时候需要注意的问题

NULL值会使分区过滤无效

分区列和索引列不匹配

选择分区的成本可能很高

打开并锁住所有底层表的成本可能很高

维护分区的成本可能很高

分区的一些其他限制

查询优化

合并表

---

分区表简介

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象(Handler Object)的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是从底层的文件系统来看就很容易发现，每一个分区表都有一个使用＃分隔命名的表文件。

MySQL实现分区表的方式一对底层表的封装一一意味着索引也是按照分区的子表定义的，而没有全局索引。这和Oracle不同，在Oracle中可以更加灵活地定义索引和表是 否进行分区。

MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时 候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所 有分区一一只需要查找包含需要数据的分区就可以了。

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关 的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。

在下面的场景中，分区可以起到非常大的作用：

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他 均是历史数据。

分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。

另外，还可以对一个独立分区进行优化、检查、修复等操作。

分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。

可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。

如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

CREATE TABLE `t` (
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

我在表t中初始化插入了两行记录，按照定义的分区规则，这两行记录分别落在p_2018和p_2019这两个分区上。

可以看到，这个表包含了一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件。也就是说：

对于引擎层来说，这是4个表；

对于Server层来说，这是1个表。

 你可能会觉得这两句都是废话。其实不然，这两句话非常重要，可以帮我们理解分区表的执行逻辑。

分区表的应用场景

1 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据

2 分区表的数据更容易维护

批量删除大量数据可以使用清除整个分区的方式

对一个独立分区进行优化、检查、修复等操作

如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候，按照时间分区的分区表，就可以直接通过alter table t drop partition …这个语法删掉分区，从而删掉过期的历史数据。

这个alter table t drop partition …操作是直接删除分区文件，效果跟drop普通表类似。与使用delete语句删除数据相比，优势是速度快、对系统影响小。 

3 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

4 可以使用分区表来避免某些特殊的瓶颈

innodb的单个索引的互斥访问

ext3文件系统的inode锁竞争

5 可以备份和恢复独立的分区

分区表的限制

1 一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区

2 在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区

3 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

4 分区表无法使用外键约束

分区表的原理

如前所述，分区表由多个相关的底层表实现，这些底层表也是由句柄对象(Handler object)表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和 管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表上的操作按照下面的操作逻辑进行：

SELECT查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

INSERT操作

当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。

DELETE操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区， 最后对相应底层表进行删除操作。

UPDATE操作

当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区， 最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

有些操作是支持过滤的。例如，当删除一条记录时，MySQL需要先找到这条记录，如 果WHERE条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉。 这对UPDATE语句同样有效。如果是INSERT操作，则本身就是只命中一个分区，其他分区都会被过滤掉。MySQL先确定这条记录属于哪个分区，再将记录写人对应的底层分区表，无须对任何其他分区进行操作。

虽然每个操作都会“先打开并锁住所有的底层表”，但这井不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB, 则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。

分区表的引擎层行为

我先给你举个在分区表加间隙锁的例子，目的是说明对于InnoDB来说，这是4个表。

我们初始化表t的时候，只插入了两行数据， ftime的值分别是，‘2017-4-1’ 和’2018-4-1’ 。session A的select语句对索引ftime上这两个记录之间的间隙加了锁。如果是一个普通表的话，那么T1时刻，在表t的ftime索引上，间隙和加锁状态应该是图3这样的。

 也就是说，‘2017-4-1’ 和’2018-4-1’ 这两个记录之间的间隙是会被锁住的。那么，sesion B的两条插入语句应该都要进入锁等待状态。

但是，从上面的实验效果可以看出，session B的第一个insert语句是可以执行成功的。这是因为，对于引擎来说，p_2018和p_2019是两个不同的表，也就是说2017-4-1的下一个记录并不是2018-4-1，而是p_2018分区的supremum。所以T1时刻，在表t的ftime索引上，间隙和加锁的状态其实是图这样的：

 由于分区表的规则，session A的select语句其实只操作了分区p_2018，因此加锁范围就是图4中深绿色的部分。

所以，session B要写入一行ftime是2018-2-1的时候是可以成功的，而要写入2017-12-1这个记录，就要等session A的间隙锁。

图就是这时候的show engine innodb status的部分结果。

看完InnoDB引擎的例子，我们再来一个MyISAM分区表的例子。

我首先用alter table t engine=myisam，把表t改成MyISAM表；然后，我再用下面这个例子说明，对于MyISAM引擎来说，这是4个表。 

在session A里面，我用sleep(100)将这条语句的执行时间设置为100秒。由于MyISAM引擎只支持表锁，所以这条update语句会锁住整个表t上的读。

但我们看到的结果是，session B的第一条查询语句是可以正常执行的，第二条语句才进入锁等待状态。

这正是因为MyISAM的表锁是在引擎层实现的，session A加的表锁，其实是锁在分区p_2018上

因此，只会堵住在这个分区上执行的查询，落到其他分区的查询是不受影响的。

看到这里，你可能会说，分区表看来还不错嘛，为什么不让用呢？我们使用分区表的一个重要原因就是单表过大。那么，如果不使用分区表的话，我们就是要使用手动分表的方式。

接下来，我们一起看看手动分表和分区表有什么区别。

比如，按照年份来划分，我们就分别创建普通表t_2017、t_2018、t_2019等等。手工分表的逻辑，也是找到需要更新的所有分表，然后依次执行更新。在性能上，这和分区表并没有实质的差别。

分区表和手工分表，一个是由server层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。因此，从引擎层看，这两种方式也是没有差别的。

其实这两个方案的区别，主要是在server层上。从server层看，我们就不得不提到分区表一个被广为诟病的问题：打开表的行为。

分区策略研究

每当第一次访问一个分区表的时候，MySQL需要把所有的分区都访问一遍。一个典型的报错情况是这样的：如果一个分区表的分区很多，比如超过了1000个，而MySQL启动的时候，open_files_limit参数使用的是默认值1024，那么就会在访问这个表的时候，由于需要打开所有的文件，导致打开表文件的个数超过了上限而报错。

下图就是我创建的一个包含了很多分区的表t_myisam，执行一条插入语句后报错的情况。

可以看到，这条insert语句，明显只需要访问一个分区，但语句却无法执行。

这时，你一定从表名猜到了，这个表我用的是MyISAM引擎。是的，因为使用InnoDB引擎的话，并不会出现这个问题。

MyISAM分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分区都由server层控制。通用分区策略，是MySQL一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。

从MySQL 5.7.9开始，InnoDB引擎引入了本地分区策略（native partitioning）。这个策略是在InnoDB内部自己管理打开分区的行为。

MySQL从5.7.17开始，将MyISAM分区表标记为即将弃用(deprecated)，意思是“从这个版本开始不建议这么使用，请使用替代方案。在将来的版本中会废弃这个功能”。

从MySQL 8.0版本开始，就不允许创建MyISAM分区表了，只允许创建已经实现了本地分区策略的引擎。目前来看，只有InnoDB和NDB这两个引擎支持了本地分区策略。

接下来，我们再看一下分区表在server层的行为。分区表的server层行为

如果从server层看的话，一个分区表就只是一个表。

这句话是什么意思呢？接下来，我就用下面这个例子来和你说明。如图8和图9所示，分别是这个例子的操作序列和执行结果图。 

可以看到，虽然session B只需要操作p_2107这个分区，但是由于session A持有整个表t的MDL锁，就导致了session B的alter语句被堵住。

这也是DBA同学经常说的，分区表，在做DDL的时候，影响会更大。如果你使用的是普通分表，那么当你在truncate一个分表的时候，肯定不会跟另外一个分表上的查询语句，出现MDL锁冲突。
到这里我们小结一下：

1. MySQL在第一次打开分区表的时候，需要访问所有的分区；

2. 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；

3. 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

而关于“必要的分区”的判断，就是根据SQL语句中的where条件，结合分区规则来实现的。比如我们上面的例子中，where ftime=‘2018-4-1’，根据分区规则year函数算出来的值是2018，那么就会落在p_2019这个分区。

但是，如果这个where 条件改成 where ftime>=‘2018-4-1’，虽然查询结果相同，但是这时候根据where条件，就要访问p_2019和p_others这两个分区。

如果查询语句的where条件中没有分区key，那就只能访问所有分区了。当然，这并不是分区表的问题。即使是使用业务分表的方式，where条件中没有使用分表的key，也必须访问所有的分表。

分区表的类型

MySQL支持多种分区表。我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。例如，下表就可以将 每一年的销售额存放在不同的分区里：

PARTITION分区子句中可以使用各种函数。但有一个要求，表达式返回的值要是一个确 定的整数，且不能是一个常数。这里我们使用函数YEAR(), 也可以使用任何其他的函数，如TO_DAYS()。根据时间间隔进行分区，是一种很常见的分区方式，后面我们还会再回 过头来看这个例子，看看如何优化这个例子来避免一些问题。

MySQL还支持键值、哈希和列表分区，这其中有些还支持子分区，不过我们在生产坏 境中很少见到。在MySQL 5.5中，还可以使用RANGE COLUMNS类型的分区，这样即使是 基于时间的分区也无须再将其转化成一个整数，后面将详细介绍。

在我们看过的一个子分区的案例中，对一个类似于前面我们设计的按时间分区的InnoDB 表，系统通过子分区可降低索引的互斥访问的竞争。最近一年的分区的数据会被非常频 繁地访问，这会导致大最的互斥量的竞争。使用哈希子分区可以将数据切成多个小片， 大大降低互斥量的竞争问题。

我们还看到的一些其他的分区技术包括：

根据键值进行分区，来减少InnoDB的互斥量竞争。

使用数学模函数来进行分区，然后将数据轮询放入不同的分区。例如，可以对日期 做模7的运算，或者更简单地使用返回周几的函数，如果只想保留最近几天的数据， 这样分区很方便。

假设表有一个自增的主键列id, 希望根据时间将最近的热点数据集中存放。那么必 须将时间戳包含在主键当中才行，而这和主键本身的意义相矛盾。这种情况下也可 以使用这样的分区表达式来实现相同的目的： HASH(id DIV 1000000), 这将为100 万数据建立一个分区。这样一方面实现了当初的分区目的，另一方面比起使用时间 范围分区还避免了一个问题，就是当超过一定闽值时，如果使用时间范围分区就必 须新增分区。

范围分区

范围分区表的分区方式是：每个分区都包含行数据且分区的表达式在给定的范围内，分区的范围应该是连续的且不能重叠，可以使用values less than运算符来定义。

1、创建普通的表

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

2、创建带分区的表，下面建表的语句是按照store_id来进行分区的，指定了4个分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
--在当前的建表语句中可以看到，store_id的值在1-5的在p0分区，6-10的在p1分区，11-15的在p3分区，16-20的在p4分区，但是如果插入超过20的值就会报错，因为mysql不知道将数据放在哪个分区

3、可以使用less than maxvalue来避免此种情况

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
--maxvalue表示始终大于等于最大可能整数值的整数值

4、可以使用相同的方式根据员工的职务代码对表进行分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

5、可以使用date类型进行分区：如虚妄根据每个员工离开公司的年份进行划分，如year(separated)

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

6、可以使用函数根据range的值来对表进行分区，如timestampunix_timestamp()

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
--timestamp不允许使用任何其他涉及值的表达式

基于时间间隔的分区方案，在mysql5.7中，可以基于范围或事件间隔实现分区方案，有两种选择

1、基于范围的分区，对于分区表达式，可以使用操作函数基于date、time、或者datatime列来返回一个整数值

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

2、基于范围列的分区，使用date或者datatime列作为分区列

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

列表分区

类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

列分区

mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */

 CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 `c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1,c3)
(PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */

 CREATE TABLE `list_c` (
 `c1` int(11) DEFAULT NULL,
 `c2` int(11) DEFAULT NULL,
 `c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY LIST COLUMNS(c3)
(PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
 PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */

hash分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含myql中有效的、产生非负整数值的任何表达式

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;


CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4;

key分区

类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

子分区

在分区的基础之上，再进行分区后存储

CREATE TABLE `t_partition_by_subpart`
(
  `id` INT AUTO_INCREMENT,
  `sName` VARCHAR(10) NOT NULL,
  `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL,
  `sAddr` VARCHAR(20) DEFAULT NULL,
  `sGrade` INT(2) NOT NULL,
  `sStuId` INT(8) DEFAULT NULL,
  `sSex` INT(1) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`, `sGrade`)
)  ENGINE = INNODB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(sGrade) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(5),
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(15)
);

如何使用分区表

假设我们希望从一个非常大的表中查询出一段时间的记录，而这个表中包含了很多年的 历史数据，数据是按照时间排序的，例如，希望查询最近几个月的数据，这大约有10亿 条记录。可能过些年本书会过时，不过我们还是假设使用的是2012年的硬件设备，而 原表中有10TB的数据，这个数据量远大于内存，并且使用的是传统硬盘，不是闪存（多 数SSD也没有这么大的空间）。你打算如何查询这个表？如何才能更高效？

首先很肯定：因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在 空间和维护上的消耗，也不希望使用索引。即使真的使用索引，你会发现数据并不是按 照想要的方式聚集的，而且会有大量的碎片产生，最终会导致一个查询产生成于上万的 随机I/O, 应用程序也随之僵死。情况好一点的时候，也许可以通过一两个索引解决一 些问题。不过多数情况下，索引不会有任何作用。这时候只有两条路可选：让所有的查询都只在数据表上做顺序扫描，或者将数据表和索引全部都缓存在内存里。

这里需要再陈述一遍：在数据量超大的时候，B-Tree索引就无法起作用了。除非是索 引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的 记录，如果数据量巨大，这将产生大量随机I/O, 随之，数据库的响应时间将大到不可 接受的程度。另外，索引维护（磁盘空间、I/O操作）的代价也非常高。有些系统，如 Infobright, 意识到这一点，于是就完全放弃使用B-Tree索引，而选择了一些更粗粒度 的但消耗更少的方式检索数据，例如在大量数据上只索引对应的一小块元数据。

这正是分区要做的事情。理解分区时还可以将其当作索引的最初形态，以代价非常小的 方式定位到需要的数据在哪一片”区域”。在这片”区域”中，你可以做顺序扫描，可以 建索引，还可以将数据都缓存到内存，等等。因为分区无须额外的数据结构记录每个分 区有哪些数据一分区不需要精确定位每条数据的位置，也就无须额外的数据结构—— 所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

为了保证大数据量的可扩展性，一般有下面两个策略：

全量扫描数据，不要任何索引。

可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的 数据位置。只要能够使用WHERE条件，将需要的数据限制在少数分区中，则效率是 很高的。当然，也需要做一些简单的运算保证查询的响应时间能够满足需求。使用 该策略假设不用将数据完全放入到内存中，同时还假设需要的数据全都在磁盘上， 因为内存相对很小，数据很快会被挤出内存，所以缓存起不了任何作用。这个策略 适用于以正常的方式访问大量数据的时候。警告：后面我们会详细解释，必须将查 询需要扫描的分区个数限制在一个很小的数量。

索引数据，并分离热点。

如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可 以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在 内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效地 使用缓存。

仅仅知道这些还不够，MySQL的分区表实现还有很多陷阱。下面我们看看都有哪些，以及如何避免。

在使用分区表的时候需要注意的问题

上面我们介绍的两个分区策略都基于两个非常重要的假设：查询都能够过滤(prunning) 掉很多额外的分区、分区本身并不会带来很多额外的代价。而事实证明，这两个假设在某些场景下会有问题。下面介绍一些可能会遇到的问题。

NULL值会使分区过滤无效

关于分区表一个容易让人误解的地方就是分区的表达式的值可以是NULL: 第一个 分区是一个特殊分区。

假设按照PARTITION BY RANGE YEAR(order_date)分区，那 么所有order_date为NULL或者是一个非法值的时候，记录都会被存放到第一个 分区。

现在假设有下面的查询： WHERE order_date BETWEEN'2012-01-01'AND '2012-01-31'。实际上，MySQL会检查两个分区，而不是之前猜想的一个：它会检 查2012年这个分区，同时它还会检查这个表的第一个分区。检查第一个分区是因为 YEAR()函数在接收非法值的时候可能会返回NULL值，那么这个范围的值可能会返 回NULL而被存放到第一个分区了。这一点对于其他很多函数，例如TO_DAYS()也一 样。

如果第一个分区非常大，特别是当使用“全量扫描数据，不要任何索引＂的策略时， 代价会非常大。而且扫描两个分区来查找列也不是我们使用分区表的初衷。为了避 免这种情况，可以创建一个“无用”的第一个分区，例如，上面的例子中可以使用 PARTITION p_nulls VALUES LESS THAN (0)来创建第一个分区。如果插入表中的数 据都是有效的，那么第一个分区就是空的，这样即使需要检测第一个分区，代价也 会非常小。

在MySQL 5.5中就不需要这个优化技巧了，因为可以直接使用列本身而不是基于列 的函数进行分区： PARTITION BY RANGE COLUMNS(order_date)。所以这个案例最好的解决方法是能够直接使用MySQL 5.5的这个语法。

分区列和索引列不匹配

如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在列a上 定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，所以扫描列 b上的索引就需要扫描每一个分区内对应的索引。如果每个分区内对应索引的非叶 子节点都在内存中，那么扫描的速度还可以接受，但如果能跳过某些分区索引当然会更好。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还同 时包含了可以过滤分区的条件。

听起来避免这个问题很简单，不过有时候也会遇到一些意想不到的间题。例如，在一个关联查询中，分区表在关联顺序中是第二个表，并且关联使用的索引和分区条件并不匹配。那么关联时针对第一个表符合条件的每一行，都需要访问并搜索第二个表的所有分区。

选择分区的成本可能很高

如前所述分区有很多类型，不同类型分区的实现方式也不同，所以它们的性能也各不相同。尤其是范围分区，对于回答“这一行属于哪个分区”、“这些符合查询条件的行在哪些分区”这样的问题的成本可能会非常高，因为服务器需要扫描所有的分区定义的列表来找到正确的答案。类似这样的线性搜索的效率不高，所以随着分区数的增长，成本会越来越高。

我们所实际碰到的类似这样的最糟糕的一次问题是按行写入大量数据的时候。每写入一行数据到范围分区的表时，都需要扫描分区定义列表来找到合适的目标分区。 可以通过限制分区的数量来缓解此问题，根据实践经验，对大多数系统来说，100 个左右的分区是没有问题的。

其他的分区类型，比如键分区和哈希分区，则没有这样的问题。

打开并锁住所有底层表的成本可能很高

当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。这一点对一些本身操作非常快的查询，比如根据主键查找单行，会带来明显的额外开销。可以用批量操作的方式来降低单个操作的此类开销，例如使用批量插入或者LOAD DATA INFILE、一次删除多行数据，等等。当然同时还是需要限制分区的个数。

维护分区的成本可能很高

某些分区维护操作的速度会非常快，例如新增或者删除分区（当删除一个大分区可 能会很慢，不过这是另一回事）。而有些操作，例如重组分区或者类似ALTER语句的 操作：这类操作需要复制数据。重组分区的原理与ALTER类似，先创建一个临时的 分区，然后将数据复制到其中，最后再删除原分区。

如上所述，分区表不是什么“银弹”。

分区的一些其他限制

所有分区都必须使用相同的存储引擎。

分区函数中可以使用的函数和表达式也有一些限制。

某些存储引擎不支持分区。

对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作。

对于MyISAM表，使用分区表时需要打开更多的文件描述符。虽然看起来是一个表， 其实背后有很多独立的分区，每一个分区对于存储引擎来说都是一个独立的表。这 样即使分区表只占用一个表缓存条目，文件描述符还是需要多个。因此，即使已经 配置了合适的表缓存，以确保不会超过操作系统的单个进程可以打开的文件描述符 的个数，但对于分区表而言，还是会出现超过文件描述符限制的问题。

最后，需要指出的是较老版本的MySQL问题会更多些。所有的软件都是有bug的。分区表在MySQL 5.1中引入，在后面的5.1.40和5.1.50之后修复了很多分区表的bug。在 MySQL 5.5中，分区表又做了很多改进，这才使得分区表可以逐步考虑用在生产环境了。 在即将发布的MySQL 5.6版本中，分区表做了更多的增强，例如新引入的ALTER TABLE EXCHANGE PARTITION。

查询优化

引入分区给查询优化带来了一些新的思路（同时也带来新的bug)。分区最大的优点就是 优化器可以根据分区函数来过滤一些分区。根据粗粒度索引的优势，通过分区过滤通常 可以让查询扫描更少的数据（在某些场景下）。

所以，对于访问分区表来说，很重要的一点是要在WHERE条件中带人分区列，有时候即 使看似多余的也要带上，这样就可以让优化器能够过滤掉无须访问的分区。如果没有这 些条件，MySQL就需要让对应存储引擎访问这个表的所有分区，如果表非常大的话， 就可能会非常慢。

使用EXPLAIN PARTITION可以观察优化器是否执行了分区过滤，下面是一个示例：

正如你所看到的，这个查询将访问所有的分区。下面我们在WHERE条件中再加入一个时 间限制条件：

MySQL优化器已经很善于过滤分区。比如它能够将范围条件转化为离散的值列表，井 根据列表中的每个值过滤分区。然而，优化器也不是万能的。下面查询的WHERE条件理 论上可以过滤分区，但实际上却不行：

MySQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的 值去过滤分区，即使这个表达式就是分区函数也不行。这就和查询中使用独立的列才能 使用索引的道理是一样的。所以只需要把上面的查询等价地 改写为如下形式即可：

这里写的WHERE条件中带入的是分区列，而不是基于分区列的表达式，所以优化器能够 利用这个条件过滤部分分区。一个很重要的原则是：即便在创建分区时可以使用表达式， 但在查询时却只能根据列来过滤分区。

优化器在处理查询的过程中总是尽可能聪明地去过滤分区。例如，若分区表是关联操作 中的第二张表，且关联条件是分区键，MySQL就只会在对应的分区里匹配行。(EXPLAIN 无法显示这种情况下的分区过滤，因为这是运行时的分区过滤，而不是查询优化阶段 的。）

合并表

合并表(Merge tahle)是一种早期的、简单的分区实现，和分区表相比有一些不同的限 制，并且缺乏优化。分区表严格来说是一个逻辑上的概念，用户无法访问底层的各个分区， 对用户来说分区是透明的。但是合并表允许用户单独访问各个子表。分区表和优化器的 结合更紧密，这也是未来发展的趋势，而合并表则是一种将被淘汰的技术，在未来的版 本中可能被删除。

和分区表类似的是，在MyISAM中各个子表可以被一个结构完全相同的逻辑表所封装。

可以简单地把这个表当作一个“老的、早期的、功能有限的＂的分区表，因为它自身的特性，甚至可以提供一些分区表没有的功能。

合井表相当于一个容器，里面包含了多个真实表。可以在CREATE TABLE 中使用一种特别 的UNION语法来指定包含哪些真实表。下面是一个创建合并表的例子：

注意到，这里最后建立的合并表和前面的各个真实表字段完全相同，在合并表中有的 索引各个真实子表也有，这是创建合并表的前提条件。另外还注意到，各个子表在对 应列上都有主键限制，但是最终的合并表中仍然出现了重复值，这是合并表的另一个 不足：合并表中的每一个子表行为和表定义都是相同，但是合并表在全局上并不受这 些条件限制。

这里的语法INSERT_METHOD=LAST告诉MySQL, 将所有的INSERT语句都发送给最后一 个表。指定FIRST或者LAST关键字是唯一可以控制行插入到合井表的哪一个子表的方式 （当然，还是可以直接在SQL中明确地操作任何一个子表）。而分区表则有更多的方式可 以控制数据写入到哪一个子表中。

INSERT语句的执行结果可以在最终的合并表中看到，也可以在对应的子表中看到：

合并表还有些有趣的限制和特性，例如，在删除合并表或者删除一个子表的时候会怎样？ 荨删除一个合并表，它的子表不会受任何影响，而如果直接删除其中一个子表则可能会有 不同的后果，这要视操作系统而定。例如在GNU/Linux上，如果子表的文件描述还是被 打开的状态，那么这个表还存在，但是只能通过合并表才能访问到：

合并表还有很多其他的限制和行为，下面列举的这几点需要在使用的时候时刻记住。

在使用CREATE语句创建一个合并表的时候，并不会检查各个子表的兼容性。如果子 表的定义稍有不同，那么MySQL就可能创建出一个后面无法使用的合并表。另外， 如果在成功创建了合并表后再修改某个子表的定义，那么之后再使用合并表可能会 看到这样的报错： ERROR 1168 (HYOOO): Unable to open underlying table which is differently defined or ofnon-MyISAM type or doesn't exist。

根据合并表的特性，不难发现，在合并表上无法使用REPLACE语法，无法使用自增 字段。更多的细节请参阅MySQL官方手册。

如果一个查询访问合并表，那么它需要访问所有子表。这会让根据键查找单行的查 询速度变慢，如果能够只访问一个对应表，速度肯定将更快。所以，限制合并表中 的子表数量很重要，特别是当合井表是某个关联查询的一部分的时候，因为这时访 问一个表的记录数可能会将比较操作传递到关联的其他表中，这时减少记录的访问 就是减少整个关联操作。当你打算使用合并表的时候，还需要记住以下几点：

执行范围查询时，需要在每一个子表上各执行一次，这比直接访问单个表的性 能要差很多，而且子表越多，性能越糟。

全表扫描和普通表的全表扫描速度相同。

在合并表上做唯一键和主键查询时，一且找到一行数据就会停止。所以一且查 询在合并表的某一个子表中找到一行数据，就会立刻返回，不会再访问任何其 他的表。

子表的读取顺序和CREATE TABLE语句中的顺序相同。如果需要频繁地按照某个 特定顺序访问表，那么可以通过这个特性来让合并排序操作更高效。

因为合并表的各个子表可以直接被访问，所以它还具有一些MySQL 5.5分区所不能提供的特性：

一个MyISAM表可以是多个合并表的子表。

可以通过直接复制，f rm,MYl,MYD文件，来实现在不同的服务器之间复制各个子表。

在合并表中可以很容易地添加新的子表：直接修改合并表的定义就可以了。

可以创建一个合并表，让它只包含需要的数据，例如只包含某个时间段的数据，而 在分区表中是做不到这一点的。

如果想对某个子表做备份、恢复、修改、修复或者别的操作时，可以先将其从合并 表中删除，操作结束后再将其加回去。

可以使用myisampack来压缩所有的子表。

相反，分区表的子表都是被MySQL隐藏的，只能通过分区表去访问子表。