MySQL のインデックス障害の一般的なシナリオと回避方法

序文

これまでにも、一部の SQL ステートメントを不適切に使用すると MySQL インデックスが失敗するという同様の記事を多数読んできました。また、特定の SQL をこのように記述することはできず、そうしないとインデックスが無効になるという MySQL の「軍事規制」または仕様もあります。

大体の内容には同意しますが、いくつかの例の言い回しが絶対的すぎて理由が説明されていないため、多くの人が理由を知らないと思います。そこで、MySQL のインデックス障害の一般的なシナリオをもう一度整理し、その理由を分析して、皆さんの参考としたいと思います。

もちろん、説明することは良い習慣であることを忘れないでください。

MySQL インデックス障害の一般的なシナリオ

以下のシナリオを検証する場合は、十分なデータを用意してください。データ量が少ない場合、MySQL オプティマイザは、フルテーブルスキャンが無害であると判断することがあり、インデックスにヒットしません。

1. where文にorが含まれている場合、インデックスが無効になる可能性がある

または を使用しても、必ずしもインデックスが無効になるわけではありません。 または の左側と右側のクエリ列が同じインデックスにヒットするかどうかを確認する必要があります。

USER テーブルの user_id 列にはインデックスがありますが、age 列にはインデックスがないとします。

次のステートメントは実際にインデックスにヒットします (これは新しいバージョンの MySQL でのみ可能であると言われています。古いバージョンの MySQL を使用している場合は、explain を使用して確認できます)。

user_id = 1 または user_id = 2 の場合、`user` から * を選択します。

ただし、このステートメントはインデックスにヒットできません。

user_id = 1 または age = 20 の場合、`user` から * を選択します。

age 列にもインデックスがあると仮定すると、インデックスにヒットすることはできません。

user_id = 1 または age = 20 の場合、`user` から * を選択します。

したがって、or ステートメントの使用はできるだけ避けることをお勧めします。状況に応じて、代わりに union all または in を使用できます。これら 2 つのステートメントの実行効率は or よりも優れています。

2. where文のインデックス列に否定クエリが使用されているため、インデックスが失敗する可能性があります。

否定クエリには、NOT、!=、<>、!<、!>、NOT IN、NOT LIKE などがあります。

ある「軍事規則」では、否定クエリを使用すると必ずインデックスが無効になると規定されています。筆者はいくつかの記事を確認したところ、一部のネットユーザーがこの点を反論し、証拠を提供していることがわかりました。

実際、ネガティブクエリは必ずしもインデックスの失敗につながるわけではありません。これは、フルテーブルスキャンとインデックスのどちらがコストが低いかという MySQL オプティマイザの判断によって決まります。

3. インデックス フィールドは null にすることができます。is null または is not null を使用すると、インデックスが失敗する可能性があります。

実際、単一のインデックスフィールドは、is null または is not null を使用するとインデックスにヒットする可能性があります。ただし、ネットユーザーは証拠として、2つの異なるインデックスフィールドが or で接続されると、インデックスが無効になると述べています。インデックスが無効であることは確かだと思いますが、その責任は or にあるはずです。これは最初のシナリオに属します~~

USER テーブルの user_id 列にインデックスがあり、null が許可されており、age 列にインデックスがあり、null が許可されているとします。

user_id が null でないか age が null でない場合に、`user` から * を選択します。

ただし、一部の「軍事規則」や仕様では、フィールドを null 以外に設定し、デフォルト値を提供する必要があることを強調しており、これには理由があるため参照する価値があります。

• NULL 列があると、インデックス/インデックス統計/値の比較がより複雑になり、MySQL による最適化が難しくなります。
• このタイプの null は MySQL 内で特別な処理を必要とし、データベース レコード処理の複雑さが増します。同じ条件で、テーブルに空のフィールドが多数ある場合、データベース処理のパフォーマンスが大幅に低下します。
• NULL 値はより多くのストレージ スペースを必要とし、テーブルとインデックスの両方の各行の NULL 列を識別するために追加のスペースが必要になります。
• null を扱う場合、is null または is not null のみを使用でき、=、in、<、<>、!=、not in などの演算記号は使用できません。たとえば、name!='shenjian' の場合、name が null のレコードがあると、クエリ結果には name が null のレコードは含まれません。

4. インデックス列に組み込み関数を使用すると、インデックスが確実に失敗する

たとえば、次のステートメントでは、インデックス列 login_time で関数が使用されているため、インデックスが無効になります。

DATE_ADD(login_time, INTERVAL 1 DAY) = 7 の場合、`user` から * を選択します。

最適化の提案: アプリケーション内で計算と変換を実行するようにしてください。

実際、ネットユーザーによって言及されたインデックス失敗のシナリオは 2 つあり、どちらもインデックス列での関数の使用に起因すると考えられます。

