MySQL - パフォーマンスチューニング

提供: MochiuWiki : SUSE, EC, PCB

概要

MySQLのパフォーマンスチューニングは、データベースアプリケーションの応答速度とスループットを向上させるための重要なプロセスである。

チューニングは、インデックス最適化、クエリの改善、InnoDB設定の調整、実行計画の分析など、複数の領域にわたる。
適切なチューニングにより、同一ハードウェアでも数倍から数十倍のパフォーマンス向上が実現可能である。

MySQL 8.0では、オプティマイザヒント、ヒストグラム統計、EXPLAIN ANALYZEなど、新たなチューニング機能が追加されている。
一方で、クエリキャッシュはMySQL 8.0で完全に廃止されており、代替手段への移行が必要となる。

測定と分析を繰り返し、ボトルネックを特定してから対策を実施することが、効果的なチューニングの基本方針である。

関連する設定パラメータの詳細は MySQL - 設定ファイル を、クエリ構文の最適化は MySQL - SELECT を参照すること。


チューニングの基本方針

パフォーマンスチューニングは、測定と分析に基づいた体系的なアプローチが必要である。

測定と分析の重要性

チューニングの第1歩は、現状のパフォーマンス測定である。
測定なしに最適化を行うと、効果のない変更や逆効果の変更を実施するリスクが高まる。

主な測定対象として、クエリ実行時間、リソース使用状況、テーブル統計が挙げられる。
クエリ実行時間は、Slow Query Logで閾値を超えるクエリを記録し、Performance Schemaで全クエリの統計情報を収集する。
リソース使用状況は、CPU使用率、メモリ使用量、ディスクI/Oを監視し、SHOW GLOBAL STATUS で各種カウンタを確認する。
テーブル統計は、SHOW TABLE STATUS でテーブルサイズと行数を確認し、INFORMATION_SCHEMA.TABLES で詳細統計を取得する。

ワークロードの分類

データベースのワークロードを分類し、最適化の方向性を決定する。

MySQLの環境別特性と最適化対策
環境 / ワークロード 特性 重要事項 推奨対策
OLTP (Online Transaction Processing) 短時間の読み書きトランザクションが多数発生 同時実行性とレスポンスタイム インデックス最適化、バッファプール設定
OLAP (Online Analytical Processing) 大規模なSELECT、集約、分析クエリが中心 スループット ソート用メモリ増加、REDOログサイズ増加
混合ワークロード OLTPとOLAPが混在 ワークロード特性の分離 読み取りレプリカの分離、パラメータの優先順位調整


チューニングの優先順位

効果の大きい順にチューニングを実施する。
各段階で測定を行い、効果を検証してから次の段階に進む。

推奨順序を以下に示す。

  1. インデックスの最適化
    最も効果が大きく、リスクが低い。フルテーブルスキャンの削減が最優先である。
  2. クエリの最適化
    不適切なJOIN、サブクエリ、ソートを改善する。
    EXPLAINで実行計画を確認する。
  3. InnoDB設定の調整
    バッファプールサイズ、REDOログ、I/O設定を調整する。
    サーバ全体のパフォーマンスに影響する。
  4. オプティマイザヒント
    特定のクエリの実行計画を調整する。
    インデックスとクエリ最適化で解決できない場合のみ使用する。



インデックスの最適化

インデックスは、データベースパフォーマンスに最も影響を与える要素である。

インデックスの種類

MySQLでは複数のインデックス形式がサポートされている。

MySQLインデックスタイプの比較
インデックスタイプ 対応エンジン 対応検索形式 特性
B-Tree InnoDB、MyISAM 等値検索、範囲検索、ORDER BY、GROUP BY MySQLのデフォルトインデックス形式
リーフノードはデータポインタまたはPRIMARY KEYを保有
ハッシュ MEMORY 等値検索のみ 範囲検索、ORDER BY、GROUP BYには使用不可
フルテキスト InnoDB、MyISAM (MySQL 5.6以降) 自然言語検索、ブール検索 MATCH() AGAINST()構文で使用
VARCHAR、TEXT、CHARカラムに作成可能
空間インデックス InnoDB (MySQL 8.0以降) GIS検索 GEOMETRYカラムに作成
地理情報システム検索に使用


OLTP環境では、B-Treeインデックスが最も一般的に使用される。

複合インデックスの設計

複数カラムにまたがるインデックスは、適切に設計することで効果が最大化される。

複合インデックスの最左プレフィックスルール
インデックス定義 使用可能な検索 使用不可な検索
(A, B, C) Aのみ、(A, B)、(A, B, C) Bのみ、Cのみ、(B, C)


複合インデックスのカラム配置順序
配置ルール 説明 具体例
等値条件を左に配置 等値条件 (=) を左に、範囲条件 (<, >, BETWEEN) を右に配置 WHERE A = 10 AND B > 20 AND C = 30の場合、(A, B, C)ではCが使用されない。
一方、WHERE A = 10 AND B = 20 AND C > 30の場合、(A, B, C)が全て使用される。


複合インデックスの選択度設計
設計項目 説明 具体例
カーディナリティの高いカラムを左に配置 選択度の高いカラム (異なる値が多い) を左に配置すると、絞り込み効果が大きい 性別 (2値) より顧客ID (数十万値) を左に配置する


設計例を以下に示す。

 CREATE INDEX idx_order ON orders(customer_id, order_date, status);


このインデックスは、
WHERE customer_id = 123、WHERE customer_id = 123 AND order_date >= '2026-01-01'、 WHERE customer_id = 123 AND order_date >= '2026-01-01' AND status = 'shipped'
に効果的である。

一方、WHERE order_date >= '2026-01-01'、WHERE status = 'shipped' には効果が薄い。(最左プレフィックスが使用されない)

複合インデックスは、単一カラムインデックスの組み合わせよりも効果的な場合が多い。

カバリングインデックス

カバリングインデックスは、SELECT句で必要な全カラムがインデックスに含まれている状態を指す。

テーブル本体へのアクセスが不要となり、ディスクI/Oを大幅に削減できる。
ランダムアクセスからシーケンシャルアクセスに変換される。

