MySql ページングで limit+order by を使用する場合のデータ重複の解決策

まとめ

複雑な知識をシンプルに説明できることは重要です

学習の過程で、私たちは多くの資料、ビデオ、文書などを読みました。現在では資料やビデオが非常に多いため、知識のポイントを説明するためにさまざまなビデオ形式が使用されることがよくあります。プロモーションマーケティング以外にも、動画による説明が優れている人はたくさんいます。例えば、李永楽先生の短い動画レッスンでは、複雑な知識を黒板で簡単かつ徹底的に説明できます。プログラミングを学んでいる私たちにとって、知識のポイントを明確に説明する方法だけでなく、それをわかりやすく書く方法も学ぶ必要があります。

問題の説明

MySQL では、通常、ページ クエリを実行するために limit を使用します。たとえば、limit(0,10) は最初のページに 10 個のデータをリストすることを意味し、limit(10,10) は 2 番目のページをリストすることを意味します。ただし、limit と order by が一致すると、2 ページ目に進んだときに最初のページのレコードが再び表示される場合があります。

詳細は以下の通りです。

選択
  `投稿タイトル`,
  `投稿日`
から
  役職
どこ
  `post_status` = '公開'
注文する
  表示回数の降順
制限
  5, 5

上記の SQL クエリを使用すると、LIMIT 0,5 と同一のレコードが表示される可能性が高くなります。以下の方法を使用すると重複は発生しません。

選択
  *
から
  役職
どこ
  post_status = '公開'
注文する
  表示回数の降順
制限
  5, 5

ただし、post テーブルには多くのフィールドがあるため、これら 2 つのフィールドのみを使用し、post_content もチェックしたくありません。この問題を解決するには、次のように、ORDER BY の後に 2 つの並べ替え条件を使用します。

選択
  `投稿タイトル`,
  `投稿日`
から
  役職
どこ
  `post_status` = '公開'
注文する
  表示回数の降順、
  ID昇順
制限
  5, 5

論理的に言えば、MySQL のソートでは、デフォルトで主キー ID がソート条件として使用されます。つまり、view_count が等しい場合、主キー ID がデフォルトのソート条件として使用され、ID asc を追加する必要はありません。しかし実際には、MySQL で order by と limit を混在させると、ソートが混乱します。

問題を分析する

MySQL 5.6 では、オプティマイザは order by limit ステートメントに遭遇すると、つまり、優先キューを使用して最適化を行います。

優先キューを使用する目的は、インデックス順序が使用できない場合に、ソートして制限 n を使用する必要があると、ソート処理中に n レコードのみを保持する必要があることです。これにより、すべてのレコードをソートするオーバーヘッドは解決できませんが、ソートを完了するためにソート バッファーに少量のメモリしか必要ありません。

MySQL 5.6 で 2 ページ目に重複データが存在する問題が発生する理由は、優先キューがヒープソート方式を採用しているためです。ヒープソート方式は不安定なソート方式です。つまり、同じ値のソート結果が、読み込んだデータの順序と一致しない可能性があります。

MySQL 5.5 にはこの最適化がないため、この問題は発生しません。

つまり、この記事で言及されている問題は MySQL 5.5 には存在せず、この状況はバージョン 5.6 以降でのみ発生しました。

MySQL が SQL 言語を解釈するときの実行順序を見てみましょう。

（１）選択
(2) DISTINCT <選択リスト>
（３）<left_table>から
(4) <結合タイプ> JOIN <右テーブル>
（５）ON <結合条件>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <制限数>

実行順序は、form…where…select…order by…limit…となります。前述の優先キューにより、selectが完了した後、すべてのレコードがヒープソートで並べられます。order byを実行すると、view_count値が大きいレコードのみが前に移動します。

ただし、制限係数のため、ソート処理中に保持する必要があるレコードは 5 つだけです。view_count にはインデックス順序がないため、2 ページ目のデータを表示するときに、MySQL は表示されるレコードを取得します。したがって、ソート値が同じ場合、最初のソートはランダムになります。SQL を 2 回目に実行すると、結果は最初の結果と同じになります。

問題を解決する

（１）インデックスソートフィールド

フィールドにインデックスを追加すると、インデックスの順序に従って直接読み取り、ページ付けできるため、この問題を回避できます。

（２）ページングを正しく理解する

ページ区切りはソートに基づいており、数量の範囲に分割されています。ソートはデータベースによって提供される機能ですが、ページングは​​派生したアプリケーション要件です。

MySQL と Oracle の公式ドキュメントでは、limit n と rownum < n のメソッドが提供されていますが、ページングの概念は明確に定義されていません。

もう 1 つの重要な点は、上記のソリューションはユーザーにとってこの問題を軽減できますが、ユーザーの理解によっては、まだ問題があるということです。たとえば、このテーブルは頻繁に挿入され、ユーザーがクエリを実行すると、読み取りコミット分離レベルでは最初のページと 2 番目のページが依然として重複します。

したがって、ページングには常にこの問題があり、さまざまなシナリオではデータ ページングに非常に高い精度の要件はありません。

（３）データベースのソートに関するよくある問題

order by が追加されていない場合の並べ替えの問題

ユーザーが Oracle または MySQL を使用するとき、MySQL は常に整然としていますが、Oracle は非常に乱雑であることに気づきます。これは主に、Oracle がヒープ テーブルであり、MySQL がインデックス クラスター化テーブルであるためです。したがって、order by がない場合、データベースはレコードが返される順序を保証せず、各戻り値の一貫性も保証しません。

ページネーションの問題 ページネーションの重複の問題

前述のように、ページングは​​データベースが提供するソート機能から派生したアプリケーション要件であり、データベースはページングの重複を保証するものではありません。

NULL値と空文字列の問題

異なるデータベースでは、NULL 値と空の文字列の理解と処理が異なります。たとえば、Oracle の NULL と NULL 値を比較することはできません。それらは等しいわけでも等しくないわけでもなく、不明です。空の文字列を挿入すると、MySQL はそれを長さ 0 の空の文字列として扱いますが、Oracle は NULL 値を直接処理します。

これで、MySql ページングで limit+order by を使用するときにデータ重複問題を解決する方法に関するこの記事は終了です。MySql limit+order by ページングの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。