1. SQLリファレンス・ガイド
  2. 問合せの最適化
  3. 例: 問合せの最適化での索引の使用

例: 問合せの最適化での索引の使用

例9-1 問合せの最適化での索引の使用

SELECT * FROM Users2
WHERE 10 < income AND income < 20;

問合せに2つの索引条件が含まれています。索引idx1、idx2、midx2およびmidx3はすべて適用可能です。索引idx1の場合、10 < incomeは開始条件で、income < 20は停止条件です。他の索引では、両方の条件がフィルタリング条件になります。たとえば、idx2が使用される場合、スキャンする部分範囲は索引全体となります。この場合、明らかに、idx1は他の索引よりも優れています。ただし、取得される表の行数は、idx1とidx2のどちらが使用されても同じになります。midx2またはmidx3が使用された場合、取得される個別の行数はidx1およびidx2の場合と同じですが、行はその行のphones配列の要素数と同じ回数取得されます。このような重複は、最終的な問合せ結果セットから削除されます。

索引idx2がWITH NO NULLSで作成された場合、address.stateおよびaddress.cityフィールドの索引条件がないため、この問合せには適用できません。たとえば、Users2にaddress.cityがNULLでincomeが15の行が含まれている場合、索引にはこの行のエントリは含まれず、その結果、索引が使用された場合、この行は条件を満たしても結果に表示されません。同じことが索引midx2およびmidx3にも当てはまります。一方、idx1はWITH NO NULLSで作成された場合でも、単一フィールド(income)に索引を付け、問合せにそのフィールドの2つの開始/停止条件が含まれるため、引き続き適用可能です。

例9-2 問合せの最適化での索引の使用

SELECT * FROM Users2
WHERE 20 < income OR income < 10;

問合せには、WHERE式全体である1つの索引条件が含まれています。索引idx1、idx2、midx2、midx3はすべて適用可能です。これらのすべてについて、条件はフィルタリング条件です。

例9-3 問合せの最適化での索引の使用

SELECT * FROM Users2
WHERE 20 < income OR age > 70;

索引にユーザーの年齢に関する情報が含まれていないため、この場合、索引条件はありません。

例9-4 問合せの最適化での索引の使用

SELECT * FROM Users2 u
WHERE u.address.state = "CA" 
    AND u.address.city = "San Jose";

idx2のみが適用可能です。開始条件と停止条件の両方として機能する2つの索引条件があります。

例9-5 問合せの最適化での索引の使用

SELECT id, 2*income FROM Users2 u
WHERE u.address.state = "CA" 
    AND u.address.city = "San Jose";

idx2のみが適用可能です。開始条件と停止条件の両方として機能する2つの索引条件があります。この場合、SELECT句に必要なidおよび収入情報を索引で利用できます。その結果、表にアクセスすることなく、問合せ全体に索引のみから回答できます。索引idx2は、例5の問合せのカバー索引であると言います。問合せプロセッサは、この最適化を適用します。

例9-6 問合せの最適化での索引の使用

SELECT * FROM Users2 u
WHERE u.address.state = "CA" 
    AND u.address.city = "San Jose" 
    AND u.income > 10;

idx1、idx2、midx2およびmidx3が適用可能です。idx2には、3つの索引条件があります。stateおよびcity条件は開始条件と停止条件の両方として機能し、income条件は開始条件です。idx1では、income条件のみが開始条件として適用可能です。midx2およびmidx3では、income条件はフィルタリング条件です。

例9-7 問合せの最適化での索引の使用

SELECT * FROM Users2 u
WHERE u.address.state = "CA" 
    AND u.income > 10;

idx1、idx2、midx2およびmidx3が適用可能です。idx2には、2つの索引条件があります。state条件は開始条件と停止条件の両方として機能し、income条件はフィルタリング条件です。income条件はidx1の開始条件であり、midx2およびmidx3のフィルタリング条件です。

例9-8 問合せの最適化での索引の使用

DELCARE $city STRING;

