MySQLでbulk updateする際、CASE WHEN構文とELT, FIELD関数を用いた場合で実行時間がほぼ変わらなかった例

# モチベーション

Djangoの**bulk_update**は**CASE WHEN構文**と**WHERE IN ...句**を用いたクエリを発行することを別記事で検証しました。

ほぼ同時期にMySQLの**ELT関数**, **FIELD関数**というものについて知って、簡単な**CASE WHEN構文**であれば書き換えられることに気づいたので、DjangoのORMが発行するクエリを改善できないか同等なupdateのクエリをいくつかの方法で書き実行時間を比較し検証しました。

結論から言うと、タイトルにもありますが
**今回比較した例においてELT, FIELD関数を使用することによる実行時間の短縮を図ることはできませんでした**。

# 環境

```
MySQL==5.7
```

# 前提
自明な知識ではないと思われるのでELT関数とFIELD関数について説明をします。

###ELT関数

```sql
ELT(N, str_1, str_2, str_3,..., str_N, str_(N+1), ..., str_M) 
※ str_n: 数字 or 文字列
```
の形で引数を取り、str_Nを返します。

###FIELD関数

```sql
FIELD(str_N, str1, str2, str3,..., str_N, str_(N+1), ..., str_M)
※ str_n: 数字 or 文字列
```
の形で引数を取り、Nを返します。

### ELTとFIELDの組み合わせ
具体的な例を用います。
DBに**students** (学生)テーブルがあり、**department** (学部)カラムが物理学部、文学部の2つの値をとるとします。
更にまだ適切な値が入っていない**is_science** (自然科学系の学生である)カラムを以下のようにdepartmentの値に基づいて更新したいとします。
(物理学部 ならば is_science = 1, 文学部 ならば is_science = 0)

この時一人の学生**student**の適切なis_scienceの値は疑似コードで

```sql
is_science = ELT(FIELD(student.department, '物理学部', '文学部'), 1, 0)
```

と表すことができます。
例えばstudent.departmentが'文学部'の時

```sql
is_science = ELT(FIELD('文学部', '物理学部', '文学部'), 1, 0)
```
FIELD('文学部', '物理学部', '文学部') = 2なので

```sql
=>
is_science = ELT(2, 1, 0)
=> 
is_science = 0
```
となります。
よって

```sql
UPDATE students
  SET is_science = 
    ELT(FIELD(students.department, '物理学部', '文学部'), 1, 0)
  WHERE students.department IN ('物理学部', '文学部')
```
とすることで**department**に対し動的に**is_science**を更新することができます。

###CASE WHEN構文でELT, FIELD関数を表現する
上のクエリは

```sql
UPDATE students
  SET is_science =
  CASE
    WHEN department = '物理学部' THEN 1
    WHEN department = '文学部' THEN 0
  END
  WHERE students.department IN ('物理学部', '文学部')
```
と同等であることがわかると思います。

次に2つのクエリの実行時間を簡単な例で比較します。


# 比較する
### 設定
本のDBテーブル**Books** (50000レコード)を考えます。
カラムはAUTO_INCREMENTのプライマリキー**id**, VARCHARの**title**のみとします。


### 計画
全ての**title**を全て同じ'X'に更新することを考え、
id=1ならばtitle='X', id=2ならばtitle='X', ... という意味のない条件分岐を含むクエリを書きます。
参考用に条件分岐なしのクエリも用意します。

以下の合計**6つの**クエリを用意しました。


|   |ELT FIELD|CASE WHEN|条件分岐なし|
|---|-------|--------|--------|
|WHERE BETWEEN|   クエリ1|クエリ3  |クエリ5
|WHERE ... IN|  クエリ2| クエリ4 |   クエリ6|
列は**id**による条件分岐の仕方
行はWHERE句の書き方を表します。

例えばクエリ1は

```sql
UPDATE books
  SET title = ELT(FIELD(books.id, 1, 2, ..., N), 'X', 'X', ...)
  WHERE id BETWEEN 1 AND N
```

クエリ4は

```sql
UPDATE books
  SET title = 
  CASE
    WHEN id = 1 THEN 'X'
    WHEN id = 2 THEN 'X'
    ...
    WHEN id = N THEN 'X'
  END

  WHERE id IN (1, 2, ..., N)
```

更にクエリ5は

```sql
UPDATE books
  SET title = 'X'
  WHERE id BETWEEN 1 AND N
```
のような形になります。

以上の6種類のクエリについて
50000件の全レコードのtitleを'X'に更新する操作を50バッチ=50クエリ (1バッチ=1クエリ=1000件更新)に分割して行い、
MySQLのinformation_schema.PROFILINGテーブルを用いて50バッチ分の合計実行時間を計測します。
更に、DBクライアント (Sequel Pro)のクエリ実行ボタンを押してから、操作が返ってくるまでの時間も手動で計測します。
以上をそれぞれ10回繰り返します。

