📣
TiDB Cloud Premium はパブリックプレビュー中です。エンタープライズワークロード向けの無制限のスケーリング、即時の弾力性、高度なセキュリティを提供します。このページは自動翻訳されたものです。原文はこちらからご覧ください。
AI
開発者
ベストプラクティス
API

複数列インデックスの最適化のベストプラクティス

今日のデータドリブンの世界では、大規模データセットに対する複雑なクエリを効率的に処理することが、アプリケーションの応答性とパフォーマンスを維持するために不可欠です。大規模かつ高負荷の環境を管理するために設計された分散SQLデータベースであるTiDBでは、データアクセスパスの最適化がスムーズで効率的なクエリの実行に不可欠です。

インデックスは、テーブル内のすべての行をスキャンする必要性を回避し、クエリパフォーマンスを向上させる強力なツールです。TiDBのクエリオプティマイザは、複数列のインデックスを活用してデータをインテリジェントにフィルタリングし、MySQLなどの従来のデータベースでは効率的に処理できない複雑なクエリ条件を処理します。

このドキュメントでは、マルチカラムインデックスの仕組み、その重要性、そしてTiDBの最適化によって複雑なクエリ条件が効率的なアクセスパスに変換される仕組みについて解説します。最適化を行うことで、大規模な環境でもレスポンスの高速化、テーブルスキャンの最小化、そしてパフォーマンスの合理化を実現できます。

これらの最適化を行わないと、大規模なTiDBデータベースにおけるクエリパフォーマンスは急速に低下する可能性があります。テーブル全体のスキャンや不適切なフィルタリングにより、数ミリ秒単位の時間が数分単位にまで悪化する可能性があります。さらに、メモリ使用量の過剰は、特に制約の厳しい環境ではメモリ不足（OOM）エラーにつながる可能性があります。TiDBのターゲット型アプローチは、関連データのみにアクセスすることを保証します。これにより、最も複雑なクエリであっても、レイテンシーを低く抑え、メモリ使用効率を向上できます。

前提条件

  • マルチ列インデックス機能は、TiDB v8.3 以降のバージョンで使用できます。
  • この機能を使用する前に、 optimizer fix control 54337の値をONに設定する必要があります。

背景: 複数列インデックス

このドキュメントでは、以下のように定義された賃貸物件一覧テーブルを例に挙げます。この例では、各物件には一意のID、都市、寝室数、賃料、入居可能日が含まれています。

CREATE TABLE listings (
    listing_id INT PRIMARY KEY AUTO_INCREMENT,
    city VARCHAR(100) NOT NULL,
    bedrooms INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    availability_date DATE NOT NULL
);

このテーブルに全米の物件が2,000万件登録されているとします。価格が2,000ドル未満の物件をすべて検索したい場合は、price列にインデックスを追加できます。このインデックスにより、オプティマイザは範囲[-inf, 2000.00)のみをスキャンして行をフィルタリングできます。これにより、検索行数を約1,400万行に削減できます（物件の70%が価格が$2,000を超えると仮定）。クエリ実行計画では、TiDBはprice列に対してインデックス範囲スキャンを実行します。これにより、テーブル全体のスキャンの必要性が制限され、効率が向上します。

-- Query 1: Find listings with price < 2000
EXPLAIN FORMAT = "brief" SELECT * FROM listings WHERE price < 2000;

+-----------------------------+---------+----------------------------------------------+---------------------------+
| id                          | task    | access object                                | operator info             |
+-----------------------------+---------+----------------------------------------------+---------------------------+
| IndexLookUp                 | root    |                                              |                           |
| ├─IndexRangeScan(Build)     | root    | table: listings, index: price_idx(price)     | range: [-inf, 2000.00)    |
| └─TableRowIDScan(Probe)     | root    | table: listings                              |                           |
+-----------------------------+---------+----------------------------------------------+---------------------------+

このフィルターはパフォーマンスを向上させますが、それでも大量の行が返される可能性があります。これは、より具体的な物件を探しているユーザーには理想的ではありません。都市、寝室数、最高価格などのフィルターを追加すると、結果が大幅に絞り込まれます。例えば、San Franciscoでベッドルームが2つあり、価格が$2,000未満の物件を検索するクエリの方が有用ですが、返される行数は数十行程度になる可能性が高いでしょう。

