🐕‍🦺

フロントエンドエンジニアがDBを初めて触って学んだこと

まさきち

2024/05/20に公開

MySQL

tech

はじめに

ここ2~3ヶ月のうちにDBを触る機会が多くなってきたので、備忘録を兼ねてこれまで学んだことをアウトプットします。

SQLの実行順

SQLは以下の順序で実行されます。クエリ生成時においては、異なる順序や方法で実行することがありますが、結果的には以下の順序に従って生成されます。

FROM -- テーブル指定（最初に、FROM句で指定されたテーブルやビューが読み込まれます）
ON -- 結合条件の指定
JOIN -- テーブルの結合（このステップでON句が利用され、結合条件が適用されます）
WHERE -- フィルタ条件による行の絞り込み
GROUP BY -- グループ化
HAVING -- 集計後の絞り込み
SELECT, DISTINCT -- 列や式の計算・重複行の排除（このタイミングで集約関数も適用されます）
ORDER BY -- 結果の並べ替え
LIMIT -- 取得件数の指定

WHERE句が実行されてからGROUP BY句やSUMやAVG関数が実行されるので、WHERE句の中でSUM、AVGなど集約関数を使用することはできません。 SUM、AVGは実行順序的にGROUP BYを適用してから集約が行われるため、HAVING句で集約関数を使用します。

SELECT d.name AS department_name, AVG(e.salary) AS average_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 30000 
GROUP BY d.name
HAVING AVG(e.salary) > 50000 
ORDER BY average_salary DESC
LIMIT 5;

WITH句を使用する

WITH句は、サブクエリに名前をつけて一時的に別管理にすることで、可読性やメンテナンス性を向上させる仕組みのことを指します。この一時的な名前のことをCTE（共通テーブル式）と呼びます。WITH句を知る前は、サブクエリを多用していてかなり分かりにくいクエリになっていました。

WITH句適用前

SELECT d.id, AVG(s.salary) as average_salary
FROM (
    SELECT *
    FROM employee
    WHERE hire_date > '2023-12-11'
) s
JOIN development d ON s.id = d.id
GROUP BY d.id;

WITH句適用後

WITH selected_id AS (
SELECT *
FROM employee
WHERE hire_date > '2023-12-11'
)
SELECT d.id, AVG(salary) as average_salary
FROM selected_id s
JOIN development d ON s.id = d.id
GROUP BY d.id;

これでメインクエリの部分の可読性が向上します。一点注意点として、WITH句内で定義されたCTE（今回の場合はselected_employee）は、その直後のSELECT句でのみ有効となります。WITH句とSELECT句の間に改行を入れてしまうと上手く読み取ってくれません。

JOIN句の違い

JOIN句はINNER JOIN(内部結合)とOUTER JOIN（外部結合）の二種類存在します。DBの複数のテーブルかららデータを検索して取得するケースにおいて、例えばdevelopmentテーブル、employeeテーブルに対して、「developmentテーブルから検索してデータ抽出 → employテーブルから検索してデータ抽出 → テーブルをまとめる」処理を行った場合、検索結果が表示されるまで多くの時間を要します。そのためJOIN句ではdevelopmentテーブル、employeeテーブルの両方をまとめてから条件に合うデータを抽出します。そのことを「結合」と呼び、その方法として内部結合と外部結合が存在します。

1. 内部結合

内部結合（INNER JOIN）は、それぞれのテーブルの指定したカラムの値が一致するものだけを結合します。つまり結合できなかった行は返却されません。結合相手がいない行は結合結果から消滅する為、例えば一致しないidを抽出する場合期待通りに機能しないため注意が必要です。

SELECT c.id as 'ID', c.customer_id as '顧客ID', c.last_name as 'ご芳名（姓）', c.first_name as 'ご芳名（名）', c.created_at as '作成日'
FROM employee c 
INNER JOIN development e ON e.id = c.id
where c.delivery_date IS NULL AND c.type_id = 1  AND c.old_date_flag
order by c.id desc

2. 外部結合

外部結合（LEFT JOIN/RIGHT JOIN）はそれぞれのテーブルの指定したカラムの値が一致するものを結合するのに加えて、どちらかのテーブルにしか存在しない行についても取得を行います。

