MySQLでクエリのパフォーマンスを改善する

MySQL

Last updated at 2025-01-21Posted at 2025-01-21

MySQLでクエリスループットを考慮したインデックス設計の覚え書き
正しくインデックス設計を理解することで、UXを改善できた。

引き継いだシステムでクエリがボトルネックなサマリーなページの応答時間を70~80%カット
3~4時間かかっていた激重なスクリーニングクエリを数分に短縮

無用なトライアンドエラーを回避するためには最低限、ツリーのメカニズムの理解は不可避だ。随時更新しなければならない。

インデックスの効用

顕著に効果が出るケース

内部表の結合キーには思考停止でインデックスをはる

テーブル内のデータ量が多く、少量のレコードを検索する場合

WHERE句の条件、結合の条件、ORDER BY句の条件として頻繁に利用する
ただし全表走査が目的のテーブルであれば索引は不要

NULL値が多いデータから、NULL値以外の検索をする
IndexはNULLを含まないのでNULL値以外の検索には効果がある
MySQLのIndexをはるコツ

ただし表から大部分のレコードを検索する場合、オプティマイザは効率的にフルスキャンを選択する場合はある
追加・更新頻度が高い表は索引の再構築が走るため性能低下に繋がるリスクがある。

インデックスが使えない検索条件

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

中間一致、後方一致のLIKE術語(前方一致はOK)

❌... WHERE col_1 LIKE '%検索値'
❌... WHERE col_1 LIKE '%検索値%'
⭕... WHERE col_1 LIKE '検索値%'

索引列に関数を使っている/演算を行っている

❌... WHERE LENGTH(col_1) = 10
❌... WHERE col_1 * 1.1 > 100
⭕... WHERE col_1 > 100 / 1.1

IS NULL術語を使っている

「索引データにNULLは存在しないため」…とあったが、実行計画を見る限りref~constでヒットされた。

⭕... WHERE col_1 IS NULL
【MySQL】IS NULL でもインデックスは使用される #MySQL - Qiita
❌... WHERE col_1 IS NOT NULL

否定形を使っている( `<>` / `!=` / `NOT IN`)

❌... WHERE col_1 <> 100

アプリケーション設計

ナノ秒早くなるから予約語は大文字に統一する
必要ないならORDERしない
WHEREとORDERはひとつのテーブルに集中する
否定演算は等価演算にする
JOINの駆動表は行数の少ないテーブルを選択する

実行計画

スロークエリはEXPLAINで実行計画を分析して改善する。

まとめ

EXPLAINコマンドの各フィールドの詳細を説明したが、実際にEXPLAINコマンドを使ってクエリの実行計画を見る際には次のようなステップを踏むといいだろう。

id/select_type/tableフィールドを見て、どのテーブルがどの順序でアクセスされるのかを知る。これらはクエリの構造を示すフィールドであると言える。サブクエリが含まれている場合にはEXPLAINの表示順とアクセスされる順序が異なる場合があるので気をつける必要がある。

type/key/ref/rowsフィールドを見て、各テーブルから行がどのようにフェッチされるのかを知る。どのテーブルへのアクセスが最も重いか（クエリの性能の足を引っ張っているのか）を、これらのフィールドから判断することが出来る。

Extraフィールドを見て、オプティマイザがどのように判断して、各々のテーブルへのアクセスにおいて何を実行しているのかを知る。Extraフィールドはオプティマイザの挙動を示すものであり、クエリの全体像を把握するのに役立つ。

同じ結果が得られるなら、ひとつのクエリにおいてテーブルからフェッチされる行数が減れば減るほど良い。なぜなら、ストレージエンジンの性能には上限があるからである。例えば、あるストレージエンジンが1秒間に100万行のフェッチが出来る性能を持っているとすると、一つのクエリが平均1000行のフェッチを行うならば1秒間に最大1000クエリ、一つのクエリが平均100行のフェッチを行うならば1秒間に最大1万クエリが可能であるという計算になる。ひとつのクエリがフェッチする行数が減れば、クエリのレスポンスだけでなく全体のスループットも向上するのである。
漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!

実行計画から得られる情報

select_type ==注目==

SIMPLE

JOINを含めてひとつのクエリで構成されている

以下はサブクエリの場合に出てくるパターン
サブクエリの場合、外部クエリとサブクエリでは別々のidが付番される

