MySQL SQL文を最適化するためのヒント

十分に最適化されていない、またはパフォーマンスが極端に低い SQL ステートメントに直面した場合、通常は、クエリ結果セットが元のものと同じになるように SQL ステートメントをリファクタリングし、SQL パフォーマンスが向上することを期待します。 SQL をリファクタリングする場合、一般的には参照できる特定の方法とテクニックがあります。この記事では、これらのテクニックと方法を使用して SQL をリファクタリングする方法を紹介します。

1. SQLの分解

複雑な SQL の場合、最初に考えるのは、同じビジネス処理結果を得るために、複雑な SQL を複数の単純な SQL に分解する必要があるかどうかです。

これまで、データベース層が可能な限り多くの作業を完了する必要性は常に強調されてきました。そのため、一部の古い製品やプロジェクトで非常に複雑で非常に長い SQL ステートメントを頻繁に目にする理由を理解するのは難しくありません。以前は、これを行うロジックには複数のインタラクションが必要であると考えられていましたが、これはネットワーク帯域幅、プログラムとデータベース間のネットワーク通信などの点で非常にコストのかかるものでした。今では、帯域幅や遅延の面でも、ネットワーク速度は以前よりもはるかに速くなり、複数のやり取りでも大きな問題は発生しません。汎用サーバーでも1秒あたり10万件以上のクエリを実行できるため、複数の小さなクエリを実行しても大きな問題にはなりません。

複雑な SQL ステートメントを分解すると、非常に複雑な SQL ステートメントを処理するときにパフォーマンスが大幅に向上します。したがって、非常に複雑な SQL ステートメントに直面し、パフォーマンスの問題が発生する場合は、最適化のために小さなクエリに分割することをお勧めします。

ただし、アプリケーションを設計する際に、クエリが十分でパフォーマンス上の問題が発生しない場合は、少し複雑な SQL で完了できます。複数の小さなクエリに厳密に分割するのは賢明ではありません。

今日の多くの高性能アプリケーションシステムでは、複雑なビジネスのクエリ要件を満たすために、単一テーブル操作を使用し、アプリケーション内で単一テーブルのクエリ結果を関連付けることが強く推奨されています。 1 つの SQL ステートメントで同じ作業を実行できる場合、なぜ別々の SQL ステートメントを記述するのでしょうか。また、アプリケーションで SQL クエリを複数回実行し、結果セットを関連付けるのはなぜでしょうか。なぜこれを行う必要があるのでしょうか。

一見すると、これは複雑でメリットがないように見えます。単一のクエリではなく、複数のクエリになります。実際、この分解には次のような利点があります。

キャッシュをより効率的にします。アプリケーションでは、単一テーブルのクエリ結果に対応する結果オブジェクトを簡単にキャッシュできるため、後でいつでも結果オブジェクトから直接データを取得できます。
クエリを分割した後、単一のクエリを実行すると、テーブルロックの競合を減らすことができます。
アプリケーション層で関連付けを行うことで、データベースを分割し、高いパフォーマンスとスケーラビリティを実現しやすくなります。
単一テーブルのクエリの効率は、複数テーブルの複雑なクエリよりも高くなります。
冗長レコードのクエリを減らします。アプリケーション層での関連付けは、アプリケーションが特定のレコードを 1 回だけクエリすればよいことを意味しますが、データベースで関連付けられたクエリを実行する場合は、一部のデータレコードに繰り返しアクセスする必要がある場合があります。この観点から、このような再構築により、ネットワークとメモリの消費も削減される可能性があります。

2. クエリのセグメンテーション

場合によっては、大規模なクエリ、つまり結果セットが大きいクエリの場合、「分割統治」の考え方を採用し、大規模なクエリを小さなクエリに分割する必要があります。各クエリはまったく同じ機能を持ちますが、小さな部分のみを完了し、毎回クエリ結果の小さな部分のみを返します。簡単に言えば、WHERE 条件のフィルタリング範囲を分割し、毎回データの一部のみをクエリすることであり、ページングクエリに似ています。

そうすることで、SQL クエリ自体と上位レベルのサービスの両方に対して、ごくわずかなオーバーヘッドしか発生しなくなります。最も典型的なケースはページングクエリであり、MyBatis などのさまざまなフレームワークで十分にサポートされています。実際の使用時に少し注意を払うだけで回避できます。

3. 実行計画

実行プランで EXPLAIN キーワードを使用すると、MySQL が SQL ステートメントをどのように実行するかを知ることができ、クエリステートメントまたはテーブル構造のパフォーマンスのボトルネックを分析するのに役立ちます。 EXPLAIN のクエリ結果では、インデックスの主キーがどのように使用されているか、データテーブルがどのように検索またはソートされているかなどもわかります。

構文の形式は次のとおりです。

EXPLAIN SELECT ステートメント;

実行プランの結果は、インデックスの追加、インデックス順序の調整、特定の関数の使用回避など、SQL ステートメントをさらに再構築するためのガイドとなります。

実行計画については、以降の章で詳しく説明します。

IV. 原則の遵守

日常的に SQL を記述するときに、良い習慣を身につけて注意を払うと、SQL パフォーマンスの問題を大幅に回避できます。要約すると次のようになります。

各テーブルに必ず ID 主キーを設定します。
SELECT * の使用は避けてください。
検索フィールドにインデックスを付けます。
テーブルを結合するときは、対応するタイプの列を使用してインデックスを作成します。
可能な場合は常に NOT NULL を使用してください。
列が小さいほど速くなります。
1 行のデータのみ必要な場合は、LIMIT 1 を使用します。
演算子の最適化では、テーブル全体のスキャンを回避するために、インデックス作成に役立たない演算子を使用しないように努めます。

1) in と not in は注意して使用してください。in の代わりに between を使用し、not in の代わりに not exists を使用するようにしてください。
2) is nullとis not nullを注意して使用する
3) 可能であれば、!= または <> 演算子の使用を避けてください。そうしないと、エンジンはインデックスの使用を中止し、テーブル全体のスキャンを実行します。

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

多数の同一クエリが複数回実行されると、クエリ結果はキャッシュに格納されるため、後続の同一クエリは操作なしでキャッシュされた結果に直接アクセスできます。

MySQL クエリキャッシュには、クエリによって返された完全な結果が保存されます。クエリがキャッシュにヒットすると、MySQL は解析、最適化、実行の切り捨てをスキップして結果を返します。

これはクエリパフォーマンスを向上させる最も効果的な方法の 1 つであり、MySQL エンジンによって処理されます。通常、MySQL はデフォルトでクエリキャッシュを有効にしないため、手動で有効にする必要があります。

クエリキャッシュはアプリケーションに対して完全に透過的です。アプリケーションは、MySQL がクエリを通じて結果を返すのか、実際の実行を通じて結果を返すのかを気にする必要はありません。実際、これら 2 つの方法の結果はまったく同じです。つまり、キャッシュをクエリするために必要な構文はありません。

現在の汎用サーバーがより強力になるにつれて、クエリキャッシュがサーバーのスケーラビリティに影響を与える要因であることがわかります。サーバー全体のリソース競合の単一ポイントになる可能性があり、マルチコアサーバーでサーバーのデッドロックを引き起こす可能性もあります。したがって、ほとんどの場合、クエリキャッシュはデフォルトでオフにする必要があります。クエリキャッシュが非常に便利な場合は、数十メガバイトの小さなキャッシュスペースを構成できます。（選択する際にはトレードオフが必要です）

クエリキャッシュ構成には次のパラメータを使用できます。