PolarDB:CREATE TABLE (AUTO モード)

パーティションテーブル

CREATE TABLE key_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY KEY(name, id)
PARTITIONS 8;

## このクエリはパーティションプルーニングをトリガーし、単一のパーティションのみをスキャンします。
SELECT id from key_tbl where name='Jack';

CREATE TABLE hash_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
partition by hash(id)
partitions 8;

CREATE TABLE hash_tbl_todays(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY HASH(TO_DAYS(birthday))
PARTITIONS 8;

CREATE TABLE hash_tbl2(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 birthday datetime not null,
 primary key(id)
)
PARTITION BY HASH(name, birthday)
PARTITIONS 8;

##SQL1 (このクエリはパーティションプルーニングをトリガーし、単一のパーティションのみをスキャンします)
SELECT id from hash_tbl2 where name='Jack' and birthday='1990-11-11';

##SQL2 (このクエリはパーティションプルーニングをトリガーせず、フルパーティションスキャンになります)
SELECT id from hash_tbl2 where name='Jack';

CREATE TABLE orders(
 order_id int,
 order_time datetime not null)
PARTITION BY RANGE COLUMNS(order_id,order_time)
(
  PARTITION p1 VALUES LESS THAN (10000,'2021-01-01'),
  PARTITION p2 VALUES LESS THAN (20000,'2021-01-01'),
  PARTITION p3 VALUES LESS THAN (30000,'2021-01-01'),
  PARTITION p4 VALUES LESS THAN (40000,'2021-01-01'),
  PARTITION p5 VALUES LESS THAN (50000,'2021-01-01'),
  PARTITION p6 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

CREATE TABLE orders_todays(
 id int,
 order_time datetime not null)
PARTITION BY RANGE(to_days(order_time))
(
  PARTITION p1 VALUES LESS THAN (to_days('2021-01-01')),
  PARTITION p2 VALUES LESS THAN (to_days('2021-04-01')),
  PARTITION p3 VALUES LESS THAN (to_days('2021-07-01')),
  PARTITION p4 VALUES LESS THAN (to_days('2021-10-01')),
  PARTITION p5 VALUES LESS THAN (to_days('2022-01-01')),
  PARTITION p6 VALUES LESS THAN (MAXVALUE)
);

CREATE TABLE orders_region(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Hangzhou'), ('China','Beijing')),
  PARTITION p2 VALUES IN (('United States','New York'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russia','Moscow'))
);

CREATE TABLE orders_years(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019)
);

CREATE TABLE orders_region(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Hangzhou'), ('China','Beijing')),
  PARTITION p2 VALUES IN (('United States','New York'),('United States','Chicago')),
  PARTITION p3 VALUES IN (('Russia','Moscow')),
  PARTITION pd VALUES IN (DEFAULT)
);

CREATE TABLE orders_years(
 id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST(YEAR(order_time))
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019),
  PARTITION pd VALUES IN (DEFAULT)
);

CREATE TABLE t_orders(
 id bigint not null auto_increment, 
 order_id bigint, 
 buyer_id bigint,
 order_time datetime not null,
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT(`order_id`,6) /* order_id の最後の 6 文字 */,
 	RIGHT(`buyer_id`,6) /* buyer_id の最後の 6 文字 */
) 
PARTITIONS 8;

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY RANGE_HASH(order_id, buyer_Id,6) 
PARTITIONS 8;

CREATE TABLE orders(
 id bigint not null auto_increment, 
 buyer_id bigint,
 order_id bigint,
 ...
 primary key(id)
) 
PARTITION BY CO_HASH(
 	RIGHT(`order_id`,6) /* order_id の最後の 6 文字を使用 */,
 	RIGHT(`buyer_id`,6) /* buyer_id の最後の 6 文字を使用 */
) 
PARTITIONS 8;

/*
 * この例では、LIST-KEY 複合戦略を使用してテンプレート化されたサブパーティションを定義します。
 * テーブルはまず LIST COLUMNS によって 3 つのパーティションに分割されます。
 * 各パーティションは次に KEY によって 4 つのサブパーティションにサブパーティション化され、合計 12 の物理パーティションになります。
*/
CREATE TABLE sp_tbl_list_key_tp(
 id int, 
 country varchar(64),
 city varchar(64),
 order_time datetime not null,
 PRIMARY KEY(id)
)
PARTITION BY LIST COLUMNS(country,city) 
SUBPARTITION BY KEY(id) SUBPARTITIONS 4
(
  PARTITION p1 VALUES IN (('China','Hangzhou')),
  PARTITION p2 VALUES IN (('Russian','Moscow')),
  PARTITION pd VALUES IN (DEFAULT)
);

/*
 * この例では、LIST-KEY 複合戦略を使用して非テンプレート化されたサブパーティションを定義します。
 * テーブルは LIST COLUMNS によって 3 つの第 1 レベルのパーティションに分割され、それぞれが KEY によってサブパーティション化されます。
 * 第 1 レベルのパーティションには、それぞれ 2、3、4 つのサブパーティションがあります。
 * これにより、合計 9 つの物理パーティション (2 + 3 + 4) になります。
*/
CREATE TABLE sp_tbl_list_key_ntp(
 id int, 
 country varchar(64),
 city varchar(64),
 order_time datetime not null,
 PRIMARY KEY(id)
)
PARTITION BY LIST COLUMNS(country,city) 
SUBPARTITION BY KEY(id)
(
  PARTITION p1 VALUES IN (('China','Hangzhou')) SUBPARTITIONS 2,
  PARTITION p2 VALUES IN (('Russia','Moscow')) SUBPARTITIONS 3,
  PARTITION pd VALUES IN (DEFAULT) SUBPARTITIONS 4
);

自動パーティショニング

SET GLOBAL AUTO_PARTITION=true;

自動パーティショニングを有効にすると：

• パーティショニングキーを指定せずにテーブルを作成すると、PolarDB-X はデフォルトでそのプライマリキーを使用してテーブルをパーティショニングします。テーブルにプライマリキーがない場合は、代わりに暗黙の主キーを使用します。このプロセスでは KEY パーティショニングが使用され、レベル 1 のパーティションテーブルが作成されます。

  デフォルトでは、パーティションの数はインスタンス内の論理ノードの数の 8 倍です。たとえば、PolarDB-X インスタンスが 2 つの論理ノードで作成された場合、デフォルトのパーティション数は 16 です。

• プライマリテーブルのパーティショニングに加えて、PolarDB-X はデフォルトですべてのインデックスもパーティショニングします。インデックスのパーティショニングキーは、インデックス列とテーブルのプライマリキー列で構成されます。

次の例は、テーブルを作成するための標準的な MySQL 構文を示しています。ここで、id はプライマリキー、name はインデックス付き列です：

CREATE TABLE auto_part_tbl(
 id bigint not null auto_increment,
 bid int,
 name varchar(30),
 primary key(id),
 index idx_name (name)
);

SHOW CREATE TABLE ステートメントを実行すると、標準の MySQL テーブル作成構文が表示され、すべてのパーティション情報が自動的に非表示になります：

show create table auto_part_tbl;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE         | CREATE TABLE                                                                                                                                                                                                                     |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_part_tbl | CREATE TABLE `auto_part_tbl` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `bid` int(11) DEFAULT NULL,
    `name` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_name` (`name`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

SHOW FULL CREATE TABLE ステートメントを実行すると、メインテーブルとそのインデックステーブルのすべてのパーティション情報が表示されます：

show full create table auto_part_tbl;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE         | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                              |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_part_tbl | CREATE PARTITION TABLE `auto_part_tbl` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `bid` int(11) DEFAULT NULL,
    `name` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`id`),
    GLOBAL INDEX /* idx_name_$a870 */ `idx_name` (`name`) PARTITION BY KEY (`name`, `id`) PARTITIONS 16,
    LOCAL KEY `_local_idx_name` (`name`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`id`)
PARTITIONS 16
/* table group = `tg108` */ |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

手動パーティショニング

CREATE TABLE ステートメントでパーティション列、パーティション関数、およびパーティションタイプを指定することで、手動でパーティション化されたテーブルを作成できます。パーティションタイプの詳細については、「パーティションテーブルを手動で作成する (AUTO モード)」をご参照ください。

データ型

表 4. パーティショニングタイプ別のパーティションキー列でサポートされるデータ型

パーティションキーのデータ型とルーティング

パーティションテーブルのルーティングは、パーティションキーのデータ型に直接依存します。特に KEY パーティションとハッシュパーティションで顕著です。データ型が異なると、使用されるハッシュアルゴリズムや比較ロジック (大文字と小文字の区別など) が異なり、ルーティングの動作も異なります。(注：MySQL のパーティションルーティングも型に強く依存します。)

この例では、tbl_int と tbl_bigint の 2 つのテーブルを示します。どちらも 1,024 のパーティションを持ちます。tbl_int テーブルは int パーティションキーを使用し、tbl_bigint は bigint パーティションキーを使用します。どちらも整数型ですが、この違いにより、同じクエリ値 (12345678) が異なるパーティションにルーティングされます：

show create table tbl_int;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_int | CREATE TABLE `tbl_int` (
    `a` int(11) NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 1024 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

show create table tbl_bigint;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE      | CREATE TABLE                                                                                                                                                                      |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_bigint | CREATE TABLE `tbl_bigint` (
    `a` bigint(20) NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 1024 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)mysql> create table if not exists tbl_bigint(a bigint not null)
    -> partition by key(a) partitions 1024;
Query OK, 0 rows affected (28.41 sec)

explain select * from tbl_int where a=12345678;
+---------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                             |
+---------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_int[p260]", sql="SELECT `a` FROM `tbl_int` AS `tbl_int` WHERE (`a` = ?)") |
| HitCache:false                                                                                    |
| Source:PLAN_CACHE                                                                                 |
| TemplateId: c90af636                                                                              |
+---------------------------------------------------------------------------------------------------+
4 rows in set (0.45 sec)

explain select * from tbl_bigint where a=12345678;
+------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                      |
+------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_bigint[p477]", sql="SELECT `a` FROM `tbl_bigint` AS `tbl_bigint` WHERE (`a` = ?)") |
| HitCache:false                                                                                             |
| Source:PLAN_CACHE                                                                                          |
| TemplateId: 9b2fa47c                                                                                       |
+------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)
            

大文字と小文字の区別、文字セット、照合順序

例 1

show create table tbl_varchar_cs;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE          | CREATE TABLE                                                                                                                                                                                                          |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_varchar_cs | CREATE TABLE `tbl_varchar_cs` (
    `a` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`a`)
PARTITIONS 64 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

explain select a from tbl_varchar_cs where a in ('AbcD');
+-------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_cs[p29]", sql="SELECT `a` FROM `tbl_varchar_cs` AS `tbl_varchar_cs` WHERE (`a` IN(?))") |
| HitCache:false                                                                                                          |
| Source:PLAN_CACHE                                                                                                       |
| TemplateId: 2c49c244                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.11 sec)

explain select a from tbl_varchar_cs where a in ('abcd');
+-------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_cs[p11]", sql="SELECT `a` FROM `tbl_varchar_cs` AS `tbl_varchar_cs` WHERE (`a` IN(?))") |
| HitCache:true                                                                                                           |
| Source:PLAN_CACHE                                                                                                       |
| TemplateId: 2c49c244                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)

例 2

show create table tbl_varchar_ci;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE          | CREATE TABLE                                                                                                                                                                      |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_varchar_ci | CREATE TABLE `tbl_varchar_ci` (
    `a` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`a`)
PARTITIONS 64 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

explain select a from tbl_varchar_ci where a in ('AbcD');
+------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_ci[p4]", sql="SELECT `a` FROM `tbl_varchar_ci` AS `tbl_varchar_ci` WHERE (`a` IN(?))") |
| HitCache:false                                                                                                         |
| Source:PLAN_CACHE                                                                                                      |
| TemplateId: 5c97178e                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.15 sec)

explain select a from tbl_varchar_ci where a in ('abcd');
+------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_varchar_ci[p4]", sql="SELECT `a` FROM `tbl_varchar_ci` AS `tbl_varchar_ci` WHERE (`a` IN(?))") |
| HitCache:true                                                                                                          |
| Source:PLAN_CACHE                                                                                                      |
| TemplateId: 5c97178e                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)

文字セットと照合順序の変更

パーティションテーブルのルーティングアルゴリズムは、そのパーティションキーのデータ型によって決定されるため、パーティションキーの文字セットや照合順序を変更すると、テーブル内のすべてのデータの再配布がトリガーされます。したがって、パーティションキーのデータ型を変更する際には注意が必要です。

パーティション列の型の切り捨てと変換

パーティション列の型の切り捨て

クエリや INSERT ステートメントの定数式がパーティション列のデータ型の有効範囲を超えた場合、PolarDB-X はまず値を切り捨ててから、ルーティング計算に使用します。

たとえば、tbl_smallint テーブルには smallint 型のパーティション列があり、その有効範囲は -32768 から 32767 です。この範囲外の値、たとえば 12345678 や -12345678 を挿入しようとすると、PolarDB-X はまず値を smallint 型の最大値または最小値 (それぞれ 32767 または -32768) に切り捨てます。次の例はこのプロセスを示しています。

show create table tbl_smallint;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE        | CREATE TABLE                                                                                                                                                                        |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_smallint | CREATE TABLE `tbl_smallint` (
    `a` smallint(6) NOT NULL,
    KEY `auto_shard_key_a` USING BTREE (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 128 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

insert into tbl_smallint values (12345678),(-12345678);
Query OK, 2 rows affected (0.07 sec)

select * from tbl_smallint;
+--------+
| a      |
+--------+
| -32768 |
|  32767 |
+--------+
2 rows in set (3.51 sec)

explain select * from tbl_smallint where a=12345678;
+------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                            |
+------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_smallint[p117]", sql="SELECT `a` FROM `tbl_smallint` AS `tbl_smallint` WHERE (`a` = ?)") |
| HitCache:false                                                                                                   |
| Source:PLAN_CACHE                                                                                                |
| TemplateId: afb464d5                                                                                             |
+------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.16 sec)

explain select * from tbl_smallint where a=32767;
+------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                            |
+------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="tbl_smallint[p117]", sql="SELECT `a` FROM `tbl_smallint` AS `tbl_smallint` WHERE (`a` = ?)") |
| HitCache:true                                                                                                    |
| Source:PLAN_CACHE                                                                                                |
| TemplateId: afb464d5                                                                                             |
+------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.03 sec)

同様に、クエリ内の定数値が型の範囲を超えた場合、PolarDB-X はルーティング前にそれを切り捨てます。したがって、tbl_smallint テーブルの場合、PolarDB-X は a=12345678 と a=32767 のクエリを同じパーティションにルーティングします。

パーティション列の型変換

クエリや INSERT ステートメントの定数式がパーティション列と異なるデータ型を持つ場合、PolarDB-X は定数式に対して暗黙の型変換を行い、変換された値をルーティング計算に使用します。ただし、型変換は失敗することがあります。たとえば、文字列 abc は整数に変換できません。

パーティション列の型変換が発生または失敗した場合、PolarDB-X はステートメントが DQL、DML、または DDL ステートメントであるかによって動作が異なります：

• DQL (特に WHERE 句のパーティション列に関わる型変換)

  • 変換成功：PolarDB-X は変換された値をパーティションルーティングに使用します。

  • 変換失敗：PolarDB-X はパーティション列の条件を無視し、フルテーブルスキャンになります。

• DML (特に INSERT または REPLACE ステートメント)

  • 変換成功：PolarDB-X は変換された値をパーティションルーティングに使用します。

  • 変換失敗：PolarDB-X はステートメントを拒否し、エラーを返します。

• DDL (特にパーティションテーブルに関連する DDL ステートメント、たとえば CREATE TABLE や SPLIT PARTITION)

  • 変換成功：PolarDB-X はステートメントを拒否し、エラーを返します。DDL ステートメントは暗黙の型変換を許可しません。

  • 変換失敗：PolarDB-X はステートメントを拒否し、エラーを返します。

MySQL パーティションテーブルとの構文の違い