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

このドキュメントでは、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	ビッグイント(20)	本の一意のID
タイトル	varchar(100)	書籍タイトル
タイプ	列挙型	書籍の種類（例：雑誌、アニメーション、教材）
ストック	ビッグイント(20)	ストック
価格	小数点(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)

出力例では、 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）アプローチを採用しているためです。CBOアプローチでは、実行プランはルールとデータ配分の両方に依存します。TiDBの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)

次のステップ

データベースを作成し、そこにテーブルとセカンダリインデックスを追加したら、データ書くと読む機能をアプリケーションに追加できるようになります。

ヘルプが必要ですか?

不和またはスラック、あるいはサポートチケットを送信するについてコミュニティに質問してください。