実行計画の解析とインデックス活用
クエリのボトルネックを特定するには、EXPLAINステートメントを用いた実行計画の読み取りが不可欠です。以下の構文でクエリがどのように処理されるか事前にシミュレーションできます。
EXPLAIN SELECT product_id, stock_qty, category FROM inventory WHERE region_code LIKE 'KANTO_%';出力結果の主要カラムは以下の役割を持ちます。
- id：SELECT句の実行順序を示します。値が同じ場合は並列実行、異なる場合は上から順に実行されます。
- select_type：クエリの種類を分類します。単純なSELECTは
SIMPLE、サブクエリやUNIONを含む場合はPRIMARYやUNIONとして区別されます。 - key：オプティマイザが選択した実際のインデックス名。インデックススキャンが期待通りに動作しているか確認できます。
- rows：実行計画に基づく推定走査行数。この数値が小さいほどI/Oコストが低減されます。
データアクセスパターンとクエリ構造の再設計
パフォーマンス向上の第一歩は、不要なデータ転送とスキャン範囲の最小化です。
- 取得データ量の削減：
SELECT *を避け、必要なカラムのみ指定します。WHERE句でフィルタリングを徹底し、頻繁に実行される同一クエリはアプリケーション側でキャッシュ機構を活用します。 - サーバー側スキャン行数の抑制：適切なインデックス設計により、全表走査（Full Table Scan）を回避します。
大規模クエリの分割処理
単一の複雑なクエリを一括実行すると、長時間の行ロック発生、Undo/Redoログの肥大化、システムリソースの枯渇を招き、他の軽微なトランザクションをブロックする可能性があります。バッチ処理や集計クエリは、適切な件数ごとに分割して逐次実行することで、ロック保持時間を短縮できます。
JOIN処理のアプリケーション層への委譲
複数のテーブルを結合するクエリは、単表クエリに分解し、取得結果をアプリケーションコード上でマッピングする手法が有効です。
- クエリキャッシュのヒット率が向上し、メモリ効率が改善されます。
- ロック範囲が個別テーブルに限定され、デッドロックやコンテンションのリスクが低減します。
- データベースのシャーディングや分散構成への移行が容易になり、システムのスケーラビリティが確保されます。
- 各単表クエリの実行計画が単純化され、オプティマイザの負担が軽減されます。
インデックス有効化の条件とアンチパターン
以下の記述パターンは、インデックスが機能せず全表走査をトリガーする主要原因となります。
- 否定形条件の回避：
NOT INや<>演算子はインデックススキャンをスキップします。SELECT * FROM user_profiles WHERE department_id NOT IN (10, 20, 30); - 前方一致ではないLIKE検索：ワイルドカードが先頭にある場合、B-Treeインデックスのトラバーサルが不可能です。
SELECT * FROM product_catalog WHERE sku_code LIKE '%_DISC'; - カーディナリティの低いカラムへのインデックス作成：性別やステータスフラグなど、データの偏りが大きいフィールドは、インデックス作成による恩恵が少なく、むしろ更新コストを増加させます。
- NULLデフォルト値の非推奨：NULL許容カラムはインデックス統計の精度を下げます。可能な限りNOT NULL制約とデフォルト値を設定します。
- インデックス付きカラムへの関数・演算適用：カラム値に対して計算を行うと、インデックスが使用されなくなります。
SELECT * FROM employee_records WHERE YEAR(hire_date) = 2021;→ 範囲条件（
hire_date BETWEEN '2021-01-01' AND '2021-12-31'）に書き換えます。 - 複合インデックスの最左プレフィックス原則：
(status, category, created_at)のインデックスは、WHERE status = ?またはWHERE status = ? AND category = ?で有効ですが、categoryのみでは走査できません。 - 単一行取得時のLIMIT明示：ユニークキー検索などで取得行数が1つであることが明らかな場合、
LIMIT 1を付与することでオプティマイザが早期にスキャンを停止し、コストを削減できます。 - 暗黙的型変換の防止：異なるデータ型との比較は、データベースがカラム側の値を全件変換するためインデックスが無効化されます。
SELECT * FROM orders WHERE order_no = 1005 OR order_no = '2040';→ カラム定義に厳密に合わせたリテラル型を使用します。
- JOINキーのデータ型一致：結合する両テーブルの外部キー/主キーは、データ型（INT vs VARCHARなど）と照合順序を完全に一致させる必要があります。
SQL実行のライフサイクル
クライアント側フロー
- アプリケーションがSQL文字列を生成
- コネクションプールから利用可能な接続を取得
- 確立されたTCPセッションを通じてクエリを転送
データベースサーバー側フロー
- スレッドハンドラがリクエストを受け付け、セッションコンテキストを初期化
- キャッシュ層で結果の有無を確認（構成により動作）
- パーサーが構文解析を行い、セマンティックツリーを生成
- オプティマイザがコストモデルに基づき最適実行計画を策定
- エクスキュータが計画に従いストレージエンジンAPIを呼び出し
- インデックス検索またはテーブルスキャンにより行データを取得
- フィルタリング・ソート・集計を適用し、クライアントへ結果セットを返却
InnoDBのデータ更新と永続化プロセス
トランザクションの整合性とクラッシュリカバリを保証するため、InnoDBはWrite-Ahead Logging (WAL) と2フェーズコミットを採用しています。
- 対象ページがメモリ上に存在しない場合、ディスクから読み出してBuffer Poolに配置
- 変更前のレコードバージョンをUndo Logに記録し、MVCCとロールバック機能を実現
- Buffer Pool上のメモリデータを変更
- Phase 1（Redo Log）：物理的な変更履歴をRedo Logバッファに書き込み。設定された
innodb_flush_log_at_trx_commit戦略に従いディスクへフラッシュ - Phase 2（Binary Log）：論理的な変更イベントをBinlogバッファに記録。レプリケーションとポイントインタイムリカバリに使用され、同期設定に応じてディスクへ出力
- 両ログの書き込みが正常完了した時点で、トランザクションにCommitマークを付与。メモリ上のダーティページはチェックポイント処理により随時ディスクへ書き戻される