クエリはデータベース テクノロジで最も一般的に使用される操作です。クエリ操作のプロセスは比較的単純です。まず、クライアントからクエリ SQL ステートメントが発行され、クライアントから送信された SQL ステートメントを受信した後、データベース サーバーが SQL ステートメントを実行し、クエリ結果をクライアントに返します。プロセスは非常に単純ですが、クエリ方法とデータベース設定の違いはクエリのパフォーマンスに大きな影響を与えます。
したがって、この記事では、MySQL で一般的に使用されるクエリ最適化手法について説明します。議論には、クエリ バッファリングによるクエリ速度の向上、MySQL のクエリの自動最適化、到達不能なクエリの検出、およびパフォーマンスを向上させるためのさまざまなクエリ オプションの使用が含まれます。
1. クエリバッファリングによるクエリ速度の向上
一般に、SQL ステートメントを使用してクエリを実行すると、データベース サーバーはクライアントから SQL を受け取るたびにこの SQL ステートメントを実行します。ただし、まったく同じ SQL ステートメントを一定の間隔内 (たとえば 1 分以内) に受信した場合は、同じように実行されます。これによりデータのリアルタイム性が保証されますが、ほとんどの場合、データは完全なリアルタイム性を必要としないため、ある程度の遅延が発生する可能性があります。この場合、まったく同じ SQL を短期間に実行してもメリットはありません。
幸いなことに、MySQL にはクエリ バッファリング機能が備わっています (クエリ バッファリングは MySQL 4.0.1 以降でのみ使用できます)。クエリ キャッシュにより、クエリのパフォーマンスをある程度向上させることができます。
MySQL インストール ディレクトリの my.ini ファイルを通じてクエリ バッファを設定できます。設定も非常に簡単で、query_cache_type を 1 に設定するだけです。この属性を設定した後、SELECT ステートメントを実行する前に、MySQL は同じ SELECT ステートメントが実行されたかどうかをバッファ内でチェックし、実行結果が期限切れになっていない場合は、クエリ結果がクライアントに直接返されます。ただし、SQL ステートメントを記述する場合、MySQL のクエリ バッファでは大文字と小文字が区別されることに注意してください。次の 2 つの SELECT ステートメントは次のとおりです。 SELECT * from TABLE1
テーブル 1 から * を選択
上記の 2 つの SQL ステートメントは、クエリ バッファリングのためのまったく異なる SELECT です。さらに、クエリ バッファはスペースを自動的に処理しないため、SQL ステートメントを作成するときは、スペース、特に SQL の先頭と末尾のスペースの使用を減らすように努める必要があります (クエリ キャッシュが自動的にスペースをインターセプトしないため)。始まりと終わり）。
クエリ バッファを設定しないとパフォーマンスが低下する場合がありますが、リアルタイムでデータをクエリする必要がある SQL ステートメントや、頻繁に使用されない (おそらく 1 日に 1 回か 2 回実行される) SQL ステートメントがいくつかあります。これにはバッファリングをオフにする必要があります。もちろん、query_cache_type の値を設定することでクエリ キャッシュをオフにすることもできますが、これによりクエリ キャッシュは永続的にオフになります。 MySQL 5.0 には、クエリ キャッシュを一時的にオフにするメソッドが用意されています: SELECT SQL_NO_CACHE field1, field2 FROM TABLE1
上記の SQL ステートメントは SQL_NO_CACHE を使用しているため、この SQL ステートメントが以前に実行されたかどうかに関係なく、サーバーはバッファー内を検索せず、毎回実行します。
my.ini の query_cache_type を 2 に設定して、SQL_CACHE が使用された後にのみクエリ キャッシュが使用されるようにすることもできます。 SELECT SQL_CALHE * FROM TABLE1
2. MySQL のクエリの自動最適化
インデックスはデータベースにとって非常に重要です。インデックスを使用すると、クエリ時のパフォーマンスを向上させることができます。ただし、インデックスを使用するとパフォーマンスが低下する場合があります。次の SALES テーブルを見てみましょう: CREATE TABLE SALES
(
ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT、
NAME VARCHAR(100) が NULL ではありません。
変動価格が NULL ではありません。
SALE_COUNT INT NOT NULL、
SALE_DATE 日付が NULL ではありません、
主キー(ID)、
インデックス (名前)、
インデックス (SALE_DATE)
)
このテーブルには何百万ものデータが保存されており、製品番号 1000 の 2004 年と 2005 年の平均価格をクエリしたいとします。次の SQL ステートメントを作成できます: SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 AND SALE_DATE BETWEEN '2004-01-01' と '2005-12-31';
この製品の数量が非常に多い場合は、SALES テーブルのレコードのほぼ 50% 以上を占めます。次に、SALE_DATE フィールドのインデックスを使用して平均を計算すると、少し時間がかかります。インデックスを使用する場合は、インデックスをソートする必要があるためです。条件に該当するレコードが非常に多い場合（テーブル全体のレコード数の50％以上を占める場合など）は速度が低下するため、テーブル全体をスキャンした方がよいでしょう。したがって、MySQL はテーブル全体における条件を満たすデータの割合に基づいて、クエリにインデックスを使用するかどうかを自動的に決定します。
MySQL の場合、テーブル全体のレコードに対する上記のクエリ結果の割合が約 30% の場合、インデックスは使用されません。この割合は、MySQL 開発者が経験に基づいて導き出したものです。ただし、実際のスケール値は使用するデータベース エンジンによって異なります。
3. インデックスベースのソート
MySQL の弱点の 1 つはソートです。 MySQL は 1 秒間に約 15,000 レコードをクエリできますが、MySQL はクエリ時に最大 1 つのインデックスしか使用できません。したがって、WHERE 条件がすでにインデックスを占有している場合、そのインデックスは並べ替えに使用されず、クエリの速度が大幅に低下します。次の SQL ステートメントを見てみましょう。 SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
NAME フィールドのインデックスは上記の SQL の WHERE 句で使用されているため、SALE_DATE をソートするときにインデックスは使用されなくなります。この問題を解決するには、SALES テーブルに複合インデックスを作成します。 ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
このようにして、上記の SELECT ステートメントを使用してクエリを実行するときの速度が大幅に向上します。ただし、この方法を使用する場合は、WHERE 句に並べ替えフィールドがないことを確認してください。そうしないと、並べ替えは高速になりますが、SALE_DATE フィールドに別のインデックスが存在しません。 , そのため、クエリの速度が低下します。
4. 到達不能なクエリの検出
SQL ステートメントを実行すると、必ず false でなければならない条件がいくつか発生します。いわゆる「must-false」条件とは、テーブル内のデータがどのように変化しても、この条件は false であるということです。 WHERE 値 < 100 AND 値 > 200 など。 100 未満と 200 を超える数値を見つけることはできません。
このようなクエリ条件が発生した場合、そのような SQL ステートメントを実行する必要はありません。幸いなことに、MySQL はこの状況を自動的に検出できます。たとえば、次の SQL ステートメントを見てみましょう: SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”
上記のクエリ ステートメントは、NAME が name1 と name2 の両方に等しいレコードを検索します。明らかに、これは到達不可能なクエリであり、WHERE 条件は false である必要があります。 MySQL は SQL ステートメントを実行する前に、まず WHERE 条件が到達不能なクエリであるかどうかを分析します。到達不能なクエリである場合、その SQL ステートメントは実行されません。これを確認するには。まず、EXPLAIN を使用して次の SQL をテストします。 EXPLAIN SELECT * FROM SALES WHERE NAME = "name1"
上記のクエリは通常のクエリです。EXPLAIN によって返される実行情報データのテーブル項目が SALES であることがわかります。これは、MySQL が SALES を実行していることを示しています。次のステートメントをもう一度見てください: EXPLAIN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”
テーブル項目が空であることがわかります。これは、MySQL が SALES テーブルに対して操作を行っていないことを意味します。
5. さまざまなクエリ選択を使用してパフォーマンスを向上させる
SELECT ステートメントの通常の使用に加えて、MySQL はクエリのパフォーマンスを向上させる多くのオプションも提供します。前述したように、クエリ バッファリングの制御に使用される SQL_NO_CACHE と SQL_CACHE は、オプションの 2 つです。このセクションでは、一般的に使用されるクエリ オプションをいくつか紹介します。
1. STRAIGHT_JOIN: 接続順序を強制する
クエリのために 2 つ以上のテーブルを接続する場合、MySQL がどのテーブルを最初に接続し、どのテーブルを最後に接続するかを気にする必要はありません。これらはすべて、一連の内部計算と評価を通じて MySQL によって決定される接続シーケンスによって決定されます。次の SQL ステートメントでは、TABLE1 と TABLE2 は必ずしも相互に接続されているわけではありません: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE …
開発者が接続の順序に手動で介入する必要がある場合は、次の SQL ステートメントのような STRAIGHT_JOIN キーワードを使用する必要があります: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
上記の SQL ステートメントからわかるように、STRAIGHT_JOIN は MySQL に TABLE1 と TABLE2 の順序でテーブルを強制的に結合させるために使用されます。 MySQL が推奨する順序よりも独自の順序で結合する方が効率的であると思われる場合は、STRAIGHT_JOIN を使用して接続順序を決定できます。
2. インデックスの使用に介入してパフォーマンスを向上させる
インデックスの使用については上で説明しました。通常の状況では、MySQL はインデックスを使用するかどうか、およびクエリ時にどのインデックスを使用するかを決定します。ただし、特殊なケースでは、MySQL で 1 つまたは少数のインデックスのみを使用したい場合や、特定のインデックスを使用したくない場合があります。これには、MySQL のいくつかのクエリ オプションを使用してインデックスを制御する必要があります。
インデックスの使用範囲を制限する
場合によっては、データ テーブルに多数のインデックスを作成することがあります。MySQL がインデックスを選択するときは、これらのインデックスがすべて考慮されます。ただし、場合によっては、MySQL がすべてのインデックスではなく少数のインデックスのみを考慮するようにしたい場合があります。これには、クエリ ステートメントの設定に USE INDEX を使用する必要があります。 SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …
上記の SQL ステートメントからわかるように、TABLE1 にいくつのインデックスが設定されていても、MySQL はインデックスを選択するときに FIELD1 と FIELD2 に設定されたインデックスのみを考慮します。
使用しないインデックスの範囲を制限する
考慮すべきインデックスが多く、未使用のインデックスがほとんどない場合は、IGNORE INDEX を使用して逆選択を行うことができます。上記の例では、考慮されるインデックスが選択されますが、IGNORE INDEX を使用すると、考慮されないインデックスが選択されます。 SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
上記の SQL ステートメントでは、TABLE1 テーブルの FIELD1 と FIELD2 のインデックスのみが使用されません。
インデックスの使用を強制する
上記の 2 つの例は両方とも MySQL に選択肢を提供します。つまり、MySQL はこれらのインデックスを使用する必要がありません。場合によっては、MySQL が特定のインデックスを使用する必要があることを望みます (MySQL はクエリ時に 1 つのインデックスしか使用できないため、MySQL に強制的に使用できるのは 1 つのインデックスのみです)。この機能を完了するには、FORCE INDEX を使用する必要があります。 SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
上記の SQL ステートメントは、FIELD1 に構築されたインデックスのみを使用し、他のフィールドのインデックスは使用しません。
3. 一時テーブルを使用してクエリのパフォーマンスを向上させる
クエリの結果セットに大量のデータがある場合、SQL_BUFFER_RESULT オプションを使用して結果セットを一時テーブルに強制的に入れることができるため、MySQL テーブルのロックをすぐに解放できます (他の SQL ステートメントでこれらのクエリを実行できるようになります)。 records)) )、大規模なレコードセットをクライアントに長期間提供できます。 SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
SQL_BUFFER_RESULT オプションと同様に、SQL_BIG_RESULT があります。このオプションは通常、グループ化または DISTINCT キーワードに使用され、必要に応じてクエリ結果が一時テーブルに配置されるか、一時テーブル内でソートされることを MySQL に通知します。 SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1
6. 結論
プログラミングには「20/80 原則」もあります。つまり、コードの 20% に時間の 80% がかかります。データベースアプリケーション開発も同様です。データベース アプリケーションの最適化では、SQL の実行効率が重視されます。データ クエリの最適化の焦点は、データベース サーバーがディスクから読み取るデータを減らし、ページを非順次ではなく順次に読み取るようにすることです。