各グループの最後のレコードを取得する - 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、バージョン 5.7.5 MySQL は、デフォルトで ONLY_FULL_GROUP_BY スイッチを有効にします (そのため、非決定的な GROUP BY クエリは無効になります)。さらに、GROUP BY 実装が更新されたため、スイッチが無効になっていてもソリューションが期待どおりに動作しない可能性があります。要チェックです。

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

私は、

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

index

を使用しています)。 ## 同じアイデアに基づいた他の解決策がいくつかあります:

基になるインデックスが 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 月のデータ ダンプ のコピーがあります。ベンチマークに使用します。Posts テーブルには 1,114,357 行があります。これは実行されています。 MySQL 5.0.75 (Macbook Pro 2.40GHz).

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

最初に、サブクエリで GROUP BY を使用して @Eric によって示された手法 を使用します: リーリー

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

ここで、

私の手法と LEFT JOIN: を使用して、同じクエリ結果を生成します。 リーリー

EXPLAIN

分析は、両方のテーブルがインデックスを使用できることを示しています: リーリー

これが私の

---

Posts

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

---

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