MySQLのパフォーマンスチューニング
InnoDBの概要
MySQLは内部でInnoDBというエンジンを持っています。
このエンジンがトランザクションやロックといった機能を持ちます。
これらの機能はACIDという特性を満たしていて、データの整合性を担保する役割を担っています。 また、InnoDBではB+ Treeというデータ構造を採用していて、これによりインデックスを張る事ができるようになり検索の高速化を実現しています。
インデックスについて
インデックスはいわゆる本の索引のようなもので、本をすべて調べなくても索引を調べることで、どこにほしいデータがあるのかを素早く見つけ出すことができます。
MySQLにおいて、このインデックスはB+ Treeというデータ構造によって実現されています。
なぜインデックスがあると速いのか
簡潔に説明すると計算量が少なくなるためです。
次の図のように、B+ Treeの場合は木構造になっていて、ルート（4）から順にデータを辿っていくことで目的のデータを素早く見つけ出せます。
https://gyazo.com/716c4a5099063ca7fec4650cf8ba368e
計算量について
O(n)はn回の計算が必要という意味
O(log n)は2^ステップ数回の計算が必要という意味
例えばO(log 4)の場合、計算回数は2、つまり2^2(ステップ数)となる
上図の例に例えると、7を見つけるのにB+ Treeの場合は4 -> 6 -> 7と2回の計算が必要に対して、そうじゃない場合は1 -> 7と6回の計算が必要
B+ Treeの概要
B+ Treeは大きくルートノードと子ノード(下図では中間ノード)、リーフノードで構成されています。
https://image.itmedia.co.jp/ait/articles/0501/18/r12fig_03.gif
リーフノードがキー値（インデックス）と実データを持ち、以下の特徴があります。
キー値の順でソートされる
リーフノード同士がポインタでつながっている
これにより、範囲検索が高速になります。
例えば 4 ~ 7 の範囲の値を抽出するには 4 のリーフノードを見つけた後、次のリーフノードをポインタで辿っていくだけで済みます。
B+ treeについて、より詳細な説明はこちらの記事を参考してください。 クラスタインデックス
MySQLではPrimary Keyがインデックスになります。これをクラスタインデックスと呼びます。
先程のリーフノードの説明の通り、このクラスタインデックスはソートされていて、実データが格納されています。
Primary Keyがない場合、NOT NULLのUNIQUEなカラムがクラスタインデックスとして使われます。
更に両方がない場合は内部で行IDをクラスタインデックスとして使われます。
http://2.bp.blogspot.com/_3l-X4JQ1EX4/TKpt3auAJ1I/AAAAAAAAAd0/Hs4Oz4ASZtU/s1600/C:%5Cfakepath%5Cinnodb_clust_index.png
セカンダリインデックス
Primary Key以外のインデックスのことをセカンダリインデックスと呼びます。
セカンダリインデックスはリーフノードに実データを持たず、クラスタインデックスへのポインタを持ちます。
そのため、クラスタインデックスの検索より遅いです。
http://4.bp.blogspot.com/_3l-X4JQ1EX4/TKpuV6PFlmI/AAAAAAAAAd4/-xSSBaFDU38/s1600/C:%5Cfakepath%5Cinnodb_second_index.png
カバリングインデックス
検索対象のデータがセカンダリインデックスで完結できる場合はPrimary Keyを更にルックアップする必要がなくなるため検索が早くなります。
https://www.softel.co.jp/blogs/tech/wordpress/wp-content/uploads/2015/10/covering-index.png
例として上図の様にSELECT対象のデータ（item_id, item_type, item_price）がセカンダリインデックスにすべて含まれているため、クラスタインデックスをルックアップしなくなります。
クエリ条件によりますが、カバリングインデックスを使用可能の場合は使いましょう。
DBチューニング方法
チューニングする際に、大きく分けて以下のやり方があります。
1. MySQLのパラメータチューニング
2. SQLの改善
SQL発行回数
3. テーブル定義の改善
インデックス
パーティション
性能アップが一番見込めるのは2と3になります。
特に3ははインデックスを張ることで検索速度が劇的に変わる場合があります。
スロークエリの絞り込み
MySQLには設定したしきい値を超えた実行時間のクエリをログに出力するスロークエリという機能があります。
confファイルに以下の設定を追加することで、ログを出力できます。
code:conf
...
slow_query_log=1 // 1で有効化
slow_query_log_file=/var/log/mysql/slow.log // 出力されるログファイルのパス
long_query_time=1 // しきい値の秒数
これにより、チューニングすべき対象を絞り込めます。
code:log
# Time: 2021-11-15T05:52:53.252192Z
# Query_time: 8.049209 Lock_time: 3.098439 Rows_sent: 1 Rows_examined: 1
use sample_table;
SET timestamp=1636955573;
select * from sample_table where id = "bs24qup8d3b3ueut8cle";
実行計画の確認
チューニング対象を絞り込んだところで、インデックスを張っていくにあたりEXPLAINを使って実行計画について調べます。
実行計画は統計情報を元にMySQLがどのように検索を行うかを判断した結果です。
それを見ることで発行するクエリがインデックスを使うのか、またはフルスキャンするのかが分かります。
なお、統計情報と実際のデータの分布が異なることがあるため、必ずしも実行計画通りにクエリが実行されるとは限りません。
TODO: 実例
主な項目の説明
select_type
SELECTの種類、SIMPLEはサブクエリやUNIONがない単純なSELECT
partitions
パーティション分けをしている場合は照合するパーティション一覧、それ以外はnull
type
レコード検索の方法
ALL はテーブルフルスキャンのため、改善すべき
indexはインデックスを使った検索
possible_keys
選択可能なインデックス
インデックスが使われていないときは、使用可能なインデックスを選択するようにクエリを見直す
key
実際選択されているインデックス
<null>の場合はインデックスが使われいない
rows
テーブルからフェッチされる行数（推定値）
Extra
Using index
データアクセスにインデックスを使用する場合、一番速い
Using where
データをフェッチした後に、whereを使って更に絞り込みをする場合
基本的にインデックスが使われていない状況は改善すべき点なので、
その様な結果が出た場合はクエリとテーブル定義を見直します。
実行計画についてより詳細な情報はこちらの記事を参照してください。 クラスタインデックス
インデックスがないため場合は検索はフルスキャンになるため、検索時間がかかります。
また、for updateなどの排他ロックを使用するっとテーブルロックが掛かってしまうため、ロック待ち時間が発生して遅くなる場合もあります。
実際にテーブルを使って動作検証をしていきます。
TODO: テーブル定義
EXPLAINで実行計画を見てみると、typeがALLになっているためフルスキャンで対象データを検索するのが分かります。
TODO: EXPLAINの結果
実際に100万件からidを指定して1件を検索してみると約6秒ほどかかっています。
インデックスが効いていない状態なので、idカラムにクラスタインデックスを追加して改善されるかを確認します。
見ての通り、クラスタインデックスが効いているため、検索にほぼ時間が掛かっていない結果になりました。
セカンダリインデックス
id以外も検索条件に含まれることが多々あるのため、セカンダリインデックスを追加することで検索速度を改善されることがあります。
インデックスを追加するのは一般的にこのセカンダリインデックスのことを指しています。
TODO: 実例
しかし実際に計測してみると、インデックスが効いているにも関わらず遅くなっています。
TODO: 実例
インデックスを張ると遅くなる原因の一つとしてカーディナリティが低い場合があります。 カーディナリティはカラム値の種類の多さという意味で、低い（つまり種類が少ない）ほどインデックスの効果が薄く、かえって遅くなることがあります。
実際にxxxxxxxは4種類しかないためカーディナリティが低いのが分かります。
そのため、インデックスを張るカラムのカーディナリティを意識して選択する必要があります。
しかし、カーディナリティが低いけど、検索条件として使用したい場合は後述するパーティションを使用することで改善される場合があります。
TODO: 実例
複合インデックス
復数カラムが検索条件になることがほとんどなので、その場合は複合インデックスを張ることで速度が改善される場合があります。 次の通り、複合インデックスを張った結果、5倍ほど早くなったことを確認できます。
TODO: 実例
インデックスや複合インデックスの詳細についてはこちらの記事を参照してください。 カバリングインデックス
必要なカラム情報がWHERE句の条件に含まれている場合、カバリングインデックスが効くので、
必要なカラムのみSELECTすることで速度が改善される場合があります。
TODO: 実例
パーティション
カーディナリティが低いけど、検索条件として使用したいカラムがある場合はパーティションを分けることで速度が改善される場合があります。
簡潔に書くとパーティションはテーブルのデータを小分けして、対象のパーティションを指定することでスキャンするデータ量を減らす仕組みです。
なお、パーティション分けても速くならないことがあるので、実際検証してい速くるかを確認する必要があります。
TODO: 実例
上記の結果の通り、パーティションを分けることで速度が改善されたことが分かります。
パーティションを解除するには次のクエリを実行します。
TODO: 実例
クエリ発行数
xormを使って実装を行う際に、改善できる点として発行するクエリ数を減らす例があります。
頻繁に実行されるクエリでかつ、復数回クエリを実行してデータを整形する様な処理の場合は、
適宜にJOIN句を使用してクエリ数を減らすことで速度が改善される場合があります。
次の例は、同一テーブルの別カラム値を上書きするの改善例となります。
BAD
code:go
var address string
err := db.Cols("old_address").Where("id = ?", id).Get(&address)
if err != nil {
// error handling
}
user := User{
OldAddress: address,
}
_, err = db.Where("id = ?", id).Cols("old_address").Update(&user)
if err != nil {
// error handling
}
GOOD
code:go
condition := builder.
Update(builder.Eq{"old_address": builder.Expr("address")}).
From("users").
Where(builder.Eq{"id": id})
if_, err := db.Exec(condition); err != nil {
// error handling
}
SHOW PROFILE