このクエリを最適化するには、次のようにcity 、 bedrooms 、 priceに複数列のインデックスを作成します。

CREATE INDEX idx_city_bedrooms_price ON listings (city, bedrooms, price);

SQLの複数列インデックスは辞書式順序で並べられます。(city, bedrooms, price)インデックスの場合、データはまずcityでソートされ、次に各都市内でbedroomsでソートされ、最後に各(city, bedrooms)組み合わせ内でpriceでソートされます。この順序付けにより、TiDBは各条件に基づいて効率的に行にアクセスできます。

  1. プライマリフィルターであるcityでフィルターします。
  2. オプションで、その都市内でbedroomsでフィルタリングします。
  3. オプションで、都市-ベッドルーム グループ内でpriceでフィルタリングします。

サンプルデータ

次の表は、複数列のインデックスによって検索結果がどのように絞り込まれるかを示すサンプルデータセットを示しています。

CityBedroomsPrice
San Diego11000
San Diego11500
San Diego21000
San Diego22500
San Diego31000
San Diego32500
San Francisco11000
San Francisco11500
San Francisco21000
San Francisco21500
San Francisco32500
San Francisco33000

最適化されたクエリと結果

マルチカラムインデックスを使用すると、TiDB はスキャン範囲を効率的に絞り込み、San Franciscoでベッドルームが 2つあり、価格が 2,000 ドル未満の物件を検索できます。

-- Query 2: Find two-bedroom listings in San Francisco under $2,000
EXPLAIN FORMAT = "brief"
    SELECT * FROM listings
    WHERE city = 'San Francisco' AND bedrooms = 2 AND price < 2000;

+------------------------+------+---------------------------------------------------------------------------------------------+---------------------------------+
| id                     | task | access object                                                                               | operator info                   |
+------------------------+------+---------------------------------------------------------------------------------------------+---------------------------------+
| IndexLookUp            | root |                                                                                             |                                 |
| ├─IndexRangeScan(Build)| root |table:listings,index:idx_city_bedrooms_price ["San Francisco" 2 -inf,(city, bedrooms, price)]|range:["San Francisco" 2 2000.00)|
| └─TableRowIDScan(Probe)| root |table:listings                                                                               |                                 |
+------------------------+------+---------------------------------------------------------------------------------------------+---------------------------------+

このクエリは、サンプルデータから次のフィルター処理された結果を返します。

CityBedroomsPrice
San Francisco21000
San Francisco21500

複数列のインデックスを使用することで、TiDB は不要な行スキャンを回避し、クエリパフォーマンスを大幅に向上させます。

インデックス範囲の導出

TiDBオプティマイザには、強力な範囲導出コンポーネントが含まれています。これは、クエリの条件と関連するインデックス列を受け取り、テーブルアクセスのための効率的なインデックス範囲を生成するように設計されています。この導出された範囲は、TiDBのテーブルアクセスコンポーネントに送られ、最もリソース効率の高いテーブルアクセス方法を決定します。

クエリ内の各テーブルについて、テーブルアクセスコンポーネントは適用可能なすべてのインデックスを評価し、最適なアクセス方法（フルテーブルスキャンまたはインデックススキャン）を特定します。各関連インデックスの範囲を計算し、アクセスコストを評価し、最もコストの低いパスを選択します。このプロセスでは、範囲の導出とコスト評価サブシステムを組み合わせることで、パフォーマンスとリソース使用量のバランスを取りながら、最も効率的なデータ取得方法を見つけ出します。

以下の図は、TiDB のテーブル アクセス ロジック内で範囲の導出とコスト評価がどのように連携して最適なデータ取得を実現するかを示しています。

Table Access Path Selection

複数列のフィルターは、前述の基本的な例よりも複雑になることがよくあります。AND条件、 OR条件、あるいはその両方の組み合わせが含まれる場合があります。TiDBの範囲導出サブシステムは、これらのケースを効率的に処理し、最も選択性の高い（したがって最も効果的な）インデックス範囲を生成するように設計されています。

一般的に、サブシステムはOR条件から生成された範囲にはUNION演算を適用し、 AND条件から生成された範囲にはINTERSECT演算を適用します。このアプローチにより、TiDBは複雑なフィルタリングロジックであっても、可能な限り正確にデータをフィルタリングできます。

