SQLチューニングのテクニック

この記事では、SQLクエリの速度と効率を向上させるためのいくつかの基本的な戦略について紹介します。

インデックスの活用

インデックスはSQLにおいて重要な概念であり、データベースクエリのパフォーマンスを大幅に向上させます。

インデックスを作成してSQLのパフォーマンスを向上させる

WHERE句で使用されるカラムにインデックスを作成することで、フルテーブルスキャンがインデックススキャンに変わり、クエリの速度が向上します。ただし、インデックスは比較的小さなデータのサブセットをフィルタリングする場合に有益です。

-- 最適化前
SELECT * FROM books WHERE author = "Hemingway";

-- 最適化後
CREATE INDEX idx_books_author ON books(author);
SELECT * FROM books WHERE author = "Hemingway";

複合インデックスの作成

複合インデックスは複数のカラムを含みます。WHERE句で複数のカラムをフィルタリングする場合に特に効果的です。ただし、インデックスとWHERE句でのカラムの順序には注意が必要です。

-- 最適化前
SELECT * FROM students WHERE firstName = "John" AND lastName = "Doe";

-- 最適化後
CREATE INDEX idx_students_first_last_name ON students(firstName, lastName);
SELECT * FROM students WHERE firstName = "John" AND lastName = "Doe";

複合インデックスではなく各カラムのためにインデックスを作成する

特定の場合には、単一の複合インデックスではなく、各カラムに対して個別のインデックスを作成する方が有益です。個々のカラムで頻繁にフィルタリングを行う場合に有効です。

-- 最適化前
SELECT * FROM orders WHERE customerID = 123 OR productID = 456;

-- 最適化後
CREATE INDEX idx_orders_customerID ON orders(customerID);
CREATE INDEX idx_orders_productID ON orders(productID);
SELECT * FROM orders WHERE customerID = 123 OR productID = 456;

ORDER BYおよびGROUP BYで使用されるカラムのためにインデックスを作成する

ORDER BYおよびGROUP BY句で使用されるカラムは、インデックスを活用することでこれらの操作を高速化できます。複数のカラムが関与する場合には、複合インデックスが作成される場合があります。

-- 最適化前
SELECT category, COUNT(*) FROM products GROUP BY category;

-- 最適化後
CREATE INDEX idx_products_category ON products(category);
SELECT category, COUNT(*) FROM products GROUP BY category;

-- 最適化前
SELECT * FROM customers ORDER BY lastName, firstName;

-- 最適化後
CREATE INDEX idx_customers_last_first_name ON customers(lastName, firstName);
SELECT * FROM customers ORDER BY lastName, firstName;

無意味なソートの回避

不要なソート操作を含めることでデータベースクエリのパフォーマンスに重大な影響を与えることがあります。これらの操作には、ORDER BYおよびGROUP BYコマンドが含まれますが、これらは処理時間を増大させ、全体的なパフォーマンスに影響を与える可能性があります。

不要なORDER BYおよびGROUP BYの回避

ORDER BYおよびGROUP BYの操作は、多くの処理時間を消費します。したがって、必要な場合にのみ使用するべきです。さらに、使用する場合には、データセットができるだけフィルタリングされた後に適用するべきです。

-- 最適化前
SELECT * FROM users ORDER BY name;

-- 最適化後
SELECT * FROM users WHERE active = 1 ORDER BY name;

最適化されたバージョンでは、非アクティブなユーザーをフィルタリングした後にORDER BY句が適用されるため、ソートするレコードの数が減少します。

インデックスを使用してMAX、MINの計算を高速化する

MINやMAXなどの計算は、インデックスを使用することで最適化することができます。これらの操作に関与するカラムにインデックスが適用されると、データベースは必要な値を素早く見つけることができます。

-- 最適化前
SELECT MAX(score), MIN(score) FROM students;

-- 最適化後
CREATE INDEX idx_students_score ON students(score);
SELECT MAX(score), MIN(score) FROM students;

DISTINCTの代わりにEXISTSを使用する

重複する行を除外するために使用されるDISTINCT句は、データのソートが必要なため、負荷がかかる操作です。ソートの必要なしに一意の値のみが必要な場合は、代わりにEXISTSを使用することができます。

-- 最適化前
SELECT DISTINCT c.course_id FROM courses AS c INNER JOIN enrollments AS e ON c.course_id = e.course_id;

-- 最適化後
SELECT c.course_id FROM courses AS c WHERE EXISTS (SELECT 1 FROM enrollments AS e WHERE e.course_id = c.course_id);

UNIONの代わりにUNION ALLを使用する

UNIONの操作は、ソートや重複レコードの除去が含まれるため、リソースを多く消費します。重複のない行を結合する場合は、代わりにUNION ALLを使用することができます。UNION ALLはソートや重複の除去を行わないため、効率的です。

