データベーススペシャリスト 午後対策

午後対策

データベーススペシャリスト(DBS)の午後試験は大きく分けて論理設計、物理設計の2つに分けられれますが、エンジニアにとっては物理設計の方が簡単に思えると思います。

過去問から引っ張り出した頻出問題について、QAをまとめてみました。
一部ChatGPTを用いて回答を作成しています。

インデックスとパーティショニング

インデックスとパーティショニングは頻出問題です。

そのため、インデックスキー、パーティションキーの扱いに慣れることは必須です。

インデックスとパーティショニングの違い

パーティショニング

物理的にファイルを分割し、アクセスを高速化。
DBSではパーティションキーを用いてのパーティショニングのことを、"区分キーを用いてのテーブルの物理分割"と表現しています。
また、区分キーで分割した後、さらにローカルインデックスという仕組みで分割していることもあります。

カテゴリ	説明
目的	データベーステーブルを複数の部分に分割することで、データの管理を効率化し、クエリの性能を向上させます。
種類	範囲、リスト、ハッシュ、コンポジットなどがあります。
概念	物理的または論理的に一つのテーブルを複数の部分（パーティション）に分割します。
利用シーン	大量のデータを持つテーブルで、特に古いデータと新しいデータでアクセスパターンが異なる場合や、特定のカラムでのクエリが多い場合に有効です。

CRUDにおける注意点

操作	注意点
Create	パーティションキーに基づいて、レコードは適切なパーティションに格納されます。適切なパーティションキーの選定が重要です。
	不適切なパーティションキーを選定すると、データの不均衡が発生し、特定のパーティションに過負荷がかかる可能性があります。
Read	クエリの性能は、対象となるパーティションの数やサイズに依存します。必要なパーティションのみを対象とするクエリを設計することが重要です。
	パーティションを跨ぐクエリは避けるべきです。これは性能を大幅に低下させる可能性があります。
Update	更新操作は、特定のパーティション内でのみ行われるべきです。パーティションを跨ぐ更新は、性能に悪影響を与える可能性があります。
	パーティションキーを変更すると、レコードの移動が発生する可能性があります。これは、特に大きなテーブルで高コストとなる可能性があります。
Delete	パーティションの削除は、そのパーティション内の全てのデータを削除します。これにより、データの復旧が困難になる可能性があります。
	個別のレコードを削除する場合、対象となるパーティションを正確に特定する必要があります。不要なパーティションを読み込むと、性能に悪影響を与える可能性があります。

インデックス

B-treeなどのアルゴリズムでインデックスを作成し、アクセスを高速化。

カテゴリ	説明
目的	データの検索性能を向上させるために使用されます。
種類	主キー、一意、非一意、フルテキスト、空間インデックスなどがあります。
概念	テーブルの指定したカラムに対して、キー値とデータの位置情報が格納されたデータ構造を作成します。
利用シーン	検索、ソート、フィルタリングが頻繁に行われるカラムに対して作成します。

CRUDにおける注意点

操作	注意点
Create	新しいデータが挿入されるたび、関連するインデックスも更新される。性能オーバーヘッドが存在する。
	インデックスのフラグメンテーション（断片化）が発生する可能性がある。
Read	クエリオプティマイザが常に最も効果的なインデックスを選択するわけではない。クエリやインデックスの調整が必要。
	インデックスが全ての読み取り操作において必要とは限らず、多くのインデックスがパフォーマンスに悪影響を及ぼす可能性がある。
Update	インデックスカラムの値が変更されると、インデックスも再構築される必要があり、パフォーマンスが低下する可能性がある。
	インデックスの更新中にロックが発生し、他のトランザクションがブロックされることがある。
Delete	関連するインデックスも更新される必要があり、性能オーバーヘッドが存在する。
	インデックスのフラグメンテーション（断片化）が発生し、再構築や再編成が必要になることがある。

総合的に見ると、インデックスは読み取りのパフォーマンスを向上させるものの、書き込み操作にはオーバーヘッドをもたらす可能性がある点を理解することが重要です。適切なバランスを取るために、実際のアクセスパターンやワークロードを考慮して、必要なインデックスのみを作成・維持することが求められます。