SELECT c.id, c.last_name, c.first_name, c.created_at, c.updated_at
FROM customers c
LEFT JOIN sisters es ON c.id = es.customer_id
LEFT JOIN es e ON c.id = e.customer_id
LEFT JOIN con c ON c.id = c.customer_id
WHERE es.customer_id IS NULL AND e.customer_id IS NULL;

例えばidが一致しないデータを抽出する時に、LEFT JOINを使用した場合、どちらかのテーブルにしか存在しない行についてはNULLとなる為、IS NULLを使用してすべてのテーブルでNULLとなるidのみが選択されることになります。

DBの正規化が必ずしも最適解ではない

DBの正規化は、複数のテーブルに分割して管理することで保守性を向上させますが、一方でそれぞれのテーブルデータを抽出する必要がある為、パフォーマンスが低下していきます（正規化された場合、欲しいデータがそのテーブルに必ずしも存在しない為）データベース設計において、データの冗長性を許容する方法を非正規化と呼びます。私が初めてDB設計を担当した時、最低第三正規化まで設計を行いましたが、テーブルを複数管理する必要がありそれに伴う開発コストの増加が懸念されました。もともと流用できそうなテーブルも存在した為、結果として１つのテーブルで管理することになりました。

重複したデータの抽出

DB内でidは異なるがバグにより重複してしまったデータを抽出する機会があり、その際に色々試した方法を共有します。

1. GROUP BYとHAVINGを併用する場合

サブクエリでGROUP BYで抽出した結果が2件以上あるものをHAVINGで抜き出され、結合条件に基づいてテーブルが結合されます。

SELECT e.id, e.last_name, e.first_name, e.hire_date
FROM employee e
INNER JOIN (
    SELECT last_name, first_name, hire_date
    FROM employee
    GROUP BY last_name, first_name, hire_date
    HAVING COUNT(*) > 1
) dup ON e.last_name = dup.last_name AND e.first_name = dup.first_name AND e.hire_date = dup.hire_date

2. ウィンドウ関数を使用する場合

ウィンドウ関数を使用して重複したデータを抽出します。

SELECT sub.*
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (PARTITION BY e.last_name, e.first_name, e.hire_date ORDER BY e.last_name, e.first_name, e.hire_date) as rn
FROM employee e
) sub
WHERE sub.rn > 1;

ROW_NUMBERは結果に対してシーケンス番号を振ってくれる関数です。このROW_NUMBERを使用してlast_name、first_name、hire_dateが重複しているデータを抽出し2個以上のものを表示

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

3. EXISTSを使用する場合

最後にEXISTSを使用して、重複したデータを抽出します。WHERE EXISTS (SELECT 1 ...)構文はSQLにおいて、サブクエリにおいて条件にマッチした行が少なくとも一つ存在する場合にtrueとなります。返される具体的な値は関係なく、データベースシステムは行が存在するかどうかだけをチェックします。

