サイレントキラー：NULLがデータベースパフォーマンスに与える影響の理解

はじめに

データベースの世界では、「NULL」という概念はどこにでも存在します。 これは、値がないこと、未知であること、または適用できない状態を示します。 一見無害に見えますが、開発者はしばしばNULLのより深い意味合いを理解していません。 これは単なる別のデータポイントではなく、そのユニークな特性は、データベースエンジンがクエリを最適化し実行する方法を根本的に変える可能性があります。 具体的には、NULL値は、インデックス作成、COUNT()などの集計関数、およびリレーショナルJOIN操作のような重要なデータベース操作の効率に大きく影響します。 これらの影響を理解することは、パフォーマンスが高く堅牢なデータベースアプリケーションを作成する上で不可欠です。 この記事では、NULLがこれらの主要な領域にどのように静かに影響を与えているかを掘り下げ、その過小評価されがちな力のより明確な像を提供します。

コアコンセプト

詳細に入る前に、議論の中心となるいくつかのコアコンセプトを簡単に定義しましょう：

• NULL: SQLにおいて、NULLは任意のデータ値の不在を表します。 それはゼロ、空文字列、またはfalseと同等ではありません。 それはデータが欠落しているか不明であることを示す特別なマーカーです。
• インデックス: データベースインデックスは、通常BツリーまたはB+ツリーのデータ構造であり、データベーステーブルに対するデータ取得操作の速度を向上させます。 これにより、データベースシステムはテーブル全体をスキャンすることなく、クエリの基準に一致する行を迅速に見つけることができます。
• COUNT(): グループ内のアイテム数を返す集計関数です。 COUNT(*)はすべての行をカウントしますが、COUNT(column_name)は指定された列のNULL以外の値をカウントします。
• JOIN: 2つ以上のテーブルの関連列に基づいて行を結合するために使用されるSQL句です。 一般的なタイプには、INNER JOIN、LEFT JOIN、RIGHT JOIN、およびFULL OUTER JOINがあります。
• カーディナリティ: 列内のユニークな値の数です。 高いカーディナリティは多くのユニークな値を意味し、低いカーディナリティは少ないユニークな値を意味します。
• 選択度: ユニークな値と総行数の比率です。 高い選択度は、列の値がフィルタリングに適していることを意味します。

NULLとインデックス

NULL値とインデックスの相互作用は、パフォーマンスに影響を与える可能性のある最も重要な領域の1つです。 ほとんどのデータベースシステムは、NULLを許可する列のインデックスの構築と走査において、NULLを特別に処理します。

インデックスがNULLを処理する方法：

一般的に、最も一般的なタイプであるBツリーインデックスは、NULLを許可する列の場合、リーフノードにNULL値を明示的に格納しません。 これは、NULLが他の値と比較して固有の順序を持たず、それらを格納するとインデックス構造と走査ロジックが複雑になるためです。 ただし、実際の設定は異なる場合があります。

• ほとんどのデータベース（例：MySQL、PostgreSQL、SQL Server）： デフォルトでは、単一列のBツリーインデックスは、インデックス付けされた列がNULLである行を一般的に含みません。 これは、SELECT * FROM my_table WHERE my_column IS NULLのようなクエリは、my_columnにインデックスがあっても、通常はフルテーブルスキャンにつながることを意味します。 インデックスにはNULLのエントリが含まれていないため、単にスキャンされません。
• 複合インデックス： 複合（複数列）インデックスでは、インデックスの列のいずれかが特定の行でNULLの場合、その行はインデックスに含まれないか、特殊な方法で格納される可能性があります。 たとえば、MySQLでは、複合インデックスのすべての列がNOT NULLである場合、行はインデックス付けされます。 1つでもNULLがあると、行はインデックス全体に表示されない場合があります。
• 特殊NULL処理： 一部のデータベースは、NULLをインデックス化するための特別な方法を提供します。 たとえば、PostgreSQLでは「部分インデックス」やインデックスでの式（CREATE INDEX ON my_table ((my_column IS NULL))）の使用を許可しており、NULLを強制的にインデックス化できます。 SQL Serverのフィルタリングされたインデックスも、WHERE句（CREATE INDEX ix ON MyTable (Col1) WHERE Col1 IS NOT NULL）を含めることでこれを達成できます。

例：インデックス使用への影響

delivery_dateにインデックスがあるordersテーブルを考えます。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE NOT NULL,
    delivery_date DATE -- まだ配送されていない場合はNULLになる可能性があります
);

CREATE INDEX idx_delivery_date ON orders (delivery_date);

-- クエリ1：効率的、インデックスを使用
EXPLAIN ANALYZE SELECT * FROM orders WHERE delivery_date = '2023-10-26';

-- クエリ2：DBによってはフルテーブルスキャンになる可能性があります。
-- NULLは通常インデックス化されないため。
EXPLAIN ANALYZE SELECT * FROM orders WHERE delivery_date IS NULL;

-- クエリ3：複合インデックスの例
CREATE INDEX idx_customer_delivery ON orders (customer_id, delivery_date);

