MySQL– tag –
-
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 以降が必要です。 -
データベース
プロセスを止めても消えない場所 — MySQLとSQLの基本
前回のAPIは、サーバーを止めるとデータが消えた。なぜファイルに書くだけでは駄目なのかから始めて、MySQLを入れ、SQLの4つを覚え、最後にNode.jsから繋いで「再起動しても残る」ところまで持っていく。WHEREを忘れたUPDATEで全行が飛ぶ話つき。 -
データベース
ALTER TABLE を手で打たない — ORMとマイグレーション
SQLの文字列がコードじゅうに散らばってきた。ORMは何を解決して、何を解決しないのか。生成されたSQLを実際に表示させて「魔法ではない」ことを確かめ、テーブルの変更をコードとして残し、権限の壁(appuserはCREATE TABLEできない)を専用ユーザーで正面から片づけて、最後に必ず戻します。 -
システム設計
速くする前に必ず測る — パフォーマンス改善とEXPLAIN
「遅い」と言われて最初にやることは、コードを直すことではなく測ることです。フロントは開発者ツールとLighthouse、バックは応答時間のログとEXPLAIN。道具が別だという整理から始めて、20万件のテーブルでわざと遅いクエリを作り、EXPLAINで「全部読んでいる」ことを見せ、索引を張って直します。1件ずつ100回問い合わせるN+1も実際に作って潰します。そして最後に、直さない判断の話をします。 -
データベース
部分一致は索引が効かない — 全文検索とMeilisearch
検索窓を付けた瞬間に書く LIKE '%文字%' が、なぜ20万件で止まるのか。EXPLAIN で「索引が1つも使われていない」ところを実際に見せます。日本語には単語の区切りが無いという本質的な難しさを踏まえ、まず MySQL の FULLTEXT + ngram でどこまで戦えるかを試し、足りないところから Meilisearch を Docker Compose で立てます。タイプミスに強い検索を実演し、最後に「検索用のデータはコピーである」という一番大事な話と、Elasticsearch との使い分け、そして入れないほうがいい場面まで。 -
データベース
MySQLとPostgreSQLの違いは1割 — 移して確かめる
2つ目のデータベースを触ります。目的は「どちらが優れているか」を決めることではありません。1つしか知らないと、それがSQLの仕様なのかMySQLの仕様なのかが区別できないからです。PostgreSQLを入れて(最初の関門は認証です)、同じテーブルに同じSQLを打って9割同じことを確かめ、残り1割を1つずつ手で潰します。最後にKnexの接続先を差し替えて、マイグレーションがどこで落ちるかを見ます。