MySQL を使用して各グループの最後のレコードを取得する
P粉736935587
2023-08-20 11:48:53
<p><code>messages</code> というテーブルがあり、次のようなデータが含まれています: </p>
<pre class="brush:php;toolbar:false;">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</pre>
<p>クエリ <code>select * frommessages group by name</code> を実行すると、次の結果が得られます。
<pre class="brush:php;toolbar:false;">1 A A_data_1
4 B B_データ_1
6 C C_data_1</pre>
<p>次の結果を返すクエリはどれですか? </p>
<pre class="brush:php;toolbar:false;">3 A A_data_3
5 B B_データ_2
6 C C_data_1</pre>
<p>つまり、各グループの最後のレコードが返される必要があります。 </p>
<p>現在、これが私が使用しているクエリです: </p>
<pre class="brush:php;toolbar:false;">SELECT
*
から (選択
*
FROMメッセージ
ID DESC) AS x で注文
名前によるグループ化</pre>
<p>しかし、これは効率が悪いようです。同じ結果を達成する他の方法はありますか? </p>
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
UPD: 2017-03-31、MySQL バージョン 5.7.5 では、ONLY_FULL_GROUP_BY スイッチがデフォルトで有効になっています (したがって、非決定的な GROUP BY クエリは無効になっています)。さらに、GROUP BY 実装が更新されたため、スイッチが無効になっていてもソリューションが期待どおりに動作しなくなる可能性があります。検査が必要です。
Bill Karwin のソリューションは、グループ内の項目数が少ない場合にはうまく機能しますが、グループが大きくなると、ソリューションに約
#18684446n*n/2 n/2## かかるため、クエリのパフォーマンスが低下します。 #timesIS NULL比較。行と
usingindex1182グループを含む InnoDB テーブルでテストしました。このテーブルには機能テストのテスト結果が含まれており、(test_id, request_id)が主キーです。したがって、test_idはグループであり、各test_idの最後のrequest_idを探しています。は）。
基礎となるインデックスが BTREE インデックスの場合 (通常はこれに当てはまります)、各group_id- 内の最大の
各インデックスには追加の主キー列が暗黙的に含まれます (つまり、主キーはカバーインデックス内にあります)。以下のソリューションでは、主キーを直接操作します。あなたの場合、結果に主キー列を追加するだけです。 -
多くの場合、より安価な方法は、サブクエリで必要な行 ID を目的の順序で収集し、サブクエリの結果を ID と連結することです。 MySQL では、サブクエリ結果の各行の主キーに基づいて 1 回のフェッチが必要なため、サブクエリが最初に結合に配置され、行はサブクエリ内の ID 順に出力されます (明示的な ORDER BY を省略した場合)。結合の)-
- MySQL がインデックスを使用する 3 つの方法
は、詳細を学ぶのに適した記事です。(group_id, item_value)ペアは、各group_id のペアになります。インデックスを降順でたどった場合、最後の値は各group_id;の最初の値になります。インデックスでカバーされている値を読み取る場合、値はインデックスの順序で読み取られます。解決策 1
このソリューションは非常に高速で、1,800 万行のデータに対して約 0.8 秒かかります: リーリー
順序を昇順に変更したい場合は、サブクエリに入れて ID のみを返し、サブクエリとして他の列と結合します。リーリー
私のデータでは、この解決には約 1.2 秒かかります。解決策 2
これは別の解決策です。私のテーブルでは約 19 秒かかります: リーリー
また、テスト結果を降順で返します。フルインデックススキャンを実行するため速度は遅くなりますが、グループごとに最大 N 行を出力する方法のアイデアが得られます。
このクエリの欠点は、その結果をクエリによってキャッシュできないことです。MySQL 8.0 は、ほぼすべての一般的な SQL 実装と同様に、ウィンドウ関数をサポートするようになりました。この標準構文を使用すると、グループごとに最大 n 個のクエリを作成できます。 リーリー
MySQL マニュアルでは、この方法とグループ化された最大の行 を見つける他の方法を示しています。
以下は、私が 2009 年にこの質問に対して書いた元の回答です:私は次のように解決策を書きました:
リーリー
パフォーマンスに関しては、データの性質によっては、いずれかのソリューションの方が優れている場合があります。したがって、両方のクエリをテストし、データベースのパフォーマンスに基づいてより良い方を選択する必要があります。
たとえば、StackOverflow 8 月のデータ ダンプ のコピーがあります。ベンチマークに使用させていただきます。 Posts
指定されたユーザー ID (私のもの) の最新の投稿を検索するクエリを作成します。テーブルには 1,114,357 行のデータがあります。これは私の Macbook Pro 2.40GHz でMySQL 5.0.75 を実行しています。サブクエリで GROUP BY を使用する Eric
のテクニックを初めて使用しました:リーリーEXPLAIN
分析でも 16 秒以上かかります: リーリー今、LEFT JOINを使用しています
MY TECHNIQUEを使用すると、同じクエリ結果が生成されます: リーリーEXPLAIN
分析の結果、両方のテーブルでインデックスを使用できることがわかりました:リーリーこれは私の
Posts
テーブルの DDL です:リーリーコメント者注: 別のバージョンの MySQL、別のデータセット、または別のテーブル設計を使用して別のベンチマークを実行したい場合は、ご自身で自由に実行してください。上記のテクニックを実証しました。 Stack Overflow の目的は、ソフトウェア開発作業の方法を示すことであり、すべての作業を行うことではありません。