MySQL最適化のコツ：5つのステップ + 10の事例

SQL最適化の一般的な手順

1 スロークエリログなどから、実行効率の低いSQL文を特定する

2 explainでSQLの実行計画を分析する

重点的に確認すべき項目は、type、rows、filtered、extraです。

typeは上から下に向かって効率が高くなります。

• ALL フルテーブルスキャン
• index インデックスフルスキャン
• range インデックス範囲スキャン。<, <=, >=, between, inなどで使用される。
• ref 非ユニークインデックススキャンまたはユニークインデックスのプレフィックススキャン。単一のレコードを返す。関連クエリでよく見られる。
• eq_ref refに似ているが、ユニークインデックスを使用する点が異なる。主キーを使用した関連クエリで使用される。
• const/system 単一レコード。システムはマッチした行の他のカラムを定数として扱う。主キーやユニークインデックスによるクエリなど。
• null MySQLはテーブルやインデックスにアクセスせず、直接結果を返す。

上から下に向かって効率が高くなりますが、コストモデルに基づくと、インデックスidx1(a, b, c)とidx2(a, c)がある場合、SQLがselect * from t where a = 1 and b in (1, 2) order by c;だと、idx1を使うとtypeはrange、idx2を使うとtypeはrefになります。スキャン行数が、idx2を使う場合にidx1の約5倍以上になる場合はidx1が使われ、そうでなければidx2が使われます。

Extra

• Using filesort：MySQLは行をソート順に取得する方法を見つけるために、追加のパスを必要とする。結合タイプですべての行をブラウズし、WHERE句にマッチするすべての行のソートキーと行ポインタを保存することでソートを完了する。その後キーがソートされ、ソート順に行が取得される。
• Using temporary：中間結果を保存するために一時テーブルを使用。パフォーマンスが非常に悪いため、重点的に最適化が必要。
• Using index：対応するSELECT操作でカバリングインデックス（Covering Index）が使用されていることを示す。テーブルのデータ行へのアクセスを回避しており、効率が良い！もしusing whereも同時に表示される場合、インデックス検索だけでは条件に合うデータを直接クエリできないことを意味する。
• Using index condition：MySQL 5.6以降で追加されたICP（Index Condition Pushdown）を使用。using index conditionはICP（インデックス条件プッシュダウン）を使用していることを示す。ストレージエンジンレベルでデータフィルタリングを行い、サービスレイヤーでフィルタリングするのではなく、インデックスの既存データを利用してテーブルへのアクセス（回表）を減らす。

3 show profileによる分析

SQL実行のスレッド状態と消費時間を把握する。

デフォルトでは無効。有効化にはset profiling = 1;を実行。

SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

4 trace

traceはオプティマイザがどのように実行計画を選択するかを分析する。traceファイルを通じて、なぜオプティマイザがAの実行計画を選び、Bを選ばなかったのかをさらに理解できる。

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

5 問題を特定し、適切な対策を実施する

• インデックスの最適化
• SQL文の最適化：SQLの修正、INクエリの分割、時間範囲クエリの分割、前回のデータに基づくフィルタリング
• 別の実装方法への変更：ES、データウェアハウスなど
• データフラグメントの処理

マイクロサービスの思想に基づき、B2C ECサイトのシナリオで構築されたプロジェクト実践。コア技術スタックはSpring Boot + Dubbo。将来的にはSpring Cloud Alibabaにリファクタリング予定。

プロジェクトアドレス：https://github.com/YunaiV/onemall

シナリオ分析

ケース1：最左マッチング

インデックス

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL文

select * from _t where orderno=''

クエリマッチングは左から右へ行われる。order_noでインデックスを使用するには、クエリ条件にshop_idを含めるか、インデックス(shop_id,order_no)の順序を入れ替える必要がある。

ケース2：暗黙的な型変換

インデックス

KEY `idx_mobile` (`mobile`)

SQL文

select * from _user where mobile=12345678901

