スロークエリの特定と書き換え — 遅いのはどのSQLか

スロークエリの特定と書き換え。SQLの書類とストップウォッチ、原因を探す虫眼鏡。

遅いのがどのSQLかを、まず特定します。slow logとperformance_schemaで見つけて、書き換えます。

これは深掘りです。本線の順路ではありません。 読まなくても先へは進めます。 本線の速くする前に必ず測る — パフォーマンス改善とEXPLAINの回の続きです。あの回では「遅いSQLはこれだ」と分かっている状態から始めました。この記事はその手前、「どれが遅いのかを機械で見つける」ところと、見つけたあとの「書き換え」を1本かけてやります。

前回: インデックスとEXPLAIN — 張ったのに使われない

作業場所はVMの中(Ubuntu Server)です。MySQL 8.0 以降が動いていれば、それ以外の準備は要りません。練習用のテーブルはこの記事の中で作って、最後に全部消します。既存のテーブルには一切触りません。

「アプリが遅いんですけど」と言われました。

私が最初にやったことは、心当たりのあるSQLを開くことでした。 一覧画面のクエリです。見るからに重そうだったからです。索引を張って、書き直して、30分かけました。

体感は1ミリも変わりませんでした。

実際に遅かったのは、画面の右上に出ている「未読 3件」のバッジでした。1回あたり 8ミリ秒。速いです。ただし、1ページ表示するたびに 240回呼ばれていました。合計 1.9 秒。一覧のクエリ(1回 120ミリ秒)より、10倍以上の時間を食っていました。

このとき私が持っていなかったものは、はっきりしています。

「どのSQLが、合計で何秒使ったか」の一覧です。

1回が遅いクエリは、目立つので見つかります。 見つからないのは、そこそこ速いものが何万回のほうです。そして、体感を殺しているのはたいてい後者です。

前回は「索引を張ったのに使われない」をやりました。索引の話はあそこで済んでいます。 この記事では索引を張り直しません。やるのは2つだけです。

  1. 遅いクエリを、当てずっぽうではなく機械で見つける
  2. 見つけたクエリを、索引ではなく書き方で直す

対象レベル: 中級〜上級 / 区分: 発展

目次

この記事でわかること

  • 「どのSQLが遅いか」は推測するものではなく、MySQL に出させるものだということ。道具はスロークエリログと performance_schema の2つだということ
  • 1回が遅いクエリより、そこそこ遅いのが何万回のほうが効くこと。だから並べるのは平均時間ではなく合計時間だということ
  • EXPLAIN の rows は推定値で、実測とずれることがあること。ずれたときに打つのが ANALYZE TABLE だということ
  • SELECT * / 大きい OFFSET / 毎回の COUNT(*) / 長すぎる IN は、索引ではなく書き方で直ること
  • 2回目が速いのは、索引が効いたからとは限らないこと。バッファプールに乗っただけのことがあり、測り方を間違えると嘘の成果が出ること
  • スロークエリログは、有効にしたまま放置するとディスクを食うこと。戻すところまでが作業だということ

この記事でやること

  1. 練習用のテーブルを作り、WITH RECURSIVE で30万行を入れる(既存のテーブルには触らない)
  2. スロークエリログをグローバルに有効にし、わざと遅いクエリを流して、ログに出るのを確認する。mysqldumpslow で集計する
  3. performance_schema.events_statements_summary_by_digest を「合計時間が長い順」に並べ、N+1 をそこで見つける
  4. SHOW FULL PROCESSLIST で実行中のクエリを見て、KILL QUERY で1本だけ止める
  5. EXPLAIN ANALYZE で推定と実測を比べ、ずれていたら ANALYZE TABLE とヒストグラムで直す
  6. 書き換えを4パターン、before/after で時間を測って表にする
  7. スロークエリログを元に戻し、練習用の表を全部消す

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
スロークエリログ決めた秒数より遅かったSQLだけをファイルに書き出す MySQL の機能。遅いものを探すための最初の道具
long_query_time「何秒より遅かったら記録するか」の設定。既定は10秒。 つまり素の状態ではほぼ何も記録されない
グローバル / セッションMySQL の設定にはサーバー全体に効くもの(グローバル)と、いま繋いでいる接続だけに効くもの(セッション)がある。ログ出力はグローバルでないと意味がない
performance_schemaMySQL が自分の動作の統計を貯めている専用のデータベース。 表として SELECT できる
ダイジェスト(digest)値の違いを消して、形だけにしたSQL。 WHERE id = 1 も WHERE id = 99 も同じ形としてまとめて数えられる
SUM_TIMER_WAITダイジェストごとの合計実行時間。 単位はピコ秒(1兆分の1秒)。ここを降順に並べるのが本題
プロセスリストいま MySQL に繋がっている接続の一覧。 各接続が何をしていて、何秒経ったかが見える
KILL走っているクエリ、または接続そのものを止める命令。 2種類あり、間違えるとアプリの接続が切れる
キーセットページング「◯件飛ばす」ではなく 「前のページの最後の値より大きいものを取る」という送り方。深いページでも速度が落ちない
バッファプールInnoDB がデータをメモリに載せておく場所。 ここに載っていればディスクを読まない。2回目が速い原因のほとんどはこれ
一時テーブルCREATE TEMPORARY TABLE で作る、その接続だけに見えて、切断すると消える表

この記事で一番大事な言葉は「合計時間」です。 平均が速くても、回数が多ければ合計は大きい。 直すべきはいつも合計が大きいほうです。

練習用のデータを作る

この記事は他の回のデータに依存しません。 ここに書いてある CREATE と INSERT を打てば、そのまま最後まで進めます。

VMの中で MySQL に入ります。

bash
$ sudo mysql

sudo は「管理者としてこのコマンドを実行する」という意味です。MySQL の初期状態では管理者ユーザーでしか入れないのでこうします。この記事では、あとでサーバー全体の設定(グローバル変数)を変えるので、管理者で入る必要があります。

データベースを選びます。

USE appdb;

appdb が無いと言われたら、CREATE DATABASE appdb; を先に打ってから USE appdb; します。この記事で作る表はすべて d_qt_ で始まる名前にするので、既存の表とはぶつかりません(qt は query tuning のつもりです)。

表を2つ作る

