深掘り– category –
**本線を一周した人向け。** roadmap.sh のコースを1つ選んで、その中を細かく潰していく記録。**順路ではありません。** 必要になったコースだけ読んでください。
-
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 以降が必要です。 -
Linux 深掘り
シェルスクリプトのクォート — 空白入りのファイル名でforが割れる
動くスクリプトは書けるのに、なぜ動くか・なぜ壊れるかを説明できなかった。壊れ方はほぼ決まっている。クォート、変数展開、[ と [[ の違い、while read、set -euo pipefail が効かない場所までを、全部その場で壊して確かめる。最後は shellcheck に自分のスクリプトを見せる。