インデックス作成のベストプラクティス
このドキュメントでは、TiDB でインデックスを作成および使用するためのベスト プラクティスをいくつか紹介します。
あなたが始める前に
このセクションでは、 書店データベースのbooksテーブルを例に説明します。
CREATE TABLE `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;
インデックス作成のベストプラクティス
複数の列を含む結合インデックスを作成します。これはカバーインデックスの最適化と呼ばれる最適化です。カバーインデックス最適化により、 TiDB はインデックス上で直接データをクエリできるようになり、パフォーマンスが向上します。
頻繁にクエリを実行しない列には、セカンダリ インデックスを作成しないでください。セカンダリ インデックスを使用するとクエリの速度が向上しますが、副作用もあることに注意してください。インデックスを追加するたびに、行を挿入するときにキーと値がさらに追加されます。インデックスの数が増えるほど、書き込み速度が遅くなり、消費するスペースが増えます。また、インデックスが多すぎるとオプティマイザの実行時間に影響し、不適切なインデックスはオプティマイザを誤らせる可能性があります。したがって、インデックスの数が増えても必ずしもパフォーマンスが向上するわけではありません。
アプリケーションに応じて適切なインデックスを作成します。原則として、パフォーマンスを向上させるために、クエリで使用する列にのみインデックスを作成します。次のケースは、インデックスの作成に適しています。
- 区別度の高い列を使用すると、フィルターされる行の数を大幅に減らすことができます。たとえば、性別ではなく個人 ID 番号にインデックスを作成することをお勧めします。
- 複数の条件でクエリを実行する場合は、結合インデックスを使用します。同等の条件を持つ列は、結合インデックスの先頭に配置する必要があることに注意してください。次に例を示します。1
select* from t where c1 = 10 and c2 = 100 and c3 > 10が頻繁に使用される場合は、結合インデックスIndex cidx (c1, c2, c3)を作成して、クエリ条件でスキャンするためのインデックス プレフィックスを構築できるようにします。
セカンダリ インデックスには意味のある名前を付け、会社または組織のテーブル命名規則に従うことをお勧めします。そのような命名規則が存在しない場合は、 インデックス命名仕様の規則に従ってください。
インデックスの使用に関するベストプラクティス
インデックスはクエリを高速化するためのものなので、既存のインデックスが実際にいくつかのクエリで使用されていることを確認してください。インデックスがどのクエリでも使用されていない場合は、そのインデックスは無意味なので、削除する必要があります。
結合インデックスを使用する場合は、左プレフィックスルールに従ってください。
title列目とpublished_at列目に新しい結合インデックスを作成するとします。CREATE INDEX title_published_at_idx ON books (title, published_at);次のクエリでは、結合されたインデックスを引き続き使用できます。
SELECT * FROM books WHERE title = 'database';ただし、次のクエリでは、インデックスの左端の最初の列の条件が指定されていないため、結合インデックスを使用できません。
SELECT * FROM books WHERE published_at = '2018-08-18 21:42:08';クエリの条件としてインデックス列を使用する場合は、計算、関数、または型変換を使用しないでください。そうしないと、TiDB オプティマイザーがインデックスを使用できなくなります。
時間型列
published_atに新しいインデックスを作成するとします。CREATE INDEX published_at_idx ON books (published_at);ただし、次のクエリでは
published_atインデックスを使用できません。SELECT * FROM books WHERE YEAR(published_at)=2022;published_atのインデックスを使用するには、クエリを次のように書き換えます。これにより、インデックス列で関数を使用することがなくなります。SELECT * FROM books WHERE published_at >= '2022-01-01' AND published_at < '2023-01-01';式インデックスを使用して、クエリ条件で
YEAR(Published at)の式インデックスを作成することもできます。CREATE INDEX published_year_idx ON books ((YEAR(published_at)));ここで、
SELECT * FROM books WHERE YEAR(published_at)=2022;クエリを実行すると、クエリはpublished_year_idxインデックスを使用して実行を高速化できます。インデックス内の列にクエリ対象の列が含まれるカバーリング インデックスを使用し、
SELECT *ステートメントですべての列をクエリすることは避けてください。次のクエリでは、データを取得するためにインデックス
title_published_at_idxのみをスキャンする必要があります。SELECT title, published_at FROM books WHERE title = 'database';次のクエリ ステートメントでは結合インデックス
(title, published_at)を使用できますが、インデックスのない列をクエリするために追加のコストが発生し、TiDB はインデックス データに格納されている参照 (通常は主キー情報) に従って行データをクエリする必要があります。SELECT * FROM books WHERE title = 'database';クエリ条件に
!=またはNOT IN含まれている場合、クエリではインデックスを使用できません。たとえば、次のクエリではインデックスを使用できません。SELECT * FROM books WHERE title != 'database';クエリ内の
LIKE条件がワイルドカード%で始まる場合、クエリではインデックスを使用できません。たとえば、次のクエリではインデックスを使用できません。SELECT * FROM books WHERE title LIKE '%database';クエリ条件に複数のインデックスが使用可能で、実際にどのインデックスが最適かがわかっている場合は、 オプティマイザーヒント使用して、TiDB オプティマイザにこのインデックスの使用を強制することをお勧めします。これにより、不正確な統計やその他の問題により、TiDB オプティマイザが間違ったインデックスを選択するのを防ぐことができます。
次のクエリでは、インデックス
id_idxとtitle_idxがそれぞれ列idとtitleで使用可能であると想定し、id_idxが適していることがわかっている場合は、SQL でUSE INDEXヒントを使用して、TiDB オプティマイザーにid_idxインデックスを使用するように強制できます。SELECT * FROM t USE INDEX(id_idx) WHERE id = 1 and title = 'database';クエリ条件で
IN式を使用する場合、その後に一致する値の数が 300 を超えないようにすることをお勧めします。そうしないと、実行効率が低下します。