重要
このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

インデックス作成のベストプラクティス

このドキュメントでは、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はインデックスで直接データをクエリできるようになり、パフォーマンスの向上に役立ちます。

  • 頻繁にクエリを実行しない列にセカンダリインデックスを作成することは避けてください。便利なセカンダリインデックスはクエリを高速化できますが、副作用もあることに注意してください。インデックスを追加するたびに、行を挿入するときに追加のKey-Valueが追加されます。インデックスが多いほど、書き込みが遅くなり、より多くのスペースが消費されます。さらに、インデックスが多すぎるとオプティマイザの実行時間に影響し、不適切なインデックスはオプティマイザを誤解させる可能性があります。したがって、インデックスが多いからといって、必ずしもパフォーマンスが向上するとは限りません。

  • アプリケーションに基づいて適切なインデックスを作成します。原則として、パフォーマンスを向上させるために、クエリで使用される列にのみインデックスを作成します。次の場合は、インデックスの作成に適しています。

    • 区別度の高い列は、フィルタリングされた行の数を大幅に減らすことができます。たとえば、性別ではなく、個人ID番号にインデックスを作成することをお勧めします。
    • 複数の条件でクエリを実行する場合は、複合インデックスを使用します。同等の条件の列は、結合されたインデックスの前に配置する必要があることに注意してください。次に例を示します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インデックスを使用して実行を高速化できます。

    警告

    現在、式インデックスは実験的機能であり、TiDB構成ファイルで有効にする必要があります。詳細については、 式インデックスを参照してください。

  • インデックス内の列にクエリ対象の列が含まれているカバーインデックスを使用し、 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_idxtitle_idxがそれぞれ列idtitleで使用可能であると仮定して、 id_idxの方が優れていることがわかっている場合は、SQLでUSE INDEXヒントを使用して、TiDBオプティマイザーにid_idxインデックスを使用させることができます。

    SELECT * FROM t USE INDEX(id_idx) WHERE id = 1 and title = 'database';
    
  • クエリ条件でIN式を使用する場合は、一致する値の数が300を超えないようにすることをお勧めします。そうしないと、実行効率が低下します。