SQLインデックスとパーティションを使用したボトルネックの解決

公開: 2022-03-11

SQLインデックスの説明の最初のレッスンでは、データが特定の列の値で既に並べ替えられている場合、 SELECTクエリの方が高速であることを学びました。

2番目のレッスンでは、Bツリーインデックスの基本構造と、それらを使用してクエリの実行中にアクセスするデータの量を減らす方法を学びました。また、複数のテーブルを結合するクエリを実装する方法と、インデックスがそのようなクエリを高速化する方法も理解しました。

また、SQLでのインデックスの使用が役立つ2つのシナリオを強調しました。インデックスがインデックスをカバーし、クエリのすべての列（ WHERE条件、 JOIN条件、およびSELECTリスト）を含む場合、対応するテーブルを完全に読み取ることは避けます。あるいは、インデックスは、アクセスされるデータブロックの数をテーブルサイズのごく一部に減らすときに役立ちます。

それ以外の場合は、インデックスから読み取り、対応するテーブル行にランダムに前後にジャンプするよりも、テーブル全体をスキャンする方が効率的です。

SQL範囲クエリ

インデックスを利用できるクエリには、通常、1つ以上の列が取ることができる可能な値の範囲を大幅に減らす条件が含まれています。範囲クエリは、「列Aの値はXとYの間にある必要があります」などの条件に基づいてデータを制限します。

この良い例は、2番目のレッスンの演習4のクエリです。

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND r.HotelID = 3;

ここに2つの範囲があります。 1つ目は、2020年8月13日から2020年8月14日までの日付の範囲です。2つ目は、可能な限り最小の数値範囲です。条件は、 r.HotelID BETWEEN 3 AND 3と同等です。

演習1：期間（日付と時間の範囲のクエリ）

CheckInTimeという列をReservationsテーブルに追加しましょう。このスプレッドシートでサンプルデータを確認できます。 CheckInTimeとClientIdの両方をカバーする単一のインデックスがあることに注意してください。

2020年8月15日にチェックインしたクライアントの名前を返すクエリを記述します。

経験の浅いSQL開発者は通常、次のクエリを記述します。

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE TO_DATE(r.CheckInTime, 'YYYY-MM-DD') = '2020-08-15';

彼らは、クエリの実行が次のようになると想定しています。

 Get first row from IX_CheckInTime_ClientID where TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' While found and TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName Get next row from IX_CheckInTime_ClientID

問題は、この記事の執筆時点では、このような実行プランを生成できるRDBMSが1つもないことです。彼らは、 TO_DATE （Oracle構文）を、 CheckInTime列の値をインデックス付けされていないものに変換する関数と見なしています。したがって、それらが生成する傾向のある実行プランは次のようになります。

 For each row from IX_CheckInTime_ClientID If TO_DATE(CheckInTime, 'YYYY-MM-DD') = '2020-08-15' then Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName

インデックス行はテーブル行よりも狭いため、これを実行すると、 Reservationsテーブルからすべての行を読み取るよりも高速になります。行が小さいということは、ディスクからアクセスする必要のあるブロックが少ないことを意味します。

ただし、最初の実行計画の方がはるかに効率的であることがわかっています。 RDBMSにそのアプローチを使用するように説得するには、クエリを書き直す必要があります。

 SELECT c.ClientName FROM Reservations r JOIN Clients c ON r.ClientID = c.ClientID WHERE r.CheckInTime >= TO_DATE('2020-08-15 00:00:00', 'YYYY-MM-DD HH:MI:SS') AND r.CheckInTime < TO_DATE('2020-08-16 00:00:00', 'YYYY-MM-DD HH:MI:SS');

これは適切な範囲クエリであり、すべての優れたRDBMSが理解できるものです。 RDBMSは、 CheckInTimeの値（派生したものではない）が明確に定義された範囲に属するReservationsテーブルのデータが必要であることを認識しています。生成される実行プランは、次のようになります。

 Get first row from IX_CheckInTime_ClientID where CheckInTime >= '2020-08-15 00:00:00' While found and CheckInTime < '2020-08-16 00:00:00' Fetch Clients.* where ClientID = IX_CheckInTime_ClientID.ClientID Write down Clients.ClientName Get next row from IX_CheckInTime_ClientID

それが私たちが本当に望んでいることです。インデックス自体だけでなく、それがソートされているという事実も活用することです。

演習2： `LIKE`にワイルドカードを使って好き

今回、私たちの探偵は容疑者に関する漠然とした情報を持ってホテルにやって来ました。名前が「-son」で終わっているだけです。探偵は、そのようなすべてのゲストの名前と名前を求めています。

 SELECT FirstName, LastName FROM Clients WHERE LastName LIKE '%son';