EXPLAINのExtraカラムに Using index と表示される。

設計例を以下に示す。

 CREATE INDEX idx_covering ON users(last_name, first_name, email);


以下のクエリがカバリングインデックスで実行される。

 SELECT first_name, email FROM users WHERE last_name = 'Smith';


InnoDBでは、全てのセカンダリインデックスに PRIMARY KEY が自動的に追加される。
例えば、INDEX(A, B) は実際には INDEX(A, B, PRIMARY_KEY) として機能する。
PRIMARY KEY もカバリングインデックスに含めることができる。

カバリングインデックスは、頻繁に実行されるクエリに対して効果が大きい。

プレフィックスインデックス

長い文字列カラムに対して、先頭の一部のみをインデックス化する手法である。

構文例を以下に示す。

 CREATE INDEX idx_prefix ON articles(title(20));


この例では、title カラムの先頭20文字のみがインデックス化される。

メリットとして、インデックスサイズが削減され、ディスク使用量とメモリ使用量が減少する。
インデックススキャンが高速化する。

制約として、ORDER BY / GROUP BYには使用できない。(プレフィックスのみでソート順が確定しないため)
カバリングインデックスには使用できない。(完全なカラム値がインデックスに含まれないため)

適切なプレフィックス長の決定例を以下に示す。

 SELECT
   COUNT(DISTINCT LEFT(title, 10)) AS prefix_10,
   COUNT(DISTINCT LEFT(title, 20)) AS prefix_20,
   COUNT(DISTINCT LEFT(title, 30)) AS prefix_30,
   COUNT(DISTINCT title) AS full_column
 FROM articles;


選択度 (異なる値の比率) が十分に高い最小のプレフィックス長を選択する。
VARCHAR(255) 以上、TEXT、BLOBカラムでの使用が推奨される。

インデックスの管理

インデックスは定期的に見直し、不要なものは削除する。

インデックスの確認は、以下のコマンドで実行する。

 SHOW INDEX FROM <テーブル名>;


未使用インデックスの検出は、以下のクエリで実行する。

 SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'database_name';


このビューは、Performance Schemaのイベントデータに基づいて未使用インデックスを検出する。

インデックスの削除は、以下のコマンドで実行する。

 ALTER TABLE <テーブル名> DROP INDEX <インデックス名>;


削除前に不可視インデックス機能でテストすることを推奨する。
削除後に再作成するとテーブルロックが発生する可能性があり、大規模テーブルでは再作成に長時間を要する。

インデックスのメンテナンスとして、OPTIMIZE TABLE でインデックスの断片化を解消する。
InnoDBではテーブル全体の再構築が行われるため、大規模テーブルでは長時間を要する。
メンテナンスウィンドウで実行する。

ANALYZE TABLE でインデックス統計を更新する。
オプティマイザが最新の統計情報を使用するために必要であり、データ分布が大きく変化した後に実行する。

不可視インデックス (MySQL 8.0)

不可視インデックスは、インデックス削除前の影響検証に使用する。

インデックスを不可視化する構文を以下に示す。

 ALTER TABLE <テーブル名> ALTER INDEX <インデックス名> INVISIBLE;


インデックスを可視化する構文を以下に示す。

 ALTER TABLE <テーブル名> ALTER INDEX <インデックス名> VISIBLE;


オプティマイザがインデックスを無視するため、クエリの実行計画にインデックスが含まれなくなる。
パフォーマンスへの影響を検証可能である。
インデックスのメンテナンスは継続されるため、INSERT / UPDATE / DELETE 時にインデックスは更新される。

また、いつでも可視化して即座に使用可能である。

オプティマイザヒントで不可視インデックスを強制使用する構文を以下に示す。

 SELECT /*+ USE_INDEX(<テーブル名> <インデックス名>) */ * FROM <テーブル名> WHERE ...;


セッション変数で不可視インデックスを有効化する構文を以下に示す。

 SET SESSION optimizer_switch = 'use_invisible_indexes=on';


不可視インデックスは、本番環境でのインデックス削除のリスクを軽減する。


EXPLAIN による実行計画の分析

EXPLAIN は、クエリの実行計画を表示し、パフォーマンス問題を特定する。

EXPLAINの基本

基本構文を以下に示す。

 EXPLAIN SELECT * FROM users WHERE last_name = 'Smith';


EXPLAIN の出力には複数のカラムが含まれる。
下表に、主要なカラムの意味を示す。

EXPLAIN出力カラム
カラム 説明
id SELECT識別子。数値が小さいほど外側のクエリ。同じidは同時実行される
select_type SIMPLE (単純なSELECT、サブクエリなし)、PRIMARY (外側のクエリ)、SUBQUERY (サブクエリ)、DERIVED (派生テーブル、FROM句のサブクエリ)、UNION (UNION の2番目以降のSELECT)
table 参照されるテーブル名。派生テーブルの場合は <derived2> のように表示
type アクセスタイプ。パフォーマンスに最も影響。後述の「typeカラムの最適化」を参照
possible_keys 使用可能なインデックス候補。NULLの場合はインデックスが存在しない
key 実際に使用されるインデックス。NULLの場合はインデックスが使用されない
key_len 使用されるインデックスの長さ (バイト単位)。複合インデックスでどこまで使用されているかを示す
ref インデックスと比較されるカラムまたは定数。const (定数)、database.table.column (他テーブルのカラム)
rows 読み込む推定行数。実際の行数とのズレが大きい場合は統計情報の更新が必要
filtered WHERE句で絞り込まれる行の割合 (%)。100%に近いほど効率的
Extra 追加情報。後述の「Extraカラムの注意点」を参照


JSON形式での出力は、以下の構文で実行する。

 EXPLAIN FORMAT=JSON SELECT ...;


JSON形式では、コストや実行時間の推定値など、より詳細な情報が表示される。

EXPLAIN ANALYZE (MySQL 8.0.18以降)

EXPLAIN ANALYZE は、実行計画と実際の実行統計を表示する。