インデックスとパーティショニングは同時に使用できるのか?

パーティショニングとインデックスは、異なる目的と概念を持つため、それぞれの利点を最大限に活かすために、同時に使用でき、かつ推奨されます。

パーティションを利用することで、クエリが対象とするデータ量を削減でき、全体のクエリパフォーマンスが向上します。
各パーティションに対して適切なインデックスを設定することで、データの検索やアクセス性能がさらに向上します。

-- テーブル作成とパーティショニング
CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    order_date DATE
)
PARTITION BY RANGE (order_date) (
    PARTITION p0 VALUES LESS THAN ('2023-01-01'),
    PARTITION p1 VALUES LESS THAN ('2023-02-01'),
    PARTITION p2 VALUES LESS THAN ('2023-03-01'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

-- インデックスの追加
CREATE INDEX idx_orders_order_id ON orders (order_id);
CREATE INDEX idx_orders_product_id ON orders (product_id);

-- 探索
SELECT * 
FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-02-01' 
AND order_id = 100;

この例では、order_idとproduct_idに対してグローバルインデックスが作成されています。この場合、クエリが特定のパーティションにしかアクセスしないことが確定している場合、グローバルインデックスは効率的でない場合があります。

なぜなら、クエリが特定のパーティションだけにアクセスする場合でも、データベースは全てのパーティションに存在するグローバルインデックスをスキャンする必要があるからです。

従って、クエリが特定のパーティションにしかアクセスしないことが予想される場合には、ローカルインデックスの方が効果的です。

CREATE INDEX idx_orders_order_id_local ON orders (order_id) LOCAL;

SELECT * 
FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-02-01' 
AND order_id = 100;

クラスタリング

1台のDBでパフォーマンスが足りない場合、マシンを増やして分散キーを設定してクラスタを構成します。

なので、インデックスキー、パーティションキーとはまた別に分散キーという概念があります。

特定のパーティションにのみアクセスするようなクエリの場合、クラスタの性能が発揮されません。

シェアードナッシングとシェアードエブリシング

クラスタ化した場合にはシェアードナッシング、シェアードエブリシングで構成するのかが問題となります。

シェアードナッシングの場合joinする際にどこかのノードにデータを集めないといけないので通信のオーバヘッドが発生します。

そのため、マスターテーブルなど頻繁に更新しないがjoinに多く使われるものはシェアードナッシングに設定することがあります。

分散キーの粒度

クラスタ化した場合に発生する問題として、分散キーの粒度の問題があります。

分散キーとして主キーである{id, 明細番号}を設定すると同じidであっても違うマシンにページが配置されてしまうことがあり、その場合に同じidへのアクセス速度が遅くなります。

その時は分散キーをidのみにすることがあります。

デッドロック

デッドロックを回避するためには、以下のようないくつかの方法が存在します：

• ロックの順序
  全てのトランザクションがテーブルや行にロックをかける順序を一定にすることで、デッドロックの可能性を減少させることができます。

• ロックのタイムアウト
  ロックの取得にタイムアウトを設定することで、長時間ロックを保持し続けることを防ぎます。タイムアウトが発生すると、トランザクションはロールバックされます。

• ロックの粒度
  可能な限り行レベルのロックを使用し、テーブル全体のロックを避けることで、コンテンションとデッドロックのリスクを減少させます。

• 適切なトランザクションの設計
  トランザクションは、可能な限り短時間で完了するように設計するべきです。長いトランザクションは、他のトランザクションがロックを取得できなくなるリスクがあります。

• デッドロック検出
  多くのデータベース管理システムはデッドロック検出ツールを提供しており、デッドロックが発生した際には一方のトランザクションを自動的にロールバックしてデッドロックを解消します。

• READ COMMITTED イソレーションレベル
  デッドロックのリスクを減少させるためには、READ COMMITTED イソレーションレベルを利用できます。しかし、このレベルでは他のトランザクションのコミットされていないデータは読めないため、アプリケーションの要求によっては利用できない場合があります。

• インデックスの使用
  適切なインデックスの使用は、ロックの競合を減少させるためにも重要です。インデックスが適切に配置されていれば、データベースは必要な行により迅速にアクセスでき、ロックの時間が短縮されます。

マルチスレッドでのロック

• パターン1
  READ COMMITEDをする場合、複数のスレッドでUPDATEする順番が異なるとデッドロックが発生する。

-- スレッド1がアカウント1のロックを取得
UPDATE table SET column = value WHERE id = 1;

-- スレッド2がアカウント2のロックを取得
UPDATE table SET column = value WHERE id = 2;

-- スレッド1がアカウント2のロックを取得しようとするが、スレッド2によってロックされている
UPDATE table SET column = value WHERE id = 2;

-- スレッド2がアカウント1のロックを取得しようとするが、スレッド1によってロックされている
UPDATE table SET column = value WHERE id = 1;

• パターン2
  REPETABLE READを使用する場合、SELECTの参照ロック、UPDATEの共有ロックがトランザクション終了時に解放される。
  そのため、下記のように選択、更新の順番が各スレッドで同じでもデッドロックが発生しうる。

-- スレッド1
SELECT ... FROM table WHERE id = 1 FOR SHARE;

-- スレッド2
UPDATE table SET column = value WHERE id = 1;

外部キー制約

外部キー制約の種類

• CASCADE
  主テーブルのレコードが削除または更新されると、外部キー制約を持つ関連テーブルのレコードも自動的に削除または更新されます。

• SET NULL
  主テーブルのレコードが削除または更新されると、関連テーブルの外部キー値がNULLに設定されます。

-SET DEFAULT
主テーブルのレコードが削除または更新されると、関連テーブルの外部キー値がデフォルト値に設定されます。

• NO ACTION
  主テーブルのレコードの削除または更新が、外部キー制約に違反する場合、その操作は拒否されます。

• RESTRICT
  これも「NO ACTION」と同様、関連テーブルに依存するレコードが存在する限り、主テーブルのレコードの削除または更新が拒否されます。

NO ACTIONとRESTRICTの違い

NO ACTION と RESTRICT の違いは主に、定義とタイミングにありますが、多くのデータベースシステムでは、実際の効果は同じと見なされます。どちらのオプションも、外部キー制約に違反すると操作（更新や削除）を拒否します。

• NO ACTION(猶予モード?)
  NO ACTION は、外部キー制約の違反がチェックされるタイミングが、トリガーがある場合にその後になることを意味します。つまり、他のすべての制約やトリガーが処理された後で外部キー制約が評価されます。
  データベースが制約をチェックするタイミングが異なることがあります。具体的には、トランザクションの終了時にチェックが行われることがあります。つまり、NO ACTIONは他の操作が完了するまで制約違反のチェックを延期する可能性があります。

• RESTRICT(即時モード?)
  RESTRICT は、他の依存するテーブルに対する変更がすぐに評価され、違反が検出された場合には即座に操作が拒否されます。
  子テーブルの行が親テーブルの対応する行を参照している場合、親テーブルの行の削除やキーの更新を即座に拒否します。

ほとんどのRDBMSでは、NO ACTIONとRESTRICTは実際の挙動やパフォーマンスに違いはないとされています。どちらも、参照されているレコードが存在する場合には更新や削除を拒否し、外部キー制約が違反されたときにエラーを即座に返します。

SQLの例

betweenを使ったjoin

SELECT *
FROM orders
INNER JOIN ranges ON orders.order_id BETWEEN ranges.start_id AND ranges.end_id;

GroupをGroupにGrant

CREATE ROLE group_a;
CREATE ROLE group_b;

GRANT group_b TO group_a;

Left Joinで結合した同士のテーブルのSUMには、NULLが入るためCOALESEを用いる

SELECT 
  o.order_id, 
  COALESCE(SUM(oi.quantity * oi.price), 0) AS total_amount
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

完全外部結合もNULLが入るため、COALESEを用いる

SELECT 
  COALESCE(o1.order_id, o2.order_id) AS order_id, 
  COALESCE(o1.name, o2.name) AS name
FROM orders o1
FULL OUTER JOIN orders o2 ON o1.order_id = o2.order_id;

積集合

SELECT id FROM table1
INTERSECT
SELECT id FROM table2;

Winwow関数のframe区

入力

category	product	amount
Fruits	Apple	10
Fruits	Banana	20
Fruits	Cherry	30
Veg	Carrot	15
Veg	Potato	25

SELECT 
  category,
  product,
  amount,
  SUM(amount) OVER (
    PARTITION BY category 
    ORDER BY amount 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) as sum_amount
FROM sales;

出力

category	product	amount	sum_amount
Fruits	Apple	10	30
Fruits	Banana	20	60
Fruits	Cherry	30	50
Veg	Carrot	15	40
Veg	Potato	25	25

再起問い合わせ

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    manager_id INT REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES (1, 'CEO', NULL);
INSERT INTO employees VALUES (2, 'Manager A', 1);
INSERT INTO employees VALUES (3, 'Manager B', 1);
INSERT INTO employees VALUES (4, 'Employee A', 2);
INSERT INTO employees VALUES (5, 'Employee B', 3);

WITH RECURSIVE superiors AS (
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE employee_name = 'Employee A'
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    JOIN superiors s ON e.employee_id = s.manager_id
)
SELECT employee_name FROM superiors WHERE employee_name != 'Employee A';

• 結果

employee_name
Manager A
CEO

INとEXSITの違い

SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'Tokyo');

SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND location = 'Tokyo');