SELECT * FROM Users2 u
WHERE u.address.state = "CA" 
    AND u.address.city = $city 
    AND (u.income > 50 
        OR (10 < u.income 
            AND u.income < 20));

idx1、idx2、midx2およびmidx3が適用可能です。idx2には、3つの索引条件があります。stateおよびcity条件は、開始条件と停止条件の両方として機能します。複合income条件は、適用可能なすべての索引のフィルタリング条件です(ORノードをルートとします)。

例9-9 問合せの最適化での索引の使用

SELECT id FROM Users3 u
WHERE EXISTS u.info.income;

この例では、ユーザーに関するすべての情報をjsonデータとして格納する表Users3を使用します。この問合せは、収入を記録するユーザーを検索します。索引jidx1が適用可能です。EXISTS条件は、実際には2つの索引の開始/停止条件(u.info.income < EMPTYおよびu.info.income > EMPTY)に変換されます。その結果、索引に対して2つのレンジ・スキャンが実行されます。

例9-10 問合せの最適化での索引の使用

SELECT * FROM users2 u
    WHERE (u.address.state, u.address.city) IN
        (("CA","San Jose"), ("MA","Boston"))

この例では、idx2索引が使用されます。索引に対して2つのスキャンが実行されます。1つはstateフィールドとcityフィールドがそれぞれ「CA」と「San Jose」のエントリ、もう1つはstateフィールドとcityフィールドがそれぞれ「MA」と「Boston」のエントリです。

例9-11 問合せの最適化での索引の使用

SELECT * FROM users2 u
    WHERE u.address.state in ("CA", "MA") AND
        u.address.city in ("San Jose","Boston")

