多数のレコードがあるMySQLでのページナビゲーション

遅かれ早かれ、多くの大規模プロジェクトでは、投稿をページングするときにパフォーマンスの問題が発生します。 それらのいくつかは、表示可能なレコードの数を制限することでこの問題を解決します（たとえば、1000以下）。 これは許容できる解決策です。 ただし、この場合、サードパーティの検索エンジンによるサイトのインデックス作成に問題が発生する可能性があり、これが最大の脅威となります。 この記事では、単純な「前方...後方」（説明は簡単になります）を優先して、通常の「1..2..3..4 ..」ナビゲーションバーをすべて放棄しますが、これを実装しても問題はありません。最初のオプション。
この数字は誰にとっても異なり、ハードドライブの速度、メモリの量、データのキャッシュ量に大きく依存するため、ブレーキが表示されるのに十分な大きさのレコードの数を言うことでトピックを決定することはより正確にはなりませんその中などに。 しかし、あなたとあなたのサーバーが出力中のn番目のページが最初のページよりも重いと感じ、それをどうするかわからない場合-記事はあなたのためです。 しかし、最初に、ITが遅い理由を指で説明したいと思います。

ちなみに、テストは仮想マシンで行われ、ルートの下でDBMSを操作します。MySQLのバージョンは5.0.32です。

1データから始めましょう

テストのために、小さなプレートを作成し、何かで満たします。

