MySQL 索引失效的情况，违反最左前缀法则联合索引一定失效？

索引的用途：MySQL 除了存储数据之外，还维护着一种数据结构——索引。索引是为了提高查询数据时的效率，而使用索引也需要注意一些细节，否则会造成索引失效的情况，造成查询性能降低。

索引失效情况一：如果数据库表中有一个字段为 colum ，类型为 varchar ，并为其建立了索引。那么在使用这个索引时，如果在 where 条件后的字符串未加单引号，那么 MySQL 进行数据类型转换的时候出现异常而导致索引失效。

举例说明：有一个学生表，其中一个字段为 name ， 类型是 varchar。

表结构如下：

并在 name 字段上建立索引：

create index idx_name on student(name);

此时查询姓名为“小明”的学生的数据：

explain select * from student where name = '小明';

查询结果为：

此时我们根据 explian 的结果的 key 字段可知，本次查询使用了 name 这个字段建立的索引。

下面开始模拟字符串未加单引号查询数据的情况：由于写中文会报错，我们改为 1 ；

explain select * from student where name = 1;

查询结果为：

key 字段的结果为 null ，显然本次查询没有走索引。

索引失效情况二：如果进行字符串的模糊匹配查询时，在匹配字段前加了百分号也会导致索引失效。

举例：查询名字里带 ‘明’ 字的学生信息；

SQL：

select * from student where name like '%明%';

查询结果：

key 字段的结果为 null ，显然本次查询没有走索引。在MySQL中，进行字符串模糊匹配时，如果使用百分号（%）作为通配符，会导致索引失效的原因是，百分号表示匹配该位置之后的任意字符序列，这使得数据库无法使用索引来快速定位到符合条件的行。索引是用来加速等值查询（如=）和范围查询（如>或BETWEEN）的，但当使用百分号作为前缀时，数据库必须对表中的每一行进行全表扫描来确定是否匹配条件，因为匹配的模式可能在任何位置出现。

索引失效的情况三：联合索引的使用过程中，跳过了位于左边的索引，会造成索引失效。

下面我们针对 age 、name 建立联合索引：

create index idx_name_age on student (name,age);

为了排除单列索引的干扰，这里我们使用 SQL 提示的方式，使得查询强制走联合索引：

explain select * from student force index(idx_name_age)  where name = '小明' and age > 14;

查询结果如下：

从 key 字段的值我们可与i看出，本次查询走了我们刚才创建的联合索引。

下面我们模拟违反最左前缀法则查询的情况：

explain select * from student  where  age > 14;

查询结果：

可以看出依然使用了联合索引。

那这是怎么一回事呢？难道我们被骗了？

下面是我向 AI 提问的结果：

1. 隐式转换：如果查询条件中的列与联合索引中的列类型不完全匹配，但MySQL能够进行隐式类型转换，那么联合索引可能仍然有效。

2. 查询优化器：MySQL的查询优化器非常智能，它可能会根据查询的具体情况选择使用联合索引。例如，如果优化器认为使用部分索引比不使用索引更有效，它可能会这样做。

3. 索引统计信息：MySQL会收集索引列的统计信息，包括列的分布情况。如果优化器根据这些统计信息判断，即使不满足最左前缀法则，使用索引也是有利的，那么索引可能会被使用。

4. 查询条件的顺序：在某些情况下，查询条件的顺序可能影响索引的使用。如果查询条件中满足最左前缀的列在WHERE子句中出现得更早，MySQL可能会使用这部分索引。

5. 覆盖索引：如果查询只需要从索引中获取数据，而不需要访问表中的数据行（即覆盖索引），那么即使查询条件不满足最左前缀法则，索引仍然可能有效。

6. 前缀索引：如果联合索引中的某个列是前缀索引，那么即使查询条件不完全符合最左前缀法则，只要它与索引的前缀匹配，索引仍然可能被使用。

7. 查询优化器的启发式规则：查询优化器可能使用一些启发式规则来决定是否使用索引，这些规则可能在某些情况下允许违反最左前缀法则的查询仍然使用索引。

8. 索引选择性：如果某个索引列的选择性非常好（即该列的值分布非常广泛），即使查询条件不满足最左前缀法则，优化器也可能选择使用这个索引。

9. 强制使用索引：在某些情况下，如果通过SQL提示（hint）强制MySQL使用特定的索引，那么即使违反了最左前缀法则，索引也可能被使用。

下面我们针对第五点进行测试，由于我们的字段只有 id、name、age 三个字段，因此，此时创建的联合索引即为覆盖索引。即从联合索引中可以得到 id、name、age 三个字段的信息。

那么我们再新建一个字段 gender，类型为 tinyInt，表示性别；

更改后的表结构如下：

我们继续执行上面的 SQL，查询结果如下：

可以看出上次查询使用的是联合索引，而这次使用的为单列索引。继续测试，为了排除单例索引的干扰，我们删除单列索引：

drop index idx_age on student;

继续执行上面的查询 SQL，结果为：

可以看出，本次查询并未使用联合索引，违反最左前缀法则，且联合索引不是覆盖索引，索引失效！！！

大家可以根据 AI 回答的其他情况做进一步的测试，这里就不一一测试了，不测不知道，测了才发现网上说的很多东西其实并不全面，建议大家实践出真知。