重要
このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

パーティショニング

このドキュメントでは、TiDBによるパーティショニングの実装を紹介します。

パーティショニングタイプ

このセクションでは、TiDBのパーティショニングのタイプを紹介します。現在、 List パーティショニング範囲分割 、およびList COLUMNS パーティショニングをサポートしていハッシュ分割

範囲パーティショニング、List パーティショニング、およびList COLUMNS パーティショニングは、アプリケーションでの大量の削除によって引き起こされるパフォーマンスの問題を解決し、高速ドロップパーティション操作をサポートするために使用されます。ハッシュパーティショニングは、大量の書き込みがある場合にデータを分散するために使用されます。

範囲分割

テーブルがRangeでパーティション化されている場合、各パーティションには、パーティション化式の値が指定されたRange内にある行が含まれます。範囲は連続している必要がありますが、重複してはなりません。 VALUES LESS THANを使用して定義できます。

次のように、人事レコードを含むテーブルを作成する必要があると想定します。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
);

必要に応じて、さまざまな方法で範囲ごとにテーブルを分割できます。たとえば、 store_id列を使用してパーティション化できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

このパーティションスキームでは、 store_idが1から5の従業員に対応するすべての行がp0パーティションに格納され、 store_idが6から10のすべての従業員がp1に格納されます。範囲分割では、パーティションを最低から最高の順に並べる必要があります。

データ(72, 'Tom', 'John', '2015-06-25', NULL, NULL, 15)の行を挿入すると、その行はp2パーティションに分類されます。ただし、 store_idが20より大きいレコードを挿入すると、TiDBはこのレコードを挿入するパーティションを認識できないため、エラーが報告されます。この場合、テーブルを作成するときにMAXVALUEを使用できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUEは、他のすべての整数値よりも大きい整数値を表します。これで、 store_idが16(定義された最大値)以上のすべてのレコードがp3パーティションに格納されます。

job_code列の値である従業員のジョブコードでテーブルを分割することもできます。 2桁のジョブコードは正社員を表し、3桁のコードはオフィスおよびカスタマーサポート担当者を表し、4桁のコードは管理職を表すと想定します。次に、次のようなパーティションテーブルを作成できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

この例では、正社員に関連するすべての行がp0パーティションに格納され、すべてのオフィスおよびカスタマーサポート担当者がp1パーティションに格納され、すべての管理担当者がp2パーティションに格納されます。

テーブルをstore_idで分割するだけでなく、テーブルを日付で分割することもできます。たとえば、従業員の離職年ごとに分割できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

範囲パーティショニングでは、 timestamp列の値に基づいてパーティショニングし、 unix_timestamp()関数を使用できます。次に例を示します。

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

タイムスタンプ列を含む他のパーティショニング式を使用することは許可されていません。

範囲分割は、次の1つ以上の条件が満たされる場合に特に役立ちます。

  • 古いデータを削除したい。前の例のemployeesテーブルを使用すると、 ALTER TABLE employees DROP PARTITION p0;を使用するだけで、1991年より前にこの会社を退職した従業員のすべてのレコードを削除できます。 DELETE FROM employees WHERE YEAR(separated) <= 1990;操作を実行するよりも高速です。
  • 時刻または日付の値を含む列、または他の系列から生じる値を含む列を使用するとします。
  • パーティショニングに使用される列に対して頻繁にクエリを実行する必要があります。たとえば、 EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;のようなクエリを実行すると、TiDBは、他のパーティションがWHEREの条件に一致しないため、 p2のパーティションのデータのみをスキャンする必要があることをすばやく知ることができます。

List パーティショニング

Listパーティションテーブルを作成する前に、セッション変数tidb_enable_list_partitionの値をONに設定する必要があります。

set @@session.tidb_enable_list_partition = ON

また、デフォルト設定であるtidb_enable_table_partitionONに設定されていることを確認してください。

List パーティショニングは、範囲のパーティション化に似ています。範囲パーティショニングとは異なり、List パーティショニングでは、各パーティションのすべての行のパーティショニング式の値が特定の値セットに含まれます。パーティションごとに定義されたこの値セットには、任意の数の値を含めることができますが、重複する値を含めることはできません。 PARTITION ... VALUES IN (...)句を使用して、値セットを定義できます。

人事記録テーブルを作成するとします。次のようにテーブルを作成できます。

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    store_id INT
);

次の表に示すように、4つの地区に20の店舗が分散しているとします。

| Region  | Store ID Numbers     |
| ------- | -------------------- |
| North   | 1, 2, 3, 4, 5        |
| East    | 6, 7, 8, 9, 10       |
| West    | 11, 12, 13, 14, 15   |
| Central | 16, 17, 18, 19, 20   |

同じ地域の従業員の人事データを同じパーティションに保存する場合は、 store_idに基づいてリストパーティションテーブルを作成できます。

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    store_id INT
)
PARTITION BY LIST (store_id) (
    PARTITION pNorth VALUES IN (1, 2, 3, 4, 5),
    PARTITION pEast VALUES IN (6, 7, 8, 9, 10),
    PARTITION pWest VALUES IN (11, 12, 13, 14, 15),
    PARTITION pCentral VALUES IN (16, 17, 18, 19, 20)
);