PC起動直後、実行時間が早くなる傾向があったのでパイロットランとしてそれぞれのクエリで50000件更新を2回づつ行いました。

またWHERE BETWEENを用いたクエリが連続する場合や、ELT FIELDを用いたクエリが連続する場合に、後続のクエリに実行時間が顕著に早くなる傾向があったため (バッファプール等の影響でしょうか、知識不足で原因究明に至りませんでした。)

条件分岐の仕方やWHERE句が連続しないよう

|クエリ1 ->|クエリ4 ->|クエリ5 ->|クエリ2 ->|クエリ3 ->|クエリ6 ->|クエリ1(次の周回) ->|...|
|---|-------|--------|--------|-|-|-|-|
|ELT FIELD|CASE WHEN|条件分岐なし|ELT FIELD|CASE WHEN|条件分岐なし|ELT FIELD|...|
|WHERE ... BETWEEN|WHERE IN|WHERE ... BETWEEN|WHERE IN|WHERE ... BETWEEN|WHERE IN|WHERE ... BETWEEN|...|
のように、左から順番にクエリを実行しました。


### EXPLAINによる実行計画
参考用に各クエリの1バッチの実行計画を掲載します。

クエリ1,3,5

| select_type | table         | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
|-------------|---------------|------------|-------|---------------|---------|---------|-------|------|----------|-------------|
| UPDATE      | policy_policy | NULL       | range | PRIMARY       | PRIMARY |       8 | const | 1864 |      100 | Using where |

クエリ2,4,6

| select_type | table         | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
|-------------|---------------|------------|-------|---------------|---------|---------|-------|------|----------|-------------|
| UPDATE      | policy_policy | NULL       | range | PRIMARY       | PRIMARY |       8 | const | 1000 |      100 | Using where |



#結果

全データと要約を掲載します。

### 全データ

|| クエリ1<br>(実行時間)| クエリ1<br>(手動計測)| 手動計測 - <br>実行時間| クエリ2<br>(実行時間)| クエリ2<br>(手動計測)|手動計測 - <br>実行時間|クエリ3<br>(実行時間)|クエリ3<br>(手動計測)| 手動計測 - <br>実行時間|
|-|-------------|------------|--------------|--------------|-------------|--------------|--------------|-------------|--------------|
|1|        47.9 |       50.8 |          2.9 |           48 |        51.9 |          3.9 |         48.8 |        54.7 |          5.9 |
|2|        49.2 |         52 |          2.8 |         48.9 |          53 |          4.1 |           47 |        52.7 |          5.7 |
|3|        47.2 |         50 |          2.8 |         48.9 |        53.1 |          4.2 |         48.9 |        54.3 |          5.4 |
|4|        50.9 |       53.3 |          2.4 |         48.2 |        52.3 |          4.1 |         45.6 |          51 |          5.4 |
|5|        46.7 |       49.6 |          2.9 |         49.1 |        53.3 |          4.2 |           48 |        53.6 |          5.6 |
|6|        47.2 |       49.9 |          2.7 |         49.1 |        53.2 |          4.1 |         47.9 |        53.6 |          5.7 |
|7|        48.4 |       50.1 |          1.7 |         48.7 |          53 |          4.3 |         48.4 |          54 |          5.6 |
|8|        48.9 |       51.7 |          2.8 |         48.5 |        52.7 |          4.2 |         47.7 |        53.5 |          5.8 |
|9|        46.8 |       49.5 |          2.7 |         48.1 |        52.2 |          4.1 |           48 |        53.8 |          5.8 |
|10|        49.2 |       51.8 |          2.6 |         46.2 |        50.1 |          3.9 |         47.1 |        52.4 |          5.3 |
|Avg.|       48.24 |      50.87 |         2.63 |        48.37 |       52.48 |         4.11 |        47.74 |       53.36 |         5.62 |
|SD| 1.337659648 | 1.27021433 | 0.3591656999 | 0.8628763269 | 0.958934594 | 0.1286683938 | 0.9777525249 | 1.072069649 | 0.1988857852 |