4.1 暗黙的な型変換によるインデックスの無効化

たとえば、次のステートメントでは、インデックス列 user_id は varchar 型であり、インデックスにヒットしません。

user_id = 12 の場合、`user` から * を選択します。

これは、MySQL が暗黙的な型変換を実行し、関数を呼び出して user_id を変換するためです。

CAST(user_id AS signed int) = 12 の場合、`user` から * を選択します。

4.2 暗黙的な文字エンコーディング変換によるインデックスの無効化

2 つのテーブル間で結合クエリを実行するときに、2 つのテーブル内のフィールドの文字エンコーディングが一致していない場合、MySQL は CONVERT 関数を呼び出して、異なる文字エンコーディングを暗黙的に変換し、統一性を実現することがあります。関連するフィールドに対して作用すると、インデックスは無効になります。

たとえば、次の文では、d.tradeid の文字エンコーディングは utf8 ですが、l.tradeid の文字エンコーディングは utf8mb4 です。 utf8mb4 は utf8 のスーパーセットであるため、MySQL は変換時に CONVERT を使用して utf8 を utf8mb4 に変換します。簡単に言えば、CONVERT は d.tradeid に作用するため、インデックスは無効です。

tradelog l 、 trade_detail d から l.operator を選択します (d.tradeid=l.tradeid かつ d.id=4)。

この状況には一般的に 2 つの解決策があります。

解決策 1: 関連するフィールドの文字エンコードを統一します。

解決策 2: 文字エンコードを統一することが本当に不可能な場合は、関連付けの = の右側に CONVERT 関数を手動で適用して、文字エンコードを統一するという目的を達成します。ここでは、utf8mb4 は強制的に utf8 に変換されます。もちろん、スーパーセットからサブセットに変換するときにデータが切り捨てられるリスクがあります。次のように：

tradelog l 、 trade_detail d から d.* を選択します。ここで、 d.tradeid=CONVERT(l.tradeid USING utf8) かつ l.id=2 です。

5. インデックス列に対する操作は必ずインデックスを無効にします

+、-、*、/ などの演算は次のとおりです。

`user` から * を選択します。年齢 - 1 = 10;

最適化するには、値に対して操作を実行するか、アプリケーション内で直接計算する必要があります。次に例を示します。

age = 10 - 1 の場合、`user` から * を選択します。

6. ワイルドカードを使用するとインデックスが失敗する可能性がある

LIKE クエリが % で始まる場合、インデックスは無効になります。解決策は2つあります。

次のように、% を後ろに移動します。

select * from `user` where `name` like '李%';

カバーリング インデックスを使用してインデックスをヒットします。

`user` から名前を選択します。`name` は '%李%' のような名前です。

7. 結合インデックスでは、where 句のインデックス列が左端一致の原則に違反すると、インデックスは確実に無効になります。

(k1, k2, k3) のような結合インデックスを作成する場合、それは 3 つのインデックス (k1)、(k1, k2)、(k1, k2, k3) を作成することと同じです。これが最も左の一致原則です。

たとえば、次のステートメントはインデックスにヒットしません。

k2=2 の場合、t から * を選択します。
k3=3 の場合、t から * を選択します。
k2=2かつk3=3であるtから*を選択します。

次のステートメントはインデックス (k1) のみにヒットします。

k1=1かつk3=3であるtから*を選択します。

8. MySQLオプティマイザの最終的な選択はインデックスを使用しないことです

前述のように、シナリオがインデックスの有効性の要件を完全に満たしている場合でも、インデックスが最終的に使用されるかどうかは、実際のデータ量やその他の要素を考慮した MySQL オプティマイザの判断によって決まります。もちろん、SQL ステートメントで特定のインデックスを強制するように指定することもできます。

インデックスを最適化するためのいくつかの提案

• 非常に頻繁に更新され、差別化が低い属性にはインデックスを作成しないでください。
  • 更新により B+ ツリーが変更され、頻繁に更新されるフィールドにインデックスを作成するとデータベースのパフォーマンスが大幅に低下します。
  • 「性別」のように区別がほとんどないプロパティにインデックスを作成しても意味がありません。データを効果的にフィルタリングできず、パフォーマンスは完全なテーブルスキャンと同様になります。
• 複合インデックスを作成する場合、識別力の高いフィールドを先頭に配置する必要があります。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

参照する

これらの SQL ステートメントは同じロジックであるにもかかわらず、パフォーマンスに大きな違いがあるのはなぜでしょうか? 》

《バックエンドプログラマー必携：インデックス失敗の原因トップ10》

「道家データベース58の30の軍事規則の解釈」

《MySQL or/in/union とインデックス最適化 | アーキテクトへの道》