mysql 5.6 order by limitソートページングデータ重複問題

mysql公式サイト関連bug説明
https://bugs.mysql.com/bug.php?id=69732
https://mariadb.com/kb/en/filesort-with-small-limit-optimization/
MySQL 5.6 has an optimization for  ORDER BY ...LIMIT n  queries. When  n  is sufficiently small, the optimizer will use a priority queue for sorting. The alternative is, roughly speaking, to sort the entire output and then pick only first  n  rows.
関連記事:
https://www.burnison.ca/notes/fun-mysql-fact-of-the-day-priority-order
 
以下は転載内容です.
0問題の説明
MySQLでは、通常、limit(0,10)は最初のページをリストする10のデータを表し、limit(10,10)は2番目のページをリストするlimitを使用します.しかし、limitがorder byに出会ったとき、2ページ目にめくったとき、意外にも1ページ目の記録が現れた.
具体的には以下の通りです.

SELECT `post_title`,`post_date` FROM post WHERE `post_status`='publish' ORDER BY view_count desc LIMIT 5,5

上記のSQLクエリを使用すると、LIMIT 0,5と同じレコードが表示される可能性があります.次の方法を使用すると、重複することはありません.

SELECT * FROM post WHERE post_status='publish' ORDER BY view_count desc LIMIT 5,5

しかし、postテーブルのフィールドが多いので、この2つのフィールドだけを使って、post_をcontentも調べました.この場合、ORDER BYの後に2つのソート条件を使用して、この問題を解決します.

SELECT `post_title`,`post_date` FROM post WHERE `post_status`='publish' ORDER BY view_count desc,ID asc LIMIT 5,5

本来ならばMySQLのソートはデフォルトではプライマリ・キーIDをソート条件としている、つまりview_countが等しい場合、プライマリ・キーIDはデフォルトのソート条件として、ID ascを一挙に追加する必要はありません.
しかし、事実上、MySQLがorder byとlimitを混用すると、ソートの混乱が発生した.
 
1問題の分析
MySQL 5.6のバージョンでは、オプティマイザはorder by limit文に遭遇したときにpriority queueを使用する最適化を行いました.
priority queueを使用する目的は、インデックス秩序性が使用できない場合、ソートしてlimit nを使用する場合は、ソート中にn個のレコードを保持するだけでよいため、すべてのレコードをソートする必要があるオーバーヘッドを解決することはできませんが、sort bufferの少量のメモリだけでソートを完了することができます.
MySQL 5.6で2ページ目のデータ重複の問題が発生したのは、priority queueがスタックソートのソート方法を使用しているためであり、スタックソートは不安定なソート方法であり、同じ値でソートされた結果と読み出されたデータの順序が一致しない可能性があるためである.
MySQL 5.5はこの最適化がないので、この問題は発生しません.
つまり、MySQL 5.5は本明細書で説明した問題がなく、5.6以降にこのような状況が発生した.
 
MySQLがsql言語を解釈するときの実行順序を見てみましょう.

(1)     SELECT 
(2)     DISTINCT 
(3)     FROM 
(4)      JOIN 
(5)     ON 
(6)     WHERE 
(7)     GROUP BY 
(8)     HAVING 
(9)     ORDER BY 
(10)    LIMIT 

実行順はform… where… select… order by… limit…の順で、
上記priority queueのため、selectが完了した後、すべてのレコードはスタックソートの方法で並べられ、order byを行う場合、view_のみをcount値の大きい前へ移動します.
しかしlimitの要因のため、ソート中に5つのレコードに残すだけでいい、view_countはインデックス秩序性を備えていないので、2ページ目のデータが表示されるとmysqlはどれを見てもどちらを取ります.
したがって、並べ替え値が同じ場合、1回目の並べ替えは任意に並べ替えられ、2回目にsqlが実行される場合、その結果は1回目の結果と同じであるべきである.
 
2解決方法

インデックスソートフィールド
フィールドにインデックスを追加すると、インデックスの順序に従って直接読み取り、ページングされ、この問題を回避できます.

ページの正確な理解
ページングはソートに基づいて作成され,数量範囲分割が行われた.ソートはデータベースが提供する機能であり、ページングは派生したアプリケーション要件です.
MySQLとOracleの公式ドキュメントではlimit nとrownumしたがって、ページングには常にこの問題があり、異なるシーンではデータのページングに非常に正確性の要求がありません.

一般的なデータベース並べ替え問題order byを追加しない場合の並べ替え問題
ユーザーがOracleまたはMySQLを使用している場合、MySQLは常に整列しているのに、Oracleは混乱していることがわかります.これは主にOracleがスタック・テーブルであり、MySQLがインデックス・クラスタリング・テーブルであるためです.したがってorder byがない場合、データベースはレコードが返す順序性を保証せず、毎回一致することを保証しません.
ページング問題ページング繰り返しの問題
前述したように、ページングは、データベースが提供するソート機能に基づいて派生したアプリケーション要件であり、データベースはページングの重複問題を保証しない.
NULL値と空白列の問題
異なるデータベースでは、NULL値と空白列の理解と処理が異なります.たとえば、Oracle NULL値とNULL値は比較できません.等しいわけでもないし、等しくないわけでもないし、未知です.一方、空白列の場合、MySQLは挿入時に文字列長が0の空白列であり、OracleはNULL値処理を直接行います.