オンラインMySQLオプティマイザの誤判断によって発生した低速クエリイベントを記録する

序文：

非常に遅いクエリとリクエストのタイムアウトのアラートを受け取りました。メトリックを通じて MySQL リクエストの異常を分析したところ、cli —> show processeslist で多くの遅いクエリを確認しました。 この SQL は以前は存在せず、データ量の増加により後からこの問題が発生しました。 フィード テーブルは 1 億と大きいですが、フィード ストリーム情報は最近ホットであるという特性があるため、頻繁な IO は非効率的な innodb_buffer_pool_size によって発生するものではありません。 その後、実行プランの分析をさらに詳しく説明すると、その理由がわかりました。MySQL クエリ オプティマイザーが、効率的であると判断したインデックスを選択しました。

MySQL クエリ オプティマイザーはほとんどの場合信頼できます。ただし、SQL 言語に複数のインデックスが含まれている場合は、最終結果が少しわかりにくくなることが多いため注意が必要です。 MySQL では同じ SQL に対して 1 つのインデックスしか使用できないため、どれを選択すればよいでしょうか? データ量が少ない場合、MySQL オプティマイザは主キー インデックスを最後に配置し、インデックスと一意性を優先します。 特定のデータ レベルに達すると、クエリ操作が実行されるため、MySQL クエリ オプティマイザーは主キーを選択する可能性が高くなります。

覚えておいていただきたいのは、MySQL クエリの最適化は、時間コストの考慮ではなく、取得コストの考慮に基づいているということです。 オプティマイザーは、SQL ステートメントを実際に実行するのではなく、既存のデータの状態に基づいてコストを計算します。

したがって、MySQL オプティマイザは毎回最適化効果を達成できるわけではありません。 コストを正確に見積もることはできません。各インデックスを通過するコストを正確に取得したい場合は、実際に一度実行して知る必要があります。したがって、コスト分析はあくまでも推定であり、推定であるため、誤った判断が行われる可能性があります。

ここで取り上げているテーブルは、フィード情報フロー テーブルです。フィード情報フロー テーブルは頻繁にアクセスされるだけでなく、大量のデータも含まれていることがわかっています。 ただし、このテーブルのデータ構造は非常に単純であり、インデックスも単純です。インデックスは合計で 2 つだけあり、1 つは主キー インデックス、もう 1 つは一意キー インデックスです。

以下に示すように、このテーブルのサイズは 1 億に達しています。キャッシュ フロントエンドが十分にあることと、さまざまな理由により、データベースとテーブルをシャードする時間がありません。

問題は、データ量が 1 億未満の場合は、MySQL オプティマイザがインデックス インデックスを使用することを選択することです。データ量が 1 億を超えると、MySQL クエリ オプティマイザは主キー インデックスを使用することを選択します。 これによって生じる問題は、クエリ速度が遅すぎることです。

これが通常の状況です:

mysql> explain SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377) AND cid IN (1001,1005,1054,1092,1093,1095) AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: フィード
  パーティション: NULL
     タイプ: 範囲
可能なキー: PRIMARY、フィードユーザーターゲット
     キー: feed_user_target
   キーの長さ: 6
     参照: NULL
     行数: 18
   フィルター: 50.00
    追加: where の使用; index の使用; filesort の使用
セットに 1 行、警告 1 件 (0.00 秒)

同じ SQL ステートメントでも、データ量が大幅に変化すると、MySQL クエリ オプティマイザのインデックスの選択も変化します。

mysql> explain SELECT * FROM `feed` WHERE user_id IN (116537309,116709093,116709377) AND cid IN (1001,1005,1054,1092,1093,1095) AND id <= 128384713 ORDER BY id DESC LIMIT 0, 11 \G;
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: フィード
     タイプ: 範囲
可能なキー: PRIMARY、フィードユーザーターゲット
     キー: PRIMARY
   キーの長さ: 4
     参照: NULL
     行数: 11873197
    追加: where の使用
セット内の 1 行 (0.00 秒)

解決策は、force index を使用して、指定したインデックスをクエリ オプティマイザーが使用するように強制することです。 ここでは Python 開発環境を使用しています。一般的な Python ORM には、force index、ignore index、user index パラメータがあります。

explain SELECT * FROM `feed` force index (feed_user_target) WHERE user_id IN (116537309,116709093,116709377) ...

では、データの増加により MySQL オプティマイザが非効率的なインデックスを選択するという問題をどのように防ぐことができるでしょうか?

この問題について、さまざまな工場の複数の DBA に相談したところ、彼らが得た答えは私たちの方法と同じでした。 この問題は、後の段階で遅いクエリを通じてのみ発見され、その後、SQL ステートメントで強制インデックスを指定してインデックスの問題を解決します。 また、システム立ち上げの初期段階ではこのような問題は回避されますが、ビジネス開発者は初期のレビュー作業で DBA と協力することがよくありますが、後期段階では手間を省くため、または問題がないと判断して、MySQL クエリ事故が発生します。

私は MySQL オプティマイザのインデックス選択ルールについて漠然としか理解していないので、後で時間をかけてルールを勉強するつもりです。