-- 最適化前
SELECT name FROM employees UNION SELECT name FROM managers;

-- 最適化後
SELECT name FROM employees UNION ALL SELECT name FROM managers;

繰り返し処理の削減

SQLチューニングでは、可能な限り繰り返し処理を最小化することが重要です。これは、テーブルアクセスの回数を減らし、SELECT内のサブクエリよりもJOINを好むこと、不要な繰り返しフィルタリングを避けることで実現できます。

テーブルアクセスの回数を減らす

SQLチューニングの基本的な部分は、テーブルアクセスの頻度を減らすことです。テーブルの内容を単一のインスタンスで確認できる場合には、その単一の機会でチェックを完了することが最適です。

SELECT内のサブクエリの代わりにJOINを使用する

SELECT文の対象列でサブクエリを使用する場合、サブクエリのSQLは各行ごとに実行されるため、処理時間が増加することがあります。代わりに、単一の関連付けに対してJOINを使用することで、同じ結果をより少ない処理で得ることができます。

次のコードでは、各従業員行ごとにサブクエリを実行して部門の名前を取得しています。

SELECT
emp.*, (SELECT dp.name FROM departments AS dp WHERE dp.id = emp.department_id)
FROM employees AS emp;

修正されたコードでは、LEFT JOINを使用して複数のサブクエリの実行を回避し、同じ結果を得ることができます。

SELECT
emp.*, dp.name
FROM employees AS emp LEFT JOIN departments AS dp
ON emp.department_id = dp.id;

不要な繰り返しフィルタリングを避ける

WHERE句も処理時間に寄与する要因です。使用頻度を減らすことで不要な処理を排除することができます。

以下の例を見てみます。ここでは、WHERE句で2回フィルタリングを行い、データを関連付けています。

SELECT o1.*, o2.daily_summary
FROM orders AS o1
LEFT JOIN(
   SELECT order_date, SUM(order_amount * order_price) AS "daily_summary" FROM orders
   WHERE order_date BETWEEN "2023-01-01" AND "2023-01-31"
   GROUP BY order_date
) AS o2
ON o1.order_date = o2.order_date
WHERE o1.order_date BETWEEN "2023-01-01" AND "2023-01-31";

ウィンドウ関数を使用することで、フィルタリングと集計を1回の操作にまとめることができます。

SELECT
*, SUM(o1.order_amount * o1.order_price) OVER(PARTITION BY o1.order_date) AS daily_summary
FROM orders AS o1
WHERE o1.order_date BETWEEN "2023-06-01" AND "2023-06-30";

SQL実行回数の削減

SQLのパフォーマンスを最適化する際には、SQL実行回数を減らすことが効果的です。SQLの各実行はアプリケーションサーバーとDBサーバー間の通信を伴い、時間がかかるためです。以下では、SQL実行回数を減らすためのいくつかのテクニックを探ってみます。

INSERTコマンドの回数を減らす

複数のレコードをテーブルに挿入する場合、マルチインサートを使用することを検討してください。これにより、1つのSQL実行で複数のレコードを挿入することができます。

例えば、projectsテーブルに2つのレコードを挿入する場合、通常は次のように行います。

INSERT INTO projects(name, start_date, end_date) VALUES ('Database','2023-02-01','2023-08-31');
INSERT INTO projects(name, start_date, end_date) VALUES ('WebApp','2023-04-05','2023-12-23');

しかし、これら2つの別々の挿入操作を次のように結合することができます。

INSERT INTO
projects(name, start_date, end_date)
VALUES
('Database','2023-02-01','2023-08-31'),  ('WebApp','2023-04-05','2023-12-23');

バルクINSERTの前にインデックスを削除し、後で追加する

インデックスを持つテーブルにレコードを挿入する場合、システムはインデックスも更新する必要があります。これは時間がかかる操作です。数万件以上のレコードを挿入する場合は、挿入操作前にインデックスを一時的に削除し、挿入後に再作成することを検討すると効果的です。

クエリの実行にはLIMITを使用する

テーブルの一部のレコードをチェックする場合は、LIMIT句を使用して処理時間を短縮することができます。ただし、COUNT(*)などの集計関数にLIMIT句を追加しても処理時間は短縮されませんので注意してください。

全体のレコード削除にはDELETEの代わりにTRUNCATEを使用する

テーブルから全てのレコードを削除する場合、DELETE操作は各個別の行の削除をログに記録するため、遅くなることがあります。削除のログを保持する必要がなく、テーブルを完全にクリアする場合は、一般的にはTRUNCATEコマンドを使用することを検討してください。これは通常、はるかに高速です。