構文を以下に示す。

 EXPLAIN ANALYZE SELECT * FROM users WHERE last_name = 'Smith';


実際の実行時間 (ミリ秒単位) が各ステップに表示されるため、ボトルネックの特定が容易である。

実際の行数が表示され、推定行数 と 実際の行数 を比較することができる。
もし、ズレが大きい場合はヒストグラム統計の作成を検討する。

ループ回数として、ネステッドループの実行回数が表示される。

クエリが実際に実行されるため、UPDATE / DELETE では実際にデータが変更される。
そのため、本番環境での使用は注意が必要である。
実行時間が長いクエリでは完了まで待機するため、テスト環境での使用を推奨する。

EXPLAIN ANALYZE は、オプティマイザの推定と実際の実行のギャップを可視化する。

type カラムの最適化

typeカラムは、テーブルアクセスの方法を示し、パフォーマンスに直接影響する。

下表に、アクセスタイプの優劣 (上から順に高速) を示す。

アクセスタイプの優劣
type 説明
system システムテーブル (1行のみ) への参照。最速だが稀
const PRIMARY KEY または UNIQUE KEYでの等値検索。最大1行のみが該当。例: WHERE id = 123 (id がPRIMARY KEY)
eq_ref JOINでPRIMARY KEY または UNIQUE KEYによる結合。各行に対して最大1行が結合される。例: JOIN ON t1.id = t2.id (id がPRIMARY KEY)
ref 非ユニークなインデックスでの等値検索。複数行が該当する可能性がある。例: WHERE last_name = 'Smith' (last_name にインデックス)
range インデックスを使用した範囲検索。BETWEEN、IN、<、>、<=、>= で使用。例: WHERE id BETWEEN 100 AND 200
index インデックス全体スキャン。テーブル全体スキャンより高速だが、大規模インデックスでは遅い。カバリングインデックスの場合に発生しやすい
ALL フルテーブルスキャン。最も遅い。大規模テーブルでは避けるべき。インデックスが存在しないか、オプティマイザが使用を選択しなかった


最適化の目標として、ALL を range または ref 以上に改善する。
インデックスの追加または既存インデックスの利用を検討する。

rangeをref以上に改善する場合は、範囲条件を等値条件に変更できる場合は変更する。
ref を eq_ref に改善する場合は、JOINカラムを PRIMARY KEY または UNIQUE KEY に変更する。

typeがALLの場合は、possible_keys を確認してインデックス追加を検討する。

Extraカラムの注意点

Extra カラムには、実行計画の追加情報が表示される。

下表に、パフォーマンス問題を示す値を示す。

パフォーマンス問題を示すExtra値
Extra 説明 対策
Using filesort ディスク上でのソートが発生
ORDER BY句のカラムがインデックスに含まれていない。
メモリ内で完結しない場合はディスクI/Oが発生		 ORDER BYカラムをインデックスに含める
sort_buffer_size を増加させる。
Using temporary 一時テーブルが作成される。
GROUP BY、DISTINCT、UNION、サブクエリで発生
メモリ内一時テーブルはディスク一時テーブルより高速		 GROUP BYカラムをインデックスに含める
tmp_table_size と max_heap_table_size を増加させる。
Using where WHERE句でのフィルタリングが発生
インデックスで絞り込めなかった条件を後処理
必ずしも問題ではないが、行数が多い場合は最適化を検討		 インデックスの追加または改善


下表に、パフォーマンス向上を示す値を示す。

パフォーマンス向上を示すExtra値
Extra 説明
Using index カバリングインデックスが使用される。
テーブル本体へのアクセスが不要
ディスクI/Oが大幅に削減
Using index condition Index Condition Pushdown (ICP) が使用される。
インデックススキャン時にWHERE条件の一部を評価
テーブル本体へのアクセス回数が減少


下表に、その他の値を示す。

その他のExtra値
Extra 説明
Using join buffer (Block Nested Loop) JOINバッファが使用される。
結合カラムにインデックスがない場合に発生
MySQL 8.0.18以降はハッシュJOINが使用される
Using join buffer (hash join) ハッシュJOINが使用される。(MySQL 8.0.18以降)
Block Nested Loopより高速
Impossible WHERE WHERE条件が常に偽

例: WHERE 1 = 0


Using filesort と Using temporary が同時に表示される場合は、最優先で最適化する。


クエリの最適化

適切なクエリ構造は、インデックス以上にパフォーマンスに影響する。

SELECT文の最適化

基本的なSELECT文の最適化手法を以下に示す。

SELECT * を避け、必要なカラムのみを指定する。
不要なカラムの転送がネットワーク帯域を消費し、カバリングインデックスが使用できなくなる可能性がある。
例えば、SELECT id, name, email FROM users (必要なカラムのみ抽出) のように記述する。

LIMITでスキャン行数を制限する。
結果セットのサイズを制限することで、パフォーマンスが向上する。
例えば、SELECT * FROM logs ORDER BY created_at DESC LIMIT 100 のように記述する。

ただし、全行スキャン後にLIMITが適用されると効果が薄い。
ORDER BY カラムにインデックスがあれば効果的である。

DISTINCT は、必要な場合のみ使用する。
これは、DISTINCTはソートまたはグループ化を伴うからである。

インデックスがない場合は、Using temporaryが発生する。
GROUP BYで代替できる場合は、GROUP BYを使用する。

WHERE句でカラムに関数を適用しない。
例えば、WHERE YEAR(created_at) = 2026 はインデックスが使用されない。
代わりに、WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' と記述すると、インデックスが使用される。

関数ベースインデックス (MySQL 8.0) を使用する場合は、以下のように記述する。
この場合、WHERE YEAR(created_at) = 2026 でインデックスが使用される。

 CREATE INDEX idx_year ON logs ((YEAR(created_at)));


JOINの最適化

JOINは、複数テーブルの結合時にパフォーマンスが低下しやすい。

WHERE句で最も絞り込めるテーブルを駆動表にする。
オプティマイザが自動的に選択するが、適切でない場合もある。
STRAIGHT_JOIN または JOIN_ORDERヒントで順序を指定する。

