TiDB Cloud
TiDB Self-Managed
Learning Center
お問い合わせ

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

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

次のステップ

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

このページは役に立ちましたか？