ALHアドベントカレンダー５日目
ALH株式会社のアドベントカレンダー、５日目です！
https://qiita.com/advent-calendar/2020/alhinc
弊社では去年もアドベントカレンダーを実施していて、自分は「GASを好きなエディタでコーディング」という題で記事を書きました。
地味〜にLGTMが伸びていてニーズはどこに転がってるかわからんな〜、って実感してます。
https://qiita.com/wol/items/c8633f706c96fc4daaab
書くこと
エンジニアとして働き始めて丸3年が経ちました。
業務の中で「なんかDB遅いな〜。調べておいて」みたいな雑なフリをされた中で学んだ、基本的なDBチューニングについてザックリとした観点を紹介しようと思います。
基本的にはSQL中心です。
前提として
パフォーマンスが出ないのは本当にDBが原因でしょうか？
アプリケーションにおけるパフォーマンス低下の要因は多岐に渡ります。
回線、アプリケーションサーバ、DBサーバ、アプリケーションロジック、フロントのレンダリング速度。ぱっと思いついただけでもこれだけあるので、自分の認識していない部分も含めて本当に多くの要因が考えられると思います。
まずはボトルネックの特定のために適切な手段で計測を行い、ボトルネックを特定しましょう。
（お医者さんが検査もせずいきなり手術するようなもんですね）
本題
すごい頑張ってる
欲しいデータをSQLでまとめて取ろうとした結果、結合と抽出を際限なく行う超大型クエリが生み出されていることがあります。
なぜ遅い？
JOIN自体は便利で素晴らしいものなのですが、JOINするテーブルが巨大だと往々にして処理が遅くなります。複数テーブルのJOINとなると、巨大なテーブルが混ざる可能性も高まりリスク↑。性能試験がおざなりだと、運用始まってレコード増えてきたら…という時限爆弾。
SQLという観点からは外れますが、適切に正規化してテーブルをコンパクトにしたり、アプリケーション側に結合処理を任せてみると良いかもしれません。
ナンニデモ=インデックス
DBパフォーマンスを語る上で外せないインデックスのお話。
使わないインデックスほど足を引っ張るものはありません。
なぜ遅い？
インデックスが増えると、特にINSERT/UPDATE/DELETEのパフォーマンスに影響を及ぼします(オーバーヘッド)。1件1件の処理速度をみれば大した問題ではないかもしれませんが、バッチ処理の場合は話が別。1ミリ秒でも早いに越したことは無いので大量のインデックスを見つけたら警戒しましょう。
男気インデックス
あまりの潔さにインデックスがほとんど張られていない状態。やむにやまれない事情でこうしているイメージ。
なぜ遅い？
そもそもインデックスが無いとどうなのかと言うと、見出しが無いということ。見出しがないと1ページ目からパラパラめくって探すのでそりゃ遅いよって感じですね。インデックスを貼るとオーバーヘッドが発生すると言いましたが、オーバーヘッドよりインデックスを貼ることによる恩恵の方が大きいのかどうかを考慮してインデックスを設定しましょう。
例えばカーディナリティが低い(候補値が少ない)カラムに対してインデックスを張っても、データを絞り込み切れないので効果が非常に薄いということになります。こういう場合はインデックスは張らない方が良い可能性もあります。
インデックスを有効活用していない
SQLを実行した結果、インデックスが効いていないパターン
なぜ効かない？
- LIKEを使った中間/後方一致
インデックスは見出しなので前方一致でないと機能しない - WHEREの左辺に関数や、計算式を設定している
これはインデックスを張る時に関数を指定してやれば解消することもあります - 暗黙の型変換
intのカラムに対して抽出条件に文字列を指定するとRDBMSが型変換を行ってクエリ実行してくれるのですが、この変換が行われるとインデックスを使用しません - 否定の使用
まだあったと思うのですが、とりあえずこの辺りはインデックス効かないので別の方法で書けないかどうか検討してみましょう。
社内の他システムからDBの設定値を流用
結構あるんですが、社内の他サービスの設定をそのまま流用しているパターン。
なぜ遅い？
具体的にこの数値！みたいなのは出せませんが、システムによってテーブル構成は違うわ、レコード数も違うわ、DBリソースは異なるわ、発行するクエリも別物だわなので、他のシステムのDB設定はあくまで参考に留めておくべきと考えています。
設定値変更→テストを細かく回して最適な設定を少しづつ探るのがなんだかんだ近道だと思います。
まぁそれが難しいんですよね
終わりに
なんだかとっ散らかってしまいましたが、まずはこの辺りを確認するようにしています。
あとは実行計画取ったり、スロークエリを調べたりですね。（どちらかというとこっちを先に確認すべき！）
コネクションの問題もあったりで、DB沼の底はとても深そうです。
正直、DBに関しては浅瀬でチャプチャプしているレベルなのでこれを機にしっかりと腰を据えて向き合ってみようと思います。
余談
色々偉そうに書きましたが、自分はここにあるアンチパターンのほとんどを体験済みです。
やはり人は痛みを伴うことで学ぶ生き物・・・