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

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

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

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

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

前提条件

マルチ列インデックス機能は、TiDB v8.3 以降のバージョンで使用できます。
この機能を使用する前に、オプティマイザー修正制御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ドル未満の物件をすべて検索したい場合は、価格列にインデックスを追加できます。このインデックスにより、オプティマイザは範囲[-inf, 2000.00)のみをスキャンして行をフィルタリングできます。これにより、検索行数を約1,400万行に削減できます（物件の70%が価格が$2,000を超えると仮定）。クエリ実行プランでは、TiDBは価格に対してインデックス範囲スキャンを実行します。これにより、テーブル全体のスキャンの必要性が制限され、効率が向上します。

-- 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                              |                           |
+-----------------------------+---------+----------------------------------------------+---------------------------+

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

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

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

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

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

サンプルデータ

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

市	寝室	価格
サンディエゴ	1	1000
サンディエゴ	1	1500
サンディエゴ	2	1000
サンディエゴ	2	2500
サンディエゴ	3	1000
サンディエゴ	3	2500
サンフランシスコ	1	1000
サンフランシスコ	1	1500
サンフランシスコ	2	1000
サンフランシスコ	2	1500
サンフランシスコ	3	2500
サンフランシスコ	3	3000

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

マルチカラムインデックスを使用すると、TiDB はスキャン範囲を効率的に絞り込み、サンフランシスコでベッドルームが 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                                                                               |                                 |
+------------------------+------+---------------------------------------------------------------------------------------------+---------------------------------+

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

市	寝室	価格
サンフランシスコ	2	1000
サンフランシスコ	2	1500

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

インデックス範囲の導出

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

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

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

Table Access Path Selection

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

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

複数列インデックスにおける選言条件（ `OR`条件）

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

例1: 重複する範囲

ニューヨークで、価格が 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: 重複しない範囲

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

サンフランシスコの物件、1ベッドルーム、価格$1,500 ～ $2,500
サンディエゴの物件、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 つの手順で処理する必要があります。

表現を翻訳してください。
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))
```
範囲を導出して結合します。
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 のアーキテクチャの違いをさらに詳しく知るには、比較ホワイトペーパー参照してください。また、これがスケーラビリティ、信頼性、ハイブリッドトランザクションおよび分析ワークロードにとってなぜ重要なのかについても説明します。