ON句のカラムにインデックスを設定する。
駆動表のJOINカラムにはインデックス不要 (全行スキャンされるため)。
被駆動表のJOINカラムには必ずインデックスを設定する。
例えば、SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id の場合、customers.id (PRIMARY KEY) にはインデックスが存在する。

Batched Key Access (BKA) は、JOINバッファを使用してランダムアクセスを削減する。
デフォルトで無効であり、有効化するには以下のように設定する。

 SET optimizer_switch = 'batched_key_access=on,mrr=on,mrr_cost_based=off';


Multi-Range Read (MRR) の有効化が必要である。
ランダムアクセスが多いJOINで効果的である。

ハッシュJOIN (MySQL 8.0.18以降) は、インデックスがないJOINで自動的に使用される。
Block Nested Loopより高速である。
join_buffer_size がハッシュテーブルのサイズに影響する。
EXPLAINのExtraに Using join buffer (hash join) と表示される。

サブクエリの最適化

サブクエリは、適切に使用しないとパフォーマンスが低下する。

相関サブクエリは、外側のクエリの各行で実行される。
例えば、以下のクエリでは、customer_id ごとにサブクエリが実行される。

 SELECT * FROM orders o
 WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id);


行数が多い場合は極めて遅い。

JOINへの書き換えにより、サブクエリが1回のみ実行される。

 SELECT o.* FROM orders o
 JOIN (SELECT customer_id, AVG(amount) AS avg_amount FROM orders GROUP BY customer_id) AS avg_orders
 ON o.customer_id = avg_orders.customer_id
 WHERE o.amount > avg_orders.avg_amount;


EXISTSの使用により、INよりEXISTSが効率的な場合がある。

 SELECT * FROM customers c
 WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);


EXISTSは最初の一致で評価を終了する。
INはサブクエリの全結果を評価する。
MySQL 8.0ではINも半結合最適化される。

派生テーブルの最適化 (MySQL 8.0) により、派生テーブルがマテリアライズされる。
MySQL 8.0では派生テーブルのマージまたはマテリアライゼーションが自動的に選択される。

ソートの最適化

ORDER BYは、インデックスが使用されない場合にfilesortが発生する。

ORDER BYカラムをインデックスに含めることで、EXPLAINのExtraに Using filesort が表示されない。
例えば、CREATE INDEX idx_order_date ON orders(order_date) を作成し、SELECT * FROM orders ORDER BY order_date を実行する。

複合インデックスでのソートでは、ORDER BYカラムがインデックスの左から連続している必要がある。
例えば、INDEX(A, B, C) は ORDER BY A, B または ORDER BY A, B, C に使用可能である。
ORDER BY B, C または ORDER BY A, C には使用できない。
WHERE句とORDER BY句の組み合わせでは、INDEX(status, order_date) は WHERE status = 'shipped' ORDER BY order_date に使用可能である。

ソートバッファサイズとして、sort_buffer_size を増加する。
デフォルトは256KBである。
セッション単位で設定可能であり、SET SESSION sort_buffer_size = 1048576; (1MB) のように記述する。
メモリ内でソートが完結する場合はディスクI/Oが発生しない。
大きすぎるとメモリ不足を引き起こす可能性がある。

ソート方向として、昇順と降順の混在は最適化が困難である。
例えば、ORDER BY A ASC, B DESC はインデックスが使用されにくい。
MySQL 8.0では降順インデックスがサポートされており、CREATE INDEX idx ON table(A ASC, B DESC) のように記述する。

GROUP BY / DISTINCT の最適化

GROUP BYとDISTINCTは、一時テーブルまたはソートを伴う。

GROUP BYカラムをインデックスに含めることで、インデックススキャンのみで完結する。
例えば、CREATE INDEX idx_status ON orders(status) を作成し、SELECT status, COUNT(*) FROM orders GROUP BY status を実行する。

複合インデックスでのグループ化では、GROUP BYカラムがインデックスの左から連続している必要がある。
例えば、INDEX(A, B, C) は GROUP BY A, B または GROUP BY A, B, C に使用可能である。
GROUP BY B, C または GROUP BY A, C には使用できない。

DISTINCTは内部的にGROUP BYに変換される。
インデックスが使用できる場合は高速である。
例えば、SELECT DISTINCT status FROM orders (status にインデックス) のように記述する。

一時テーブルサイズとして、tmp_table_size と max_heap_table_size を増加する。
デフォルトは16MB (tmp_table_size)、16MB (max_heap_table_size) である。
メモリ内一時テーブルがディスクに書き出される閾値であり、両方を同じ値に設定する必要がある。
大規模なGROUP BYで効果的である。


InnoDB パフォーマンス

InnoDBは、MySQLのデフォルトストレージエンジンである。

バッファプール

バッファプールは、InnoDBの最も重要なメモリ領域である。

innodb_buffer_pool_size のデフォルト値は128[MB]である。
専用データベースサーバでは、物理メモリの70〜80[%]を割り当てることが推奨されており、例えば、16[GB] RAMサーバでは12[GB] (12884901888バイト) 程度を設定する。

サーバの再起動なしで動的に変更可能であり、以下のように設定する。

 SET GLOBAL innodb_buffer_pool_size = 12884901888;


下表に、バッファプール関連パラメータを示す。

バッファプール関連パラメータ
パラメータ デフォルト値 説明
innodb_buffer_pool_size 128MB バッファプールの合計サイズ
innodb_buffer_pool_instances 8 (MySQL 8.0以降) バッファプールのインスタンス数
複数インスタンスで並行性を向上
推奨: 最低1[GB]/インスタンス、つまり8[GB]バッファプールで8インスタンス
innodb_buffer_pool_chunk_size 128MB (134217728バイト) バッファプールの最小割り当て単位
動的リサイズ時にチャンク単位で追加 / 削除される。


制約として、バッファプールサイズは innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances の倍数である必要がある。

再起動後のウォームアップ時間を短縮するために、以下の設定を行う。

 innodb_buffer_pool_dump_at_shutdown = ON
 innodb_buffer_pool_load_at_startup = ON


