メインコンテンツまでスキップ

SQL

SQLの普遍的なものについて記載していく。

SQLを手元で試したいとき​

docker run --platform linux/amd64 --name mysql-container -e MYSQL_ROOT_PASSWORD=rootpassword -d mysql:5.7
# 20秒ぐらい時間を空ける
docker exec -it mysql-container mysql -uroot -prootpassword

# DB作成
mysql> CREATE DATABASE my_database;
# DB一覧
mysql> SHOW DATABASES;
# 現在どこのDBにuseしているのか
mysql> SELECT DATABASE();
# テーブル一覧コマンド
mysql> SHOW TABLES;
# 以下で mysql>　のpromptを変更できる。
mysql> prompt Transaction A> 
# テーブル作成コマンド
mysql> create table t1(i1 int not null primary key, v2 varchar(20)) engine = innodb;

# 現在のトランザクションレベルを確認するコマンド
mysql> SELECT @@tx_isolation;
# MySQL 8.0以降では次のコマンドを使用
mysql> SELECT @@transaction_isolation;

# トランザクションを始める
mysql> start transaction;

SQLがオンラインでできる​

SQL Fiddle 使い方説明

DML・DCL・DDL​

SQLは、いくつかのキーワードと、テーブル名や列名などを組み合わせて1つの文とし、操作の内容を記述する
キーワードは最初から意味や使い方が決められている特別な英単語。
SQL文はDBMSに与える命令の種類により、次の3つに分類される

  1. DDL（Data Definition Language：データ定義言語）
  2. DML（Data Manipulation Language：データ操作言語）
  3. DCL（Data Control Language：データ制御言語）

オンラインDDL(Online Data Definition Language)​

オンラインDDL（Online Data Definition Language）とは、データベースのテーブル構造（スキーマ）を変更する際に、テーブルへの読み取りや書き込み操作を継続しながら変更する仕組みのこと。

オンラインDDLができるか確認するコマンド

SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';

Selectの落とし穴​

レコード数が多いテーブルの場合はフルスキャンに気をつける
LIKE などを用いたクエリがあたる。

MySQL​

MySQLで実行中のSQLをキャンセルする

MySQLにおいて、クライアントのコネクションを切断しても、サーバー側で実行中の重いSELECT文が自動的に中断されるわけではない場合があります。この挙動は、とくに大きなデータベース操作において問題となることがあります。そのため、手動でこれらのクエリを停止する方法が必要になります。

MySQLでの実行中のクエリの取り扱い​

  1. SHOW PROCESSLIST コマンド:

    • SHOW PROCESSLIST コマンドを実行すると、現在MySQLサーバー上で実行中のすべてのスレッド（プロセス）のリストを表示します。これには、各クエリの状態（例: Query、Sleep）や実行時間も含まれます。

  2. プロセスのキル:

    • クエリが長時間実行されている場合、そのプロセスIDを使用して、明示的にそのクエリを停止（キル）することが可能。たとえば、プロセスIDが 12 のクエリを停止したい場合は、次のコマンドを使用します：
    KILL 12;
    • これは該当するプロセスを強制的に終了させます。これにより、不要なリソースの消費を防ぐことができます。

注意点​

  • データの整合性:

    • KILL コマンドを使用してクエリを中断する場合、トランザクション内での操作であれば、変更はロールバックされデータの整合性が保たれます。ただし、SELECTクエリの場合は通常データの整合性に影響はありません。

  • パフォーマンスモニタリング:

    • 定期的に SHOW PROCESSLIST コマンドを使用してシステムの状態をチェックし、予期しない長時間実行クエリがないか監視することが重要です。
    • サーバーのパフォーマンスに影響を与える可能性のあるクエリを事前に特定し、適切に最適化することも考慮してください。

以上の手法を用いることで、MySQLデータベースの管理とパフォーマンスの向上に役立ちます。また、これによムダなリソースの消費を避け、システムの応答性を維持すること可能

生クエリ​

生クエリとは、SQL文そのままのこと。
アプリケーション層でそのまま記載しているのも生クエリ（ORM不使用）

