mysqlでインデックスに障害が発生する原因は何ですか？

トレーニングプロバイダーからのヒントと、私の個人的なまとめをご紹介します。
以下のインデックス内容を説明するために、まずテンポラリテーブルtest02を作成します。

CREATE TABLE `sys_user` (
  `id` varchar(64) NOT NULL COMMENT 'primary key',
  `name` varchar(64) DEFAULT NULL COMMENT 'name',
  `age` int(64) DEFAULT NULL COMMENT 'age',
  `pos` varchar(64) DEFAULT NULL COMMENT 'Position',
  PRIMARY KEY (`id`),
  KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='user table';

このテーブルには、プライマリキー、名前、年齢、タイトルの4つのフィールドがあります。

最初のニーモニックを説明しましょう。
1. オールバリューマッチングがお気に入り
2. ベストレフトプレフィックスルール（重要）
完全一致とは、連結されたインデックスの順番と数が、検索された条件の順番と数と同じでなければならないことを意味します。
best-left-prefixルールとは、複数の列がインデックスされている場合、leftmost-prefixルールに従うというものである。つまり、クエリはインデックスの一番左のカラムから開始し インデックス内のカラムをスキップしない
ここでは、このテーブルに対して複合インデックスを作成します。

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

ここに検索文があります。

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

インデックスが使用されているかどうかは、検索文の前にEXLAINというキーワードを付けることで判別できます

(1) EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';
(2) EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 ;
(3) EXPLAIN SELECT * FROM sys_user WHERE name='小明' 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つのケースは、左端の左接頭辞の原則に違反しているため、フルテーブルスキャンになる、左端の複合インデックスが名前なので、ときに検索条件名は、インデックスの前に失敗します、最初のケースでは、完全な値の一致を満たして、第二は、名前と年齢、および第三に2つのフィールドを満たしている名前だけを満たしているので、インデックスは名前にのみ使用されています。

3. インデックス列に対していかなる操作（計算、関数（自動または手動）型変換）も行わない場合、インデックスのフルテーブルスキャンに失敗します。

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

7番目のケースは、インデックス列がフルテーブルスキャンを引き起こす計算または関数操作を行ったため、失敗します。

4. ストレージエンジンは、インデックスで範囲条件の右側にある列を使用できない
もしかしたら、上の文章を見ても意味が分からないかもしれませんが、分かりやすくするために以下のクエリ文を実行してみましょう。

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

上の画像から、typeがレンジレベルになっていることがわかります。これは、age<22以降のposフィールドのインデックス作成が無効になっていることを意味します。

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

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

6. not equal to (! = または <>) を使用する場合、mysql はインデックスを使用できません。インデックスを使用できない場合、フルテーブルスキャンになります。
(! = または <>) は通常、大量のデータにマッチし、インデックスを使用するコストがフルテーブルスキャンよりも大きい場合、mysql はインデックスを放棄してフルテーブルスキャンを優先させます。

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

その結果、インデックスの不具合により、フルテーブルスキャンが発生したことがわかります

7. is null, is not null もインデックスを使用できない。
はヌル、is not nullは通常大量のデータにマッチし、インデックスを使用するコストがフルテーブルスキャンより大きい場合、mysqlはフルテーブルスキャンを支持してインデックスの使用をあきらめます。

(12) EXPLAIN SELECT * FROM sys_user WHERE name is not null

8. ワイルドカード('%abc...')で始まるような場合、mysqlインデックスはフルテーブルスキャン操作に失敗します。（右に書かれた%はインデックス失敗を回避します。）

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

上記の結果から、最初のインデックスは失敗し、2番目は正しい%だけを書くことで回避し、3番目はビジネスが本当に'%abc...%'のようなSQLを必要とする場合にインデックスをオーバーライドすることで解決します。

9. シングルクォートを使用しない文字列インデックス作成は失敗する

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

文字列の検索はシングルクォートを追加する必要があるため、上記の使用222はint型であり、名前を取得するときにmysqlはvarchar型は222を'222'に変換して検索すると判断し、インデックス列は型に変換されたのでインデックスが無効である。

10. 10.使用または控えめに、結合に使用するとインデックスが失敗する

(16) EXPLAIN SELECT * FROM sys_user WHERE name='小明' or age = 22;

インデックスに失敗するmysqlで起こることについては、この記事ですべてです。mysqlのインデックスエラーについてもっと詳しく知りたい方は、スクリプトハウスの過去記事を検索するか、下記の関連記事を引き続きご覧ください。