■SlowQuery logの設定
Queryの実行に時間のかかってしまうSlowQueryログを出力するようにmy.cnfを設定
[mysqld]
slow_query_log=ON
slow_query_log_file=mysql-slow.log
long_query_time=0.5
■mysqldumpslow
# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
並べ替えるには「-s」オプションを付けた上でソート順を指定。
- -s al → 平均ロックタイムの長い順
- -s ar → 平均行数の多い順
- -s at → 平均実行時間の長い順
- -s c → 総クエリ数の多い順
- -s l → 総ロックタイムの長い順
- -s r → 総行数の多い順
- -s t → 総実行時間の長い順
■インデックスの確認
mysql> show index from questions;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| questions | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.04 sec)
■explainの使い方、見方１
mysql> explain select * from questions where id = 1;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | questions | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.05 sec)
■explainの使い方、見方２
mysql> explain select * from questions where user_id = 1;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | questions | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
■explainの結果の見方
type ALLの場合：テーブルのフルスキャン
type ALL以外の場合：keyで表示されたインデックスを使った検索となる
フルスキャンで遅いクエリはキーにインデックスの付与を検討する
Using filesort：sortが発生して遅くなっている、クエリが高頻度の場合はインデックス付与を検討
Using temporary：一時ファイルの作成がおこなわれている。要注意。
■インデックスを貼る時は？
主キー：他のテーブルとの関連を取得するときに、主キーのidなどで関連テーブルを取得する
複合インデックス:複数のキーで検索するために、高頻度でフルスキャンが発生している場合などに複合インデックスを張る
■インデックスの貼り方は？
・通常
CREATE TABLE テーブル名(カラム名 型,カラム名 型, ... INDEX(カラム名);
または
CREATE INDEX インデックス名 ON テーブル名(カラム名);
または
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
・複合インデックス
CREATE INDEX インデックス名 ON テーブル名 (カラム名1([数値])[, カラム名2([数値]), ...);
・インデックス削除
ALTER TABLE テーブル名 DROP INDEX インデックス名;
■CentOSでのMySQLの起動、停止、再起動
sudo service mysqld start
sudo service mysqld stop
sudo service mysqld restart
■MySQL設定項目
- max_connections：接続数
- innodb_buffer_pool_size：メモリ使用量
■crontab
毎週日曜日 AM3時に実行 日付のファイル名でバックアップファイルを作成
00 3 * * 7 mysqldump -u root -p “password” —all-databases -single-transaction > “/backup/`date '+%Y-%m-%d'`-backup.dump”
毎週日曜日 AM7時に実行 古くなったバックアップファイルを削除
00 7 * * 7 find /backup -name "*backup.dump" -mtime 180 -delete