-- このクエリはcustomer_idの複合インデックスを使用するかもしれませんが、その後NULLのフィルタリングに
-- 頼ることになります。DBが複合インデックスでNULLをインデックス化しない場合。
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND delivery_date IS NULL;

実際的な意味合い：

• 非効率なIS NULLクエリ： IS NULLでフィルタリングするクエリは、標準的なBツリーインデックスをバイパスし、特に大規模なテーブルではフルテーブルスキャンとパフォーマンスの低下につながることがよくあります。
• インデックスサイズとオーバーヘッド： 列に多くのNULLがあり、インデックスの一部である場合、インデックスは必要以上に大きくなるか、多くの重要なクエリがIS NULL述語を含む場合は効果が薄くなります。
• 解決策：
  • 部分/フィルタリングされたインデックス： データベースがサポートしている場合は、NULL値専用のインデックス（CREATE INDEX idx_null_delivery ON orders ((delivery_date IS NULL))）またはNULL以外の値専用のインデックス（CREATE INDEX idx_not_null_delivery ON orders (delivery_date) WHERE delivery_date IS NOT NULL）を作成します。
  • デフォルト値： 可能であれば、意味的に正しい場合は、デフォルトのNULL以外の値（例：未配送の場合は特定の日付9999-12-31、整数フィールドの場合は0）を割り当てます。 ただし、これはアプリケーションロジックを複雑にする可能性があります。
  • 別々のテーブル： NULLの割合が非常に高く、クエリパターンが異なる列の場合は、テーブルを分割するか、スパース列アプローチを使用することを検討してください。ただし、これは複雑さを増します。

NULLとCOUNT()パフォーマンス

COUNT()集計関数は、COUNT(*)かCOUNT(column_name)かによって動作が異なり、この区別はNULL値に直接関連しています。

• COUNT(*)： これは、NULL値を含むかどうかにかかわらず、結果セットのすべての行をカウントします。 これは一般的に最も効率的なCOUNT()形式です。なぜなら、多くのデータベースシステムは、主キーインデックス（常にNOT NULL）を使用するか、単にメタデータから行数を取得することで、それを最適化できるからです。
• COUNT(column_name)： この形式は、column_nameがNULL以外である行のみをカウントします。 指定された列がNULLを許可し、多くのNULL値がある場合、COUNT(column_name)は各行に対してNULL性の追加チェックを実行する必要があり、COUNT(*)よりも遅くなる可能性があります。 列がインデックス付けされており、NOT NULLの場合、COUNT(column_name)も非常に高速になる可能性があります。

例：COUNT()の動作

SELECT COUNT(*) FROM orders;          -- すべての行をカウント
SELECT COUNT(delivery_date) FROM orders; -- NULL以外のdelivery_dateを持つ行をカウント

-- 100,000件の注文があり、50,000件がNULLのdelivery_dateを持っていると仮定します。
-- COUNT(*)は迅速に100,000を返します。
-- COUNT(delivery_date)は50,000を返しますが、各行のdelivery_date列を調べるか、
-- NULLを除外するインデックスを使用する必要があるため、遅くなる可能性があります。

パフォーマンスへの影響：

• COUNT(*) 対 COUNT(column_name)： 行の総数が必要な場合は、パフォーマンス上の理由から常にCOUNT(*)を優先してください。 列のNULL以外の値を特別にカウントしたい場合は、COUNT(column_name)が適切ですが、COUNT(*)と比較したパフォーマンス特性に注意してください。
• COUNT(column_name)へのインデックスの影響： column_nameがインデックス付けされ、NOT NULLの場合、COUNT(column_name)は、オプティマイザがインデックスを使用してエントリをカウントできるため、非常に高速になる可能性があります。 ただし、column_nameがNULLを許可し、インデックスが完全に活用されない場合や、データベースがNULLを確認するために実際の行をスキャンする必要がある場合があります。

実際的な意味合い：

• COUNT(*)の戦略的な選択： 列のNULL以外の値を気にするのではなく、総行数を取得したい場合はCOUNT(*)を使用します。
• NOT NULL制約の考慮： 列が絶対にNULLであってはならない場合は、NOT NULL制約を適用します。 これにより、データの整合性が向上するだけでなく、データベースが集計カウントやインデックス使用のために、より良い仮定を行うことができます。

NULLとJOINパフォーマンス

SQLの3値論理（TRUE、FALSE、UNKNOWN）のため、NULL値はJOIN操作に特異な影響を与えます。 値とNULLを比較すると、結果は常にUNKNOWNになります。

• INNER JOIN： 両方のテーブルに一致がある場合にのみ行が返されます。 いずれかのテーブルの結合列にNULLが含まれている場合、それはNULLや非NULL値のいずれとも決して一致せず、別のNULLとも結合しません。 したがって、結合列にNULLを持つ行はINNER JOINの結果から除外されます。 これは一般的にパフォーマンスが高いため、照合する必要のある行が少なくなります。

  SELECT o.order_id, c.customer_name
  FROM orders o
  INNER JOIN customers c ON o.customer_id = c.customer_id;
  -- o.customer_idが注文に対してNULLの場合、その注文はここには表示されません。

