MySQLでインデックスエラーが発生する状況について簡単に説明します

以下に、トレーニング機関からのヒントと私自身の要約をいくつか示します。
以下のインデックスの内容を説明するために、まず一時テーブルtest02を作成します。

テーブル `sys_user` を作成します (
  `id` varchar(64) NOT NULL COMMENT '主キー',
  `name` varchar(64) デフォルト NULL コメント 'name',
  `age` int(64) デフォルト NULL コメント '年齢',
  `pos` varchar(64) デフォルト NULL コメント '位置',
  主キー (`id`)、
  キー `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザーテーブル';

このテーブルには、主キー、名前、年齢、役職の4つのフィールドがあります。

最初の式を説明しましょう:
1. フルバリューマッチ私のお気に入り
2. 最良左接頭辞ルール（重要）
完全な値の一致とは、結合された複合インデックスの順序と数が、検索条件の順序と数と同じである必要があることを意味します。
最良左プレフィックス ルールとは、複数の列にインデックスが付けられる場合、最も左のプレフィックス ルールに従う必要があることを意味します。これは、クエリがインデックスの左端の列から開始され、インデックス内の列をスキップしないことを意味します。<br /> 次に、このテーブルに複合インデックスを作成します。

ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(name,age,pos);

以下は検索ステートメントです:

SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';

検索ステートメントの前にキーワード EXLAIN を追加すると、インデックスが使用されているかどうかがわかります。

(1) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';
（2）EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22;
（3）EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND pos = 'java';

表示された結果から、最初の複合インデックスの 3 つのフィールドすべて、2 番目の複合インデックスの 2 つのフィールドのみ、3 番目の複合インデックスの 1 つのフィールドのみを使用していることがわかります。 3 つのステートメントすべてでインデックスを使用しますが、最初のステートメントが最も優れていることは明らかです。

何が失敗するか見てみましょう:

（4）EXPLAIN SELECT * FROM sys_user WHERE age = 22;
(5) EXPLAIN SELECT * FROM sys_user WHERE pos = 'java';
(6) EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos = 'java';

上記の 3 つの状況はすべて、最左プレフィックスの原則に違反しているため、フル テーブル スキャンになります。複合インデックスの左端の列は name であるため、検索条件 name が先頭にない場合、インデックスは無効になります。最初の状況は完全な値の一致を満たし、2 番目の状況は name と age の 2 つのフィールドを満たし、3 番目の状況は name のみを満たすため、インデックスは name のみを使用します。

3. インデックス列に対して操作 (計算、関数 (自動または手動) の型変換) を実行しないでください。実行すると、インデックスが無効になり、完全なテーブル スキャンに変換されます。

(7) EXPLAIN SELECT * FROM sys_user WHERE LEFT(name,1)='Xiaoming';

7 番目のケースは、インデックス列が計算または関数操作を実行し、テーブル全体のスキャンが発生するため失敗します。

4. ストレージ エンジンは、インデックス内の範囲条件の右側にある列を使用できません<br /> 上記のテキストの意味がわからないかもしれません。理解するために、以下のクエリ ステートメントを実行してみましょう。

(8) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age < 22 AND pos = 'java';

上の図から、type が範囲レベルになっていることがわかります。つまり、age<22 以降の pos フィールドのインデックスは無効です。

5. カバーリングインデックス（インデックスのみにアクセスするクエリ（インデックス列とクエリ列は同じ））の使用を試み、select *の使用を減らします。<br /> これは文字通りの意味です。特定のフィールドをクエリする方が、*をクエリするよりも効率的です。比較してみましょう。

(9) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';
(10) EXPLAIN SELECT name, age, pos FROM sys_user WHERE name = 'Xiaoming' AND age = 22 AND pos = 'java';

6. MySQLは不等号（!=または<>）を使用する場合はインデックスを使用できないため、テーブル全体のスキャンが発生します。
(!= または <>) は通常、大量のデータと一致します。インデックスの使用コストがテーブル全体のスキャンよりも大きい場合、MySQL はインデックスの使用を断念し、代わりにテーブル全体のスキャンを選択します。

(11) EXPLAIN SELECT * FROM sys_user WHERE name != 'Xiaoming'

結果は、インデックスの失敗によりテーブル全体のスキャンが発生したことを示しています。

7. null、nullではない、インデックスは使用できません
is null、is not null は通常、大量のデータと一致します。インデックスの使用コストがフル テーブル スキャンよりも大きい場合、MySQL はインデックスの使用を断念し、代わりにフル テーブル スキャンを選択します。

(12) EXPLAIN SELECT * FROM sys_user WHERE nameがnullでない

8. がワイルドカード ('%abc...') で始まる場合、MySQL インデックスは無効になり、操作は完全なテーブル スキャンになります。(右側に % を記述すると、インデックスの無効化を回避できます。ビジネスで '%abc...%' が本当に必要な場合は、カバー インデックスを使用してインデックスの無効化を回避できます。)

(13) EXPLAIN SELECT * FROM sys_user WHERE name like '%明%'
(14) EXPLAIN SELECT * FROM sys_user WHERE name like '明%'
(15) EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%'

上記の結果から、最初のインデックスは失敗します。2 番目は、右側に % を記述するだけでインデックスの失敗を回避できます。3 番目は、ビジネスで '%abc...%' のような SQL が本当に必要な場合に、カバー インデックスを使用してインデックスの失敗の問題を解決できます。

9. 文字列を一重引用符で囲まないとインデックス作成が失敗する

(16) EXPLAIN SELECT * FROM sys_user WHERE name=222;

検索文字列は一重引用符で囲む必要があるため、上記で使用されている 222 は int 型です。検索時に、MySQL は名前が varchar 型であると判断し、取得のために 222 を '222' に変換します。インデックス列は型変換されるため、インデックスは無効になります。

10. 接続に使用するとインデックスが無効になるため、以下を使用してください。

(16) EXPLAIN SELECT * FROM sys_user WHERE name = 'Xiaoming' または age = 22; 

MySQL インデックスが無効になる状況に関するこの記事はこれで終わりです。MySQL インデックスの無効化の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。