遅い MySQL SELECT クエリを最適化してパフォーマンスを向上させ、ディスク領域の使用量を削減するにはどうすればよいですか?

パフォーマンスとディスク容量削減のための MySQL 選択クエリの最適化

実行に数分かかる遅い MySQL クエリは、直面する一般的なパフォーマンスの問題です開発者による。このシナリオでは、クエリは 3 つのテーブルからデータを取得して Web ページを表示します。 EXPLAIN コマンドを使用して調査したところ、クエリが中間結果をディスクに書き込んでいるため、重大なパフォーマンスのボトルネックが発生していることが判明しました。

クエリを最適化するために、次のような包括的なアプローチが採用されました。

テーブル構造分析

クエリでは、poster_data、poster_categories、poster_prodcat の 3 つのテーブルを使用しました。 Poster_data には個々のポスターに関する情報が含まれており、poster_categories にはすべてのカテゴリ (映画、アートなど) がリストされています。 Poster_prodcat は、ポスター ID と関連するカテゴリを保持しました。主なボトルネックは、poster_prodcat で特定されました。これには、1,700 万を超える行と、フィルタリングされている特定のカテゴリの多数の結果 (約 400,000) がありました。

インデックスの最適化

EXPLAIN 出力により、クエリに最適なインデックスが作成されておらず、非効率なデータ アクセスと実行速度の低下につながっていることが明らかになりました。主な問題は、フィルタリングに使用される poster_prodcat.apcatnum 列にインデックスが存在しないことでした。インデックスがないと、MySQL オプティマイザはフル テーブル スキャンに頼ったため、過剰なディスク I/O と長い実行時間が発生しました。

クエリ リライト

パフォーマンスに対処するためこの問題が解決されたため、クエリはより効率的なアプローチを使用して書き直されました:

• 3 つのテーブルは、代わりに INNER JOIN を使用して結合されました。効率の低い SELECT * の
• WHERE 句は、サブクエリの必要性を避け、目的のカテゴリで直接フィルタリングできるように簡素化されました。
• ORDER BY 句は、大規模なデータセットの不必要な並べ替えを防ぐためのクエリ。

一時テーブル作成

ディスク領域の使用量の問題を軽減するために、最適化されたクエリがさらに変更され、中間結果を格納する一時テーブルが作成されました。このアプローチにより、クエリがディスクへのデータの書き込みをバイパスできるようになり、パフォーマンスが大幅に向上しました。

追加の最適化

主な最適化とは別に、さらに強化するためにいくつかの追加の対策が実装されました。クエリのパフォーマンス:

• LIMIT 句を使用した結果の制限: 元のクエリには制限がありませんでした。 Web ページに必要のない多数の結果が返される可能性があります。
• 結果のキャッシュ: 一時テーブルは MEMORY エンジンを使用して作成され、高速アクセスのためにデータをメモリ内に保持します。

結論

インデックス作成、クエリ構造、一時テーブルの使用状況に対処することで、元のクエリが最適化され、パフォーマンスが向上し、ディスク領域の使用量が削減されました。この最適化により、実行時間が大幅に短縮され、Web ページ生成の応答性が大幅に向上しました。

以上が遅い MySQL SELECT クエリを最適化してパフォーマンスを向上させ、ディスク領域の使用量を削減するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。