MySQLの重複排除操作を極限まで最適化する方法

1. ソーステーブルには 100 万件のレコードがあり、そのうち 50 万件に created_time と item_name が重複しています。
2. 重複排除された 500,000 データをターゲットテーブルに書き込みます。
3. created_time と item_name が同じデータが複数ある場合は、ルールの制限なしにいずれか 1 つを保持できます。

• 実験環境

Linux 仮想マシン: CentOS リリース 6.4、8G 物理メモリ (MySQL は 4G で構成)、100G 機械式ハードディスク、デュアル物理 CPU デュアルコア、合計 4 つのプロセッサ、MySQL 8.0.16。

• テストテーブルとデータを作成する

-- ソーステーブルを作成する create table t_source 
(item_id int, 
 created_time 日時、 
 modified_time 日付時刻、 
 アイテム名 varchar(20), 
 その他のvarchar(20) 
); 
-- ターゲット テーブルを作成します。create table t_target like t_source; 
-- 100 万個のテスト データを生成します。そのうち 500,000 個の created_time と item_name には重複した区切り文字があります // 
プロシージャ sp_generate_data() を作成する 
始める 
 @i を 1 に設定します。 
 @i<=500000の場合 
 @created_time を date_add('2017-01-01'、間隔 @i 秒) に設定します。 
 @modified_time を @created_time に設定します。 
 @item_name を concat('a',@i) に設定します。 
 t_sourceに挿入 
 値 (@i,@created_time,@modified_time,@item_name,'other'); 
 @i:=@i+1 を設定します。 
 終了しながら; 
 専念; 
 @last_insert_id を 500000 に設定します。 
 t_sourceに挿入 
 item_id + @last_insert_id を選択し、 
 作成日時、 
 date_add(変更日時、間隔 @last_insert_id 秒)、 
 アイテム名、 
 '他の' 
 t_source から; 
 専念;
終わり 
// 
区切り文字 ; 
sp_generate_data() を呼び出します。 

-- ソース テーブルには主キーまたは一意制約がなく、同一のデータが 2 つ存在する可能性があるため、この状況をシミュレートするには別のレコードを挿入します。
t_source に挿入します。item_id=1 の場合、t_source から * を選択します。

 ソース テーブルには 1,000,001 件のレコードがあり、重複排除後のターゲット テーブルには 500,000 件のレコードがあるはずです。
mysql> t_source から count(*),count(distinct created_time,item_name) を選択します。
+----------+-----------------------------------------+
| count(*) | count(個別の created_time,item_name) |
+----------+-----------------------------------------+
| 1000001 | 500000 |
+----------+-----------------------------------------+
セット1列目（1.92秒）

1. インデックスと変数の賢い使用

1. インデックスなしの比較テスト

（１）相関サブクエリの使用

t_targetを切り捨てます。 
t_targetに挿入 
t_source t1からitem_idが 
(t1.created_time=t2.created_time かつ t1.item_name=t2.item_name の場合、t_source t2 から min(item_id) を選択)。

このステートメントは結果を生成するのに長い時間がかかるので、実行プランを確認してください。

mysql> explain select distinctive t1.* from t_source t1 where item_id in 
 -> (t1.created_time=t2.created_time かつ t1.item_name=t2.item_name の場合、t_source t2 から min(item_id) を選択); 
+----+--------------------+-------+-----------+-------+---------------+-------+--------+---------+----------+------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------------+-------+-----------+-------+---------------+-------+--------+---------+----------+------------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | where の使用; temporary の使用 |
| 2 | 従属サブクエリ | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 1.00 | where の使用 |
+----+--------------------+-------+-----------+-------+---------------+-------+--------+---------+----------+------------------------------+
セットに 2 行、警告 3 件 (0.00 秒)

メインクエリと関連するサブクエリの両方がテーブル全体をスキャンするため、合計 100 万 * 100 万のデータ行をスキャンする必要があります。結果が生成されないのも当然です。

（２）テーブル結合の使用

t_targetを切り捨てます。 
t_targetに挿入 
t_source t1から別のt1.*を選択し、 
(t_source から min(item_id) item_id,created_time,item_name を選択し、created_time、item_name でグループ化) t2 
ここで、t1.item_id = t2.item_id;

この方法は 14 秒かかり、クエリプランは次のようになります。

mysql> explain select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id;
+----+--------------+-------------+------------+----------+---------------+------------+----------+----------------+---------+-----------+------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+------------+----------+---------------+------------+----------+----------------+---------+-----------+------------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | where の使用; temporary の使用 |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | test.t1.item_id | 10 | 100.00 | 個別 |
| 2 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | 一時を使用 |
+----+--------------+-------------+------------+----------+---------------+------------+----------+----------------+---------+-----------+------------------------------+
セットに 3 行、警告 1 件 (0.00 秒)

•内部クエリは、t_source テーブルの 100 万行をスキャンし、一時テーブルを作成し、重複排除後の最小の item_id を見つけ、50 万行の派生テーブル derived2 を生成します。
•MySQL は派生テーブル derived2 の item_id フィールドにインデックス auto_key0 を自動的に作成します。
•外部クエリでは、t_source テーブル内の 100 万行のデータをスキャンする必要もあります。エクスポートテーブルにリンクする場合、t_source テーブルの各行の item_id に対して、auto_key0 インデックスを使用してエクスポートテーブル内の一致する行を検索し、この時点で個別の操作が最適化されます。最初の一致する行が見つかった後、同じ値の検索は停止されます。

（３）変数の使用

@a:='1000-01-01 00:00:00' を設定します。 
@b:=' ' を設定します。 
@f:=0 を設定します。 
t_targetを切り捨てます。 
t_targetに挿入 
アイテムID、作成日時、変更日時、アイテム名、その他を選択 
 から 
(t0.* を選択、if(@a=created_time かつ @b=item_name、@f:=0、@f:=1) f、@a:=created_time、@b:=item_name 
 から 
(select * from t_source order by created_time,item_name) t0) t1、f=1 の場合;

この方法は 13 秒かかり、クエリプランは次のようになります。

mysql> 選択項目ID、作成日時、変更日時、項目名、その他を説明します 
 -> から 
 -> (t0.* を選択、if(@a=created_time かつ @b=item_name、@f:=0、@f:=1) f、@a:=created_time、@b:=item_name 
 -> から 
 -> (select * from t_source order by created_time,item_name) t0) t1 where f=1; 
+----+--------------+-------------+-----------+---------+---------------+-------------+---------+---------+----------+----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+---------+---------------+-------------+---------+---------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL |
| 2 | 派生 | <derived3> | NULL | すべて | NULL | NULL | NULL | NULL | 997282 | 100.00 | NULL |
| 3 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | filesort を使用 |
+----+--------------+-------------+-----------+---------+---------------+-------------+---------+---------+----------+----------------+
セットに 3 行、警告 5 件 (0.00 秒)

•最も内側のクエリは、t_source テーブルの 100 万行をスキャンし、ファイルソートを使用して派生テーブル derived3 を生成します。
•第 2 レベルのクエリは、derived3 の 100 万行をスキャンし、派生テーブル derived2 を生成し、変数の比較と割り当てを完了し、派生列 f にインデックス auto_key0 を自動的に作成します。
•最外層はauto_key0インデックスを使用してderived2をスキャンし、重複排除された結果行を取得します。

上記の方法 2 と比較すると、スキャンされる行の総数は変わらず、200 万行のままです。わずかな違いがあります。今回は、自動生成されたインデックスは定数列 f にあり、テーブル結合の自動生成されたインデックスは item_id 列にあるため、クエリ時間はほぼ同じです。

現時点では、ソーステーブルにインデックスは作成されていません。どちらの書き込み方法を使用する場合でも、created_time フィールドと item_name フィールドは重複をチェックするためにソートする必要があります。したがって、これら 2 つのフィールドに結合インデックスが設定されている場合は、インデックス自体の順序付けされた性質を利用して追加のソートを省き、クエリのパフォーマンスを向上できると考えるのが自然です。

2. created_timeとitem_nameの結合インデックス比較テストを作成する

-- created_time フィールドと item_name フィールドの結合インデックスを作成します。create index idx_sort on t_source(created_time,item_name,item_id); 
テーブル t_source を分析します。

（１）相関サブクエリの使用

t_targetを切り捨てます。 
t_targetに挿入 
t_source t1からitem_idが 
(t1.created_time=t2.created_time かつ t1.item_name=t2.item_name の場合、t_source t2 から min(item_id) を選択)。

今回は 19 秒かかり、クエリプランは次のようになります。

mysql> explain select distinctive t1.* from t_source t1 where item_id in 
 -> (t1.created_time=t2.created_time かつ t1.item_name=t2.item_name の場合、t_source t2 から min(item_id) を選択); 