PRIMARY

そのクエリは外部クエリを指している
tableが<derived>なら実態はDERIVEDサブクエリ

SUBQUERY

そのクエリは相関関係のないサブクエリを指している。
サブクエリが実行されるのは最初の一回だけで、それ以降はキャッシュされた実行結果が利用されている。

DERIVED

そのクエリはFROM句で用いられているサブクエリを指している

❗DEPENDENT SUBQUERY

==そのクエリは相関関係のあるサブクエリ==
駆動表と比較結合のたびにサブクエリが走るかなりやばい状態

対策

サブクエリ側にインデックスを張って改善する
非相関クエリになるようにクエリを再設計する

table

対象のテーブル名

type ==注目==

対象のテーブルに対してどのような方法でアクセスするかを示す。

const👏

プライマリキーまたはユニークキーによって、単一のステートメントに一致した。

eq_ref👏

JOINにおいてプライマリキーまたはユニークで単一のステートメントに一致した。

ref👏

インデックスで単一のステートメントに一致した。

range👏

インデックスによる範囲検索でステートメントに一致した。

❗index

==フルインデックススキャンした。==
複合インデックスにおいて、ひとつめのカラムに範囲指定など広範囲のキーをクエリ条件に指定した場合に、ふたつめ以降のカラムの条件組み合わせにより余儀なくインデックスをフルスキャンしている。

対策
複合インデックスにおいてはひとつめのカラムに等価演算で大部分を削れるようなインデックスを設計すること。
そうでなければ単一インデックスで良い。

インデックスレコードの走査の数を減らす観点では c1 = 2 AND c2 <= 4 のように c1 の条件が等価比較になる場合に限って、複合インデックスとしての意味を持つことになります。
MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ

❗ALL

==フルテーブルスキャンした==

対策

インデックスを作成する

possible_keys

オプティマイザがテーブルのアクセスに利用可能なインデックスの候補として挙げたキーの一覧。

key

実際にオプティマイザによって選択されたキー。

key_len

選択されたキーの長さ。==キー長は短い方が早い==ようだ。

ref

検索条件で、keyと比較されている値やカラムを示す。

定数が指定されている場合はconst。
JOINの場合は、結合する相手側のテーブルで検索条件として利用されているカラム。

rows

対象tableからフェッチされる行数の見積もりを示す。

Extra=Using whereの状況では、フェッチした行に対してさらにWHERE句の検索条件が適用されて行の絞り込みが行われるので、結果は少なくなる可能性がある。
JOINの場合、最終的な結果行数の見積もりはJOINする全てのテーブルのrowsフィールドの積である。
レコードアクセスタイプがeq_ref（プライマリキー or ユニークキー）の場合、rowsフィールドは1になる。

Extra

オプティマイザがどのような戦略を選択したかということを示す

Using index👏

クエリがカバリングインデックスでデータ取得できた。

Using index for group-by👏

MIN()/MAX()がGROUP BY句と併用されているとき、クエリがインデックスだけを用いて解決された

Using index Condition👏

クエリ条件をインデックスを使ってフィルターできた。

Using where

ストレージエンジンによるUsing index Conditionの後に、Executorが追加で行レベルのフィルタリングが必要となった

Range checked for each record (index map: N)

JOINにおいてrangeまたはindex_mergeが利用された

Not exists

LEFT JOINにおいて、左側のテーブルからフェッチされた行にマッチする行が右側のテーブルに存在しない場合、右側のテーブルはNULLとなるが、右側のテーブルがNOT NULLとして定義されたフィールドでJOINされている場合にはマッチしない行を探せば良い・・・ということを示す。

❗Using filesort

ソート処理にインデックスが使われず、==クイックソートされた。==

対策

カバリングインデックスを作成する
実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法 #MySQL - Qiita
漢(オトコ)のコンピュータ道: オトコのソートテクニック2008
漢(オトコ)のコンピュータ道: Using filesort

❗Using temporary

ソートのために==一時テーブルが作成された。==

対策

駆動表にカバリングインデックスを作成する
駆動表にWHERE-ORDERBYを集中して、JOIN結合前にソートを完了させる
実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法 #MySQL - Qiita
漢(オトコ)のコンピュータ道: オトコのソートテクニック2008

まとめ。

