MySQLパフォーマンスチューニングとは〜SQLチューニング（個別最適化）〜

MySQLのSQLチューニング（個別最適化）は、個々のクエリのパフォーマンスを向上させ、レスポンスタイムを改善することを目的としています。

この記事では、SQLチューニング（個別最適化）の主な概要と具体的な方法を紹介します。

パフォーマンスチューニングの概要は以下の記事で紹介しておりますので、ご参考ください。

SQLチューニング（個別最適化）とは
具体的な設定方法
まとめ

SQLチューニング（個別最適化）とは

SQLチューニングは、テーブル構成やクエリの最適化によってパフォーマンス改善を図ります。
主に以下の点に焦点を当てます。

データ型の見直し
インデックスの最適化
クエリの書き方の改善

具体的な設定方法

データ型の見直し

SQLチューニングにおけるデータ型の見直しのベストプラクティスは以下の通りです。

適切な数値型の選択
- 整数には INT, BIGINT などを使用し、小数には DECIMAL を使用します。
- 値の範囲に応じて、TINYINT, SMALLINT, MEDIUMINT などを適切に選択します。
文字列型の最適化
- 固定長の文字列には CHAR を、可変長には VARCHAR を使用します。
- VARCHAR の長さは実際のデータ長に近いものを選択し、過剰に長くしないようにします。
日付・時刻型の適切な使用
- 日付のみの場合は DATE, 時刻のみの場合は TIME, 両方必要な場合は DATETIME を使用します。
ENUM や SET の活用
- 限られた選択肢から値を選ぶ場合、ENUM や SET を使用してストレージを節約します。
テキストデータの適切な管理
- 大量のテキストデータには TEXT 型を使用し、必要に応じて TINYTEXT, MEDIUMTEXT, LONGTEXT を選択します。
浮動小数点数の扱い
- 精度が重要な場合（金額など）は FLOAT や DOUBLE ではなく DECIMAL を使用します。
ビット値の効率的な格納
- 0と1のみの値を格納する場合は BIT 型を使用します。
UNSIGNED の活用
- 負の値を扱わない数値データには UNSIGNED を付けて、正の値の範囲を広げます。
デフォルト値の設定
- 適切なデフォルト値を設定し、NULL 値の発生を最小限に抑えます。
インデックスとの関連性
- インデックスを付ける列のデータ型は、検索や結合の効率を考慮して選択します。

インデックスの最適化

インデックスの最適化に関する具体的なベストプラクティスは以下の通りです。

適切なカラムの選択
- WHERE句、JOIN条件、ORDER BY句で頻繁に使用されるカラムにインデックスを作成します。
- 選択性の高いカラム（ユニークな値が多いカラム）を優先してインデックスに含めます。
複合インデックスの効果的な設計
- 最も頻繁に使用される検索条件を左側に配置します。
- インデックスのカラム順序は、「等価条件 > 範囲条件」の順に設定します。
カバリングインデックスの活用
- クエリで必要なすべてのカラムをインデックスに含めることで、テーブルアクセスを回避します。
インデックスの断片化管理
- 定期的に断片化率を確認し、必要に応じて再構成または再構築を行います。
- 一般的に、断片化率が5-30%の場合は再構成、30%以上の場合は再構築を検討します。
不要なインデックスの削除
- 使用頻度の低いインデックスや重複するインデックスを特定し、削除します。
フィルター付きインデックスの活用
- 特定の条件に一致するデータのみにインデックスを作成し、インデックスサイズを削減します。
インデックスの使用状況の監視
- クエリプランを定期的に確認し、インデックスが適切に使用されているか確認します。
統計情報の更新
- インデックス統計を最新の状態に保ち、オプティマイザが適切な実行計画を選択できるようにします。
主キーの適切な設定
- クラスター化インデックスとなる主キーは、できるだけ狭く、単調増加する値を選択します。
インデックスのメンテナンス計画
- 業務への影響が少ない時間帯にインデックスのメンテナンスを実行するようスケジュールします。

クエリの書き方の最適化

SQLクエリの書き方改善に関する具体的なベストプラクティスは以下の通りです。

必要な列のみを選択
- SELECT * の使用を避け、必要な列のみを明示的に指定します。
- これにより、不要なデータの読み取りを減らし、I/Oを最適化します。
適切な結合方法の選択
- INNER JOIN、LEFT JOIN、RIGHT JOINなど、状況に応じて最適なJOINを選択します。
- 大きいテーブルを JOIN の左側に配置し、小さいテーブルを右側に配置します。
サブクエリの最適化
- 可能な限りサブクエリを避け、JOINを使用して効率的なクエリを作成します。
- 繰り返し同じサブクエリを使用することを避けます。
インデックスの効果的な利用
- WHERE句、JOIN条件、ORDER BY句で使用される列にインデックスを作成します。
LIMIT句の活用
- 大量のデータを並べ替える場合で、すべての結果が必要ない場合はLIMIT句を使用します。
複雑なクエリの分割
- 1つの複雑なクエリを、より小さく単純な複数のクエリに分割します。
- 一時テーブルやCTEを活用して中間結果を保存し、再利用します。
関数の使用を最小限に
- WHERE句での関数使用を避け、インデックスが効果的に使用できるようにします。
- 複雑な計算や変換は、可能な限りクエリの最後に配置します。
UNIONの代わりにUNION ALLを使用
- 重複除去が不要な場合、UNIONの代わりにUNION ALLを使用してパフォーマンスを向上させます。
EXISTS句の活用
- 大きなテーブルから少量のデータを取得する場合、IN句の代わりにEXISTS句を使用します。
クエリの統一とキャッシュの活用
- バインド変数を使用してSQL文を統一し、クエリキャッシュの効率を高めます。

これらのベストプラクティスを適用することで、SQLクエリのパフォーマンスを大幅に改善できます。
ただし、具体的な適用にあたっては、実際のデータ特性やシステム要件を考慮する必要があります。

まとめ

今回は、SQLチューニング（個別最適化）の主な概要と具体的な方法について紹介しました。

SQLの個別最適化を通じて、クエリの実行速度を向上させるだけでなく、リソースの最適活用にもつながります。

ご参考になれば、幸いです。

以上になります。ありがとうございました。

参考

https://proengineer.internous.co.jp/content/columnfeature/7104
https://qiita.com/micah/items/f45f7b73cf99454ef8e9
https://qiita.com/fururun02/items/e143ae87ec8a1c3884eb
https://nulogic.cc/articles/lu6p87c6xvc4
https://zenn.dev/waiwawaiwai/articles/f95829d2e2064b
https://style.potepan.com/articles/19460.html
https://tech-blog.cloud-config.jp/2022-07-11-sql-server-index/
https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16
https://dev.mysql.com/doc/refman/8.0/ja/optimization-indexes.html
https://qiita.com/SimonLee/items/04878ca0f071f096aedb
https://cloud.google.com/bigquery/docs/best-practices-performance-compute?hl=ja
https://qiita.com/ichi_zamurai/items/fdbe3872a505c22ee431