• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 位置: MySQL 8 中文手册 -> MySQL 分区

    分区的限制

    本节讨论了对MySQL分区支持的当前限制。

    禁止构造。分区表达式中不允许以下构造:

    • 存储过程,存储函数,UDF或插件。
    • 声明的变量或用户变量。

    有关分区表达式中允许的SQL函数的列表,请参见“与函数相关的分区限制”。

    算术和逻辑运算符。分区表达式中允许使用算术运算符+-*。但是,结果必须是一个整数值或NULL(关于[LINEAR] KEY分区的情况除外,如本章其他地方所述;有关更多信息,请参见“分区类型”)。

    DIV运营商还支持;该/操作是不允许的。

    该位运营商|&^<<>>,并~没有在分区表达式允许的。

    服务器SQL模式。使用用户定义分区的表在创建时不会保留有效的SQL模式。如本手册其他部分所述(请参见“服务器SQL模式”),许多MySQL函数和运算符的结果可能会根据服务器SQL模式而变化。因此,在创建分区表之后的任何时间更改SQL模式都可能导致此类表的行为发生重大变化,并且很容易导致数据损坏或丢失。由于这些原因,强烈建议您不要在创建分区表之后更改服务器SQL模式

    例子。以下示例说明了由于服务器SQL模式的更改而导致的分区表行为的某些更改:

    1. 错误处理。如在其他地方所讨论的,“特殊”值(例如零)的处理NULL在不同的服务器SQL模式之间可能有所不同(请参见“服务器SQL模式”)。例如,ERROR_FOR_DIVISION_BY_ZERO可能会影响是否可以将0作为值插入到其分区表达式使用或的表中。column DIV valuecolumn MOD value.
    2. 表可访问性。有时,服务器SQL模式的更改会使分区表无法使用。CREATE TABLE仅当该NO_UNSIGNED_SUBTRACTION模式有效时,以下语句才能成功执行:

      mysql> SELECT @@sql_mode;
      +------------	+
      | @@sql_mode	|
      +------------	+
      |	|
      +------------	+
      1 row in set (0.00 sec)
      
      mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
      ->   PARTITION BY RANGE(c1 - 10) (
      ->     PARTITION p0 VALUES LESS THAN (-5),
      ->     PARTITION p1 VALUES LESS THAN (0),
      ->     PARTITION p2 VALUES LESS THAN (5),
      ->     PARTITION p3 VALUES LESS THAN (10),
      ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
      -> );
      ERROR 1563 (HY000): Partition constant is out of partition function domain
      
      mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> SELECT @@sql_mode;
      +-------------------------	+
      | @@sql_mode	|
      +-------------------------	+
      | NO_UNSIGNED_SUBTRACTION	|
      +-------------------------	+
      1 row in set (0.00 sec)
      
      mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
      ->   PARTITION BY RANGE(c1 - 10) (
      ->     PARTITION p0 VALUES LESS THAN (-5),
      ->     PARTITION p1 VALUES LESS THAN (0),
      ->     PARTITION p2 VALUES LESS THAN (5),
      ->     PARTITION p3 VALUES LESS THAN (10),
      ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
      -> );
      Query OK, 0 rows affected (0.05 sec)
      

      如果NO_UNSIGNED_SUBTRACTION在创建后删除服务器SQL模式tu,则可能不再能够访问此表:

      mysql> SET sql_mode='';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> SELECT * FROM tu;
      ERROR 1563 (HY000): Partition constant is out of partition function domain
      mysql> INSERT INTO tu VALUES (20);
      ERROR 1563 (HY000): Partition constant is out of partition function domain
      

      另请参见“服务器SQL模式”。

    服务器SQL模式还会影响分区表的复制。主服务器和从服务器上不同的SQL模式可能导致对分区表达式的求值方式有所不同。这可能会导致分区之间的数据分配在给定表的主副本和从属副本中有所不同,甚至可能导致对成功在主副本上的分区表的插入在从属副本上失败。为了获得最佳结果,您应该始终在主服务器和从服务器上使用相同的服务器SQL模式。

    性能注意事项。下表列出了分区操作对性能的一些影响:

    • 文件系统操作。分区和重新分区操作(例如ALTER TABLE使用PARTITION BY ...,,REORGANIZE PARTITIONREMOVE PARTITIONING)取决于文件系统操作的实现。这意味着这些操作的速度受以下因素的影响:文件系统类型和特征,磁盘速度,交换空间,操作系统的文件处理效率以及与文件处理有关的MySQL服务器选项和变量。特别是,您应确保large_files_support已启用它并且open_files_limit已正确设置。涉及的分区和重新分区操作InnoDB通过启用可以使表更有效innodb_file_per_table

      另请参阅最大分区数。

    • 表锁。通常,对表执行分区操作的进程对表进行写锁定。从此类表读取的数据相对不受影响;挂起,INSERT并且UPDATE在分区操作完成后立即执行操作。有关InnoDB此限制的特定例外,请参阅“分区操作”。
    • 索引;分区修剪。与非分区表一样,正确使用索引可以显着加快对分区表的查询。此外,设计分区表和对这些表的查询以利用分区修剪可以大大提高性能。有关更多信息,请参见“分区修剪”。

      分区表支持索引条件下推。请参见“索引条件下推优化”。

    • LOAD DATA的性能。在MySQL 8.0中,LOAD DATA使用缓冲来提高性能。您应该意识到,缓冲区为每个分区使用130 KB内存来实现此目的。

    最大分区数。对于不使用NDB存储引擎的给定表,最大分区数为8192。此数目包括子分区。

    使用NDB存储引擎的表的用户定义分区的最大可能数量取决于所使用的NDB Cluster软件的版本,数据节点的数量以及其他因素。有关更多信息,请参见 NDB和用户定义的分区。

    如果在创建具有大量分区(但小于最大值)的表时,遇到错误消息(例如,来自存储引擎的错误...:打开文件时资源不足),则可以解决此问题通过增加open_files_limit系统变量的值。但是,这取决于操作系统,可能并非在所有平台上都是可行或不可取的。有关更多信息,请参见第B.4.2.17节“找不到文件和类似错误”。在某些情况下,由于其他原因,也不建议使用大量(数百个)分区,因此使用更多分区不会自动导致更好的结果。

    另请参阅文件系统操作。

    分区的InnoDB表不支持外键。使用InnoDB存储引擎的分区表不支持外键。更具体地说,这意味着以下两个陈述是正确的:

    1. InnoDB使用用户定义的分区的表的定义都不能包含外键引用。没有InnoDB表,其定义中包含外键引用可以被划分。
    2. 没有InnoDB表定义可能包含一个外键引用到用户分区表;InnoDB具有用户定义分区的表不能包含外键引用的列。

    刚刚列出的限制范围包括所有使用InnoDB存储引擎的表。CREATE TABLEALTER TABLE这将导致表违反这些限制的语句是不允许的。

    ALTER TABLE ... ORDER BY。针对分区表运行的语句仅导致每个分区内的行排序。ALTER TABLE ... ORDER BY column

    修改主键对REPLACE语句的影响。在某些情况下(参见“分区键,主键和唯一键”),可能希望修改表的主键。请注意,如果您的应用程序使用了REPLACE语句并执行了此操作,则这些语句的结果可能会发生巨大变化。有关更多信息和示例,请参见“ REPLACE语句”。

    FULLTEXT索引。分区表不支持FULLTEXT索引或搜索。

    空间列。具有空间数据类型(例如POINTGEOMETRY不能在分区表中使用)的列。

    临时表。临时表无法分区。

    日志表。无法对日志表进行分区;ALTER TABLE ... PARTITION BY ...这样的表上的语句失败,并显示错误。

    分区键的数据类型。分区键必须是整数列或解析为整数的表达式。ENUM不能使用采用列的表达式。列或表达式值也可以是NULL;请参见“ MySQL分区如何处理NULL”。

    此限制有两个例外:

    1. 当通过[LINEAR]进行分区时KEY,可以使用除以外的任何有效MySQL数据类型的列TEXT或将其BLOB用作分区键,因为内部键哈希函数会从这些类型中生成正确的数据类型。例如,以下两个CREATE TABLE语句是有效的:

      CREATE TABLE tkc (c1 CHAR)
      PARTITION BY KEY(c1)
      PARTITIONS 4;
      
      CREATE TABLE tke
          ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') )
      PARTITION BY LINEAR KEY(c1)
      PARTITIONS 6;
      
    2. 通过RANGE COLUMNS或进行分区时LIST COLUMNS,可以使用字符串DATE,和DATETIME列。例如,以下每个CREATE TABLE语句均有效:

      CREATE TABLE rc (c1 INT, c2 DATE)
      PARTITION BY RANGE COLUMNS(c2) (
          PARTITION p0 VALUES LESS THAN('1990-01-01'),
          PARTITION p1 VALUES LESS THAN('1995-01-01'),
          PARTITION p2 VALUES LESS THAN('2000-01-01'),
          PARTITION p3 VALUES LESS THAN('2005-01-01'),
          PARTITION p4 VALUES LESS THAN(MAXVALUE)
      );
      
      CREATE TABLE lc (c1 INT, c2 CHAR(1))
      PARTITION BY LIST COLUMNS(c2) (
          PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
          PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
          PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
      );
      

    前述任何例外均不适用于BLOBTEXT列类型。

    子查询。分区键可能不是子查询,即使该子查询解析为整数值或NULL

    子分区问题。子分区必须使用HASHKEY分区。只有RANGELIST分区可以再分区;HASH并且KEY分区不能再分区。

    SUBPARTITION BY KEY要求显式指定一个或多个子分区列,与的情况不同PARTITION BY KEY,可以将其省略(在这种情况下,默认情况下使用表的主键列)。考虑以下语句创建的表:

    CREATE TABLE ts (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(30)
    );
    

    您可以KEY使用如下语句创建具有相同列的表,并按进行分区:

    CREATE TABLE ts (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(30)
    )
    PARTITION BY KEY()
    PARTITIONS 4;
    

    前面的语句就好像是这样写的,表的主键列用作分区列:

    CREATE TABLE ts (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(30)
    )
    PARTITION BY KEY(id)
    PARTITIONS 4;
    

    但是,以下尝试使用默认列作为子分区列创建子分区表的语句失败,并且必须指定该列才能使语句成功,如下所示:

    mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY()
    -> SUBPARTITIONS 4
    -> (
    ->     PARTITION p0 VALUES LESS THAN (100),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use near ')
    
    mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY(id)
    -> SUBPARTITIONS 4
    -> (
    ->     PARTITION p0 VALUES LESS THAN (100),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
    Query OK, 0 rows affected (0.07 sec)
    

    这是一个已知问题(请参见Bug#51470)。

    DATA DIRECTORY和INDEX DIRECTORY选项。表级DATA DIRECTORYINDEX DIRECTORY选项将被忽略(请参见Bug#32091)。您可以将这些选项用于InnoDB表的单个分区或子分区。

    修复和重建分区表。该声明CHECK TABLEOPTIMIZE TABLEANALYZE TABLE,并且REPAIR TABLE都支持分区表。

    另外,您可以ALTER TABLE ... REBUILD PARTITION用来重建分区表的一个或多个分区。ALTER TABLE ... REORGANIZE PARTITION还导致分区被重建。有关这两个语句的更多信息,请参见“ ALTER TABLE语句”。

    ANALYZECHECKOPTIMIZEREPAIR,和TRUNCATE操作与子分区支持。请参见“ ALTER TABLE分区操作”。

    分区和子分区的文件名定界符。表分区和子分区文件名包含生成的定界符,例如#P##SP#。此类分隔符的字母大小写可以变化,因此不应依赖。