MySQL がインデックス障害を引き起こす状況はどのようなものですか?

1. 準備作業

まず、デモ用に 2 つのテーブルを準備します:

CREATE TABLE `student_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `course_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

#准备数据
select count(*) from student_info;#1000000
select count(*) from course;      #100

2. インデックス無効化ルール

# 1. 結合インデックスの使用を優先します

次の SQL ステートメントにはインデックスがありません:

#平均耗时291毫秒
select * from student_info where name='123' and course_id=1 and class_id=1;

インデックスを作成することでクエリの効率を最適化します。次のようないくつかのオプションがあります:

①通常のインデックスを作成します:

#建立普通索引
create index idx_name on student_info(name);
#平均耗时25毫秒，查看explain执行计划，使用到的是idx_name索引查询
select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;

②通常のインデックスを基に、結合インデックスを追加します:

#name，course_id组成的联合索引
create index idx_name_courseId on student_info(name,course_id);
#该查询语句一般使用的是联合索引，而不是普通索引，具体看优化器决策
#平均耗时20ms
select * from student_info where name='zhangsan' and course_id=1 and class_id=1;

ご覧のとおり、

複数のインデックスを使用できる場合、システムは通常、より長いジョイント インデックスの使用を優先します。これは、ジョイント インデックスの方が高速であるためです。 これも同様です。理解するのは簡単です。 前提条件は、結合インデックス の左端の一致原則に従うことです。