CREATE TABLE d_qt_users (
  id    INT          NOT NULL,
  name  VARCHAR(40)  NOT NULL,
  plan  TINYINT      NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE d_qt_orders (
  id         INT          NOT NULL,
  user_id    INT          NOT NULL,
  status     TINYINT      NOT NULL,
  code       VARCHAR(20)  NOT NULL,
  title      VARCHAR(60)  NOT NULL,
  amount     INT          NOT NULL,
  memo       VARCHAR(200) NOT NULL,
  created_at DATETIME     NOT NULL,
  PRIMARY KEY (id)
);
列何のために入れたか
user_id1000種類。 30万行なので1人あたり300行。JOIN と N+1 に使う
status0か1の2種類だけ。 COUNT(*) の実験に使う
amount集計に使う
memo200文字の使わない列。 SELECT * の代金を目に見えるようにするために入れています
created_at日時。ページングの並び順に使う

memo を入れたのには理由があります。 実際のテーブルには、たいていその画面では使わない大きい列があります。SELECT * の害はそこで出ます。 列が5つしかない練習用の表だと差が出ないので、わざと重い列を1本足しています。

30万行入れる

まず、この1行を先に打ってください。

SET SESSION cte_max_recursion_depth = 1000000;

これを忘れると、次の INSERT が1001行目で止まります。 WITH RECURSIVE は「1、2、3…」と自分を呼び出しながら行を作る書き方で、暴走を防ぐために既定では1000回で打ち切られます。 ここでは30万行ほしいので上限を上げます。SET SESSION は、いま繋いでいる接続の中だけの設定です。 MySQL を抜ければ元に戻ります。書き方そのものはサブクエリ3段は読めない — SQL深掘りでやりました。

まず利用者を1000人。

INSERT INTO d_qt_users (id, name, plan)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT n, CONCAT('ユーザー', n), n % 3 FROM seq;

次に注文を30万件。

INSERT INTO d_qt_orders (id, user_id, status, code, title, amount, memo, created_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 300000
)
SELECT
  n,
  (n % 1000) + 1,
  n % 2,
  LPAD(n, 8, '0'),
  CONCAT('注文', n),
  n % 5000,
  REPEAT('x', 200),
  DATE_ADD('2024-01-01 00:00:00', INTERVAL n MINUTE)
FROM seq;

マシンによっては数十秒かかります。 途中で止めないでください。

件数を確認します。

SELECT COUNT(*) FROM d_qt_orders;
+----------+
| COUNT(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.09 sec)

30万行になっていれば準備完了です。 索引は主キーの1本だけです。この記事では索引の話はしません。前回やったので、ここは「見つけ方」と「書き換え」だけに絞ります。

mysql クライアントは、実行時間を最後に出してくれます((0.09 sec) の部分)。この記事の測定は全部これを使います。 ストップウォッチは要りません。

遅いクエリを、機械で見つける

ここが本題です。「たぶんこれが遅い」をやめます。

スロークエリログを有効にする

MySQL には「◯秒より遅かったSQLだけをファイルに書く」機能があります。既定では止まっています。

いまの状態を見ます。

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | OFF                                 |
| slow_query_log_file | /var/log/mysql/ubuntu-slow.log      |
+---------------------+-------------------------------------+

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 10.000000|
+-----------------+----------+

2つとも読んでください。

  • slow_query_log が OFF → 何も記録されていません
  • long_query_time が 10 → 仮に ON にしても、10秒かかるSQLしか記録されません。 Webアプリで10秒かかるクエリはめったにありません。つまり「ONにしたのに何も出ない」は、ほぼこれです

有効にします。ここが分かれ目です。

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;

SET SESSION ではなく SET GLOBAL です。 slow_query_log はそもそもグローバルにしか存在しません(SET SESSION slow_query_log = ON; はエラーになります)。long_query_time は両方ありますが、セッションで変えても、それは自分の接続にしか効きません。 探したいのはアプリが投げているクエリです。アプリは別の接続です。 ここを間違えると「自分で打った分だけ記録されて、アプリの分は1行も出ない」になります。

long_query_time = 0 にしないでください。 0 は全部のクエリを記録するという意味です。練習用のVMなら困りませんが、本番でやると、秒間数百行のログが書かれ、ディスクが埋まり、その書き込み自体でデータベースが遅くなります。 「遅い原因を探すために遅くした」で終わります。まず 0.1 から始めて、多すぎたら上げるのが安全です。

SET GLOBAL は、MySQL を再起動すると消えます。 それは今回は利点です。設定ファイルには書きません。 恒久的に有効にしたい場合だけ /etc/mysql/mysql.conf.d/mysqld.cnf に書きますが、この記事ではやりません。 消し忘れが一番怖いからです。

ここが引っかかりやすい点です。 SET GLOBAL long_query_time は、すでに繋いでいる接続には反映されません。 グローバル値は新しく繋いだ接続にコピーされるものだからです。いま自分が打っている接続で試すなら、セッション側も同じ値にしてください。

SET SESSION long_query_time = 0.1;

反映を確認します。

SHOW VARIABLES LIKE 'slow_query_log';
SELECT @@GLOBAL.long_query_time, @@SESSION.long_query_time;
+--------------------------+---------------------------+
| @@GLOBAL.long_query_time | @@SESSION.long_query_time |
+--------------------------+---------------------------+
|                 0.100000 |                  0.100000 |
+--------------------------+---------------------------+

2つとも 0.100000 になっていれば成功です。

[スクリーンショット: 01_slow_query_log_on.png] SHOW VARIABLES LIKE 'slow_query_log'; と SELECT @@GLOBAL.long_query_time, @@SESSION.long_query_time; を実行して、ON と 0.100000 が返っている画面を撮ってください。

わざと遅いクエリを流す

設定しただけで満足しないでください。 「有効にしたつもりで、実は書かれていない」が一番多い失敗です。必ず、出ることを確かめます。

memo の中身を部分一致で探します。索引では直らない、確実に遅いクエリです。

SELECT COUNT(*) FROM d_qt_orders WHERE memo LIKE '%zzz%';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.51 sec)

0.51秒。 long_query_time の 0.1 を超えたので、これは記録されているはずです。

もう1本、回数で効くほうも作ります。同じ形のクエリを100回流します。これがのちほど N+1 の見本になります。

DROP PROCEDURE IF EXISTS d_qt_n1;
DELIMITER //
CREATE PROCEDURE d_qt_n1()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 100 DO
    SELECT COUNT(*) INTO @c FROM d_qt_orders WHERE user_id = i AND status = 1;
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;
CALL d_qt_n1();

DELIMITER // は「区切り文字を一時的に // に変える」という指示です。手続きの中に ; が入っているので、そのままだと途中で切られてしまいます。DELIMITER ; で元に戻します。 これは mysql クライアント側の機能で、SQL ではありません。 ここでは「同じ形のクエリを大量に投げる」を再現するためだけに使っています。手続きそのものの話は別の回です。

1本あたりは速いのに、100回で合計はそこそこになります。 これが冒頭の「未読バッジ」と同じ形です。

ログに出ているか確認する

別のターミナルを開くか、いったん MySQL を抜けて VM のシェルで見ます。

bash
$ sudo tail -n 30 /var/log/mysql/ubuntu-slow.log

ファイル名は環境によって違います。 さきほど SHOW VARIABLES LIKE 'slow_query_log%' で出た slow_query_log_file の値をそのまま使ってください。 ホスト名が入るので、ubuntu-slow.log とは限りません。 sudo が要ります。 このファイルは MySQL の実行ユーザーのものなので、普通のユーザーでは読めません。

