MySQL ページング最適化の「ページング アルゴリズムを最適化する INNER JOIN メソッド」はどのような状況で有効になりますか?

最近、偶然にMySQLのページング最適化のテストケースを見ました。テストシナリオを詳しく説明せずに、古典的な解決策が示されました。現実には多くの状況が固定されていないため、一般的な慣行やルールをまとめるには多くのシナリオを考慮する必要があります。同時に、最適化を実現できる方法に直面したときは、その理由を調査する必要があります。同じ方法が異なるシナリオで最適化効果を達成できない場合は、その理由も調査する必要があります。

私はさまざまな状況でこのシナリオに懐疑的でしたが、自分でテストしてみました。案の定、いくつかの問題が見つかり、予想していたアイデアも確認できました。

この記事では、最も単純なケースから始めて、MySQL ページングの最適化について簡単に分析します。

さらに、この記事のテスト環境は、最も完全に構成されたクラウドサーバーです。相対的に言えば、サーバーのハードウェア環境は限られていますが、異なるステートメント（書き込み方法）は「同等」である必要があります。

20170916追加:

つま先で考えてみてください。

1. ページング ソート フィールドがクラスター化インデックスである場合、インデックスがデータそのものであることから、インデックスをページ分割してからデータをクエリする必要はありません。

2. 非クラスター化インデックスの場合は、まずインデックスをページ分割し、次にそのインデックスを使用してデータをクエリします。最初にインデックスをページ分割すると、スキャン範囲が実際に縮小されます。

3. 2 のように、非クラスター化インデックスをソートしてクエリを実行することが多い場合は、この列にクラスター化インデックスを作成してはいかがでしょうか。

MySQL の古典的なページング「最適化」アプローチ

MySQL のページング最適化には、典型的な問題があります。データが後ろにあるほどクエリが遅くなります (テーブルのインデックスの種類によって異なりますが、SQL Server の B ツリー インデックスでも同様です)。

 t順から* を選択  ID制限m,n

つまり、M が増加すると、同じ量のデータのクエリがますます遅くなります。

この問題に直面して、次のようなもの（またはそのバリエーション）に似た古典的なアプローチが開発されました。

まず、ページング範囲内の ID を個別に検索し、それを基本テーブルに関連付けて、最後に必要なデータをクエリします。

 t内部から*を選択 結合（ t順序からIDを選択）   id制限m,n)t1でt1.id = t.id

このアプローチは常に機能するのでしょうか、あるいはどのような状況で後者が最適化の目的を達成できるのでしょうか?効果がない書き直しや、プロセスの遅延につながる書き直しを行ったことはありませんか?

同時に、ほとんどのクエリにはフィルタリング条件があります。フィルタリング条件がある場合、SQL文は次のようになります。

 tから* を選択、 ***順序  ID制限m,n

同じことをするなら、次のように書き直してください。

 t内部から*を選択 結合( tからidを選択、 ***順序  id制限m,n)t1でt1.id = t.id

この場合、書き換えられた SQL 文は最適化の目的を達成できるでしょうか?

テスト環境構築

テスト データは比較的単純です。テスト データは、テスト テーブルの InnoDB エンジン テーブルをテストするために、ストアド プロシージャを介してループに書き込まれます。

ここで注意すべきは、ログ書き込みモードを innodb_flush_log_at_trx_commit = 2 に変更する必要があることです。そうしないと、デフォルトでは 1 日に 500 万件のデータが書き込まれない可能性があります。これはログ書き込みモードに関係するため、詳細には触れません。

ページングクエリの最適化の理由

まず、この古典的な問題を見てみましょう。ページング時に、クエリが遅くなるほど、応答が遅くなります。

テスト1: 1行目から20行目までのデータをクエリ、0.01秒

このクエリでは、20 行のデータも照会され、行 4900001 から 4900020 までのデータなど、比較的「後の」データも照会されます。これには 1.97 秒かかります。

このことから、クエリ条件が変更されていない場合、クエリが過去に遡るほど、クエリ効率が低下することがわかります。これは、同じ 20 行のデータを検索する場合、データが過去に遡るほど、クエリ コストが高くなると簡単に理解できます。

