⚙️

MySQL スロークエリ改善初心者向け

2021/06/15に公開1件

MySQL

SQL

tech

概要

本記事はQiita(移行元)からお引越ししてきた記事です。
スロークエリを改善するために、2017年末くらいから勉強会をやっているのでその内容を一度まとめてみる。

環境

MySQL 5.6.22

スロークエリとは？

実行が遅いQuery。事業や実行する処理にもよるが、2秒以上かかっていたら大体遅い部類と言って良い。

スロークエリ改善に重要な用語

用語	意味
実行計画	どのインデックスorテーブルスキャンを使ってクエリーを処理するかの計画。計画が遅ければ、当然実行も遅い。
インデックス	テーブルに格納されたデータの一部を取り出し検索(SELECTなど)用に最適化したもの。インデックスに含まれるカラムは検索が早くなる。

どうやってスロークエリを改善するのか

手順

スロークエリを見つける
スロークエリである原因の特定
スロークエリ改善

主な改善方法

クエリ自体を変更する(ただし、結果は変わらないようにする)
インデックスをテーブルに貼る

他にも実行自体を早くするための方法は存在するが、それはMySQLの方法とはかけ離れるため、割愛。

1. スロークエリを見つける

設定が必要。
MySQLにはその設定がある。
方法としては主に二つある。

MySQLのコンソールからの設定
my.cnfからの設定

MySQLのコンソールからの設定

mysql> set global slow_query_log_file = '/tmp/mysql-slow.log';
mysql> set global long_query_time = 5;
mysql> set global slow_query_log = ON;

my.cnfからの設定

my.cnf にパラメータを設定してスロークエリのログを出力する。
root権限で設定ファイルを編集する。(というか/etc以下はそうであろう・・・)

[mysqld]
slow_query_log=ON
long_query_time = 5
slow_query_log_file = /tmp/mysql-slow.log

上記は「/tmp/mysql-slow.log」に５秒以上のスロークエリを出力するという設定。
設定ファイルを更新したら、変更を反映するために次のコマンド(mysqldがserviceに登録されているならば、そちらの方が良い)
mysqldで再起動を忘れずに。

/etc/init.d/mysqld restart

設定の確認

設定の確認を行う方法は次のとおり。

mysql> show variables like 'slow%';

+---------------------+----------------+
| Variable_name       | Value          |
+---------------------+----------------+
| slow_launch_time    | 2              |
| slow_query_log      | OFF            |
| slow_query_log_file | mysql-slow.log |
+---------------------+----------------+

slow_query_log がOFFになっているとスロークエリの出力は行われない。
何秒以上がスロークエリかは、設定で定義する。
多くの場合は、プロダクトごとに支障が出る秒数を指定する。

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+------------+
| long_query_time | 10.000000 |
+-----------------+-----------+

↓↓↓↓↓↓↓ あなたの記事の内容
───────
上記の場合だと、slow_query_logがONのとき、long_query_timeを超えるクエリがスロークエリとして出力される。

ちなみに設定されていない場合は以下のように設定することができる。

MySQLのコンソールからの場合

mysql> set global slow_query_log_file = '/tmp/mysql-slow.log';
mysql> set global long_query_time = 5;
mysql> set global slow_query_log = ON;

my.cnfからの設定

my.cnf にパラメータを設定してスロークエリのログを出力する。
root権限で設定ファイルを編集する。(というか/etc以下はそうであろう・・・)

[mysqld]
slow_query_log=ON
long_query_time = 5
slow_query_log_file = /tmp/mysql-slow.sql

上記は「/tmp/mysql-slow.log」に５秒以上のスロークエリを出力するという設定。是非お試しあれ。
設定ファイルを更新したら、変更を反映するために以下コマンド(mysqldがserviceに登録されているならば、そちらの方が良いでしょうが)mysqldの再起動を忘れずに。

/etc/init.d/mysqld restart

↑↑↑↑↑↑↑ 編集リクエストの内容

