24. MySQL分区限制

• 1. 分区限制
  • 1.1 分区键，主键，唯一键
  • 1.2 与存储引擎相关的分区限制
  • 1.3 与函数相关的分区限制
  • 1.4 分区和表级锁定
    • 1.4.1 DML 语句
    • 1.4.2 DDL 语句
    • 1.4.3 其它语句

1. 分区限制

• 划分表达式不允许的结构：

  • 存储过程，存储函数，UDFS，插件
  • 声明或使用变量

• 部分算术和逻辑运算符不允许在划分表达式中出现：

  • 除法运算符：/
  • 位运算符： |, &, ^, <<, >>, ~

• MySQL 5.5 不支持在分区表中使用HANDLER语句

• 创建分区表之后不要改变模式MySQL mode

• 性能方面

  • 文件系统操作。分区与重新分区的操作基于文件系统对它们的限制，所以速度的快慢与文件系统的类型、字符集、磁盘速度等都有关系。特别的，应该保证large_files_support可用并且合适地设置 open_files_limit。
  • MyISAM和分区文件描述符的用法。MyISAM为每个分区使用两个文件描述符，并且在操作数据的时候会使用所有的分区。在你重新划分分区后，MyISAM不会删除原来的文件描述符，而是继续扩张新的分区大小的数量，这是MyISAM的设计决定的。比如原来有100个分区，那么就有200个文件去存储，现在重新划分，变成101个分区。此时有402个文件去存储。
  • 表锁。分区操作会在表上获取写锁。读取表的操作几乎不会受到影响；一旦分区操作完成，就执行挂起的INSERT和UPDATE操作。
  • 存储引擎。对于MyISAM表而言，分区操作，查询和更新操作通常比使用InnoDB或NDB表更快。
  • LOAD DATA。在MySQL 5.5中，LOAD DATA使用缓冲来提高性能。您应该知道每个分区的缓冲区使用130 KB内存来实现此目的。

• 最大分区数。一张表最多有1024（包括子分区）个分区，NDB存储引擎不受此限制。

• 不支持查询缓存。

• 每个分区的key caches。在MySQL 5.5中，CACHE INDEX和LOAD INDEX INTO CACHE语句支持MyISAM分区表的key caches。

• InnoDB分区表不支持外键。

• ALTER TABLE ... ORDER BY。导致仅在每个分区内对记录排序。

• 通过修改主键对REPLACE语句的影响。see Section 19.5.1, “Partitioning Keys, Primary Keys, and Unique Keys” and REPLACE

• 完整TEXT索引。不支持FULLTEXT索引或搜索。

• 空间类型的列。具有空间数据类型（如POINT或GEOMETRY）的列不能在分区表中使用。

• 临时表、log表不能分区。

• 分区键的类型。分区键必须是整数列或解析为整数的表达式。可以接受NULL值。不能是子查询。

• 子分区。只有RANGE和LIST表支持子分区，并且子分区的类型必须是HASH或KEY分区。

• SUBPARTITION BY KEY 要求必须指明分区的字段或字段列表。

• 不支持DELAYED选项。不支持INSERT DELAYED。

• DATA DIRECTORY and INDEX DIRECTORY选项。

  • 用于表级选项会被忽略。

  • Windows不支持这两个选项。

  • mysqlcheck, myisamchk, and myisampack不支持分区表。

1.1 分区键，主键，唯一键

三者的关系：分区表的分区表达式中使用的所有列必须是表可能具有的每个唯一键的一部分（主键被定义为唯一键）。换句话说：表上的每个唯一键必须使用表的分区表达式中的每一列。如果表中没有唯一键则不受此约束。

例如下面这些是合法的：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

1.2 与存储引擎相关的分区限制

• MERGE ：表不能分区。分区表也不能合并。

• FEDERATED ：表不能分区。

• CSV ：表不能分区。

• InnoDB ：表中不能含有外键，也不能有被引用为外键的字段。对于子分区也不支持多块磁盘存储。实操发现MyISAM也不支持了。优化相关参考前文分区维护章节。

• 用户定义的分区和NDB存储引擎（NDB集群）：NDB只支持[LINEAR] KEY分区类型。

  一张表的最大的分区数取决于在一个集群中数据节点与节点组的数量、NDB集群的版本、其它实际情况， See NDB and user-defined partitioning。

  NDB表中每个分区可以存储的最大固定大小数据量为16 GB。

  分区表必须具有显式主键。

• 升级分区表：执行升级时，必须转储并重新加载由KEY分区并使用除NDB之外的任何存储引擎的表。

• 所有的分区必须使用同一个存储引擎：分区表的所有分区必须使用相同的存储引擎，并且它必须与整个表使用的存储引擎相同。另外，如果在创建或修改表的时候在表的级别没有指定存储引擎，要么不指定任何一个[子]分区的引擎，要么全部指定。

1.3 与函数相关的分区限制

参考分区键允许使用的Functions

1.4 分区和表级锁定

对于在执行DML或DDL语句时实际执行表级锁的MyISAM等存储引擎，影响分区表的这种语句会对表作为一个整体进行锁定。也就是说，所有分区都会被锁定，直到语句结束。例如，来自MyISAM的分区表的SELECT会导致整个表的锁定。

这会导致随着分区数量的增加下面讨论的语句的执行速度回越来越慢。但是在MySQL 5.6.6 版本引入了分区锁定修剪（partition lock pruning）大大减少了这种影响。

对于使用行级锁定并且在分区修剪之前不会实际执行（或需要执行）锁定的存储引擎（如InnoDB），情况不会如此。

下面讨论在采用表级锁定的存储引擎的分区表上使用MySQL语句的影响。

1.4.1 DML 语句

SELECT 锁定整个表。包括UNION和JOIN语句包含的表。 UPDATE 锁定整个表。 REPLACE 锁定整个表。 INSERT（包括 INSERT ... ON DUPLICATE KEY UPDATE） 锁定整个表。 INSERT ... SELECT 锁定源与目标表。

LOAD DATA 锁定整个表。

触发器激活时锁定整个表。

1.4.2 DDL 语句

CREATE VIEW 锁定它读到的所有分区表。 ALTER TABLE 锁定影响到的分区表。

1.4.3 其它语句

LOCK TABLES 锁定分区表的所有分区。

CALL stored_procedure(expr)调用时锁定在expr中涉及到的所有分区表的所有分区。

ALTER TABLE 还将在表级获取元数据锁。