🤖

【MySQL】ざっくりパフォーマンスチューニング

2021/03/19に公開

MySQL

最適化

パフォーマンスチューニング

tech

あまりDB周りの仕事はしたことがないので、パフォーマンスチューニングについて調べた

パフォーマンスチューニングの流れ

遅いクエリの抽出
実行計画の確認
最適化(クエリ、インデックス、テーブル構成）

遅いクエリの抽出

遅いクエリをログに出力できる。まずは１秒以上に設定するのが良い。

// 0のときはログを取得しない。1のときはログを取得
mysql> set global slow_query_log = 1;

// ログファイルの出力先
mysql> set global slow_query_log_file = '/var/log/mysql/slow.log';

// ログに出力するSQLの実行時間(秒単位)。
mysql> set global long_query_time = 5;

// フルスキャンやフルインデックススキャンが行われているSQLを出力
mysql> set global log_queries_not_using_indexes = 1;




$ ll /var/lib/mysql/
mysql-slow.log

なお、logファイルはmysqldumpslowコマンドw使うと集計された状態で取得できる

slowlogの見方

Rows_examined（フェッチしている件数）が大きいSQLをピックアップ

mysqldumpslowの見方

Count（回数）が少なく平均Time（実行時間）が長いものをピックアップ

実行計画の確認

抽出したSQLをEXPLAINコマンドで実行

各項目の見方

こちらから引用、https://ameblo.jp/principia-ca/entry-11923272810.html
要するにインデックスが適切に利用されているかを確認する。

ここにDEPENDENT SUBQUERYやUNCACHEABLE SUBQUERYが出ていたら要注意。DEPENDENT SUBQUERYはインデックスチューニングで改善できる可能性がありますが、UNCACHEABLE SUBQUERYが出ている場合は読んで字のごとくキャッシュできないのでインデックスどうこうではなく、クエリ自体を見なおしたほうが良い.
type
対象テーブルへのアクセス方法を表示しますが、indexとALLが出たら要注意です。indexは一見良さそうですがインデックスのフルスキャンなので遅く、ALLはテーブルのフルスキャンなのでさらに遅いです。上の解析結果はindexなのでチューニングの余地がありますね。
key
クエリに使用されたインデックスが表示されます。意図したインデックスが使われていないということもあるので確認します。NULLの場合はインデックスが使われていません。
key_len
使用されているキーの長さ（バイト）です。キー長は短いほうが良しとされますが、user_id(int),status(int)で作成した複合インデックスが使われているのに、key_lenが4だとuser_idまでしか利用されていないということがわかります。インデックスが使われているのにスロークエリが出ている場合、このパターンが多いです。データタイプごとに必要な容量は公式ドキュメントをチェケラーしましょう。
rows
検索に読み取る必要がある推定レコード数が表示されます。インデックスが使われていようとここが数万を超えるようだとスロークエリとして出力されることが多いです。上の解析結果では480万行と明らかに多いことがわかります。まずはここを減らせるようにインデックスを作成します。
Extra
注意すべきはUsing filesortとUsing temporaryです。Using temporaryはソート時のテンポラリテーブルが必要な場合に表示されますが、極力少ないほうが良いです。Using filesortはインデックスを利用しないクイックソートなので大抵遅くなります。レコード件数が多ければ多いほど致命的になるのでインデックス作成が必須になってきます。後述しますが、Using IndexはCovering Indexが利用されている場合に表示されます

最適化

クエリ

相関クエリはJOINで書き換える。
インデックスが効かないクエリを直す

関数、or条件、否定構文、式、LIKE検索は避ける。
一行ずつのインサートではなく、バルクインサートする

インデックス

インデックスを貼るべきカラム:カーディナリティ（キーの値の数と全レコード数の比率）が高いカラム
インデックスの基本はWHERE句の順番どおりに作成
インデックスが張れたら、再度explainをおこなって効果を確認.

参考：http://memorandum-plus.com/2018/02/19/mysql-インデックスを貼ってクエリをチューニングし/

また、インデックスを追加すべきものについても、
一概には言い切れないのですが、一般的に以下のように言われています
一意性が高いカラムに設定する
表が大規模
WHERE句やJOIN句で頻繁に使用されるカラムに設定する
外部キーに使われる

複合インデックスがあるが使われないケースもあるので注意
参考：http://tech.aainc.co.jp/archives/4634

テーブルに複合インデックスがある場合、オプティマイザではインデックスの左端の先頭部分のいずれかをレコードの検索に使用できます。たとえば、(col1, col2, col3)に 3 カラムのインデックスがある場合、(col1)、(col1, col2)そして(col1, col2, col3)に対して、インデックスの検索機能を使用できます。

テーブル構成

（調査中

参考：

https://www.unitrust.co.jp/1665

公式