SQLスキルチェック - ノーマル編（基礎的な知識があり、ある程度の動くものを作れるようになった段階）
はじめに
前回の初学者編の続きとして、今回もデータベースとSQLの業務スキルレベル 判別表の問題にチャレンジしていきたいと思います。
悲観ロックと楽観ロックの違い
悲観ロックと楽観ロックは、どちらも排他制御（共有資源に対して同時にアクセスしても問題なく動作する制御）の方式のことです。例えば、通販サイトで在庫1点の商品に対して2人のユーザーが購入しようとした場合に、排他制御をすることで2人に購入させるというトラブルを防ぐことができます。（参考：悲観ロックと楽観ロックの違い）
- 悲観ロック：更新対象データを取得時にロックして他者が操作できないようにする方式で、片方の人はロックが解除されるまで待機することになります。（同じデータを他者が頻繁に更新するという悲観的な考え方です）
- 楽観ロック：更新対象データが更新前と同じであるかを確認して更新する方式で、片方の人はバージョンの違いからエラーとなりアクセスできなくなります。（同時アクセスはあまり起きないだろうという楽観的な考え方です）
ALTER TABLE
ALTER TABLEを使うと、既存のテーブルの構造を変更することができます。（参考：テーブル構造を変更する(ALTER TABLE文)）
# テーブル名の変更（sales_2022→sales_2023）
ALTER TABLE sales_2022 RENAME TO sales_2023
# カラム名の変更（name→product_name）
ALTER TABLE sales RENAME COLUMN name TO product_name
# カラム定義の変更（priceカラムをbigintに変更）
ALTER TABLE sales MODIFY price bigint
# カラムの追加（representativeカラムをtext型で追加）
ALTER TABLE sales ADD representative text
# カラムの削除（representativeカラムを削除）
ALTER TABLE sales DROP representative
「SELECT *」と書いてはいけない理由
- 不要な項目が含まれることで全体としての処理の量が増え、処理速度が低下し、メモリやディスクの消費も大きくなる。
- 後からカラムの追加/削除があった際に、結果レコードに対してインデックスでアクセスしていた場合、列がずれる可能性がある。
- プログラムソースを見るだけでテーブル定義が把握しやすくなる。
- ストアド（DBへの命令をまとめたもの）を再コンパイラ（翻訳）しないとエラーになる場合がある。
（参考：SQLについて質問です。）
Autocommitとトランザクション分離レベル
Autocommit（MySQLではデフォルトで有効）
Autocommitモードを有効にすると、データの変更は直ぐに反映されます。無効にすると、トランザクションをコミットしなければデータの変更は反映されません。（参考；MySQLのautocommitとトランザクションレベルの分離レベルのメモ）
トランザクション分離レベル
MySQLの場合、トランザクションの分離レベルはtransaction-isolation = valueで指定することができます。valueのデフォルト値はREPEATABLE-READです。
| value | 説明 |
|---|---|
| READ-UNCOMMITTED | 他のトランザクションのコミットされていない変更が見えます。 |
| READ-COMMITTED | コミットされた変更が見えます。 |
| REPEATABLE-READ | トランザクション開始時にコミットされていたデータのみ見えます。 |
| SERIALIZABLE | 全てのSELECTでロックを行うことでトランザクションを直列化し競合が発生しないようにします。 |
ACID特性
ACID特性とは、トランアクション処理において必要とされる4要素「Atomicity（原子性）」「Consistency（一貫性）」「Isolation（独立性）」「Durability（永続性）」の頭文字をとったものです。
- A：一連の処理は全体として実行される/されない、のどちらかであることが保証されていること。
- C：処理の結果の整合性が保証されていること。
- I：処理の結果を他からみることができ、実行途中の状態が他へ影響しないということ。
- D：完了した処理の結果が失われずにDBに記録されていること。
NULL値を置換する関数
NULL値は、CASE関数の中で「IS NULL」を用いて置換することもできますが、ここではNULL値の置換に特化した関数を紹介したいと思います。（参考：NULL値を置き換える (NVL,ISNULL,IFNULL,COALESCE)）
- NVL, ISNULL, IFNULL：第一引数がNULLの場合、第二引数の値を返す。
- COALESCE：引数の先頭から評価し、最初に出現したNULLでない値を返す。
例として、こちらのtestテーブルでNULL値を置換した場合を試してみます。
| name | English | Chinese |
|---|---|---|
| Joey | 80 | 70 |
| Chan | null | 85 |
| Monica | null | null |
SELECT name, NVL(English, 60) AS language FROM test
SELECT name, ISNULL(English, 60) AS language FROM test
SELECT name, IFNULL(English, 60) AS language FROM test
| name | language |
|---|---|
| Joey | 80 |
| Chan | 60 |
| Monica | 60 |
SELECT name, COALESCE(English, Chinese, 60) AS language FROM test
| name | language |
|---|---|
| Joey | 80 |
| Chan | 85 |
| Monica | 60 |
※ COALESCEは「合体する」という意味の英単語です。
タプル
タプルとは、RDBにおけるレコード(行)の呼び名のことです。（参考：由美ちゃん、これがRDBの要素だよ）
| SQL | ファイルシステム | RDB | |
|---|---|---|---|
| 表 | テーブル | ファイル | リレーション |
| 行 | ロー | レコード | タプル |
| 列 | カラム | フィールド | 属性 |
スキーマ
スキーマとは、複数テーブルを束ねる論理的なハコのような概念です。スキーマの目的としては「1つのDBを多数のユーザーが干渉することなく利用すること」「スキーマ毎にアクセス権を管理しセキュリティを高めること」が挙げられます。（参考：データベースのスキーマを理解する, DBのスキーマとは何か？DBごとの違い）
個人的にはプロジェクト毎にスキーマを分たり、ユーザー毎にスキーマを分けて管理するようなイメージを持っています。
ER図
ER（Entity Relationship）図とは、データの実態 (Entity) の関係性 (Relationship) を図で表現したもので、システム設計の部分にあたる上流工程で利用されます。（参考：ER図とは？書き方やテクニックをわかりやすく解説）
| ER設計 | 物理DB | 具体例 |
|---|---|---|
| エンティティ名 | テーブル名 | 顧客, 商品 |
| アトリビュート | カラム名 | 顧客ID, 顧客名, 商品名, 価格 |
| アイデンティティファイア | レコード識別子 | 顧客ID, 商品ID |
| カーディナリティ | 多重度 | 1対1, 1対多, 多対多 |
O/Rマッピング
O/Rマッピング（Object/Relational Mapping）とは、オブジェクトとRDBのレコードをマッピング（対応付け）することです。O/Rマッピングの段階としては、①低レベルAPI→②前後処理の抽象化→③クエリと単純なオブジェクトのマッピング→④クエリと関連ナビゲーション可能なオブジェクトのマッピング→⑤テーブルとオブジェクトのマッピングに分けられます。（参考：O/Rマッピングの役割とメリット, 段階的に理解するO/Rマッピング）
インデックス
インデックスとは、テーブル内の特定データに付与する索引番号のことです。データ数が膨大なテーブルで一部のレコード (1~15%) を頻繁にアクセスする場合や、JOINなどのキーになっているカラムに対して有効であり、ソート作業を省略できるというメリットがあります。ただし、インデックス分のカラムがテーブルに追加されるため、データが少ないテーブルなどには不向きです。（参考：SQLのインデックスとは？作成、確認、削除方法からメリットまで解説！, SQL：INDEXのメリットとデメリットについて）
例えば、以下のようなテーブルに対してnameを検索する場合、データが順番に並んでいないため検索に時間がかかってしまうことがあります。そこで、CREATE TABLEを用いてnameのインデックスを作成（予めソートした状態でDBに保存）すると以下のようになります。
元のテーブル
| no | name | age | prefecture | position |
|---|---|---|---|---|
| 1 | yamamoto | 32 | nagoya | employee |
| 2 | abe | 28 | tokyo | employee |
| 3 | kimura | 36 | chiba | leader |
| 4 | hatano | 41 | osaka | manager |
| 5 | ono | 25 | tokyo | employee |
nameのインデックス
| no | name |
|---|---|
| 2 | abe |
| 3 | kimura |
| 4 | hatano |
| 5 | ono |
| 1 | yamamoto |
※ 並び順を保持するのであり、この並び替えを行ったテーブルを保持するわけではありません。
複合インデックス
複合インデックスとは、複数の並び替え条件でインデックスを作成することです。（参考：DBのインデックスと複合インデックス）
ただし、複合インデックスでは2つ目以降のインデックスで利用できないことに注意が必要です。例えば、以下のテーブルでidのみを検索した場合、全行検索が実行されることとなります。
| id | team | name |
|---|---|---|
| 2 | 1 | abe |
| 3 | 1 | kimura |
| 4 | 1 | hatano |
| 5 | 2 | ono |
| 1 | 2 | yamamoto |
カーソル処理
カーソルを利用した処理では、まずカーソル（最初に取り出すSELECT文）を定義し、取り出したテーブルに対して1行ずつ条件を判定し、条件に応じてデータを変更することができます。ただし、別の処理を並行で実行してた場合意図しない更新が発生することがあるため、時にはロックをかけることも重要です。（参考：「カーソル」を理解する, 【DB基礎】カーソルとは）
| 処理の流れ | SQL構文 |
|---|---|
| ①カーソルの宣言 | DECLARE カーソル名 CURSOR FOR (SELECT文) |
| ②カーソルを開く | OPEN カーソル名 |
| ③データの取得 | FETCH NEXT FROM カーソル名 INTO 変数リスト |
| ④ループ処理の実行 | WHILE (条件式) BEGIN (処理内容) WHERE CURRENT OF カーソル名 END |
| ⑤カーソルを閉じる | CLOSE カーソル名 |
テーブルの一部をCSVで出力
MySQLの場合、INTO OUTFILEを用いることでテーブルの一部をCSVファイルとして出力することができます。
SELECT * FROM table_name INTO OUTFILE 'file_path'
phpMyAdmin
phpMyAdminとは、ブラウザ上でMySQLの操作を行うことができるツールで、都度ターミナルを立ち上げてMySQLにログインしSQLを書くという手間を省くことができます。（参考：SQLの管理に役立つ！phpMyAdminの使い方【初心者向け】）
相関サブクエリ・自己相関サブクエリ
相関サブクエリとは、自己テーブルを参照したサブクエリを記述して、同一テーブルや他のテーブルの値を参照するクエリのことです。特に自己テーブルと結合させたものを自己相関サブクエリと呼びます。（参考：【SQL】「相関サブクエリ」とは何か？, 相関副問い合わせ(相関サブクエリ)）
SELECT *
FROM a
WHERE (SELECT price FROM b WHERE a.name = b.product) > 100
SELECT *
FROM a
WHERE (SELECT AVG(price) FROM b WHERE a.name = b.product) > a.price
一般的に、相関サブクエリではパフォーマンスが落ちる傾向があるため、以下のように非相関サブクエリへの置き換えを検討する必要があります。（参考：相関サブクエリとパフォーマンス）
# 相関サブクエリ (3248 ms)
# サブクエリの処理が1行ごとに実行されるため低速
SELECT
name,
(SELECT count(*) FROM employees WHERE company_id = companies.id) AS employees_count
FROM companies
ORDER BY id
# 非相関サブクエリ (52 ms)
# サブクエリの結果が一時テーブルに保持されるため高速
SELECT
name,
coalesce(employee_count, 0) # NULL値を0で置き換え
FROM companies
LEFT JOIN (
SELECT company_id, count(*) AS employees_count
FROM employees
GROUP BY company_id
) AS sub ON companies.id = sub.company_id
ORDER BY id
デッドロックの回避
デッドロックとは、複数ユーザーが同じタイミングでDB内のテーブルを参照した際に両方ともロックされ、永久的に処理がストップした状態のことです。通常、複数ユーザーが同じタイミングで書き込みをしようとしてもどちらか遅れた方が、相手の処理が終わるのを待つだけですが、デッドロックの場合は複数テーブルへアクセスする順番が異なったために両方ともロックされてしまいます。（参考：デッドロックを回避するために）
少しでもデッドロックをゼロに近づけるには、以下のことに気を付ける必要があります。
- トランザクションからアクセスするテーブルの順番を一貫する
- トランザクションを冗長的にしない
- FOR UPDATEで行ロックする
SQLの制約 (CONSTRAINT)
| 制約の種類 | 説明 |
|---|---|
| NOT NULL | ・NULL値を禁止 |
| CHECK | ・指定した条件に満たないデータを禁止 |
| UNIQUE KEY | ・重複禁止（NULLはOK） ・複数の列に設定可能 |
| PRIMARY KEY | ・一意を保証（重複とNULL禁止） ・1テーブルにつき1つ ・複合主キーの指定 |
| FOREIGN KEY | ・参照した他のテーブルの列にない値を禁止 ・2つの列の外部参照 |
| DEFAULT | ・デフォルト値を設定 |
CREATE TABLE tbl_sales
(
product_id INT PRIMARY KEY, # 一意を保証(重複とNULL禁止)
product_name VARCHAR(16) UNIQUE NOT NULL, # NULL禁止
price INT DEFAUlT 1000, #デフォルト値を設定
user_id CHAR(4)
CONSTRAINT price_check CHECK(price >= 100 AND price <= 10000) # 値段を制限
UNIQUE (product_name, price) # 重複禁止
FOREIGN KEY (user_id, user_gender) REFERENCE tbl_user(user_id) #外部の値を参照
)
（参考：SQLの制約の種類とその指定方法）
ストアドプロシージャー・トリガー
ストアドプロシージャーとは、DB側に登録（CREATE PROCEDURE）したSQLの一連の処理のことです。ストアドプロシージャーを使うメリットとしては以下の2つがあります。（参考：ストアドプロシージャとトリガを学ぼう）
- 処理の高速化：処理の作成時にプリコンパイル（機械語に近い形式）に変換されるため、SQL文で1文ずつ呼び出す場合に比べて処理が高速になります。
- 通信量の削減：処理を予めDBサーバーに登録するため、サーバーとクライアントの通信量が減ります。
一方、あるイベントが起きたときにDBで実行されるストアドプロシージャーをトリガーと呼びます。トリガーを用いることで、別のテーブルの変更と連動させてあるテーブルの中身を変更させることが可能です。
Discussion