+----+--------------------+-------+-----------+--------+---------------+----------+---------+---------+----------------------------------------+----------+----------+------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------------+-------+-----------+--------+---------------+----------+---------+---------+----------------------------------------+----------+----------+------------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997281 | 100.00 | where の使用; temporary の使用 |
| 2 | 従属サブクエリ | t2 | NULL | ref | idx_sort | idx_sort | 89 | test.t1.created_time、test.t1.item_name | 2 | 100.00 | インデックスを使用 |
+----+--------------------+-------+-----------+--------+---------------+----------+---------+---------+----------------------------------------+----------+----------+------------------------------+
セットに 2 行、警告 3 件 (0.00 秒)

• 外部クエリの t_source テーブルは駆動テーブルであり、100 万行をスキャンする必要があります。

•ドライバーテーブルの各行の item_id については、idx_sort インデックスを通じて 2 行のデータが照会されます。

（２）テーブル結合の使用

t_targetを切り捨てます。 
t_targetに挿入 
t_source t1から別のt1.*を選択し、 
(t_source から min(item_id) item_id,created_time,item_name を選択し、created_time、item_name でグループ化) t2 
ここで、t1.item_id = t2.item_id;

今回は 13 秒かかり、クエリプランは次のようになります。

mysql> t_source t1 から distinct t1.* を選択して、 
 -> (t_source から min(item_id) item_id,created_time,item_name を選択し、created_time、item_name でグループ化) t2 
 -> ここで、t1.item_id = t2.item_id; 
+----+--------------+-------------+------------+----------+---------------+------------+----------+-----------------+----------+-----------+------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+------------+----------+---------------+------------+----------+-----------------+----------+-----------+------------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997281 | 100.00 | where の使用; temporary の使用 |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | test.t1.item_id | 10 | 100.00 | 個別 |
| 2 | DERIVED | t_source | NULL | index | idx_sort | idx_sort | 94 | NULL | 997281 | 100.00 | インデックスを使用 |
+----+--------------+-------------+------------+----------+---------------+------------+----------+-----------------+----------+-----------+------------------------------+
セットに 3 行、警告 1 件 (0.00 秒)

インデックスがない場合と比較すると、サブクエリは完全なテーブルスキャンから完全なインデックススキャンに変わりますが、それでも 100 万行のレコードをスキャンする必要があります。したがって、クエリパフォーマンスの向上は明らかではありません。

（３）変数の使用

@a:='1000-01-01 00:00:00' を設定します。 
@b:=' ' を設定します。 
@f:=0 を設定します。 
t_targetを切り捨てます。 
t_targetに挿入 
アイテムID、作成日時、変更日時、アイテム名、その他を選択 
 から 
(t0.* を選択、if(@a=created_time かつ @b=item_name、@f:=0、@f:=1) f、@a:=created_time、@b:=item_name 
 から 
(select * from t_source order by created_time,item_name) t0) t1、f=1 の場合;

今回は 13 秒かかり、クエリプランはインデックスがない場合とまったく同じでした。この書き込み方法ではインデックスは影響を及ぼさないことがわかります。ネストをなくして、1 つのレイヤーだけで結果を照会することは可能ですか?

（4）変数を使用し、ネストされたクエリを排除する

@a:='1000-01-01 00:00:00' を設定します。 
@b:=' ' を設定します。 
t_targetを切り捨てます。 
t_targetに挿入 
t_source から * を強制インデックス (idx_sort) で選択 
 (@a!=created_time または @b!=item_name) であり、(@a:=created_time) は null ではなく、(@b:=item_name) は null ではありません 
 created_time、item_name で並べ替えます。

今回は 12 秒かかり、クエリプランは次のようになります。

mysql> explain select * from t_source force index (idx_sort) 
 -> (@a!=created_time または @b!=item_name) かつ (@a:=created_time) が null でなく (@b:=item_name) が null でない 
 -> created_time、item_name で並べ替えます。
+----+-------------+-----------+-----------+----------+---------------+-----------+---------+----------+-----------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+-----------+-----------+----------+---------------+-----------+---------+----------+-----------+-------------+
| 1 | SIMPLE | t_source | NULL | index | NULL | idx_sort | 94 | NULL | 997281 | 99.00 | where の使用 |
+----+-------------+-----------+-----------+----------+---------------+-----------+---------+----------+-----------+-------------+
セットに 1 行、警告 3 件 (0.00 秒)

このステートメントには次の特徴があります。

•ネストされたサブクエリが削除され、t_source テーブルの完全なインデックススキャンが 1 回だけ必要になるため、クエリプランは最適になります。
•重複コンテンツを再度チェックするために DISTINCT を使用する必要はありません。
•変数の判断と割り当ては、where 句にのみ表示されます。
• インデックスを使用してファイルソートを排除します。

