こんにちはiwanagaです。
今日はハロウィンですがよくよく考えるとそれ即ち今年も後2ヶ月という事になります。年々時間が過ぎるのは早いですね。何なら早すぎですけどね笑まだ今年食べた雑煮の味を覚えているのですがこのまま行くと気分的には毎週お雑煮みたいな気分になるのでしょうか。
今回もSQLを学んでいくのですがいつもと違う事をやりたいと思います。今までは知識的な事やクエリを学習してきましたが、自社の尊敬する上司Mr.IDさんから頂いたアドバイスでSQLの高速化、つまりいかにSQLの遅延を防げるかが今後の鍵になるとのことでしたので学習していこうかと思います！
確かに昨今どこでもデータベースには数万件レベルでデータがあります。なのでその膨大な量のデータを遅延なく抽出、上書きするにはどうすればいいか？次の項目で説明させていただきます！
そもそも遅れる理由は？
そもそも遅れる理由とは何か？
自分が知っている範囲では以下の理由がございました。
・大量のデータ処理
テーブル内に大量のデータがある場合、クエリの実行に時間がかかることがあります。これはシンプルに容量が増えれば遅くなるというだけなので対策としていらないデータを消去するなどの対策が取れます。
(まぁそんなに簡単に現場でデータをポンポン消せないんですけどね💦)
・ハードウェアの制約
データベースサーバーのハードウェアリソース（CPU、メモリ、ディスク速度など）が不足している場合、クエリのパフォーマンスに影響を与えることがあります。
・複雑なクエリ
複雑なJOINやサブクエリを含むクエリは、処理に時間がかかる可能性があります。不要なJOINを避け、必要なデータのみを取得するように心がけるとパフォーマンスが向上します。前回までサブクエリとJOINを学習してる身としては少し悲しいですが、抽出する際はただ複雑にして一発で抽出するだけが良いのではなく、前述のハードウェアの制約も含め仕事で使うデータベースのスペックや処理時間も踏まえて最適なクエリを使用することを心掛けるのが遅延対策になるのだと思います。
ここまでは何やかんや想像できますが、ここからが学習していくうちに知った理由でした。
・不適切なクエリのキャッシュ
言われてみれば確かにそうなのですがキャッシュが適切に設定されていない場合、クエリの実行に時間がかかることがあります。
・デッドロック
複数のトランザクションが同時にデータベースリソースにアクセスし、相互にロックをかけた場合、デッドロックが発生し、クエリが停滞することがあります。お互いがお互いを待ってしまっているのでダチョウ倶楽部見たいな事が起こっていると考えれば分かりやすいですね。なお、デッドロックはSQLに限らずIT業界ではよく行き交う言葉なので覚えた方がいい様です。
・不適切なインデックス設計
データベース内のテーブルに適切なインデックスが設定されていない場合、データの検索や結合が遅くなることがあります。適切なインデックスを設計することで、データベースのパフォーマンスを向上させることができるとの事です。
(なるほど...そもそもあんまりインデックスを理解していない💦)
なので次項からインデックスの学習をします。
インデックス
そもそもインデックスとは何か？一言で言うとテーブルに格納されているデータを高速に取り出す為の仕組みを意味します。インデックスは主に高速なデータ検索やデータの整合性維持、ソートの高速化の為にあり、前述でもある通りこのインデックスの設計が適切でないとSQLが遅くなってしまいます。
インデックスの構造について、本当は図を作成して説明したいのですが記入できないので文で軽く説明させていただきます。
・B-ツリーインデックス (B-Tree Index)
B-ツリーは、データベースで最も一般的に使用されるインデックス構造です。データが平衡木構造に保存され、高効率で範囲検索と精确検索をサポートします。形のイメージとしては根っこが枝分かれしているイメージです。
他にも色々ありますが自分であまり理解できていないところもありますので今度リベンジします。
SQLを早くする方法
今までは遅くなる原因でしたが今度は早くするコツを記載します。
・行数を数えるときは「COUNT(列名)」を使う
シンプルな話ですが行数を数えるだけなら全体が無くてもカラム一つで出来るのでおすすめです。
・キーワードは大文字
これは簡単です。SQLのキーワードをすべて大文字で書くだけです！理由は大文字で書くことでキャッシュを使う確率が上がるのと、SQLの高速化につながる上に、キーワードを大文字で統一することで検索キーワードなどに指定ができるのでデバッグにも役立ちます。下記に例文を記載します。
--悪い例
select * from テーブル where カラム = food;
--良い例
SELECT * FROM テーブル WHERE カラム = food;
・インデックスの再構築
インデックスの再構築を定期的に行い、インデックスの効率を保ちましょう。インデックスが膨れ上がってしまった場合などに有用です。
・ハードウェアのアップグレード
ハードウェアリソースが不足している場合、CPU、メモリ、ディスクなどのハードウェアをアップグレードすることでパフォーマンスを向上させることができます。これがまた意外と見落としがちで職場でみんなであーでもないこーでもないと考えてたらシンプルにPCをアップグレードするだけでほぼ改善される時も多々ありますのでご注意ください。灯台もと暗しです。
・極値関数(MAX / MIN)でインデックスを活用する
SUM関数などソート領域の使用は、パフォーマンス悪化の原因になりやすい一方、極値関数はソート処理の負荷を軽減可能な場合があります。単純に全部出すよりは負担軽減になりますよねという話です。
最後に
今回もご覧いただきありがとうございました！
やはり自分が使っていくSQLに関して使用方法だけでなく理屈もしっかりと押さえないとダメだなぁと痛感させられました。今後も精進していきます！
次回はデータベースの理屈について自社の尊敬する先輩Mr.ITさんから教えて頂いたことや自分が学習したことをまとめいきますのでよろしくお願いいたします。
参照
https://learning.zealseeds.com/contents/text/IPA/technology/database/transaction/exclusive-control/index.html
https://products.sint.co.jp/siob/blog/index
https://style.potepan.com/articles/26070.html
エンジニアファーストの会社 株式会社CRE-CO iwanaga