この設定により、シャットダウン時にバッファプールの内容がダンプファイル ib_buffer_pool (データディレクトリ内) に保存され、起動時に自動復元される。

手動ダンプ・ロードは、以下のコマンドで実行する。

 SET GLOBAL innodb_buffer_pool_dump_now = ON;
 SET GLOBAL innodb_buffer_pool_load_now = ON;


バッファプールの状態は、以下のコマンドで監視する。

 SHOW ENGINE INNODB STATUS;


出力結果のBuffer pool hit rate (キャッシュヒット率) を確認する。
99[%]以上が理想的であり、90[%]未満の場合はバッファプールサイズの増加を検討する。

REDOログ

REDOログは、トランザクションの永続性を保証する。

MySQL 8.0.30以降では、innodb_redo_log_capacity パラメータでREDOログ容量を設定する。
デフォルトは100[MB] (104857600バイト) であり、8[MB]〜128[GB]の範囲で設定可能である。
動的変更に対応しており、以下のように設定する。

 SET GLOBAL innodb_redo_log_capacity = 536870912;


MySQL 8.0.30以降では、#innodb_redo ディレクトリ内に32個のREDOログファイルが自動管理される。
従来の innodb_log_file_size および innodb_log_files_in_group パラメータは廃止されている。

MySQL 8.0.29以前では、以下のパラメータを使用する。

旧REDOログパラメータ (MySQL 8.0.29以前)
パラメータ デフォルト値 説明
innodb_log_file_size 48MB 各REDOログファイルのサイズ
innodb_log_files_in_group 2 REDOログファイルの数


合計REDOログサイズは、innodb_log_file_size ✕ innodb_log_files_in_group で算出される。
なお、これらのパラメータの変更にはサーバのシャットダウンが必要である。

下表に、推奨設定を示す。

REDOログ容量の推奨設定
環境 推奨容量 説明
OLTP環境 512[MB] 〜 1[GB] 短時間の読み書きトランザクションが多数
トランザクション量が多い環境 1[GB] 〜 4[GB] バッチ処理やデータインポートが頻繁


REDOログ容量が小さすぎる場合、チェックポイントが頻繁に発生してディスクI/Oが増加し、パフォーマンスが低下する。

下表に、REDOログフラッシュの設定を示す。

innodb_flush_log_at_trx_commit
値 説明
0 1秒ごとにディスクにフラッシュ
最速だが、クラッシュ時に最大1秒分のトランザクションを失う。
1 各トランザクションコミット時にディスクにフラッシュ (デフォルト)
最も安全である。
2 各トランザクションコミット時にOSバッファにフラッシュ、1秒ごとにディスクにフラッシュ


チェンジバッファ

チェンジバッファは、セカンダリインデックスへの変更をバッファリングする機能である。

innodb_change_buffering のデフォルト値は all であり、
設定可能な値は、all、none、inserts、deletes、changes、purges である。

下表に、主な設定値を示す。

チェンジバッファ設定一覧
設定値 説明
all INSERT、DELETE、UPDATEの全てをバッファリングする。
inserts INSERTのみバッファリングする。
none チェンジバッファを無効化する。


動作原理として、セカンダリインデックスへの変更がバッファに記録され、ランダムディスクI/Oが削減される。
バッファに記録された変更は、後でバックグラウンドプロセスによりマージされる。
なお、PRIMARY KEY への変更は対象外であり、常に即座に更新される。

セカンダリインデックスが多いテーブルや、INSERT / UPDATE / DELETE が頻繁なワークロードにおいて特に効果が大きい。

innodb_change_buffer_max_size のデフォルト値は 25 (バッファプールの25[%]) であり、0〜50 の範囲で設定する。
値が大きいほど、より多くの変更をバッファリングできる。

ダブルライトバッファ

ダブルライトバッファは、部分的なページ書き込みが発生した際のクラッシュ復旧を保証する機能である。

MySQL 8.0.30以降では、innodb_doublewrite パラメータで設定する。
デフォルトは ON であり、設定可能な値は以下の通りである。

ダブルライトバッファ設定一覧
設定値 説明
ON ダブルライトバッファを有効化する。(通常の動作)
OFF ダブルライトバッファを無効化する。(パフォーマンス向上、安全性低下)
DETECT_ONLY 破損検出のみを行い、復旧は行わない。
DETECT_AND_RECOVER 破損検出と復旧を行う。(ON と同等)


動作原理は以下の通りである。

  1. まず、ページをダブルライトバッファに書き込む。(シーケンシャル書き込みのため高速)
  2. 次に、実際のデータファイルに書き込む。(ランダム書き込み)
  3. クラッシュにより部分的な書き込みが発生した場合、ダブルライトバッファから復旧する。


パフォーマンスへの影響として、ON 時には書き込みが2回発生するため、10〜20[%]程度のオーバーヘッドがある。
ただし、SSDではオーバーヘッドは比較的小さい。

OFF に設定する場合は、部分的な書き込み時にデータ破損のリスクがあることに注意する。
ファイルシステムがアトミック書き込みをサポートする場合 (例: Fusion-io NVMe) にのみ、無効化を検討する。

MVCC とトランザクション

Multi-Version Concurrency Control (MVCC) は、InnoDBの同時実行制御機構である。

下表に、分離レベルとパフォーマンスを示す。

トランザクション分離レベル
分離レベル 説明
READ UNCOMMITTED ダーティリード許容、最小オーバーヘッド
本番環境での使用は非推奨
READ COMMITTED 各ステートメント開始時にスナップショット取得
パフォーマンス良好、多くのOLTPアプリケーションに適合
バイナリログ形式はROWである必要がある。
REPEATABLE READ デフォルト
トランザクション開始時にスナップショット取得
長時間トランザクションでUNDOログが肥大化
ファントムリード防止
SERIALIZABLE 完全分離、全SELECTに共有ロック
ロック多発、パフォーマンス低い。
デッドロックが発生しやすい


