MySQL インデックス障害の上位 10 の問題の概要

背景

最近、 or と != が使用されたためにインデックスが失敗し、遅い SQL ステートメントが生成されました。そこで、インデックス失敗の上位 10 の原因をまとめてみました。お役に立てれば幸いです。さあ。

1. クエリ条件に「or」が含まれているため、インデックスが失敗する可能性があります。

共通インデックス userId を持ち、次の構造を持つ新しいユーザー テーブルを作成します。

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NULLではない、
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  主キー (`id`)、
  キー `idx_userId` (`userId`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

クエリ SQL を実行すると、次の図に示すようにインデックスが参照されます。

図に示すように、インデックスなしで or 条件 + age を追加すると、インデックスは使用されません。

分析と結論:

• インデックスなしの or+age の場合、userId のインデックスを使用すると仮定しますが、age クエリ条件になると、テーブル全体をスキャンする必要があり、つまり、フルテーブルスキャン + インデックススキャン + マージの 3 つのステップが必要になります。
  
• テーブル全体のスキャンから開始する場合は、1 回で完了します。
  
• MySQL にはオプティマイザーがあります。効率とコストを考慮して、OR 条件に遭遇するとインデックスが無効になることがあります。これは妥当なようです。

注意: or 条件の列にインデックスが付けられている場合、インデックスが失敗する可能性があります。自分で試してみることができます。

2. フィールドタイプが文字列の場合、whereを使用するときは必ず引用符で囲んでください。そうしないとインデックスが無効になります。

デモ テーブルの構造が次のようになっていると仮定します。

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  主キー (`id`)、
  キー `idx_userId` (`userId`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=2 デフォルト CHARSET=utf8;

userId は文字列型で、B+ ツリーの共通インデックスです。クエリ条件に数値が渡された場合、図に示すようにインデックスは通過しません。

数値に「 '' 」を追加すると、つまり文字列を渡すと、当然、以下に示すようにインデックスに従って進みます。

分析と結論:

最初のステートメントが一重引用符なしでインデックスを通過しないのはなぜですか?これは、一重引用符が追加されていない場合、比較は文字列と数値の間で行われ、それらの型が一致しないためです。MySQL は暗黙的な型変換を実行し、比較する前にそれらを浮動小数点数に変換します。

3. ワイルドカードを使用するとインデックスが無効になる可能性があります。

like ワイルドカードを使用するとインデックスが無効になるのではなく、like クエリが % で始まるためインデックスが無効になります。

テーブル構造:

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  主キー (`id`)、
  キー `idx_userId` (`userId`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=2 デフォルト CHARSET=utf8;

クエリが % で始まる場合、図に示すようにインデックスは無効です。

最後に % を付けると、次のようにインデックスが正常に動作していることがわかります。

% を追加し直し、インデックス付きフィールド (インデックスをカバー) のみを検索するように変更します。インデックスがまだ使用されていることがわかりました。驚きましたか?

結論は：

類似クエリが % で始まる場合、インデックスは無効になります。最適化には 2 つの方法があります。

• カバーインデックスの使用
• 最後に%を入れる

注:クエリ要件を満たすすべてのデータを含むインデックスは、カバーリング インデックスと呼ばれます。

4. 結合インデックス: クエリ中の条件列が結合インデックスの最初の列でない場合、インデックスは無効になります。

テーブル構造: ( idx_userid_age結合インデックスがあり、 userId最初、 age 2 番目)

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NULLではない、
  `age` int(11) デフォルト NULL,
  `name` varchar(255) NOT NULL,
  主キー (`id`)、
  キー `idx_userid_age` (`userId`,`age`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=2 デフォルト CHARSET=utf8;

結合インデックスでは、クエリ条件が最も左の一致原則を満たす場合、インデックスは正常に有効になります。デモをご覧ください:

条件列が結合インデックスの最初の列でない場合、次のようにインデックスは無効になります。

分析と結論:

• (k1, k2, k3) のような結合インデックスを作成すると、(k1)、(k1, k2)、(k1, k2, k3) という 3 つのインデックスを作成するのと同じになります。これが左端の一致原則です。
• 結合インデックスは最左原則を満たしていないため、インデックスは一般的に無効になりますが、これも MySQL オプティマイザに関連しています。

5. インデックス列で MySQL の組み込み関数を使用すると、インデックスが無効になります。

テーブル構造:

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `loginTime` 日時 NOT NULL、
  主キー (`id`)、
  キー `idx_userId` (`userId`) BTREE 使用、
  キー `idx_login_time` (`loginTime`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=2 デフォルト CHARSET=utf8;

loginTime はインデックス化されていますが、図に示すように、MySQL の組み込み関数 Date_ADD() が使用されているため、インデックスは直接 GG になります。

6. インデックス列に対して操作（+、-、*、/ など）を実行すると、インデックスは無効になります。

テーブル構造:

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `age` int(11) デフォルト NULL,
  主キー (`id`)、
  キー `idx_age` (`age`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=2 デフォルト CHARSET=utf8;

年齢はインデックス化されていますが、計算中のためインデックスは失われます。 。 。 図に示すように:

7. インデックス フィールドで (!= または < >、not in) を使用すると、インデックスが無効になる可能性があります。

テーブル構造:

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NULLではない、
  `age` int(11) デフォルト NULL,
  `name` varchar(255) NOT NULL,
  主キー (`id`)、
  キー `idx_age` (`age`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=2 デフォルト CHARSET=utf8;

年齢はインデックスされていますが、使用されています！ = または < > でない場合、インデックスはダミーです。次のように：

8. インデックス フィールドで is null および is not null を使用すると、インデックスが失敗する可能性があります。

テーブル構造:

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `card` varchar(255) デフォルト NULL,
  `name` varchar(255) デフォルト NULL,
  主キー (`id`)、
  キー `idx_name` (`name`) BTREE 使用、
  キー `idx_card` (`card`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=2 デフォルト CHARSET=utf8;

単一の名前フィールドにインデックスが付けられ、空でない名前のクエリが実行される場合、次のようにインデックスが使用されます。

単一のカード フィールドにインデックスが付けられ、空でない名前のクエリが実行される場合、次のようにインデックスが使用されます。

しかし、またはと接続すると、次のようにインデックスは無効になります。

9. 左結合クエリまたは右結合クエリに関連付けられたフィールドのエンコード形式が異なるため、インデックスが失敗する可能性があります。

2つの新しいテーブル（user 1つとuser_job 1つ）を作成します。

テーブル `user` を作成します (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) 文字セット utf8mb4 デフォルト NULL,
  `age` int(11) NOT NULL,
  主キー (`id`)、
  キー `idx_name` (`name`) BTREE の使用
) ENGINE=InnoDB AUTO_INCREMENT=2 デフォルト CHARSET=utf8;
 
テーブル `user_job` を作成します (
  `id` int(11) NULLではない、
  `userId` int(11) NULLではない、
  `job` varchar(255) デフォルト NULL,
  `name` varchar(255) デフォルト NULL,
  主キー (`id`)、
  キー `idx_name` (`name`) BTREE の使用
)ENGINE=InnoDB デフォルト文字セット=utf8;

user テーブルの名前フィールドは utf8mb4 でエンコードされていますが、user_job テーブルの名前フィールドは utf8 でエンコードされています。

左外部結合クエリを実行すると、user_job テーブルは次のように完全なテーブル スキャンを実行します。

名前フィールドと同じエンコーディングに変更した場合でも、インデックスは引き続き使用されます。

10. MySQL は、テーブル全体のスキャンの方がインデックスよりも高速であると推定するため、インデックスを使用しません。

• テーブルのインデックスがクエリされると、オプティマイザーがテーブル全体のスキャンの方が効率的であると判断しない限り、最適なインデックスが使用されます。オプティマイザーは、最適なインデックスを使用して見つかったデータがテーブル内のデータの 30% を超えるかどうかに応じて、完全なテーブル スキャンに最適化します。
• 「性別」などにインデックスを追加しないでください。データ列に「0/1」や「Y/N」などの値、つまり重複する値が多数含まれている場合は、そのデータ列にインデックスを作成しても、インデックスの効果はあまり良くなく、テーブル全体のスキャンが必要になることもあります。

効率とコストを考慮して、MySQL は完全なテーブルスキャンとインデックスの使用のどちらが速いかを推定します。これはオプティマイザに関連しています。論理アーキテクチャ図を見てみましょう (画像ソース: オンライン)

要約する

インデックス失敗の上位 10 の原因をまとめました。作成した遅い SQL ステートメントを分析してみましょう。 シミュレートされたテーブル構造と原因となる SQL は次のとおりです。

テーブル `user_session` を作成します (
  `user_id` varchar(32) 文字セット utf8mb4 NOT NULL,
  `device_id` varchar(64) NULLではない、
  `status` varchar(2) NOT NULL,
  `create_time` 日時 NOT NULL、
  `update_time` datetime 更新時にデフォルトでNULL CURRENT_TIMESTAMP、
  BTREE を使用した主キー (`user_id`,`device_id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

説明する 
user_session を更新し、ステータスを 1 に設定します。
ここで (`user_id` = '1' かつ `device_id` != '2')
または (`user_id` != '1' かつ `device_id`='2')

分析:

• 実行された SQL はor条件を使用します。複合主キー ( user_id 、 device_id ) のため、各列にインデックスが付けられ、インデックスが有効になるように見えます。
• ただし、 !=が存在するとインデックスが無効になる可能性があります。つまり、 or + !=の 2 つの主要な症候群により、SQL の更新が遅くなります。

解決：

それで、どうやって解決するのでしょうか? or条件を削除し、実行を 2 回に分割します。同時に、 device_idに通常のインデックスを追加します。

最後に、インデックスが失敗する原因のトップ 10 をまとめました。皆さんが仕事や勉強でこれらの 10 の原因を参考にし、実行計画のexpainとシナリオを組み合わせて、ルーチンに従ってルールに固執するのではなく、具体的な分析を行って、どのシナリオがインデックスの失敗を引き起こすのかを判断していただければ幸いです。

これで、MySQL インデックス障害の上位 10 の問題についての記事は終了です。MySQL インデックス障害の上位 10 の問題についての詳細は、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。