MySQL 8 より前では、このステートメントはシングルスレッド重複排除に最適なソリューションでした。このステートメントを注意深く分析すると、SQL ステートメントの論理クエリ処理手順とインデックス機能を巧みに利用していることがわかります。 SQL クエリの論理的な手順は次のとおりです。

1. デカルト積（クロス結合）を実行する
2. ONフィルター（結合条件）を適用する
3. 外部行を追加する（外部結合）
4. whereフィルターを適用する
5. グループ化
6. キューブまたはロールアップを適用する
7. フィルタを適用する
8. 選択リストの処理
9. distinctive節を適用する
10. order by句を適用する
11. 制限条項を適用する

各クエリステートメントの論理実行手順は、これらの 11 手順のサブセットです。このクエリステートメントを例に挙げます。実行順序は、インデックス idx_sort を介してデータ行を強制的に検索 -> where フィルターを適用 -> 選択リストを処理 -> order by 句を適用です。

変数を created_time と item_name の順序で割り当てて比較するには、データ行をインデックス順に検索する必要があります。ここでの強制インデックス (idx_sort) ヒントがこの役割を果たしており、重複チェックステートメント全体を有効にするには、このように記述する必要があります。そうしないと、最初にテーブルがスキャンされてからソートされるため、変数の割り当て順序が保証されず、クエリ結果の正確性が保証されません。 order by 句も無視できません。そうしないと、force index プロンプトがあっても、MySQL は完全なインデックススキャンではなく完全なテーブルスキャンを使用し、結果が不正確になります。インデックスは created_time と item_name の順序も保証し、ファイルのソートを回避します。 force index (idx_sort)ヒントと order by 句はどちらも不可欠です。インデックス idx_sort はまさにここで役立ち、一石二鳥です。

クエリ文が始まる前に、変数をデータに出現し得ない値に初期化し、左から右に判断する where 句を入力します。まず変数とフィールドの値を比較し、次にこの行の created_time と item_name の値を変数に割り当て、created_time と item_name の順序で各行を処理します。 item_name は文字列型であり、 (@b:=item_name) は有効なブール式ではないため、 (@b:=item_name) is not null と記述する必要があります。

最後に、「 insert into t_target select * from t_source group by created_time,item_name ;」の記述は“sql_mode='ONLY_FULL_GROUP_BY' 」によって制限されているため、ここでは無視されることを付け加えておきます。

2. ウィンドウ関数の使用

MySQL 8 の新しいウィンドウ関数により、以前は面倒だった重複排除操作が非常に簡単になります。

t_targetを切り捨てます。 
t_targetに挿入 
アイテムID、作成日時、変更日時、アイテム名、その他を選択
 from (select *, row_number() over(partition by created_time,item_name) as rn
 t_source から) t (rn=1 の場合)

このステートメントの実行には 12 秒しかかからず、記述も明確で理解しやすいです。クエリプランは次のとおりです。

mysql> explain select item_id、created_time、modified_time、item_name、other
 -> from (select *, row_number() over(partition by created_time,item_name) as rn
 -> t_source から) t (rn=1 の場合)
+----+--------------+-------------+-----------+---------+---------------+-------------+---------+---------+----------+----------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+---------+---------------+-------------+---------+---------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 10 | 100.00 | NULL |
| 2 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997281 | 100.00 | filesort を使用 |
+----+--------------+-------------+-----------+---------+---------------+-------------+---------+---------+----------+----------------+
セットに 2 行、警告 2 件 (0.00 秒)

このクエリは、t_source テーブルに対して完全なテーブルスキャンを実行し、filesort を使用して、パーティションフィールド created_time および item_name でテーブルをソートします。外部クエリは各パーティションから 1 つのデータを保持します。 created_timeとitem_nameが重複する複数のデータのうちいずれか 1 つを保持できるため、oevr で order by 句を使用する必要はありません。

実行プランから見ると、ウィンドウ関数の重複排除ステートメントは、ネストされたクエリの変数の重複排除ほど優れているようには見えませんが、この方法は実際には最も高速です。

MySQL ウィンドウ関数の説明については、https://dev.mysql.com/doc/refman/8.0/en/window-functions.html を参照してください。

3. マルチスレッド並列実行

単一の重複チェックステートメントは最適な状態に調整されていますが、依然としてシングルスレッド方式で実行されます。複数のプロセッサを使用して、複数のスレッドで重複排除操作を並列に実行し、速度をさらに向上させることはできますか?たとえば、私の実験環境は 4 プロセッサです。4 つのスレッドを使用して重複チェック SQL を同時に実行すると、理論的にはパフォーマンスが 4 倍近く向上するはずです。

