MySQLクエリ文を書き換える3つの戦略

問題のあるクエリを最適化する場合、クエリ結果を取得する方法を変更する必要がありますが、これは MySQL から同じ結果セットを取得することを意味するものではありません。場合によっては、クエリを、同じ結果が得られるがパフォーマンスが向上する形式に変換できることがあります。ただし、開発効率を向上させるために、異なる結果を得るためにクエリを書き換えることも検討する必要があります。同じ効果を得るためにアプリケーションコードを変更することもできます。この記事では、クエリを書き換える方法に関するヒントをいくつか紹介します。

複雑なクエリとステップバイステップのクエリ

クエリ設計における重要な質問は、複雑なクエリを複数の単純なクエリに分解する方がよいかどうかです。従来のデータベース設計では、できるだけ少ないクエリで大量の作業を解決することに重点が置かれています。昔は、このほうがよかったでしょう。これは、過去にネットワーク通信のコストが高く、クエリパーサーとオプティマイザーに負荷がかかっていたためです。

ただし、MySQL は接続の確立と切断を非常に効率的に処理し、単純なクエリにすばやく応答するため、このアドバイスは MySQL にはあまり当てはまりません。今日のネットワーク速度も以前に比べて大幅に向上しています。サーバーのバージョンに応じて、MySQL は通常のマシン上で 1 秒あたり 100,000 件を超える単純なクエリを実行し、ギガビットネットワーク上で 1 秒あたり 2,000 件のクエリ通信を完了できます。したがって、クエリの分散は以前ほど悪くはありません。

1 秒あたりに走査されるデータ行の数と比較すると、接続応答はまだ比較的遅いです。メモリデータでは、この時間はミリ秒に達します。もちろん、できるだけ多くのクエリを使用することは依然として良い選択です。ただし、複雑なクエリを複数の単純なクエリに分割することでパフォーマンスを向上できる場合もあります。次にいくつか例を挙げます。

クエリを使いすぎるのは、プログラミングにおいてよくある間違いです。たとえば、一部のアプリケーションでは、10 行のデータを取得するために 10 個の個別のクエリを実行します (ループを使用して 1 行ずつ取得します)。これは、10 行のデータを取得する単一のクエリで実行できます。したがって、毎回クエリを分割することを推奨するわけではなく、実際の状況によって異なります。

クエリステートメントを分割する

別の方法としては、クエリを分割して再構成する方法があります。大きなデータクエリを小さなクエリに分割して、毎回影響を受ける行数を減らします。

古いデータの消去は典型的な例です。定期的なデータクリーニングでは大量のデータを削除する必要があり、大量のデータ行が長期間ロックされる可能性があります。この操作ではトランザクションログも生成され、大量のリソースが消費され、中断されるべきではない小さなクエリがブロックされる可能性があります。 DELETE ステートメントを中規模のクエリに分割すると、パフォーマンスが大幅に向上し、クエリが繰り返されるときに繰り返しクエリによって発生する余分な待機時間を削減できます。たとえば、次の削除ステートメント:

メッセージから DELETE を実行し、 created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

アプリケーションの疑似コードは次のとおりです。

影響を受ける行数 = 0
する {
  影響を受ける行 = do_query(
  「作成されたメッセージからDELETE < DATE_SUB(NOW(), INTERVAL 3 MONTH)
  制限 10000")
  } 影響を受ける行数 > 0 の場合

一度に 10,000 行を削除するのは、各クエリを効率的に実行するには十分な規模のタスクです。十分に短いタスクはサーバーへの影響を軽減します (トランザクションストレージエンジンはこれによって恩恵を受けます)。また、時間の経過に伴って負荷を分散し、ロックの保持期間を短縮するために、DELETE ステートメントにスリープ時間を挿入することもお勧めします。

共同クエリの分解

多くの高性能アプリケーションでは、共同クエリが分解されます。ユニオンクエリを複数の単一テーブルクエリに分割し、アプリケーション内で結果を結合できます。例えば：

SELECT * FROM タグ
	tag_post を tag_post.tag_id=tag.id に参加します
  tag_post.post_id=post.id の投稿に参加
ここで、 tag.tag='mysql';

このユニオンクエリは次の部分に分割できます。

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123, 456, 567, 9098, 8904);

注: tag_id=1234 および post.id IN (123, 456, 567, 9098, 8904) は、前のクエリの結果に基づいて取得された値です。なぜこれをするのですか?一見すると、これは不必要に思えます。クエリの数が増えるだけです。ただし、クエリを再構築すると、次のような利点が得られます。

キャッシュメカニズムがより効果的になります。多くのアプリケーションは、ORM を使用してデータテーブルを直接マップします。この例では、タグ mysql を持つオブジェクトがすでにキャッシュされている場合、最初のクエリはスキップされます。 ID 123、567、または 9908 の投稿がキャッシュ内にある場合は、それらを IN リストから削除できます。この戦略により、クエリキャッシュはそれに応じてメリットを得られます。テーブルの 1 つだけが頻繁に変更される場合は、結合クエリを分割すると、キャッシュ無効化の数を減らすことができます。
これらのクエリを個別に実行すると、テーブルがロックされる可能性が低くなる場合があります。
この方法により、データベースの拡張やテーブルを別のマシンへの移動が簡単になります。
クエリ自体を最適化できます。この例では、ユニオンクエリの代わりに IN クエリを使用すると、MySQL は行 ID をソートし、行をより最適に取得できるようになります。
冗長な行アクセスを削減できます。このアプローチを使用すると、データ行は 1 回だけ取得されますが、結合クエリでは同じデータを繰り返し取得できます。このため、この分解により、全体的なネットワーク負荷とメモリ使用量も削減される可能性があります。
さらに、MySQL 結合クエリのネストされたループをハッシュ結合クエリを手動で実行して置き換えることもできます。これもより効率的である可能性があります。

最後に、ユニオンクエリを分解することで、キャッシュの再利用性が向上し、マルチサーバー分散データソリューションが簡素化され、大規模なデータテーブルで IN クエリを使用して、ユニオンクエリまたは同じテーブルでの複数の繰り返しクエリを置き換えることができることがわかります。

上記は、MySQL がクエリステートメントを書き換えるための 3 つの戦略の詳細です。MySQL がクエリステートメントを書き換える詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: