Mysqlのソート機能の詳細

1. 問題シナリオ

新しい取引記録のエクスポート機能が開始されました。ロジックはシンプルで、クエリ条件に基づいて該当するデータをエクスポートします。データ量が多いため、一度に1000件のデータをデータベースに照会するページド・クエリを使用します。

セルフテストもテスト環境も問題なく動作し、本番稼動後はエクスポートされた データに重複したレコードがある .

当初はビジネスロジックの問題だと考えられていましたが、リ Review のコードを書き直しましたが、やはり原因はわかりませんでした。最終的に、私は SQL ステートメントを使用して、データをエクスポートしてみると SQL 文のクエリ結果が順番に表示されない。

2. 原因分析

クエリ文の先頭は create_time で逆順にソートされます。 limit ページングは、通常であれば問題ありません。しかし、ビジネスの並行性が高い場合、ページングが原因で create_time 同じ値が大量にある場合、その値に基づいて limit のページネーションでは、無秩序の問題があります。

発生するシナリオは と create_time 並び順 create_time が同じ値であれば、ソートされます。 limit ページングされたデータが順番通りに表示されない。

例えば、クエリ 1000 のデータで、一括して create_time のレコードは、すべて " という値を持っています。 2021-10-28 12:12:12 同時に作成されたこれらのデータの一部が1ページ目に、一部が2ページ目に表示される場合、2ページ目のデータを照会すると、1ページ目ですでにチェックされたデータが表示されることがあります。

つまり、ある瞬間には1ページ目に現れ、次の瞬間には2ページ目に現れるというように、データが行ったり来たりするため、エクスポートされるデータの一部が重複し、一部が欠落するという現象が発生するのです。

Mysql5.7と8.0の公式ドキュメントを確認すると、以下のように記述されています。

複数の行が ORDER BY カラムに同じ値を持つ場合、サーバはそれらの行を任意の順番で返すことができます。 つまり、それらの行のソート順は、順不同のカラムに対して非決定的なのです。

以上のまとめ。 を使用する場合 ORDER BY カラムをソートする際に、対応する( ORDER BY )列には、同じデータが複数行ある場合( Mysql ) サーバはこれらの行を任意の順序で返し、全体の実行計画によって異なる順序で返すこともあります。

簡単に言うと ORDER BY クエリデータが、もし ORDER BY カラムに同じデータが複数行ある場合は Mysql はランダムに返されます。そのため、ソートを使用しているにもかかわらず、ぐちゃぐちゃになってしまうことがあります。

3. 解決方法

上記の問題に対する基本的な解決策の考え方です。 避ける ORDER BY のカラムは値が重複している。したがって、ID や他のソートされたカラムなど、他のディメンションを追加することができます。

select * from tb_order order by create_time ,id desc;

このように create_time が同じであれば、異なるはずのidでソートされることになり、上記の問題は発生しなくなります。

4. 知識の拡大

実は、上記のコンテンツは Mysql 公式サイトでもわかりやすく説明されていますし、例も挙げられています。以下、公式サイトと例を簡単にまとめます。

4.1 限定クエリの最適化

結果セットの一部だけをクエリする場合、すべてのデータをクエリしてから不要なデータを破棄するのではなく、リミット条件を使用して制限する必要があります。

を使用せずに having の条件では Mysql に影響を与える可能性があります。 limit 条件付きで最適化される。

• 数個のデータのみを問い合わせる場合には limit ということで Mysql はインデックスを使用することができますが、通常は Mysql はフルテーブルスキャンです。
• もし limit row_count と order by を組み合わせて使用すると、Mysql は最初の row_count は、結果セット全体をソートするのではありません。この時点でインデックスに基づく操作を行っている場合は、この方が高速です。どうしてもソートしなければならない場合は、結果セットから row_count の結果セットが見つかる前に、対象となる結果の一部または全部がソートされます。しかし row_count が見つかれば、残りはソートされない。この特徴の現れとして、先に述べたように、制限ありの問い合わせと制限なしの問い合わせで返される結果の順序が異なることが挙げられる。
• もし limit row_count と distinct が一緒に使用されている場合、Mysql は row_count 結果セットで一意な行を見つけた直後に
• 場合によっては、インデックスを順番に読み込んで(あるいはインデックスをソートして)、インデックスが変わるまでサマリーを計算することでグループバイを実装できることがあります。この場合は limit row_count は不要な group by の値を指定します。
• MySQL が必要な数の行をクライアントに送信すると、以下の場合を除いて、クエリは中断されます。 SQL_CALC_FOUND_ROWS . その場合は SELECT FOUND_ROWS() で行数を取得します。
• LIMIT 0 はすぐに空のセットを返すので、SQL の妥当性をチェックするためによく使われます。また、アプリケーションの結果セットの種類を取得するためにも使用できます。Mysql クライアントでは --column-type-info を使用して、結果カラムのタイプを表示します。
• クエリの解析にテンポラリテーブルが使用されている場合は Mysql を使用することになります。 limit row_count を使って、どれだけのスペースが必要かを計算します。
• もし order by が使用されておらず、かつ制限条件がある場合、オプティマイザはマージファイルの使用を避け、代わりにメモリ内の filesort オペレーションを使用して、メモリ内の行をソートします。
  

理解される limit を、この記事の焦点である limit row_count と order by 機能を組み合わせて使用する。

4.2 リミットとオーダーバイの併用

上記2回目の記事で紹介したように limit row_count と order by 複合表示の特徴として、結果が返される順番が不定であることが挙げられる。実行計画に影響を与える要因のひとつが limit というように、実行プランに limit であり limit 同じクエリ文を実行しても、結果が異なる順序で返されることがあります。

次の例では、id とレーティングが不定であるのに対し、category 列に基づいてソートされたクエリを実行しています。

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |

| 7 | 3 | 2.7 |
+----+----------+--------+

クエリ文に制限がある場合、同じカテゴリー値を持つデータに影響が出る場合があります。

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+ ----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 4 | 2 | 3.5 |
| 3 | 2 | 3.7 |
| 6 | 2 | 3.5 |
+----+----------+--------+

ID 3 と 4 の結果の位置が変わったところ。

実際には、クエリの結果を連続したものにすることが重要な場合が多く、その場合は他のカラムを導入して結果を連続したものにする必要があります。

上記の例でidを導入すると、クエリ文と結果は次のようになります。

mysql> SELECT * FROM ratings ORDER BY category, id;
+ ----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |

| 7 | 3 | 2.7 |
+ ----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
+----+----------+--------+

idカラムのソートが追加されると、たとえ category が同じであれば、無秩序の問題は発生しません。これは、私たちの当初の解決策と同じです。

5. まとめ

元々、実務でたまにある落とし穴を経て、雑談で Mysql に対して limit ビジネス要件を満たし、ビジネスロジックのエラーを回避するソリューションを提供しながら、クエリステートメントを最適化することができます。

多くの方が order by と limit ステートメントを使用することができます。 Mysql これらの最適化機能のうち、データ量が発表のトリガーにならないことを除けば、すでに穴埋めされている可能性が高いです。

Mysqlのソート機能の詳細については、今回で終了です。Mysqlのソート機能の詳細については、スクリプトハウスの過去記事を検索するか、以下の関連記事を引き続き閲覧してください。