[MySQL]インデックスによるチューニング〜ログ出力、EXPLAIN、インデックス〜

1.ログ出力

1-1.スロークエリログ（slow_query_log）

MySQLの設定は/etc/my.cnfに記述する。

vim /etc/my.cnf

/etc/my.cnfファイルに以下の設定をする。

[mysqld]
slow_query_log = 1 #スロークエリログ出力有効化
slow_query_log_file = mysql-slow.log #スロークエリログファイル名
long_query_time = 2 #スロークエリとして認識させる時間[秒]
log-long-format #インデックスを使用していないクエリをログに残す

• 上記の設定は[mysqld]のところに書かなければ正しく変数名を解決してくれないので注意。
• slow_query_logはONではなく1としなければスロークエリログ出力が有効化されないので注意。
• slow_query_log_fileの場所はfind パス名 -name mysql-slow.logで検索すればわかる。　→　/var/lib/mysql/mysql-slow.logに生成された。
• long_query_timeで設定した秒数以上かかるクエリがスローログに書かれる。
• log-long-formatと記述することで、インデックスが使われていないクエリもログに残せるようになる。
• 設定ファイルいじったら、mysqld再起動忘れずに！

/etc/init.d/mysqld restart

• スローログをリアルタイムで見るには、tail -f /var/lib/mysql/mysql-slow.log

　↓　ログの中身　↓

1行目　記録日時
2行目　ユーザーIDとリクエストした端末
3行目　Query_time（実行時間）　Lock_time(ロック時間)　Rows_sent（送信行数）　Rows_examined（処理対象となった行数）
4行目　SQL文

• スロークエリを特定できたら、そのクエリの前にEXPLAINをつけることで、MySQLのインデックス効果を見積もることができる

1-2.一般クエリログ

まず、ログの出力先ディレクトリを作成。

mkdir /var/log/mysql

次に、my.cnfに以下を記述。

[mysqld]
general-log=TRUE
general-log-file=/var/log/mysql/query.log

最後、my.cnfいじったら再起動。

/etc/init.d/mysqld restart

2.EXPLAINコマンド

EXPLAIN SQL文

• そのクエリがどのような順番でどのような方法でテーブルを 検索するかを知ることができる
• rowsの値の積が、検索にかかる行数の見積もり

3.インデックス

3-1.インデックスの閲覧

SHOW INDEX FROM テーブル名;

3-2.インデックスの作成

テーブル作成時にインデックス作成

CREATE TABLE テーブル名(
  id INT NOT NULL PRIMARY KEY,
  カラム名1 CHAR(8) DEFAULT NULL,
  カラム名2 CHAR(5) DEFAULT NULL,
  INDEX インデックス名(カラム名)
);

ALTER TABLE文でインデックス作成

ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名); 

ALTER TABLE文で複合インデックス作成

CREATE INDEX インデックス名 ON テーブル名 (カラム名1([数値])[, カラム名2([数値]), ...);

3-3.インデックス削除

ALTER TABLE テーブル名 DROP INDEX インデックス名;

【参考URL】
▼インデックスの作成・削除・情報表示・追加
http://phpjavascriptroom.com/?t=mysql&p=index