上記のようにパーティションを作成した後、テーブル内の特定の領域に関連するレコードを簡単に追加または削除できます。たとえば、東部地域(東部)のすべての店舗が別の会社に販売されているとします。次に、このリージョンのストア従業員に関連するすべての行データをALTER TABLE employees TRUNCATE PARTITION pEastを実行することで削除できます。これは、同等のステートメントDELETE FROM employees WHERE store_id IN (6, 7, 8, 9, 10)よりもはるかに効率的です。

ALTER TABLE employees DROP PARTITION pEastを実行して関連するすべての行を削除することもできますが、このステートメントはテーブル定義からpEastパーティションも削除します。この状況では、 ALTER TABLE ... ADD PARTITIONステートメントを実行して、テーブルの元のパーティションスキームを復元する必要があります。

範囲パーティショニングとは異なり、List パーティショニングには、他のパーティションに属していないすべての値を格納するための同様のMAXVALUEパーティションがありません。代わりに、パーティション式のすべての期待値をPARTITION ... VALUES IN (...)句に含める必要があります。 INSERTステートメントに挿入される値がどのパーティションの列値セットとも一致しない場合、ステートメントは実行に失敗し、エラーが報告されます。次の例を参照してください。

test> CREATE TABLE t (
    ->   a INT,
    ->   b INT
    -> )
    -> PARTITION BY LIST (a) (
    ->   PARTITION p0 VALUES IN (1, 2, 3),
    ->   PARTITION p1 VALUES IN (4, 5, 6)
    -> );
Query OK, 0 rows affected (0.11 sec)

test> INSERT INTO t VALUES (7, 7);
ERROR 1525 (HY000): Table has no partition for value 7

上記のエラータイプを無視するには、 IGNOREキーワードを使用できます。このキーワードを使用した後、どのパーティションの列値セットとも一致しない値が行に含まれている場合、この行は挿入されません。代わりに、値が一致する行が挿入され、エラーは報告されません。

test> TRUNCATE t;
Query OK, 1 row affected (0.00 sec)

test> INSERT IGNORE INTO t VALUES (1, 1), (7, 7), (8, 8), (3, 3), (5, 5);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 2

test> select * from t;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
|    1 |    1 |
|    3 |    3 |
+------+------+
3 rows in set (0.01 sec)

List COLUMNS パーティショニング

List COLUMNS パーティショニングは、 List パーティショニングの変形です。複数の列をパーティションキーとして使用できます。整数データ型に加えて、文字列、 DATE 、およびDATETIMEデータ型の列をパーティション列として使用することもできます。

次の表に示すように、次の12の都市の店舗の従業員を4つの地域に分割するとします。

| Region | Cities                         |
| :----- | ------------------------------ |
| 1      | LosAngeles,Seattle, Houston    |
| 2      | Chicago, Columbus, Boston      |
| 3      | NewYork, LongIsland, Baltimore |
| 4      | Atlanta, Raleigh, Cincinnati   |

次に示すように、 List COLUMNS パーティショニングを使用してテーブルを作成し、従業員の都市に対応するパーティションに各行を格納できます。

CREATE TABLE employees_1 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT,
    city VARCHAR(15)
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('LosAngeles', 'Seattle', 'Houston'),
    PARTITION pRegion_2 VALUES IN('Chicago', 'Columbus', 'Boston'),
    PARTITION pRegion_3 VALUES IN('NewYork', 'LongIsland', 'Baltimore'),
    PARTITION pRegion_4 VALUES IN('Atlanta', 'Raleigh', 'Cincinnati')
);

List パーティショニングとは異なり、List COLUMNS パーティショニングでは、列の値を整数に変換するためにCOLUMNS()句の式を使用する必要はありません。

次の例に示すように、List COLUMNS パーティショニングは、 DATEタイプとDATETIMEタイプの列を使用して実装することもできます。この例では、前のemployees_1のテーブルと同じ名前と列を使用していますが、 hiredの列に基づいてList COLUMNS パーティショニングを使用しています。

CREATE TABLE employees_2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT,
    city VARCHAR(15)
)
PARTITION BY LIST COLUMNS(hired) (
    PARTITION pWeek_1 VALUES IN('2020-02-01', '2020-02-02', '2020-02-03',
        '2020-02-04', '2020-02-05', '2020-02-06', '2020-02-07'),
    PARTITION pWeek_2 VALUES IN('2020-02-08', '2020-02-09', '2020-02-10',
        '2020-02-11', '2020-02-12', '2020-02-13', '2020-02-14'),
    PARTITION pWeek_3 VALUES IN('2020-02-15', '2020-02-16', '2020-02-17',
        '2020-02-18', '2020-02-19', '2020-02-20', '2020-02-21'),
    PARTITION pWeek_4 VALUES IN('2020-02-22', '2020-02-23', '2020-02-24',
        '2020-02-25', '2020-02-26', '2020-02-27', '2020-02-28')
);

さらに、 COLUMNS()句に複数の列を追加することもできます。例えば:

CREATE TABLE t (
    id int,
    name varchar(10)
)
PARTITION BY LIST COLUMNS(id,name) (
     partition p0 values IN ((1,'a'),(2,'b')),
     partition p1 values IN ((3,'c'),(4,'d')),
     partition p3 values IN ((5,'e'),(null,null))
);

ハッシュ分割

ハッシュパーティションは、データが特定の数のパーティションに均等に分散されるようにするために使用されます。範囲パーティショニングでは、範囲パーティショニングを使用する場合は各パーティションの列値の範囲を指定する必要がありますが、ハッシュパーティショニングを使用する場合はパーティションの数を指定するだけで済みます。

