MySQL クエリを最適化して効率を最大化する-mysql チュートリアル-php.cn

MySQL クエリを最適化して効率を最大化する

Mary-Kate Olsen

リリース： 2024-12-30 04:49:21

オリジナル

306 人が閲覧しました

Optimizing Your MySQL Queries for Maximum Efficiency

MySQL クエリの最適化は、パフォーマンスを向上させ、データベースが大量のデータを効率的に処理できるようにするために不可欠です。 MySQL クエリを最適化するために使用できるテクニックの概要を以下に示します:

1. インデックスを賢く使用する

インデックスを使用すると、データの取得を大幅に高速化できますが、書き込み (INSERT、UPDATE、DELETE) が遅くなる可能性があります。インデックスの使用を最適化する方法は次のとおりです:

WHERE 句で使用される列にインデックスを作成します: これにより、検索が高速化されます。
複数の列に複合インデックスを使用する: 列の組み合わせ (例: WHERE column1 = ? AND column2 = ?) を頻繁にクエリする場合は、両方に複合インデックスを作成することを検討してください。
過剰なインデックス作成を避ける: クエリのパフォーマンスを向上させるインデックスのみを作成します。余分なインデックスにより、挿入および更新操作が遅くなります。

2. クエリ構造の最適化

クエリを書き換えて効率を高めます:

SELECT *: は避けてください すべての列を選択するのではなく、常に必要な列を指定してください。
データ取得の制限: 必要な行数のみを返すには、LIMIT を使用します。
サブクエリを避ける: 多くの場合、結合はサブクエリより高速です。サブクエリを JOIN ステートメントにリファクタリングしてみてください。
EXPLAIN を使用してクエリを分析する: MySQL の EXPLAIN コマンドは、クエリがどのように実行されるかを示し、ボトルネック (フルテーブルスキャンや不要なソートなど) を特定するのに役立ちます。

3. サブクエリの代わりに JOIN を使用します

可能な場合は、サブクエリを JOIN ステートメントにリファクタリングします。 JOIN はサブクエリより効果的に最適化できるため、通常、これによりパフォーマンスが向上します。

-- Subquery (less efficient)
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

-- Optimized with JOIN (more efficient)
SELECT employees.name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE departments.location = 'New York';

ログイン後にコピー

4. 適切なデータ型を使用する

列に適切なデータ型を選択することは、パフォーマンスにとって重要です。より小さいデータ型を使用すると、ストレージ要件が大幅に削減され、クエリ速度が向上します。

必要な場合を除き、BIGINT のような大きな型ではなく、整数には INT を使用します。
短い文字列を格納する列には、TEXT の代わりに VARCHAR を使用します。
日付/時刻情報を保存するには、文字列の代わりに DATE 型と DATETIME 型を使用します。

5. 「いいね！」の使用を制限する

LIKE 演算子は、特に先頭にワイルドカード (�c) を使用すると遅くなる可能性があります。可能であれば、より具体的なフィルター (完全一致や IN など) を使用してください。

�c または abc% は、MySQL にテーブル全体を強制的にスキャンさせるため、避けてください。
全文検索は、高度なテキスト検索が必要な場合、特に部分的な単語や語句で検索を実行する必要がある場合に使用します。

6. 不必要に DISTINCT を使用しないようにします

DISTINCT キーワードは、特に大規模なデータセットの場合、クエリの速度を低下させる可能性があります。本当に重複を排除する必要がある場合にのみ使用し、間違った列や不要なフィールドに適用されないようにしてください。

7. ORDER BY 句を最適化する

大規模な結果セットの並べ替えにはコストがかかる場合があります。最適化するには:

ORDER BY で使用される列にインデックスを使用する: 並べ替える列にインデックスが付けられていることを確認してください。
結果の制限: LIMIT を適用して、並べ替える必要がある行の数を減らします。
複数の列を使用した ORDER BY を検討してください: 複数の列で並べ替える場合は、その組み合わせに適切なインデックスが付けられていることを確認してください。

8. クエリキャッシュを使用する

MySQL は、同じクエリが繰り返し再実行されることを避けるために、クエリ結果をキャッシュできます。これにより、頻繁に実行されるクエリ、特に読み取り負荷の高いワークロードのパフォーマンスが向上します。

クエリキャッシュを有効にする: まだ有効になっていない場合は、query_cache_size 構成を使用してキャッシュを有効にできます。
必要に応じてキャッシュをクリア: データが頻繁に変更される場合は、最新のデータを反映するためにキャッシュがクリアされていることを確認してください。

9. バッチ挿入と更新

多数の行を 1 つずつ挿入または更新すると、非常に時間がかかることがあります。一括操作を使用して挿入を高速化します:

-- Subquery (less efficient)
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

-- Optimized with JOIN (more efficient)
SELECT employees.name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE departments.location = 'New York';

ログイン後にコピー

これにより、複数の単一行挿入操作に関連するオーバーヘッドが削減されます。

10. サーバーリソースの監視と最適化

MySQL のパフォーマンスは、クエリだけでなくサーバーリソースの制限によってもボトルネックになる可能性があります。次のことを行う必要があります:

適切なハードウェアを使用します: データベースサーバーに十分な CPU、メモリ、ディスク I/O 容量があることを確認してください。
MySQL 構成の調整: サーバーのリソースとワークロードに基づいて MySQL の構成設定 (innodb_buffer_pool_size、query_cache_size、max_connections など) を調整します。
接続処理の最適化: 同時実行性の高いワークロードがある場合は、多くの接続を効率的に処理できるようにサーバーが最適化されていることを確認してください。

11. 分析と最適化を使用する

データベーステーブルを定期的に分析および最適化して、インデックスと統計が最新であることを確認します。

-- Subquery (less efficient)
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

-- Optimized with JOIN (more efficient)
SELECT employees.name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE departments.location = 'New York';

ログイン後にコピー