MySQL从5.0和5.1版本开始引入了很多高级特性，如分区、触发器等。下面学习MySQL的分区表（本人所使用的MySQL版本是5.7）。
1 概述
对用户来说，分区表是一个独立的逻辑表，但底层是由多个物理子表组成 。实现分区的代码实际上是对一组底层表的句柄对象（Handler Object）的封闭。对分区表的请求，都会通过句柄对象转化成存储引擎的接口调用。所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但从底层的文件系统来看就很容易发现，每一个分区表都有一个使用#分隔命名的表文件。
MySQL实现分区表的方式——对底层表的封闭——意味着索引也是按分区的子表定义的，而没有全局索引。这和Oracle不同，在Oracle中可以更加灵活地定义索引和表是否进行分区。
MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区——只需要查询包含需要数据的分区就可以了。
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外如果想一次批量删除整个分区的数据也会变得更加方便。
MySQL的分区实现很复杂，所以不说明实现的全部细节。主要专注于性能方面，如果想了解更多的关于分区的知识，可阅读MySQL官网手册中的“分区”一节，其中介绍了很多分区相关的基础知识。另外，还可阅读CREATE TABLE、SHOW CREATE TABLE、ALTER TABLE和INFORMATION_SCHEMA.PARTITIONS、EXPLAIN关于分区部分的介绍。分区特性使得CREATE TABLE和ALTER TABLE命令变得更加复杂了
2 适用场景与限制
在下面的场景中，分区可以起到非常大的作用：
- 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据；
- 分区表的数据更容易维护。如想批量删除大量数据可使用清除整个分区的方式；另外还可以对一个独立分区进行优化、检查、修复等操作；
- 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备；
- 可以使用分区表避免某些特殊的瓶颈，如InnoDB的单个索引的互斥访问、ext3文件每户的inode锁竞争等；
- 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集场景下效果非常好。
分区表本身也有一些限制，下面是其中比较重要的几点：
- 一个表最多只能有1024个分区；
- 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区；
- 如果分区字段中有主键或者唯一索引列，那所有主键列和唯一索引列都必须包含进来；
- 分区表中无法使用外键约束。
3 分区表的原理
上面所述，分区表是由多个相关的底层表实现的，这些底层表也是由句柄对象（Handler object）表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个完全的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。
分区表上的操作按照下面的逻辑进行：
- SELECT查询
当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
- INSERT操作
当写一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对就底层表。
- DELETE操作
当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应的底层表进行删除操作。
- UPDATE操作
当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。
有些操作是支持过滤的。例如，当删除一条记录时，MySQL需要先找到这条记录，如果WHERE条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉。这对UPDATE语句同样有效。如果INSERT操作，则本身就只命中一个分区，其它分区都会过滤掉。MySQL先确定这条记录属于哪个分区，再将记录写入对应的底层分区表，无须对任何其他分区进行操作。
虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中就是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区层释放对应表锁。这个加锁和解锁过程与普通的InnoDB上的查询类似。
4 分区表的类型
MySQL支持多种分区表。看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。MySQL支持按时间、键值、哈希、列表分区，这其中有些还支持子分区，虽然这种情况很少见到。在MySQL5.5中，还可以使用RANGE COLUMNS类型的分区，这样即使是基于时间的分区也无须再将其转化成一个整数。例如，下表就可以将每一年的销售额存放在不同的分区里。
CREATE TABLE sales (
order_date DATETIME NOT NULL,
create_user varchar(20)
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catcha11 VALUES LESS THAN MAXVALUE
);上述SQL中，PARTITION分区子句中可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的值，且不能是一个常数。这里使用的是YEAR()函数，也可以使用TO_DAYS()这样的函数。根据时间分区是一种很常见的分区方式。通过Navicat查看SQL运行结果，如下图所示：
有看过的一个子分区案例中，对一个类似于前面设计的按时间分区的InnoDB表，系统通过子分区可降低索引的互斥访问的竞争。最近一年的分区的数据会被非常频繁的访问，这会导致大量的互斥量的竞争 。使用哈希子分区可将数据切成多个小片，大大降低互斥量的竞争问题。
一些其它的分区技术包括：
- 根据键值进行分区，来减少InnoDB的互斥量竞争；
- 使用数学模函数来进行分区，然后将数据轮询放入不同的分区。例如可以对日期使用模7的运算，或者更简单的使用返回周几的函数，如果只想保留最近几天的数据，这样区分更方便；
- 假设表有一个自增的主键列id，希望根据时间将最近的热点数据集中存放。那必须将时间戳包含在主键中才行，而这和主键本身的意义相矛盾。这种情况下可以使用这样的分区表达式来实现相同的目的：HASH(id DIV 1000000)，这将为100万数据建立一个分区。这样一方面实现了当初的分区目的，另一方面比起使用时间范围分区还避免了一个问题，就是当超过一定阈值时，如果使用时间范围分区就必须新增分区。
最后来看下MySQL进行分区操作时的情况，通过Navicat连接MySQL，还是sales表：
以上截图是MySQL支持的分区类型，下图是支持的子分区类型。
5 如何使用分区表
假设我们希望从一个非常大的表中查询出一段时间的记录，而这个表中包含了很多年的历史数据，数据是按照时间排序的。例如，希望查询最近几个月的数据，这大约有10条记录。根据硬盘设备，这些数据在硬盘中量很大。那如何查询这个表呢？怎么做才能高效？
首先很肯定：因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引。即使真的使用索引，会发现数据并不是按照想要的方式聚集的，而且会有大量的碎片产生，最终会导致一个查询产生成千上万的随机I/O，应用程序也随之僵死。情况好一点的时候，也许可以通过一两个索引解决一些问题。不过大多数情况下，索引不会有任何作用。这时候有两条路可选：让所有的查询都只在数据表上做顺序扫描，或者将数据表和索引全部都放在内存里。
这里陈述一个问题：在数据量超大的时候，B-Tree索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，随之，数据库的响应时间将大到不可接受的程度。另外，索引维护（磁盘空间、I/O操作）的代价也非常高。有些系统，如Infobright意识到这一点，于是就完全放弃使用B-Tree索引，而选择了一些更粗粒度的但消耗更少的方式检索数据，例如在大量数据上只索引对应的一小块元数据。
这正是分区要做的事情。理解分区时还可以将其当作索引的最初形态，以代价非常小的方式定位到需要的数据在哪一片“区域”。在这片“区域”中，可以做顺序扫描，可以建索引，还可以净数据都缓存到内存，等等。因为分区无须额外的数据结构记录每个分区有哪些数据——分区不需要精确定位每条数据的位置，也就无须额外的数据结构——所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么样的数据。
为了保证大数据量的可扩展性，一般有下面两个策略：
- 全量扫描数据，不要任何索引
可使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用WHERE条件，将需要的数据限制在少数分区中，则效率是很高的。当然，也需要做一些简单的运算保证保证查询的响应时间能够满足需求。使用该策略假设不用将数据完全放入到内存中，同时还假设需要的数据全都在磁盘上，因为内存很小，数据很快会被挤出内存，所以缓存起不了任何作用。这个策略适用于以正常的方式访问大量数据的时候。警告：必须将查询需要扫描的分区个数限制 在一个很小的数量。
- 索引数据，并分离热点
如果数据有明显的“热点”，而且除了这些数据，其他数据很少被访问到，那可将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存。
但仅这些还不够，MySQL的分区表实现还有很多陷阱，下面看如何去避免。
6 什么情况下会出问题
上面介绍的两个分区策略都是基于两个非常重要的假设：查询能够过滤（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) 来创建第一个分区。如果插入表的数据都是有效的，那第一个分区就是空的。这样即使需要检测到第一个分区，代价也非常小。
在MySQL5.5中就不需要这个优化技巧了，因为可以直接使用列本身而不是基于列的数据进行分区：PARTITION BY RANGE COLUMNS(order_date)。所以此案例最好的解决办法就是能够直接使用MySQL5.5的这个语法。
- 分区列和索引列不匹配
若定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。因为每个分区都有其独特的索引，所以扫描列A上的索引就需要扫描每一个分区内对应的索引。如果每个分区内对应索引的非叶子节点都在内存中，那扫描的速度还可以接受，但如果能跳过某些分区索引当然会更好。要避免此问题，应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。
听起来避免这个问题很简单，但有时也会遇到一意想不到的问题。例如，在一个关联查询中，分区表在关联顺序中是第二个表，并且关联使用的索引和分区条件并不匹配。那关联时针对第一个表符合条件的第一行，都需要访问并搜索第二个表的所有分区。
- 选择分区的成本可能很高
如上所述分区有很多类型，不同类型的分区的实现方式也不同，所以它们的性能也各不相同。尤其是范围分区，对于回答“这一行属于哪个分区”、“这些符合查询条件的行在哪些分区”这样的问题的成本可能会非常高，因为服务器需要扫描所有的分区定义的列表来找到正确的答案。类似这样的线性搜索的效率并不高，所以随着分区数的增长，成本会越来越高。
实际碰到的类似这样的最糟糕的一些问题是按行写入大量数据的时候。每写入一行数据到范围分区的表时，都需要扫描分区定义列表来找到适合的目标分区。可以通过限制分区的数量来缓存此问题，根据实践经验，对大多数系统来说，100个左右的分区是没有问题的。
其它的分区类型，比如键分区和哈希分区，则没有这样的问题。
- 打开并锁住所有底层表的成本可能很高
当查询访问分区表时，MySQL需要打开并锁住所有底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，并且该开销也和分区类型无关，会影响所有的查询。这一点对一些本身查询非常快的如根据主键查询并行，会带来明显的额外开销。可以用批量操作的方式来降低单个操作的此类开销，例如使用批量插入或LOAD DATA INFILE、一些删除多行数据，等等。当然同时还是需要限制分区的个数。
- 维护分区的成本可能很高
某些分区维护操作的速度会非常快，例如新增或删除分区（当删除一个大分区可能会很慢，不过这是另一回事）。而有些操作，例如重组分区或类似ALTER语句的操作：这类操作需要复制数据。重组分区的原理与ALTER类似，先创建一个临时的分区，然后将数据复制到其中，最后再删除原分区。
如上所述，分区表不是什么“银弹”。下面是目前分区实现中的一些其他限制：
- 所有分区都必须使用相同的存储引擎；
- 分区函数中可使用的函数和表达式也有一些限制；
- 某些存储引擎不支持分区；
- 对于MyISAM的分区表，不能再使用LOAD INDEX INTO CACHE操作；
- 对于MyISAM表，使用分区表时需要打开更多的文件描述符。虽然看起来是一个表，其实背后有很多独立的分区，每个分区对存储引擎来说都是一个独立的表。这样即使分区表只占用一个表缓存条目，文件描述符还是需要多个。因此，即使已经配置了适合的表缓存，以确保不会超过操作系统的单个进程可以打开的文件描述符的个数，但对于分区表而言，还是会出现起来文件描述符限制的问题。
最后，在5.5版本之前，MySQL的分区表有一些bug，在5.5版本做了很多改进，才使得分区表可以逐步考虑用在生产环境了。在后来的5.6版本中，分区表做了更多的增强，如新引入的ALTER TABLE EXCHANGE PARTITION。
7 查询优化
引入分区给查询优化带来了一些新的思路，同时也带来了新的bug。分区最大的优点就是优化器可以根据分区函数来过滤一些分区。根据粗粒度索引的优势，通过分区过滤通常可以让查询扫描更少的数据（在某些场景下）。
所以，对于访问分区表来说，很重要的一点就是在WHERE条件中带入分区列，有时很似多余也要带上，这样就可以让优化器能够过滤无须访问的分区。如果没有这些条件，MySQL就需要让对应存储引擎访问这个表的所有分区，如果非常大的话，就可能非常慢。
使用EXPLAIN PARTITION可以观察优化器是否执行了分区过滤，看下面的例子：
EXPLAIN PARTITIONS SELECT * FROM sales;
以上的SQL查询的是所有的分区。下面在WHERE条件中再加入一个时间限制条件：
EXPLAIN PARTITIONS SELECT * FROM sales WHERE order_date > '2011-01-01';
此时查询就没有查询所有分区了。即说明MySQL优化器已经很善于过滤分区。比如它能够将范围条件转化为离散的值列表，并根据列表中的每个值过滤分区。然而优化器也不是万能的。下面看一种情况是查询的WHERE条件理论上可以过滤分区，但实际上不行：
EXPLAIN PARTITIONS SELECT * FROM sales WHERE YEAR(order_date) = '2011';
上述SQL查询，实际上还是查询了所有的分区。MySQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。这就和查询中使用独立的列才能使用索引的道理是一样的。所以只需要将上面的查询等价地改成下面的形式即可：
EXPLAIN PARTITIONS SELECT * FROM sales WHERE order_date BETWEEN '2011-01-01' and '2011-12-31'; 
注意，两个时间段要是同一个年份的。如果写成下面的形式，还是会查询两个分区：
EXPLAIN PARTITIONS SELECT * FROM sales WHERE order_date > '2010-12-31' and order_date <= '2011-12-31';
因为WHERE条件中带入的分区列，而不是基于列的表达式，所在优化器能够利用这个查询条件去过滤部分分区。一个很重要的原则：即便在创建分区时可使用表达式，但在查询时却只能根据列来进行过滤分区。
8 合并表
合并表（Merge table）是一种早期的、简单的分区实现，和分区表相比有一些不同的限制，并且缺乏优化。分区表严格来说是一个逻辑上的概念，用户无法访问底层的各个分区，对用户来说是透明的。但是合并表允许用户单独访问各个子表。分区表和优化器的结合更紧密，这也是未来发展的趋势，而合并表则是一种将被淘汰的技术，在未来的版本中可能被删除。
和分区表类似的是，在MyISAM中各个子表可被一个结构完全相同的逻辑表所封装。可简单地把这个表当作是一个“老的、早期的、功能有限的”分区表，因为它自身的特性，甚至可提供一个分区表没有的功能。
全并表相当于一个容器，里面包含了多个真实表。可在CREATE TABLE中使用一种特别的UNION语法来指定包含哪些真实表。下面是一个创建全并表的例子：
CREATE TABLE t1(a INT NOT NULL PRIMARY KEY)ENGINE=MyISAM;
CREATE TABLE t2(a INT NOT NULL PRIMARY KEY)ENGINE=MyISAM;
INSERT INTO t1(a) VALUES(1),(2);
INSERT INTO t2(a) VALUES(1),(2);
CREATE TABLE mrg(a INT NOT NULL PRIMARY KEY)ENGINE=MERGE UNION=(t1, t2) INSERT_METHOD=LAST;
SELECT a from mrg;
这里最后建立的合并表和前面的各个真实表字段完全相同。在合并表中有的索引各个真实子表也有，这是创建合并表的前提条件。另外还注意到，各个子表在对应列上都有主键限制，但最终的合并表中仍出现了重复值，这是合并表的另一个不足：合并表中的每个子表行为和表定义都是相同的，但在合并表的全局上并不爱些条件限制。
这里的语法INSERT_METHOD=LAST告诉MySQL，将所有的INSERT语句都发送给最后一个表。指定FIRST或LAST关键字是唯一可以控制行插入到合并表的哪一个子表的方式（当然，还可以直接在SQL中明确地操作任何一个子表）。而分区表则有更多的方式控制数据写入到哪一个子表中。
INSERT语句的执行结果可以在最终的合并表中看到，也可在对应的子表看到：
INSERT INTO mrg(a) VALUES(3);
SELECT a FROM t2;
合并表还有些有趣的限制和特性，如在删除合并表或删除一个子表的时候会怎么样？删除一个合并表，它的子表不会受到任何影响，而如果直接删除其中一个子表则可能会有不同的后果，这要视操作系统而定。如在GNU/Linux上，如果子表的文件描述还是被打开的状态， 那这个表还存在，但是只能通过合并表才能访问到。在Windows系统中，如果先删除了子表，再去查询合并表，则会报错，如下所示：
DROP TABLE t1, t2;
SELECT a FROM mrg;
合并表还有很多其他的限制和行为，下面列举的这几点需要在使用的时候记住。
- 在使用CREATE语句创建一个合并表时，并不会检查各个子表的兼容性。如果子表的定义稍有不同，那MySQL就可能创建出一个后面无法使用的合并表。另外，如果在成功创建了合并表后再修改某个子表的定义，那之后再使用合并表可能会看到这样的报错：ERROR 1168(HY000)：Unable to open underlying table whick is differently defined or of non-MyISAM type or doesn't exist；
- 根据合并表的特性，不难发现，在合并表上无法使用REPLACE语法，无法使用自增字段。更多的细节请参阅MySQL官方手册；
- 若一个查询访问合并表，那它需要访问所有子表。这会根据键查找单行的查询速度要慢，若能够只访问一个对应表，速度肯定会更快。所以限制合并表中子表数量很重要，特别是当合并表是某个关联查询的一部分时，因为这时访问一个表的记录数可能会将比较操作传递到关联的其他表中，这时减少记录的访问就是减少整个关联操作。倘若打算使用合并表时，还需要记住以下几点：
- 执行范围查询时，需要在每一个子表上各执行一次，这比直接访问单个表的性能要差很多，而且子表越多，性能越糟；
- 全表扫描和普通表的全表扫描速度相同；
- 在合并表上做唯一键和主键查询查询时，一旦找到一行数据就会停止。所以一旦查询在合并表的某一个子表中找到一行数据，就会立刻返回，不会再访问任何其他的表；
- 子表的读取顺序和CREATE TABLE语句的顺序相同。如果需要频繁地按照某个特定顺序访问表，那可以通过这个特性来让合并排序操作更高效。
因为合并表的各个子表可以直接被访问，所以它还具有一些MySQL5.5分区表所不能提供的特性：
- 一个MyISAM表可以是多个合并表的子表；
- 可通过直接复制.frm、.MYI、.MYD文件，来实现在不同的服务器之间复制各个子表；
- 在合并表中可以很容易地添加新的子表：直接修改合并表的定义就可以了；
- 可以创建一个合并表，让它只包含需要的数据，例如只包含某个时间段的数据，而在分区表中是做不到这一点的；
- 如果想对栽个子表做备份、恢复、修改、修复或别的操作时，可以先将其从合并表中删除，操作结束后再将其加回去；
- 可以使用myisampack来压缩所有的子表。
相反，分区表的子表都是被MySQL隐藏的，只能通过分区表去访问子表。
注：本文参考《高性能MySQL》第3版。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