暗黙的な型変換はインデックス上で演算を行うことになり、インデックスを無効にする。mobileは文字型だが数値が使用されている。文字列でマッチさせるべきで、そうしないとMySQLが暗黙的な変換を行い、インデックスが無効になる。

ケース3：大きなページネーション

インデックス

KEY `idx_a_b_c` (`a`, `b`, `c`)

SQL文

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

大きなページネーションのシナリオでは、まずプロダクトに要件の最適化を依頼することを優先する。最適化できない場合は、以下の二つの方法がある。

• ひとつは、前回の最後のデータ（上記のc）を受け渡し、"c < xxx"のように処理する方法。ただし、一般的にインターフェースプロトコルの変更が必要であり、必ずしも実行可能ではない。
• もうひとつは、遅延結合（延迟关联）を用いた処理方法。SQLのテーブルアクセス（回表）を減らすが、インデックスが完全にカバーしている必要がある。SQLの変更例は以下の通り。

SELECT
 t1.*
FROM
 _t t1,
 ( SELECT id FROM _t WHERE a = 1 AND b = 2 ORDER BY c DESC LIMIT 10000, 10 ) t2
WHERE
 t1.id = t2.id;

ケース4：in + order by

インデックス

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

SQL文

SELECT
 *
FROM
 _order
WHERE
 shop_id = 1
 AND order_status IN ( 1, 2, 3 )
ORDER BY
 created_at DESC
 LIMIT 10

inクエリはMySQL内部でn*mの方法で検索される。unionに似ているが、効率はunionより高い。

inクエリのコスト計算（コスト = タプル数 * IO平均）では、inに含まれる値を一つずつクエリしてタプル数を取得するため、計算処理が遅くなる。そのためMySQLはしきい値（eq_range_index_dive_limit）を設定しており、5.6以降ではこのしきい値を超えると、そのカラムのコストは計算に含まれなくなる。

このため、実行計画が不正確に選択される可能性がある。デフォルトは200で、in条件が200データを超えるとinのコスト計算に問題が生じ、MySQLが不正確なインデックスを選択する可能性がある。

対処方法としては、(order_status, created_at)の順序を入れ替え、SQLを遅延結合に調整すること。

ケース5：範囲クエリによる後続フィールドのインデックス使用不可

インデックス

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

SQL文

SELECT
 *
FROM
 _order
WHERE
 shop_id = 1
 AND created_at > '2021-01-01 00:00:00'
 AND order_status = 10

範囲クエリには"IN"や"between"も含まれる。

ケース6：不等号、NOTを含む条件はインデックスの高速検索に使用できない（ICPは使用可能）

select * from _order where shop_id=1 and order_status not in (1,2)

select * from _order where shop_id=1 and order_status != 1

インデックス上では、NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKEなどを避けること。

ケース7：オプティマイザがインデックスを使用しないケース

要求されるデータ量が非常に少ない場合、オプティマイザは補助インデックスを選択する。しかし、アクセスするデータがテーブル全体のデータのかなりの部分（一般的には約20%）を占める場合、オプティマイザはクラスタ化インデックスを使用してデータを検索する。

select * from _order where  order_status = 1

未払いの注文を全て取得するクエリ。通常このような注文は少ない。たとえインデックスを作成しても、インデックスを使用できない。

ケース8：複雑なクエリ

select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';

select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;

統計データの集計などであれば、データウェアハウスに切り替えることを検討する。

ビジネス上どうしても複雑なクエリが必要な場合、SQLにこだわらず、ESなどの別の方法で解決することを推奨する。

ケース9：ascとdescの混在

select * from _t where a=1 order by b desc, c asc
descとascを混在させるとインデックスが無効になる

ケース10：大規模データ

プッシュ業務のデータ保存では、データ量が非常に大きくなる可能性がある。もしMySQLに保存し、7日間などの有効期限を設定する場合、頻繁なデータ削除によってデータフラグメントが発生する可能性があるため、DBAに連絡してデータフラグメント処理を依頼する必要がある。