MySQL: 各グループの最新エントリを取得する

Question

以下に示すデータを含むテーブル messages があります:

リーリー

クエリ select * frommessages group by name を実行すると、結果は次のようになります:

リーリー

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

リーリー

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

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

リーリー

しかし、これは非常に非効率的だと思われます。同じ結果を達成する他の方法はありますか?

Answer 1

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

Bill Karwin の上記の解決策は、グループ内の項目数がかなり少ない場合にはうまく機能しますが、グループがかなり大きい場合、解決策には約 n*n/2 n/ 2 ## が必要となるため、クエリのパフォーマンスが低下します。 #IS NULL のみを比較します。

#18684446

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

同じアイデアに基づいた他の解決策がいくつかあります:

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

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

MySQL がインデックスを使用する 3 つの方法

は、詳細を理解するのに役立つ優れた記事です。

解決策 1

これは信じられないほど高速で、1,800 万行で約 0.8 秒かかります。 リーリー

順序を ASC に変更する場合は、ID のみを返すサブクエリに配置し、それをサブクエリとして使用して残りの列を結合します。 リーリー

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

解決策 2

これは、私のテーブルで約 19 秒かかった別の解決策です: リーリー また、テストを降順で返します。フルインデックススキャンを実行するため、かなり遅くなりますが、各グループの最大 N 行を出力する方法のアイデアが得られます。

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

Answer 2

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

リーリー

この方法とグループ化された行の最大数を見つける他の方法については、MySQL マニュアルで説明されています。

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

---

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

リーリー

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

たとえば、

StackOverflow 8 月のデータ ダンプ のコピーがあります。ベンチマークの目的で使用します。 投稿 テーブルには 1,114,357 行があります。私の Macbook Pro 2.40GHz の MySQL 5.0.75 で実行しています。

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

まず、@Eric が示した手法 をサブクエリ GROUP BY: で使用します。 リーリー

Even

EXPLAIN分析には 16 秒以上かかります: リーリー

さあ、LEFT JOIN を使用するためのヒントを使用してください: リーリー

EXPLAIN

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

Posts

---

テーブルの DDL です:

リーリー

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

---