MySQLをチューニングしてみた

ITエンジニアをやっていて、避けられないことの1つは、DB(DataBase)との闘いかもしれない。そう、いかに高速化するかである。

果てしない死闘の末に、勝利を手にするか、敗北の苦渋を舐めるか・・・

今日は、勝利の美酒を少しでも手に入れるために、こんな方法をやっています。

①EXPLAIN & Session STATUSを見る

EXPLAIN 実際のクエリー ;　とやると、利用するインデックスや、どのくらいの行数をスキャンしているかなど、実に様々な情報が得られます。すばらしい。

しかし、気をつけたいのは、EXPLAINは「サンプリングによる結果を表示している」という点です。このサンプリング(sampling)は、統計用語です。つまり、「全部を試すのは時間かかるしコストだから、n回やったものの平均とかをとって、結果としよう」ということです。

別に間違ったわけでもないですし、素早く結果を出すの非常に便利ですが、サンプリングにより導き出した結果と、実行結果が異なると異なります。

さて、ということは、「実行結果」が知りたいのです。実行結果とは、実際に「利用したインデックスや、どのくらいの行数をスキャンしたか」です。

それが、次のコマンドです。

    FLUSH STATUS;
    実際のクエリー ;
    SHOW SESSION STATUS LIKE 'Handler\_%';

Flush Statusはセッションスコープのステータス変数をクリアします。余計な値が混じらないように、念のため実行しています。

SHOW SESSION STATUS LIKE 'Handler\_%';で出てくるHandler_ read_ next は、実際にどのくらいの行を読み込んだかを表示します。

実際にどのインデックスを利用したかどうか、また本当に最適なインデックスを使用したかどうかは、残念ながら都度USE INDEXを加えたりしながら、様子を見るしかありません。みんないろんなインデックスを作っては試し、また破棄し、作り直す、、、みたいなことをやっています。

    ALTER TABLE `table1` ADD INDEX `id_ix`(`id`);  (idを`id_ixというインデックスとして、table1のインデックスに追加する)
    ALTER TABLE `table1` ADD INDEX `multi_ix`(`id`, `id2`);  (idとid2による複合インデックスをmulti_ixとして、table1のインデックスに追加する)
    ALTER TABLE `table1` DROP INDEX `id_ix`;  (table1の`id_ix`というインデックスを削除する)

②Logに書く

こっちの方が簡単ですね。long_query_time=0にすることで、全てのクエリー を保存します。mysqlは、restartしてください。

    [mysqld]
    slow_query_log = 1
    slow_query_log_file = /Users/var/log/mysql_log/slow_query.log
    long_query_time = 0

すると、ファイルパスに指定されている/Users/var/log/mysql_log/slow_query.logには、次のような情報が書き込まれます。

 Query_time: 0.513508
 Lock_time: 0.000554
 Rows_sent: 11716
 Rows_examined: 71146
 SET timestamp=1542860587;
 SELECT
 `table1`.`group_id` AS `group_id`,
 `table1`.`type_id` AS `type_id`,
...

Query_time（クエリーにかかった時間）, Rows_sent（抽出された行数）, Rows_examined（抽出するためにスキャンした行数）

Rows_sentと Rows_examinedの差異が少ないほど、より効率的なクエリーになります。ですが、これを縮めるのはかなり骨が折れることも事実です。

もっと効率的にslow queryを調べる方法は、またの機会に。