セカンダリーインデックスを作成する
このドキュメントでは、SQLと各種プログラミング言語を使用してセカンダリインデックスを作成する方法と、インデックス作成のルールについて説明します。このドキュメントでは、 書店を例として、セカンダリインデックス作成の手順を順を追って説明します。
始める前に
セカンダリインデックスを作成する前に、以下の手順を実行してください。
二次インデックスとは何ですか？
TiDBにおけるセカンダリインデックスは論理オブジェクトです。簡単に言えば、TiDBがクエリパフォーマンスを向上させるために使用するソートタイプのデータと考えることができます。TiDBでは、セカンダリインデックスの作成はオンライン操作であり、テーブルに対するデータの読み書き操作をブロックしません。TiDBは、各インデックスに対してテーブルの各行への参照を作成し、データ自体ではなく、選択された列に基づいて参照をソートします。
二次インデックスの詳細については、 二次索引参照してください。
TiDB では、既存のテーブルにセカンダリインデックスを追加するか新しいテーブルを作成する際にセカンダリインデックスを作成することができます。
既存のテーブルにセカンダリインデックスを追加する
既存のテーブルにセカンダリ インデックスを追加するには、次のようにインデックスインデックスを作成するステートメントを使用できます。
CREATE INDEX {index_name} ON {table_name} ({column_names});
パラメータの説明:
{index_name}: セカンダリ インデックスの名前。{table_name}: テーブル名。{column_names}: インデックスを作成する列の名前をセミコロンとカンマで区切ります。
新しいテーブルを作成する際にセカンダリインデックスを作成する
テーブルの作成と同時にセカンダリ インデックスを作成するには、テーブルを作成するテーブルを作成するの末尾にKEYキーワードを含む句を追加します。
KEY `{index_name}` (`{column_names}`)
パラメータの説明:
{index_name}: セカンダリ インデックスの名前。{column_names}: インデックスを作成する列の名前をセミコロンとカンマで区切ります。
二次索引作成におけるルール
インデックス作成のベストプラクティスを参照してください。
例
bookshopアプリケーションで、特定の年に出版されたすべての書籍を検索できるようにしたいとします。
booksテーブルのフィールドは以下のとおりです。
booksテーブルは、次の SQL ステートメントを使用して作成されます。
CREATE TABLE `bookshop`.`books` (
`id` bigint AUTO_RANDOM NOT NULL,
`title` varchar(100) NOT NULL,
`type` enum('Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports') NOT NULL,
`published_at` datetime NOT NULL,
`stock` int DEFAULT '0',
`price` decimal(15,2) DEFAULT '0.0',
PRIMARY KEY (`id`) CLUSTERED
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
年による検索機能をサポートするには、特定の年に出版されたすべての書籍を検索するSQL文を作成する必要があります。2022年を例にとると、次のようなSQL文を作成します。
SELECT * FROM `bookshop`.`books` WHERE `published_at` >= '2022-01-01 00:00:00' AND `published_at` < '2023-01-01 00:00:00';
SQL文の実行計画を確認するには、 EXPLAIN文を使用できます。
EXPLAIN SELECT * FROM `bookshop`.`books` WHERE `published_at` >= '2022-01-01 00:00:00' AND `published_at` < '2023-01-01 00:00:00';
以下は実行計画の出力例です。
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+
| TableReader_7 | 346.32 | root | | data:Selection_6 |
| └─Selection_6 | 346.32 | cop[tikv] | | ge(bookshop.books.published_at, 2022-01-01 00:00:00.000000), lt(bookshop.books.published_at, 2023-01-01 00:00:00.000000) |
| └─TableFullScan_5 | 20000.00 | cop[tikv] | table:books | keep order:false |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.61 sec)
出力例において、 id列にTableFullScanと表示されています。これは、TiDB がこのクエリのbooksテーブルに対してフルテーブルスキャンを実行する準備ができていることを意味します。ただし、データ量が多い場合、フルテーブルスキャンは非常に時間がかかり、致命的な影響を与える可能性があります。
このような影響を回避するには、次のようにpublished_atテーブルのbooks列にインデックスを追加できます。
CREATE INDEX `idx_book_published_at` ON `bookshop`.`books` (`bookshop`.`books`.`published_at`);
インデックスを追加した後、 EXPLAINステートメントを再度実行して、実行プランを確認します。
以下は出力例です。
+-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+
| IndexLookUp_10 | 146.01 | root | | |
| ├─IndexRangeScan_8(Build) | 146.01 | cop[tikv] | table:books, index:idx_book_published_at(published_at) | range:[2022-01-01 00:00:00,2023-01-01 00:00:00), keep order:false |
| └─TableRowIDScan_9(Probe) | 146.01 | cop[tikv] | table:books | keep order:false |
+-------------------------------+---------+-----------+--------------------------------------------------------+-------------------------------------------------------------------+
3 rows in set (0.18 sec)
出力では、 TableFullScanの代わりにIndexRangeScanが表示されます。これは、TiDB がインデックスを使用してこのクエリを実行する準備ができていることを意味します。
実行プラン内のTableFullScanやIndexRangeScanなどの単語は、TiDB ではオペレーターです。実行プランと演算子の詳細については、 TiDBクエリ実行プランの概要参照してください。
実行プランは毎回同じ演算子を返すとは限りません。これは、TiDBがコストベース最適化（CBO）方式を採用しているためで、実行プランはルールとデータ分布の両方に依存します。
SQLパフォーマンスチューニングの詳細については、以下のドキュメントを参照してください。
注記：
TiDB はクエリ時のインデックスの明示的な使用もサポートしており、オプティマイザのヒントやSQLプラン管理（SPM）を使用してインデックスの使用を人為的に制御できます。ただし、インデックス、オプティマイザ ヒント、または SPM についてよく知らない場合は、予期しない結果を避けるためにこの機能を使用しないでください。
テーブルのインデックスをクエリするには、インデックスインデックスを表示ステートメントを使用できます。
SHOW INDEXES FROM `bookshop`.`books`;
以下は出力例です。
+-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | Clustered |
+-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
| books | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | YES |
| books | 1 | idx_book_published_at | 1 | published_at | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | NO |
+-------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+-----------+
2 rows in set (1.63 sec)
次のステップ
データベースを作成し、テーブルとセカンダリ インデックスを追加したら、アプリケーションにデータ書く機能と読む機能を追加できます。
お困りですか？
- 不和or スラックコミュニティに質問してください。
- TiDB Cloudのサポートチケットを送信してください
- TiDB Self-Managedのサポートチケットを送信してください