倚数のレコヌドがある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ファむルを配眮する堎所はありたせん。 䞀般的に...結論を出す力はありたせんでした:)



All Articles