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

on   in      0 件のコメント

クエリ性能を考慮したスキーマ設計&クエリを実装するための覚え書き。

インデックスの効用

顕著に効果が出るケース

データ量=NLJ突合量が多いテーブル同士のJOINでは、内部表の結合キーに思考停止レベルでインデックスを貼る

  1. テーブル内のデータ量が多く、少量のレコードを検索する場合
  2. WHERE句の条件、結合の条件、ORDER BY句の条件として頻繁に利用するただし全表走査が目的のテーブルであれば索引は不要
  3. 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でヒットされた。

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

  • ❌... WHERE col_1 <> 100

アプリケーション設計

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

実行計画

スロークエリはEXPLAINで当該クエリの実行計画を確認してボトルネックを分析する。

まとめ

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

  1. id/select_type/tableフィールドを見て、どのテーブルがどの順序でアクセスされるのかを知る。これらはクエリの構造を示すフィールドであると言える。サブクエリが含まれている場合にはEXPLAINの表示順とアクセスされる順序が異なる場合があるので気をつける必要がある。
  2. type/key/ref/rowsフィールドを見て、各テーブルから行がどのようにフェッチされるのかを知る。どのテーブルへのアクセスが最も重いか（クエリの性能の足を引っ張っているのか）を、これらのフィールドから判断することが出来る。
  3. 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

フルインデックススキャンした。

対策

　❗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 where

インデックスだけではWHERE句の条件を全て適用することが出来ていない

対策

  • カバリングインデックスを作成する

Range checked for each record (index map: N)

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

Not exists

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

❗Using filesort

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

対策

❗Using temporary

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

対策

まとめ。

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

  • 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の誉が与えられる。

調べ物

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

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

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

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

内部表はインデックス付き結合キーで高速にルックアップできるため、あとは駆動表が小さいほど結合の効率がよいということだ。

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のサブクエリは遅いのか。

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

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

参考文献

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

インデックスの定石系

実行計画系

Share:

0 Comments:

コメントを投稿