インデックスに基づく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インデックスを選択します。ただし、極端なケースでは、このクエリがすべてのフィルタ処理を完了した後でも、依然として多くのデータが残っている可能性があります。その場合、このクエリは大量のディスク読み取りを発生させることになります。このような状況では、業務側と協力して、クエリロジックや使用方法が合理的であるかどうかを確認するしかありません。
インデックス最適化の鍵となる問題は、クエリ内でフィルタリング性が非常に強い述語の集合を特定し、それらの述語を用いて十分に良いQuery Rangeを生成するためのインデックスを作成することです。そのため、Query Rangeを決定するために使用できる述語とは何かということが重要な問題となります。一般的には、以下のカテゴリが含まれます：
- 等価述語：
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)のような述語は、Query Rangeを決定するために使用できません。特に注意すべき点として、述語C1 in (1, 2, 3)が与えられた場合、C1の列型が整数型でない場合、データベースは実際の処理時にC1を暗黙的に整数型に変換してから述語の判定を行います。このようなシナリオでは、実際には列にcast計算が含まれており、インデックスを使用してQuery Rangeを決定することができなくなります。一般的な問題シナリオは以下のとおりです。以下のクエリでは、実行計画は最終的に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)