以上の話をまとめると次のようになる。

Filesortとはクイックソートのことである。

JOINにおいてORDER BYが指定されているとき、ソートが行われるタイミングは3種類ある。

Using filesortがEXPLAINに出ていないときはインデックスを用いてソートが行われている。（最も高速。）

Using filesortだけがEXPLAINの最初の行に出ている時は、先にソートしてからJOINが実行されている。

Using temporary; Using filesortがEXPLAINの最初の行に出ている時は、先にJOINしてからソートされている。（最も遅い。）

WHERE句による検索条件とORDER BYにおけるソート条件は、一つのテーブルに集中させる。

JOINにおいてソート処理する場合には、LIMIT句が適用されるのはJOINとソートが完了した後である。

LEFT/RIGHT JOINの場合には、LIMIT句の問題はサブクエリである程度対策が可能である。
漢(オトコ)のコンピュータ道: Using filesort

用語

NLJ (ネステッド・ループ・ジョイン) テーブル結合のアルゴリズム。結合元テーブル駆動表×被結合テーブル内部表の直積。
駆動表 NLJにおける結合元テーブル
内部表 NLJにおける被結合テーブル
外部クエリ サブクエリからみたPRIMARYのクエリ
相関クエリ サブクエリにおいて、メインクエリの各行に対してサブクエリが実行されるため、オーダー数が大爆発する（dependent subquery）。非相関サブクエリとは、まずサブクエリが実行され、その結果をメインクエリに渡す。実行計画が逆。
カバリングインデックス クエリ(WHERE~ORDER=>SELECT)が必要とする情報を網羅した複合インデックスであり、インデックススキャンのみで高速にデータを取得するために最適設計したインデックス。達成した暁にはExplainのExtraにて、Using Indexの誉が与えられる。

インデックスの強制

実行計画でオプティマイザが想定外のインデックスを採用するような時はインデックスを強制的に指定することができる。ただし、オプティマイザの判断についてなぜそうなのか、変更によってトレードオフとなる要件は何か十分に理解しておいた方が良い。

SELECT ... FROM nm_table FORCE INDEX (nm_index)

調べ物

駆動表に小さなテーブルを選ぶ理由

結果的に駆動表(n)と内部表(m)の突合数はどちら(nm or mn)にせよを変わらないのでは、、

・駆動表の重要性
Nested Loopsの性能を改善するキーワードとして「駆動表に小さなテーブルを選ぶ」ということを聞いたことのある人もいると思います。これは大方針として間違いではないのですが、実はある前提条件がないと意味がないので、なぜ駆動表が小さいほうが性能的に有利なのか、それが意味を持つ条件は何なのか、その理由をここで理解しておきましょう。
実際、上で解説した Nested Loopsのしくみを前提すると、駆動表がどちらのテーブルになっても、結局のところアクセスされる行数はR（A）✕R（B）で表現されるのだから、駆動表が小さかろうが大きかろうが、結合コストに違いはないように思われます。実は、この「駆動表を小さく」という格言には、次のような暗黙の前提が隠れています。
内部表の結合キーの列にインデックスが存在すること
もし内部表の結合キーの列にインデックスが存在する場合、そのインデックスをたどることによって、DBMSは駆動表の1行に対して内部表を馬鹿正直にループする必要がなくなります。いわば内部表のループをある程度スキップできるようになるのです
SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

外部表で条件に一致した行の数だけループするので、外部表の条件で結合対象の行数を少なくすることができれば内部表でディスクにアクセスする回数を減らすことができ、より良いパフォーマンスを期待できます。
MySQL(InnoDB)のSQLパフォーマンスチューニングのエッセンス

要は、内部表はインデックス付き結合キーで高速にルックアップできるため、あとは「駆動表に小さなテーブルを選ぶ」には以下にWHEREで絞り込めるかが肝要だ。

where句またはjoin句にANDで複数条件を与えたときインデックスは使われないのか

クエリに用いられるインデックスはテーブルごとに1つで、オプティマイザが最適なインデックスを選択する。
クエリ条件＆ソートに登場するカラムを網羅的にカバーする複合インデックスが必要。
複合インデックスに対してクエリはインデックス定義順に前方一致でオーダーしなければならない。
実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法 #MySQL - Qiita

order by句にインデックスは使われないのか