1. データシャーディング

テストデータを生成する際、created_time は各レコードに 1 秒を追加する方法を使用します。つまり、最大と最小の時間差は 500,000 秒であり、データは均等に分散されるため、データは最初に 4 つの部分に分割されます。

（１）４つのデータセットのcreated_time境界値を照会する

mysql> date_add('2017-01-01'、間隔 125000 秒) dt1 を選択します。
 -> date_add('2017-01-01',間隔 2*125000 秒) dt2,
 -> date_add('2017-01-01',間隔 3*125000 秒) dt3,
 -> max(作成時間) dt4
 -> t_source から;
+---------------------+---------------------+---------------------+---------------------+
| dt1 | dt2 | dt3 | dt4 |
+---------------------+---------------------+---------------------+---------------------+
| 2017-01-02 10:43:20 | 2017-01-03 21:26:40 | 2017-01-05 08:10:00 | 2017-01-06 18:53:20 |
+---------------------+---------------------+---------------------+---------------------+
セット内の 1 行 (0.00 秒)

（２）各データセットのレコード数をチェックして、データが均等に分布していることを確認する

mysql> created_time >= '2017-01-01' の場合にケースを選択します 
 -> かつ created_time < '2017-01-02 10:43:20'
 -> 次に '2017-01-01'
 -> created_time >= '2017-01-02 10:43:20' の場合
 -> かつ created_time < '2017-01-03 21:26:40'
 -> その後 '2017-01-02 10:43:20'
 -> created_time >= '2017-01-03 21:26:40' の場合 
 -> かつ created_time < '2017-01-05 08:10:00'
 -> その後 '2017-01-03 21:26:40' 
 -> それ以外の場合は '2017-01-05 08:10:00'
 -> min_dtの終了、
 -> created_time >= '2017-01-01' の場合 
 -> かつ created_time < '2017-01-02 10:43:20'
 -> その後 '2017-01-02 10:43:20'
 -> created_time >= '2017-01-02 10:43:20' の場合
 -> かつ created_time < '2017-01-03 21:26:40'
 -> その後 '2017-01-03 21:26:40'
 -> created_time >= '2017-01-03 21:26:40' の場合 
 -> かつ created_time < '2017-01-05 08:10:00'
 -> '2017-01-05 08:10:00' になります
 -> それ以外の場合は '2017-01-06 18:53:20'
 -> max_dtの終了、
 -> カウント(*)
 -> t_source から
 -> created_time >= '2017-01-01' の場合にグループ化 
 -> かつ created_time < '2017-01-02 10:43:20'
 -> 次に '2017-01-01'
 -> created_time >= '2017-01-02 10:43:20' の場合
 -> かつ created_time < '2017-01-03 21:26:40'
 -> その後 '2017-01-02 10:43:20'
 -> created_time >= '2017-01-03 21:26:40' の場合 
 -> かつ created_time < '2017-01-05 08:10:00'
 -> その後 '2017-01-03 21:26:40' 
 -> それ以外の場合は '2017-01-05 08:10:00'
 -> 終了、
 -> created_time >= '2017-01-01' の場合 
 -> かつ created_time < '2017-01-02 10:43:20'
 -> その後 '2017-01-02 10:43:20'
 -> created_time >= '2017-01-02 10:43:20' の場合
 -> かつ created_time < '2017-01-03 21:26:40'
 -> その後 '2017-01-03 21:26:40'
 -> created_time >= '2017-01-03 21:26:40' の場合 
 -> かつ created_time < '2017-01-05 08:10:00'
 -> '2017-01-05 08:10:00' になります
 -> それ以外の場合は '2017-01-06 18:53:20'
 -> 終了;
+---------------------+---------------------+----------+
| min_dt | max_dt | カウント(*) |
+---------------------+---------------------+----------+
| 2017-01-01 | 2017-01-02 10:43:20 | 249999 |
| 2017-01-02 10:43:20 | 2017-01-03 21:26:40 | 250000 |
| 2017-01-03 21:26:40 | 2017-01-05 08:10:00 | 250000 |
| 2017-01-05 08:10:00 | 2017-01-06 18:53:20 | 250002 |
+---------------------+---------------------+----------+
4 列セット (4.86 秒)

