MySQLの実行計画について - Wiz テックブログ

こんにちはバックエンドエンジニアの小室です。

業務では主にLaravelを使って実装しています。現在担当している案件の検索機能の実装が複雑でクエリビルダーでは事足りず生SQLで実装する必要があり、改めてSQLの重要性を実感しました。そこで最近学んだSQLの実行計画について簡単にまとめてみました。

実行計画

実行計画とは、テーブルに対して検索をかけた際、どういった手順を踏んでアクセスしたかを示す実行手順書のようなものになります。

データ量や統計情報（オプティマイザ統計）などの情報をもとに、最適な実行計画がされますが、同じSQLであればいつも同じ実行計画が作成されるとは限らず、データ量が大きく変更されたときや、統計情報が古いままだと適切な実行計画が作成されず、パフォーマンスが低下する場合もあります。

実行計画の確認方法

mysqlで実行計画を確認するにはselect文の先頭に「EXPLAIN」をつければ確認できます。

EXPLAIN 
    SELECT departments.id, departments.name d_name, companies.name c_name 
    FROM departments 
    INNER JOIN companies
    ON departments.company_id = companies.id

以下の様な表が表示されます。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	departments		ALL	departments_company_id_foreign				9	100.0
1	SIMPLE	companies		eq_ref	PRIMARY	PRIMARY	8	practice_db.departments.company_id	1	100.0

DBeaverというツールを使えば、実行計画をツリー構造で表示する事もできます。「https://dbeaver.io/」からインストールできます。ほぼ全てのDBに対応しており、実行計画の表示から、ER図の自動作成など便利な機能が豊富です。

f:id:shuto_komuro:20211106152701p:plain — DBeaver-実行計画

各カラムの説明

SELECT 識別子を表し、クエリー内の SELECT の連番になります。

select_type

SELECTの種類を表し、SIMPLE,PRIMARY,UNIONなどがあります。

table

出力の行で参照しているテーブルの名前を示します。

partitions

クエリーでレコードが照合されるパーティションを示します。

type

結合のタイプを表し、ALL,CONST,eq_refなどがあります。

possible_keys

テーブル内の行の検索に使用するために選択できるインデックスを示します。

実際に使用することを決定したキー (インデックス) を示します。

key_len

実際に使用することを決定したキーの長さを示します。

テーブルから行を選択するために、key カラムに指定されたインデックスに対して比較されるカラムまたは定数を示します。

rows

クエリーを実行するために調査する行数を示します。(推定数)

filtered

テーブル条件によってフィルタ処理されるテーブル行の推定の割合を示します。

Extra

クエリーを解決する方法に関する追加情報が含まれます。(where句など)

より詳しい説明は以下の公式リファレンスを参照してください。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット

チューニングサンプル

unionとcase分

以下のような都市別、男女別の人口を示すpopulationsテーブル(table1)があるとします。

このテーブルから、都市別に性別を1レコードにまとめた結果(table2)を

出力したいとします。

table1

id	city_name	sex	population
1	都市1	1	63
2	都市1	2	99
3	都市2	1	39
4	都市2	2	93
5	都市3	1	42
6	都市3	2	32
7	都市4	1	38
8	都市4	2	67
9	都市5	1	79
10	都市5	2	59

table2

city_name	p_men	p_wom
都市1	63	99
都市2	39	93
都市3	42	32
都市4	38	67
都市5	79	59

unionを使った解

都道府県別に男性の合計値を求めた後、都道府県別に女性の合計値を求めそれらの結果をマージするという手順になると思います。

sqlは以下の様になります。

SELECT city_name, sum(p_men) AS p_men, sum(p_wom) AS p_wom 
FROM (
    SELECT city_name, population AS p_men, NULL AS p_wom 
    FROM populations WHERE sex = 1
    UNION 
    SELECT city_name, null AS p_men, population AS p_wom 
    FROM populations WHERE sex = 2
) tmp 
GROUP BY city_name

以下の実行計画が出力されます。

id	select_type	table	type	rows	filtered	Extra
1	PRIMARY		ALL	4	100.0	Using temporary
2	DERIVED	populations	ALL	10	10.0	Using where
3	UNION	populations	ALL	10	10.0	Using where
	UNION RESULT	<union2,3>	ALL			Using temporary

populationsテーブルに対してフルスキャンが2回実行されていることがわかります。

UNIONを使えば、問題を小さなサブ問題に分割して考えることができますが、

内部的に複数のSELECT文を実行する実行計画として解釈されるためI/Oコストが膨らみませす。

CASE式を使った解

CASE式を使えばアクセスを1回に減らしコスト改善が可能です。

CASE式を集約関数内に収め、男性だけの人口と女性だけの人口の列を作る方法です。

sqlは以下の様になります。

SELECT
    city_name,
    sum(CASE WHEN sex = 1 THEN population ELSE 0 end) AS p_men, 
    sum(CASE WHEN sex = 2 THEN population ELSE 0 end) AS p_wom
 FROM populations
GROUP BY city_name