Using filesortを解消するためにソートしたいカラムにインデックスを張ったが使われない。

-- at_dayカラムにはインデックスを張っているのにUsing filesort
EXPLAIN SELECT * FROM reports ORDER BY at_day DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	reports	NULL	ALL	NULL	NULL	NULL	NULL	240	100.00	Using filesort

なぜなら、'Using index'は（カバリングインデックスを使用している場合など）インデックスの情報のみを使用してデータを取得できる場合に使用できる方法だからです。
MySQLのインデックスがうまく効かない
 MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.2.1.16 ORDER BY の最適化

Using index
実際の行を読み取るための追加のシークを実行する必要がなく、インデックスツリーの情報のみを使用して、テーブルからカラム情報が取得されます。この戦略は、クエリーで単一のインデックスの一部であるカラムのみを使用している場合に使用できます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット

インデックスツリーの情報のみを使用してテーブルからカラムが取得するためには、SELECTでカバリングインデックスに含まれないカラムを指定してはいけない。

-- インデックスに含まれるカラムのみをselectするとUsing index
EXPLAIN SELECT at_day FROM reports ORDER BY at_day DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	reports	NULL	index	NULL	idx_at_day	3	NULL	240	100.00	Using index

select_type.DEPENDENT SUBQUERYとは

explain select * from employees e
where exists ( select * from reports r where r.`employee_id` = e.id );

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	e	NULL	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
2	DEPENDENT SUBQUERY	r	NULL	ref	idx_employee_id	idx_employee_id	4	kkk.e.id	30	100.00	Using where; Using index

DEPENDENT SUBQUERYとはいわゆる相関サブクエリに相当するもので、サブクエリにおいて外部クエリのカラムを参照しているサブクエリのことである。

現時点でのMySQL（バージョン5.1）では、サブクエリはまず外部クエリの条件から評価される。そして、外部クエリの条件に合致する行が見つかると、その行がサブクエリの条件に合致するかどうかが評価されるわけである。即ち、サブクエリにおいてフェッチしなければいけない行数が平均N行、外部クエリでフェッチされる行数がM行のとき、サブクエリにおいてM×N行の評価が行われることになる。これは膨大な計算量である。

MySQLは内部的にINを直接処理することができないので、EXISTSに変換することでSQL的には相関のないサブクエリも相関サブクエリになってしまうのである。これがまさにMySQLのサブクエリが遅い！と言われている原因だろう。
なぜMySQLのサブクエリは遅いのか。

相関サブクエリに相当する。外部クエリでフェッチしたステートメントごとに、サブクエリを都度フェッチして評価するため計算量が膨らむため遅い。以下対策。

外部クエリのフェッチを絞る
サブクエリにインデックスを張る

インデックス=>行データはどうなって取得されているというのか

MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ

WHERE句でインデックスに含まれるカラムに条件を指定することで、効率的にデータを選択した後、SELECTでインデックスに含まれないカラムを取得するために、インデックスエントリに含まれる行識別子でクラスタの実データにマッピングして追加のディスクアクセスが行う。

クラスタインデックスは、テーブルのプライマリキーをキーとして持つインデックスです。リーフノードにキー以外のテーブルのすべてのデータが含まれています。プライマリキーを使ってクラスタインデックスを探索した場合、リーフノードから目的のデータを取得した時点で探索は終了です。
セカンダリインデックスは、インデックス作成時に指定したカラムの値をキーとして持ちます。また、リーフノードにはキーとテーブルのプライマリキーのみを持ちます。セカンダリインデックスを使った探索は、キーとプライマリキー以外のデータを取得する必要がある場合、プライマリキーを使って別途クラスタインデックスを探索する必要があります。
一般に、セカンダリインデックスを利用した探索は、探索後に別途クラスタインデックスの探索を行うため、クラスタインデックスのみの探索と比べて遅くなります。例外として、セカンダリインデックスに取得したいデータが全て揃っている場合は、クラスタインデックスへの追加の探索は行われません。このような特定のクエリに対して必要なデータを網羅しているセカンダリインデックスのことを、カバリングインデックスと呼びます。
MySQL(InnoDB)のSQLパフォーマンスチューニングのエッセンス

クラスタインデックスとはテーブルのプラマリキーで、セカンダリインデックスとはインデックスだ。

参考文献