# Time: 2026-09-20T02:11:44.019283Z
# User@Host: root[root] @ localhost []  Id:    23
# Query_time: 0.512883  Lock_time: 0.000004 Rows_sent: 1  Rows_examined: 300000
SET timestamp=1789870304;
SELECT COUNT(*) FROM d_qt_orders WHERE memo LIKE '%zzz%';

読むところは3つです。

項目意味
Query_timeかかった秒数。これが long_query_time を超えたから記録された
Rows_examined読んだ行数。 30万行読んで1行返している(Rows_sent: 1)。この差が大きいほど無駄をしている
Lock_timeロック待ちの時間。ここが長いなら、遅いのはクエリではなく待ちです

Rows_examined と Rows_sent の比は、EXPLAIN を打つ前に分かる「無駄の量」です。 30万対1なら、ほぼ確実に直せます。 300対300なら、そのクエリは頑張っています。

もし1行も出ていないなら、次のどれかです。

  1. SET GLOBAL ではなく SET SESSION にした
  2. long_query_time を変えたあと接続し直していない(グローバル値は既存接続に効かない)
  3. 見ているファイルが違う(slow_query_log_file を確認)
  4. クエリが実は long_query_time より速かった(0.1 を 0.01 に下げて試す)

mysqldumpslow で集計する

ログを tail で読むのは、数本のうちはできます。数千行になったら無理です。 集計する道具が最初から入っています。

bash
$ sudo mysqldumpslow -s t -t 5 /var/log/mysql/ubuntu-slow.log
オプション意味
-s t合計時間の順に並べる(at なら平均時間、c なら回数)
-t 5上位5件だけ出す
Count: 100  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (100)
  SELECT COUNT(*) INTO @c FROM d_qt_orders WHERE user_id = N AND status = N

Count: 1  Time=0.51s (0s)  Lock=0.00s (0s)  Rows=1.0 (1)
  SELECT COUNT(*) FROM d_qt_orders WHERE memo LIKE 'S'

注目してほしいのは、WHERE user_id = N の N です。

mysqldumpslow は、値を N(数値)と S(文字列)に置き換えて、同じ形のクエリをまとめます。 だから「同じクエリが100回」と1行で出てきます。これが digest の考え方です。

ただし mysqldumpslow には弱点があります。

  • long_query_time を超えたものしかログに無い。 1回8ミリ秒のクエリが24万回飛んでいても、ログには1行も出ません
  • ログを消すと過去が消える
  • 合計時間の精度が粗い(上の例でも Time=0.00s としか出ていません)

冒頭の「未読バッジ」は、まさにこれで見つかりませんでした。 そこで次の道具に移ります。

performance_schema で「合計時間が長い順」に出す

MySQL は、実行したSQLの統計を自分で貯めています。 ログとは別に、メモリの中に、形ごとにまとめて持っています。

SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest;

行が返れば使えます。 performance_schema は MySQL 8.0 では既定で有効です。

本題のクエリです。この1本がこの記事で一番使うものです。

SELECT
  SCHEMA_NAME,
  COUNT_STAR                                   AS 回数,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 3)     AS 合計秒,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2)        AS 平均ms,
  SUM_ROWS_EXAMINED                            AS 読んだ行,
  SUM_ROWS_SENT                                AS 返した行,
  LEFT(DIGEST_TEXT, 70)                        AS SQL文
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'appdb'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

単位に注意してください。 SUM_TIMER_WAIT はピコ秒(1兆分の1秒)です。1兆で割ると秒、10億で割るとミリ秒になります。ここを間違えると「合計3京秒」みたいな数字を見て混乱します。

+-------------+------+--------+--------+----------+----------+------------------------------------------+
| SCHEMA_NAME | 回数 | 合計秒 | 平均ms | 読んだ行 | 返した行 | SQL文                                    |
+-------------+------+--------+--------+----------+----------+------------------------------------------+
| appdb       |  100 |  0.930 |   9.30 |    30000 |      100 | SELECT COUNT (*) INTO @ FROM `d_qt_orde  |
| appdb       |    1 |  0.512 | 512.00 |   300000 |        1 | SELECT COUNT (*) FROM `d_qt_orders` WHE  |
| appdb       |    1 |  0.091 |  91.00 |   300000 |        1 | SELECT COUNT (*) FROM `d_qt_orders`      |
+-------------+------+--------+--------+----------+----------+------------------------------------------+

ここが、この記事で一番言いたいところです。

平均回数合計
memo LIKE '%zzz%'512ms(遅い)1回0.51秒
WHERE user_id = ?9.3ms(速い)100回0.93秒

平均で並べたら、直すのは LIKE のほうです。 合計で並べたら、直すのは「速いほう」です。

そして、体感を作っているのは合計のほうです。 1リクエストで100回呼ばれるなら、利用者は 0.93秒 待っています。 LIKE のほうは、管理画面で誰かが1日1回押すだけかもしれません。

だから ORDER BY SUM_TIMER_WAIT DESC なのです。 AVG_TIMER_WAIT ではありません。

この表の便利なところと、注意点も書いておきます。

良い点速いクエリも全部数えている。 long_query_time に引っかからないものが見える
良い点値が違うだけの同じクエリを、勝手にまとめてくれる(それが digest)
注意メモリの中の統計なので、MySQL を再起動すると消える
注意貯める形の種類には上限がある(既定 5000)。溢れると NULL の行にまとめられる
注意「起動してから今まで」の累計。 「直近1時間」を見たいなら、測り始める前に一度リセットする

リセットはこれです。

TRUNCATE performance_schema.events_statements_summary_by_digest;

これは統計を消すだけで、データは1行も消えません。 「これから5分間の負荷だけを見たい」というときは、リセット → 5分待つ → さっきの SELECT という手順にします。この記事でも、次の測定に入る前に一度打ちます。

N+1 は、ここで気づく

さきほどの表の1行目です。

| appdb | 100 | 0.930 | 9.30 | 30000 | 100 | SELECT COUNT (*) INTO @ FROM `d_qt_orde |

回数 = 100。 これが N+1 の見た目です。

N+1問題とは、一覧を1回引いたあと、その1件ごとに追加で問い合わせてしまうことです。100件の一覧なら 1 + 100 = 101回。本線のパフォーマンス改善の回で、アプリ側のログから見つけました。

アプリのコードを読まなくても、SQL側から見つけられます。 目印は2つです。

  1. COUNT_STAR が、画面の表示件数と同じか、その倍数になっている(20件表示なら20、40、60…)
  2. AVG_TIMER_WAIT が小さいのに、SUM_TIMER_WAIT が上位にいる

この2つが揃ったら、ほぼN+1です。 直し方(JOIN にまとめる、IN でまとめて引く)は本線の回でやったので、ここでは繰り返しません。ここで言いたいのは「アプリを読まずに、データベース側から気づけた」ということだけです。

逆も言えます。 アプリのフレームワークを疑う前に、この表を1回出せば、N+1があるかどうかは30秒で分かります。

いま走っているクエリを見る

