📘

社内向け SQLチューニング勉強会を実施しました

すずっきー

2023/10/02に公開

はじめのご挨拶

はじめまして。BEENOSの鈴木です。

普段はBEENOSグループのtenso株式会社でヘルプデスク業務に従事しておりますが、たまにサービス関連のデータベース、MySQLのチューニングや調査などもしております。

今回、普段から触っているMySQLのチューニング勉強会を実施しましたので、その内容を少し公開したいと思います。

勉強会を開催しようとしたきっかけ

tenso株式会社の開発チームには、SREチーム（運用チーム）があり、元々は私も所属しておりました。

SREチームに新規メンバーが参入してきたこともあり、改めてデータベースと向き合う人のために、まずはSQLのチューニングを覚えてもらいたいとの要望があり、開催することにしました。

また、BEENOS全体としても開発エンジニアがコードを書くだけでなく、コードに含まれているSQLがどのように動くかを把握しパフォーマンスの良いSQLを書くための手法を学ぶ機会になるかと思い、BEENOSエンジニア全体へ向けての勉強会として開催しました。

SQLチューニング勉強会

SQLチューニング勉強会の内容については、基本的なことを中心に実施しましたので、ここでは概要を簡単に記載致します。

EXPLAINとは？

EXPLAINの機能について簡単に説明しました。

監視から情報を拾って分析する

スロークエリの情報やシステム監視の状態から、どのような場合にEXPLAINを使ってSQLを分析する必要があるのかを説明しました。

実際にEXPLAINをかけてみよう！

項目1と2の内容を踏まえて、自分の操作できる環境で実際にEXPLAINをかけて、どのように結果が表示されるか、手を動かして頂きました。

SQLのオーバーヘッドを特定する

EXPLAINの実行結果を元に、SQLの動作が遅い原因がどこにあるのかアタリをつけて、オーバーヘッドを取り除くための対処方法を確定する流れの説明をしました。
今回はINDEXを作成して対応しました。

対策方法を確定する

4でアタリをつけたINDEXを作成するにあたり、INDEX作成前後でSQLを実行して、どのINDEXに効果があって、どの程度速度改善されるかを、実際の計測データとEXPLAINを元に読み解いてみました。

上記5項目で、基本的なEXPLAINの使用方法とSQL改善対策方法をお伝えしました。

勉強会の目的

EXPLAIN自体は非常に簡単に使うことができるのですが、開発中にEXPLAINを確認しながら開発するということをあまりしている方はいないかと思います。
（私が知らないだけかもしれませんが）
開発中またはリリース直後は問題なく動いていた画面が、日増しに遅くなっていくということは、開発者の方は一度は経験したことがあるのではないでしょうか？
現在はクラウド環境の発展により、様々な手法でサービスを展開できますが、その根幹のデータ保存場所としてはRDBMSは多く使われていると思います。
その上で、RDBMSのパフォーマンスを最大限に活かす手法として、SQLの最適化やINDEXによる高速化は外せないチューニング作法になると思います。
今回の勉強会は、このような開発中には見えなかった、リリース後の運用フェーズに入ったときにまずはじめにやってほしいこととして、困ったらとりあえずEXPLAINかけてみよう！と考えて頂けるようになれば嬉しいなぁという思いを込めて開催しました。

勉強会のもう一つの目的

勉強会を実施してほしい！とお願いされたときの目的は、上記「勉強会の目的」で記載した通りですが、この勉強会の内容にはもう一つの目的も含めてみました。
勉強会の項目4の中に、あえてチューニングの失敗例を記載しています。
勉強会ではこの項目を、単純に「銀の弾丸はない」という形で締めてしまっていますが、この「銀の弾丸はない」というのは、「EXPLAINとINDEX作成だけではRDBMSのパフォーマンスを本質的に改善できない」という意味を含めています。

これについては、一般的に言われているテーブルあたりのレコード数の限界（1テーブル100万レコード以上になると性能劣化すると言われている）が一番わかりやすい障壁となるためです。
弊社の環境にも1テーブルのレコードが数千万行のテーブルは存在します。
このテーブルに対して、いくら最適なINDEXを使ったSQLを作っても、高速なレスポンスは得られないのです。

こういったケースが発生した場合は、SQLのチューニングではパフォーマンス改善できないのです。

ではどのような形でチューニングするか？となりますが、一番シンプルな対応としては、SQLを実行するテーブルのレコード数を100万以下に抑えることとなります。
例えば、ECサイトで特にメインのトランザクションテーブルになりやすい、注文データのテーブルなどは日々データが溜まっていきます。
このテーブルのレコード数が少ないうちは、注文テーブルからユーザ毎の過去の注文履歴を取り出すのも容易ですが、1年間運用して500万レコードずつ増えていくような場合は、注文履歴を表示する画面が3ヶ月もすれば徐々に重くなっていき、1年経ってみると数秒待つ画面になってしまう、というような事になりかねません。

こういった場合、RDBMSだけで対応する場合は、

注文データテーブルのデータ構造を元に、年ごとの注文履歴を表示するためのテーブルを作成し、定期的に更新（追記）する処理をする
注文履歴テーブルを、ユーザの会員IDの下1桁を利用してデータをパーティショニングする

のような形を取ることで、年間500万レコードのテーブルへのSQLが、ユーザID毎にパーティショニングされた1テーブル50万レコードの注文履歴テーブルへのSQLへ変更できます。
単純にテーブル上のデータのスキャンも、1度の画面表示時の負荷が10分の1に削減できる上に、性能劣化しづらい100万レコード以内のテーブルへのSQLへの改善も見込めます。

ただし、上記は改善策の1例でしかなく、他にもいくつか改善する方法はあるかと思います。
RDBMSだけで改善する必要はなく、他の仕組みを組み合わせる効果の高い改善策もあるかと思います。

前置きが長くなってしまいましたが、ここでお伝えしたいことは、
「EXPLAINはRDBMSにおいてツールの1つでしかなく、最も容易な調査手段の1つではあるので、まずは日常的に使う習慣をつけてほしい」
ということと、
「プロダクトが育っていく過程で、SQLをいくらチューニングしても追いつかないところまでプロダクトが育つ前に、SQL以外でのチューニングにも目を向けられるようになってほしい」
という、2つになります。

特に、DBAのような専門職を置いていない環境の場合、ここに気が付けないままプロダクトが成長してしまうと、あとから改善していくのには大きな工数がかかってしまいます。
早いうちに手を打って改善していく、DevOpsの目線の一つとして、プロダクトが健全に成長していくのを見守れるようになって頂きたいという思いも込めています。

最後に

データベースはなま物です。
そして、プロダクトと一緒に日々成長していきます。
人が手をかけて面倒を見てあげることで、健全な状態を維持できると思っています。
皆様が育てているデータベースは、きっと愛情を込めてメンテナンスすることで、その愛情に応えてくれると思います。
プログラムと同様に、RDBMSにも愛を込め、大きなプロダクトへの成長を願い、皆様がシステムと共に成長できることを願っております。