セカンダリインデックスを作成する

このドキュメントでは、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テーブルのフィールドは次のとおりです。

フィールド名	タイプ	フィールドの説明
ID	bigint(20)	本の一意のID
タイトル	varchar(100)	本のタイトル
タイプ	列挙型	本の種類 (雑誌、アニメ、教材など)
ストック	bigint(20)	ストック
価格	10 進数(15,2)	価格
公開された_で	日付時刻	発行日

booksテーブルは、次の SQL ステートメントを使用して作成されます。

CREATE TABLE `bookshop`.`books` (
  `id` bigint(20) 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(11) 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)

出力例では、 TableFullScan がid列に表示されます。これは、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)アプローチを使用しており、実行計画がルールとデータ分散の両方に依存するためです。 TiDB SQL のパフォーマンスの詳細については、 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)

次のステップ

データベースを作成し、テーブルとセカンダリインデックスを追加した後、アプリケーションにデータ書くおよび読む機能の追加を開始できます。