4 つのデータセットを結合すると、ソースデータセット全体がカバーされ、データ間に重複がなくなる必要があります。つまり、データの 4 つのコピーの created_time は連続しており、相互に排他的である必要があります。連続性により、すべてのデータが処理されることが保証され、相互排他性により、2 回目の重複チェックが不要になります。実際、これは時間範囲パーティション分割の概念に似ています。パーティション分割されたテーブルを使用する方が良いかもしれませんが、テーブルを再構築する手順はここでは省略されています。

2. 重複コンテンツをチェックするためのストアドプロシージャを作成する

上記の情報を使用して、すべてのデータを処理するための 4 つのステートメントを記述できます。呼び出しインターフェイスをできるだけシンプルにするために、次のストアドプロシージャを作成します。

区切り文字 //
プロシージャ sp_unique(i smallint) を作成します。 
始める 
 @a:='1000-01-01 00:00:00' を設定します。 
 @b:=' ' を設定します。 
 もし (i<4) ならば
 t_targetに挿入 
 t_source から * を強制インデックス (idx_sort) で選択 
  created_time >= date_add('2017-01-01',interval (i-1)*125000 秒) の場合 
  created_time < date_add('2017-01-01',interval i*125000 秒) 
  および (@a!=created_time または @b!=item_name) 
  (@a:=created_time) は null ではない 
  (@b:=item_name) は null ではありません 
  created_time、item_name で並べ替えます。 
 それ以外 
 t_targetに挿入 
 t_source から * を強制インデックス (idx_sort) で選択 
  created_time >= date_add('2017-01-01',interval (i-1)*125000 秒) の場合 
  created_time <= date_add('2017-01-01',interval i*125000 秒) 
  および (@a!=created_time または @b!=item_name) 
  (@a:=created_time) は null ではない 
  (@b:=item_name) は null ではありません 
  created_time、item_name で並べ替えます。 
 終了の場合; 
終わり 
//

クエリステートメントの実行プランは次のとおりです。

mysql> explain select * from t_source force index (idx_sort) 
 -> created_time >= date_add('2017-01-01',interval (1-1)*125000 秒) の場合 
 -> created_time < date_add('2017-01-01',interval 1*125000 秒) 
 -> かつ (@a!=created_time または @b!=item_name) 
 -> かつ (@a:=created_time) は null ではない 
 -> かつ (@b:=item_name) は null ではない 
 -> created_time、item_name で並べ替えます。 
+----+-------------+-----------+-----------+---------+---------------+-----------+--------+---------+---------+-----------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+-----------+-----------+----------+---------------+------------+---------+---------+----------+-----------------------+
| 1 | SIMPLE | t_source | NULL | range | idx_sort | idx_sort | 6 | NULL | 498640 | 100.00 | インデックス条件を使用 |
+----+-------------+-----------+-----------+---------+---------------+-----------+--------+---------+---------+-----------------------+
セットに 1 行、警告 3 件 (0.00 秒)

MySQL オプティマイザーはインデックス範囲スキャンを実行し、インデックス条件プッシュダウン (ICP) を使用してクエリを最適化します。

3. 並列実行

以下では、シェルのバックグラウンドプロセスと MySQL スケジュールイベントを使用して並列処理を実現します。

（１）シェルのバックグラウンドプロセス

•次の内容のduplicate_removal.shファイルを作成します。

#!/bin/bash
mysql -vvv -u root -p123456 test -e "truncate t_target" &>/dev/null 
日付 '+%H:%M:%S'
yが{1..4}の場合
する
 sql="sp_unique($y) を呼び出す"
 mysql -vvv -u ルート -p123456 テスト -e "$sql" &>par_sql1_$y.log &
終わり
待って
日付 '+%H:%M:%S'

• スクリプトファイルの実行

./duplicate_removal.sh

実行出力は次のようになります。

[mysql@hdp2~]$./duplicate_removal.sh
14:27:30
14:27:35

このメソッドには 5 秒かかり、並列で実行される 4 つのプロセス呼び出しにはそれぞれ 4.87 秒、4.88 秒、4.91 秒、4.73 秒かかります。

[mysql@hdp2~]$cat par_sql1_1.log | sed '/^$/d'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
--------------
sp_unique(1)を呼び出す
--------------
クエリは正常、124999 行が影響を受けました (4.87 秒)
さよなら
[mysql@hdp2~]$cat par_sql1_2.log | sed '/^$/d'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
--------------
sp_unique(2)を呼び出す
--------------
クエリは正常、125000 行が影響を受けました (4.88 秒)
さよなら
[mysql@hdp2~]$cat par_sql1_3.log | sed '/^$/d'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
--------------
sp_unique(3)を呼び出す
--------------
クエリは正常、125000 行が影響を受けました (4.91 秒)
さよなら
[mysql@hdp2~]$cat par_sql1_4.log | sed '/^$/d'
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
--------------
sp_unique(4)を呼び出す
--------------
クエリは正常、125001 行が影響を受けました (4.73 秒)
さよなら
[mysql@hdp2~]$