CREATE TABLE items (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
height INT UNSIGNED NOT NULL DEFAULT 0,
width INT UNSIGNED NOT NULL DEFAULT 0,
price DECIMAL(10,2) NOT NULL DEFAULT 0.0,
title VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;


小さなPHPスクリプトがINSERT 100,000レコードを生成しました。 この種のデータ：
-INSERTのフィールドの順序：
高さ、幅、価格、タイトル
-フィールドのテンプレート：
$ val_tmpl = "\ t（％d、％d、％f、 'Item％d'）";
-テスト値（$ i = 1..100000）：
sprintf（$ val_tmpl、rand（0、120）、rand（0、220）、10 * rand（0、$ i）/ $ i、$ i）;

すべてをデータベースに入れます。 そして、あなたは始めることができます...

2従来のページネーション方法

COUNT（*）とLIMIT ... OFFSETが悪い理由をすでに知っている人は、この部分をスキップできます。

ナビゲーターを描画する前に、SELECT COUNT（*）... WHERE（selection_conditions）を実行します。 多くの場合、何らかの理由で、数百万のレコードがある場合でも、選択の条件によりインデックスを使用できるようになると、そのようなクエリは非常に迅速に機能します。 実験してみましょう。 高さが100を超えるレコードの数を選択します。まず、高さフィールドにインデックスがない場合に何が起こるかを見てみましょう。

フラッシュステータス;

SELECT SQL_NO_CACHE COUNT（*）FROM items WHERE height> 100;
+ ---------- +
| カウント（*）|
+ ---------- +
| 16405 |
+ ---------- +
セット内の1行（0.09秒）

「handler％」のようなステータスを表示します。

最後のコマンドは、要求を満たすためにDBMSが実行する必要のある数とアクションを示します。 インデックスがないため、MySQLはテーブルから直接データを読み取る必要があったため、次の行に興味があります。
...
| Handler_read_rnd_next | 100001 |
...

つまり、MySQLはクエリに一致するすべてを見つけるために、100001の移動操作を次のレコードに実行する必要がありました。

以下のどこでも、各SELECTの前はFLUSH STATUSの実行を意味し、後はSHOW STATUS LIKE 'handler％'を意味します。

インデックスはどのように役立ちますか？

ALTER TABLEアイテムADD INDEX height_idx（高さ）;

SELECT SQL_NO_CACHE COUNT（*）FROM items FORCE INDEX（height_idx）WHERE height> 100;
+ ---------- +
| カウント（*）|
+ ---------- +
| 16405 |
+ ---------- +
セット内の1行（0.04秒）

この場合、インデックスが使用されたため、Handler_read_rnd_nextは0になりますが、
...
| Handler_read_next | 16405 |
...
つまり、インデックスを使用すると、最初に必要なレコードのみを計算できますが、それでもすべてを調べる必要があります。 魔法はありません; MySQLはクエリに一致するレコードの数をどこにも保存しません。 したがって、クエリ条件に一致する数百万のレコードがある場合、COUNTの動作は非常に遅くなります。

第二の瞬間。 LIMIT ... OFFSET。 同じ実験。 5つのレコードを要求します。

SELECT SQL_NO_CACHE * FROMアイテムFORCE INDEX（height_idx）WHERE height> 100 LIMIT 5;
...
セット内の5行（0.00秒）
...
| Handler_read_next | 4 |
...
すべてが論理的なようです。 そして、16401から始まる他の5つのレコードを返すようにお願いします。

SELECT SQL_NO_CACHE * FROM items FORCE INDEX（height_idx）WHERE height> 100 LIMIT 16400、5;
...
セット内の5行（0.13秒）

サンプリング時間が大幅に増加していることがわかります。 ステータスを確認します。
...
| Handler_read_next | 16404 |
...
つまり、MySQLは16405をすべて読み取り、その後のみ不要なものをすべて破棄しました。
になる方法

3なる方法

だから。 10個のエントリを表示し、ナビゲーションメニューを描画する必要があります。 結果を出すために必要な記録を得るために、MySQLは多くの余分な作業を費やすことに気付きました。 これを回避する唯一の方法は、選択条件を変更して、正しいものにまっすぐ進むことです。
すべてを単純な例で考えてみましょう。レコードをIDでソートします。 この場合、停止したレコードのIDをリンクと共に渡す必要があります。 そして、id = 10のレコードで停止します。 つまり、次のページへのリンクのパラメーターでは、10を渡す必要があります。したがって、2ページ目では、要求は次のようになります。

SELECT SQL_NO_CACHE id from items WHERE id> 10 ORDER BY id LIMIT 10;

ちなみに、どちらの場合も、Handler_read_nextは9になります。つまり、リクエストに一致する最初のレコードにジャンプし（インデックスのおかげで）、次のレコードに9回移行しました。 最も重要なことは、10の条件で何を代入しても、SHOW STATUSの結果として常に同じものが表示され、そのようなリクエストの実行時間は現在の場所に依存せず、依存するのは依存することですどのくらい、何を選ぶか。
意味を理解してください。 次に、ナビゲーションメニューをどうするかを決めてから、さらに複雑な状況に進みましょう。 urlでnext、previous、lastキーワードを使用します。 どのような場合にリンクが「進む」、「戻る」、「最後」と表示されますか？
次のページ（次のページのリクエスト）が来るたびに、10個のレコードではなく、リクエストパラメータで渡されたidから始まる11個のレコードを選択します。 11個のレコードを返した場合、10番目のレコードのIDで前方へのリンクを表示し、11番目を折りたたむ必要があります。 返されるエントリが11未満の場合、フォワードリンクは表示されません。 同時に、常に（次の場合は常に）選択からの最初のレコードのIDで（前の）リンクを表示します。 「先頭へ」および「最後」へのリンクは、それぞれ「戻る」および「進む」とともに常に表示されます。 つまり、「戻る」を表示することにした場合、「最初に」表示する必要があります。
前のレコードが届くたびに（前のページへのリクエスト）、IDがリクエストで指定されたよりも小さい11のレコードを選択し、逆順にソートします。 同じ：11個のレコードが返された場合、「戻る」リンクが表示されます。 常に前方リンクを表示します。
私がはっきりと書いてくれたらと思います...
「last」というクエリを受け取った場合はどうなりますか？ 最新のものから始めて、10個のエントリを表示するだけです。 それは：

アイテムからIDを選択IDで並べ替えDESC LIMIT 10;

ユーザーの1人が、数百、または数千のページを無駄にして、最終的に10エントリではなく最初のページを見つけると非難する力があると思いますか？ たとえそれが十分であったとしても、彼はあまりにも長い間揺れたと答えることができます...

前の例は単純で、IDは一意です。 しかし、フィールドでソートする必要があり、その値を繰り返すことができる場合はどうでしょうか？ たとえば、この場合の高さ。 簡単なクエリにより、テーブルでは各高さの値が約800回発生することがわかりました。 照会パラメーターで最後に表示された高さを渡すだけでは不十分です。 同じIDが役立ちます。 レコードを高さでソートするように頼まれますが、それは後でIDでレコードをソートするのを止めませんか？
これを行うには、新しいインデックスが必要です。

ALTER TABLE items ADD KEY height_id_idx（height、id）;

最初のページのリクエストは次のようになります。

SELECT SQL_NO_CACHE id、height FROM items ORDER BY height、id LIMIT 10;

私の結果では、最後のエントリの高さは0、IDは1174です。 そのため、次のページを渡す必要があります。 たとえば、next_0_1174またはnext / 0/1074-必要に応じて。
ここで、高さが0より大きいか、高さが0で、id> 1174のレコードを選択する必要があります（このために、追加の並べ替えを行いました）。
それは：

SELECT SQL_NO_CACHE * FROMアイテムWHERE（height> 0）OR（height = 0 AND id> 1174）ORDER BY height、id LIMIT 10;

これがなぜそうなのかを説明する必要がないことを願っています。 ステータスはまだ9ステップ進むだけです。
したがって、他のソートを追加できます。 たとえば、価格と高さでソートされたすべてのレコードを表示する場合、クエリは次のようになります。

SELECT SQL_NO_CACHE * FROM WHERE WHERE WHERE（price> 5）OR（price = 5 AND height> 0）AND（price = 5 AND height = 0 AND id> 1174）ORDER BY価格、高さ、id LIMIT 10;

すべての必要なデータを転送し、それを正しく処理し、リクエストに代入するだけです。 そして、インデックスを忘れないでください。

4結果の数をどうするか

見つかった結果の数をユーザーに表示する場合はどうなりますか？ 私たちは多数について話しているので、誰かが私たちをチェックすることはまずありません。 同じGoogleは、クエリに一致する1,000,000ページを検出したことを伝えることができますが、1,000を超えるページは表示されません。 私たちも、結果の数をおよそのみに渡すことができます。 どこで入手でき、どのように評価するのですか？ リクエストを実行したことを忘れないでください：

SELECT SQL_NO_CACHE COUNT（*）FROM items FORCE INDEX（height_idx）WHERE height> 100;

そして、これをしましょう：
EXPLAIN SELECT SQL_NO_CACHE * FROM items FORCE INDEX（height_idx）WHERE height> 100;

その結果、次のような結果が得られます。
+ ---- + ------------- + ------- + ------- + -------------- -+ ------------ + --------- + ------ + ------- + ---------- --- +
| id | select_type | テーブル| タイプ| possible_keys | キー| key_len | ref | 行| エクストラ|
+ ---- + ------------- + ------- + ------- + -------------- -+ ------------ + --------- + ------ + ------- + ---------- --- +
| 1 | シンプル| アイテム| 範囲| height_idx | height_idx | 4 | NULL | 22616 | whereを使用する|
+ ---- + ------------- + ------- + ------- + -------------- -+ ------------ + --------- + ------ + ------- + ---------- --- +

行の列には、表示する必要がある推定レコード数が表示されます。 22616および16405-違いはまったく大きくありません。 〜20,000に丸めることができます。 します。 たとえば、サブクエリや結合を使用すると、EXPLAINは複数の行を返すことに注意してください。 それらはすべて読み取られ、行の値で乗算される必要があります。

おわりに

この問題はすでにハブで簡単にカバーされていますが、そのような詳細ではありません。
記事は予想以上に判明しましたが、テキストはクエリと結果の挿入で希釈されています。 現時点では、データの生成に使用されるスクリプト+ sqlファイルを配置する場所はありません。 一般的に...結論を出す力はありませんでした:)