|      | クエリ<br>4(実行時間)| クエリ4<br>(手動計測)|手動計測 - <br>実行時間| クエリ5<br>(実行時間)| クエリ5<br>(手動計測)|手動計測 - <br>実行時間| クエリ6<br>(実行時間)| クエリ6<br>(手動計測)|手動計測 - <br>実行時間|
|------|-------------|-------------|--------------|-------------|-------------|--------------|--------------|--------------|--------------|
|    1 |        47.1 |          55 |          7.9 |        40.2 | 40.8        |          0.6 |           42 | 44.1         |          2.1 |
|    2 |        49.3 |          57 |          7.7 |        43.7 |        44.3 |          0.6 |         42.5 |         44.2 |          1.7 |
|    3 |          49 |        56.8 |          7.8 |        42.9 |        43.5 |          0.6 |         43.5 |         45.1 |          1.6 |
|    4 |        49.4 |        57.3 |          7.9 |        44.9 |        45.5 |          0.6 |         44.3 |           46 |          1.7 |
|    5 |        46.9 |        54.6 |          7.7 |        41.4 |        41.9 |          0.5 |         42.9 |         44.7 |          1.8 |
|    6 |        49.9 |        57.5 |          7.6 |        44.4 |          45 |          0.6 |         42.4 |         44.2 |          1.8 |
|    7 |        47.8 |        55.4 |          7.6 |        43.9 |        44.5 |          0.6 |         44.2 |         45.8 |          1.6 |
|    8 |        50.3 |          58 |          7.7 |        43.3 |          44 |          0.7 |         43.5 |         45.2 |          1.7 |
|    9 |        49.4 |        57.1 |          7.7 |        41.6 |        42.1 |          0.5 |         44.3 |         45.3 |            1 |
|   10 |        50.3 |          58 |          7.7 |        44.4 |        44.9 |          0.5 |         43.1 |         44.9 |          1.8 |
| Avg. |       48.94 |       56.67 |         7.73 |       43.07 |       43.65 |         0.58 |        43.27 |        44.95 |         1.68 |
| SD   | 1.248287716 | 1.230221299 | 0.1059349905 | 1.536265313 | 1.552238527 | 0.0632455532 | 0.8313977521 | 0.6620674689 | 0.2780887149 |


### 要約
上からクエリ番号(クエリ容量), 実行時間, 手動計測時間, 手動計測時間 - 実行時間の平均の順にまとめました。

|               | ELT FIELD                                 | CASE WHEN                                 | 条件分岐なし                              |
|---------------|-------------------------------------------|-------------------------------------------|-------------------------------------------|
| WHERE BETWEEN | クエリ1 (1.2MB)<br>48.24<br>50.87<br>2.63 | クエリ3 (1.5MB)<br>47.74<br>53.36<br>5.62 | クエリ5 (5KB)<br>43.07<br>43.65<br>0.58   |
| WHERE ... IN  | クエリ2 (1.8MB)<br>48.37<br>52.48<br>4.11 | クエリ4 (2.1MB)<br>48.94<br>56.67<br>7.73 | クエリ6 (603KB)<br>43.27<br>44.95<br>1.68 |

# 考察
厳密な統計学的検定は行いませんが、**ELT + FIELD**と**CASE WHEN**を使った場合で**実行時間**に目立った違いは見られません。
一方WHERE句が同じ場合には、**操作が返ってくるまでの時間(手動計測時間)**、及び**手動計測時間 - 実行時間**が
**ELT + FIELD**を使用したクエリである程度短くなることがわかりました。
クエリ4と1では手動の計測時間で約5秒の違いがあることがわかります。
クエリ容量から分かる通り**ELT + FIELD**を使用したクエリではクエリが短く、、実行以前のクエリの読み込みのようなフェーズで時間がかかっていることが予想されます(がこれも予想に過ぎないので、もし心当たりのある優しい方おりましたらコメントお願いします)。
この傾向は6クエリのほとんどに当てはまりますが、クエリ3とクエリ4に関してその関係が逆転している点は気になります。

また本筋からはそれますが、**条件分岐あり**と**なし**で実行時間に**5秒**ほどの差があることがわかります。
1000個の条件分岐を持つクエリ50個で5秒なので、今回の場合、単純計算で**1000個の条件分岐を持つクエリ1個で約0.1秒**の計算コストが割り出されます。

# 結び
繰り返しにはなりますが、**CASE WHEN構文**を**ELT + FIELD**に書き直すことによる**実行時間の差異は確認できませんでした**。
Djangoのbulk updateは**CASE WHEN構文**を使用しているので、本記事の結果が当てはまるならば
そのクエリを**ELT + FIELD**で書き直すことができるとしても、この方法で実行時間自体を短縮できません。
但し、手動計測による結果から、クエリの読み込み自体に時間がかかるくらい、大量のレコードをbulk updateする場合(=大量の条件分岐が存在する長いクエリの場合)に、
**CASE WHEN**を**ELT + FIELD**に、 **WHERE IN ...**を**WHERE BETWEEN**に書き換え、クエリ自体が短くなることで
全体の待ち時間を短縮できる可能性が示唆されます。