Zenn
🕌

SQLクエリチューニング

2024/12/09に公開
SQL
tech

修正必要な箇所の特定方法

  • New Relic等によるトランザクション単位での処理時間計測
  • 出力されているスロークエリをチェックする

原因

  • 改善要なSQL
  • リクエスト数が多い為にSQL実行が多い→メモリを食ってTMP落ちが起き重くなっている
    • メモリを増やす（スケールアップ）
    • DBサーバの台数を増やす（スケールアウト）
    • キャッシュを利用する
      • 事前にバッチで実行したものをテーブルやRedis等に保存しておく
  • 抽出するデータ量が多く重くなっている
    • UI(仕様)の見直しでデータ量を抑えられないか（データの絞り込みを行えないか）
  • 実行計画が意図と異なる
    • ヒント句設定して強制する
      • 動的に変えられなくなるので他で問題が発生する可能性がある
    • データ量がある閾値を超えた→結合のアルゴリズムが変わり重くなっている（DBによって異なる）
      • Nested Loops(MySQLはこれのみなので変わることない)
      • Hash
      • Sort Merge
  • 同じテーブルへの並列更新が重い
    • インデックス貼られている場合リーフ分割起きると組み換え完了まで参照/更新ブロックされる
    • 更新タイミングをずらす
  • 新規登録/更新が重い
    • 不要なインデックスはないか確認→インデックスの更新が重くなる

改善要なSQLとは？

テーブルへのアクセス(I/O)を減らす

  • ウィンドウ関数で、GROUPBYの集計後の行単位のソート等が行える→結合が減らせるかも
  • 相関サブクエリは重い→結合やウィンドウ関数に置き換えられないか
    • 相関サブクエリは駆動表の行数分内部表を見にいくので重くなる
  • WITH句の活用：一時テーブル生成（メモリに保存）→結果を参照できるので複数箇所で利用する場合には処理・アクセス回数が減る

利用するメモリを減らす

TMP落ちすると重くなるのでメモリを節約する

  • 必要なカラムだけSELECT（*を使わない）

処理を減らす

  • 結合の駆動表はレコード数が少ない方にする
    • 結合条件にインデックスが効くこと前提

インデックス活用できるSQLになっているか

  • 中間LIKE(LIKE '%aa%')になっていないこと
  • 否定形にしない

マテリアライズド・ビューとデータマート

  • 重いSQLはマテリアライズド・ビューで実行結果をもたせておく方法もある
    • データが最新でなくてOKな場合のみ
    • 元のテーブルが更新されても自動反映はされない。定期的に更新要
    • インデックスもセット可能
  • データマート：SQL実行結果をテーブルに保存しておく（キャッシュ）
    • バッチで生成する

インデックスの貼り方

  • 有効なインデックスとは
    • 少ない行数に絞り込めるカラムに対してインデックスを貼ること
      • 全体の5-10%程度に絞り込めるとよい
  • 有効でなければテーブルフルスキャンの方が早いかも
    • 登録/更新が重くなるのでデメリットの方が大きくなる
  • ビットマップインデックスはカーディナリティが低い列に有効
    • 更新時のオーバヘッドが大きい
  • ハッシュインデックスは等値検索を高速化
    • 等値検索以外では効果薄、範囲検索では利用できない等用途が限定的
  • データの更新が多いテーブルはインデックスが断片化するので一定期間ごとにインデックスを再構築する

外部キー制約

CASCADE UPDATE,DELETEといったオプションにより
親テーブルの大量データの更新時、子テーブルに波及して重くなることがある為やらない方がよい

Discussion