SHOW INDEX を使う時気は付けないと - Adwaysエンジニアブログ

はじめまして、エンジニアの胡です。

先日ちょうどある事件が起こりました。と言っても、ほぼ MySQL のリファレンスマニュアルに書いてあることですが、軽く共有しておきたいと思います。

環境：
MySQL 5.1.50

とある機能で発行した SQL が一番効率的と思われる INDEX を使ってないことに気づきました。まぁ、別に珍しいことでもないから、FORCE KEY を入れようとしました。
しかし、使ってる ORM（DBIx::Class）が FORCE KEY 非対応のため、その部分では ORM を辞め、素直に SQL に書くことにしました。

このままいつもの平和の日々に戻ってもいいですが、どうしても ORM/SQL を混ぜて使うことに気が済まなくて、いつかきれいにしたくて、もちろんすぐには変えないですが、テーブルの INDEX を張りなおそうと考えました。
そうすると、FORCE KEY を使ってるため、該当 INDEX がなかったら怒られますね。問題なくスムーズに移行できるように、下記のようなロジックを加えました。

if ( the index exists ) {
use sql with force key;
}
else {
use orm method;
}

INDEX 存在してるかどうかの判断は SHOW INDEX を使って簡単に分かるので、さっさと改修しました。

コーディング、よし！
テスト、よし！
実装、よ、(ﾟﾛﾟ;)ｴｪｯ!?、DB が固まった！(((( ;ﾟдﾟ)))
ロールバック、よし！よし！

SHOW INDEX が大変重いの事実が分かりました。
シンジラレナイ！SHOW ですよ？
まぁまぁ、気持ちは分かるけど、事実なんで、信じましょう。

Google 先生に聞いたら、どうやら SHOW INDEX が実行される度に innodb が index 情報を更新するみたいです。テーブルのデータが多いほど遅いです。当たり前ですけど。

ちなみに、本番（slave）ではこんな感じだった。

mysql> show table status like 'hoge'\G
*************************** 1. row ***************************
           Name: hoge
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 89591827
Avg_row_length: 330
    Data_length: 29583671296
Max_data_length: 0
   Index_length: 50444500992
      Data_free: 491782144
Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options: partitioned
        Comment:
1 row in set (13.72 sec)

更新しないように設定を変えられます。

mysql> SET GLOBAL innodb_stats_on_metadata = 0;

この変更によって、業務に特に副作用がないため、これで問題解決しました。サーバ/MySQL 再起動することもあるので、my.cnf にも追加しました。

テスト、よし！
実装、よし！

あ～やっぱり平和一番！(*￣∇￣*)ｴﾍﾍ

参考資料：
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/