複数列インデックスの分離条件（ OR条件）

クエリにOR条件（「分離述語」と呼ばれる）がある場合、オプティマイザは各条件を個別に処理し、 OR条件の各部分について範囲を作成します。これらの範囲が重複している場合、オプティマイザはそれらを1つの連続した範囲に結合します。重複していない場合は、それぞれ別々の範囲として保持され、どちらもインデックススキャンに使用できます。

例1: 重複する範囲

New Yorkで、価格が 2つの重複する範囲のいずれかに該当する 2 ベッドルームの物件を検索するクエリを考えてみましょう。

  • 価格は$1,000 ～ $2,000
  • 価格は$1,500 ～ $2,500

この場合、2つの範囲が重複しているため、オプティマイザはそれらを$1,000から$2,500の単一の範囲に結合します。クエリとその実行計画は次のとおりです。

-- Query 3: Overlapping price ranges
EXPLAIN FORMAT = "brief"
    SELECT * FROM listings
    WHERE (city = 'New York' AND bedrooms = 2 AND price >= 1000 AND price < 2000)
       OR (city = 'New York' AND bedrooms = 2 AND price >= 1500 AND price < 2500);

+-------------------------+------+----------------------------------------------------------------------+--------------------------------------------------+
| id                      | task | access object                                                        | operator info                                    |
+-------------------------+------+----------------------------------------------------------------------+--------------------------------------------------+
| IndexLookUp             | root |                                                                      |                                                  |
| ├─IndexRangeScan(Build) | root | table:listings,index:idx_city_bedrooms_price(city, bedrooms, price)  | range:["New York" 2 1000.00,"New York" 2 2500.00)|
| └─TableRowIDScan(Probe) | root | table:listings                                                       |                                                  |
+-------------------------+------+----------------------------------------------------------------------+--------------------------------------------------+

例2: 重複しない範囲

別のシナリオとして、San FranciscoまたはSan Diegoで手頃な価格のシングルベッドルーム物件を検索するクエリを想像してみてください。ここでは、条件OR異なる都市の2つの異なる範囲を指定しています。

  • San Franciscoの物件、1ベッドルーム、価格$1,500 ～ $2,500
  • San Diegoの物件、1ベッドルーム、価格$1,000 ～ $1,500

インデックス範囲は重複しないため、実行計画では個別のままとなり、各都市には独自のインデックス範囲が設定されます。

-- Query 4: Non-overlapping ranges for different cities

EXPLAIN FORMAT = "brief"
    SELECT * FROM listings
    WHERE
        (city = 'San Francisco' AND bedrooms = 1 AND price >= 1500 AND price < 2500)
     OR (city = 'San Diego' AND bedrooms = 1 AND price >= 1000 AND price < 1500);

+-------------------------+------+--------------------------------------------------------------------+------------------------------------------------------------+
| id                      | task | access object                                                      | operator info                                              |
+-------------------------+------+--------------------------------------------------------------------+------------------------------------------------------------+
| IndexLookUp             | root |                                                                    |                                                            |
| ├─IndexRangeScan(Build) | root | table:listings,index:idx_city_bedrooms_price(city, bedrooms, price)| range:["San Francisco" 1 1500.00,"San Francisco" 1 2500.00)|
| └─TableRowIDScan(Probe) | root | table:listings                                                     |       ["San Diego" 1 1000.00,"San Diego" 1 1500.00)        |
+-------------------------+------+--------------------------------------------------------------------+------------------------------------------------------------+

重複に基づいて結合された範囲または個別の範囲を作成することにより、オプティマイザはOR条件に対してインデックスを効率的に使用し、不要なスキャンを回避してクエリのパフォーマンスを向上させることができます。

複数列インデックスの結合条件（ AND条件）

AND条件（結合条件とも呼ばれます）を含むクエリの場合、TiDBオプティマイザは各条件に対して範囲を作成します。次に、これらの範囲の重なり（共通部分）を検索し、インデックスアクセスの正確な結果を取得します。各条件に範囲が1つしかない場合は簡単ですが、条件に複数の範囲が含まれる場合は複雑になります。このような場合、TiDBはこれらの範囲を結合し、最も選択的で効率的な結果を生成します。

例1：テーブルのセットアップ

