当数据库查询无法有效利用索引时，就会发生索引失效，这会显著降低查询性能。以下是 MySQL 中一些常见的索引失效场景：

1. 使用非前导列进行查询

索引列的顺序很重要。如果查询使用非前导列，则索引将无法使用，因为 MySQL 只能从左到右使用索引。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引： sql SELECT FROM mytable WHERE name = 'John'; 以下查询将不会使用索引，因为它是使用非前导列 age： sql SELECT FROM mytable WHERE age = 30;

2. 对索引列使用函数或表达式

如果对索引列使用函数或表达式，则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引： sql SELECT FROM mytable WHERE name LIKE 'John%'; 以下查询将不会使用索引，因为它使用函数 UPPER()： sql SELECT FROM mytable WHERE UPPER(name) = 'JOHN';

3. 隐式数据类型转换

如果进行隐式数据类型转换，则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_age ON mytable (age); 以下查询将使用索引： sql SELECT FROM mytable WHERE age = 30; 以下查询将不会使用索引，因为它进行隐式数据类型转换将字符串 '30' 转换为整数： sql SELECT FROM mytable WHERE age = '30';

4. 使用 OR 条件连接不同列

如果在索引列之间使用 OR 条件，则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_name ON mytable (name); CREATE INDEX idx_age ON mytable (age); 以下查询将使用索引： sql SELECT FROM mytable WHERE name = 'John' OR age = 30; 以下查询将不会使用索引： sql SELECT FROM mytable WHERE name = 'John' AND age = 30;

5. 使用 LIKE 以通配符开始的模式

如果在 LIKE 模式中以通配符 % 开始，则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255)); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引： sql SELECT FROM mytable WHERE name LIKE 'John%'; 以下查询将不会使用索引： sql SELECT FROM mytable WHERE name LIKE '%John';

6. 范围查询后的列不使用索引

如果在范围查询之后使用非索引列，则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_age ON mytable (age); 以下查询将使用索引： sql SELECT FROM mytable WHERE age BETWEEN 20 AND 30; 以下查询将不会使用索引，因为它在范围查询之后使用了非索引列 name： sql SELECT FROM mytable WHERE age BETWEEN 20 AND 30 AND name = 'John';

7. WHERE 子句中使用不等式（<> 或 !=）

如果在 WHERE 子句中使用不等式 (<> 或 !=)，则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255)); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引： sql SELECT FROM mytable WHERE name = 'John'; 以下查询将不会使用索引，因为它使用不等式 !=： sql SELECT FROM mytable WHERE name != 'John';

8. 使用 NULL 值进行查询

如果查询中使用 NULL 值，则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255)); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引： sql SELECT FROM mytable WHERE name IS NULL; 以下查询将不会使用索引，因为它使用不等式 !=： sql SELECT FROM mytable WHERE name = NULL;

9. 索引列参与计算或拼接

如果索引列参与计算或拼接，则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), address VARCHAR(255)); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引： sql SELECT FROM mytable WHERE name = 'John'; 以下查询将不会使用索引，因为它包含计算： sql SELECT FROM mytable WHERE name = 'John' AND address = '123 Main Street';

10. 数据分布不均匀导致的优化器选择不使用索引

如果数据分布不均匀，优化器可能会选择不使用索引。 例如，如果一个索引列的值非常稀疏，则优化器可能会认为使用索引的成本高于全表扫描的成本。

11. 索引选择性不高

如果索引的选择性不高，则优化器可能会选择不使用索引。 索引选择性是指索引中唯一值的比率。如果索引的选择性较低，则优化器可能会认为使用索引的成本高于全表扫描的成本。

结论

了解索引失效场景对于优化 MySQL 查询性能至关重要。通过避免这些场景，你可以确保你的查询充分利用索引，从而提高性能。

---

索引失效的情况和解释

索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)　注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引　2.对于多列索引，不是使用的第一部分，则不会使用索引　查询是以%开头4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外，查看索引的使用情况 show status like ‘Handler_read%; 大家可以注意： handler_read_key:这个值越高越好，越高表示使用索引查询到的次数 handler_read_rnd_next:这个值越高，说明查询低效。 ------------------ 1、WHERE字句的查询条件里有不等于号（WHERE column!=...），MYSQL将无法使用索引 2、类似地，如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=...），MYSQL将无法使用索引 3、在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了 索引也不会使用 4、如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能 使用索引。 比如说，如果查询条件是LIKE abc%,MYSQL将使用索引；如果条件是LIKE %abc，MYSQL将不使用索引。 5、在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。 尽管如此，在涉及多个数据表的查 询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。 6、如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。 比如说，如果某个数据列里包含了净是 些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

索引失效的几种情况

索引失效的几种情况分别是：隐式转换、类型不一致。

1、隐式转换

隐式转换会导致索引失效，特别是在查询时将字段作为number类型以where条件传给Oracle时。这种错误的行为在开发中是常见的，也是经常会犯的错误。因为表的字段定义都是varchar2类型，而查询时将该字段设置为number类型会导致索引不可访问。

2、类型不一致

类型不一致会导致索引失效。在设计表字段时，必须保持一致性，比如user表的id是int自增。如果使用数字类型进行查询，中间存在隐式类型转换，就会导致索引失效。

此外，如果查询条件中使用了一个不匹配索引的数据类型，MySQL将无法使用该索引来优化查询，从而导致索引失效。

索引的优缺点

一、索引的优点

1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

二、索引的缺点

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。