各プロセスの実行時間は4.85秒であることがわかります。並列実行されているため、プロセス全体の実行時間は最も遅い4.91秒となり、シングルスレッドの速度より2.5倍高速になっています。

（２）MySQLスケジュールイベント

• イベント履歴ログテーブルの作成

-- イベント実行時間やその他の情報を表示するために使用されます。create table t_event_history ( 
 dbname varchar(128) NULLでないデフォルト '' 
 イベント名 varchar(128) null でない デフォルト '' 
 開始時刻 datetime(3) NULLでない デフォルト '1000-01-01 00:00:00', 
 終了時刻 datetime(3) デフォルト null、 
 issuccess int(11) デフォルトはnull、 
 期間 int(11) デフォルト null、 
 エラーメッセージ varchar(512) デフォルト null、 
 randno int(11) デフォルトはnull
);

• 同時スレッドごとにイベントを作成する

区切り文字 //
スケジュールに従って、現在のタイムスタンプ + 間隔 1 時間にイベント ev1 を作成し、完了したら保存して無効にする 
始める
 r_code char(5) をデフォルト '00000' として宣言します。 
 r_msg テキストを宣言します。 
 v_error 整数を宣言します。 
 v_starttime datetime を宣言します。デフォルトは now(3); 
 v_randno 整数を宣言します。デフォルトは floor(rand()*100001) です。 
 t_event_history に挿入 (dbname、eventname、starttime、randno) 
 #ジョブ名値(database(),'ev1', v_starttime,v_randno); 
 始める 
 #例外処理セクションでは、sqlexception の継続ハンドラを宣言します。 
 始める 
  v_error = 1 を設定します。 
  診断条件 1 を取得します。r_code = returned_sqlstate、r_msg = message_text; 
 終わり; 
 #これは、call sp_unique(1);と呼ばれる実際のユーザープログラム手順です。 
 終わり; 
 t_event_history を更新し、endtime=now(3)、issuccess=isnull(v_error)、duration=timestampdiff(microsecond,starttime,now(3))、errormessage=concat('error='、r_code,'、message='、r_msg)、randno=null に設定します。ここで、starttime=v_starttime、randno=v_randno です。 
終わり
// 
スケジュールに従って、現在のタイムスタンプ + 間隔 1 時間にイベント ev2 を作成し、完了したら保存して無効にする 
始める
 r_code char(5) をデフォルト '00000' として宣言します。 
 r_msg テキストを宣言します。 
 v_error 整数を宣言します。 
 v_starttime datetime を宣言します。デフォルトは now(3); 
 v_randno 整数を宣言します。デフォルトは floor(rand()*100001) です。 
 t_event_history に挿入 (dbname、eventname、starttime、randno) 
 #ジョブ名値(database(),'ev2', v_starttime,v_randno); 
 始める 
 #例外処理セクションでは、sqlexception の継続ハンドラを宣言します。 
 始める 
  v_error = 1 を設定します。 
  診断条件 1 を取得します。r_code = returned_sqlstate、r_msg = message_text; 
 終わり; 
 #これは、call sp_unique(2);と呼ばれる実際のユーザープログラム手順です。 
 終わり; 
 t_event_history を更新し、endtime=now(3)、issuccess=isnull(v_error)、duration=timestampdiff(microsecond、starttime、now(3))、errormessage=concat('error='、r_code、'、message='、r_msg)、randno=null に設定します。ここで、starttime=v_starttime、randno=v_randno です。 
終わり
// 
スケジュールに従って、現在のタイムスタンプ + 間隔 1 時間にイベント ev3 を作成し、完了したら保存して無効にする 
始める
 r_code char(5) をデフォルト '00000' として宣言します。 
 r_msg テキストを宣言します。 
 v_error 整数を宣言します。 
 v_starttime datetime を宣言します。デフォルトは now(3); 
 v_randno 整数を宣言します。デフォルトは floor(rand()*100001) です。 
 t_event_history に挿入 (dbname、eventname、starttime、randno) 
 #ジョブ名値(database(),'ev3', v_starttime,v_randno); 
 始める 
 #例外処理セクションでは、sqlexception の継続ハンドラを宣言します。 
 始める 
  v_error = 1 を設定します。 
  診断条件 1 を取得します。r_code = returned_sqlstate、r_msg = message_text; 
 終わり; 
 #これは、call sp_unique(3);と呼ばれる実際のユーザープログラム手順です。 
 終わり; 
 t_event_history を更新し、endtime=now(3)、issuccess=isnull(v_error)、duration=timestampdiff(microsecond,starttime,now(3))、errormessage=concat('error='、r_code,'、message='、r_msg)、randno=null に設定します。ここで、starttime=v_starttime、randno=v_randno です。 