ハッシュによるパーティショニングでは、 CREATE TABLEステートメントにPARTITION BY HASH (expr)句を追加する必要があります。 exprは整数を返す式です。この列のタイプが整数の場合は、列名にすることができます。さらに、 PARTITIONS numを追加する必要がある場合もあります。ここで、 numは、テーブルが分割されているパーティションの数を示す正の整数です。

次の操作により、ハッシュパーティションテーブルが作成されます。このテーブルは、 store_idで4つのパーティションに分割されます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY HASH(store_id)
PARTITIONS 4;

PARTITIONS numが指定されていない場合、デフォルトのパーティション数は1です。

exprの整数を返すSQL式を使用することもできます。たとえば、雇用年ごとにテーブルを分割できます。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

最も効率的なハッシュ関数は、単一のテーブル列を操作する関数であり、その値は列の値に応じて増減します。

たとえば、 date_colはタイプがDATEの列であり、 TO_DAYS(date_col)式の値はdate_colの値によって変化します。 YEAR(date_col)TO_DAYS(date_col)とは異なります。これは、 date_colで可能なすべての変更がYEAR(date_col)で同等の変更を生成するわけではないためです。

対照的に、タイプがINTint_col列があると仮定します。ここで、式POW(5-int_col,3) + 6について考えます。ただし、 int_colの値が変化しても、式の結果は比例して変化しないため、これは適切なハッシュ関数ではありません。 int_colの値を変更すると、式の結果が大幅に変更される可能性があります。たとえば、 int_colが5から6に変わると、式の結果の変化は-1になります。ただし、 int_colが6から7に変わると、結果の変化は-7になる可能性があります。

結論として、式の形式がy = cxに近い場合は、ハッシュ関数である方が適しています。式が非線形であるほど、パーティション間でデータが不均一に分散する傾向があります。

理論的には、複数の列値を含む式に対してプルーニングも可能ですが、そのような式のどれが適切であるかを判断することは非常に困難で時間がかかる可能性があります。このため、複数の列を含むハッシュ式の使用は特に推奨されません。

PARTITION BY HASHを使用する場合、TiDBは、式の結果のモジュラスに基づいて、データがどのパーティションに分類されるかを決定します。言い換えると、パーティショニング式がexprで、パーティションの数がnumの場合、 MOD(expr, num)はデータが格納されるパーティションを決定します。 t1が次のように定義されていると仮定します。

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH( YEAR(col3) )
    PARTITIONS 4;

データの行をt1に挿入し、 col3の値が「2005-09-15」の場合、この行はパーティション1に挿入されます。

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

TiDBパーティショニングがNULLを処理する方法

TiDBでは、パーティショニング式の計算結果としてNULLを使用できます。

ノート:

NULLは整数ではありません。 TiDBのパーティショニング実装では、 ORDER BYと同様に、 NULLを他の整数値よりも小さいものとして扱います。

範囲分割によるNULLの処理

Rangeでパーティション化されたテーブルに行を挿入し、パーティションの決定に使用される列の値がNULLの場合、この行は最下位のパーティションに挿入されます。

CREATE TABLE t1 (
    c1 INT,
    c2 VARCHAR(20)
)

PARTITION BY RANGE(c1) (
    PARTITION p0 VALUES LESS THAN (0),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);
Query OK, 0 rows affected (0.09 sec)
select * from t1 partition(p0);
+------|--------+
| c1   | c2     |
+------|--------+
| NULL | mothra |
+------|--------+
1 row in set (0.00 sec)
select * from t1 partition(p1);
Empty set (0.00 sec)
select * from t1 partition(p2);
Empty set (0.00 sec)

p0つのパーティションを削除し、結果を確認します。

alter table t1 drop partition p0;
Query OK, 0 rows affected (0.08 sec)
select * from t1;
Empty set (0.00 sec)

ハッシュ分割によるNULLの処理

ハッシュでテーブルを分割する場合、 NULLの値を処理する別の方法があります。分割式の計算結果がNULLの場合、 0と見なされます。

CREATE TABLE th (
    c1 INT,
    c2 VARCHAR(20)
)

PARTITION BY HASH(c1)
PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)
INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 2 rows affected (0.04 sec)
select * from th partition (p0);
+------|--------+
| c1   | c2     |
+------|--------+
| NULL | mothra |
|    0 | gigan  |
+------|--------+
2 rows in set (0.00 sec)
select * from th partition (p1);
Empty set (0.00 sec)

挿入されたレコード(NULL, 'mothra')(0, 'gigan')と同じパーティションに分類されることがわかります。

ノート:

TiDBのハッシュパーティションによるNULL値は、 MySQLパーティショニングがNULLを処理する方法で説明したのと同じ方法で処理されますが、MySQLの実際の動作とは一致しません。言い換えると、この場合のMySQLの実装は、そのドキュメントと一致していません。

この場合、TiDBの実際の動作は、このドキュメントの説明と一致しています。

パーティション管理

LISTおよびRANGEパーティションテーブルの場合、 ALTER TABLE <table name> ADD PARTITION (<partition specification>)またはALTER TABLE <table name> DROP PARTITION <list of partitions>ステートメントを使用してパーティションを追加および削除できます。

LISTRANGEのパーティションテーブルの場合、 REORGANIZE PARTITIONはまだサポートされていません。

HASHつのパーティションテーブルの場合、 COALESCE PARTITIONADD PARTITIONはまだサポートされていません。