SELECT a.*
FROM employee a
WHERE EXISTS (
      SELECT 1
      FROM employee b
      WHERE a.last_name = b.last_name
        AND a.first_name = b.first_name
        AND a.hire_date = b.hire_date
        AND a.id != b.id

重複データにおいて最新ID以外を抽出する

それぞれ重複したデータかつ各テーブルにおいて使用されていないIDから、最新のID以外を抽出する場合のクエリです。ROW_NUMBER()により、順番に番号付けを行い、それぞれ重複したデータの順位づけを行い、RowNum > 1の条件を満たす（つまり最大のID以外の）レコードを抽出します。NOT EXISTSサブクエリで各テーブルにおいて使用されていないなどの条件付けを行いWITH句を併用します。

WITH RankedCustomers AS (
  SELECT
    c.*,
    ROW_NUMBER() OVER (
      PARTITION BY c.full_name, c.full_name_kana, c.hire_date
      ORDER BY c.id DESC
    ) AS RowNum
  FROM
    employee c
  WHERE
    c.full_name != ''
    AND c.full_name_kana != ''
    AND c.ec_customer_id IS NULL
    AND NOT EXISTS (
        SELECT 1 FROM demo_sisters es WHERE c.id = es.customer_id
    )
    AND NOT EXISTS (
        SELECT 1 FROM employee_history e WHERE c.id = e.customer_id
    )
    AND NOT EXISTS (
        SELECT 1 FROM estimates_customer e WHERE c.id = e.customer_id
    )
)
SELECT
  *
FROM
  RankedCustomers
WHERE
  RowNum > 1
ORDER BY id DESC;

SQLのパフォーマンスチューニング

SQLでクエリを書く際のパフォーマンスチューニングについて紹介します。

インデックスを正しく使用する

インデックスはSQLエンジンがデータを検索する際に、該当するクエリの条件に基づいて設定されているインデックス（索引）を使用して、結果を抽出する際に使用します。クエリの実行計画（execution plan）において、インデックスを使用しない場合は、フルテーブルスキャン（全ての行を一つ一つ検査する処理）が行われ、パフォーマンスに影響を及ぼします。

例えば、以下のクエリの場合、結果を特定するために<>(不等号) 演算子を使用しており、100000 と異なIdを持つすべての行を対象にするため、インデックスの利点を活かしづらい操作となります。なのでインデックスを使用したとしても多くの行が条件にマッチしてしまう為、SQLエンジンはインデックススキャンよりもフルテーブルスキャンを選択する可能性があります。

SELECT et.employee_id FROM employee_table et  WHERE et.employee_id <> 100000;

それ以外にもIN述語の引数に（1,2,3）のような値をリストに取る場合は問題ありませんが、サブクエリを引数にする場合は注意が必要です。

SELECT et.employee_id FROM employee_table et
IN(SELECT ec.commit_id FROM estimates_customer ec);

IN述語を引数としてサブクエリを与える場合、DBはまずサブクエリを実行して、一時的なワークテーブルに格納し、そのビューをフルテーブルスキャンする為、結果の抽出に膨大なコストが発生する可能性があります。
この場合同じ結果を返すEXISTSを使用した方が早く算出できます。

SELECT * FROM employee_table et WHERE
EXISTS(SELECT * FROM estimates_customer ec where et.employee_id = ec.commit_id);

EXISTSはワークテーブルが作成されることがなく、一行でも合致する行を見つけたら、そこで検索を打ち切るので、IN述語のように全表検索しない為です(結合キーにインデックスが存在すれば、estimates_customerのインデックスを参照するのみで実表は見に行かない）。

IN述語はEXISTS以外にも結合に置き換えることが可能で、この場合は中間テーブルが作られることなくインデックスを利用することができます。

SELECT * FROM employee_table et
INNER JOIN estimates_customer ec on et.employee_id = ec.commit_id;

インデックスを効果的に機能させる為に、検索、範囲検索、JOIN操作、ORDER BY操作など、特定の値または値の範囲を迅速に見つけ出すようにする必要があります。

MAX/MIN、GROUP BY句、ORDER BY句はインデックスを使用する

MAX/MIN関数とGROUP BY句、ORDER BY句にはインデックスを使用します。いずれもソートを発生させます。データ量によっては、メモリだけでなくストレージを使用してソートが行われる可能性があり、クエリのパフォーマンスに大きく影響を与えます。
MAX/MIN関数は引数にインデックスを使用することで、そのインデックスだけスキャンされフルテーブルスキャンを回避します。

SELECT MAX(et.employee_id) FROM employee_table et

GROUP BY句、ORDER BY句もインデックスをキーに指定することで、ソートのための検索を高速化することが可能です。

WHERE句の使用を優先する

WHERE句でクエリを書くことができる場合は、HAVAING句ではなく、WHERE句で記載する方が、インデックスを利用できるため、絞り込みを効率的に行うことが可能です。

SELECT et.get_date ,MAX(et.employee_id)
FROM employee_table et
GROUP BY get_date
HAVAING get_date > '2024-03-01'

SELECT et.get_date ,MAX(et.employee_id)
FROM employee_table et
WHERE get_date > '2024-03-01'
GROUP BY get_date

また上記クエリのようにWHERE後にGROUP BY句を使用した場合、行を絞り込んだ結果からGROUP BY句によるソートを行なっているため、負荷軽減を行うことが可能です。

インデックスがない検索はなぜ遅いのか

テーブルはハードディスク（HDD）上にページ単位（MySQLがHDDを読み出す単位）で保存されています。読み出されたページはキャッシュメモリ上に配置され、それをCPUが処理します。キャッシュメモリの容量を超えると捨てられてしまう為、次の検索時にはまたHDDにアクセスすることになります。HDDにアクセスする場合、メモリにアクセスするよりも速度が遅く、テーブルのデータ自体はソートされていないため、インデックスが存在しない場合はどのデータがどのページに配置されているかが分からないためデータがある分だけ読み出さないといけなくなります。

インデックスが効いている場合は、B+tree(ビーツリー)方式を取るなどして、無駄なページを読み込まずに済ませることができます。

スパゲッティクエリからの脱却

スパゲッティクエリとは、複数のタスクを実行しようとしてしまい非常に複雑で読み解くのが難しいクエリのことを指します。意図しない結果になるだけでなく、クエリの修正やデバッグが難しくなる原因となります。

SELECT e1.name AS '名前', e1.department AS '部署', e2.name AS 'マネージャー名'
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id
WHERE e1.salary > (
    SELECT AVG(e3.salary)
    FROM employees e3
    WHERE e3.department = e1.department
)
AND EXISTS (
    SELECT 1
    FROM project_assignments pa
    WHERE pa.employee_id = e1.id
    AND pa.project_id IN (
        SELECT p.id
        FROM projects p
        WHERE p.end_date > NOW()
    )
)
AND e1.hire_date < (
    SELECT MAX(e4.hire_date)
    FROM employees e4
    WHERE e4.department = e1.department
)
ORDER BY e1.department, e1.name;

以下のクエリは以下の条件を算出していますが、多数のサブクエリや入れ子になった条件、複雑な結合などが含まれており、メンテナンスが困難であることやパフォーマンスに影響を与えてしまします。

従業員の給与が、その人が属する部署の平均給与よりも高い。
その従業員が現在進行中のプロジェクトに割り当てられている。
従業員の入社日が、その人が属する部署の中で最も遅い入社日よりも前である。

スパゲッティクエリを避けるために以下の2つの方法をとることができます。

1. UNION句の使用

UNIONは複数のSELECTの結果を統合して表示してくれる仕組みです。UNIONには以下の二種類が存在しますが、重複を気にする必要がない場合は、ソートが発生しない「UNION ALL」を使用する方が望ましいです。

「UNION」・・・SELECTの結果における重複は削除されます。
「UNION ALL」・・・SELECTの結果における重複も含めます。

-- 重複データの識別
WITH dup AS (
    SELECT last_name, first_name, hire_date
    FROM employees
    GROUP BY last_name, first_name, hire_date
    HAVING COUNT(*) > 1
)

-- 各テーブルとのJOINと結果の結合
SELECT c.id, c.last_name, c.first_name, c.hire_date
FROM project_assignments c
INNER JOIN dup ON c.last_name = dup.last_name AND c.first_name = dup.first_name AND c.hire_date = dup.hire_date
LEFT JOIN est_date e ON c.id = e.customer_id
WHERE e.id IS NULL

UNION ALL

SELECT c.id, c.last_name, c.first_name, c.hire_date
FROM project_assignments c
INNER JOIN dup ON c.last_name = dup.last_name AND c.first_name = dup.first_name AND c.hire_date = dup.hire_date
LEFT JOIN human_contract ct ON c.id = ct.human_id OR c.id = ct.int_customer_id
WHERE ct.count_id IS NULL AND ct.int_customer_id IS NULL;

2. CASE式とSUM関数の組み合わせ

CASEとSUM関数を組み合わせて、条件ごとの集約を一つのクエリで行うことが可能です。

SELECT p.id
SUM(CASE b.product_status WHEN 'FIXED' THEN 1 ELSE 0 END) as count_fixed
SUM(CASE b.product_status WHEN 'STILL' THEN 1 ELSE 0 END) as count_open
FROM products p
INNER JOIN UNFIX_products b USEING(unfix_id)
WHERE p.id = 1
GROUP BY p.id;

最後に

DBはまだまだ初心者なので、間違いがあれば指摘してもらえると助かります。ここまで読んでくださりありがとうございました。

参考文献・書籍

Arsaga Developers BlogPublication

アルサーガパートナーズ株式会社のエンジニアによるテックブログです。11月14（木）20時〜エンジニア向けセミナーを開催！詳細とご応募は👉️ arsaga.jp/news/pressrelease-cheer-up-project-november-20241114/