終わり
// 
スケジュールに従って、現在のタイムスタンプ + 間隔 1 時間にイベント ev4 を作成し、完了したら保存して無効にする 
始める
 r_code char(5) をデフォルト '00000' として宣言します。 
 r_msg テキストを宣言します。 
 v_error 整数を宣言します。 
 v_starttime datetime を宣言します。デフォルトは now(3); 
 v_randno 整数を宣言します。デフォルトは floor(rand()*100001) です。 
 t_event_history に挿入 (dbname、eventname、starttime、randno) 
 #ジョブ名値(database(),'ev4', v_starttime,v_randno); 
 始める 
 #例外処理セクションでは、sqlexception の継続ハンドラを宣言します。 
 始める 
  v_error = 1 を設定します。 
  診断条件 1 を取得します。r_code = returned_sqlstate、r_msg = message_text; 
 終わり; 
 #これは、call sp_unique(4);と呼ばれる実際のユーザープログラムプロシージャです。 
 終わり; 
 t_event_history を更新し、endtime=now(3)、issuccess=isnull(v_error)、duration=timestampdiff(microsecond,starttime,now(3))、errormessage=concat('error='、r_code,'、message='、r_msg)、randno=null に設定します。ここで、starttime=v_starttime、randno=v_randno です。 
終わり
//

各イベントの実行時間を記録するために、操作ログテーブルのロジックをイベント定義に追加します。各イベントでは挿入と更新がそれぞれ 1 つずつしか実行されないため、4 つのイベントに対して合計 8 つの非常に単純なステートメントが実行され、テストへの影響はごくわずかです。実行時間はミリ秒単位の精度です。

• イベント実行をトリガーする

mysql -vvv -u root -p123456 test -e "truncate t_target;alter event ev1 on schedule at current_timestamp enable;alter event ev2 on schedule at current_timestamp enable;alter event ev3 on schedule at current_timestamp enable;alter event ev4 on schedule at current_timestamp enable;"

このコマンドラインは 4 つのイベントを順番にトリガーしますが、前のイベントが完了するまで待機せずに次のイベントを実行します。代わりに、次のイベントをすぐに実行します。これは、コマンドの出力から明確にわかります。

[mysql@hdp2~]$mysql -vvv -u root -p123456 test -e "truncate t_target;alter event ev1 on schedule at current_timestamp enable;alter event ev2 on schedule at current_timestamp enable;alter event ev3 on schedule at current_timestamp enable;alter event ev4 on schedule at current_timestamp enable;"
mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。
--------------
t_targetを切り捨てる
--------------
クエリは正常、影響を受けた行は 0 行 (0.06 秒)
--------------
スケジュールに従ってイベント ev1 を current_timestamp で変更する
--------------
クエリは正常、影響を受けた行は 0 行 (0.02 秒)
--------------
スケジュールに従ってイベント ev2 を current_timestamp で変更する
--------------
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
--------------
スケジュールに従って、現在のタイムスタンプでイベント ev3 を変更します。
--------------
クエリは正常、影響を受けた行は 0 行 (0.02 秒)
--------------
スケジュールに従って、現在のタイムスタンプでイベント ev4 を変更します。
--------------
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
さよなら
[mysql@hdp2~]$

• イベント実行ログの表示

mysql> test.t_event_history から * を選択します。
+--------+-----------+--------------------------+-----------+----------+--------------+---------+
| dbname |
+--------+----------+-----------------------+----------------------+---------+---------+-----------+--------+
| EV1 |
| EV2 |
| EV3 |
| EV4 |
+--------+----------+----------------------+-----------------------+---------+---------+-----------+--------+
セット内の 4 行 (0.00 秒)

各プロセスは、並行して実行されるために4.83秒かかることがわかります。

要約する

上記は、MySQLの重複排除操作を極端に最適化するための操作方法です。質問があることを願っています。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。
この記事が役に立ったと思われた方は、ぜひ転載していただき、出典を明記してください。ありがとうございます!

以下もご興味があるかもしれません: