はじめに
MySQLの実行計画について学習したことを簡単にまとめます。
検証に使用した環境
DB：MySQL8.0
クエリを実行するまで
MySQLには、クエリを実行するまでに以下の処理があります。
クエリ解析
↓
実行計画策定
↓
実行
実行計画策定
オプティマイザがクエリの実行方法を計画し、「EXPLAIN」を使用することで実行計画を確認できます。
オプティマイザの判断基準
オプティマイザは、統計情報やインデックスなどに基づき実行内容を計画します。
基本的にインデックスを使う方法が効率的です。
インデックスが複数ある場合は、どのインデックスを使用すると効率的か判断することになります。
[注意]
カーディナリティが低い場合はインデックスの効果は低くなります。
そのため、この特性を考慮する必要はあります。
カーディナリティの高低
特定のカラムに登録されているデータの種類の数で決まります。
・カーディナリティが高い
→テーブルの中で、対象カラムに登録されているデータの種類が多い状態
（一位性が高いとも言います）
・カーディナリティが低い
→テーブルの中で、対象カラムに登録されているデータの種類が少ない状態
EXPLAIN
クエリの実行計画を出力します。
※クエリ自体は実行されません
EXPLAIN クエリ;
実行計画を出力できるステートメントは、以下の5つになります。
・select
・delete
・insert
・replace
・update
EXPLAINは、クエリで使用される各テーブルに関する情報を出力します。
クエリを処理する上でテーブルを読み取る順番に出力され、内容は1テーブルにつき1行で表現されます。
表示される項目
id
クエリー内のselectの連番を示し、select識別子と呼ばれます。
select_type
selectの種類が表示されます。
基本は「simple」が表示され、他の種類はこちらに記載されています。
table
出力行で参照しているテーブル名が表示されます。
partitions
クエリーでレコードが照合されるパーティションが表示されます。
パーティショニングしていない場合はnullとなります。
type
テーブルの結合方法が示され、効率の良い順に以下が表示されます
※一部抜粋
const（主キー、またはUNIQUEインデックス）
主キー、またはUNIQUEなカラムでテーブルに該当するレコードが1つとなるため、クエリーの開始時に読み取られる状態です。
レコードが1つしかないため、このレコードのカラムの値は、オプティマイザの残りによって定数とみなされることがあります。
constテーブルは、1回しか読み取られないため、非常に高速です。
eq_ref（JOINで主キー、またはUNIQUEインデックス）
結合元のテーブルの行に対し、このテーブルから「1レコード」が読み取られる状態で、systemとconst型以外では、最適な結合型です。
結合でインデックスのすべてのパートが使用されており、インデックスが「PRIMARY KEY」または「UNIQUE NOT NULL」インデックスである場合に使用されます。
ref（UNIQUEじゃないインデックスで結合）
結合元のテーブルの行に対し、このテーブルから単一のレコードを選択できず、一致する「インデックス値を持つすべてのレコード」が読み取られる状態です。
refは、結合でキーの左端のプリフィクスのみが使用される場合、またはキーが「PRIMARY KEY」や「UNIQUE」インデックスではない場合に使用されます。
使用されているキーがほんの数行にしか一致しない状況であれば、適切な結合型と判断できます。
range（インデックスを使った範囲検索）
インデックスを使用して、絞り込みたい範囲のレコードを取得している状況です。
index（インデックスをフルスキャン）
インデックスツリーをスキャンしていること以外、ALLと同じです。
クエリーで単一のインデックスの一部のカラムのみが使用されている場合に、この結合型となる場合があります。
Extraカラムに「Using index」と表示されている場合
インデックスがクエリーのカバリングインデックスで使用することで、テーブルから必要なデータを全て取得できる場合はインデックスツリーのみがスキャンされます。
インデックスのサイズは通常テーブルデータより小さいため、インデックスのみのスキャンは通常、ALLより高速です。
Extraカラムに「Uses index」が表示されていない場合
フルテーブルスキャンとなっている状況です。
インデックスからの読み取りにより、インデックス順でレコードを読み取っていきます。
ALL（インデックスを使わずテーブルを全部見る）
結合元のテーブルの行との組み合わせごとインデックスを使わずテーブルをスキャンし、著しく効率の悪い状況です。
定数値または結合元のテーブルからのカラム値に基づいて、インデックスを追加することと回避できます。
possible_keys
採用可能なインデックスが表示されます。
key
採用されたインデックスが表示されます。
key_len
採用されたキーの長さが表示されます。
キー長が短い方が高速になります。
ref
テーブルからレコードを選択するために、インデックスと比較されるカラム名または定数が表示されます。
rows
クエリーを実行するために走査が必要と想定される行数が表示されます。
※InnoDBの場合、推定値となります。
filtered
テーブルのレコード全体から条件で絞り込まれた後のレコード数の推定割合(%)が表示されます。
全く絞り込みされなかた場合を最大値の100とし、絞り込まれるにつれて数値が下がります。
たとえば、
rowsが1000行でfilteredが50.00(50%)の場合、次のテーブルと結合される行数は「1000行 × 50% = 500行」になります。
Extra
クエリーを処理するための追加情報が表示されており、スロークエリ対策に有用な情報が表示されるため、とても重要な項目になります。
各表示内容の説明はこちら
参考文献