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テーブルのフィールドは次のとおりです。

フィールド名タイプフィールドの説明
IDbigint(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)

次のステップ

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

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