リレーション 外部キー制約(参照整合性ともいう)​

外部キーを設定して参照する側は子テーブル、参照される側は親テーブル

リレーションは単方向と双方向しかない。
一方向で参照されている側は何も知らないということ。
つまり参照されている側から参照先を知ることは難しくなる。そういう時は双方向にする。

外部キーは、親テーブルに存在しない値の登録をエラーにする 外部キーは、子テーブルに存在する値の削除をエラーにする

親子関係とリレーション​

親子関係はリレーションの性質を示すものであり、単方向か双方向かとは直接関係ない。
親モデルと子モデルの関係は、通常は双方向のリレーションで実装されますが、必要に応じて単方向にすることも可能です。

双方向リレーションにおける親子関係 双方向リレーションを持つ場合でも、親子関係は変わりません。親モデルは子モデルのコレクションを持ち、子モデルは親モデルを参照します。この関係性は、データベース内の外部キー制約によって強化されます。

リレーションシップの方向​

参考URL

リレーションシップには、双方向のリレーションシップと単方向のリレーションシップがあります。双方向のリレーションシップを扱う場合、リレーションシップには所有者と被所有者があります。単方向のリレーションシップには所有者だけがあります。リレーションシップの所有者は、データベースのリレーションシップの更新を決定できます。

集約の実装とLazy Loading​

集約の実装とLazy Loading
Lazy Loadとは
参照URL

関連をたくさん持った巨大な集約というのは扱いづらいし、双方向の関連は複雑さを生む。 必要最低限かつ、単方向の関連しか持たない集約が望ましい

カスケード​

MySQLカスケード参考URL

リレーションにおけるオプションのひとつで、依存関係を持った親と子のレコード同士の整合性を保つために、親への操作を子のレコードにも一貫して操作を連動させるような仕組み。

具体的には、親テーブルのレコードを削除または更新したときに、子テーブルの関連するレコードを自動的に削除または更新してくれる機能。

