MySQL ソート機能の詳細

1. 問題のシナリオ

新しいトランザクションレコードのエクスポート機能がリリースされました。ロジックは非常にシンプルで、クエリ条件に基づいて対応するデータをエクスポートします。データ量が多いため、データベースを照会する際にはページネーションクエリが使用され、1 回につき 1,000 個のデータが照会されました。

セルフテストは正常、テスト環境は正常、オンラインになった後に操作フィードバックによってエクスポートされたデータには重複レコードがあります。

当初はビジネスロジックの問題だと思っていたので、コードを再度Reviewが、それでも問題の原因は見つかりませんでした。結局、 SQL文を取り出して個別に実行し、データをエクスポートして比較する必要がありました。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 ページ目で照会したデータが表示されることがあります。

つまり、データは前後にジャンプし、しばらくの間は最初のページに表示され、しばらくの間は 2 番目のページに表示されるため、エクスポートされたデータの一部が重複し、一部が欠落することになります。

MySQL 5.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でソートされ、idは必ず異なるものとなるため、上記のような問題は発生しなくなります。

4. 知識を広げる

実際、上記の内容はMysqlの公式サイトでもわかりやすく解説されており、例も挙げられています。以下は公式サイトの内容と事例の簡単な概要です。

4.1 クエリの最適化を制限する

結果セットの一部のみをクエリする場合は、すべてのデータをクエリして不要なデータを破棄するのではなく、制限条件によって制限する必要があります。

所有条件が使用されていない場合、MySQL は制限条件を最適化することがあります。

少数のレコードのみをクエリする必要がある場合は、 Mysqlインデックスを使用できるようにlimitを使用することをお勧めします。一方、 Mysql通常、テーブル全体をスキャンします。
limit row_countとorder byを一緒に使用すると、MySQL は結果セット全体をソートするのではなく、最初のrow_count結果セットが見つかるとすぐにソートを停止します。この時にインデックスを元に操作すると速度が速くなります。ファイルの並べ替えが必要な場合は、 row_count結果セットが見つかる前に、条件に該当する結果の一部またはすべてが並べ替えられることがあります。ただし、 row_count結果が見つかると、残りはソートされません。この機能の現れの 1 つは、前述のように、制限ありまたは制限なしでクエリを実行した場合に、返される結果の順序が異なる可能性があることです。
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 limitとorder byを一緒に使う

上記の 2 番目のポイントで述べたように、 limit row_countとorder byの組み合わせの特徴の 1 つは、結果が返される順序が不確実であることです。実行プランに影響を与える要因の 1 つはlimitです。したがって、同じクエリステートメントをlimitで実行すると、返される結果の順序が異なる場合がありますlimit

次の例では、ID と評価は不明ですが、カテゴリ列に基づいて並べ替えクエリが実行されます。

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| ID | カテゴリー | 評価 |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
+----+----------+--------+

クエリステートメントに制限が含まれている場合、同じカテゴリ値を持つデータに影響する可能性があります。

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| ID | カテゴリー | 評価 |
+----+----------+--------+
| 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 | カテゴリー | 評価 |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| ID | カテゴリー | 評価 |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
+----+----------+--------+

id列のソートを追加すると、 categoryが同じであっても乱れの問題がないことがわかります。これは当社の当初の解決策と一致しています。