MySQL で重複を削除するには、distinct または group by を使用する必要がありますか?

序文

group by と distinctive のパフォーマンス比較について: インターネット上の結論は次のとおりです。distinct はインデックスなしの少量データではパフォーマンスが良く、group by は大量データではパフォーマンスが良くなります。インデックス付きの group by の方がパフォーマンスが良いです。インデックスを調べるとき、グループ化の種類が少ないほど、distinct は高速になります。導き出された結論をオンラインで検証します。

準備フェーズ中にクエリキャッシュを無効にする

MySQL でクエリ キャッシュが設定されているかどうかを確認します。テスト結果に影響を与えないようにするには、クエリ キャッシュをオフにする必要があります。

'%query_cache%' のような変数を表示します。 

クエリ キャッシュが有効かどうかを確認します。これは、 query_cache_typeとquery_cache_sizeによって決まります。

• 方法 1: クエリ キャッシュをオフにするには、my.ini を見つけてquery_cache_type :\ProgramData\MySQL\MySQL Server 5.7\my.ini 構成ファイルを変更し、 query_cache_type=0或2変更する必要があります。
• 方法 2: query_cache_sizeを 0 に設定し、次のステートメントを実行します。

グローバル query_cache_size を 0 に設定します。

方法 3: クエリ キャッシュをオフにしたくない場合は、 RESET QUERY CACHE使用することもできます。

現在のテスト環境では、query_cache_type=2 はオンデマンドのクエリ キャッシュを意味します。デフォルトのクエリ モードはキャッシュしません。キャッシュが必要な場合は、クエリ ステートメントにsql_cache追加する必要があります。

データ準備

テーブルt0には少量種類少が格納されます。

存在する場合はテーブルを削除します t0;
テーブルt0を作成(
id bigint 主キー auto_increment、
varchar(255) は null ではない
) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_bin;
1
2
3
4
5
プロシージャ insert_t0_simple_category_data_sp を削除します。
区切り文字 //
プロシージャ insert_t0_simple_category_data_sp(IN num int) を作成します。
始める
@i = 0 に設定します。
@i < num の間
	t0(a) に値を挿入します(truncate(@i/1000, 0));
 @i = @i + 1 と設定します。
終了しながら;
終わり
//
insert_t0_simple_category_data_sp(100000) を呼び出します。

テーブルt1には少量種類多格納されます。

存在する場合はテーブル t1 を削除します。
t0 のようにテーブル t1 を作成します。
1
2
プロシージャ insert_t1_complex_category_data_sp を削除します。
区切り文字 //
プロシージャ insert_t1_complex_category_data_sp(IN num int) を作成します。
始める
@i = 0 に設定します。
@i < num の間
	t1(a) に値を挿入します(truncate(@i/10, 0));
 @i = @i + 1 と設定します。
終了しながら;
終わり
//
insert_t1_complex_category_data_sp(10000) を呼び出します。

t2 テーブルには大量種類多

存在する場合はテーブルを削除します t2;
t1 と同じようにテーブル t2 を作成します。
1
2
プロシージャ insert_t2_complex_category_data_sp を削除します。
区切り文字 //
プロシージャ insert_t2_complex_category_data_sp(IN num int) を作成します。
始める
@i = 0 に設定します。
@i < num の間
	t1(a) に値を挿入します(truncate(@i/10, 0));
 @i = @i + 1 と設定します。
終了しながら;
終わり
//
insert_t2_complex_category_data_sp(5000000) を呼び出します。

テストフェーズ

少量のデータを検証する

インデックスされていない

プロファイリングを 1 に設定します。
t0 から別の a を選択する。
プロフィールを表示します。
t0 から a を選択し、 a でグループ化します。
プロフィールを表示します。
テーブル t0 を変更し、インデックス `a_t0_index`(a) を追加します。 

これは、型数が少なくデータが少ない場合、インデックスなしでは、distinct と group by のパフォーマンスはほぼ同じであることを示しています。

インデックスを追加

テーブル t0 を変更し、インデックス `a_t0_index`(a) を追加します。

上記のようなクエリを実行した後

これは、タイプ数が少なくデータが少ない場合、インデックスを追加すると、distinct と group by のパフォーマンスがほぼ同じであることを示しています。

少量で多くの種類のデータがインデックス化されていないことを確認する

上記と同様のインデックスなしのクエリを実行した後

このことから、少量のデータで種類が多く、インデックスがない場合、distinct のパフォーマンスは group by よりもわずかに高いものの、その差は大きくないことがわかります。

インデックスを追加

テーブル t1 を変更し、インデックス `a_t1_index`(a) を追加します。

同様のインデックスなしのクエリを実行した後

このことから、データ量が少なく型が多い場合、インデックスを追加すると、distinct と group by のパフォーマンスはほぼ同じであることがわかります。

大量のデータを検証する

インデックスされていない

count(1)をt2から選択します。 

上記と同様のインデックスなしのクエリを実行した後

これは、多くの種類のデータが大量にあり、インデックスがない場合、DISTINCT の方が GROUP BY よりもパフォーマンスが優れていることを示しています。

インデックスを追加

テーブル t2 を変更し、インデックス `a_t2_index`(a) を追加します。

上記の同様のインデックスクエリを実行した後

これは、多くの種類の大量のデータの場合、インデックスを追加すると、distinct と group by のパフォーマンスがほぼ同じであることを示しています。

要約する