EXCHANGE PARTITIONは、 RENAME TABLE t1 TO t1_tmp, t2 TO t1, t1_tmp TO t2のようにテーブルの名前を変更するのと同じように、パーティションとパーティション化されていないテーブルを交換することで機能します。

たとえば、 ALTER TABLE partitioned_table EXCHANGE PARTITION p1 WITH TABLE non_partitioned_tablep1パーティションのnon_partitioned_tableテーブルをpartitioned_tableテーブルと交換します。

パーティションに交換するすべての行がパーティション定義と一致することを確認してください。そうしないと、これらの行が見つからず、予期しない問題が発生します。

警告

EXCHANGE PARTITIONは実験的機能です。実稼働環境での使用はお勧めしません。これを有効にするには、 tidb_enable_exchange_partitionシステム変数をONに設定します。

範囲パーティション管理

パーティションテーブルを作成します。

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)

PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

パーティションを削除します。

ALTER TABLE members DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

パーティションを空にします:

ALTER TABLE members TRUNCATE PARTITION p1;
Query OK, 0 rows affected (0.03 sec)

ノート:

ALTER TABLE ... REORGANIZE PARTITIONは現在TiDBではサポートされていません。

パーティションを追加します。

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

テーブルを範囲でパーティション化する場合、 ADD PARTITIONはパーティションリストの最後にのみ追加できます。既存のRangeパーティションに追加されると、エラーが報告されます。

ALTER TABLE members
    ADD PARTITION (
    PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

ハッシュパーティション管理

範囲パーティショニングとは異なり、 DROP PARTITIONはハッシュパーティショニングではサポートされていません。

現在、 ALTER TABLE ... COALESCE PARTITIONはTiDBでもサポートされていません。現在サポートされていないパーティション管理ステートメントの場合、TiDBはエラーを返します。

alter table members optimize partition p0;
ERROR 8200 (HY000): Unsupported optimize partition

パーティションの剪定

パーティションの剪定は、非常に単純なアイデアに基づく最適化です。一致しないパーティションはスキャンしないでください。

パーティションテーブルを作成するとしますt1

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)

PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

このSELECTのステートメントの結果を取得する場合:

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

結果がp1またはp2パーティションのいずれかに該当することは明らかです。つまり、 p1p2で一致する行を検索するだけで済みます。不要なパーティションを除外することは、いわゆる「プルーニング」です。オプティマイザーがパーティションの一部をプルーニングできる場合、パーティション化されたテーブルでのクエリの実行は、パーティション化されていないテーブルでのクエリの実行よりもはるかに高速になります。

オプティマイザーは、次の2つのシナリオで、 WHEREつの条件を通じてパーティションを整理できます。

  • partition_column=定数
  • partition_column IN(constant1、constant2、...、constantN)

現在、パーティションプルーニングはLIKEの条件では機能しません。

パーティションプルーニングが有効になる場合

  1. パーティションプルーニングはパーティションテーブルのクエリ条件を使用するため、プランナーの最適化ルールに従ってクエリ条件をパーティションテーブルにプッシュダウンできない場合、パーティションプルーニングはこのクエリには適用されません。

    例えば:

    create table t1 (x int) partition by range (x) (
            partition p0 values less than (5),
            partition p1 values less than (10));
    create table t2 (x int);
    
    explain select * from t1 left join t2 on t1.x = t2.x where t2.x > 5;
    

    このクエリでは、除外された結合が内部結合に変換され、次にt1.x > 5t1.x = t2.xt2.x > 5から派生するため、パーティションのプルーニングに使用でき、パーティションp1のみが残ります。

    explain select * from t1 left join t2 on t1.x = t2.x and t2.x > 5;
    

    このクエリでは、 t2.x > 5t1パーティションテーブルにプッシュダウンできないため、このクエリではパーティションプルーニングは有効になりません。

  2. パーティションのプルーニングはプランの最適化フェーズで行われるため、実行フェーズまでフィルター条件が不明な場合には適用されません。

    例えば:

    create table t1 (x int) partition by range (x) (
            partition p0 values less than (5),
            partition p1 values less than (10));
    
    explain select * from t2 where x < (select * from t1 where t2.x < t1.x and t2.x < 2);
    

    このクエリはt2から行を読み取り、その結果をt1のサブクエリに使用します。理論的には、パーティションのプルーニングはサブクエリのt1.x > val式の恩恵を受ける可能性がありますが、実行フェーズで発生するため、そこでは有効になりません。

  3. 現在の実装からの制限の結果として、クエリ条件をTiKVにプッシュダウンできない場合、パーティションプルーニングでは使用できません。

    例としてfn(col)式を取り上げます。 TiKVコプロセッサーがこのfnの機能をサポートしている場合、プランの最適化フェーズ中に述語プッシュダウン・ルールに従ってfn(col)がリーフ・ノード(つまり、パーティション化されたテーブル)にプッシュダウンされ、パーティションのプルーニングでそれを使用できます。

    TiKVコプロセッサーがこのfnの機能をサポートしていない場合、 fn(col)はリーフノードにプッシュダウンされません。代わりに、リーフノードの上のSelectionノードになります。現在のパーティションプルーニングの実装は、この種のプランツリーをサポートしていません。

  4. ハッシュパーティションの場合、パーティションプルーニングでサポートされるクエリはequal条件のみです。

  5. 範囲パーティションの場合、パーティションプルーニングを有効にするには、パーティション式がcolまたはfn(col)の形式である必要があり、クエリ条件は> 、および<>=かである必要が<= = 。パーティション式がfn(col)の形式である場合、 fn関数は単調である必要があります。

    fn関数が単調である場合、任意のxyについて、 x > yの場合、 fn(x) > fn(y) 。その場合、このfnの関数は厳密に単調と呼ぶことができます。 xyの場合、 x > yの場合、 fn(x) >= fn(y) 。この場合、 fnは「単調」とも呼ばれます。理論的には、すべての単調な関数はパーティションプルーニングによってサポートされます。

    現在、TiDBでのパーティションプルーニングは、これらの単調な関数のみをサポートしています。

    unix_timestamp
    to_days
    

    たとえば、パーティション式は単純な列です。

    create table t (id int) partition by range (id) (
            partition p0 values less than (5),
            partition p1 values less than (10));
    select * from t where t > 6;
    

    または、パーティション式はfn(col)の形式であり、 fnto_daysです。

    create table t (dt datetime) partition by range (to_days(id)) (
            partition p0 values less than (to_days('2020-04-01')),
            partition p1 values less than (to_days('2020-05-01')));
    select * from t where t > '2020-04-18';
    

    例外は、パーティション式としてのfloor(unix_timestamp())です。 TiDBはそのケースバイケースでいくつかの最適化を行うため、パーティションプルーニングによってサポートされます。

    create table t (ts timestamp(3) not null default current_timestamp(3))
    partition by range (floor(unix_timestamp(ts))) (
            partition p0 values less than (unix_timestamp('2020-04-01 00:00:00')),
            partition p1 values less than (unix_timestamp('2020-05-01 00:00:00')));
    select * from t where t > '2020-04-18 02:00:42.123';
    

