MySQL クエリステートメントのプロセスと EXPLAIN ステートメントの基本概念とその最適化

ウェブサイトやサービスのパフォーマンスは、データベースの設計（適切な言語開発フレームワークを選択した場合）とデータのクエリ方法に大きく依存します。

MySQL のパフォーマンス最適化方法を理解しています。これには通常、インデックスの作成、複雑な結合クエリの回避、冗長フィールドの設定、中間テーブルの作成、クエリキャッシュなどが含まれます。また、EXPLAIN を使用して実行プランを表示する方法も理解しています。

しかし、複雑な MySQL クエリステートメントの実行プロセスと内部メカニズム、MySQL Optimizer 自体によって行われる最適化、クエリステートメントの調整がパフォーマンスに与える影響とその原因についてはほとんどわかっていません。

この記事では、実行プロセスやインデックスの使用法など、いくつかの重要な概念についてさらに詳しく説明し、その理由を理解しようとします。

これにより、単純な MySQL 最適化で良好な結果が得られる場合に、盲目的に NoSQL ストレージに切り替えたり、インフラストラクチャのアップグレードに投資したりする必要がなくなります。

仕事をうまくやり遂げたいなら、まずツールを磨かなければなりません。ここではまず、MySQL クエリステートメントのパフォーマンス分析ツールを紹介します。

MySQL の EXPLAIN コマンドは、クエリのパフォーマンスを分析するためのツールです。EXPLAIN の出力の各行は、クエリステートメント内のテーブルの実行プランの説明に対応します。出力列の意味は次のとおりです。

上記の表の「タイプ」列は、テーブルの関連付けのタイプです。一般的なタイプは次のとおりです (関連付けクエリの効率の高い順に並べています)。

const (定数接続)、例: SELECT * FROM user WHERE id=1;
eq_ref (等値参照)、例: SELECT * FROM user,card WHERE user.id=card.userid;
ref (参照)。SELECT * FROM user,card WHERE user.last_name='test'; など、一意でないインデックスに使用されます。
範囲、例: SELECT * FROM tbl_name WHERE key_column > 10;
index: インデックスに従ってデータを読み取ります。インデックスにすでにクエリデータが含まれている場合は、インデックスツリーのみをスキャンする必要があります。それ以外の場合は、All と同様に完全なテーブルスキャンが実行されます。
ALL (すべて)、テーブル全体のスキャン

キー列はインデックスを表し、行はスキャンされる行の推定数を表します。

Extra は追加情報を示します。一般的なものは次のとおりです (これもクエリ効率の降順で並べられています)。

インデックスの使用: インデックスの使用を示します。同時に「場所の使用」が表示されている場合は、インデックスを使用してレコードを検索および読み取ります。「場所の使用」が見つからない場合は、インデックスにクエリデータが含まれており、追加の検索は不要であることを意味します。
where: を使用すると、条件付きクエリを示します。type 列が ALL または index で、この情報が表示されない場合は、すべてのデータを返すという誤ったクエリを実行している可能性があります。
filesort の使用: 「ファイルインデックスの使用」の意味ではありません。 Filesort は、MySQL によって実装されるソート戦略です。このメッセージは通常、ソートステートメント ORDER BY が使用されるときに表示されます。
一時テーブルの使用: 結果を取得するために、一時テーブルが使用されます。これは通常、複数のテーブルが結合され、結果が並べ替えられる場合に発生します。

EXPLAIN に次の 2 つのメッセージ (Using filesort、Using temporary) が表示され、行が比較的大きい場合は、通常、クエリステートメントを調整するか、インデックスを追加する必要があることを意味します。つまり、これら 2 つのメッセージを排除する必要があります。

以下は EXPLAIN 結果の例です (ユーザープロファイルテーブルからニックネームと性別を検索し、ユーザーテーブル内のユーザーフォロワーの数で並べ替えます)。