オンライン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 オプティマイザのインデックス選択ルールについて漠然としか理解していないので、後で時間をかけてルールを勉強するつもりです。

以下もご興味があるかもしれません: