先日、情報の正規化をしない場合でも中間テーブルを使用した方が良いという記事を書きました。
ダミーデータの生成時にはあまり気にしていませんでしたが、中間テーブルの有無に関わらず、タグと企業の組み合わせが重複して登録される可能性がありました。
重複したデータが登録されると、データの肥大化や検索などのパフォーマンスに悪影響が想定されます。
そこで、今回は組み合わせでのユニーク制約の方法を実践します。
組み合わせユニークの処理方法はいくつか選択肢があるようなので、その比較もしてみます。
方法
今回は中間テーブルありのデータベースを前提に操作していきます。
私が調べた限り、5種類ありました。
| 方法 | 特徴 | メリット | デメリット |
|---|---|---|---|
| UNIQUE 制約 | DBレベルで制約を適用 | - 最もシンプル - エラーで重複防止 - 高速 |
- INSERT 時にエラーが発生する（処理の制御が必要） |
| ON CONFLICT DO NOTHING |
INSERT 時に重複を無視 |
- エラーが発生しない - 事前チェック不要 - パフォーマンスが良い |
- 既存データの上書きができない |
| INSERT ... WHERE NOT EXISTS |
SQL だけで完結 |
- エラーハンドリング不要 - SQLレベルで適用可能 |
- ON CONFLICT より処理が遅い |
事前に SELECT で確認 |
Python で制御 | - SQLをカスタマイズしやすい | - パフォーマンスが悪い - 2回クエリが走る ( SELECT + INSERT)
|
| ON CONFLICT DO UPDATE | 重複時にデータを更新 | - データの更新が可能 - 柔軟な処理ができる |
- UPDATE が発生するため DO NOTHING より遅い
|
挙動としては、上の3つは重複時は何もしません。
事前にSELECTをする方法は最も自由度が高いですが、処理が遅いですしクエリが2回走るのでDBへの負荷もありますね。
最後の方法は、重複時にアップデートすることになります。
さてここから実際に性能比較をしてみましょう。
ダミーデータの生成
今回は企業にタグを中間テーブルありで付与していきます。
データの概要
企業数: 10,000 社
タグ数: 5,000 種類
１企業あたりのタグ数
平均: 7.49 個
標準偏差: 1.71
最頻値（最も多くの企業が持つタグの数）: 5 個
中央値（企業のタグ数の真ん中の値）: 7 個
パフォーマンスの比較
新規・重複データ挿入の処理速度比較
実際にINSERT処理をしていきます。
今回は条件を揃えるために、重複データない状態と重複データがある状態に意図的に分けて、計測しました。
処理結果は下記のようになりました。
| 方法 | データ種別 | 平均 (秒) | 標準偏差 (秒) | 最大値 (秒) | 最小値 (秒) |
|---|---|---|---|---|---|
| UNIQUE制約 + 通常INSERT | 新規 | 0.001934 | 0.002383 | 0.018477 | 0.001049 |
| 重複 | 0.001976 | 0.000301 | 0.002573 | 0.001132 | |
| ON CONFLICT DO NOTHING | 新規 | 0.001482 | 0.000258 | 0.002307 | 0.000999 |
| 重複 | 0.001261 | 0.000174 | 0.001756 | 0.000822 | |
| SELECTで事前チェック | 新規 | 0.001928 | 0.001019 | 0.008799 | 0.001220 |
| 重複 | 0.000909 | 0.000131 | 0.001362 | 0.000574 | |
| INSERT ... WHERE NOT EXISTS | 新規 | 0.001791 | 0.000914 | 0.007899 | 0.001008 |
| 重複 | 0.000955 | 0.001003 | 0.007889 | 0.000521 |
SELECTで事前チェックする方法は重複時には、１度のクエリで後の処理を行わないので催促になっています。
新規・重複データ挿入の合算平均処理時間と増加率
実際の運用の場面では、新規と重複が混ざっているはずなので合計値を計算します。
| 方法 | 合算平均処理時間 (秒) | 最速処理との差 (増加率) |
|---|---|---|
| UNIQUE制約 + 通常INSERT | 0.001955 | +42.65% |
| ON CONFLICT DO NOTHING | 0.001371 | 最速 (基準) |
| SELECTで事前チェック | 0.001419 | +3.50% |
| INSERT ... WHERE NOT EXISTS | 0.001373 | +0.15% |
UNIQUE制約に通常のINSERTをするパターンが最も遅かったです。
これは恐らく、INSERT処理中にUNIQUE制約に引っかかりロールバックが発生しているからだと思います。このロールバック処理がトータルとして処理の遅さにつながっているのではないかと思います。
SELECTで事前チェックする方法は、すでにデータがある場合は最も早い処理となりますが、既存データがない場合は追加処理が発生し、新規と重複が同程度存在する場合は遅くなってしまいます。
一般的用途においてはON CONFLICT DO NOTHINGが最適だと思われます。
しかし、今回はデータベースに存在するデータ件数が少ないため、件数が増えるともっと大きな差になる可能性もあります。
データ総量を増やして比較
下記のようにデータを量産して、再度テストを行なってみます。
企業数: 1,000,000 社
タグ数: 5,000 種類
1企業あたりのタグ数:
平均: 12.43 個
標準偏差: 3.52
最頻値: 12 個
中央値: 12.0 個
新規・重複データ挿入の合算平均処理時間と増加率
テスト1回目
| 方法 | 合算平均処理時間 (秒) | 最速処理との差 (増加率) |
|---|---|---|
| UNIQUE制約 + 通常INSERT | 0.00315 | +76.47% |
| ON CONFLICT DO NOTHING | 0.002146 | +20.22% |
| SELECTで事前チェック | 0.002033 | +13.89% |
| INSERT ... WHERE NOT EXISTS | 0.001785 | 最速 (基準) |
テスト2回目
| 方法 | 合算平均処理時間 (秒) | 最速処理との差 (増加率) |
|---|---|---|
| UNIQUE制約 + 通常INSERT | 0.003208 | +49.49% |
| ON CONFLICT DO NOTHING | 0.002167 | +0.98% |
| SELECTで事前チェック | 0.002334 | +8.76% |
| INSERT ... WHERE NOT EXISTS | 0.002146 | 最速 (基準) |
テスト3回目
| 方法 | 合算平均処理時間 (秒) | 最速処理との差 (増加率) |
|---|---|---|
| UNIQUE制約 + 通常INSERT | 0.003253 | +57.45% |
| ON CONFLICT DO NOTHING | 0.002165 | +4.79% |
| SELECTで事前チェック | 0.00224 | +8.42% |
| INSERT ... WHERE NOT EXISTS | 0.002066 | 最速 (基準) |
3回テストをして、結果が意外と揺れました。
恐らく処理速度としては下記のようになります。
- INSERT ... WHERE NOT EXISTS
- ON CONFLICT DO NOTHING
- SELECTで事前チェック
- UNIQUE制約 + 通常INSER
2と３は状況により変わりそうですが、1番は安定して早そうです。
それぞれの挙動と考察
INSERT ... WHERE NOT EXISTS
この処理では、INSERT前にWHEREでテーブルに重複データがあるかを検索します。
重複データがない場合はINSERTが実行されます。
この処理はSQL内部で行われるため、高速に処理することができます。
ON CONFLICT DO NOTHING
この処理では、INSERTを行おうとしてその際にUNIQUE制約に違反する場合は処理がスキップされます。
INSERTが最初に実行されようとするので、その分遅くなると思われます。
SELECTで事前チェック
SQLを最適に使わないパワープレイです。
SELECTのクエリと、その結果に応じてもう１度クエリを走らせます。
重複がない場合、クエリを2回実行することになるので処理が遅くなります。
DBとの通信オーバーヘッドが発生するので遅い感じです。
UNIQUE制約 + 通常INSER
テーブル制約に違反するINSERTを強引に行い、制約違反でロールバックされる方法です。
ロールバックのコストが高く、安定して処理が遅くなります。
結論
パフォーマンス重視なら、INSERT ... WHERE NOT EXISTSが最適だと思います。
しかし調べている中で、SQLAlchemyのORMでは一般的にあまり使用されないようです(サポートされていない)。
そのため、若干パフォーマンスが落ちますがON CONFLICT DO NOTHINGが一般的に使用されるようです。
私は勢いでやってしまっていましたが、ON CONFLICT DO NOTHINGがベストプラクティスのようです。
ON CONFLICT DO UPDATEを使用すれば、updated_atなども更新できるので、この方法いいように思います。