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

このドキュメントでは、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)

出力例では、 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 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)

次のステップ

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

ヘルプが必要ですか?

TiDB コミュニティ、またはサポートチケットを作成するについて質問します。