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を機械的に抽出できるかなと。