• 性能面の違い
  INはリストの中から一致する値を探すため、リストが大きいと性能が低下します。
  EXISTSは、一致するものが見つかった時点で処理を停止します。そのため、大量のデータに対して効率的です。

• 使い方の違い
  INは値のリストを返す必要があります。
  EXISTSは行が存在するかどうかをチェックするので、値を返す必要はありません。

With句

with
avg_salary as (
    select avg(salary) as average_salary  -- 平均給与をWith句で計算
    from employees
),
filter_employees_by_avg as (
    select e.employee_id, e.employee_name, e.salary
        from employees as e
    inner join avg_salary as a
        on e.salary > a.average_salary;
)
select * from filter_employees_by_avg

その他

データ復旧、バックアップ

データをバックアップする方法として、フルバックアップ、増分バックアップ、差分バックアップがある。

通常、データを復元する場合はフルバックアップ+増分バックアップor差分バックアップを用いてDBの状態を復元する。
その際、差分バックアップがない分のデータは消失する。

テーブル内に異常値が混入した場合、
DBのリストアとエクスポート/インポート機能を用いて異常値を排除することができる。

1. 異常値が混入した直前までの状態をサブDBにリストアする
2. 異常値の更新前のデータを抽出し、データをエクスポートする
3. 2でエクスポートしたデータを、メインDBに取り込む

バックアップを用いてDBのアップデートをする場合、
新旧テーブルの定義が変わっている場合は変換機構を用意する必要がある。

ストアードプロシージャーを使う意味は?

APとDBの通信量削減

性能試験

性能試験を行う場合、テストデータの作成方法や、評価を行うアプリケーションの同時実行によって結果が変わってくる

• テストデータの作成方法
  本番ではランダムにidが作成され登録されるデータでも、テストデータを作成する場合にidが連続する場合クラスタ率が上がり、性能試験に影響がある

• アプリケーションの同時実行
  バッファヒット率が高いアプリケーションを並行に評価すると、片方を処理する際に片方のデータをバッファから追い出す可能性があるため、バッファヒット率が低くなる。

Primary KeyとUniq KeyのNotNull制約

そもそもPrimary KeyはNullを許可されないので、NotNull制約が自動的につく。
Uniq KeyはNullは許可されるため、データの重複の判定がややこしい(データベースにより異なる)

Uniq Key(id, name)

INSERT INTO A VALUES(1, null)
INSERT INTO A VALUES(1, null) => 挿入できる