はじめに
こんにちは、アソビュー株式会社でバックエンドエンジニアをしています竹村です。
ありがたいことにアソビューのサービスも長く続き、ご利用いただいているユーザーが増えるにしたがってデータも増え、SQLのパフォーマンス改善を行なうタスクが増えてきました。
今回はそんなSQLのパフォーマンス改善をしているなかで遭遇した問題とその対策について書こうと思います。
問題の概要と背景
パフォーマンス上問題がありそうなクエリを調査しているなかで、ごく一部のテーブルですが、数値であるべきカラムが誤って文字列型で定義されていたことがわかりました。
そしてこの型の不一致が原因で、SQLクエリの実行時にインデックスが効率的に利用できず、結果としてクエリのパフォーマンス低下を引き起こしていました。
問題の詳細
実際のテーブルとは大幅に変更、簡略化していますが、下記のような感じで本来user_statuses.user_idはintであるべきところをvarcharにしてしまっていたというのが今回の問題になります。
create table users ( user_id int auto_increment primary key, email varchar(255) not null ) create table user_statuses ( user_statuses_id int auto_increment primary key, user_id varchar(45) not null status_code varchar(4) not null ) create index idx_user_id on user_statuses (user_id);
型が違うと何が問題かというと、例えば下記のようなクエリを実行した際にインデックスが使われずフルスキャンされてしまいます。 今回は25万件程度のテーブルで検証を行いましたが単純なクエリにもかかわらず300ms以上かかっています。
そしてインデックスが使われない理由にもなりますが、型が違ってもクエリ実行時に暗黙的に変換されるためエラーになりません。この挙動は親切ではありますが、個人的にはエラーにならないのが一番怖いです。
select * from users join user_statuses on users.user_id = user_statuses.user_id where users.user_id = 10000 372 ms (execution: 341 ms, fetching: 31 ms) で 1 から 1 行を取得しました -- 実行計画 +--+-----------+-------------+----------+-----+-------------+-------+-------+-----+------+--------+-----------+ |id|select_type|table |partitions|type |possible_keys|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------------+----------+-----+-------------+-------+-------+-----+------+--------+-----------+ |1 |SIMPLE |users |null |const|PRIMARY |PRIMARY|4 |const|1 |100 |null | |1 |SIMPLE |user_statuses|null |ALL |idx_user_id |null |null |null |250897|10 |Using where| +--+-----------+-------------+----------+-----+-------------+-------+-------+-----+------+--------+-----------+
初期の解決策: 関数インデックスの導入
関数インデックスの概要
関数インデックスとは、カラムの値をそのままインデックス化するのではなく、指定した関数を適用した結果をインデックス化するものです。これにより、カラムの値を動的に変換し、その変換後の結果に対してインデックスを作成することができます。
以下にサンプルを記載します。
create index idx_user_id_to_int on user_statuses ((cast(user_id as signed))); -- 使う際は関数インデックスを作った時と同じ形で条件指定する select * from users join user_statuses on users.user_id = (cast(user_statuses.user_id as signed)) where users.user_id = 10000 65 ms (execution: 46 ms, fetching: 19 ms) で 1 から 1 行を取得しました -- 実行計画 +--+-----------+-------------+----------+-----+------------------+------------------+-------+-----+----+--------+-----+ |id|select_type|table |partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra| +--+-----------+-------------+----------+-----+------------------+------------------+-------+-----+----+--------+-----+ |1 |SIMPLE |users |null |const|PRIMARY |PRIMARY |4 |const|1 |100 |null | |1 |SIMPLE |user_statuses|null |ref |idx_user_id_to_int|idx_user_id_to_int|8 |const|1 |100 |null | +--+-----------+-------------+----------+-----+------------------+------------------+-------+-----+----+--------+-----+
実行計画、速度ともに大幅に改善しました。やった、これで解決!!と思いきや・・・。
MySQLにおける関数インデックスの問題
とてもうまくいったように見えた関数インデックスですが、すぐに致命的な問題が発生することがわかりました。 例えば下記のようにwhere句を書き換えるだけでインデックスが効かなくなります。
select * from users join user_statuses on users.user_id = (cast(user_statuses.user_id as signed)) where users.user_id in (10000, 20000) 229 ms (execution: 208 ms, fetching: 21 ms) で 1 から 2 行を取得しました -- 実行計画 +--+-----------+-------------+----------+-----+-------------+-------+-------+----+------+--------+------------------------------------------+ |id|select_type|table |partitions|type |possible_keys|key |key_len|ref |rows |filtered|Extra | +--+-----------+-------------+----------+-----+-------------+-------+-------+----+------+--------+------------------------------------------+ |1 |SIMPLE |users |null |range|PRIMARY |PRIMARY|4 |null|2 |100 |Using where | |1 |SIMPLE |user_statuses|null |ALL |null |null |null |null|250897|100 |Using where; Using join buffer (hash join)| +--+-----------+-------------+----------+-----+-------------+-------+-------+----+------+--------+------------------------------------------+
速度も遅くなり、フルスキャンも発生してしまっています。
この動作がバグか仕様かは置いておくとして、どうもMySQL 8で関数インデックスをjoinで使うのは問題がある模様。
参考： MySQL Bugs: #98937: Functional indexes prevent JOINs to use them
やっぱりテーブル作りなおすしかないのかと絶望していたところ、SREチームの同僚から仮想カラムを作成してそのカラムのインデックスを作成してみてはどうかとアドバイスを貰いました。
最終的な解決策: 仮想カラムとインデックスの作成
仮想カラムを使用した実質的な関数インデックスの作成
アドバイスに従って仮想カラムを明示的に作成しそのカラムに対してインデックスを作成します。
ちなみに仮想カラムと言ってますが、MySQLではGeneterated Columnという名前で、関数インデックスと同様に関数で導出した結果をカラム化したものです。計算結果を保持するstoredと保持しないvirtualの2種類があり、今回はvirtualを使うので仮想カラムと呼ぶことにします。
virtualは値が保持されないため毎回計算するというデメリットがありますが、今回欲しいのはインデックスであり、インデックスには計算結果が格納されるため、カラムに計算結果が保持されていなくても特に問題はありません。
関数インデックスとの違いは、名前のとおりインデックスではなくカラムなのでnot nullなどの制約をかけることもできますし、カラムに対してインデックスを作成することもできます。今回はこれを実質的な関数インデックスとして利用します。
サンプルは以下のとおりです。
alter table user_statuses add column user_id_to_int int generated always as ((cast(`user_id` as signed))) virtual, algorithm=instant; create index idx_user_id_to_int on user_statuses (user_id_to_int) algorithm=inplace lock=none;
使う際は条件指定に仮想カラムを指定します。
select * from users join user_statuses on users.user_id = user_statuses.user_id_to_int where users.user_id in (10000, 20000)
パフォーマンス改善の結果
さきほど問題があったin句を使ったクエリで比較してみたところ、無事に速度・実行計画ともに改善されました。
-- 改善前 select * from users join user_statuses on users.user_id = user_statuses.user_id where users.user_id in (10000, 20000) 269 ms (execution: 249 ms, fetching: 20 ms) で 1 から 2 行を取得しました -- 改善後 select * from users join user_statuses on users.user_id = user_statuses.user_id_to_int where users.user_id in (10000, 20000) 80 ms (execution: 52 ms, fetching: 28 ms) で 1 から 2 行を取得しました -- 実行計画 +--+-----------+-------------+----------+-----+------------------+------------------+-------+-------------+----+--------+-----------+ |id|select_type|table |partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra | +--+-----------+-------------+----------+-----+------------------+------------------+-------+-------------+----+--------+-----------+ |1 |SIMPLE |users |null |range|PRIMARY |PRIMARY |4 |null |2 |100 |Using where| |1 |SIMPLE |user_statuses|null |ref |idx_user_id_to_int|idx_user_id_to_int|5 |users.user_id|1 |100 |null | +--+-----------+-------------+----------+-----+------------------+------------------+-------+-------------+----+--------+-----------+
まとめ
仮想カラムと関数インデックスを用いた解決策の有効性
通常、カラムの型を間違えてしまうとテーブルを作り直す必要があり、無停止で実施しようとすると正しい定義のテーブルを作って両方に書き込みする仕組みを作って最後に入れ替えたりと結構な手間がかかります。
仮想カラムと関数インデックスを用いた今回の方法ならカラム追加とインデックスの追加というオンライン実行できるDDLで完結するため、応急処置としては有効な手段だと感じました。
今後の教訓や注意点
カラムの型のようなテーブル定義のミスは暗黙的に変換されクエリが通ってしまい、また作った当初はデータも少なく問題が顕在化しづらいため問題発覚まで年単位の月日がかかることもあり、レビューの段階で発見することが非常に重要になります。
仮にcreate文単体で違和感を抱かなかったとしても実際に使用されるクエリを作成してみて、実行計画を見れば気付けた可能性が高く、改めて実際に使われるクエリについて検討することや実行計画を見ることの重要性を感じました。
最後に
アソビューではより良いプロダクトを世の中に届けられるよう共に挑戦していくエンジニアを募集しています。
カジュアル面談も実施していますので お気軽にエントリーしていただければと思います。