ClientsテーブルとLastNameのインデックスには、このスプレッドシートを使用します。クエリが返す結果を書き留めます。適用できるさまざまなアプローチについて考えてください。

テーブルスキャンアプローチ

最も簡単な戦略は、テーブルからすべてのデータを読み取り、ゲストの名前が「-son」で終わる場合はゲストの名前を書き留めることです。

 For each row from Clients If LastName like '%son' then write down FirstName, LastName

ここでは、テーブル全体を順番に読み取る必要があります。

インデックスの使用

LastName列のインデックスを利用してみましょう。 IX_LastNameシートに移動し、それを使用して、指定された基準を満たすすべてのクライアントを見つけ、それらの名前を書き留めます。

テーブルからすべてのアンダーソン、ロビンソン、およびトンプソンを見つけるには、インデックス全体を読み取る必要があることがわかりました。これは、テーブルスキャンを使用するよりも優れていますか？インデックス全体を読み取ることに加えて、一致するすべてのエントリについて、 rowAddress値を使用してテーブルから対応する行を見つけ、そこからFirstNameを書き留める必要がありました。

 For each row from IX_LastName If LastName like '%son' then Fetch Clients.* where RowAddress = IX_LastName.RowAddress Write down FirstName, LastName

私たちにとっては、テーブルを順番に読み取る方が簡単で高速でした。 RDBMSの場合、基準を満たす行の割合によって異なります。大きなテーブルにアンダーソン、ロビンソン、トンプソンがほんの一握りしかない場合、RDBMSは、一致が見つかったときにテーブルから数ブロックを読み取らなければならない場合でも、はるかに狭いインデックスエントリから読み取るデータブロックが少なくなります。それ以外の場合、テーブルスキャンにかかる時間は短くなります。

インデックス内のデータを並べ替えても、このようなクエリには役立ちません。インデックス行のサイズを小さくすると便利な場合がありますが、それはたまにしかありません。

演習3： `LIKE`にワイルドカードを使用するように

次回私たちの探偵が来るとき、私たちは姓が「Rob-」で始まるすべてのクライアントを見つける必要があります。

 SELECT FirstName, LastName FROM Clients WHERE LastName LIKE 'Rob%';

同じスプレッドシートからクエリに一致するデータを抽出してみてください。

テーブルスキャンアプローチを使用した場合、インデックスIX_LastNameを最大限に活用する機会を逃しました。「Rob-」（Roberts）で始まるインデックスから最初のエントリを見つけ、後続の行（RobertsesとRobinsonsの両方）を読み取り、 LastNameが基準に一致しなくなったら停止する方がはるかに高速です。

 Get first row from IX_LastName where LastName <= 'Rob' While found and LastName < 'Roc' Fetch Clients.* where rowAddress = IX_LastName.rowAddress Write down FirstName, LastName Get next from IX_LastName

この場合、最初のエントリのBツリールックアップの後、基準を満たすエントリのみを読み取ります。基準に一致しない名前を読み取るとすぐに読み取りを停止します。

Bツリースケーリングの問題への対処

通常、新しいデータベースをデプロイすると、いくつかの入力されたルックアップテーブルと空のトランザクションテーブルがあります。特にテーブルを正規化することでデータベース設計のグッドプラクティスを尊重した場合、システムは最初からスムーズに実行されます。プライマリキー、外部キー、および一意キーの作成。対応するインデックスを持つ外部キーをサポートします。

数か月または数年後、データ量によってシステムとデータベースの複雑さが大幅に増加すると、パフォーマンスの低下に気づき始めます。システムの速度が低下している理由とその対処法について意見が出ています。

多くの場合、データベースのサイズが主な原因であるというのが一般的な意見です。解決策は、毎日必要のない履歴データを削除し、レポートと分析のために別のデータベースに配置することだと思われます。

最初に主な仮定を調べてみましょう。

SQL範囲クエリ：実行時間はテーブルサイズに依存しますか？

単一のテーブルからの一般的な範囲クエリについて考えてみます。

 SELECT Column1, …, ColumnN FROM Table WHERE Column BETWEEN X AND Y;

Columnにインデックスがあると仮定すると、最適な実行プランは次のとおりです。

 Get first row from IX_Column where Column between X and Y While found and Column <= Y Fetch Table.* where rowAddress = IX_Column.rowAddress Write down Column1, …, ColumnN Get next row from IX_Column

このデータを返すためにRDBMSが読み取らなければならないブロックを数えましょう。

Get first row部分は、2番目のレッスンで紹介したBツリールックアップによって実装されます。読み取る必要のあるブロックの数は、Bツリーの深さと同じです。その後、インデックスのリーフレベルから後続のアイテムを読み取ります。

OLTPクエリでは、通常、すべての結果が1つのインデックスブロック内に表示されます（2つになることもありますが、それ以上になることはめったにありません）。それに加えて、インデックスエントリごとに、テーブル内のブロックにアクセスして、そのアドレスに基づいて対応する行を見つけることができます。一部のテーブル行は、すでにロードしたのと同じテーブルブロック内にある可能性がありますが、見積もりを簡単にするために、毎回新しいブロックをロードすると仮定します。

したがって、式は次のとおりです。

B = D + 1 + R

Bは読み取られたブロックの総数、DはBツリーの深さ、Rはクエリによって返された行の数です。

テーブルの行数に依存する唯一のパラメーターは、Bツリーの深さであるDです。

計算を単純化してポイントを作るために、1,000のインデックスエントリが1つのブロックに収まると仮定します。テーブル内の行が1,000行未満である限り、D=1です。ビジネストランザクションを保持するテーブルの場合、システム展開後の最初の営業日がこれに該当する可能性があります。すぐに、Bツリーの深さが増加します。テーブルの行数が100万未満である限り、インデックスは2つのレベルで構成されます。

データベースの応答時間が遅く、データ量が原因であることに悩まされている場合は、トランザクションテーブルの行数が数百万行しかないことが多いことに注意してください。 2レベルのBツリーインデックスに適合するのは100万行のみであるため、深さは少なくとも3でなければなりません。テーブルに10億行を超えない限り、深さは4になりません。これで、より正確な見積もりができました。

B = 4 + R

Rが小さい場合、Bツリーの深さを2に戻すと、クエリが大幅に高速化されます。プライマリキー値または一意キー値で検索すると、システムは5ブロックではなく4ブロックを読み取ります。これは、20％の改善です。クエリがより多くの行を返す場合、改善は目立たない可能性があります。問題は、多くのアプリケーションで、データベースに100万未満のトランザクションを保持するだけでは、必要なビジネスオペレーションをサポートできない可能性があることです。

したがって、結論は、テーブルのサイズは重要ではないということのようです。つまり、履歴データの移動は時間とリソースの浪費です。

しかし、それほど速くはありません。Bツリーインデックスの構造と、データの変更がインデックスにどのように影響するかについて詳しく見ていきましょう。

Bツリーインデックスの実装の詳細

2番目のレッスンのBツリーインデックスの説明では、バランスの取れたツリーのすべてのレベルが（物理的に）キー列の値で順序付けられていることがわかりました。ただし、アイテムを挿入、更新、または削除する場合、順序を維持するために大量のデータを移動する必要があることがよくあります。

たまたまいっぱいになったブロックの真ん中に挿入しているとしましょう。ブロックを分割し、データを再配置し、場合によっては現在のBツリーレベルを指す別のBツリーレベルでデータを更新する必要があります。

このような場合をより効率的にするために、各インデックス項目には前の行と次の行へのポインタが含まれ、二重にリンクされています。一般的に挿入する場合、これは、新しいアイテムを前のアイテムにできるだけ近づけて書き込み、ポインターを修正することを意味します。

ブロックも分割する必要がある場合は、前のBツリーレベルで新しいアイテムを作成する必要があります。これは、さらにいくつかのポインタを修正するだけの問題です。ツリーの大部分を書き直す必要はありません。分割後、データの両方のブロックは約半分いっぱいになります。ディスクの空き領域によっては、「隣接する」ブロックが物理的にかなり離れている場合があります。

しばらくすると、インデックスの断片化が増加し、クエリ実行の速度低下が顕著になります。 RDBMSが説明した方法でクエリを実行すると、アイテムの順序と近接性の仮定がますます正確でなくなり、読み取りがはるかに多くなります。最悪の場合、すべてのデータブロックが半分空になると、システムは2倍のブロックを読み取る必要があります。

Bツリーインデックスのメンテナンス

これの解決策は、インデックスの最適化（または「インデックスの再作成」）です。すべてのRDBMSには、インデックス全体を再作成する機能があります。インデックスを再作成した後、インデックスは再び物理的に順序付けられます。

インデックスの再作成は、大量のデータの読み取りと書き込みを行いますが、非常に高速な操作です。最新のRDBMSは通常、2つのインデックス再作成モードを提供しますが、より高速なモードでは、処理中にテーブルをロックする必要があります。いずれにせよ、オフピーク時にインデックスを再作成することをお勧めします。そうしないと、処理によってデータベースのパフォーマンスが低下する可能性があります。

履歴データの削除

数十億または数億行のテーブルがある場合、オフピーク時にインデックスの再作成操作を完了するのは現実的ではない可能性があります。

この状況を回避するには、OLTPデータベースから履歴データを移動することが解決策になる場合があります。ただし、特定のしきい値より古い行を単に削除すると、インデックスがさらに断片化され、さらに頻繁にインデックスを再作成する必要があります。

レスキューへのSQLパーティショニング？

履歴データの削除によって引き起こされる断片化を回避し、本番データベースに「アクティブな」トランザクションのみを保持する方法があります。すべての主要なRDBMSが実装する考え方は、テーブルを小さなチャンク（パーティションと呼ばれる）に分割し、それらを追加、削除、さらにはテーブル間で切り替える機能を提供することです（たとえば、アクティブなテーブルから同じテーブルを持つ履歴テーブルに）構造）。

このスプレッドシートでパーティション化されたReservationsテーブルを見てみましょう。表は月ごとに分割されており、パーティション名は期間やその他のスプレッドシートにマップされています。パーティションテーブルに対するクエリがどのように実行されるかを確認するために、いくつかの演習を行います。

演習4：SQLでのパーティションクエリ

上にリンクされているスプレッドシートから、インデックスを使用せずに、次のクエリで要求されたデータを抽出してみてください。

 SELECT HotelID, ReservationID, ClientID, DateFrom, DateTo FROM Reservations WHERE DateFrom BETWEEN TO_DATE('2021-03-01','YYYY-MM-DD') AND TO_DATE('2021-03-03');

最初にパーティションマッピングシートを見て、2021年3月からの予約を含むパーティションを見つける必要があることを理解したと思います。その後、対応するパーティションを開き、データを順番に読み取り、満たされていない行を除外しました。調子。

簡単ですが、多くの行を読んだ後、それほど少ない行を保持するのはおそらく好きではありませんでした。 3月のパーティションを読み取ることは、予約テーブル全体を読み取るよりも優れていましたが、それでも理想的ではありませんでした。インデックスはどうですか？

グローバルインデックス

RDBMSを使用すると、パーティションテーブルのすべてのパーティションをカバーするグローバルインデックスを作成できます。ただし、グローバルインデックスと通常のインデックスの動作に違いはありません。グローバルインデックスはパーティションを認識しません。したがって、グローバルインデックスを使用するCRUDクエリには、そのテーブルのパーティションマップは含まれません。

パーティション全体を削除する場合にのみ、パーティションマップを更新する必要があります。次に、削除されたパーティションを指す行をインデックスから削除する必要があります。つまり、グローバルインデックス全体を再構築する必要があります。

廃止されたアイテムが削除されるまでインデックスは使用できないため、停止ウィンドウは引き続き必要です。パーティションを定期的に削除してアクティブなパーティションの数を制限できる場合は、インデックスの再作成操作が停止ウィンドウに収まる可能性があります。したがって、パーティションを使用すると、グローバルインデックスの保守を含む保守タスクに必要な時間が短縮されるため、元の問題が解決されます。

しかし、それでも停止の余裕がない場合はどうなるでしょうか。

グローバルにパーティション化されたインデックス

この戦略はその問題を解決します。テーブルを分割するのと同じ方法でインデックスを分割するだけです。パーティションスプレッドシートがリンクしているスプレッドシートでは、各パーティションにReservationsテーブルの一部とIX_DateFromというインデックスシートが含まれており、どちらもDateFromでパーティション化されています。

演習4のクエリを実行するために、RDBMSは最初にインデックスパーティションマップを調べて、範囲の日付が含まれているパーティションを特定します。（この場合、これは1つのインデックスパーティションです。）その後、Bツリールックアップを使用し、リーフレベルまで循環し、最後に対応する行アドレスを使用してテーブルにアクセスします。

テーブルからパーティションを削除するときは、対応するパーティションをインデックスから削除するだけで十分です。ダウンタイムは必要ありません。

ローカルインデックス

グローバルにパーティション化されたインデックスの主な欠点は、テーブルと対応するインデックスパーティションの両方を削除する必要があることです。インデックスパーティションマップ自体からの読み取りと維持に関連する追加コストはわずかです。

ローカルインデックスには、類似しているがわずかに異なるアプローチが含まれます。単一のグローバルインデックスをパーティション化する代わりに、各テーブルパーティション内にローカルインデックスを作成します。そうすることで、ローカルインデックスは、グローバルにパーティション化されたインデックスの主な利点を共有します。つまり、ダウンタイムが発生しない一方で、欠点を回避します。

それは完璧な解決策のようです。しかし、祝う前に、いくつかのクエリの可能な実行プランを調べてみましょう。

演習5：ローカルに分割されたインデックス

今度はDateFromのローカルにパーティション化されたインデックスを使用して、クエリを再実行してみてください。

おそらく次の実行プランを使用しました。

 For all partitions where [StartDateFrom, StartDateTo) intersects ['2021-03-01', '2021-03-03'] Get first row from IX_DateFrom where DateFrom between '2021-03-01' and '2021-03-03' While found and DateFrom < '2021-03-04' Fetch Reservations.* where RowAddress = IX_DateFrom.RowAddress Write down HotelID, ReservationID, ClientID, DateFrom, DateTo Get next row from IX_DateFrom

すべての日付が1つのパーティションに属しているのは幸運であるため、1つのローカルインデックスのみをトラバースする必要がありました。期間が6か月の場合、6つのローカルインデックスを読み取る必要があります。

演習6：対照的に

あなたのタスクは、予約パーティションマップを再度使用することです。今回は、クライアント124がホテル1を訪問した期間のリストを作成します。

 SELECT DateFrom, DateTo FROM Reservations WHERE ClientID = 124 AND HotelID = 1;

ここで、ローカルインデックスの主な欠点を確認できます。 ReservationsテーブルのすべてのパーティションからローカルインデックスシートIX_HotelID_CientIDを読み取る必要がありました。

 For all partitions Get first row from IX_HotelID_ClientID where ClientID = 124 and HotelID = 1 While found and ClientID = 124 and HotelID = 1 Fetch Reservations.* where RowAddress = IX_HotelID_ClientID.RowAddress Write down DateFrom, DateTo Get next row from IX_HotelID_ClientID

この実行では、テーブルがパーティション化されていない場合よりも明らかに多くのブロックが読み取られ、時間がかかります。

そのため、オフピーク期間中にインデックスの正常性を維持する方法を見つけましたが、この戦略により、一部のクエリが遅くなりました。

私たちのビジネスモデルで少数のパーティションを保持できる場合、または少なくとも最も頻繁なクエリにRDBMSが1つまたは2つのパーティションのみを読み取ることを許可する基準が含まれている場合、このソリューションが必要になる可能性があります。それ以外の場合は、パーティション化を避け、データモデル、インデックス、クエリの改善、およびデータベースサーバーの強化に取り組むことをお勧めします。

SQLのインデックス：次に学ぶべきこと

これで私たちの旅は終わりです。 SQL Indexes Explainedでは、最新のすべてのRDBMSに共通するインデックスの実装に焦点を当てました。また、データベース管理者に通常関係するトピックを犠牲にして、アプリケーション開発者が関心を持っているトピックにも焦点を当てました。後者は、インデックスの断片化に対するフィルファクターの影響を調査するのに適していますが、両方の役割の人々は、以下についてさらに読むことが役立つと思われます。

データとインデックスのキャッシング
ハッシュ、GiST、ビットマップ、列ストアインデックスなどの非Bツリーインデックス構造
クラスタ化インデックス（Oracleではインデックス編成テーブルと呼ばれます）
機能インデックス
部分インデックス

ここで説明したパーティショニングアプローチは、範囲パーティショニングです。これは最も一般的に使用されるタイプのパーティショニングですが、ハッシュパーティショニングやリストパーティショニングなどの他のタイプもあります。また、一部のRDBMSは、複数レベルのパーティショニングのオプションを提供します。

最後に、SQL開発者は、RDBMSクエリの実行に関する他の重要なトピック、つまり、最初にクエリの解析、次にコストベースの実行プランのコンパイル、キャッシング、および再利用を検討することをお勧めします。

私が経験した4つのRDMBSについては、次のステップとして次のリソースをお勧めします。

オラクル

オプティマイザーの概要
インデックスとインデックス編成テーブル
インデックスの管理
パーティションの概要
パーティションガイド
トムに聞いて

PostgreSQL

クエリ処理
PostgreSQLのインデックス
PostgreSQLのインデックス（公式ドキュメント）
バッファ管理
テーブルのパーティション化
パーティションガイド

Microsoft SQL Server

クエリ処理アーキテクチャ
インデックス
パーティション化されたテーブルとインデックス

MySQL / MariaDB

クエリ実行プランを理解する
最適化とインデックス
パーティショニング-基本
パーティショニング-ドキュメント
MariaDBドキュメント：クエリの最適化とインデックス