パーティションの選択

SELECTステートメントは、 PARTITIONオプションを使用して実装されるパーティション選択をサポートします。

SET @@sql_mode = '';

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)

PARTITION BY RANGE(id)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (15),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

p1つのパーティションに格納されている行を表示できます。

SELECT * FROM employees PARTITION (p1);
+----|-------|--------|----------|---------------+
| id | fname | lname  | store_id | department_id |
+----|-------|--------|----------|---------------+
|  5 | Mary  | Jones  |        1 |             1 |
|  6 | Linda | Black  |        2 |             3 |
|  7 | Ed    | Jones  |        2 |             1 |
|  8 | June  | Wilson |        3 |             1 |
|  9 | Andy  | Smith  |        1 |             3 |
+----|-------|--------|----------|---------------+
5 rows in set (0.00 sec)

複数のパーティションの行を取得する場合は、コンマで区切られたパーティション名のリストを使用できます。たとえば、 SELECT * FROM employees PARTITION (p1, p2)p1パーティションとp2パーティションのすべての行を返します。

パーティション選択を使用する場合でも、 ORDER BYLIMITなどのWHEREの条件とオプションを使用できます。 HAVINGGROUP BYなどの集計オプションの使用もサポートされています。

SELECT * FROM employees PARTITION (p0, p2)
    WHERE lname LIKE 'S%';
+----|-------|-------|----------|---------------+
| id | fname | lname | store_id | department_id |
+----|-------|-------|----------|---------------+
|  4 | Jim   | Smith |        2 |             4 |
| 11 | Jill  | Stone |        1 |             4 |
+----|-------|-------|----------|---------------+
2 rows in set (0.00 sec)
SELECT id, CONCAT(fname, ' ', lname) AS name
    FROM employees PARTITION (p0) ORDER BY lname;
+----|----------------+
| id | name           |
+----|----------------+
|  3 | Ellen Johnson  |
|  4 | Jim Smith      |
|  1 | Bob Taylor     |
|  2 | Frank Williams |
+----|----------------+
4 rows in set (0.06 sec)
SELECT store_id, COUNT(department_id) AS c
    FROM employees PARTITION (p1,p2,p3)
    GROUP BY store_id HAVING c > 4;
+---|----------+
| c | store_id |
+---|----------+
| 5 |        2 |
| 5 |        3 |
+---|----------+
2 rows in set (0.00 sec)

パーティションの選択は、範囲パーティショニングやハッシュパーティショニングを含むすべてのタイプのテーブルパーティショニングでサポートされています。ハッシュパーティションの場合、パーティション名が指定されてp2ない場合、 p0 、...、またはpN-1がパーティション名として自動的に使用されp1

SELECTでもINSERT ... SELECT選択を使用できます。

パーティションの制限と制限

このセクションでは、TiDBのパーティションテーブルに関するいくつかの制限と制限を紹介します。

パーティショニングキー、主キー、および一意キー

このセクションでは、パーティション化キーと主キーおよび一意キーとの関係について説明します。この関係を管理するルールは、次のように表すことができます。テーブルのすべての一意のキーは、テーブルのパーティショニング式のすべての列を使用する必要があります。これには、定義上一意のキーであるため、テーブルの主キーも含まれます。

たとえば、次のテーブル作成ステートメントは無効です。

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)

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),
    UNIQUE KEY (col3)
)

PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

いずれの場合も、提案されたテーブルには、パーティショニング式で使用されるすべての列を含まない一意のキーが少なくとも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;

次の例はエラーを表示します:

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2),
    UNIQUE KEY (col3)
)

PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

提案されたパーティショニングキーにcol1col3の両方が含まれているため、 CREATE TABLEステートメントは失敗しますが、これらの列はどちらもテーブルの両方の一意キーの一部ではありません。次の変更を行うと、 CREATE TABLEステートメントが有効になります。

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