ここまでは終わったクエリの話でした。「いま重い」ときに見るものは別です。

わざと重いクエリを走らせる

ターミナルを2つ使います。 VM に SSH で2本繋ぐか、tmux でも構いません。

1つ目のターミナルで MySQL に入り、これを打ちます。わざと何十秒もかかるクエリです。

SELECT COUNT(*)
FROM d_qt_orders a
JOIN d_qt_orders b ON a.user_id = b.user_id AND a.amount = b.amount;

返ってきません。 それでいいです。そのまま放置して、2つ目のターミナルに移ります。

なぜ遅いか:user_id は1000種類しかないので、同じ user_id の組み合わせが1人あたり300×300通りできます。索引も張っていません。 意図的に、現実の事故(絞り込みが甘い JOIN)を小さく再現したものです。

SHOW PROCESSLIST

2つ目のターミナルで MySQL に入ります。

bash
$ sudo mysql
SHOW PROCESSLIST;
+----+-----------------+-----------+-------+---------+------+------------------------+------------------+
| Id | User            | Host      | db    | Command | Time | State                  | Info             |
+----+-----------------+-----------+-------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL  | Daemon  | 4210 | Waiting on empty queue | NULL             |
| 23 | root            | localhost | appdb | Query   |   17 | executing              | SELECT COUNT(*)  |
| 27 | root            | localhost | appdb | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------+-------+---------+------+------------------------+------------------+

見る列は決まっています。

列読み方
Id接続番号。 KILL に使う番号
CommandQuery なら実行中、Sleep なら「繋がっているだけで何もしていない」
Time秒数。 Query で Time が大きいものがいま重いクエリ。Sleep の Time は「何秒暇か」なので気にしなくていい
Stateいま何をしている段階か(後述)
Info実行中のSQL。ただし途中で切れる

Info が切れるのが不便なので、普通はこちらを打ちます。

SHOW FULL PROCESSLIST;

FULL を付けると Info が全文出ます。 長いSQLだと画面が崩れるので、行を縦に出す \G と組み合わせると読みやすくなります。

SHOW FULL PROCESSLIST\G

\G は、; の代わりに使う終端記号です。1行1列の縦並びで表示されます。列が多い結果を読むときの定番です。

表として絞り込む

SHOW PROCESSLIST は並べ替えも絞り込みもできません。 接続が200本あると使い物になりません。同じ内容が表としても見えます。

SELECT ID, USER, DB, COMMAND, TIME, STATE, LEFT(INFO, 60) AS INFO
FROM information_schema.processlist
WHERE COMMAND <> 'Sleep'
  AND ID <> CONNECTION_ID()
ORDER BY TIME DESC;
+----+------+-------+---------+------+--------------+-------------------------------------+
| ID | USER | DB    | COMMAND | TIME | STATE        | INFO                                |
+----+------+-------+---------+------+--------------+-------------------------------------+
| 23 | root | appdb | Query   |   34 | executing    | SELECT COUNT(*) FROM d_qt_orders a  |
+----+------+-------+---------+------+--------------+-------------------------------------+
  • COMMAND <> 'Sleep' で、繋いでいるだけの接続を消します(普通、これが大半です)
  • ID <> CONNECTION_ID() で、自分自身を消します(CONNECTION_ID() は「いまの自分の接続番号」)
  • ORDER BY TIME DESC で、長く走っているものが一番上に来ます

これが「いま重い」を見るときの定型です。 SHOW PROCESSLIST と違って、WHERE も ORDER BY も書けます。

MySQL 8.0 では performance_schema.processlist も同じ内容を返します。 そちらは他の接続を止めずに読めるぶん、接続数が多いサーバーでは安全です。列名は同じなので、information_schema の部分を差し替えるだけで動きます。

State は何を言っているか

State は「いまどの段階か」です。 全部覚える必要はありません。よく見るものだけ。

State意味
executing(8.0.17 より前は Sending data)実行中。行を読んで、条件に合うか調べている最中もここ。 旧名は「送信中」と読めますが、実際は読み込みと処理の大半がここに入ります。 長いクエリはほぼここで止まって見えます
Sorting result並べ替え中。ORDER BY が索引で解決できていない
Creating sort index並べ替えのために作業用の構造を作っている
Copying to tmp table一時テーブルを作っている。 GROUP BY や DISTINCT で出やすい
Waiting for table metadata lock誰かが ALTER TABLE などを打って、その表を触れない状態。 これが並んでいたら、遅いのはクエリではなくロックです
statistics実行計画を立てている最中。ここで止まるのは、JOIN する表が多すぎるとき

Waiting for table metadata lock が並んでいるときに、クエリを書き直しても永遠に直りません。 見るべきは「誰がその表をロックしているか」です。これが、プロセスリストを見る一番の価値です。

KILL の使いどころ

Id: 23 のクエリを止めます。 2種類あります。ここを間違える人が非常に多いです。

KILL QUERY 23;
KILL 23;
書き方何が起きるか
KILL QUERY 23走っているクエリだけ止める。 接続は生きたまま。アプリからは「1本のSQLがエラーになった」だけ
KILL 23(= KILL CONNECTION 23)接続ごと切る。 アプリ側は接続が落ちたことになる。コネクションプールが混乱することがある

まず KILL QUERY を使ってください。 接続を切る理由は、普通はありません。

1つ目のターミナルを見ると、こうなっています。

ERROR 1317 (70100): Query execution was interrupted

止まりました。 データは何も壊れていません。

KILL を打つ前に、必ず考えることが2つあります。

  1. それは SELECT か、書き込みか。 SELECT なら止めても副作用はありません。UPDATE や DELETE を止めると、そこまでの変更を巻き戻す作業(ロールバック)が始まります。 これは元の処理より時間がかかることがあります。 「止めたのに、さらに10分重かった」はここです
  2. なぜ長いのかを、止める前に記録したか。 Info と Time と State をコピーしてから止めてください。止めた瞬間、証拠は消えます

KILL は治療ではなく、応急処置です。 止めたあと、必ず digest の表に戻って「なぜそれが走ったのか」を見ます。 止めて終わりにすると、明日も同じものが走ります。

[スクリーンショット: 02_processlist_kill.png] information_schema.processlist で Time の大きいクエリが見えている画面と、KILL QUERY <番号> を打った直後の画面を撮ってください。

推定と実測がずれるとき

見つけたクエリを直す前に、EXPLAIN の読み方の続きを1つだけ足します。

読み方そのものは前回でやりました。 ここでやるのは、「EXPLAIN の言うことが実際と違う」ときの対処だけです。

EXPLAIN ANALYZE

EXPLAIN は実行しません。 「こうするつもりだ」を見せるだけです。EXPLAIN ANALYZE は実際に実行して、推定と実測を並べます。