トランザクションサイズの管理について、以下に示す事柄に注意する。

  • 長時間トランザクションを避ける。
    UNDOログが肥大化してバッファプールを圧迫し、他のトランザクションがブロックされる可能性がある。
    適切なサイズに分割することを推奨する。
    例: 100万行のUPDATEを1万行ずつに分割する。


autocommit の設定について以下に示す。

  • autocommit のデフォルト値は 1 (ON) であり、各ステートメントが自動的にコミットされる。
  • 明示的なトランザクションを使用する場合は、autocommit = 1 のままで BEGIN を使用する。


I/O設定

下表に、InnoDBのI/O動作を制御するパラメータを示す。

I/O容量パラメータ
パラメータ デフォルト値 説明
innodb_io_capacity 200 IOPS バックグラウンドI/O操作の上限
HDD: 100-200
SSD: 1000-10000以上

ストレージのIOPS性能に合わせて設定
innodb_io_capacity_max 2000 IOPS (innodb_io_capacityの2倍) バッファプールフラッシュが遅延した場合の上限
通常は、innodb_io_capacity の2倍を設定


下表に、フラッシュメソッドを示す。

innodb_flush_method
値 説明
fsync 標準のfsync()システムコール。デフォルト (Unix系)
O_DIRECT ファイルシステムキャッシュをバイパス。Linux推奨。ダブルバッファリング (ファイルシステムキャッシュとバッファプール) を回避。メモリ使用量が削減される
O_DSYNC データの同期書き込み


Linux環境では、O_DIRECT が推奨される。

  • innodb_read_ahead_threshold
    デフォルト値は56 (ページ数) であり、0〜64の範囲で設定する。
    エクステント (64ページ) 内でこの閾値を超える数のページが順次読み込まれた場合、残りのページが先読みされる。
    シーケンシャルスキャンが多いワークロードで効果的である。



オプティマイザヒントとインデックスヒント

オプティマイザの実行計画を制御する手法である。

オプティマイザヒント (MySQL 8.0)

オプティマイザヒントは、クエリの実行計画に影響を与える。

構文を以下に示す。

 SELECT /*+ HINT_NAME(parameters) */ ...


下表に、主要なヒントを示す。

主要なオプティマイザヒント
ヒント 説明
BKA / NO_BKA Batched Key Access を有効化/無効化
例: SELECT /*+ BKA(t1) */ * FROM t1 JOIN t2 ON t1.id = t2.t1_id
INDEX_MERGE / NO_INDEX_MERGE インデックスマージを有効化 / 無効化
例: SELECT /*+ NO_INDEX_MERGE(t1) */ * FROM t1 WHERE a = 10 OR b = 20
JOIN_FIXED_ORDER FROM句の順序でJOINを実行
例: SELECT /*+ JOIN_FIXED_ORDER() */ * FROM t1 JOIN t2 JOIN t3
JOIN_ORDER 指定した順序でJOINを実行
例: SELECT /*+ JOIN_ORDER(t1, t3, t2) */ * FROM t1 JOIN t2 JOIN t3
JOIN_PREFIX 最初のテーブル順序を指定
例: SELECT /*+ JOIN_PREFIX(t1, t2) */ * FROM t1 JOIN t2 JOIN t3
JOIN_SUFFIX 最後のテーブル順序を指定
例: SELECT /*+ JOIN_SUFFIX(t2, t3) */ * FROM t1 JOIN t2 JOIN t3
MAX_EXECUTION_TIME クエリの最大実行時間 (ミリ秒単位) を制限
例: SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM large_table
QB_NAME クエリブロックに名前を付ける。
サブクエリに対してヒントを適用する時に使用する。
例: SELECT /*+ QB_NAME(qb1) */ * FROM t1 WHERE id IN (SELECT /*+ QB_NAME(qb2) */ id FROM t2)
SEMIJOIN / NO_SEMIJOIN 半結合最適化を制御
例: SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ * FROM t1 WHERE id IN (SELECT /*+ QB_NAME(subq1) */ id FROM t2)


下表に、インデックスヒント (オプティマイザヒント形式) を示す。

インデックスヒント (オプティマイザヒント形式)
ヒント 説明
INDEX 指定したインデックスの使用を提案
例: SELECT /*+ INDEX(t1 idx_name) */ * FROM t1 WHERE name = 'Smith'
NO_INDEX 指定したインデックスを無視
例: SELECT /*+ NO_INDEX(t1 idx_name) */ * FROM t1 WHERE name = 'Smith'
INDEX_MERGE インデックスマージを有効化
例: SELECT /*+ INDEX_MERGE(t1 idx1, idx2) */ * FROM t1 WHERE a = 10 OR b = 20


インデックスヒント

テーブル名の後にインデックスヒントを記述する旧形式である。

USE INDEX は、指定したインデックスの使用を提案する。
オプティマイザは他のインデックスも考慮する。

 SELECT * FROM users USE INDEX (idx_name) WHERE name = 'Smith';


FORCE INDEX は、指定したインデックスの使用を強制する。
テーブルスキャンよりインデックススキャンを優先する。

 SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'Smith';


IGNORE INDEX は、指定したインデックスを無視する。
他のインデックスまたはテーブルスキャンが選択される。

 SELECT * FROM users IGNORE INDEX (idx_name) WHERE name = 'Smith';


FOR 句により、ヒントの適用範囲を指定する。

  • FOR JOIN は、JOIN処理に適用する。
  • FOR ORDER BY は、ORDER BY 処理に適用する。
  • FOR GROUP BY は、GROUP BY 処理に適用する。


 SELECT * FROM users USE INDEX FOR ORDER BY (idx_order_date) ORDER BY order_date;


インデックスヒントは将来のバージョンで非推奨になる可能性がある。
オプティマイザヒントへの移行が推奨される。

データ分布が変化するとヒントが不適切になるため、定期的な見直しが必要である。

SET_VARヒント

クエリ実行中にセッション変数を一時的に変更する。

これは、長時間実行クエリのタイムアウト設定に使用する。

