2011年10月30日日曜日

[mysql]indexが使われていないSQLを調べる方法

log_queries_not_using_indexesというオプションを有効にすることによって、indexが使われていないSQLを抽出することができる。

log_queries_not_using_indexesオプションの値の確認・設定方法


global variablesの値が「on」になっていれば、indexが使われていないSQLが抽出できます。
mysql> show  global variables like '%indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

もし、offになっている場合には以下のコマンドで有効に変更できます。
mysql> set global log_queries_not_using_indexes = on;

抽出されたSQLの出力先

log_queries_not_using_indexesのログは、slow queryログに出力されるのでslow queryの設定で確認できます。

slow_query_logが、slow queryログを出力するかどうかの設定。onになっていれば、ログが出力されます。
slow_query_log_fileが、ログの出力先となります。
mysql> show global variables like '%slow%';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| log_slow_queries    | ON               |
| slow_launch_time    | 2                |
| slow_query_log      | ON               | 
| slow_query_log_file | c:\work\slow.log |
+---------------------+------------------+
4 rows in set (0.00 sec)

slow queryの設定は、以下のように「set global」コマンドで変更できます。
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log_file = 'c:\\slow.log';
Query OK, 0 rows affected (0.00 sec)

動作を確認してみよう


テスト用に主キーのみのテーブルを作成
mysql> create table test_table (col1 char(1), primary key (col1));
Query OK, 0 rows affected (0.02 sec)

以下の2種類のSQLを実行。前者のSQLは、主キー検索のためindexが使用される。
後者のSQLは、関数を使用しているのでindexは使用されずに検索が行われる。
mysql> select * from test_table where col1 = '1';
Empty set (0.00 sec)

mysql> select * from test_table where substr(col1, 1, 1) = '1';
Empty set (0.00 sec)

slow queryログには、以下のようにindexが使用されなかったSQLの情報が取得できる。
# Time: 111030 16:00:38
# User@Host: root[root] @ localhost [::1]
# Query_time: 0.000500  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use test_db;
SET timestamp=1319958038;
select * from test_table where substr(col1, 1, 1) = '1';

これを使えばindexの張り忘れや、SQLのつくりが原因でindex使われていないSQLを機械的に抽出できるかなと。