MySQLのチューニングについてまとめる

こんにちは、こがです。

今回はメモがてらMySQLのチューニングについて、自分で調べながらメモとして記事を書いていこうと思います。
また、色々とチューニング方法を書いていきますが、動作環境はこれ!っていうのはありません。
たぶん現在の環境で動いているものであれば動くとはおもいますが。

SQL・テーブル設計周りのチューニング

スロークエリを見つける

まずはじめに行うべきはスロークエリになっている部分の改善でしょう。

スロークエリを見つけるにはMySQLの設定が必要です。
my.cnfに以下の設定をし、再起動します。
また、ログの保存先やスロークエリとするlong_query_timeの値は適宜変えてください。

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

こうして書き出したログはmysqldumpslowコマンドを利用することで集計することが出来ます。
例えば時間を元に集計する場合は-s tをつけて以下のようにします。

mysqldumpslow -s t /tmp/mysql-slow.log

参考リンク:
Mysql slow queryの設定と解析方法
http://masayuki14.hatenablog.com/entry/20120704/1341360260

実行計画を確認する

スロークエリを割り出したことで、直していくべきクエリがどれか判明しました。
しかし、そのクエリがどうして重たいのか?などは分からない場合も多くはありません。
その場合にはEXPLAINを利用して、MySQLの実行計画を確認してみると良いでしょう。

SELECT * FROM t1; // こんなSQLが有ったとして
EXPLAIN SELECT * FROM t1; // 先頭にEXPLAINをつけるだけ

この結果を見ることでインデックスはしっかり利用されているか?無駄に行フェッチをしていないか?
など色々な問題点を確認することが出来ます。

参考リンク:
MySQLのEXPLAINを徹底解説!!
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html

インデックスを適切に利用する

MySQLではインデックスを利用する上でいくつかの制限があります。
その制限をちゃんと把握しておくことが必要になります。

1. インデックスは同時に一つしか使われない

例えばプライマリキー/インデックスA/インデックスBがあるとき、
インデックスAとインデックスBはもちろんのこと
プライマリキーとインデックスAや、プライマリキーとインデックスBを同時に利用してくれない。

2. インデックスのカラムの順番とWHERE句で指定されているカラムの順番が一致している必要がある

インデックスにcol1,col2の順で設定されている場合にはWHERE句で指定する順番もcol1,col2の順番でないとインデックスを利用してくれない。

上記の注意点以外にもInnoDBはクラスタインデックスであることや、セカンダリインデックスにはクラスタインデックスののキーが含まれています。

InnoDBのクラスタインデックスについて

参考リンク:
MySQL with InnoDB のインデックスの基礎知識とありがちな間違い
http://techlife.cookpad.com/entry/2017/04/18/092524

カバリングインデックス

必要なデータをインデックスに含まれるように設定しておくことでデータのフェッチを早くする手法です。
注)むやみにインデックスにデータを追加するのは逆にパフォーマンスの低下を招きます

参考リンク:
MySQLのインデックスを学ぶ (1)
http://d.hatena.ne.jp/a666666/20100920/1284992435

遅いJOINを速くする

JOINはよく遅いと言われます。それはなぜでしょうか。

Nested Loop Join

MySQLでは簡単に言うと、このNLPしか実装されていません。
では、Nested Loop Joinはどのような仕組みで動いているのでしょうか。

Nested Loopというくらいですから入れ子のループが回って動いています。
テーブルAの一行をフェッチして、テーブルBからマッチングするものを探して、またテーブルAの一行をフェッチして・・・・を繰り返していきます。
計算量としてとても大きくなりそうだというのはすぐ分かって頂けると思います。
テーブルAが100行増えればテーブルBを100回走査しないといけなくなりますからね・・・。
いわゆる計算量OがテーブルA×テーブルBであることがおわかり頂けると思います。

つまり速くするためには、このかけ算の結果を小さく出来れば良いという形になります。

実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法
https://qiita.com/yuku_t/items/208be188eef17699c7a5

パーティショニング

データを1カ所ではなく、特定の条件に基づいて振り分けを行い別々に保存する仕組みです。
上手に設定を行うことでパフォーマンスを上げることが出来ます。
またログデータなど一部のデータをまとめて消したりする際にも役に立ちます。

ただし、振り分けを行う為には条件とするデータがプライマリキーに含まれている必要があります。

MySQL パーティショニングまとめ
https://qiita.com/taroshin/items/608076c9f8e09497c4b1

MySQLの設定によるチューニング

メモリ周りの設定を行う際には、グローバルバッファとスレッドバッファの違いに気を付けましょう。
また、確保量が実際のメモリをオーバーしないように注意しましょう。

InnoDBの設定

innodb_file_per_table
InnoDBのデータをテーブルごとにディスクに保存します。
テーブルごとにデータが記録されるのでパフォーマンスが上がります。

innodb_buffer_pool_size
InnoDBにおいて、データとインデックスを保持するバッファです。
メモリ全体の80%程度と良く言われます。

innodb_log_file_size
書き込みが高負荷のシステムでは大きくすると良い。
このファイルがいっぱいになると更新データのディスクへのフラッシュが行われる。

クエリキャッシュの設定

query_cache_type
クエリキャッシュを行うタイプ

0:キャッシュしない
1:全ての参照系のクエリをキャッシュする(SELECT SQL_NO_CACHEで始まるクエリはキャッシュしない)
2:SELECT SQL_CACHEで始まるクエリをキャッシュする

query_cache_size
クエリキャッシュするメモリ量

参考リンク:
雑なMySQLパフォーマンスチューニング
https://www.slideshare.net/yoku0825/mysql-57449062

MySQL パフォーマンスチューニング
http://momota.github.io/blog/2017/04/20/mysql/

5分でできる、MySQLのメモリ関係のチューニング!
http://dsas.blog.klab.org/archives/50860867.html

MySQL クエリキャッシュの概要と導入・評価方法
https://weblabo.oscasierra.net/mysql-query-cache/

終わりに

MySQLの設定チューニングに関してはとりわけ大事な部分にとどめました。
多くの部分は参考リンクでも語って頂いてるのでそちらにお任せするのが良いと判断しました。

ただ、MySQLのチューニングもそうですがそれよりもアプリ側でのテーブル設計やSQLが重要だと思っています。
ここに書いたことを少しでも多くの方の役に立ったらなと思います。

今日はこのへんで。ではでは。