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関数

ELT(N, str_1, str_2, str_3,..., str_N, str_(N+1), ..., str_M) 
※ str_n: 数字 or 文字列

の形で引数を取り、str_Nを返します。

###FIELD関数

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の値は疑似コードで

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

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

is_science = ELT(FIELD('文学部', '物理学部', '文学部'), 1, 0)

FIELD('文学部', '物理学部', '文学部') = 2なので

=>
is_science = ELT(2, 1, 0)
=> 
is_science = 0

となります。
よって

UPDATE students
  SET is_science = 
    ELT(FIELD(students.department, '物理学部', '文学部'), 1, 0)
  WHERE students.department IN ('物理学部', '文学部')

とすることでdepartmentに対し動的にis_scienceを更新することができます。

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

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は

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

クエリ4は

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は

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 (実行時間)	クエリ1 (手動計測)	手動計測 - 実行時間	クエリ2 (実行時間)	クエリ2 (手動計測)	手動計測 - 実行時間	クエリ3 (実行時間)	クエリ3 (手動計測)	手動計測 - 実行時間
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

	クエリ 4(実行時間)	クエリ4 (手動計測)	手動計測 - 実行時間	クエリ5 (実行時間)	クエリ5 (手動計測)	手動計測 - 実行時間	クエリ6 (実行時間)	クエリ6 (手動計測)	手動計測 - 実行時間
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) 48.24 50.87 2.63	クエリ3 (1.5MB) 47.74 53.36 5.62	クエリ5 (5KB) 43.07 43.65 0.58
WHERE ... IN	クエリ2 (1.8MB) 48.37 52.48 4.11	クエリ4 (2.1MB) 48.94 56.67 7.73	クエリ6 (603KB) 43.27 44.95 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に書き換え、クエリ自体が短くなることで
全体の待ち時間を短縮できる可能性が示唆されます。