次のテーブルは、パーティション化キーに両方の一意のキーに属する列を含める方法がないため、パーティション化できません。

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

すべての主キーは定義上一意のキーであるため、次の2つのステートメントは無効です。

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)

PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col3),
    UNIQUE KEY(col2)
)

PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

上記の例では、主キーにパーティショニング式で参照されているすべての列が含まれているわけではありません。主キーに欠落している列を追加すると、 CREATE TABLEステートメントが有効になります。

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

テーブルに一意キーも主キーもない場合、この制限は適用されません。

DDLステートメントを使用してテーブルを変更する場合は、一意のインデックスを追加するときにこの制限も考慮する必要があります。たとえば、次のようにパーティションテーブルを作成する場合:

CREATE TABLE t_no_pk (c1 INT, c2 INT)
    PARTITION BY RANGE(c1) (
        PARTITION p0 VALUES LESS THAN (10),
        PARTITION p1 VALUES LESS THAN (20),
        PARTITION p2 VALUES LESS THAN (30),
        PARTITION p3 VALUES LESS THAN (40)
    );
Query OK, 0 rows affected (0.12 sec)

ALTER TABLEのステートメントを使用して、一意でないインデックスを追加できます。ただし、一意のインデックスを追加する場合は、 c1列を一意のインデックスに含める必要があります。

パーティションテーブルを使用する場合、プレフィックスインデックスを一意の属性として指定することはできません。

CREATE TABLE t (a varchar(20), b blob,
    UNIQUE INDEX (a(5)))
    PARTITION by range columns (a) (
    PARTITION p0 values less than ('aaaaa'),
    PARTITION p1 values less than ('bbbbb'),
    PARTITION p2 values less than ('ccccc'));
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

関数に関連するパーティション分割の制限

次のリストに示されている関数のみが、式の分割に使用できます。

ABS()
CEILING()
DATEDIFF()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT() (see EXTRACT() function with WEEK specifier)
FLOOR()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP() (with TIMESTAMP columns)
WEEKDAY()
YEAR()
YEARWEEK()

MySQLとの互換性

現在、TiDBは、範囲パーティショニング、List パーティショニング、List COLUMNS パーティショニング、およびハッシュパーティショニングをサポートしています。キーパーティショニングなど、MySQLで使用可能な他のパーティショニングタイプは、TiDBではまだサポートされていません。

RANGE COLUMNSでパーティション化されたテーブルの場合、現在TiDBは単一のパーティション化列の使用のみをサポートしています。

パーティション管理に関しては、ハッシュパーティションテーブルのパーティション数の調整、範囲パーティションテーブルの範囲の変更、パーティションのマージ、パーティションを交換します。

サポートされていないパーティショニングタイプの場合、TiDBでテーブルを作成すると、パーティショニング情報は無視され、テーブルは通常の形式で作成され、警告が報告されます。

LOAD DATA構文は、現在TiDBでのパーティション選択をサポートしていません。

create table t (id int, val int) partition by hash(id) partitions 4;

通常のLOAD DATA操作がサポートされています。

load local data infile "xxx" into t ...

ただし、 Load Dataはパーティションの選択をサポートしていません。

load local data infile "xxx" into t partition (p1)...

パーティションテーブルの場合、 select * from tによって返される結果は、パーティション間で順序付けられていません。これは、MySQLの結果とは異なります。MySQLはパーティション間で順序付けられていますが、パーティション内では順序付けられていません。

create table t (id int, val int) partition by range (id) (
    partition p0 values less than (3),
    partition p1 values less than (7),
    partition p2 values less than (11));
Query OK, 0 rows affected (0.10 sec)
insert into t values (1, 2), (3, 4),(5, 6),(7,8),(9,10);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

TiDBは、毎回異なる結果を返します。次に例を示します。

select * from t;
+------|------+
| id   | val  |
+------|------+
|    7 |    8 |
|    9 |   10 |
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------|------+
5 rows in set (0.00 sec)

MySQLで返される結果:

select * from t;
+------|------+
| id   | val  |
+------|------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
|    9 |   10 |
+------|------+
5 rows in set (0.00 sec)

tidb_enable_list_partition環境変数は、パーティションテーブル機能を有効にするかどうかを制御します。この変数がOFFに設定されている場合、テーブルの作成時にパーティション情報は無視され、このテーブルは通常のテーブルとして作成されます。

この変数は、テーブルの作成でのみ使用されます。テーブルの作成後、この変数値を変更しても効果はありません。詳細については、 システム変数を参照してください。

動的プルーニングモード

TiDBは、 dynamicモードとstaticモードの2つのモードのいずれかでパーティションテーブルにアクセスします。現在、デフォルトでstaticモードが使用されています。 dynamicモードを有効にする場合は、 tidb_partition_prune_mode変数を手動でdynamicに設定する必要があります。

set @@session.tidb_partition_prune_mode = 'dynamic'

手動ANALYZEおよび通常のクエリは、セッションレベルtidb_partition_prune_modeの設定を使用します。バックグラウンドでのauto-analyze操作は、グローバルtidb_partition_prune_mode設定を使用します。

staticモードでは、パーティション化されたテーブルはパーティションレベルの統計を使用します。 dynamicモードでは、パーティションテーブルはテーブルレベルの統計(つまり、GlobalStats)を使用します。 GlobalStatsの詳細については、 動的プルーニングモードでパーティションテーブルの統計を収集するを参照してください。