EXPLAIN ANALYZE
SELECT COUNT(*) FROM d_qt_orders WHERE status = 1 AND amount < 100;
-> Aggregate: count(0)  (actual time=112..112 rows=1 loops=1)
    -> Filter: ((d_qt_orders.status = 1) and (d_qt_orders.amount < 100))
       (cost=30541 rows=10011) (actual time=0.06..111 rows=3000 loops=1)
        -> Table scan on d_qt_orders  (cost=30541 rows=298433)
           (actual time=0.05..75.2 rows=300000 loops=1)

読むのは2箇所だけです。

意味
rows=10011(括弧の前)推定。「たぶん1万行残るだろう」
rows=3000(actual の中)実測。「実際は3000行だった」

この2つがどれくらいずれているかを見ます。

ずれ方判断
数倍以内気にしない。推定は概算です
10倍以上統計情報が古い可能性。 実行計画の選択自体が間違っているかもしれない
100倍以上ほぼ確実に統計が原因。 索引を張っても使われないことがある

なぜこれが効くか。 オプティマイザは推定値を見て「索引を使うか、全部読むか」を決めます。推定が10倍外れていれば、判断も外れます。 「索引があるのに使われない」の原因が、書き方ではなく統計情報だったというのは、実際によくあります。

ANALYZE TABLE — 統計を取り直す

ANALYZE TABLE d_qt_orders;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| appdb.d_qt_orders   | analyze | status   | OK       |
+---------------------+---------+----------+----------+

これは行を1つも書き換えません。 「この列にはだいたい何種類の値があるか」というメモを取り直すだけです。数秒で終わります。

大量に INSERT / DELETE したあとは、統計が実態とずれます。 MySQL は自動でも取り直しますが、タイミングは保証されません。 「昨日データを10倍にした。今日から急に遅い」なら、まずこれを打ちます。

ただし、本番で無邪気に打たないでください。 ANALYZE TABLE はその表を使っている実行中のクエリを一瞬待たせることがあります(実行計画のキャッシュが無効になるため)。負荷の低い時間に打つのが作法です。

ヒストグラム — 統計では足りないとき

ANALYZE TABLE が持つのは「何種類あるか」だけです。「どう偏っているか」は持ちません。

例えば status は0と1の2種類です。統計上は「2種類だから、半分ずつだろう」と推定されます。 でも実際のデータが 99% が 0、1% が 1 だったら、推定は大外れです。

その偏りを持たせるのがヒストグラムです。

ANALYZE TABLE d_qt_orders UPDATE HISTOGRAM ON status, amount WITH 32 BUCKETS;
+-------------------+-----------+----------+-------------------------------------------------------+
| Table             | Op        | Msg_type | Msg_text                                              |
+-------------------+-----------+----------+-------------------------------------------------------+
| appdb.d_qt_orders | histogram | status   | Histogram statistics created for column 'status'.     |
| appdb.d_qt_orders | histogram | amount   | Histogram statistics created for column 'amount'.     |
+-------------------+-----------+----------+-------------------------------------------------------+

中身も見られます。

SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') AS buckets
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'appdb' AND TABLE_NAME = 'd_qt_orders';

もう一度 EXPLAIN ANALYZE を打つと、推定値が実測に近づきます。

使いどころははっきりしています。

使う索引を張るほどではない列で、値が大きく偏っているとき
使わない索引がある列。 索引があるなら、そちらのほうが正確な情報を持っています

消すときはこれです。

ANALYZE TABLE d_qt_orders DROP HISTOGRAM ON status, amount;

書き換えのパターンを、実測付きで

ここからが実作業です。 索引は張りません。同じデータのまま、書き方だけを変えて時間を測ります。

測り方のルールを先に決めます。

1. 同じクエリを3回打って、2回目と3回目を見る。 2. before と after を、必ず続けて打つ。 3. 1回目は捨てる(理由は後述のバッファプールの節)。

digest の統計も、ここで一度リセットしておきます。

TRUNCATE performance_schema.events_statements_summary_by_digest;

① SELECT * をやめて、必要な列だけにする

「とりあえず SELECT *」は、動く限り誰も指摘しません。 代金は見えないところで払っています。

before。

SELECT * FROM d_qt_orders WHERE user_id BETWEEN 1 AND 50;
15000 rows in set (0.42 sec)

after。使う列だけにします。

SELECT id, user_id, amount FROM d_qt_orders WHERE user_id BETWEEN 1 AND 50;
15000 rows in set (0.11 sec)

0.42秒 → 0.11秒。 索引は1本も足していません。列を減らしただけです。

何が減ったのか。

  1. memo(200文字)を1万5千行ぶん、読んで、転送するのをやめた
  2. クライアントに送るバイト数が減った

さらに、索引がある場合はもっと効きます。 ここで初めて索引を1本だけ張ります。

CREATE INDEX ix_qt_user_amount ON d_qt_orders (user_id, amount);
EXPLAIN SELECT id, user_id, amount FROM d_qt_orders WHERE user_id BETWEEN 1 AND 50\G
           type: range
possible_keys: ix_qt_user_amount
          key: ix_qt_user_amount
        Extra: Using where; Using index

Using index が出ました。 必要な列が全部その索引に入っているので、表の本体を一度も読んでいません(カバリングインデックス)。前回やったやつです。

同じ索引があっても、SELECT * だとこうなります。

EXPLAIN SELECT * FROM d_qt_orders WHERE user_id BETWEEN 1 AND 50\G
           type: ALL
possible_keys: ix_qt_user_amount
          key: NULL
        Extra: Using where

key: NULL。索引があるのに使っていません。 索引を使うと「索引を読む → 該当行を表から読みに戻る」を1万5千回やることになります。memo を取るには表に戻るしかないからです。 それなら全部順番に読んだほうが速いと判断されました。

つまり、SELECT * は索引を無効化することがあります。 「索引を張ったのに使われない」の原因が SELECT * だった、というのはこれです。

SELECT * を許してよい場面もあります。 1行だけ取るとき(WHERE id = ?)、手で調べているとき、INSERT ... SELECT で全列を移すとき。「一覧を引くクエリ」でだけ、やめてください。

② 大きい OFFSET のページングを、キーセットに書き換える

これは、書き換えの効果が一番はっきり出ます。

before。1万ページ目を開くつもりで。

SELECT id, user_id, created_at
FROM d_qt_orders
ORDER BY created_at, id
LIMIT 20 OFFSET 200000;
20 rows in set (0.29 sec)

20行しか返っていないのに 0.29秒。 理由は単純です。

OFFSET 200000 は「20万行を読んで、捨てる」という意味です。 飛ばしているのではなく、読んでいます。 ページが深くなるほど遅くなります。1ページ目は速いのに、100ページ目で苦情が来るのはこれです。

after。「前のページの最後の値」を覚えておいて、その続きから取ります。

まず、200000件目の値を1回だけ調べます(実際のアプリでは、前のページの最終行が持っています)。

SELECT created_at, id FROM d_qt_orders ORDER BY created_at, id LIMIT 1 OFFSET 199999;
+---------------------+--------+
| created_at          | id     |
+---------------------+--------+
| 2024-05-18 21:20:00 | 200000 |
+---------------------+--------+