次のように定義された表t1を考えます。

CREATE TABLE t1 (
    a1 INT,
    b1 INT,
    c1 INT,
    KEY iab (a1,b1)
);

次の条件を持つクエリがあるとします。

(a1, b1) > (1, 10) AND (a1, b1) < (10, 20)

このクエリでは複数の列を比較するため、TiDB オプティマイザは次の2つの手順で処理する必要があります。

  1. 表現を翻訳します。

    TiDB オプティマイザは、これらの複雑な条件をより単純な部分に分解します。

    • (a1, b1) > (1, 10)は(a1 > 1) OR (a1 = 1 AND b1 > 10)に変換されます。つまり、 a1 1より大きい場合、またはa1がちょうど1でb1が10より大きい場合がすべて含まれます。
    • (a1, b1) < (10, 20)は(a1 < 10) OR (a1 = 10 AND b1 < 20)に変換され、 a1 10より小さい場合や、 a1がちょうど10でb1が20より小さい場合をカバーします。

    これらの式はANDを使用して結合されます。

    ((a1 > 1) OR (a1 = 1 AND b1 > 10)) AND ((a1 < 10) OR (a1 = 10 AND b1 < 20))

  2. 範囲を導出して結合します。

    TiDBオプティマイザは条件を分解した後、各部分の範囲を計算し、それらを結合します。この例では、次のように導出されます。

    • (a1, b1) > (1, 10)の場合: a1 > 1の場合は範囲(1, +inf]を作成し、 a1 = 1およびb1 > 10の場合は(1, 10, 1, +inf]を作成します。
    • (a1, b1) < (10, 20)の場合: a1 < 10の場合は範囲[-inf, 10)を作成し、 a1 = 10とb1 < 20の場合は範囲[10, -inf, 10, 20)を作成します。

    最終結果では、これらを組み合わせて、洗練された範囲(1, 10, 1, +inf] UNION (1, 10) UNION [10, -inf, 10, 20)得られます。

例2: クエリプラン

次のクエリプランは、導出された範囲を示しています。

-- Query 5: Conjunctive conditions on (a1, b1)
EXPLAIN FORMAT = "brief"
    SELECT * FROM t1
    WHERE (a1, b1) > (1, 10) AND (a1, b1) < (10, 20);

+-------------------------+------+----------------------------+-------------------------------------------+
| id                      | task | access object              | operator info                             |
+-------------------------+------+----------------------------+-------------------------------------------+
| IndexLookUp             | root |                            |                                           |
| ├─IndexRangeScan(Build) | root | table:t1,index:iab(a1, b1) | range:(1 10,1 +inf],(1,10)[10 -inf,10 20) |
| └─TableRowIDScan(Probe) | root | table:t1                   |                                           |
+-------------------------+------+----------------------------+-------------------------------------------+

この例では、テーブルには約5億行あります。しかし、この最適化により、TiDBはアクセスを約4,000行、つまり全データのわずか0.0008%に絞り込むことができます。この改良により、クエリのレイテンシーは、最適化を行わない場合の2分以上から数ミリ秒へと大幅に短縮されます。

このような条件で完全なテーブルスキャンを必要とする MySQL とは異なり、TiDB オプティマイザはこれらの導出された範囲を活用して複雑な行の式を効率的に処理できます。

結論

TiDBオプティマイザは、マルチカラムインデックスと高度な範囲導出を使用することで、複雑なSQLクエリのデータアクセスコストを大幅に削減します。結合条件（ AND ）と分離条件（ OR ）の両方を効果的に管理することで、TiDBは行ベースの式を最適なアクセスパスに変換し、クエリ時間を短縮し、パフォーマンスを向上させます。MySQLとは異なり、TiDBはマルチカラムインデックスの和集合演算と積集合演算をサポートしているため、複雑なフィルタを効率的に処理できます。実用上、この最適化により、TiDBはわずか数ミリ秒でクエリを完了できます。最適化を行わない場合は2分以上かかるため、レイテンシーが大幅に短縮されます。

MySQL と TiDB のアーキテクチャの違いをさらに詳しく知るには、 比較ホワイトペーパーを参照してください。また、これがスケーラビリティ、信頼性、ハイブリッドトランザクションおよび分析ワークロードにとってなぜ重要なのかについても説明します。

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