MySQL– tag –
-
SQL 深掘り
SELECTの実行順序とNULL — WHEREで別名が使えない
SELECT * FROM t; は書けるのに、なぜその順で返るのかを説明できない。この記事はそこを埋めます。まず練習用の社員テーブルを60行、NULL入りで作ります。そのうえで「SQLは書いた順に実行されない」ことを、WHEREで列の別名を使ってエラーを出すという形で目に見せます。次にNULLを掘ります。= NULL が何も返さないこと、NOT IN にNULLが1つ混ざるだけで結果が全部消えること。最後にORDER BYの同値、OFFSETが遅くなる理由とキーセットページネーション、DISTINCTとGROUP BYの違いまで。 -
SQL 深掘り
ウィンドウ関数 — GROUP BYだと名前が消える
「各部署で一番売った人」を出そうとすると、GROUP BY では金額しか出せません。名前が消えるからです。集計は行を潰しますが、ウィンドウ関数は潰しません。同じ SUM を GROUP BY と OVER () の両方で打って行数を比べるところから始めて、ROW_NUMBER と RANK と DENSE_RANK の違いを同点データで3列並べ、LAG で前月比を出し、累計でフレームの既定が RANGE であるという罠を踏み、WHERE でウィンドウ関数が使えない理由まで行きます。MySQL 8.0 以降が必要です。 -
SQL 深掘り
NOT NULL・UNIQUE・CHECK制約 — アプリで弾いた値が入る
入力チェックはアプリに書いてある、だから変な値は入らない。そう思っていた表を SELECT したら、名前が空文字の会員と、数量がマイナスの注文と、同じメールアドレスが2件入っていました。アプリは1つとは限らず、手でSQLを打つ日も来ます。最後に値を止められるのはデータベースだけです。この記事では制約を1つも付けない表にわざとゴミを4種類入れて見せたあと、NOT NULL・UNIQUE・主キー・CHECK を順に足していきます。UNIQUE なのに NULL が何個でも入ること、CHECK に NULL が混ざると真偽が決まらず通ってしまうこと、INSERT IGNORE が他のエラーまで握り潰すこと、制約に名前を付けないとエラーメッセージから原因にたどり着けないこと。MySQL 8.0.16 以降が必要です。 -
SQL 深掘り
DDL(CREATE・ALTER・DROP) — ALTER TABLEは取り消せない
SELECT を間違えても誰も困りませんが、ALTER TABLE は本番を止めます。この記事は「表を作る・変える・消す」だけを1本かけて掘ります。最初にやるのは、DDL が暗黙にコミットされる ―― つまり ROLLBACK が効かない ―― ことの実演です。そのうえで CREATE TABLE が実際に何を作ったかを SHOW CREATE TABLE で確かめ、LIKE と SELECT のコピーの差を見て、山場の ALTER TABLE へ入ります。ALGORITHM が INSTANT なのか COPY なのかを自分で調べる方法、80万行の表で COPY が走ったときに書き込みが何秒待たされるかの実測、INSTANT でも表が止まるメタデータロック、DROP と TRUNCATE と DELETE の違い、RENAME による無停止の入れ替え、一時テーブル、JSON に索引を張る生成列まで。 -
SQL 深掘り
DML(INSERT・UPDATE・DELETE) — WHERE無しのUPDATEは戻せない
WHERE を書き忘れた UPDATE を打って、30人全員の等級が同じ値になりました。ROLLBACK と打っても戻りません。autocommit が有効だったからです。この記事は「書き換える文を打つ前に必ずやる4手」を体に入れるところから始めます。わざと間違えた UPDATE を打って ROLLBACK で助かる体験と、助からない体験の両方をやります。そのうえで ON DUPLICATE KEY UPDATE と REPLACE の違い、LAST_INSERT_ID が複数行では先頭を返すこと、更新対象の表をサブクエリで読めない MySQL の制限、外部キーで DELETE が拒否される場合と連鎖する場合、そして論理削除が必ず連れてくる手間まで掘ります。 -
SQL 深掘り
MySQLのデータ型選び — FLOATで金額は1円ずれる
型を選ぶのは「入るか入らないか」の話ではありません。間違えると、エラーも出さずに値そのものが変わります。この記事では、郵便番号の先頭の0が消えるところから始めて、TINYINTに128を入れてOut of rangeを出し、DOUBLEで0.1+0.2が0.3にならないことをSQLで実演し、DECIMALに直します。CHARとVARCHARで後ろの空白の扱いが違うこと、TEXTには索引が張れないこと、ENUMが数値として比較されること。そして山場は暗黙の型変換で、WHERE 文字列列 = 1 と書くと索引が丸ごと効かなくなるのをEXPLAINで見ます。最後にJSON型の索引の張り方、NOT NULLを既定にする理由、ALTER TABLE で型を変えるときにテーブルがロックされる話まで。 -
SQL 深掘り
リレーショナルモデルと集合演算 — SQLは「表」を扱っていない
16本かけてSQLを打ってきました。その全部が何の上に載っていたのかを、最後に言語化します。リレーショナルモデルが言う「関係」は表ではなく集合で、行にも列にも順序はなく、重複行も存在しません。ところがSQLはその通りではありません。同じ行が3つ入ることも、ORDER BY を書かないと順序が決まらないことも、実際にSQLで確かめます。そのうえで集合演算(UNION / UNION ALL / INTERSECT / EXCEPT)を打ち、MySQL 8.0.31 より古い環境での書き換えも用意します。なぜ表を分けるのか、なぜSQLは「どう取るか」を書かないのか、そして「関係」を捨てたNoSQLが何を手に入れて何を失ったのか。SQL深掘りシリーズの最終回です。 -
SQL 深掘り
GRANTと最小権限 — rootで繋ぐと全部持っていかれる
アプリの .env に書いてあるDBのユーザー名は何ですか。root なら、アプリに1つ穴が開いた瞬間に、全部のデータベースが読まれて消せます。この記事はDB側だけでできる守りに絞ります。いまの権限を SELECT user, host と SHOW GRANTS で棚卸ししたあと、アプリ用・マイグレーション用・読み取り専用の3つを最小権限で作り直し、実際にそのユーザーで繋いで DROP TABLE が拒否されるところまで確かめます。列単位の GRANT SELECT と、行を絞るビュー。'app'@'localhost' と 'app'@'%' の差。bind-address と ss -tlnp で3306が外に出ていないかの確認、SSHトンネル、REQUIRE SSL。パスワードがコマンド履歴に残る話、general_log を本番で使わない理由、バックアップ用ユーザーに必要な最小権限、そして最後に10項目の点検チェックリスト。パスワードはすべてダミーです。 -
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 でキューを作るところまで。