その値より後ろを取ります。

CREATE INDEX ix_qt_created ON d_qt_orders (created_at, id);
SELECT id, user_id, created_at
FROM d_qt_orders
WHERE (created_at, id) > ('2024-05-18 21:20:00', 200000)
ORDER BY created_at, id
LIMIT 20;
20 rows in set (0.00 sec)

0.29秒 → 0.00秒。 表示が 0.00 なのは、ミリ秒未満だからです。

なぜ速いか。 索引の中を「その値の位置」まで一気に飛んで、そこから20行読んで終わりだからです。20万行を読んで捨てる作業が消えました。

EXPLAIN でも確認できます。

EXPLAIN SELECT id, user_id, created_at FROM d_qt_orders
WHERE (created_at, id) > ('2024-05-18 21:20:00', 200000)
ORDER BY created_at, id LIMIT 20\G
   type: range
    key: ix_qt_created
   rows: 100017
  Extra: Using where; Using index

rows が10万と出ていても構いません。 これは「範囲としてはそれだけ該当しうる」という推定で、LIMIT 20 で20行取ったら止まります。 実測は EXPLAIN ANALYZE で見てください。

キーセットページングには制約があります。正直に書きます。

できることできないこと
「次へ」「前へ」「17ページ目へ飛ぶ」
無限スクロール総ページ数の表示(それは COUNT の話。次の節)

「何ページ目へ飛ぶ」が本当に必要かどうかは、作る前に決めてください。 深いページ番号を実際に押す利用者は、ほとんどいません。

(created_at, id) > (...) という書き方は「行値比較」といいます。 「created_at が大きい、または created_at が同じで id が大きい」を1行で書いたものです。同じ時刻の行があっても、重複も抜けも起きません。 MySQL 8.0 はこの形をちゃんと範囲走査に使えます。

③ COUNT(*) を毎回やらない

「全◯件」の表示のために、ページを開くたびに全件数えていませんか。

before。

SELECT COUNT(*) FROM d_qt_orders WHERE status = 1;
+----------+
| COUNT(*) |
+----------+
|   150000 |
+----------+
1 row in set (0.13 sec)

0.13秒。 単体では速いです。ただし、これがページを開くたびに毎回走ります。

選択肢は3つあります。

(a) 概算で済ませる。

SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'appdb' AND TABLE_NAME = 'd_qt_orders';
+------------+
| TABLE_ROWS |
+------------+
|     298433 |
+------------+
1 row in set (0.00 sec)

30万ではなく 298433 と出ています。これは間違いではありません。 InnoDB の TABLE_ROWS は推定値です。「全 298,433 件」と表示してはいけません。 使えるのは「約30万件」「1000件以上」のような曖昧な表示か、内部の判断だけです。

(b) 上限を決めて、そこまでしか数えない。

SELECT COUNT(*) FROM (SELECT 1 FROM d_qt_orders WHERE status = 1 LIMIT 1001) t;
+----------+
| COUNT(*) |
+----------+
|     1001 |
+----------+
1 row in set (0.00 sec)

1001件見つかった時点で止まります。 画面には「1000件以上」と出します。検索結果の件数表示は、これで足りることがほとんどです。

(c) 集計表を持つ。

CREATE TABLE d_qt_counts (
  name  VARCHAR(40) NOT NULL,
  cnt   BIGINT      NOT NULL,
  PRIMARY KEY (name)
);
INSERT INTO d_qt_counts (name, cnt)
SELECT 'orders_status1', COUNT(*) FROM d_qt_orders WHERE status = 1;
SELECT cnt FROM d_qt_counts WHERE name = 'orders_status1';
+--------+
| cnt    |
+--------+
| 150000 |
+--------+
1 row in set (0.00 sec)

0.13秒 → 0.00秒。 主キー1行を読むだけです。

ただし、これは「ズレる仕組みを自分で作った」ということです。 誰かが INSERT したら、この数字は古くなります。維持する方法を決めない限り、やってはいけません。

維持の仕方向いている場面
1分に1回、バッチで数え直す多少ズレてよい表示。一番簡単で、一番壊れにくい
書き込みのたびに +1 する正確さが要るとき。書き込みが重くなり、失敗時のズレを自分で直す必要がある

迷ったら上です。 「全件数が1分古い」で困る画面は、そんなにありません。

④ IN の中身が多すぎる

アプリが「IDの配列」を持っていて、それをそのまま IN に並べる——よくある形です。1万件並べたらどうなるか、実際に見ます。

1万個の IN を組み立てます。

SET SESSION group_concat_max_len = 1000000;
SET @ids = (SELECT GROUP_CONCAT(id) FROM d_qt_orders WHERE id <= 10000);
SET @sql = CONCAT('SELECT COUNT(*), SUM(amount) FROM d_qt_orders WHERE id IN (', @ids, ')');
SELECT LENGTH(@sql) AS SQL文の長さ;
+----------------+
| SQL文の長さ    |
+----------------+
|          48953 |
+----------------+

GROUP_CONCAT は既定で1024文字までしか繋げません。 それを超えると警告も出さずに途中で切られます。 group_concat_max_len を上げているのはそのためです。ここではわざと長いSQLを作るために使っています。

約5万文字のSQLです。 実行します。

PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
+----------+-------------+
| COUNT(*) | SUM(amount) |
+----------+-------------+
|    10000 |    24995000 |
+----------+-------------+
1 row in set (0.19 sec)

after。一時テーブルに入れて JOIN します。

CREATE TEMPORARY TABLE d_qt_wanted (id INT NOT NULL, PRIMARY KEY (id));
INSERT INTO d_qt_wanted (id) SELECT id FROM d_qt_orders WHERE id <= 10000;
SELECT COUNT(*), SUM(o.amount)
FROM d_qt_orders o
JOIN d_qt_wanted w ON o.id = w.id;
+----------+---------------+
| COUNT(*) | SUM(o.amount) |
+----------+---------------+
|    10000 |      24995000 |
+----------+---------------+
1 row in set (0.04 sec)

0.19秒 → 0.04秒。

何が変わったか。

IN に1万個一時テーブル
SQLの長さ6万文字数十文字
パース(解析)1万個の値を毎回読み取るほぼゼロ
統計の見え方ダイジェストが巨大になる、または個数ごとに別物として数えられることがある常に同じ形として数えられる
通信量毎回6万文字を送る1回入れて、あとは短いSQL

統計に出てこなくなるのが、地味に一番つらいところです。 IN の個数がリクエストごとに違うと、digest の表に少しずつ違う行として散らばることがあります。合計時間で並べても、上位に上がってきません。 実際に重いのに、探しても見つからない。

CREATE TEMPORARY TABLE の良いところは、切断すると消えることです。 後片づけが要りません。ただし、名前が既存の表と同じだと、その接続の中では一時テーブルが優先されて元の表が見えなくなります。 ここで d_qt_ を付けているのは、そのためでもあります。