上記の場合だと、slow_query_logがONであれば、
実行時間が、long_query_time(10s)を超えるクエリがスロークエリとして出力される。

実行計画

文字通りMySQLを実行する際にどのように実行するかをあらわしたもの。
良い計画になっていると、データ量が大きくなっても、スロークエリになりにくい。

実行計画の実例

実行計画の確認方法→SQL文の前に、「EXPLAIN」とつければ良い。

mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code = C1.CountryCode;
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref            | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL           |  237 |             |
|  1 | PRIMARY     | Country    | eq_ref | PRIMARY       | PRIMARY | 3       | C1.CountryCode |    1 |             |
|  2 | DERIVED     | City       | ALL    | NULL          | NULL    | NULL    | NULL           | 4079 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
3 rows in set (0.00 sec)

2. スロークエリである原因の特定

実行計画の実例にあるカラムの中で原因特定のために、
初心者がまず見るべきはidとselect_typeとtypeというカラム。

原因特定のために理解が必要なこと

id/select_type

見る前に場合分けして考える。

JOINがある場合
サブクエリがある場合
UNIONがある場合

これらを場合分けして表示を見ると、どのテーブルにどの順番でアクセスされるのかを理解することができる。

idとselect_typeはEXPLAINの最初の2つのフィールドであるが、これらはセットにして考えると良い。
select_typeはクエリの種類を表すものであり、ツリーの構造にそのまま反映される。
クエリの種類とはJOIN、サブクエリ、UNIONおよびそれらの組み合わせで、
select_typeの内容もその組み合わせから導き出されたもの。

1. JOINの場合

MySQLが実行出来るJOINの採用アルゴリズムの種類はNested Look Join（NLJ）の一種類しかない。NLJとは例えばA、B、Cという3つのテーブルをJOINする際、最初にテーブルAから条件にマッチする行を全てフェッチして、次にBから条件にマッチする行をフェッチしてJOINし、次にCから条件にマッチする行をフェッチしてJOINする・・・というように、テーブルを一つずつ順に処理していく方式である。MySQL 6.0ではBKA JOINというのが追加されるが、これもNLJの発展系である。（JOINの方式にはソートマージやHASH JOINなどがあるが、MySQLには実装されていない。）

クエリがJOINだけから構成される場合、select_typeはSIMPLEと表示される。如何に複雑なJOINであってもCOMPLEXとはならずにSIMPLEなのである。従って「これはシンプルなクエリを示すのだ」などと誤解をしてはならない。SIMPLEではidが全て同じ値になる。これはそのクエリが一つのNLJで処理されることを示すからである。NLJではどのテーブルから処理するのかということが最も重要になるが、EXPLAINの出力の順序がどのテーブルから処理するかということを反映している。

2. サブクエリの場合

サブクエリが絡むと次のselect_typeには次の5種類のうちいずれかが表示される。

select_type	意味
PRIMARY	外部クエリを示す。
SUBQUERY	相関関係のないサブクエリ。
DEPENDENT SUBQUERY	相関関係のあるサブクエリ。
UNCACHEABLE SUBQUERY	実行する度に結果が変わる可能性のあるサブクエリ。
DERIVED	FROM句で用いられているサブクエリ。

サブクエリの場合は実行順序に気をつける必要がある。DERIVEDの場合、サブクエリ→外部クエリの順番でクエリが実行される。例えば次のような場合はCityテーブルから最初に行がフェッチされて<derived2>テーブルとなり、その次にCountryテーブルとのJOINが実行される。

3. UNIONの場合

次の5種類のいずれかがselect_typeに表示される。

1. PRIMARY => UNIONにおいて最初にフェッチされるテーブル
2. UNION => 2番目以降にフェッチされるテーブル
3. UNION RESULT => UNIONの実行結果
4. DEPENDENT UNION => DEPENDENT SUBQUERYがUNIONになっている場合
5. UNCACHEABLE UNION => UNCACHEABLE SUBQUERYがUNIONになっている場合

<derived2>UNIONは前から順番に処理されていくだけなので、テーブルが処理される順序という観点ではわかり易いと言えるだろう。