後者がなぜ効率が悪いのかについては、後で分析します。

テスト環境はCentOS 7、MySQL 5.7、テストテーブルのデータは500万です。

古典的なページングの「最適化」を再現します。フィルター条件がなく、ソート列がクラスター化インデックスである場合、改善はありません。

ここでは、クラスタ化インデックス列をソート条件として使用した場合の次の2つの書き込み方法のパフォーマンスを比較します。

 t順から* を選択  ID制限m,nによる。
 t内部から*を選択 結合（ t順序からIDを選択）   id制限m,n)t1でt1.id = t.id

***書き方:

select * from test_table1 order by id asc limit 4900000,20; テスト結果についてはスクリーンショットを参照してください。実行時間は 8.31 秒です。

2 番目に書き直した書き方:

test_table1 t1からt1.*を選択

内部結合 (select id from test_table1 order by id limit 4900000,20)t2 on t1.id = t2.id; 実行時間は 8.43 秒です

ここで明らかなのは、古典的な書き換え方法で書き換えた後、パフォーマンスはまったく改善されず、むしろ少し遅くなる可能性があるということです。実際のテストでは、両者のパフォーマンスに明らかな線形差はないことがわかりました。著者は、この 2 つに対して複数のテストを行いました。

個人的には、同様の結論を見たら、それをテストしなければなりません。これは推測や運に頼ることはできません。なぜ効率を向上できるのか、できないのか。

では、なぜ書き直しによって伝説ほどパフォーマンスが向上しなかったのでしょうか?

現在の書き換えではパフォーマンス向上の目標を達成できない原因は何でしょうか?

後者がパフォーマンスを向上できる原理は何ですか?

まず、テスト テーブルのテーブル構造を見てみましょう。ソート列にインデックスがありますが、これは問題ありません。重要なのは、ソート列のインデックスが主キー (クラスター化インデックス) であることです。

ソート列がクラスター化インデックスの場合、「最適化」後に書き換えられた SQL が「最適化」の目的を達成できないのはなぜですか?

ソート列がクラスター化インデックス列である場合、どちらの方法でも、順次テーブル スキャンを使用して、修飾されたデータをクエリします。後者は、最初にサブクエリを実行し、次にサブクエリの結果を使用してメイン テーブルを実行します。

しかし、サブクエリでは、「テーブルを順次スキャンして、条件に該当するデータをクエリする」という方法は変わりません。現状では、書き換えられた方法も不要であると思われます。

次の 2 つの実行プランを参照してください。最初のスクリーンショットの実行プランの 1 行目は、書き換えられた SQL の実行プランの 3 行目 (id = 2 の行) と基本的に同じです。

フィルタリング条件がなく、ソート列がクラスター化インデックスである場合、いわゆるページング クエリ最適化は不要になります。

現時点では、上記のデータをクエリするどちらの方法も非常に遅いです。では、上記のデータをクエリしたい場合は、どのようにすればよいでしょうか?なぜ遅いのかをまだ理解する必要があります。まず、B ツリーのバランス構造を理解する必要があります。私の大まかな理解では、下の図に示すように、クエリされたデータが「後方」の場合、実際には B ツリー インデックスの 1 つの方向から外れています。次の 2 つのスクリーンショットに示されているターゲット データは、実際にはバランス ツリー上のデータです。いわゆる「前方」と「後方」はありません。「前方」と「後方」は、互いに相対的であるか、スキャンの方向からの相対的なものです。ある方向から見ると、「後方」のデータは、別の方向から見ると「前方」です。前方と後方は絶対的ではありません。

次の 2 つのスクリーンショットは、B ツリー インデックス構造の大まかな表現です。ターゲット データの位置が固定されている場合、いわゆる「後方」は左から右への相対的なものです。

右から左に見ると、以前は後ろにあると考えられていたデータが実際には前にあります。

データが最前線にある限り、そのデータを効率的に見つけることは可能です。 MySQL にも、SQL Server と同様の前方スキャンと後方スキャンのアプローチが必要です。