目安:IN が数百を超えたら、一時テーブルか JOIN を検討します。 数十なら気にしなくて構いません。

4つを1枚の表にする

同じデータ、同じマシン、索引を2本だけ追加した状態での実測です。

書き換えbeforeafter何倍何をやめたか
① SELECT * → 必要な列だけ0.42 sec0.11 sec約4倍使わない列を読んで送るのをやめた(索引が使えるようになった)
② OFFSET 200000 → キーセット0.29 sec0.00 sec桁違い20万行を読んで捨てるのをやめた
③ 毎回の COUNT(*) → 集計表0.13 sec0.00 sec桁違い15万行を毎回数えるのをやめた
④ IN 1万個 → 一時テーブル0.19 sec0.04 sec約5倍6万文字のSQLを毎回解析するのをやめた

4つとも、共通していることが1つあります。

「やらなくていい仕事をやめた」だけです。 速くする工夫は1つもしていません。

チューニングの大半はこれです。 賢いアルゴリズムを探す前に、読まなくていい行を読んでいないか、送らなくていい列を送っていないかを見てください。

数字はあなたのVMでは違って構いません。 見るのは桁です。倍率が同じ方向に出ていれば、同じことが起きています。

バッファプールとキャッシュ — 測り方の落とし穴

ここを飛ばすと、これまでの測定が全部嘘になります。

さっきのクエリを、もう一度、そのまま打ってみてください。

SELECT * FROM d_qt_orders WHERE user_id BETWEEN 1 AND 50;
15000 rows in set (0.19 sec)

さっきは 0.42 秒だったのに、0.19 秒になりました。 何も変えていないのにです。

理由はこれです。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

134217728 バイト = 128MB。 これがバッファプール、InnoDB がデータをメモリに載せておく場所です。

1回目はディスクから読みました。2回目はメモリにありました。 それだけです。

「索引を張ったら2倍速くなりました」の半分くらいは、これです。 1回目(索引なし・ディスク)と、2回目(索引あり・メモリ)を比べているからです。索引の効果ではありません。

だから、測り方にルールが要ります。

ルール理由
同じクエリを3回打って、2回目以降を見る1回目は「ディスクから読んだ回」。条件が違う
before と after を、時間を空けずに続けて打つ途中で他の処理が走ると、メモリの中身が入れ替わる
EXPLAIN の rows も一緒に見る時間はブレるが、読んだ行数はブレない。 行数が減っていれば、それは本物
桁で判断する。1割の差は無視するVMの上では、その程度は普通にブレる

いま何が乗っているかも見られます。

SELECT COUNT(*) AS ページ数, ROUND(SUM(DATA_SIZE)/1024/1024, 1) AS MB
FROM information_schema.INNODB_BUFFER_PAGE
WHERE TABLE_NAME LIKE '%d_qt_orders%';
+-------------+------+
| ページ数    | MB   |
+-------------+------+
|        4210 | 62.3 |
+-------------+------+

この SELECT は、バッファプールが大きいサーバーだと重いです。 練習用のVM(128MB)では一瞬ですが、64GB のサーバーで打つと数十秒固まります。 本番では気軽に打たないでください。

innodb_buffer_pool_size を大きくすれば速くなるか。 一般には、はい。「よく使うデータが全部メモリに乗る」大きさが理想です。ただし、

  • この記事では変更しません。 VMの搭載メモリを超える値を設定すると、MySQL が起動しなくなります
  • メモリの余りを見てから決める設定で、SQLの話ではありません
  • そもそも、読まなくていい行を読んでいるなら、メモリを増やしても無駄が速くなるだけです

MySQL 8.0 には、クエリ結果そのもののキャッシュはありません。 昔あった「クエリキャッシュ」は 8.0 で削除されました。 だから、同じSQLの2回目が速いのは、結果を覚えているからではなく、データがメモリにあるからです。 ここを混同すると、キャッシュの話が全部ずれます。

やってはいけないこと

全部、自分でやった順に書きます。

1. 本番でいきなり ALTER TABLE を打つ

これが最悪です。 索引を1本足すだけのつもりでも、

  • メタデータロックが要ります。 その表を使っている全部のクエリが待ちます
  • 数百万行の表なら数分から数十分かかります
  • その間、SHOW PROCESSLIST は Waiting for table metadata lock で埋まります
  • 途中で KILL すると、巻き戻しにさらに時間がかかります

先に、同じ行数のコピーで時間を測ってください。 そして負荷の低い時間に、戻し方(DROP INDEX)を手元に用意してから打ちます。

2. ヒントで押し切る

FORCE INDEX や USE INDEX を書けば、その場では直ります。 そして、データが増えたときに直せなくなります。

オプティマイザは、データの分布が変わったら判断を変えます。 ヒントはそれを禁止します。「去年は正しかった判断」を固定したまま、1年後に誰も理由を知らない状態で残ります。

先に EXPLAIN ANALYZE で推定と実測を比べて、ANALYZE TABLE を打ってください。 たいていそこで直ります。

3. 測らずに直す

この記事の全部が、これへの反論です。

「たぶんこれが遅い」で30分使った話を冒頭に書きました。digest の SELECT を1本打てば、30秒で分かりました。

4. 平均時間で並べる

もう一度書きます。 直すべきは合計時間が長いものです。平均で並べると、1日1回しか走らない管理画面のクエリを、一生懸命直すことになります。

5. long_query_time = 0 を本番で使う

全クエリがログに書かれます。 ディスクが埋まり、その書き込み自体が負荷になります。「調べるために壊す」の典型です。

6. KILL を最初に打つ

止める前に、Info・Time・State を控えてください。 止めた瞬間、なぜ遅かったのかの証拠は消えます。 そして明日また走ります。

つまずいたところ

遠回りした順に書きます。

1. SET SESSION long_query_time にして、ログが空だった

一番最初にやった間違いです。 自分の接続でだけ短くして、アプリのクエリは1行も記録されませんでした。 30分ほど「MySQL のスロークエリログは壊れているのでは」と思っていました。

slow_query_log はそもそもグローバルにしか無い、と知っていれば1分で済んだ話です。

2. SET GLOBAL したのに、自分の接続で試して出なかった

今度は GLOBAL にしました。それでも自分で打ったクエリが記録されません。

グローバル値は、新しく繋いだ接続にコピーされるもので、すでに繋いでいる自分には反映されていませんでした。 一度 EXIT して入り直したら、あっさり出ました。この記事で SET SESSION も一緒に打っているのは、そのためです。

3. long_query_time = 0 にして、VM のディスクを埋めた

「全部見たほうが確実だろう」と 0 にして、練習用の30万行 INSERT を流しました。 気づいたときにはログが 2.3GB ありました。VM のディスクが満杯になって、MySQL が起動しなくなりました。

ログを消して起動し直しました。 データは無事でしたが、「調べるための設定で本体を止める」を実際にやりました。

4. digest の表を「直近の状況」だと思って読んだ

