MySQLのスロークエリログを有効化して遅いSQLを特定する方法｜出力設定からmysqldumpslowとEXPLAINまで

この記事のポイント

・スロークエリログはmy.cnfにslow_query_log=ONを追記するだけで有効化できる
・long_query_timeを1秒に設定すると実用的な閾値になる（デフォルト10秒は見逃しが多い）
・mysqldumpslow -s t -t 10でボトルネックSQLを合計実行時間の多い順に絞り込める
・EXPLAINのtype=ALLは全件スキャンを意味し、インデックス追加で劇的に改善できる

[mysqld]
# スロークエリログの有効化
slow_query_log = ON
# ログの出力先
slow_query_log_file = /var/log/mysql/slow-query.log
# スロークエリと判定する閾値（秒）
long_query_time = 1
# インデックスを使わないクエリも記録する
log_queries_not_using_indexes = ON

# Rocky Linux 9 / systemd環境でのMySQL再起動
sudo systemctl restart mysqld

# ディレクトリ作成と権限設定
sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql

# MySQL CLIでrootまたは管理者ユーザーとして接続後に実行
mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
mysql> SET GLOBAL long_query_time = 1;
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';

mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/log/mysql/slow-query.log  |
+---------------------+--------------------------------+

mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

# 合計実行時間が長い順に上位10件を表示
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Reading mysql slow query log from /var/log/mysql/slow-query.log
Count: 147  Time=3.82s (561s)  Lock=0.00s (0s)  Rows=1234.5 (181472), app[app]@webserver01.example.com
  SELECT * FROM orders WHERE status = 'S' AND created_at > 'S'

Count: 89   Time=2.14s (190s)  Lock=0.01s (1s)  Rows=8921.3 (794197), app[app]@webserver01.example.com
  SELECT u.*, p.* FROM users u LEFT JOIN profiles p ON u.id = p.user_id WHERE u.last_login > 'S'

Count: 312  Time=0.98s (305s)  Lock=0.00s (0s)  Rows=45.2 (14102), app[app]@webserver01.example.com
  SELECT COUNT(*) FROM access_logs WHERE session_id = 'S' AND created_at BETWEEN 'S' AND 'S'

# Query_timeが3秒以上のエントリを前後5行ずつ表示する
grep -A 5 "Query_time: [3-9]" /var/log/mysql/slow-query.log | head -60

# Time: 2026-08-17T09:23:41.512847Z
# User@Host: app[app] @ webserver01.example.com [192.168.1.101]
# Query_time: 4.213847  Lock_time: 0.000312  Rows_sent: 2847  Rows_examined: 987654
SET timestamp=1755427421;
SELECT * FROM orders WHERE status = 'processing' AND created_at > '2026-08-01 00:00:00';

-- EXPLAINはSELECT文の前に付けるだけ（\Gで縦表示）
EXPLAIN SELECT * FROM orders WHERE status = 'processing' AND created_at > '2026-08-01 00:00:00'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 987654
     filtered: 1.50
        Extra: Using where

-- 複合インデックスの追加
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

EXPLAIN SELECT * FROM orders WHERE status = 'processing' AND created_at > '2026-08-01 00:00:00'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: range
possible_keys: idx_orders_status_created
          key: idx_orders_status_created
      key_len: 8
          ref: NULL
         rows: 312
     filtered: 100.00
        Extra: Using index condition

# /etc/logrotate.d/mysql-slow の内容例
/var/log/mysql/slow-query.log {
    daily
    missingok
    rotate 14
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    postrotate
        if test -x /usr/bin/mysqladmin &&            /usr/bin/mysqladmin ping &>/dev/null
        then
            /usr/bin/mysqladmin flush-slow-logs
        fi
    endscript
}

# スロークエリログのサイズと最終更新日時を確認する
ls -lh /var/log/mysql/slow-query.log

# ディスク全体の空き容量も確認する
df -h /var/log

mysql> SHOW VARIABLES LIKE '%slow%';
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_extra            | OFF                            |
| slow_query_log            | ON                             |
| slow_query_log_file       | /var/log/mysql/slow-query.log  |
+---------------------------+--------------------------------+

# ログファイルの所有者・権限を確認する
ls -la /var/log/mysql/

# MySQLプロセスの実行ユーザーを確認する（通常はmysqlユーザー）
ps aux | grep mysqld | grep -v grep

# MySQLエラーログの末尾を確認する（Rocky Linux 9の場合）
sudo tail -50 /var/log/mysqld.log

-- 1000行以上スキャンしたクエリのみ記録するように制限する
mysql> SET GLOBAL min_examined_row_limit = 1000;

mysql> SET GLOBAL log_queries_not_using_indexes = 'OFF';

# mysqldumpslowの場所を確認する
which mysqldumpslow

# 見つからない場合はfindで探す
find /usr -name 'mysqldumpslow' 2>/dev/null

# Rocky Linux 9でパスが通っていない場合はフルパスで実行
/usr/bin/mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log