この例では、idx2索引が使用されます。索引に対して4つのスキャンが実行されます。これらのスキャンの検索キーは、2つのIN演算子の右側にあるキーのデカルト積("CA", "San Jose")、("CA", "Boston")、("MA, "San Jose")および("MA", "Boston")によって決定されます。

例9-12 問合せの最適化での索引の使用

SELECT * FROM users2 u
    WHERE (u.address.state, u.income) IN
        (("CA", 10000), ("MA", 20000))

この例では、idx2索引が使用されます。索引に対して2つのスキャンが実行されます。1つはstateフィールドが「CA」のエントリに対するスキャン、もう1つはstateフィールドが「MA」のエントリに対するスキャンです。さらに、IN条件全体が、2つのスキャンによって返されたエントリのフィルタリング条件として使用されます。

前述の例が示すように、条件は、次の場合にのみ、索引IDXの開始/停止条件として使用されます。
  • <path expr> op <const expr>、または<const expr> op <path expr>、または(<path expr1>, … <path exprN>) IN (<const exprs>)の形式です
  • opは比較演算子です(EXISTS、NOT EXISTS、IS NULLおよびIS NOT NULLは、Q9に示すように、この形式の条件に変換されます)。
  • <const expr>はリテラルおよび外部変数のみから作成された式です(表や内部変数を参照しません)
  • <path expr>は、IDXのCREATE INDEX文に表示される索引パスPと一致するパス式です。ここまでは、完全一致の例のみを示しました。次の例では、完全一致でないものも示します。
  • PがIDXの最初の索引パスでない場合は、IDXの定義内のPより前にある索引パスごとに、等価の開始条件または停止条件が存在します。
  • 比較演算子は、any演算子のいずれかになります。このような演算子は、複数キー索引の複数キー索引パスに対して照合されます。次の例に示すように、このような条件には追加の制限が適用されます。

例9-13 問合せの最適化での索引の使用

SELECT * FROM users2 u
WHERE u.connections[] = any 10;

midx1が適用可能で、条件は開始条件と停止条件の両方です。

例9-14 問合せの最適化での索引の使用

SELECT * FROM users2 u
WHERE u.connections[0:4] = any 10;

midx1が適用可能です。mdx1にプッシュ・ダウンする条件は、ユーザー10にまったく接続していないユーザーを除外するために、u.connections[] =any 10です。ただし、ユーザー10との接続があるが、最も強い5つの接続ではないユーザーを除外するために、元の条件(u.connections[0:4] =any 10)を問合せに保持する必要があります。これは、問合せパス式と対応する索引パスの一致が完全でない例です。

例9-15 問合せの最適化での索引の使用

SELECT * FROM users2 u
WHERE u.connections[] > any 10;

midx1が適用可能で、条件は開始条件です。

例9-16 問合せの最適化での索引の使用

SELECT id FROM users2 u
WHERE 10 < any u.connections[] 
    AND u.connections[] < any 100 ;

midx1が適用可能で、各条件自体は索引条件ですが、実際にはそのように使用できるのは1つのみです。理由を確認するには、まず、問合せが、idが10を超える接続と、idが100未満の別の接続(最初の接続と同じ場合と同じでない場合があります)を持つユーザーを要求することに注意してください。次に、それぞれ[ 1, 3, 110, 120 ]および[1, 50, 130]の接続配列を持つ2人のユーザー(たとえば、idが200と500)のみを含むUsers2表を考えてみます。これらの配列は両方とも問合せの条件を満たし、結果として両方のユーザーが返されます。ここで、midx1について考えます。次の7つのエントリが含まれています。

[1, 200], [1, 500], [3, 200], [50, 500], [110, 200], [120, 200], [130, 500]

最初の条件のみを開始条件として使用して索引をスキャンし、索引スキャンによって返された行に2番目の条件を適用することで、問合せの結果は500、200となり、正しいことになります。一方、両方の条件が索引スキャンに使用された場合、エントリ[50, 500]のみが適格となり、問合せではユーザー500のみが返されます。

例9-17 問合せの最適化での索引の使用

10から100の範囲の接続を持つユーザーを検索するには、次の問合せを使用します。

SELECT id FROM users2 u
WHERE exist u.connections
    [10 < $element AND $element < 100];

例13と同じ2人のユーザーを想定すると、この問合せの結果はユーザー500のみとなり、両方の条件が同じ配列要素に適用されるため、両方の条件を索引条件(開始と停止)として使用できます。問合せプロセッサは、両方の条件をmdx1に実際にプッシュします。

例9-18 問合せの最適化での索引の使用

SELECT * FROM Users2 u
WHERE u.address.phones.area = any 650 
    AND u.address.phones.kind = any "work" 
    AND u.income > 10;

この問合せでは、収入が10より大きく、市外局番が650の電話番号、および勤務先の電話番号(市外局番が650ではない可能性がある)を持つユーザーが検索されます。索引midx3は適用可能ですが、address.phones.kind条件は索引条件として使用できません(例13と同じ理由のため)。市外局番条件のみを開始/停止条件として使用でき、収入条件はフィルタリング条件として使用できます。例15では、索引idx1、idx2およびmidx2も適用可能です。

例9-19 問合せの最適化での索引の使用

SELECT * FROM Users2 u
WHERE u.expenses.housing = 10000;

idx4が適用可能で、条件は開始条件と停止条件の両方です。midx4も適用可能です。midx4を使用するには、問合せにある条件が1つのみであっても、2つの条件をプッシュする必要があります。最初の条件は、keys索引フィールドに、2番目の条件はvaluesフィールドにあります。具体的には、条件key = "price"とvalue = 10000が開始/停止条件としてプッシュされます。これは、問合せパス式と索引パスの間の一致が完全でない別の例です。expenses.housingをexpenses.values()索引パスと照合し、さらに、properties.keys()索引パスの索引条件を生成します。

例9-20 問合せの最適化での索引の使用

SELECT * FROM Users2 u
WHERE u.expenses.travel = 1000 
    AND u.expenses.clothes > 500;

midx4が適用可能です。各問合せ条件はそれ自体が索引条件であり、前の例のexpenses.housing条件と同じ方法でmidx4にプッシュできます。ただし、問合せ条件の両方をプッシュすることはできません(少なくとも現在の実装では)。問合せプロセッサはプッシュする1つを選択する必要があり、もう1つは問合せ内に残ります。expenses.travel条件は等価であるため、大なり条件よりも選択性が高く、問合せプロセッサではこれが使用されます。