後続のデータに対して逆スキャンを使用すると、この部分のデータをすばやく見つけることができ、見つかったデータを再度ソート（昇順）すると、結果は同じになるはずです。まず、効果を見てみましょう。結果は上記のクエリとまったく同じで、ここではわずか 0.07 秒しかかかりません。前の 2 つの書き込み方法ではどちらも 8 秒以上かかり、効率に数百倍の差があります。

なぜそうなるのかについては、上記の説明に基づいて理解できると思います。これが SQL です。

より大きな ID を持つデータや、時間的に新しいデータなど、いわゆる後のデータを頻繁にクエリする場合は、逆スキャン インデックス メソッドを使用して効率的なページング クエリを実現できます。

（ここでデータのページングを計算してください。同じデータでも昇順と降順では開始「ページ番号」が異なります。）

 *から選択 
 （
 test_table1から*を選択  IDによる記述制限 99980,20 
      
 ) t注文  ID別;

フィルタ条件がなく、ソート列が非クラスタ化インデックスの場合、いくらかの改善が見られる。

ここでは、テストテーブルtest_table1に次の変更が加えられています。

1. id_2 列を追加します。

2. フィールドに一意のインデックスを作成します。

3. このフィールドには対応する主キーIDが入力されます

上記のテストは、主キー インデックス (クラスター化インデックス) でソートされています。次に、非クラスター化インデックス、つまり新しく追加された列 id_2 でソートし、冒頭で説明した 2 つのページング方法をテストします。

まず、***の書き方を見てみましょう

select * from test_table1 order by id_2 asc limit 4900000,20; 実行時間は1分強なので、60秒と仮定しましょう。

2番目の書き方

test_table1 t1からt1.*を選択

内部結合 (select id from test_table1 order by id_2 limit 4900000,20)t2 on t1.id = t2.id; 実行時間 1.67 秒

この観点から、つまり、ソート列が非クラスター化インデックス列である場合、後者の書き込み方法により、確かに効率が大幅に向上します。これはほぼ40倍の改善です。

それで、その理由は何でしょうか?

まず、最初の書き方の実行プランを見てみましょう。このSQLを実行すると、テーブル全体をスキャンし、その後id_2で再ソートし、最初の20件のデータを取得するということが簡単にわかります。

まず、テーブル全体のスキャンは非常に時間のかかるプロセスであり、ソートも非常にコストがかかるため、パフォーマンスが非常に低くなります。

後者の実行プランを見てみましょう。最初に id_2 のインデックスの順序でサブクエリをスキャンし、次に修飾された主キー Id を使用してテーブル内のデータをクエリします。この方法では、大量のデータをクエリして再ソートする (filesort を使用) 必要がなくなります。

SQL Server の実行プランを理解していれば、後者は前者と比較して頻繁なテーブル戻り (SQL Server ではキー検索またはブックマーク検索と呼ばれるプロセス) を避けるはずです。サブクエリが外部テーブルを駆動して、条件を満たす 20 個のデータをバッチで 1 回限りでクエリすると考えられます。

実際、現在の状況、つまりソート列が非クラスター化インデックス列である場合にのみ、書き換えられた SQL によってページング クエリの効率を向上させることができます。

それでも、この「最適化された」ページング ステートメントのページング効率は、次のページング ステートメントの効率とは大きく異なります。上記のように、次のクエリは同じデータを 0.07 秒で返します。これは、ここでの 1.67 秒よりも 2 桁も長い時間です。

 *から選択 
 （
 test_table1から*を選択  IDによる記述制限 99980,20 
      
 ) t注文  ID別;

もう 1 つ言及したい質問は、ページング クエリが特定の順序で頻繁に実行される場合、この列にクラスター化インデックスを作成しないのはなぜかということです。

たとえば、ステートメントが一意性を確保するために ID または時間 + その他のフィールドを自動的に増やす場合、MySQL は主キーにクラスター化インデックスを自動的に作成します。

そして、クラスター化インデックスでは、「前」と「後ろ」は相対的な論理概念にすぎません。ほとんどの場合、「後ろ」またはより新しいデータを取得したい場合は、上記の書き込み方法を使用できます。

フィルタリング条件がある場合のページングクエリの最適化

この部分について考えてみると、状況が複雑すぎて、非常に代表的なケースをまとめるのが難しいと感じたので、あまりテストは行わないことにします。

 tから* を選択、 ***順序  ID制限m,n

1. たとえば、選択条件自体が非常に効率的で、フィルタリング後に残るデータ量が少量である場合、選択条件自体で非常に効率的な選択を実現できるため、SQL を書き直すことにはあまり意味がありません。

2. 例えば、選択条件自体があまり効果的でない場合（フィルタリング後もデータ量が膨大である場合）、この状況は実際には選択条件がない状況に戻ります。また、並べ替え方法、昇順か降順かなどによっても異なります。

3. たとえば、フィルタリング条件自体はあまり効果的ではありません (フィルタリング後のデータ量はまだ膨大です)。考慮すべき非常に実用的な問題は、データの分布です。

データの分散はSQLの実行効率にも影響します（SQL Serverの経験からすると、MySQLもそれほど変わらないはずです）

4. クエリ自体が複雑な場合、特定の方法で効率化の目的を達成できるとは言い難い。

状況が複雑になればなるほど、普遍的なルールや方法をまとめることは難しくなります。すべては具体的な状況に基づいて見なければならず、結論を導き出すことは困難です。

ここでは、クエリ条件をフィルタリング条件に追加するケースを一つ一つ分析することはしませんが、実際のシナリオがなければ決まった解決策がないことは確かです。

さらに、現在のページのデータをクエリするときに、前のページのクエリの最高値をフィルタリング条件として使用して、現在のページのデータをすばやく見つけることができます。もちろんこれは問題ありませんが、これは別のアプローチであり、この記事では説明しません。

以下は SQL Server でのテスト結果です。非クラスター化インデックスであり、クエリ対象の列が単一列インデックスである場合、ページングによって効率を向上させることはできません。

作成する テーブルTestPaging
 （
    id intアイデンティティ(1,1)、
名前 ヴァルチャー（50）、
    その他のvarchar (100)
 ）
 @i int = 0を宣言する
@i<100000の場合
始める 
入れる  TestPaging値に（NEWID()、NEWID()）
 @i = @i + 1と設定する
終わり  
  
作成する  TestPaging( name )のインデックスidx

実行プランからわかるように、IDを照会するためのサブクエリはフルテーブルスキャンです。

一致するインデックスでない限り、テーブル内のデータが比較的大きい場合は効率が向上します (サブクエリによるインデックス スキャンのコストは、完全なテーブル スキャンのコストよりも低くなります)。ただし、ページングが特定の列で頻繁にソートされる場合は、その列にクラスター化インデックスを作成しないのはなぜでしょうか。

要約する

ページング クエリの場合、後方に近づくほどクエリの速度は遅くなります。実際、B ツリー インデックスの場合、前方と後方は論理的に相対的な概念です。パフォーマンスの違いは、B ツリー インデックスの構造とスキャン方法に基づいています。

フィルタリング条件を追加すると、状況はさらに複雑になります。この問題の原理は SQL Server でも同じです。SQL Server でもテストしたので、ここでは繰り返しません。

現状では、ソート列やクエリ条件、データ配分が不確定なため、特定の手法で「最適化」を実現することは難しく、下手をすると不要な詳細が追加される副作用さえ生じます。

したがって、ページングの最適化を行う際には、具体的なシナリオに基づいて分析を行う必要があります。必ずしも 1 つの方法だけがあるわけではありません。実際のシナリオから乖離した結論はすべてナンセンスです。

この問題の詳細を理解することによってのみ、私たちはそれを簡単に処理することができます。

したがって、データの「最適化」に関する私の結論は、特定の問題の特定の分析に基づく必要があります。一連のルール (ルール 1、2、3、4、5) を要約して、それを他のルールに「適用」することは非常にタブーです。私はそれがあまり得意ではないので、あえて教義を要約しません。