🙆‍♀️

MySQLが遅いと思ったときにやること

2022/12/22に公開

1.はじめに

今までは技術記事はQiitaに書いていたけど、今回zennデビューということで、第1回はMySQLのパフォーマンス改善について書いて見ます。
MySQLで検索や更新などを行っているときに「このクエリ遅いなー」と思ったときにどういう流れで解決を図るか、自分なりの定石（？）です。
ちなみに、MySQLのバージョンは8.0を前提に記載しています。

2.スロークエリログ

まずはやはりこれ。でもそもそも出力される設定になっていないと見ることもできません。なので、出力される設定にしておきましょう。
設定ファイル（Windowsならmy.ini、MacまたはLinuxならmy.cnf）の[mysqld]のセクションに以下の設定を入れる必要があります。

slow_query_log=ON
long_query_time=2
slow_query_log_file=slow.log

1つ目はスロークエリログ出力の有無で、ONにする必要があります。
2つ目は実行に何秒以上かかったらスロークエリとするかの判定基準です。どれくらいにするかはDBをどういう使い方をするかにもよるけど、自分は大体2〜3秒くらいが多いかも。
3つ目は出力するログファイルの名前。ここはわかりやすい名前にするのがいいでしょう。

スロークエリログの見方については、こちらあたりを参考にして見てください。
絶対見るのは実行したSQLとかかった時間（Query_time、Lock_time）で、それ以外に探索したレコード数、ヒットしたレコード数とか状況に応じて必要な情報を、といったところでしょうか。

3.EXPLAIN

スロークエリログの次に見るのがEXPLAIN。MySQL Workbenchなどで遅くなっているSQLの前にEXPLAIN をつけて実行すると、実行計画（クエリをどうやって実行するか）を出力してくれます。
実行計画の見方はこちらあたりが参考になると思います。
例えば、typeのところにALLやindexがあったらインデックスがうまく使われていないんだなとかがわかると思います。

4.インデックス

上記に書いたインデックスですが、さすがに今時インデックスを全く貼らないという人はあまりいないと思いますが、それでもただ闇雲に貼ればいいというわけではありません。
例えば、TRUEまたはFALSEのみしか入らないカラムに貼ってもほとんど意味はないです。後、値がよく変更されたり新たな値が入ったり消されたりするカラムに貼るのもお勧めできないです（全体的に重くなるので）。
貼るべき箇所はレコードによって一意またはいろいろな値が入るカラムで、かつ検索条件やORDER BYのソートキー、あるいはJOINの結合条件になることが多いカラムに貼るのがいいでしょう。

後、カラムに対して何らかの演算を行なったり、後方一致や中間一致（前方一致はOK）のLIKEで検索を行う場合はインデックスが効かなくなるので注意が必要です。
あるカラムに何らかの演算を行うようなSQLを頻繁に実行する場合は演算結果をGenerated Columns（仮想列）としてテーブルに持っておいて、それにインデックスを貼るやり方もあります。Generated Columnsについてはこちらを参考にして見てください。

5.その他クエリチューニング

よく言われるのが、

SELECT * FROM CUSTOMER;

のようにワイルドカードを使うのではなく、

SELECT id, name, age FROM CUSTOMER;

のように必要なカラムだけを指定するとかです。
後はJOINする時は結合対象のテーブルをWHEREで絞り込んでから結合するとかサブクエリが複雑になりすぎている時は一旦バラしてサブクエリを使わずにできるか検討するとかでしょうか。

6.パラメータチューニング

スロークエリログのところでも出てきたmy.iniまたはmy.cnfのパラメータでパフォーマンス改善に寄与できるところはいくつかあります。
例えばinnodb_buffer_pool_sizeを大きくしてメモリにキャッシュできるサイズを大きくする（マシンのメモリサイズの7〜８割が目安と言われています）などがあります。
こちらの記事に詳しい情報が記載されています。

7.終わりに

こんな感じでスロークエリの改善と一言で言っても、いろいろなアプローチがあります。自分もまだまだSQLチューニングについては勉強することがあると実感しますが、手を加えて遅いクエリが早くなった時は何とも言えない充実感があります。
皆さんも高速化手法を覚えて、MySQLライフを楽しみましょう！