• LEFT JOIN（およびRIGHT JOIN）： LEFT JOINは左テーブルのすべての行と、右テーブルの一致する行を返します。 左テーブルの行に一致がない場合、右テーブルの列はNULLになります。 結合列が右テーブルでNULLの場合、いずれとも一致しませんが、左テーブルの行は引き続き含まれます。 左テーブルの結合列がNULLの場合、それは事実上、その特定の値に対してINNER JOINのように動作します。これは、右テーブルの非NULL値と一致しないことを意味し、右テーブルの列にはNULL が生成されます。

  SELECT o.order_id, c.customer_name
  FROM orders o
  LEFT JOIN customers c ON o.customer_id = c.customer_id;
  -- o.customer_idが注文に対してNULLの場合、それでも表示されます。
  -- その注文のc.customer_nameはNULLになります。
  -- c.customer_idが顧客に対してNULLの場合、その顧客は注文と結合することはありません。

• FULL OUTER JOIN： いずれかのテーブルに一致がある場合にすべての行を返します。 一部の行に一致がない場合、一致がないテーブル側のNULL が発生します。 ここでも、結合列自体のNULLは一致を生成しません。

パフォーマンスへの影響：

• NULLとの等価性なし： 基本的なルールNULL = NULLはFALSE（またはSQLの3値論理では正確にはUNKNOWN）です。 これは、標準的な等価演算子を使用してNULL値で結合できないことを意味します。 結合列に多くのNULLがあり、それらを結合することを意図している（例：「両方が不明な場合に結合」）場合は、特別な処理が必要です。
• 結合述語の複雑さ： 結合条件にOR句を含めてIS NULLシナリオを処理する場合（例：ON a.col = b.col OR (a.col IS NULL AND b.col IS NULL)）、これらの述語はオプティマイザがインデックスを効率的に使用するのが一般的に非常に困難になります。 これは、ハッシュ結合や、テーブルのより大きな部分をスキャンするネストループ結合のような、最適化されていない結合プランにつながる可能性があります。
• インデックスの不使用： 結合述語a.col = b.colが頻繁にNULLを含む列に関与し、選択されたインデックスがNULLを適切に処理しない場合（前述のように）、オプティマイザは、関連するすべてのキーが含まれていないことを知っているため、インデックスを使用しないことを選択する場合があります。

例：NULLとの結合

employeesとdepartmentsテーブルを結合すると仮定します。

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT -- 未割り当ての従業員の場合はNULLになる可能性があります
);

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- 従業員 'John Doe' の emp.dept_id = NULL と 
-- 部門 'R&D' の dept_id = 1 を想像してください。
-- 別の従業員 'Jane Smith' が emp.dept_id = 1 を持っていると想像してください。

-- INNER JOIN は John Doe を除外します：
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- 結果：Jane Smith, R&D

-- LEFT JOIN は John Doe を含め、NULL の部門で表示します：
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- 結果：Jane Smith, R&D
--          John Doe, NULL

-- NULLの結合の試み（等価性では期待通りに機能しません）：
-- これは、他のテーブルに結合するNULLがある場合でも、John DoeのNULL dept_id を一致させません。
-- 例えば、「unassigned_dept_id」という列が「departments」にあり、それがNULLだった場合。
-- 次のようになります。両方の側がNULLの場合に一致することはありません：
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id OR (e.dept_id IS NULL AND d.dept_id IS NULL);
-- この `OR` 条件は、しばしばフルスキャン/高コストな結合を強制します。

実際的な意味合い：

• JOINでのNULLの動作の理解： NULL = NULLは真ではないことを明確に認識してください。 これはしばしば開発者を困惑させます。
• 複雑なJOIN述語の回避： 可能であれば、IS NULLロジックを含むJOIN条件を避けるために、データの構造化や前処理を行います。 NULLを含むOR条件は、JOINパフォーマンスとインデックスの使用を著しく損なう可能性があります。
• データのサニタイズ： 結合列のNULLが実際に未知または適用不能な状態を表す場合、結合前にそれらの行をフィルタリングするか、LEFT JOINで明示的に処理する方が良いかもしれません。 NULLが特定の意味（例：「デフォルト部門」）を暗示する場合は、結合を容易にするために、明確に定義されたNULL以外の値に置き換えることを検討してください。

結論

データベースにおけるNULLは、単なる空のプレースホルダー以上のものです。 それはSQLのデータモデルの基本的な側面であり、深刻なパフォーマンスへの影響があります。 インデックスにとって、NULLはしばしば除外につながり、IS NULLクエリでフルテーブルスキャンを強制します。 COUNT()では、NULLの除外により、COUNT(column_name)よりもCOUNT(*)が効率で優位に立ちます。 JOIN操作では、NULLは、標準的な等価比較を無視するため、結合述語を複雑にし、インデックスの使用を妨げる可能性があるため、ユニークな課題を提示します。 これらの動作を理解することで、開発者はより効率的なスキーマを設計し、よりスマートなクエリを作成し、最終的にはよりパフォーマンスの高いデータベースアプリケーションを構築できます。 NULLを、それらがなり得るサイレントキラーとして扱うことは、データベース最適化の鍵となります。