SQL 深掘り– category –
本線の第5段階の続き。JOIN・集計・ウィンドウ関数・実行計画を、データを入れて確かめる。
-
SQL 深掘り
スロークエリの特定と書き換え — 遅いのはどのSQLか
「アプリが遅い」と言われたとき、どのSQLが原因かを当てずっぽうではなく機械で特定する回です。スロークエリログを有効にしてわざと遅いクエリを流し、mysqldumpslow で集計し、performance_schema の digest で「合計時間が長い順」に並べます。1回が遅いクエリより、そこそこ遅いのが何万回のほうが効くこと。SHOW PROCESSLIST でいま走っているものを見て、KILL をどこで使うか。EXPLAIN ANALYZE で推定と実測がずれていたら ANALYZE TABLE とヒストグラム。そして書き換えのパターンを4つ、before/after の実測付きで並べます。SELECT * をやめる、大きい OFFSET をキーセットにする、COUNT(*) を毎回やらない、長すぎる IN を一時表にする。最後に、2回目が速いのは索引が効いたからとは限らないという測り方の注意と、スロークエリログの戻し方まで。 -
SQL 深掘り
トランザクション分離レベル — 同じSELECTが2回目で変わる
端末を1つしか開いていないうちは、トランザクションの意味は一生分かりません。問題は2人が同時に触ったときにだけ出るからです。この記事では端末を2つ並べて、ダーティリード・ノンリピータブルリード・ファントムリードを1つずつ手で再現します。そのうえで SELECT ... FOR UPDATE でもう一方が待つところを見て、innodb_lock_wait_timeout で失敗させ、performance_schema.data_locks で誰が何を押さえているかを覗きます。索引が無いと1行のつもりが全行ロックになることも実演します。最後にデッドロックをわざと起こして ERROR 1213 を出し、SHOW ENGINE INNODB STATUS の LATEST DETECTED DEADLOCK を読み、NOWAIT と SKIP LOCKED でキューを作るところまで。 -
SQL 深掘り
ビューとストアドプロシージャ — 列を消したら壊れた
同じ複雑なSELECTを、管理画面と集計バッチとAPIが別々に持っていました。1か所にまとめる道具がSQLには2つあります。ビューとストアドです。ビューは「名前を付けたSELECT」でデータを持たず、元のテーブルの列を消すと ERROR 1356 で壊れます。単純なビューは UPDATE できますが、集計を含むと ERROR 1288 で弾かれ、WITH CHECK OPTION を付けると条件から外れる更新も止まります。後半では DELIMITER を変える理由から CREATE PROCEDURE の IN / OUT、CREATE FUNCTION の ERROR 1418、トリガーまで手を動かし、最後に「ストアドをこのサイトでは使わない」と決めた理由を正直に書きます。MySQL 8.0 以降が必要です。 -
SQL 深掘り
相関サブクエリとNOT IN — 行の数だけ実行される
サブクエリは「SELECT の中に SELECT を書けるもの」と説明されますが、それだと「なぜ急に遅くなるのか」も「なぜ NOT IN が0行になるのか」も説明できません。この記事では練習用の表を3つ作り、サブクエリを書ける4つの場所と、返る形の3種類を先に整理します。そのうえで相関サブクエリを EXPLAIN にかけ、DEPENDENT SUBQUERY という文字が「外側の行数ぶん実行されます」という意味だと確認し、JOIN に書き換えて実行時間を並べます。さらに NOT IN に NULL が1つ混ざると結果が丸ごと消える件を、NOT EXISTS と LEFT JOIN ... IS NULL の2通りで直し、3つの実行計画を比べます。 -
SQL 深掘り
インデックスとEXPLAIN — 張ったのに使われない
遅いクエリに索引を張ったのに、実行時間が1ミリ秒も変わらない。これは索引の失敗ではなく、「張る」と「使われる」が別のことだからです。この記事では30万行の練習用テーブルを自分で作り、複合索引 (a, b) に対して WHERE a / WHERE a AND b / WHERE b の3つを EXPLAIN して、左端から使うという規則を実測で見ます。そのうえで索引が効かなくなる6つの書き方を並べ、type と rows を1枚の表にします。索引があるのに全表走査のほうが速い場合があること、カバリングインデックスで表を読まなくて済むこと、EXPLAIN ANALYZE で推定と実測を比べること、索引を張ると INSERT が何倍遅くなるかの実測、使われていない索引と重複した索引の見つけ方、統計情報が古いと計画が狂うこと、そして FORCE INDEX を普段使ってはいけない理由まで。 -
SQL 深掘り
SQLの組み込み関数 — CONCATにNULL1つで全部消える
氏名を CONCAT で繋いだら、ニックネームが未入力の行だけ丸ごと空になりました。CONCAT は引数に1つでも NULL があると結果が全部 NULL になるからです。この記事は組み込み関数を「使える一覧」ではなく「いつ使うか」で並べ直します。文字列・数値・NULL・型変換を、前後に空白が入った名前、全角と半角が混ざったカナ、大文字小文字がバラバラのメールアドレスという、実際にありそうな汚いデータの上で動かします。山場は2つ。NULLIF でゼロ除算を消す定石と、WHERE に関数を書くと索引が効かなくなることを EXPLAIN で目に見せて、3通りに直すところです。 -
SQL 深掘り
CTE(WITH)と再帰クエリ — サブクエリ3段は読めない
サブクエリの入れ子が3段になると、書いた本人でも1週間後には読めません。CTE(WITH)は、その入れ子に名前を付けて上に出すだけの道具です。同じ結果を返す2つのSQLを並べて、EXPLAIN で実行計画が変わらないこと(=速さのためではなく読みやすさのための道具であること)を確かめます。そのうえで WITH RECURSIVE の3部構成を、1から10まで数えるという一番簡単な例から組み立て、わざと無限ループさせて cte_max_recursion_depth で止まるところを見ます。最後に組織図を上下にたどり、経路を文字列で作り、循環参照で止まらなくなるところまで踏みます。MySQL 8.0 以降が必要です。
12