MySQL を使用して各グループの最後のレコードを取得する

Question

messages というテーブルがあり、次のようなデータが含まれています:

ID 名 Other_Columns
------------------------
1A A_データ_1
2A A_データ_2
3A A_データ_3
4 B B_データ_1
5 B B_データ_2
6 C C_data_1

クエリ select * frommessages group by name を実行すると、次の結果が得られます。

1 A A_data_1
4 B B_データ_1
6 C C_data_1

次の結果を返すクエリはどれですか?

3 A A_data_3
5 B B_データ_2
6 C C_data_1

つまり、各グループの最後のレコードが返される必要があります。

現在、これが私が使用しているクエリです:

SELECT
  *
から (選択
  *
FROMメッセージ
ID DESC) AS x で注文
名前によるグループ化

しかし、これは効率が悪いようです。同じ結果を達成する他の方法はありますか?

Answer 1

UPD: 2017-03-31、MySQL バージョン 5.7.5 では、ONLY_FULL_GROUP_BY スイッチがデフォルトで有効になっています (したがって、非決定的な GROUP BY クエリは無効になっています)。さらに、GROUP BY 実装が更新されたため、スイッチが無効になっていてもソリューションが期待どおりに動作しなくなる可能性があります。検査が必要です。

Bill Karwin のソリューションは、グループ内の項目数が少ない場合にはうまく機能しますが、グループが大きくなると、ソリューションに約 n*n/2 n/2## かかるため、クエリのパフォーマンスが低下します。 #timesIS NULL比較。

#18684446

行と 1182 グループを含む InnoDB テーブルでテストしました。このテーブルには機能テストのテスト結果が含まれており、(test_id, request_id) が主キーです。したがって、test_id はグループであり、各 test_id の最後の request_id を探しています。 Bill のソリューションは私の Dell e4310 で数時間実行されていますが、いつ完了するかわかりません。ただし、カバー インデックスで動作します (したがって、EXPLAIN に示されている

usingindex

は）。 私も同じ考えに基づいたいくつかの解決策を持っています:

基礎となるインデックスが BTREE インデックスの場合 (通常はこれに当てはまります)、各

group_id
• 内の最大の (group_id, item_value) ペアは、各 group_id のペアになります。 インデックスを降順でたどった場合、最後の値は各 group_id; の最初の値になります。 インデックスでカバーされている値を読み取る場合、値はインデックスの順序で読み取られます。
各インデックスには追加の主キー列が暗黙的に含まれます (つまり、主キーはカバーインデックス内にあります)。以下のソリューションでは、主キーを直接操作します。あなたの場合、結果に主キー列を追加するだけです。
多くの場合、より安価な方法は、サブクエリで必要な行 ID を目的の順序で収集し、サブクエリの結果を ID と連結することです。 MySQL では、サブクエリ結果の各行の主キーに基づいて 1 回のフェッチが必要なため、サブクエリが最初に結合に配置され、行はサブクエリ内の ID 順に出力されます (明示的な ORDER BY を省略した場合)。結合の)
MySQL がインデックスを使用する 3 つの方法

は、詳細を学ぶのに適した記事です。

解決策 1

このソリューションは非常に高速で、1,800 万行のデータに対して約 0.8 秒かかります: リーリー

順序を昇順に変更したい場合は、サブクエリに入れて ID のみを返し、サブクエリとして他の列と結合します。

リーリー

私のデータでは、この解決には約 1.2 秒かかります。

解決策 2

これは別の解決策です。私のテーブルでは約 19 秒かかります: リーリー

また、テスト結果を降順で返します。フルインデックススキャンを実行するため速度は遅くなりますが、グループごとに最大 N 行を出力する方法のアイデアが得られます。

このクエリの欠点は、その結果をクエリによってキャッシュできないことです。

Answer 2

MySQL 8.0 は、ほぼすべての一般的な SQL 実装と同様に、ウィンドウ関数をサポートするようになりました。この標準構文を使用すると、グループごとに最大 n 個のクエリを作成できます。 リーリー

MySQL マニュアルでは、この方法と

グループ化された最大の行 を見つける他の方法を示しています。

以下は、私が 2009 年にこの質問に対して書いた元の回答です:

---

私は次のように解決策を書きました:

リーリー

パフォーマンスに関しては、データの性質によっては、いずれかのソリューションの方が優れている場合があります。したがって、両方のクエリをテストし、データベースのパフォーマンスに基づいてより良い方を選択する必要があります。

たとえば、

StackOverflow 8 月のデータ ダンプ のコピーがあります。ベンチマークに使用させていただきます。 Posts テーブルには 1,114,357 行のデータがあります。これは私の Macbook Pro 2.40GHz で MySQL 5.0.75 を実行しています。

指定されたユーザー ID (私のもの) の最新の投稿を検索するクエリを作成します。

サブクエリで GROUP BY を使用する Eric のテクニックを初めて使用しました: リーリー

EXPLAIN分析でも 16 秒以上かかります: リーリー

今、LEFT JOINを使用していますMY TECHNIQUEを使用すると、同じクエリ結果が生成されます: リーリー

EXPLAIN分析の結果、両方のテーブルでインデックスを使用できることがわかりました: リーリー

---

これは私の

Posts テーブルの DDL です: リーリー

---

コメント者注: 別のバージョンの MySQL、別のデータセット、または別のテーブル設計を使用して別のベンチマークを実行したい場合は、ご自身で自由に実行してください。上記のテクニックを実証しました。 Stack Overflow の目的は、ソフトウェア開発作業の方法を示すことであり、すべての作業を行うことではありません。