type

select_typeの次に意識しなければいけないのは、typeフィールドである。このフィールドはレコードアクセスタイプとも呼ばれ、対象のテーブルに対してどのような方法でアクセスするかを示す。致命的なクエリはこのフィールドを見れば一目で分かるのでとても重要なフィールドである。よく見かけるものは次の通り。

type	意味
const	PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
eq_ref	JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。
ref	ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。
range	インデックスを用いた範囲検索。
index	フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
ALL	フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。

<derived2>indexまたはALLを見かけたらすかさずクエリをチューニングしよう。

引用元:

インデックス

インデックスとはざっくり言うとSQLを早く実行するための仕組みのこと。MySQLではBtreeと言うアルゴリズムを使って実装されている。詳しいところはQiitaで「MySQL インデックス」で調べるとたくさん出てくるので、調べてみると良い。以下は参考記事一覧。良い記事抜けてるよ！などありましたら教えていただきたく。

参考記事一覧

B-treeインデックス入門 | @kiyodori さん
【MySQL】マルチインデックスの制約の話とか | @rm-rf-slant さん
[MySQL]インデックスによるチューニング〜ログ出力、EXPLAIN、インデックス〜 | @shuntaro_tamura さん
SQLチューニング: ソートを発生させないインデックス設計 | @emotu さん

インデックスをどう貼ったら良いのか？

はじめのうちは色々貼ってみるのが良いかなと。
入り口としてちょうど良いのがtype = ALL となっているクエリ、またはサブクエリ。

一つ一つ自分が作ったものでやってみると良いです。

type ALLとなっている原因の切り分け

まず、WHERE句に使っている箇所を徐々に削除していく
そこでtypeがALLじゃ無くなったらそこが原因の一つ(それが原因のすべてでない事も多い)
原因となったカラムにインデックスを貼ってみる！→早くなったらそれで良い
サブクエリを無くしてみる→サブクエリでもWHERE句やSELECTなら出力カラムの調整とか。JOINなくすとか。

上記を一度試してみると良いかも。

注意点

この記事ではインデックスを貼ってみよう！とか簡単に書いている。
が、インデックスを貼るときにやっていることはALTER TABLE。

ということは、実サービスを運用している場合において、
ユーザがそれなりに集まっている状態だと、簡単にできないことも多い。

実際に動いているサービスでやる場合にはインデックスだけでなく、
サブクエリを使わない形にSQLを変更してみる(十分に検証は必要だが)なども
考慮に入れると良い。

スロークエリ改善まとめ記事

MySQL スロークエリリンク集

余談: そもそもスロークエリを防ぐために

この記事はスロークエリを修正するためには、どうしたら良いかといった視点で主に書かせてもらったが、そもそもスロークエリが発行されないことが一番である。
普段、実装をする際に、N+1問題などは、気にされることが多いが、実行計画については、気にされないことが多い。
もし、SQLが多く発行されるようなToC向けのサービスにおいても、Redisを使う前にそもそもSQLのパフォーマンス、実行計画がどうかといったところを気にしてから、導入をしてみても良いと思う。

そもそもRedis以前にRedisに結果となるデータを入れるためのクエリが遅いのでは本末転倒になる場合もある。

余談: 何秒以上がスロークエリと定義できるのか

スロークエリを改善する上においては、先に、どこからを改善するかといったラインを定める必要がある。
最終的には「ユーザーのUXを大きく損ねるかどうか」に帰着する。
そのため、大げさにいうと、必要にならないのであれば、それまでは貼らないというのも選択肢の一つではないかと考える。
もちろん、先にわかりきっている箇所においては、貼る必要があるが、ちょっと必要になるかわからないな・・・。という要素に関しては、貼っても予想と異なる場合も多く、貼った意味がなくなったといったこともある。
そのため、基本的には、実行計画を実装時に確認しておくことが大切。

GitHubで編集を提案

Discussion

ohki_suguru.php

そのうちRDSの内容を付け加えよう・・・。