events_statements_summary_by_digest を出して、「このクエリが合計40秒も使っている」と騒ぎました。

MySQL を起動してから3週間分の累計でした。 3週間で40秒なら、何の問題もありません。

TRUNCATE して測り直すという手順を知らなかったからです。「いつからの数字か」を確認せずに読んだのが原因でした。

5. Sending data を「ネットワークが遅い」と読んだ

SHOW PROCESSLIST の State が Sending data で止まっているのを見て、「回線が細いのか」と、VMのネットワーク設定を調べに行きました。

Sending data は、実際には行を読んで条件を判定している最中です。 名前が完全に誤解を招きます(MySQL 8.0.17 以降はこの表示をやめて、executing にまとめられています)。遅いのは読み込みでした。 半日使いました。

6. SELECT * を直したのに速くならず、memo に相当する列が無かった

この記事のために memo 列を足したのは、最初に書いた練習用テーブルでは差が出なかったからです。

列が小さい表では、SELECT * を直しても体感は変わりません。「効くパターンの条件」を自分が分かっていなかったということです。大きい列があるとき、または索引でカバーできるときにだけ効きます。

7. 「2倍速くなった」と報告して、翌日には元に戻っていた

索引を張って測り、2倍速いと報告しました。 翌朝、同じクエリは元の速さでした。

張る前は初回(ディスク)、張ったあとは2回目(メモリ)を測っていました。 索引の効果はほぼゼロでした。この記事でバッファプールの節を独立させたのは、これが理由です。

8. KILL した UPDATE の巻き戻しで、さらに待った

長時間走っている UPDATE を KILL して、「これで終わった」と思いました。 そこからロールバックが始まり、元の実行時間より長く待ちました。 その間、表は触れないままでした。

止めていいのは、まず SELECT。 書き込みを止めるのは、巻き戻しの時間も込みで判断してからです。

後片づけ

スロークエリログを有効にしたまま終わらないでください。 ディスクを食い続けます。

1. スロークエリログを元に戻す

SET GLOBAL slow_query_log = OFF;
SET GLOBAL long_query_time = 10;
SET SESSION long_query_time = 10;

確認します。

SHOW VARIABLES LIKE 'slow_query_log';
SELECT @@GLOBAL.long_query_time;
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

+--------------------------+
| @@GLOBAL.long_query_time |
+--------------------------+
|                10.000000 |
+--------------------------+

SET GLOBAL は設定ファイルには書かれません。 万一戻し忘れても、MySQL を再起動すれば既定値に戻ります。 この記事で設定ファイルを触らなかったのは、そのためです。

2. 溜まったログファイルを空にする

設定を戻しても、これまでに書かれたログは残っています。 大きさを見ます。

bash
$ sudo ls -lh /var/log/mysql/
-rw-r----- 1 mysql adm  148M Sep 20 03:12 ubuntu-slow.log

148MB あります。 空にします。

bash
$ sudo truncate -s 0 /var/log/mysql/ubuntu-slow.log

rm で消さないでください。 MySQL がそのファイルを開いたままなので、消しても容量は戻らず、以後のログも書かれません。 truncate -s 0 はファイルを残したまま中身だけ空にするので、開いたままでも安全です。 sudo が要ります。 このファイルの持ち主は MySQL です。

戻ったか確認します。

bash
$ sudo ls -lh /var/log/mysql/

3. 練習用の表を消す

MySQL に戻ります。d_qt_ で始まるものだけです。

SHOW TABLES LIKE 'd\_qt\_%';
+---------------------------+
| Tables_in_appdb (d_qt_%)  |
+---------------------------+
| d_qt_counts               |
| d_qt_orders               |
| d_qt_users                |
+---------------------------+

_ は LIKE では「任意の1文字」という意味を持つので、そのまま書くと dXqt のようなものにも当たります。\_ と書いて、文字としての下線を指定しています。

DROP TABLE IF EXISTS d_qt_counts;
DROP TABLE IF EXISTS d_qt_orders;
DROP TABLE IF EXISTS d_qt_users;
DROP PROCEDURE IF EXISTS d_qt_n1;

索引は表と一緒に消えます。 DROP INDEX を個別に打つ必要はありません。d_qt_wanted(一時テーブル)は、接続を切った時点で消えています。

ヒストグラムを作った場合は、表ごと消えるので何もしなくて構いません。 残っている表に作ったなら、これで消します。

-- 練習用の表を残している場合だけ
ANALYZE TABLE d_qt_orders DROP HISTOGRAM ON status, amount;

確認します。

SHOW TABLES;

d_qt_ で始まる表が1つも無ければ成功です。 表示される中身は、あなたがここまでに作ったものによって変わります。d_qt_ が消えていることだけ見てください。

4. 統計をリセットして抜ける

TRUNCATE performance_schema.events_statements_summary_by_digest;
EXIT;

cte_max_recursion_depth と group_concat_max_len は SET SESSION なので、抜けた時点で既定値に戻ります。

まとめ

この記事で理解できたことを、7行にします。

  1. 「どのSQLが遅いか」は推測しない。 道具は2つ。スロークエリログ(ファイルに残る・遅いものだけ)と performance_schema の digest(全部数える・再起動で消える)
  2. long_query_time の既定は10秒。 つまり素の状態では何も記録されない。 設定はSET GLOBAL。本番で 0 にしない
  3. 並べるのは合計時間(SUM_TIMER_WAIT)。平均ではない。 1回512msが1回より、1回9msが100回のほうが体感を殺している
  4. N+1 は digest で見つかる。 回数が表示件数と一致していて、平均が小さいのに合計が上位——この2つが揃ったらそれ
  5. いま重いときは SHOW FULL PROCESSLIST。 見るのは Time と State。Waiting for table metadata lock が並んでいたら遅いのはクエリではない。止めるなら KILL QUERY(接続ごとではなく)
  6. 書き換えの4つは、全部「やらなくていい仕事をやめる」だけ。

SELECT * をやめる/OFFSET をキーセットに/COUNT(*) を毎回やらない/長い IN を一時表に

  1. 2回目が速いのは、索引が効いたからとは限らない。 バッファプールに乗っただけ。 測るときは3回打って2回目以降、そしてrows も一緒に見る(時間はブレるが行数はブレない)

7つとも、1つのことの言い換えです。

チューニングは「速くする工夫」ではなく、「やめる仕事を見つける作業」です。

そして、やめる仕事は数字でしか見つかりません。 冒頭の私は、数字を1つも持たずに30分使いました。 いま同じことを言われたら、最初に打つのは digest の SELECT 1本です。30秒です。

参考リンク


次に読む

この記事は 【深掘り】SQL の 15 / 17 本目です。

記事
← 前の記事インデックスとEXPLAIN — 張ったのに使われない
→ 次の記事GRANTと最小権限 — rootで繋ぐと全部持っていかれる

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Query Optimization
  • スロークエリログと digest で遅いSQLを見つける
  • 書き換えのパターンと測り方

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次