MySQLで WHERE句に工夫が必要だった話

プログラマー

注意
この内容は現職の MySQL 5.1系を使ったオンプレミス環境での話で最新の MySQLでも再現する現象とは限りません。

ある日の夜に突然…

ある日の夜に現職の社内システムから障害発生を知らせるメールが届きました。障害が発生したのは夜間バッチの集計プログラムで、今まで 2〜3分で完了していた集計のストアドプロシージャが 30分以上かかったためタイムアウトしたというエラー内容でした。

原因がよくわからない…

ずっと問題なく動作していたストアドプロシージャだったので原因特定にはかなり苦戦しました。問題となったテーブルとクエリはだいたい下記のような感じです。(わかりやすくするため少し簡略化しています。)

問題のテーブル

create table `sample` (
  `c1` smallint not null default 0,
  `c2` int not null default 0,
  `c3` int not null default 0,
  `c4` smallint,
  `c5` int,
  `c6` bigint,
  `c7` datetime,
  `c8` timestamp,
  primary key(`c1`, 'c2', 'c3')
);
問題のクエリ

INSERT INTO [一時テーブル]
  SELECT * FROM sample [別テーブルと結合] WHERE c2 >= 10000000
該当のテーブルは元々レコード数の多いテーブルですが、障害発生の前日と比べてデータが爆発的に増えたわけでもなく OS側のメモリや HDD容量にも特に問題は発生していませんでした。

原因は WHERE句

色々と調査を重ねた結果、問題のクエリを下記のように修正すると元通り集計が 2〜3分で完了するようになりました。

修正後のクエリ

INSERT INTO [一時テーブル]
  SELECT * FROM sample [別テーブルと結合] WHERE c1 <> 0 AND c2 >= 10000000
※ c1列には論理的に 0が存在しない。
この結果から考えられる原因は問題のクエリでは PRIMARY KEY等のインデックスが適切に使用されずに MySQL内部の作業領域が限界を超えたという事なのだと思います。そのため WHERE句に PRIMARY KEYの順番通りに条件を書いた事でインデックスが適切に使用されるようになり問題が解消したのでしょう。

MySQLって気難しい…

現職で MySQLを使う前は SQL Serverばかり使っていましたが、SQL Serverでこのような事で悩まされる事はありませんでした。MySQLは結合が遅いというのは比較的有名なので注意していましたが、ここまで気を使わないといけないのは悩ましいですね。あくまで MySQL 5.1系の話なので最新版では解消しているといいのですが…。