SQLインデックスの説明、Pt。 1

適切に使用すると、SQLデータベースインデックスは非常に効果的で、魔法のように見える場合があります。 しかし、次の一連の演習では、その下にあるほとんどのSQLインデックスのロジック（およびそれらを正しく使用すること）が非常に単純であることを示します。

このシリーズ「 SQLインデックスの説明」では、インデックスを使用してデータにアクセスし、最新のすべてのRDBMSで行われる方法でインデックスを設計する動機について説明します。 次に、特定のクエリパターンのデータを返すために使用されるアルゴリズムを見ていきます。

SQLインデックスの説明に従うことができるようにするために、インデックスについて多くを知る必要はありません。 前提条件は2つだけです。

• SQLの基本的な知識
• プログラミング言語の基本的な知識

SQLインデックスの説明の主なトピックは次のとおりです。

• SQLデータベースインデックスが必要な理由。 インデックスを使用した実行計画の視覚化
• インデックスの設計：どのインデックスがクエリを高速かつ効率的にするか
• インデックスを効果的に使用するためのクエリを作成する方法
• SQLでのインデックスの使用が読み取り/書き込み効率に与える影響
• カバーするインデックス
• パーティショニング、読み取りと書き込みへの影響、およびいつ使用するか

これは単なるSQLインデックスのチュートリアルではなく、インデックスの基礎となるメカニズムを理解するための詳細です。

演習を行い、問題解決方法を分析することにより、RDBMSがインデックスをどのように使用するかを理解します。 演習資料は、読み取り専用のGoogleスプレッドシートで構成されています。 演習を行うには、Googleスプレッドシートをコピーするか（ [ファイル]→[コピーを作成]）、その内容を独自のGoogleスプレッドシートにコピーします。

すべての演習で、Oracle構文を使用するSQLクエリを示します。 日付には、ISO8601形式のYYYY-MM-DDを使用します。

演習1：すべてのクライアントの予約

最初のタスク（まだ実行しないでください）は、ホテル予約システムの特定のクライアントの予約スプレッドシートからすべての行を検索し、それらを独自のスプレッドシートにコピーして、次のクエリの実行をシミュレートすることです。

 SELECT * FROM Reservations WHERE ClientID = 12;

しかし、私たちは特定の方法に従いたいと思っています。

アプローチ1：並べ替えなし、フィルタリングなし

最初の試行では、並べ替えまたはフィルタリング機能を使用しないでください。 費やした時間を記録してください。 結果のシートには73行が含まれている必要があります。

この擬似コードは、ソートせずにタスクを実行するためのアルゴリズムを示しています。

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

この場合、841行すべてをチェックして、条件を満たす73行を返し、コピーする必要がありました。

アプローチ2：並べ替えのみ

2回目の試行では、 ClientID列の値に従ってシートを並べ替えます。 フィルタは使用しないでください。 時間を記録し、データを並べ替えずにタスクを完了するのにかかった時間と比較します。

並べ替え後のアプローチは次のようになります。

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

今回は、「たった」780行をチェックする必要がありました。 どういうわけか最初の行にジャンプできれば、さらに短い時間で済みます。

しかし、タスクのプログラムを開発する必要がある場合、このソリューションは最初のソリューションよりもさらに遅くなります。 これは、最初にすべてのデータを並べ替える必要があるためです。つまり、各行に少なくとも1回はアクセスする必要があります。 このアプローチは、シートがすでに目的の順序で並べ替えられている場合にのみ有効です。

演習2：特定の日付から始まる予約の数

ここでのタスクは、2020年8月16日のチェックイン数をカウントすることです。

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

演習1のスプレッドシートを使用します。並べ替えの有無にかかわらず、タスクの完了に費やした時間を測定して比較します。 正しいカウントは91です。

ソートなしのアプローチの場合、アルゴリズムは基本的に演習1のアルゴリズムと同じです。

並べ替えの方法も、前の演習の方法と似ています。 ループを2つの部分に分割します。

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

演習3：犯罪捜査

警察の検査官は、2020年8月13日と14日にホテルに到着したゲストのリストを確認するように要求します。

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

アプローチ1：日付のみで並べ替え

