MySQLのIN句は遅くなりやすい

はじめに

医療系クラウドサービスを提供しているレイヤードという会社で働いています。

あるサービスのMySQLデータベースにおいてslowqueryが多発して、CPU使用率も99.8%まで爆上がりするという事件が起きましたので、その時の調査した内容の共有です。

1.IN句で指定する値の数が多いとヤバい

今回の事件はコーディングミスによりIN句で指定する値の数が1.5万個にもなるコードを混入させたのが原因でした。以下はスローログの画面です。

DB周り

• エンジンバージョン：8.0.mysql_aurora.3.02.2
• インスタンスクラス：r5.2xlarge
• 対象テーブルのレコード数：1,000万レコード

発行したクエリ

IN句で指定したidの数は15,000個程度

SELECT COUNT(*) FROM patients WHERE id IN (1,2,3,4...)

検証

同DBに以下のSQLを投げてみた。
※書き込み等が頻繁に発生してる実運用中のDBだともっと劣化すると思います。

• IN句で指定するidの数を15,000個で4.43s秒程度
• IN句で指定するidの数を7,000個で0.22s程度
• IN句で指定するidの数を1,000個で0.10s程度

考察

• 7,000個と15,000個の間で大きく劣化する事が分かった
• slowlogの画面を見ると、当該クエリが終わる前に次のクエリが発行され時間経過と共に同時実行数が増えていった
• 当該クエリの同時実行数が増えると共にパフォーマンスが劣化し、自身のクエリ及び他のクエリも含めて全体的にどんどん遅くなっていくという悪循環に陥っていったと思われる

対応

• そもそもコーディングのミスだったので（1.5万個もid指定する必要無い）ので普通に修正
• IN句を利用する時は想定個数を最大7,000個で考える（検証結果を見る限り1万個には近づきたくない）
• 7,000個を超える場合はクエリを分割して複数回実行するようにする

※基本方針としてなるだけDBのチューニングはしない方針です

2.JOINと一緒にIN句を使うと適切なインデックスが選ばれない場合がある

データを集計す為に実行したクエリがやたら遅い（タイムアウトする場合も多々ある）ので調査しました。

DB周り

• エンジンバージョン：8.0.mysql_aurora.3.02.2
• インスタンスクラス：r5.2xlarge
• インデックス：複合index（sheets.hospital_id,sheets.created_at）、他
• 対象テーブルのレコード：1,000万レコード
• カウント結果：18万個
• 実行時間：ほぼタイムアウトする

発行したクエリ

SELECT
 count(*) 
FROM
 sheets LEFT JOIN hospitals ON sheets.hospital_id=hospitals.id
WHERE
  sheets.hospital_id IN (1,2,3,4,5...)
AND
  sheets.created_at BETWEEN '2023-4-01' AND '2023-4-30 23:59:59'
AND 
  hospitals.summary_flg=1

検証

• 上記クエリ（IN句で指定したidの数は700個程度）をexplainすると割と無関係なindexが採用されていました
• hospital_idをINで指定する個数を2個にしても割と無関係なindexが採用されました
• hospital_idをINではなく=にすると目的のindexが採用されました
• hospitalsテーブルのJOINをやめて2回のクエリに分けると、IN句を利用しても目的のindexが利用されました

考察

• id2個でも目的のindexを採用してもらえなくなったのでeq_range_index_dive_limitは関係なさそう

対応

• 今回は単純にJOINとIN句を併用すると適切なインデックスが選ばれなくなるので、クエリを分けて運用する事にしました（Railsだと以下のような感じ）

hospital_ids = Hospital.where(id, [1,2,3,4...]).where(summary_flg: 1)
sheets = Sheet.where(hospital_id: hospital_ids).where("created_at BETWEEN ? AND ?", '2023-4-01 00:00:00', '2023-4-30 23:59:59')

参考

• freeさんの記事が大変参考になりました
  https://developers.freee.co.jp/entry/large-in-clouse-length-cause-full-scan