## モジュール 3: パーティションテーブルのグローバル一意インデックス (v8.5.0) インデックスと同様に、データベースのテーブルパーティションはデータ検索操作を高速化するためのデータ構造です。クエリが実行されると、テーブルパーティションはクエリ条件を満たすデータを迅速に見つけることができ、テーブル全体を順次スキャンする（`TableFullScan`）のではなく、クエリ効率を向上させます。 グローバルインデックスの導入前は、TiDBは各パーティションのローカルインデックスをサポートしており、主キーと一意キーはデータの一意性を確保するためにパーティションキーを含める必要がありました。さらに、複数のパーティションにまたがるデータをクエリする場合、TiDBは各パーティションのデータをスキャンして結果を返す必要がありました。 これらの問題に対処するために、TiDBはグローバルインデックス機能を導入しました。グローバルインデックスは単一のインデックスでテーブル全体のデータをカバーし、主キーと一意キーがすべてのパーティションキーを含めることなくグローバルな一意性を維持できるようにします。さらに、グローバルインデックスは複数のパーティションにまたがるインデックスデータに単一の操作でアクセスでき、非パーティションキーのクエリパフォーマンスを大幅に向上させます。 ### 手順 1. パーティションテーブルの特性を確認する + モジュール1で、すでにパーティションテーブル - `books`を作成しました。これは8つのハッシュパーティションで`id`列にパーティションされています。 + 次のSQLコマンドを実行してテーブルパーティションを確認します。出力の`PARTITION BY`句に注目してください。 ```sql SHOW CREATE TABLE books\G ``` ```output *************************** 1. row *************************** Table: books Create Table: CREATE TABLE `books` ( `id` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `type` enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') COLLATE utf8mb4_unicode_ci NOT NULL, `published_at` datetime NOT NULL, `stock` int DEFAULT '0', `price` decimal(15,2) DEFAULT '0.0', PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=30002 */ PARTITION BY HASH (`id`) PARTITIONS 8 1 row in set (0.00 sec) ``` ```sql SELECT table_name, partition_name, partition_method FROM information_schema.partitions WHERE table_name='books'; ``` ```output +------------+----------------+------------------+ | table_name | partition_name | partition_method | +------------+----------------+------------------+ | books | p0 | HASH | | books | p1 | HASH | | books | p2 | HASH | | books | p3 | HASH | | books | p4 | HASH | | books | p5 | HASH | | books | p6 | HASH | | books | p7 | HASH | +------------+----------------+------------------+ 8 rows in set (0.01 sec) ``` + `books`テーブルは8つのパーティションでパーティション化されているため、行を格納するために少なくとも8つのTiKVリージョンがあります。 ```sql SHOW TABLE books REGIONS; ``` ```output +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ | REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS | SCHEDULING_STATE | +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ | 286 | t_119_ | t_120_ | 287 | 1 | 287, 288, 289 | 0 | 0 | 1301 | 1 | 8 | | | | 290 | t_120_ | t_121_ | 291 | 1 | 291, 292, 293 | 0 | 39 | 1321 | 1 | 0 | | | | 294 | t_121_ | t_122_ | 295 | 1 | 295, 296, 297 | 0 | 0 | 1329 | 1 | 44 | | | | 298 | t_122_ | t_123_ | 299 | 1 | 299, 300, 301 | 0 | 39 | 1332 | 1 | 8 | | | | 302 | t_123_ | t_124_ | 305 | 2 | 303, 304, 305 | 0 | 39 | 1374 | 1 | 125 | | | | 306 | t_124_ | t_125_ | 307 | 1 | 307, 308, 309 | 0 | 0 | 1278 | 1 | 2 | | | | 310 | t_125_ | t_126_ | 313 | 2 | 311, 312, 313 | 0 | 0 | 1231 | 1 | 14 | | | | 314 | t_126_ | t_128_ | 315 | 1 | 315, 316, 317 | 0 | 39 | 1248 | 1 | 9 | | | +-----------+-----------+---------+-----------+-----------------+---------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ 8 rows in set (0.02 sec) ``` + `books`テーブルは主キー - `id`でハッシュパーティション化されているため、行は8つのパーティションに均等に分散されるはずです。次のクエリを実行して確認します。 ```sql SELECT 'P0_COUNT', COUNT(1) FROM books PARTITION (p0) UNION ALL SELECT 'P1_COUNT', COUNT(1) FROM books PARTITION (p1) UNION ALL SELECT 'P2_COUNT', COUNT(1) FROM books PARTITION (p2) UNION ALL SELECT 'P3_COUNT', COUNT(1) FROM books PARTITION (p3) UNION ALL SELECT 'P4_COUNT', COUNT(1) FROM books PARTITION (p4) UNION ALL SELECT 'P5_COUNT', COUNT(1) FROM books PARTITION (p5) UNION ALL SELECT 'P6_COUNT', COUNT(1) FROM books PARTITION (p6) UNION ALL SELECT 'P7_COUNT', COUNT(1) FROM books PARTITION (p7) ORDER BY 1; ``` ```output +----------+----------+ | P0_COUNT | COUNT(1) | +----------+----------+ | P0_COUNT | 12 | | P1_COUNT | 13 | | P2_COUNT | 13 | | P3_COUNT | 13 | | P4_COUNT | 13 | | P5_COUNT | 12 | | P6_COUNT | 12 | | P7_COUNT | 12 | +----------+----------+ 8 rows in set (0.00 sec) ``` ### 手順 2. パーティションテーブルにグローバルセカンダリインデックスを追加する + 次のコマンドを使用して、`books`テーブルにグローバル一意インデックスを作成しようとします。たとえば、本のタイトルとタイプの組み合わせが一意であることを確認したいとします。**エラー**が予想されます。 ```sql CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type); ``` ```output ERROR 8264 (HY000): Global Index is needed for index 'book_title_uk', since the unique index is not including all partitioning columns, and GLOBAL is not given as IndexOption ``` + エラーメッセージは、ローカル一意インデックス（`GLOBAL`キーワードが指定されていない）を作成しようとしており、パーティションキー（ローカル一意インデックスはパーティションキーを含める必要があります。私たちの場合は`id`）を含めていないため、TiDBはそのSQL実行を実行できないことを示しています。この問題を解決するには、次のいずれかを行うことができます： > `title`と`type`に**ローカル一意インデックス**を作成します。ローカルインデックスであるため、実際にはインデックスに`id`列が必要ないにもかかわらず、追加で`id`列を含める必要があります。 または > `title`と`type`に**グローバル一意インデックス**を作成します。`id`を含める必要はありません。これは単一列のインデックスです。**これは、このモジュールで議論している新機能です。** + `GLOBAL`キーワードを追加して再度グローバル一意インデックスを作成してみましょう。今回はエラーは予想されません。 ```sql CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type) GLOBAL; ``` ```output Query OK, 0 rows affected (3.06 sec) ``` + インデックスが`GLOBAL`であることを確認するために、`information_schema.tidb_indexes`テーブルをクエリし、出力の`is_global`列を確認します。 ```sql SELECT key_name, seq_in_index, column_name, clustered, is_global FROM information_schema.tidb_indexes WHERE table_name='books'; ``` ```output +---------------+--------------+-------------+-----------+-----------+ | key_name | seq_in_index | column_name | clustered | is_global | +---------------+--------------+-------------+-----------+-----------+ | PRIMARY | 1 | id | YES | 0 | | book_title_uk | 1 | title | NO | 1 | | book_title_uk | 2 | type | NO | 1 | +---------------+--------------+-------------+-----------+-----------+ 3 rows in set (0.00 sec) ``` ### 手順 3. グローバル一意インデックスの典型的なシナリオ + `books`テーブルの`title`と`type`の組み合わせが一意であることを覚えておいてください。 + グローバル一意インデックス`book_title_uk`を削除し、クエリのEXPLAINを実行します。すべてのパーティションが結果のためにスキャンされることに注意してください。説明出力の`access object`列の`partition:all`に注意してください。データセットが巨大な場合、この操作はIOコストが高くなります。 ```sql DROP INDEX book_title_uk ON bookshop.books; EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output Query OK, 0 rows affected (0.14 sec) +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ | TableReader_11 | 0.00 | root | partition:all | data:Projection_5 | | └─Projection_5 | 0.00 | cop[tikv] | | bookshop.books.stock, bookshop.books.price | | └─Selection_10 | 0.00 | cop[tikv] | | eq(bookshop.books.title, "Play That Funky Music"), eq(bookshop.books.type, "Science & Technology") | | └─TableFullScan_9 | 100.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +---------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + `(type, title)`に**ローカル**一意インデックスを追加し、クエリのEXPLAINを再度実行します。今回は、すべてのパーティションで`IndexRangeScan`が使用されていることがわかります。良いニュースは、インデックスがクエリを高速化するために活用されていることです。悪いニュースは、結果のために8つのローカルインデックスが8つのパーティションで範囲スキャンされていることです。`type`と`title`の一意の組み合わせにとって、これは必要ありません。説明出力の`access object`列の`IndexLookUp`行の`partition:all`に注意してください。以前よりは良いですが、私たちのケースにとっては最良の解決策ではありません。 ```sql CREATE UNIQUE INDEX book_title_idx ON bookshop.books (type, title, id); EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output Query OK, 0 rows affected (2.98 sec) +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_4 | 0.00 | root | | bookshop.books.stock, bookshop.books.price | | └─IndexLookUp_8 | 0.00 | root | partition:all | | | ├─IndexRangeScan_6(Build) | 0.00 | cop[tikv] | table:books, index:book_title_idx(type, title, id) | range:["Science & Technology" "\x0f\xa7\x0f.\x0e3\x10^\x02\t\x10\x02\x0e\xe1\x0e3\x10\x02\x02\t\x0e\xb9\x10\x1f\x0fd\x0f!\x10^\x02\t\x0f[\x10\x1f\x0f\xea\x0e\xfb\x0e`","Science & Technology" "\x0f\xa7\x0f.\x0e3\x10^\x02\t\x10\x02\x0e\xe1\x0e3\x10\x02\x02\t\x0e\xb9\x10\x1f\x0fd\x0f!\x10^\x02\t\x0f[\x10\x1f\x0f\xea\x0e\xfb\x0e`"], keep order:false, stats:pseudo | | └─TableRowIDScan_7(Probe) | 0.00 | cop[tikv] | table:books | keep order:false, stats:pseudo | +---------------------------------+---------+-----------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) ``` + **ローカル**インデックスを**グローバル**一意インデックスに変更しましょう。 ```sql DROP INDEX book_title_idx ON bookshop.books; CREATE UNIQUE INDEX book_title_uk ON bookshop.books (title, type) GLOBAL; ``` ```output Query OK, 0 rows affected (0.17 sec) Query OK, 0 rows affected (2.38 sec) ``` + グローバルインデックスが機能していることを確認するために次のクエリを実行します。説明出力の`access object`列の`index:book_title_uk`オブジェクトと`operator info`列の`Point_Get`操作に注意してください。すべてのパーティションで`IndexLookUp`スキャンも`TableFullScan`もなく、単一の`Point_Get`のみで、特にデータセットが巨大な場合、このモジュールで最も効率的なクエリです。 ```sql EXPLAIN SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output +-------------+---------+------+-----------------------------------------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+-----------------------------------------------+---------------+ | Point_Get_1 | 1.00 | root | table:books, index:book_title_uk(title, type) | | +-------------+---------+------+-----------------------------------------------+---------------+ 1 row in set (0.00 sec) ``` + この時点で、グローバル一意インデックスを使用する典型的なシナリオを学びました。mysqlクライアント接続を開いたままにして、次のモジュールに進みます。