インデックスに基づくSQLの最適化について議論する前に、重要な評価指標である「読み取る行数」を理解する必要があります。TP業務では通常スループットが高く、理想的な状況では各リクエストが読み取る行数は比較的小さくなければなりません。SQLを最適化する過程において、最適化結果が十分良いかどうかを判断する重要な指標は、単一実行で読み取る必要があるデータ量です。この指標の最適化への指導的意義は容易に理解できます。クラスタ全体のI/O能力には限りがあり、単一リクエストによって生じるI/Oが十分に小さければ、業務全体で生じるI/Oも比較的小さくなり、クラスタ全体の負荷も大きくなりません。まず、この指標とSQL、実行計画との関連について簡単に説明します。
SQLの実行過程において、一部のフィルタ条件はインデックス上のデータスキャン範囲（Query Range）を特定するために使用されます。この範囲内のデータ量が読み取る行数です。これらのフィルタ条件は
range_condと呼ばれます（下記例の14行を参照：T1.C2 = ?）。SQL内の他のベーステーブルフィルタ条件はtable filterと呼ばれます（下記例の10行を参照：
T1.C1 = 1）。データがインデックスから読み出された後、カーネルはtable filterに従って再度データをフィルタリングします。
ほとんどの場合、インデックスを作成する主な目的は、クエリ内の述語を最大限に活用し、Query Rangeを縮小して読み取る行数を減らすことです。したがって、遅延するクエリが与えられた場合、まずそのクエリ計画のquery range filterが何であるか、query range filterのフィルタリング能力が十分強いかどうか、インデックススキャン範囲内にどれだけのデータがあるかを分析する必要があります。クエリと実行計画が与えられた場合、この実行計画の読み取る行数を得るには2つの方法があります。
EXPLAIN SELECT * FROM T1 WHERE C1 = 1 AND C2 = 'A'
=========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|T1(IDX_C2)|1 |2695|
=========================================
Outputs & filters:
-------------------------------------
0 - output([T1.C1], [T1.C2]), filter([T1.C1 = 1]),
access([T1.C1], [T1.C2]), partitions(p0),
is_index_back=true, filter_before_indexback[false],
range_key([T1.C2], [T1.__pk_increment]), range(A,MIN ; A,MAX),
range_cond([T1.C2 = ?])
T1:table_rows:10002, physical_range_rows:99, logical_range_rows:99, index_back_rows:99, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_C2], unstable_index_name[T1], estimation info[table_id:1100611139453780, (table_type:1, version:0-1658634108084971-1658634108084971, logical_rc:0, physical_rc:0), (table_type:0, version:1-1-9223372036854775807, logical_rc:99, physical_rc:99)]
Parameters
-------------------------------------
{obj:{"VARCHAR2":"A", collation:"utf8mb4_bin", coercibility:"INVALID"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"VARCHAR2", collation:"utf8mb4_bin", coercibility:"INVALID"}}
方法1：EXPLAINを使用してこのクエリの実行計画を取得する
table_rows:10002, physical_range_rows:99, logical_range_rows:99 実行計画では、インデックスIDX_C2上の行数の推定情報が示されています。ここでtable_rowsはテーブル全体の総行数です。physical_range_rowsとlogical_range_rowsはrange(A,MIN ; A,MAX)内の行数です。一般的にこれら2つの指標は近い値になりますので、どちらか一方を見ればよいでしょう。非常に特殊なシナリオでは、physical_range_rowsがlogical_range_rowsを大幅に上回る可能性があります。このクエリではIDX_C2を利用しており、予想されるスキャン行数は99行です。
方法2：上記の方法は推定値です
一部のシナリオでは、この推定値が正確でない場合があります。正確な行数を取得するために、クエリを実行してみることができます。クエリSELECT COUNT(*) FROM T1 WHERE ...を構築します。このクエリでは、フィルタ条件にはquery range filterのみが含まれます。実際のクエリを通じて、より正確な行数を得ることができます。
新しいインデックスを追加することを検討する際、方法2を使用してクエリを構築し、この新規インデックスによる予想される読み取る行数を特定することができます。これにより、対応する実行計画が適切かどうかを大まかに判断することができます。
SELECT COUNT(*) FROM T1 WHERE C2 = 'A'
+----------+
| COUNT(*) |
+----------+
| 99 |
+----------+
インデックスを使用してクエリパフォーマンスを最適化する場合、ほとんどの場合は複合インデックスを作成し、可能な限り多くの述語をquery range filterとして使用できるようにします。複合インデックスを作成する前に、まずクエリを構築して、この複合インデックスによる読み取る行数がどの程度になるかを判断することができます。
例えば、以下のシナリオでは、STATUSには合計で2つの値しかない可能性があります：'SUCC'と'FAIL'。テーブルにはSTATUS = 'SUCC'を満たす大量のデータが存在する可能性があります。その場合、このクエリの読み取る行数は、主テーブルを参照する場合でもインデックステーブルを参照する場合でも非常に大きくなります。
CREATE TABLE T1 (NAME VARCHAR(10), STATUS VARCHAR(10));
CREATE INDEX IDX_STATUS(STATUS);
SELECT * FROM T1 WHERE NAME = 'OceanBase' AND STATUS = 'SUCC';
このテーブルについては、実際には適切な実行計画が選択できないということです。
解決策
IDX_NAME_STATUS(NAME, STATUS) インデックスを作成し、より多くの述語を利用してスキャン範囲を絞り込むことを検討できます。NAME 列に対する等価条件のフィルタリングが十分に良ければ、オプティマイザーは NAME, STATUS インデックスを選択します。ただし、極端なケースでは、このクエリがすべてのフィルタリングを終えた後でも、依然として大量のデータが残ることがあります。その場合、クエリは大量のディスク読み取りを発生させることになります。このような状況では、業務担当者と協議し、クエリロジックや使用方法が適切かどうか確認するしかありません。
インデックス最適化の鍵となる問題は、クエリ内でフィルタリングが非常に強い述語の集合を特定し、それらの述語を用いて十分に良いクエリ範囲を生成するためのインデックスを作成することです。したがって、重要な問題は、どの述語を用いてクエリ範囲を特定できるかということです。一般的には、主に以下のカテゴリが含まれます：
- 等価述語：
C1 = 1のような等価比較を行う述語。 - 範囲述語：
C1 > 1、C1 >= 1、C1 < 1、C1 <= 1、C1 between 1 and 3のような範囲比較を行う述語。 - LIKE述語：
C1 like 'abc%'のような述語。この種の述語では、ワイルドカードは文字列の先頭には置けません。 - IN述語：
C1 IN (1,2,3)のような述語。
重要なポイント
上記の述語はすべて、列の値に対して一定のフィルタリングを行います。注意点として、列に他の関数計算が含まれてはなりません。C1 + 1 IN (1, 2, 3) や cast(c1_varchar as signed) IN (1, 2, 3) のような述語は、クエリ範囲の特定には使用できません。特に注意が必要なのは、述語 C1 in (1, 2, 3) が与えられた場合、C1 の列型が整数型でない場合、データベースは実際の処理で C1 を暗黙的に整数型に変換してから述語判定を行います。このようなシナリオも実際には列に cast 計算が含まれており、インデックスを利用してクエリ範囲を特定できない原因となります。一般的な問題シナリオは以下のとおりです。次のクエリでは、実行計画は最終的に IDX_C1 を経由するのではなく、全表スキャンを選択しています。元の述語 C1 = 1 は、実際には table filter: cast(t1.C1, DECIMAL(-1, -1)) = cast(1, DECIMAL(1, 0)) と解析されます。
CREATE TABLE T1 (C1 VARCHAR(10), C2 INT);
INSERT INTO T1 VALUES (1, 1), (2, 2);
CREATE INDEX IDX_C1 ON T1 (C1);
EXPLAIN SELECT * FROM T1 WHERE C1 = 1;
| ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |1 |2 |
===================================
Outputs & filters:
-------------------------------------
0 - output([t1.C1], [t1.C2]), filter([cast(t1.C1, DECIMAL(-1, -1)) = cast(1, DECIMAL(1, 0))]), rowset=256,
access([t1.C1], [t1.C2]), partitions(p0)