staticモードからdynamicモードに切り替えるときは、統計を手動で確認して収集する必要があります。これは、 dynamicモードに切り替えた後、パーティションテーブルにはパーティションレベルの統計のみがあり、テーブルレベルの統計はないためです。 GlobalStatsは、次のauto-analyzeの操作でのみ収集されます。

set session tidb_partition_prune_mode = 'dynamic';
show stats_meta where table_name like "t";
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          | p0             | 2022-05-27 20:23:34 |            1 |         2 |
| test    | t          | p1             | 2022-05-27 20:23:34 |            2 |         4 |
| test    | t          | p2             | 2022-05-27 20:23:34 |            2 |         4 |
+---------+------------+----------------+---------------------+--------------+-----------+
3 rows in set (0.01 sec)

グローバルdynamicプルーニングモードを有効にした後でSQLステートメントで使用される統計が正しいことを確認するには、GlobalStatsを取得するためにテーブルまたはテーブルのパーティションでanalyzeを手動でトリガーする必要があります。

analyze table t partition p1;
show stats_meta where table_name like "t";
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time         | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test    | t          | global         | 2022-05-27 20:50:53 |            0 |         5 |
| test    | t          | p0             | 2022-05-27 20:23:34 |            1 |         2 |
| test    | t          | p1             | 2022-05-27 20:50:52 |            0 |         2 |
| test    | t          | p2             | 2022-05-27 20:50:08 |            0 |         2 |
+---------+------------+----------------+---------------------+--------------+-----------+
4 rows in set (0.00 sec)

analyzeプロセス中に次の警告が表示された場合は、パーティション統計に一貫性がないため、これらのパーティションまたはテーブル全体の統計を再度収集する必要があります。

| Warning | 8244 | Build table: `t` column: `a` global-level stats failed due to missing partition-level column stats, please run analyze table to refresh columns of all partitions

スクリプトを使用して、すべてのパーティションテーブルの統計を更新することもできます。詳細については、 動的プルーニングモードでパーティションテーブルの統計を更新するを参照してください。

テーブルレベルの統計の準備ができたら、グローバル動的プルーニングモードを有効にできます。これは、すべてのSQLステートメントとauto-analyzeの操作に有効です。

set global tidb_partition_prune_mode = dynamic

staticモードでは、TiDBは複数の演算子を使用して各パーティションに個別にアクセスし、 Unionを使用して結果をマージします。次の例は、TiDBがUnionを使用して2つの対応するパーティションの結果をマージする単純な読み取り操作です。

mysql> create table t1(id int, age int, key(id)) partition by range(id) (
    ->     partition p0 values less than (100),
    ->     partition p1 values less than (200),
    ->     partition p2 values less than (300),
    ->     partition p3 values less than (400));
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from t1 where id < 150;
+------------------------------+----------+-----------+------------------------+--------------------------------+
| id                           | estRows  | task      | access object          | operator info                  |
+------------------------------+----------+-----------+------------------------+--------------------------------+
| PartitionUnion_9             | 6646.67  | root      |                        |                                |
| ├─TableReader_12             | 3323.33  | root      |                        | data:Selection_11              |
| │ └─Selection_11             | 3323.33  | cop[tikv] |                        | lt(test.t1.id, 150)            |
| │   └─TableFullScan_10       | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_18             | 3323.33  | root      |                        | data:Selection_17              |
|   └─Selection_17             | 3323.33  | cop[tikv] |                        | lt(test.t1.id, 150)            |
|     └─TableFullScan_16       | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+------------------------+--------------------------------+
7 rows in set (0.00 sec)

dynamicモードでは、各オペレーターが複数のパーティションへの直接アクセスをサポートするため、TiDBはUnionを使用しなくなりました。

mysql> set @@session.tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t1 where id < 150;
+-------------------------+----------+-----------+-----------------+--------------------------------+
| id                      | estRows  | task      | access object   | operator info                  |
+-------------------------+----------+-----------+-----------------+--------------------------------+
| TableReader_7           | 3323.33  | root      | partition:p0,p1 | data:Selection_6               |
| └─Selection_6           | 3323.33  | cop[tikv] |                 | lt(test.t1.id, 150)            |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1        | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+-----------------+--------------------------------+
3 rows in set (0.00 sec)

上記のクエリ結果から、パーティションプルーニングが引き続き有効であり、実行プランがp0p1にのみアクセスしている間に、実行プランのUnion演算子が消えていることがわかります。

dynamicモードでは、実行プランがよりシンプルで明確になります。ユニオン操作を省略すると、実行効率が向上し、ユニオン同時実行の問題を回避できます。さらに、 dynamicモードでは、 staticモードでは使用できないIndexJoinを使用した実行プランも使用できます。 (以下の例を参照)

例1 :次の例では、IndexJoinを使用して実行プランを使用してstaticモードでクエリを実行します。

mysql> create table t1 (id int, age int, key(id)) partition by range(id)
    -> (partition p0 values less than (100),
    ->  partition p1 values less than (200),
    ->  partition p2 values less than (300),
    ->  partition p3 values less than (400));
Query OK, 0 rows affected (0,08 sec)

mysql> create table t2 (id int, code int);
Query OK, 0 rows affected (0.01 sec)