name、course_id、class_id で構成されるジョイント インデックスを作成する場合、上記の SQL ステートメントは、予想どおり、より長い key_len を持つこのジョイント インデックスを使用します (予期せず、オプティマイザーは他のより良いソリューションを選択する可能性があります。もっと早く）。

ジョイント インデックスの速度は、通常のインデックスの速度より必ずしも優れているわけではありません。 たとえば、最初の条件ですべてのレコードをフィルタリングする場合、後続の条件を使用する必要はありません。索引。

2. 左端の一致原理

#删除前例创建的索引，新创建三个字段的联合索引，name-course_id-cass_id
create index idx_name_cou_cls on student_info(name,course_id,class_id);

① すべてのジョイント インデックスが一致する状況:

#关联字段的索引比较完整
explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;

各フィールドの条件はジョイント インデックスと一致するため、この SQL ステートメントは左端のプレフィックス ルールに従います。ジョイント インデックスを使用すると、高速な検索が可能になり、追加のクエリが回避されるため、これが最適な状況になります。

②ジョイント インデックスの右端の部分が欠落しています:

explain select * from student_info where name='11111' and course_id=10068;

SQL ステートメントの条件にジョイント インデックスのすべてが含まれていません。条件は一致しますが、右半分が消去されます。このステートメントで使用されるインデックスは関連クエリのままですが、その一部のみが使用されます。key_len を見ると、5 バイトが欠落していることがわかります。これらの 5 バイトは class_id に対応します。 class_id が有効ではないことを証明します (where にないので、当然使用されません)。

同様に、where の course_id フィールドを消去すると、ジョイント インデックスは引き続き有効になりますが、key_len は減少します。

③ジョイント インデックスに欠落している状況:

#联合索引中间的字段未使用，而左边和右边的都存在
explain select * from student_info where name='11111' and class_id=10154;;

上記の SQL ステートメントでは引き続きジョイント インデックスが使用されていますが、その key_len小さくなりました name フィールドのみがインデックスを使用します class_id フィールドは結合インデックス内にありますが、左端の一致原則に従っていないため GG になります。

SQL ステートメント全体の実行プロセスは次のとおりです。まず、ジョイント インデックスの B ツリー内で名前 11111 を持つすべてのレコードを検索し、次に class_id が 10154 ではないこれらのレコードの全文をフィルターで除外します。 。全文検索がもう一段階増えると、①や②よりもパフォーマンスが悪くなってしまいます。

④ジョイント インデックスの左端が欠落している状況:

explain select * from student_info where class_id=10154 and course_id=10068;

この状況は、前の例の特殊なケースです。結合インデックスの左端が欠落している 左側のフィールドが見つからないため、他にもあるが全て無効となり全文検索となる。

結論: 左端一致の原則は、クエリがインデックスの左端の列から開始され、インデックス内の列をスキップできないことを意味します。列がスキップされた場合、インデックスは部分的に無効になります。 (以下のすべてのフィールドのインデックスは無効です)。

注: 結合インデックスを作成する場合、フィールドの順序は固定されており、左端の一致がこの順序に従って比較されますが、クエリ ステートメントではフィールドの順序が異なります。 where 条件はオプションです。変更とは、where 条件にインデックス フィールドが存在する限り、関連するインデックス フィールドの順序に従う必要がないことを意味します。

3. 範囲条件の右側の列インデックスが無効です

上記の結合インデックスを引き継ぎ、次の SQL クエリを使用します:

#key_len=> name:63,course_id:5,class_id:5
explain select * from student_info where name='11111' and course_id>1 and class_id=1;

key_len は 68 のみです。これは、関連付けられたインデックスの class_id が使用されないことを意味します。これは左端の一致原則に従いますが、

> 記号により右側のインデックスが作成されます。関連するインデックスの条件フィールドの値が無効です。

ただし、>= 記号を使用した場合:

#不是>、<,而是>=、<=
explain select * from student_info where name=&#39;11111&#39; and course_id>=20 and course_id<=40 and class_id=1;

右側のインデックスは無効ではなく、key_len は 73 で、すべてのインデックスが無効になります。フィールドが使用されます。

结论：为了充分利用索引，我们有时候可以将>、<等价转为>=、<=的形式，或者将可能会有<、>的条件的字段尽量放在关联索引靠后的位置。

4.计算、函数导致索引失效

#删除前面的索引，新创建name字段的索引，方便演示
create index idx_name on student_info(name);

现有一个需求，找出name为li开头的学生信息：

#使用到了索引
explain select * from student_info where name like 'li%';
#未使用索引，花费时间更久
explain select * from student_info where LEFT(name,2)='li';

上面的两条sql语句都可以满足需求，然而第一条语句用了索引，第二条没有，一点点的改变真是天差地别。

结论：字段使用函数会让优化器无从下手，B树中的值和函数的结果可能不搭边，所以不会使用索引，即索引失效。字段能不用就不用函数。

类似：

#也不会使用索引
explain select * from student_info where name+''='lisi';

类似的对字段的运算也会导致索引失效。

5.类型转换导致索引失效

#不会使用name的索引
explain select * from student_info where name=123;
#使用到索引
explain select * from student_info where name='123';

如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。

6.不等于(!= 或者<>)索引失效

#创建索引
create index idx_name on student_info(name);
#索引失效
explain select * from student_info where name<>&#39;zhangsan&#39;;
explain select * from student_info where name!=&#39;zhangsan&#39;;

不等于的情况是不会使用索引的。因为!=代表着要进行全文的查找，用不上索引。

7.is null可以使用索引，is not null无法使用索引

#可以使用索引
explain select * from student_info where name is null;
#索引失效
explain select * from student_info where name is not null;

和前一个规则类似的，!=null。同理not like也无法使用索引。

最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。

8.like以%开头，索引失效

#使用到了索引
explain select * from student_info where name like 'li%';
#索引失效
explain select * from student_info where name like '%li';

只要以%开头就无法使用索引，因为如果以%开头，在B树排序的数据中并不好找。

9.OR前后存在非索引的列，索引失效

#创建好索引
create index idx_name on student_info(name);
create index idx_courseId on student_info(course_id);

如果or前后都是索引：

#使用索引
explain select * from student_info where name like 'li%' or course_id=200;

如果其中一个没有索引：

explain select * from student_info where name like 'li%' or class_id=1;

那么索引就失效了，假设还是使用索引，那就变成了先通过索引查，然后再根据没有的索引的字段进行全表查询，这种方式还不如直接全表查询来的快。

10.字符集不统一

字符集如果不同，会存在隐式的转换，索引也会失效，所有应该使用相同的字符集，防止这种情况发生。

三、建议

• 对于单列索引，尽量选择针对当前query过滤性更好的索引

• 在选择组合索引时，query过滤性最好的字段应该越靠前越好

• 在选择组合索引时，尽量选择能包含当前query中where子句中更多字段的索引

• 在选择组合索引时，如果某个字段可能出现范围查询，尽量将它往后放

以上がMySQL がインデックス障害を引き起こす状況はどのようなものですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。