CREATE TABLE child (
    id INT, 
    parent_id INT,
    FOREIGN KEY (parent_id) 
        REFERENCES parent(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

カスケードメリット 削除処理の実装コスト削減

カスケードデメリット

  • 外部キーの利点である誤削除防止がなくなって、オペミス・実装ミスの防止ができない
  • 明示的にDELETEクエリを投げたテーブル以外のテーブル・レコードに関して何を消したか（何が消えたか）わからない
  • 運用コストの増加（リスク増加・ミス時のリカバリコスト増加）

カスケード削除に頼り切ってしまうと、親のテーブルに削除を投げた際に、どのテーブルのどの行が削除されたのかが明示的にはわからないというのは、開発において思わぬ想定の挙動を生む可能性があります。 それなのであれば、多少コストがかかっても自前で削除ロジックを組み、削除したいテーブル・レコードに対する削除処理を1つずつ書いたほうが安心に繋がります。 また、DBの操作ログ・監査ログからも、該当削除クエリによる影響範囲などが追いづらいという点もあります。

カスケードオプション​

SET NULL 親の削除・更新時、子テーブルの外部キーカラムの値をNULLにする

RESTRICT 親の削除・更新時、その行に該当する外部キーカラムのデータを持つレコードが子テーブルにあれば、親の削除・更新を拒否する

NO ACTION MySQLではRESTRICTと同等であり、一部のRDBでは遅延チェックが働く

SET DEFAULT MySQLではこの定義文は拒否される

CASCADE 上記説明を参照

クエリービルダー​

ORMとか使っていると、どうしても生のSQLを書きたくなる時がある。 その時はクエリービルダーで検索する

idについて​

Facebook, Twitter, Instagramなどがどうやって生成しているか

primary_key : ソートができる。けど何か悪い？ UUID : sortができないが秘匿性は上がる。パフォーマンスが少し落ちる ULID : UUIDの欠点をカバー。ULIDにはタイムスタンプが先頭にある。プライマリキーにUUIDを指定した場合はレコード数が増えていくほど、INSERT時間が増えていくことになる。 ※性能劣化は2割程度

昨今ではULIDを使うのが便利かも

一般のお客さんが使う(ログインやお気に入り)機能を持っているので、ユーザやデータ規模の予測しやすい整数系の id を避けたいという意図です 2台のサーバ運用だと衝突する可能性がある（auto_increment）ため、

UPSERT​

参考URL

データがあればUPDATE、なければINSERTができるやつ。 postgresだと高速。mysqlだと遅いとのこと。

mysql だとそもそも upsert 周りが弱い (Postgres みたいな conflict target がない) のと primary key として使う ULID の生成を model のレイヤーでやってるせいで import が動かん

DDL(Data Definition Language) データ定義言語​

DDLとは、コンピューターーで用いられる人工言語の分類のひとつで、データを格納するための構造を定義するための言語。

データベースの構造や構成を定義するために用いられるものが多く、単にDDLといった場合には、リレーショナルデータベース（RDB）の制御に用いられるSQL言語の一部の命令群を指すことが多い。

SQLにおけるDDLには、データベースやテーブル、ビューなどの作成を行うCREATE文や、削除するDROP文、変更を加えるALTER文、データを全削除するTRUNCATE文などが含まれる。

ここで言いたいのは、 プロジェクトのマイグレーションファイルで管理をするのはDDLまでじゃないの？

マイグレーション​

リファレンス

マイグレーションファイルはDBへの変更を記述しておくファイル 例として、テーブルを作成したいときや、カラムを追加したい時はマイグレーションファイルを作成する。

マイグレーションファイルはテーブルの中身のカラムを作成するファイル。 ※マイグレーションではRubyのDSLを持っているので生のSQLを作成する必要はない。 そしてスキーマとスキーマへの変更をDBの種類へ依存せずに済む。

バルクインサート​

バルクインサートとは、RDBのテーブルに行を追加する際、複数の行を1回のSQL文の実行で追加すること。
データベースに対する命令のひとつでありたくさんのデータを、まとめて一気にデータベースに突っ込むとき使う命令
大量のデータを1回の命令でデータベースに入れられる、insert文っぽいやつ

SQLクエリのN+1問題​

参考URL
RailsでのN+1問題発見ツール

SQLでの対応策としては2種類ある。

  • JOIN句による表の結合 テーブル結合自体に時間がかかるが、1件のクエリで欲しい情報を得られる。

  • Eager Loading（必要なデータを事前にロード） テーブルの取得に1回クエリを発行。 別テーブルから、今後の処理に必要なデータを1回のクエリでまとめて取得。 その後アプリ側で、データの結合を行う。 といった手法です。クエリは2件で済みます。

-- まずアプリ側に、書籍テーブルの情報をドサッと持ってくる
SELECT * FROM 書籍;
-- 各書籍の借用者IDを格納する配列を作成する
userIDs = [3, 1, 2, 1, 4]
-- よって、利用者テーブルから、IDが [ 3, 1, 2, 1, 4 ] の利用者の情報を取得すれば良さそうとなる。

-- 利用者テーブルから、利用者情報を取得する
SELECT * FROM 利用者 WHERE ID IN (3, 1, 2, 1, 4);

これで必要な書籍情報と、利用者情報がすべて取得できました。今後クエリは発行しません。

SQLアンチパターン​

SQLアンチパターン勉強会　第三回：IDリクワイアド

論理削除は実用的なのか​

参考URL

論理削除は削除とみなしフラグを立てるだけ。
問題の本質として削除という状態をテーブルに持たせてしまっているのがだめ

以下の場合は削除フラグを持たせてもいい

  • 対象のテーブルが小さくINDEXが不要
  • そのテーブルが関連するテーブルの親になることがなくデータを取得する際にJOINの対象になることがない。
  • UNIQUE制約や外部キーが不要

論理削除を使わない場合の対象法としては、削除するデータは別テーブルに移動することが良いと言われている。
【テーブル設計】削除フラグを使わず削除テーブルを使うべき

に最終更新