mysql> set @@tidb_partition_prune_mode = 'static';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select /*+ TIDB_INLJ(t1, t2) */ t1.* from t1, t2 where t2.code = 0 and t2.id = t1.id;
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
| id                             | estRows  | task      | access object          | operator info                                  |
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
| HashJoin_13                    | 12.49    | root      |                        | inner join, equal:[eq(test.t1.id, test.t2.id)] |
| ├─TableReader_42(Build)        | 9.99     | root      |                        | data:Selection_41                              |
| │ └─Selection_41               | 9.99     | cop[tikv] |                        | eq(test.t2.code, 0), not(isnull(test.t2.id))   |
| │   └─TableFullScan_40         | 10000.00 | cop[tikv] | table:t2               | keep order:false, stats:pseudo                 |
| └─PartitionUnion_15(Probe)     | 39960.00 | root      |                        |                                                |
|   ├─TableReader_18             | 9990.00  | root      |                        | data:Selection_17                              |
|   │ └─Selection_17             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_16       | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo                 |
|   ├─TableReader_24             | 9990.00  | root      |                        | data:Selection_23                              |
|   │ └─Selection_23             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_22       | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo                 |
|   ├─TableReader_30             | 9990.00  | root      |                        | data:Selection_29                              |
|   │ └─Selection_29             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_28       | 10000.00 | cop[tikv] | table:t1, partition:p2 | keep order:false, stats:pseudo                 |
|   └─TableReader_36             | 9990.00  | root      |                        | data:Selection_35                              |
|     └─Selection_35             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|       └─TableFullScan_34       | 10000.00 | cop[tikv] | table:t1, partition:p3 | keep order:false, stats:pseudo                 |
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
17 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                            |
+---------+------+------------------------------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t1, t2) */ or /*+ TIDB_INLJ(t1, t2) */ is inapplicable |
+---------+------+------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

例1から、 TIDB_INLJのヒントが使用されている場合でも、パーティションテーブルのクエリでIndexJoinを使用して実行プランを選択できないことがわかります。

例2 :次の例では、IndexJoinを使用した実行プランを使用してクエリがdynamicモードで実行されます。

mysql> set @@tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select /*+ TIDB_INLJ(t1, t2) */ t1.* from t1, t2 where t2.code = 0 and t2.id = t1.id;
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
| id                              | estRows  | task      | access object          | operator info                                                                                                       |
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
| IndexJoin_11                    | 12.49    | root      |                        | inner join, inner:IndexLookUp_10, outer key:test.t2.id, inner key:test.t1.id, equal cond:eq(test.t2.id, test.t1.id) |
| ├─TableReader_16(Build)         | 9.99     | root      |                        | data:Selection_15                                                                                                   |
| │ └─Selection_15                | 9.99     | cop[tikv] |                        | eq(test.t2.code, 0), not(isnull(test.t2.id))                                                                        |
| │   └─TableFullScan_14          | 10000.00 | cop[tikv] | table:t2               | keep order:false, stats:pseudo                                                                                      |
| └─IndexLookUp_10(Probe)         | 1.25     | root      | partition:all          |                                                                                                                     |
|   ├─Selection_9(Build)          | 1.25     | cop[tikv] |                        | not(isnull(test.t1.id))                                                                                             |
|   │ └─IndexRangeScan_7          | 1.25     | cop[tikv] | table:t1, index:id(id) | range: decided by [eq(test.t1.id, test.t2.id)], keep order:false, stats:pseudo                                      |
|   └─TableRowIDScan_8(Probe)     | 1.25     | cop[tikv] | table:t1               | keep order:false, stats:pseudo                                                                                      |
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

例2から、 dynamicモードでは、クエリの実行時にIndexJoinを使用した実行プランが選択されていることがわかります。

現在、 staticまたはdynamicプルーニングモードは、プリペアドステートメントプランキャッシュをサポートしていません。

動的プルーニングモードでパーティションテーブルの統計を更新する

  1. すべてのパーティションテーブルを見つけます。

    select distinct concat(TABLE_SCHEMA,'.',TABLE_NAME)
        from information_schema.PARTITIONS
        where TABLE_SCHEMA not in('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');
    
    +-------------------------------------+
    | concat(TABLE_SCHEMA,'.',TABLE_NAME) |
    +-------------------------------------+
    | test.t                              |
    +-------------------------------------+
    1 row in set (0.02 sec)
    
  2. すべてのパーティションテーブルの統計を更新するためのステートメントを生成します。

    select distinct concat('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;')
        from information_schema.PARTITIONS
        where TABLE_SCHEMA not in ('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');
    +----------------------------------------------------------------------+
    | concat('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;') |
    +----------------------------------------------------------------------+
    | ANALYZE TABLE test.t ALL COLUMNS;                                    |
    +----------------------------------------------------------------------+
    1 row in set (0.01 sec)
    

    ALL COLUMNSを必要な列に変更できます。

  3. バッチ更新ステートメントをファイルにエクスポートします。

    mysql --host xxxx --port xxxx -u root -p -e "select distinct concat('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;') \
        from information_schema.PARTITIONS \
        where TABLE_SCHEMA not in ('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');" | tee gatherGlobalStats.sql
    
  4. バッチ更新を実行します。

    sourceコマンドを実行する前にSQLステートメントを処理します。

    sed -i "" '1d' gatherGlobalStats.sql --- mac
    sed -i '1d' gatherGlobalStats.sql --- linux
    
    SET session tidb_partition_prune_mode = dynamic;
    source gatherGlobalStats.sql