前回は、UnrealEngineのc++についてご紹介いたしました。
今回は、データベースの処理速度が遅い場合に、よく目にする、「slow query」について、簡単ですがご紹介いたします。
■slow query について
slow query とは、データベースに対して SQL文を発行した際に、
結果の取得に一定時間以上かかったことを知るものです。
MySQLであれば、以下のように、config ファイルを編集することで
sloq query の出力を有効にできます。
======================
[mysqld]
slow_query_log
slow_query_log-file = /var/log/mysql/mysql-slow.sql
long_query_time = 5
======================
もし、発行されてるすべてのクエリについて、処理時間が知りたいのでしたら、
long_query_time = 0
を指定することで、すべてのログを収集できます。
※ただし、すべてのログを出すと、ファイルへのI/Oが急激に増えますので、
本番稼働中のシステムに対して、この設定は行うべきではありません。
あくまでも、解析のために一時的に設定するものです
■slow query が出る原因
クエリの発行が遅くなる原因として、最も多いのは、
indexがうまく働いていない or 単純にデータ量が多い等の理由で重い
のどちらかです。
このどちらかがトリガーとなって、
１）原因となるクエリ発行により、DBテーブルや行にロックがかかる
２）後発のトランザクション処理が、ロック解除待ち状態に入って遅くなる
３）アクセス量が多い場合、２）の状態のトランザクションが多くなり、
DBへの同時接続可能数が飽和する
４）Webシステムの場合は、３）の結果、Webサーバの同時接続可能数が飽和する
というような状態になってしまいます。
原因となるクエリについて、もし、indexがうまく働いていないのであれば、
１）クエリを改善する
２）プログラム側で対応できそうな部分を切り出すことにして
クエリ自体は単純化してしまう
３）Webサーバ側だけで対処するのではなく、クライアント側で対応できそうな
部分を切り出すことにして、クエリ自体を単純化してしまう
というような方法をとることになります。
そこで今回は、どういう場合に indexがうまく働かないのかについてご紹介します。
■今回用意したテーブル
indexについて説明を行うために、今回用意したテーブルは、以下の通りです。
この段階では、まだ index の定義を行っていませんが、
primary key は設定しています。
======================
— エピソードマスタ
CREATE TABLE episode_mst (
episode_id int unsigned NOT NULL,
kind int unsigned NOT NULL,
title varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
subtitle varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
episode_flg tinyint unsigned NOT NULL,
PRIMARY KEY (episode_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
— チャプターマスタ
CREATE TABLE chapter_mst (
episode_id int unsigned NOT NULL,
chapter_id int unsigned NOT NULL,
title varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
subtitle varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
episode_flg tinyint unsigned NOT NULL,
PRIMARY KEY (episode_id, chapter_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
— チャプターのステージマスタ
CREATE TABLE chapter_stage_mst (
episode_id int unsigned NOT NULL,
chapter_id int unsigned NOT NULL,
stage_id smallint unsigned NOT NULL,
title varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
subtitle varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
episode_flg tinyint unsigned NOT NULL,
PRIMARY KEY (episode_id, chapter_id, stage_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
======================
■indexが適用されているのかを調べる方法について
この方法は、インターネットで検索をすることで、すぐにわかることですが、
ここでも軽くご紹介します。
たとえば、先程用意したテーブルのうち、「chapter_stage_mst」を全件検索するとしたら、
select * from chapter_stage_mst;
というクエリを発行します。
このクエリには、indexが適用されているのでしょうか？
それを調べる場合には、
explain select * from chapter_stage_mst;
というクエリを発行します。
実行結果は、以下の通りです。
key のところが NULL になっており、index が適用されていないことが分かります。
※この select 文は、すべてのデータを取得するものなので、index が働かないのは当たり前のことです
今度は、
explain select * from chapter_stage_mst where episode_id = 1 and chapter_id = 1 and stage_id = 1;
というクエリを発行してみます。
実行結果は、以下の通りです。
key のところが PRIMARY となっており、index が適用されていることがわかります。
■primary key には index が適用されている？
先程のクエリの実行結果から、primary key には自分達で別途 index を設定しなくても、index が貼られている状態に
なっていることがわかりました。
しかし、安易にそう解釈するのは誤りです！
このテーブルは、定義部分を見ると、primary key は、3つのデータの複合キーになっています。
ではもし、primary key に属する1つだけを、検索条件に加えたらどうなるでしょうか？
それを確認するために、
explain select * from chapter_stage_mst where episode_id = 1;
explain select * from chapter_stage_mst where chapter_id = 1;
explain select * from chapter_stage_mst where stage_id = 1;
というクエリを発行してみます。
実行結果は、上から順に以下の通りです。
このテーブルの primary key の設定は、
PRIMARY KEY (episode_id, chapter_id, stage_id)
となります。
この時、一番左側にある設定については、key = PRIMARY ということで、index が適用されています。
しかし、chapter_id、stage_id 単独で検索をした時には、key = NULLになっており、
index が適用されていないことがわかります。
index を設定するときにも、同じことが言えるのですが、
複合キーで設定を行うというのは、二分探索木を複数のキーを用いて、作成することを意味します。
この時、一番最初の、二分探索木に利用するデータは、一番左に記載している値です。（今回だと episode_id）
そのほかのキーについては、「episode_id が決まっている前提」で、index が貼られています。
これを踏まえて、次は、
explain select * from chapter_stage_mst where episode_id = 1 and chapter_id = 1;
explain select * from chapter_stage_mst where episode_id = 1 and stage_id = 1;
というクエリを発行してみます。
御覧の通り、どちらも key = PRIMARY になっています。
この検索条件でしたら、index が正常に機能していることになります。
■primary key 以外の column で検索を行いたい場合は、index を明示的に設定する
先ほどの例では、primary key での検索を行いました。
しかし、いつでも primary key で検索を行いたいかというと、そうではありません。
たとえば、chapter_stage_mst の、title で検索を行う必要があるシステムであったとすると、
select * from chapter_stage_mst where title = ‘ステージ1’;
のようなクエリを発行することになります。
explain select * from chapter_stage_mst where title = ‘ステージ1’;
を発行すると、当然ですが、結果は以下のように、index が適用されていない状態になります。
このような場合に、どうしてもシステム上、この column で検索をしなければならないということであれば、
index を明示的に設定する必要があります。
もし、title で検索をしたいのであれば、テーブルを作成するときに、以下のように indexを設定します。
======================
— チャプターのステージマスタ
CREATE TABLE chapter_stage_mst (
episode_id int unsigned NOT NULL,
chapter_id int unsigned NOT NULL,
stage_id smallint unsigned NOT NULL,
title varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
subtitle varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
episode_flg tinyint unsigned NOT NULL,
PRIMARY KEY (episode_id, chapter_id, stage_id),
INDEX idx_chapter_stage_mst_title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
======================
この条件のもと、再度先程の
explain select * from chapter_stage_mst where title = ‘ステージ1’;
を発行すると、key = idx_chapter_stage_mst_title と表示され、index が適用されていることがわかります。
■index をはるべき対象は、何なのか
システムを構築する上で、index をどこに貼るのかということは、とても重要なことです。
理論的には、index をすべての column に設定すれば、select は早くなりますが、
insert や update 等の更新処理は、二分探索木を構築しなおす手間がかかってしまうために、かえって遅くなってしまうのです。
このため、基本的には、以下のルールで index を設定します。
１）検索条件
これまで話題に上げた、episode_id = 1 のようなものです。
２）結合条件
複数のテーブルを結合するケースでは、その結合条件にも index を利用する必要があります。
３）並べ替え
order by を行う場合にも、index を利用します。
特に、検索結果が膨大である場合、それを並べ替えしようと思ったら、index が設定されていないと、
とても重くなってしまいます。
■indexを貼っていたとしても、クエリの「検索条件」「結合条件」「並べ替え」に「関数」を利用するのは避けるべき
MySQLをはじめとする、各種DBには、便利な関数がたくさんありますが、
たとえば、複数の生徒が獲得した点数に応じて、ランキングを出す、というようなケースで、
もしクエリの中でこれを利用してしまったら、大変なことになります。
※PostgreSQLの場合は、rank という関数があります
index は、今データとして格納されているものを、そのまま利用する場合には有効ですが、
クエリの中で加工してしまった上に、それを「検索条件」「結合条件」「並べ替え」に利用してしまったら、index は働きません。
加工後のデータに対しては、index が貼られていないためです。
ランキングを出したうえで、並べ替えを行いたいのであれば、
それはクエリで解決するのではなく、プログラム側で、バブルソートやクイックソートを利用するべきです。
■対象となるテーブルには気を付ける
このようにindexを設定したとしても、クエリの書き方だけで、速度が変わってしまうケースもあります。
特に、気を付けるべきは、「検索条件」「結合条件」「並べ替え」に利用する column は、
できるだけ1つのテーブルの列に集約するということです。
以下のクエリは、良いクエリです。
======================
explain
select * from
episode_mst as e, chapter_mst as c, chapter_stage_mst as s
where
e.episode_id < 10 and
e.episode_id = c.episode_id and
e.episode_id = s.episode_id and
c.chapter_id = s.chapter_id
order by
e.episode_id;
======================
実行結果は以下の通りです。
今まで着目していませんでしたが、「Extra」のところが、「Using where」になっています。
一方、これとまったく同じ結果が得られる、以下のクエリはどうでしょうか？
======================
explain
select * from
episode_mst as e, chapter_mst as c, chapter_stage_mst as s
where
e.episode_id < 10 and
e.episode_id = c.episode_id and
c.episode_id = s.episode_id and
c.chapter_id = s.chapter_id
order by
s.episode_id;
======================
違いは、結合に利用しているテーブルと、並べ替えに利用しているテーブルです。
得られる結果は同じですが、実行結果は以下の通りです。
「Extra」のところが、「Using where; Using temporary; Using filesort」になっています。
filesortというのは、クイックソートのことで、index を使わないで、自前でソート処理をしているよ、ということです。
実は、indexが利用されているとは言え、この状態は最も遅いです。
Extraに出ている文字については、
・Using filesortが表示されていない場合は、indexを利用しているだけです（高速です）
・Using filesortが表示されている場合は、結合にindexを利用したあと、indexを利用せずにソートしています（遅いです）
となっています。
たった少し、書き方を変えるだけで、index の利用のされ方が変わってしまいます。
今回ご紹介したテーブルは、構造が比較的簡単ですので、より上位に位置するテーブルを
結合条件に利用することにすればよいです。
■データ量に気を付ける
上述のように、Extra に filesort が出る場合は、index が働いていない動きをしている箇所があるので遅い、
そもそも、key が NULL の場合は、index が働いていないので遅いという話をしましたが、
それだけでは解消できない問題もあります。
それが、データ量です。
2つのテーブルを結合する必要がある場合で、2つのテーブルの中に入っているデータ量がともに膨大である場合、
結合には、<1つ目のテーブルの行数> × <2つ目のテーブルの行数> 分の判定が行われます。
もし、どちらのテーブルにも正しく index が設定されていたとしても、
データ量が多くて、組み合わせの数が多い時には、速度が出ないという現象になります。
たとえば以下のクエリは、それぞれのテーブルの中にあるデータ量が膨大であった場合、
速度が上がらない可能性があります。
======================
explain
select * from
episode_mst as e, chapter_mst as c, chapter_stage_mst as s
where
e.episode_id = c.episode_id and
e.episode_id = s.episode_id and
c.chapter_id = s.chapter_id and
s.title like ‘ステージ1%’
order by
e.episode_id;
======================
この場合、あらかじめ chapter_stage_mst の件数を減らすことで、速度を改善できる可能性があります。「
======================
explain
select * from
episode_mst as e, chapter_mst as c,
(select * from chapter_stage_mst where title like ‘ステージ1%’) as s
where
e.episode_id = c.episode_id and
e.episode_id = s.episode_id and
c.chapter_id = s.chapter_id
order by
e.episode_id;
======================
この実行方法の場合、副問い合わせ（chapter_stage_mst を絞り込んでいるクエリ）の実行結果である「s」に対しては、もうindexは働きません。
しかし、indexが働いていない状況に敢えてする代わりに、結合対象を最初に絞り込み、データ量を減らしておくことは
結果的に処理速度の向上につながる可能性があります。
結合を行おうとする時のデータ量が、最初から小さければ、データベース側で自前ソートするのも高速です。
■クエリだけでなんとかしようとしない
データベースへのアクセスは、Webシステムを利用する上で、肝になる部分です。
ここの速度が低下すると、システム全体の処理速度に大きく影響を及ぼします。
もし、この記事を読まれている方が、データベース以外にも、Webサーバのプログラムや、クライアント側のプログラムまで考慮に入れて、
システムを構築できるのでしたら、すべてをクエリでなんとかしようと思わないことです。
私が個人的に感じているのは、クライアント側に渡すデータ量が多くない場合、
たとえば、1ユーザが利用するデータ自体は少ないが、ユーザ数が多いのだということであれば、
無理にテーブルの結合や、ソートのような部分をクエリで賄わずに、プログラム側で自前で行う方が良いケースもあります。
また、このような観点で考えると、よく利用するデータはRadisキャッシュを利用してデータベースへのアクセス数を減らしたり、
さらに上位のところでキャッシュできるようなら、CDNを利用するという選択肢もあります。
primary key、index、クエリの作りが悪くて速度に影響が出ることが多いとは思いますが、
そこをきれいにしただけでは、どうにもできないケースはあります。
そのような規模になった場合には、どこまでをデータベースで行い、どこまでをプログラムで行うのかを検討すべきです。
■まとめ
データベースの処理速度は、昔から開発者を悩ませてきたものと思いますし、
知識はあっても、思いもしないところで引っかかってしまったりと、本当に厄介な問題だと思います。
しかし、どこがボトルネックなのかを調べる、という入り口だけはほぼ共通で、
発行されているクエリの頭に「explain」をつけて実行してみる
という、たったこれだけです。
データベースの処理速度に問題が生じたときは、まずは、そのクエリがどれだけ
時間を要しているのか、分析してみてください。
最後までご覧頂き、ありがとうございます。
弊社では全国各地の請負い（ご自宅）で作業協力頂ける、フリーランスエンジニアの方を常時探しております。
ご興味ある方は、お気軽にお問い合わせ下さい。