構文を以下に示す。

 SELECT /*+ SET_VAR(var_name = value) */ ...


  • 使用例 
     SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ * FROM large_table ORDER BY created_at;

  • 大規模なソートで効果的である。 
     SELECT /*+ SET_VAR(tmp_table_size = 64M) */ status, COUNT(*) FROM orders GROUP BY status;

  • 大規模なグループ化で効果的である。 
     SELECT /*+ SET_VAR(max_execution_time = 5000) */ * FROM slow_table;


セッション全体の設定を変更せずに済むため、他のクエリに影響しない。
アプリケーションコードを変更せずにチューニング可能である。(SET SESSION を発行する必要がない)


ヒストグラム統計 (MySQL 8.0)

ヒストグラムは、オプティマイザがより正確な実行計画を選択するための統計情報である。

ヒストグラムの概要

ヒストグラムは、カラムの値の分布を記録する。

従来の統計情報は、カーディナリティ (異なる値の数) のみを記録する。
値の分布は考慮されない。
例えば、1,000万行のうち、statusカラムは 'active' が999万行、'inactive' が1万行である場合、従来の統計ではカーディナリティ2としか認識されない。

ヒストグラム統計は、値の分布を詳細に記録する。
WHERE status = 'inactive' が1万行のみを返すことをオプティマイザが認識する。
より正確な実行計画が選択される。

ヒストグラムの種類を下表に示す。

ヒストグラムの種類
種類 説明 適用対象
singleton 各値の出現頻度を個別に記録 異なる値が少ないカラム。例: ステータスカラム (5種類の値)
equi-height 値をバケットに分割、各バケットの行数を均等化 異なる値が多いカラム。例: タイムスタンプカラム (数百万種類の値)


ヒストグラムの作成と管理

ヒストグラムは手動で作成する必要がある。

ヒストグラムの作成例を以下に示す。

 ANALYZE TABLE <テーブル名> UPDATE HISTOGRAM ON column_name WITH 256 BUCKETS;


  • 複数カラムに対して作成する例
    バケット数のデフォルトは 100 であり、範囲は 1 〜 1024 である。
    バケット数が多いほど詳細な分布を記録する。
    256バケットが一般的な推奨値である。
     ANALYZE TABLE orders UPDATE HISTOGRAM ON status, order_date WITH 256 BUCKETS;

  • ヒストグラムの削除例 
     ANALYZE TABLE <テーブル名> DROP HISTOGRAM ON column_name;

  • ヒストグラムの確認例 
     SELECT * FROM information_schema.COLUMN_STATISTICS
 WHERE SCHEMA_NAME = 'database_name' AND TABLE_NAME = '<テーブル名>';

  • ヒストグラムの内容確認例 
     SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_PRETTY(HISTOGRAM)
 FROM information_schema.COLUMN_STATISTICS
 WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'status';


ヒストグラムは自動更新されない。(MySQL 8.0)

データ分布が変化した後は手動で再作成が必要である。

ANALYZE TABLE ... UPDATE HISTOGRAM を定期的に実行する。

メモリへの影響は最小限である。
ヒストグラム情報はコンパクトであり、オンラインでの作成が可能である。

効果的な使用場面

ヒストグラムは、特定の状況で効果を発揮する。

値の分布が偏ったカラムとして、ステータスカラムがある。
例えば、'active' が999万行、'inactive' が1万行である場合、WHERE status = 'inactive' がインデックススキャンを選択する。

日付カラムでは、過去1年のデータが大半、それ以前がわずかである場合、WHERE created_at < '2020-01-01' が適切な実行計画を選択する。

カテゴリカラムでは、'category_A' が90[%]、他が各2[%]である場合、WHERE category = 'category_B' が小規模スキャンを選択する。

下表に、インデックスとの使い分けを示す。

インデックスとヒストグラムの使い分け
手法 適している場合
インデックス WHERE句で頻繁に検索されるカラム
JOIN / ORDER BY / GROUP BYで使用されるカラム
ヒストグラム インデックス作成のコストが高いカラム (TEXT, JSON等)
複数の等値検索パターンがあるカラム、メンテナンスオーバーヘッドを避けたいカラム


JOINの最適化において、ヒストグラムが無い場合、オプティマイザが不適切な駆動表を選択する可能性がある。
ヒストグラムがある場合、行数推定が正確になり適切な駆動表が選択される。


Performance Schema と sysスキーマ

Performance Schema の基本

Performance Schemaは、MySQLの内部動作を監視するための機能である。

Performance Schemaは、MySQL 8.0でデフォルトで有効化されている。

performance_schema パラメータのデフォルト値はON (MySQL 8.0) である。
起動時のみ変更可能であり、動的変更は不可である。
設定ファイルの [mysqld] セクションに performance_schema = ON を記述する。

下表に、主要なテーブルを示す。

Performance Schema主要テーブル
テーブル 説明
events_statements_summary_by_digest クエリの実行統計 (正規化されたクエリごと)
実行回数、合計実行時間、平均実行時間、最大 / 最小実行時間、行数の統計
events_waits_summary_by_instance 待機イベントの統計
ファイルI/O、テーブルロック、ミューテックス等
table_io_waits_summary_by_table テーブルごとのI/O統計
読み込み / 書き込み回数、待機時間
file_summary_by_instance ファイルごとのI/O統計
REDOログ、テーブルファイル、一時ファイル等


データのリセットは、以下に示すコマンドで実行する。
定期的にリセットすることで、直近の統計情報のみを取得可能である。

 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;


sysスキーマの活用

sysスキーマは、Performance Schemaのデータを読みやすい形式で提供する。

下表に、主要なビューを示す。

sys スキーマ主要ビュー
ビュー 説明
schema_unused_indexes 未使用インデックスを検出。Performance Schema有効化後に収集されたデータに基づく
schema_tables_with_full_table_scans フルテーブルスキャンが発生したテーブル。インデックスの追加を検討
statements_with_full_table_scans フルテーブルスキャンを実行したSQL文。クエリとインデックスの最適化が必要
statements_with_runtimes_in_95th_percentile 最も遅いクエリ (95パーセンタイル)。優先的に最適化すべきクエリ
statements_with_sorting ソートを実行したクエリ。filesortが発生しているクエリを特定
statements_with_temp_tables 一時テーブルを使用したクエリ。メモリ/ディスク一時テーブルの使用状況
host_summary / user_summary ホスト/ユーザーごとの統計情報。接続数、実行時間、I/O量など


