• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 列分区

    接下来的两节讨论COLUMNS partitioning,它们是on RANGELIST partitioning的变体。COLUMNS通过分区,可以在分区键中使用多个列。出于将行放置在分区中以及确定要在分区修剪中检查要检查哪些分区的行匹配的目的,都考虑了所有这些列。

    此外,RANGE COLUMNS分区和LIST COLUMNS分区都支持使用非整数列来定义值范围或列表成员。允许的数据类型显示在以下列表中:

    • 所有整数类型:TINYINTSMALLINTMEDIUMINTINTINTEGER),和BIGINT。(这与通过RANGE和进行分区相同LIST。)

      其他数字数据类型(例如DECIMALFLOAT)不支持作为分区列。

    • DATEDATETIME

      不支持使用与日期或时间相关的其他数据类型的列作为分区列。

    • 下面的字符串类型:CHARVARCHARBINARY,和VARBINARY

      TEXTBLOB不支持和列作为分区列。

    下两节中的讨论RANGE COLUMNSLIST COLUMNS分区假定您已经熟悉MySQL 5.1及更高版本所支持的基于范围和列表的分区。有关这些的更多信息,请分别参见“ RANGE分区”和“ LIST分区”。


    范围列分区

    范围列分区类似于范围分区,但是使您能够基于多个列值使用范围来定义分区。另外,您可以使用非整数类型的列来定义范围。

    RANGE COLUMNS分区RANGE在以下方面与分区显着不同:

    • RANGE COLUMNS不接受表达式,仅接受列名。
    • RANGE COLUMNS接受一个或多个列的列表。

      RANGE COLUMNS分区基于元组(列值列表)之间的比较,而不是标量值之间的比较。行在RANGE COLUMNS分区中的放置也基于元组之间的比较;本节稍后将对此进行讨论。

    • RANGE COLUMNS分区列不限于整数列;字符串,DATE并且DATETIME列也可以用作分区列。(有关详细信息,)

    创建分区的表的基本语法RANGE COLUMNS如下所示:

    CREATE TABLE table_name
    PARTITIONED BY RANGE COLUMNS(column_list) (
        PARTITION partition_name VALUES LESS THAN (value_list)[,
        PARTITION partition_name VALUES LESS THAN (value_list)][,
        ...]
    )
    
    column_list:
        column_name[, column_name][, ...]
    
    value_list:
        value[, value][, ...]
    
    注意

    CREATE TABLE此处未显示创建分区表时可以使用的所有选项。有关完整信息,请参见“ CREATE TABLE语句”。

    在刚刚显示的语法中,column_list是一个或多个列的列表(有时称为分区列list),并且value_list是值的列表(即,它是分区定义值列表)。value_list必须为每个分区定义提供A ,并且每个分区value_list必须具有与column_list has列相同数量的值。一般来说,如果NCOLUMNS子句中使用列,则每个VALUES LESS THAN子句还必须提供N值列表。

    分区列列表和值列表中定义每个分区的元素必须以相同的顺序出现。此外,值列表中的每个元素必须与列列表中的相应元素具有相同的数据类型。但是,分区列列表和值列表中列名称的顺序不必与CREATE TABLE语句主要部分中表列定义的顺序相同。与使用分区表一样RANGE,您可以使用MAXVALUE来表示一个值,以使插入给定列的任何合法值始终小于该值。这是一个例子CREATE TABLE有助于说明所有这些要点的陈述:

    mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
    Query OK, 0 rows affected (0.15 sec)
    

    rcx包含列abcd。供给至分区列列表COLUMNS子句使用这些列3中,在顺序adc。每个用于定义分区的值列表均包含3个值,其顺序相同。即,每个值列表元组的形式为(INTINTCHAR(3)),其对应于数据类型由列中使用adc(按该顺序)。

    通过将要插入的行中与COLUMNS子句中的列列表匹配的元组与VALUES LESS THAN子句中用于定义表分区的元组进行比较,可以确定将行放置在分区中。因为我们是在比较元组(即值的列表或值集)而不是标量值,所以VALUES LESS THANRANGE COLUMNS分区一起使用的as 的语义与简单RANGE分区的情况有所不同。在RANGE分区中,VALUES LESS THAN永远不会将产生表达式值等于a中的极限值的行放置在相应的分区中;但是,当使用RANGE COLUMNS分区时,有时可能会将分区列列表的第一个元素的值与值列表中的第一个元素的值相等的行VALUES LESS THAN放在相应的分区中。

    考虑RANGE以下语句创建的分区表:

    CREATE TABLE r1 (
        a INT,
        b INT
    )
    PARTITION BY RANGE (a)  (
        PARTITION p0 VALUES LESS THAN (5),
        PARTITION p1 VALUES LESS THAN (MAXVALUE)
    );
    

    如果我们插入3行到该表,使得该列值a5针对每行,所有3行存储在分区p1,因为a列值在每一种情况下不小于5,因为我们可以通过执行针对适当的查询看INFORMATION_SCHEMA.PARTITIONS表:

    mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'r1';
    +----------------	+------------	+
    | PARTITION_NAME	| TABLE_ROWS	|
    +----------------	+------------	+
    | p0	|          0	|
    | p1	|          3	|
    +----------------	+------------	+
    2 rows in set (0.00 sec)
    

    现在考虑一个类似的表rc1,该表使用RANGE COLUMNS分区和列进行分区,abCOLUMNS子句中引用,如下所示:

    CREATE TABLE rc1 (
        a INT,
        b INT
    )
    PARTITION BY RANGE COLUMNS(a, b) (
        PARTITION p0 VALUES LESS THAN (5, 12),
        PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
    );
    

    如果将与插入时完全相同的行rc1插入到r1,则行的分布将完全不同:

    mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rc1';
    +--------------	+----------------	+------------	+
    | TABLE_SCHEMA	| PARTITION_NAME	| TABLE_ROWS	|
    +--------------	+----------------	+------------	+
    | p	| p0	|          2	|
    | p	| p1	|          1	|
    +--------------	+----------------	+------------	+
    2 rows in set (0.00 sec)
    

    这是因为我们正在比较行而不是标量值。我们可以将插入的行值与VALUES THAN LESS THAN用于定义p0table中的分区的子句中的限制行值进行比较rc1,如下所示:

    mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
    +-----------------	+-----------------	+-----------------	+
    | (5,10) < (5,12)	| (5,11) < (5,12)	| (5,12) < (5,12)	|
    +-----------------	+-----------------	+-----------------	+
    |               1	|               1	|               0	|
    +-----------------	+-----------------	+-----------------	+
    1 row in set (0.00 sec)
    

    所述2元组(5,10)(5,11)评估为小于(5,12),因此它们存储在分区p0。由于5不小于5和12不小于12,因此(5,12)被视为不小于(5,12),并将其存储在partition中p1

    SELECT前面示例中的语句也可以使用显式行构造函数编写,如下所示:

    SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);
    

    有关在MySQL中使用行构造器的更多信息,请参见“ROW子查询”。

    对于RANGE COLUMNS仅使用单个分区列进行分区的表,分区中行的存储与由进行分区的等效表的存储相同RANGE。以下CREATE TABLE语句创建一个RANGE COLUMNS使用1个partitioning列进行分区的表:

    CREATE TABLE rx (
        a INT,
        b INT
    )
    PARTITION BY RANGE COLUMNS (a)  (
        PARTITION p0 VALUES LESS THAN (5),
        PARTITION p1 VALUES LESS THAN (MAXVALUE)
    );
    

    如果将行(5,10)(5,11)(5,12)插入到此表中,我们可以看到它们的位置与r我们先前创建并填充的表的位置相同:

    mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rx';
    +--------------	+----------------	+------------	+
    | TABLE_SCHEMA	| PARTITION_NAME	| TABLE_ROWS	|
    +--------------	+----------------	+------------	+
    | p	| p0	|          0	|
    | p	| p1	|          3	|
    +--------------	+----------------	+------------	+
    2 rows in set (0.00 sec)
    

    也可以创建按以下分区的表:RANGE COLUMNS在连续的分区定义中,重复一或多个列的限制值。只要用于定义分区的列值的元组严格增加,就可以执行此操作。例如,以下每个CREATE TABLE语句均有效:

    CREATE TABLE rc2 (
        a INT,
        b INT
    )
    PARTITION BY RANGE COLUMNS(a,b) (
        PARTITION p0 VALUES LESS THAN (0,10),
        PARTITION p1 VALUES LESS THAN (10,20),
        PARTITION p2 VALUES LESS THAN (10,30),
        PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
     );
    
    CREATE TABLE rc3 (
        a INT,
        b INT
    )
    PARTITION BY RANGE COLUMNS(a,b) (
        PARTITION p0 VALUES LESS THAN (0,10),
        PARTITION p1 VALUES LESS THAN (10,20),
        PARTITION p2 VALUES LESS THAN (10,30),
        PARTITION p3 VALUES LESS THAN (10,35),
        PARTITION p4 VALUES LESS THAN (20,40),
        PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
     );
    

    以下语句也可以成功执行,即使乍看起来可能不会成功,因为column的极限值b对于partition来说是25,对partition来说是p020 p1,并且column的极限值c对于partition来说是100,对于partition 的极限是p150 p2

    CREATE TABLE rc4 (
        a INT,
        b INT,
        c INT
    )
    PARTITION BY RANGE COLUMNS(a,b,c) (
        PARTITION p0 VALUES LESS THAN (0,25,50),
        PARTITION p1 VALUES LESS THAN (10,20,100),
        PARTITION p2 VALUES LESS THAN (10,30,50)
        PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
     );
    

    设计由分区的表时RANGE COLUMNS,您始终可以通过使用mysql客户端比较所需的元组来测试连续的分区定义,如下所示:

    mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
    +-------------------------	+--------------------------	+
    | (0,25,50) < (10,20,100)	| (10,20,100) < (10,30,50)	|
    +-------------------------	+--------------------------	+
    |                       1	|                        1	|
    +-------------------------	+--------------------------	+
    1 row in set (0.00 sec)
    

    如果一条CREATE TABLE语句包含的分区定义不是严格按升序排列的,则它将失败并显示错误,如以下示例所示:

    mysql> CREATE TABLE rcf (
    ->     a INT,
    ->     b INT,
    ->     c INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,b,c) (
    ->     PARTITION p0 VALUES LESS THAN (0,25,50),
    ->     PARTITION p1 VALUES LESS THAN (20,20,100),
    ->     PARTITION p2 VALUES LESS THAN (10,30,50),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    ->  );
    ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
    

    当您收到此类错误时,可以通过在列列表之间进行“小于”比较来推断哪些分区定义无效。在这种情况下,问题在于分区的定义,p2因为用于定义它的元组不少于用于定义partition的元组p3,如下所示:

    mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);
    +-------------------------	+--------------------------	+
    | (0,25,50) < (20,20,100)	| (20,20,100) < (10,30,50)	|
    +-------------------------	+--------------------------	+
    |                       1	|                        0	|
    +-------------------------	+--------------------------	+
    1 row in set (0.00 sec)
    

    使用时,也可能MAXVALUE在多个VALUES LESS THAN子句中出现在同一列中RANGE COLUMNS。但是,否则,连续分区定义中各个列的限制值应增加,不应定义一个以上的分区,在哪里MAXVALUE用作所有列值的上限,并且该分区定义应出现在PARTITION ... VALUES LESS THAN子句列表的最后。此外,不能将MAXVALUE多个分区定义中的第一列用作极限值。

    如前所述,对于RANGE COLUMNS分区,也可以使用非整数列作为分区列。(有关这些文件的完整列表,)考虑一个employees使用以下语句创建的名为(未分区)的表:

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

    使用RANGE COLUMNS分区,您可以创建此表的版本,该版本根据员工的姓氏将每行存储在四个分区之一中,如下所示:

    CREATE TABLE employees_by_lname (
        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 COLUMNS (lname)  (
        PARTITION p0 VALUES LESS THAN ('g'),
        PARTITION p1 VALUES LESS THAN ('m'),
        PARTITION p2 VALUES LESS THAN ('t'),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );
    

    另外,您可以employees通过执行以下ALTER TABLE语句,使使用此方案对先前创建的表进行分区:

    ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname)  (
        PARTITION p0 VALUES LESS THAN ('g'),
        PARTITION p1 VALUES LESS THAN ('m'),
        PARTITION p2 VALUES LESS THAN ('t'),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );
    
    注意

    因为不同的字符集和排序规则具有不同的排序顺序,所以RANGE COLUMNS在使用字符串列作为分区列时,使用的字符集和排序规则可能会影响按给定行划分的表的哪个分区存储在其中。此外,在创建此类表后更改给定数据库,表或列的字符集或排序规则,可能会导致行的分配方式发生变化。例如,当使用区分大小写的排序规则时,'and'在之前排序'Andersen',但是当使用不区分大小写的排序规则时,则相反。

    有关MySQL如何处理字符集和排序规则的信息,请参见字符集,排序规则,Unicode

    同样,您可以employees使用以下ALTER TABLE所示的语句来对表进行分区,即根据雇用相应员工的十年,将每一行存储在几个分区之一中:

    ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired)  (
        PARTITION p0 VALUES LESS THAN ('1970-01-01'),
        PARTITION p1 VALUES LESS THAN ('1980-01-01'),
        PARTITION p2 VALUES LESS THAN ('1990-01-01'),
        PARTITION p3 VALUES LESS THAN ('2000-01-01'),
        PARTITION p4 VALUES LESS THAN ('2010-01-01'),
        PARTITION p5 VALUES LESS THAN (MAXVALUE)
    );
    

    有关语法的更多信息,请参见“ CREATE TABLE语句”PARTITION BY RANGE COLUMNS

    列表列分区

    MySQL 8.0提供了对LIST COLUMNS分区的支持。这是LIST分区的一种变体,它允许将多个列用作分区键,并且可以将整数类型以外的数据类型的列用作分区列;您可以使用字符串类型DATE,和DATETIME列。(有关COLUMNS分区列允许的数据类型的更多信息,)

    假设您有一家在12个城市拥有客户的企业,出于销售和营销目的,您将组织成3个城市的4个区域,如下表所示:

    区域城市
    1个Oskarshamn,Högsby,门斯特罗斯
    2威默比(Vimmerby)
    3Nässjö,Eksjö,维特兰达
    4Uppvidinge,阿尔韦斯塔,韦克舍

    使用LIST COLUMNS分区,您可以为客户数据创建一个表,该表根据客户所居住城市的名称将行分配给与这些区域相对应的4个分区中的任何一个,如下所示:

    CREATE TABLE customers_1 (
        first_name VARCHAR(25),
        last_name VARCHAR(25),
        street_1 VARCHAR(30),
        street_2 VARCHAR(30),
        city VARCHAR(15),
        renewal DATE
    )
    PARTITION BY LIST COLUMNS(city) (
        PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
        PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
        PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
        PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
    );
    

    与by分区一样RANGE COLUMNS,您无需在COLUMNS()子句中使用表达式将列值转换为整数。(实际上,不允许使用除列名之外的其他表达式COLUMNS()。)

    也可以使用DATEDATETIME列,如以下示例中所示,该示例使用与customers_1先前显示的表相同的名称和列,但LIST COLUMNS根据renewal列采用分区,以将行存储在4个分区之一中,具体取决于2010年2月的星期客户的帐户计划续订:

    CREATE TABLE customers_2 (
        first_name VARCHAR(25),
        last_name VARCHAR(25),
        street_1 VARCHAR(30),
        street_2 VARCHAR(30),
        city VARCHAR(15),
        renewal DATE
    )
    PARTITION BY LIST COLUMNS(renewal) (
        PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
            '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
        PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
            '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
        PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
            '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
        PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
            '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
    );
    

    这是可行的,但是如果涉及的日期数量增加很多,则定义和维护变得很麻烦;在这种情况下,采用RANGERANGE COLUMNS分区通常更为实用。在这种情况下,由于我们希望用作分区键的DATE列是一列,因此我们使用RANGE COLUMNS分区,如下所示:

    CREATE TABLE customers_3 (
        first_name VARCHAR(25),
        last_name VARCHAR(25),
        street_1 VARCHAR(30),
        street_2 VARCHAR(30),
        city VARCHAR(15),
        renewal DATE
    )
    PARTITION BY RANGE COLUMNS(renewal) (
        PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),
        PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),
        PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),
        PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')
    );
    

    有关更多信息,请参见“ RANGE COLUMNS分区”。

    另外(与RANGE COLUMNS分区一样),您可以在COLUMNS()子句中使用多个列。

    有关语法的更多信息,请参见“ CREATE TABLE语句”PARTITION BY LIST COLUMNS()


    上篇:列表分区

    下篇:HASH分区