1. インデックス参照時のテーブルバックトラック現象

補助インデックス（二次インデックス）を使用した検索において、必要なデータを取得するために一度クラスター化インデックスへ戻る処理を「テーブルバックトラック」と呼びます。このプロセスでは、まず補助インデックス木から主キー値を検索し、その値を用いて再びクラスター化インデックス木を走査して実際の行レコードを読み取るため、全スキャンではなくとも複数の B+ 木へのアクセスが必要となり、処理コストが高まります。

このオーバーヘッドを回避するための最適化アプローチとして、カバリングインデックスの利用が有効です。これは、クエリで選択する列すべてをインデックス定義内に含むことで、追加のテーブル参照を不要にする手法です。

• 単一インデックスの場合:
  対象となる補助インデックスに含まれるカラム（例：`staff_name`）および主キー（例：`staff_id`）のみを SELECT 文で指定します。
• 複合カラムが必要な場合:
  複数のカラムにまたがる条件や取得が必要な場合は、これらを含む複合インデックスを作成します（例：`(staff_name, hire_date)`）。ただし、過度に幅広なインデックスはメンテナンス負荷を増大させるため、設計上のバランス考量が必要です。

# カバリングインデックス活用による SELECT 例
# staff_idx が (name, id) の複合インデックスであると仮定
SELECT staff_name, staff_id FROM staff_table WHERE staff_name = 'Tanaka';

2. クエリ解析用のログ収集設定

MySQL は、指定した閾値を超える実行時間を要する SQL ステートメントを記録する機能を持っています。これを遅延クエリログと呼び、ボトルネック特定のための重要な手段となります。デフォルトでは無効になっていることが多く、手動でのオンボードが必要です。運用環境では常にオンにしておくわけではなく、調査時や負荷テスト時に有効化し、収集後に解析を行います。

2.1 設定ファイルの変更とパラメータ解説

/etc/my.cnf または環境に応じた設定ファイル内の [mysqld] セクションで以下のパラメータを設定します。

[mysqld]
slow_query_log = 1                      # ログ出力の有効化 (ON/OFF でも可)
slow_query_log_file = /var/log/mysql/slow_queries.log  # 出力パスの指定
long_query_time = 1.5                   # 閾値秒数 (デフォルトは 10 秒程度)
log_queries_not_using_indexes = 1       # インデックス未使用クエリの検出も記録可能
min_examined_row_limit = 50             # スキャン行数が少ない場合は記録除外など

設定変更後、現在の状態を確認するには SHOW VARIABLES LIKE '%slow_query_log%'; を発行します。セッション単位で動的に変更したい場合は、以下のように設定可能です。

SET GLOBAL long_query_time = 1;

2.2 ログファイルの解析ツール活用

生のログファイルをテキストエディタで目視確認するのは非効率です。標準提供されている mysqldumpslow ツールを使用することで、出現頻度や平均時間を基にソートが可能です。

# 最も頻繁に出現したクエリトップ 5 を表示
mysqldumpslow -s c -t 5 /var/log/mysql/slow_queries.log

# 平均実行時間が長いクエリトップ 5 を表示
mysqldumpslow -s at -t 5 /var/log/mysql/slow_queries.log

さらに高度な解析を行うには、Percona Toolkit に含まれる pt-query-digest や専用分析ツール mysqlsla を導入検討することも推奨されます。目的は、実行回数と待機時間の両方が多い悪質クエリを特定することです。

3. EXPLAIN による実行計画の可視化

EXPLAIN ステートメントは、実際にデータを取得せずに、データベースがどのようにその結果を取得するかという計画を表示します。これはチューニングの基礎情報となります。

主要フィールドの説明

• id: SELECT クエリの順序番号を示します。ネストしている場合、数値が大きい順から先に評価される傾向があります。
• select_type: クエリの複雑さを示します。SIMPLE は単純、PRIMARY は最外層、SUBQUERY はサブルーチン処理などを指します。
• table: 現在アクセス対象となっているテーブル名です。
• type: アクセス方法の種類です。性能が高い順に並べると system > const > eq_ref > ref > range > index > ALL となります。最低でも range 以上を目指すべきです。
• key: 実際の使用されたインデックス名が表示されます。NULL の場合はインデックス未使用を示唆します。
• rows: インデックスまたはテーブル統計に基づき推定された、結果を出すためにスキャンする必要がある行の概算数です。ここを減らすことがチューニングの本質です。
• Extra: 補足情報が表示されます。Using index は良い状態ですが、Using temporary や Using filesort はパフォーマンス低下の危険信号です。

判断基準の例

EXPLAIN SELECT order_id FROM orders WHERE status = 'completed';

上記のような単純な問い合わせにおいても、もし type が ALL となっていれば、テーブル全体がスキャンされておりインデックスが機能していない可能性があります。

4. 遅延クエリの実践的改善策

システム全体のレスポンスが低下した際、以下のロジックに従って原因を切り分けます。

1. ネットワーク要因の排除: サーバー間の通信遅延がないか確認。
2. データ量の妥当性: 巨大なテーブルに対して適切な範囲指定を行っているか。
3. インデックスの実装状況: 特定の WHERE 条件にインデックスが存在せずフルスキャンが発生していないか。実行プランの確認で判断可能。
4. 接続数の限界: 最大接続数（max_connections）に達していないか。
5. ロック競合: デッドロックや長時間の排他ロックが他トランザクションを阻害していないか。

具体的な最適化の手順としては、まず SQL_NO_CACHE を付けて実際の処理時間を計測します。次に、WHERE 句で絞り込み率が最も高い（区分度が一番高い）列からインデックスを検討します。特に ORDER BY と LIMIT を併用するケースでは、ソート対象となるテーブルの順序を調整したり、索引用のインデックスを付与したりする必要があります。

企業環境では、大型 SQL 文を機能的な小さなモジュールに分割し、それぞれが最適なインデックスを利用できるように改修するのが一般的です。開発者と DBA が協働し、不要なフルスキャンクエリを削減することが重要です。ただし、例えば性別のような分散度の低い列へのインデックス作成や、データウェアハウスのような大量読込が中心の用途での過剰インデックスは、逆に書込時のオーバーヘッドを生むため見極めが必要です。

# インデックス適用前の確認例（重複値が少ないかチェック）
SELECT COUNT(DISTINCT customer_category) FROM customers;

# 低速化したクエリの特定（プロセスリスト参照）
SHOW FULL PROCESSLIST;

# 索引構築のタイミング
-- 通常はトラフィックの少ない時間帯に行う
ALTER TABLE transactions ADD INDEX idx_transaction_date (transaction_date);