代表的な監視クエリ

実際の監視で使用するクエリ例を以下に示す。

  • 最も遅いクエリを取得する。 
     SELECT
   DIGEST_TEXT AS query,
   COUNT_STAR AS exec_count,
   AVG_TIMER_WAIT / 1000000000000 AS avg_sec,
   SUM_TIMER_WAIT / 1000000000000 AS total_sec
 FROM performance_schema.events_statements_summary_by_digest
 ORDER BY SUM_TIMER_WAIT DESC
 LIMIT 10;

  • フルテーブルスキャンが多いクエリを取得する。 
     SELECT
   DIGEST_TEXT AS query,
   COUNT_STAR AS exec_count,
   SUM_NO_INDEX_USED AS no_index_count,
   SUM_NO_GOOD_INDEX_USED AS no_good_index_count
 FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0
 ORDER BY SUM_NO_INDEX_USED + SUM_NO_GOOD_INDEX_USED DESC
 LIMIT 10;

  • テーブルごとのI/O統計を取得する。 
     SELECT
   OBJECT_SCHEMA AS db_name,
   OBJECT_NAME AS <テーブル名>,
   COUNT_READ,
   COUNT_WRITE,
   SUM_TIMER_READ / 1000000000000 AS read_sec,
   SUM_TIMER_WRITE / 1000000000000 AS write_sec
 FROM performance_schema.table_io_waits_summary_by_table
 WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
 ORDER BY SUM_TIMER_READ + SUM_TIMER_WRITE DESC
 LIMIT 10;

  • 未使用インデックスを取得する。 
     SELECT
   object_schema AS db_name,
   object_name AS <テーブル名>,
   <インデックス名>
 FROM sys.schema_unused_indexes
 WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys');

  • 一時テーブルを使用したクエリを取得する。 
     SELECT
   DIGEST_TEXT AS query,
   COUNT_STAR AS exec_count,
   SUM_CREATED_TMP_TABLES AS tmp_tables,
   SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables
 FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_CREATED_TMP_TABLES > 0
 ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
 LIMIT 10;


これらのクエリを定期的に実行し、パフォーマンス問題を早期に発見する。


クエリキャッシュの廃止 (MySQL 8.0)

クエリキャッシュは、MySQL 8.0で完全に削除されている。

廃止の経緯

クエリキャッシュは、マルチコア環境でのスケーラビリティ問題により廃止された。

MySQL 5.6では、クエリキャッシュがデフォルトで無効化された (query_cache_type = 0)。
パフォーマンス問題が認識された。

MySQL 8.0では、クエリキャッシュが完全に削除された。
関連するパラメータとヒントが廃止された。

廃止の理由として、グローバルロックによるスケーラビリティ問題がある。
クエリキャッシュへのアクセスはシングルミューテックスで保護される。
マルチコアCPUで並行性が低下し、同時実行数が増えるほどパフォーマンスが低下する。

テーブル更新時のキャッシュ無効化も問題である。
テーブルに対するINSERT/UPDATE/DELETEで関連する全キャッシュが無効化される。
更新が頻繁なテーブルではキャッシュヒット率が極めて低い。
OLTPワークロードでは効果が薄い。

キャッシュメンテナンスのオーバーヘッドも大きい。
キャッシュの登録、検索、無効化がボトルネックとなる。
キャッシュを無効にした方が高速な場合が多い。

下表に、廃止されたパラメータおよびヒントを示す。

MySQL廃止されたパラメータとヒント
タイプ 名称
パラメータ query_cache_type
パラメータ query_cache_size
パラメータ query_cache_limit
パラメータ query_cache_min_res_unit
パラメータ query_cache_wlock_invalidate
ヒント SQL_CACHE
ヒント SQL_NO_CACHE


代替手段

クエリキャッシュに代わる以下の手法が推奨される。

ProxySQL

ProxySQLはMySQL前段に配置されるSQL中間層（プロキシ）として動作し、クエリベースのキャッシング機能を提供する。
ルールベースのキャッシュポリシー設定が可能であり、併せてコネクションプーリング機能も実装している。
メリットとして、以下が挙げられる。

  • MySQL本体のパフォーマンスに影響しない。
  • キャッシュヒット率とキャッシュ統計を監視可能である。
  • 更新頻度に応じたキャッシュTTL設定が柔軟に行える。


Redis / Memcached

Redis / Memcachedはアプリケーション層でのキャッシング機構であり、クエリ結果をシリアライズして保存する。
アプリケーションコードでキャッシュロジックを実装し、TTL（Time To Live）を柔軟に設定可能である。

メリットとして、以下が挙げられる。

  • MySQLの負荷を根本的に削減できる。
  • キャッシュの粒度を細かく制御可能である。
  • 複数のMySQLサーバで共有可能である。


ReadySet

ReadySetはMySQL前段に配置されるSQLキャッシング専用エンジンである。
MySQLプロトコル互換性を備え、テーブル変更検知とキャッシュの自動無効化を行う。
アプリケーション変更不要の透過的なキャッシングが実現される。

メリットとして、以下が挙げられる。

  • アプリケーションコードの変更が不要である。
  • テーブル更新時に影響を受けるキャッシュのみ無効化される。
  • スケーラビリティが高い。


インデックスとクエリの最適化

クエリキャッシュは問題の対症療法であり、インデックスとクエリの最適化が本質的な解決策である。
適切なインデックス設計により、キャッシュに依存しない性能を実現できる。

根本的なパフォーマンス改善には、キャッシュ戦略と並行してクエリ最適化を推進することが重要である。


参考リンク



「https://mochiu.net/index.php?title=MySQL_-_パフォーマンスチューニング&oldid=14318」から取得