検査官はリストを速くしたいと思っています。 到着日に従ってテーブル/スプレッドシートを並べ替えた方がよいことはすでにわかっています。 演習2を終えたばかりの場合、テーブルがすでにソートされているのは幸運です。 したがって、演習2と同様のアプローチを適用します。

時間、読まなければならなかった行数、リストの項目数を記録してみてください。

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

このアプローチを使用して、46人のゲストのリストをコンパイルするために511行を読み取る必要がありました。 正確に下にスライドできれば、8月13日の最初の到着を見つけるためだけに、繰り返しサイクルから324回の読み取りを実行する必要はありませんでした。 ただし、ゲストがHotelIDが3でホテルに到着したかどうかを確認するには、100行以上を読み取る必要がありました。

検査官はずっと待っていましたが、満足しませんでした。ゲストの名前やその他の関連データの代わりに、意味のないIDのリストのみを配信しました。

シリーズの後半でその側面に戻ります。 まず、リストをより早く作成する方法を見つけましょう。

アプローチ2：ホテル、次に日付で並べ替え

HotelID 、 DateFromの順に行を並べ替えるには、すべての列を選択してから、Googleスプレッドシートのメニューオプションである[データ]→[並べ替え範囲]を使用します。

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

私たちは最初の到着をホテルに見つける前に最初の338の到着をスキップしなければなりませんでした。 その後、8月13日に最初の到着を見つけるために103以上の早い到着を調べました。 最後に、 ClientIDの46個の連続した値をコピーしました。 3番目のステップで、連続するIDのブロックをコピーできたことがわかりました。 残念ながら、そのブロックから最初の行にジャンプできませんでした。

アプローチ3：ホテルのみで並べ替え

次に、 HotelIDのみで並べ替えられたスプレッドシートを使用して同じ演習を試してください。

HotelIDのみで並べ替えられたテーブルに適用されるアルゴリズムは、 HotelIDとDateFromで（この順序で）並べ替える場合よりも効率が低くなります。

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

この場合、 HotelIDが3のホテルへの166の到着すべてを読み取り、それぞれについて、 DateFromが要求された間隔に属しているかどうかを確認する必要があります。

アプローチ4：日付で並べ替え、次にホテル

最初にHotelIDで並べ替え、次にDateFromで並べ替えるか、その逆かは本当に重要ですか？ 調べてみましょう：最初にDateFromで並べ替え、次にHotelIDで並べ替えてみてください。

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

関連する日付の最初の行を見つけてから、ホテルへの最初の到着を見つけるまで詳細を読みました。 その後、いくつかの行で、正しい日付と適切なホテルの両方の条件が満たされました。 ただし、ホテル3に到着後、同じ日にホテル4、5などに到着しました。 その後、目的のホテルへの連続した到着を読み取ることができるようになるまで、ホテル1と2の翌日の行を再度読み取る必要がありました。

ご覧のとおり、すべてのアプローチでは、行の完全なセットの中央に単一の連続したデータブロックがあり、部分的に一致したデータを表しています。 アプローチ2と4は、部分一致の終わりに到達する前に、ロジックによってアルゴリズムを完全に停止できる唯一のアプローチです。

アプローチ4は2つのブロックでデータを完全に一致させましたが、アプローチ2は、ターゲットデータがすべて1つの連続したブロックにある唯一のデータです。

数字からすると、この場合、アプローチ2が最も有利であることが明らかです。

SQLインデックスの説明：結論と次のステップ

これらの演習を行うと、次の点が明確になります。

1. 適切にソートされたテーブルからの読み取りは高速です。
2. テーブルがまだソートされていない場合、ソートは、ソートされていないテーブルから読み取るよりも時間がかかります。
3. ソートされたテーブル内の検索条件に一致する最初の行にジャンプする方法を見つけると、多くの読み取りを節約できます。
4. 事前にテーブルを並べ替えておくと便利です。
5. 最も頻繁なクエリのためにテーブルのソートされたコピーを維持することは役に立ちます。

現在、テーブルのソートされたコピーは、データベースインデックスのように聞こえます。 SQL Indexes Explainedの次の記事では、基本的なインデックスの実装について説明します。 読んでくれてありがとう！