MySQL索引失效原因以及SQL查询语句不走索引原因详解

MySQL索引失效原因以及SQL查询语句不走索引原因详解

目录

• MySQL索引失效原因以及SQL查询语句不走索引原因详解
  • 1. 索引优化思路
  • 1. 隐式的类型转换，索引失效
  • 2. 查询条件包含 or，可能导致索引失效
  • 3. like 通配符可能导致索引失效
    • 没覆盖索引的情况下，左模糊查询导致索引失效
  • 4. 查询条件不满足联合索引的最左匹配原则
  • 5. 在索引列login_time上使用 mysql 的内置函数
  • 6. 对索引列age进行列运算（如，+、-、*、/）, 索引不生效
  • 7. 索引字段age上使用（！= 或者 < >, not in），索引可能失效
  • 8. 索引字段上使用 is null， is not null，索引可能失效 （查询结果行数）
  • 9. 左右join连接，关联的字段编码格式不一样
  • 10. 索引自身失效
  • 11. 要尽量满足全值匹配
  • 12.要满足最佳左前缀法则
  • 13.主键插入顺序尽量自增（避免页面分裂）
  • 14.范围条件右边的列索引失效
  • 15.没覆盖索引时，“不等于”导致索引失效
  • 16 没覆盖索引时，is not null、not like导致索引失效
  • 17.不同字符集导致索引失败，建议utf8mb4
  • 总结

1. 索引优化思路

都有哪些维度可以进行数据库调优？简言之：

• 索引失效、没有充分利用到索引——建立索引
• 关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化
• 服务器调优及各个参数设置（关闭慢查询日志、缓冲、线程数等）——调整my.cnf
• 数据过多——分库分表
• 定期清理垃圾：对于不再使用的表、数据、日志、缓存等，应该及时清理，避免占用过多的MySQL资源，从而提高MySQL的性能。
• 使用合适的存储引擎：MyISAM比较适合读取频繁，写入较少的场景（因为表级锁、B+树叶存地址），而InnoDB比较适合并发写入的场景（因为行级锁、B+树叶存记录）。

关于数据库调优的知识非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。

虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

• 物理查询优化：索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
• 逻辑查询优化：通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法效率可能更高。

用不用索引，最终都是优化器说了算：

优化器是基于什么的优化器? 基于 cost开销(CostBaseOptimizer)，它不是基于规则(Rule-Basedoptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

1. 隐式的类型转换，索引失效

select * from test where num=13911111111; # 失效，num字段是varchar类型，没有加引号

CREATE INDEX idx_name ON student(NAME);
 
#1.手动类型转换，通过调用函数，导致索引失效
EXPLAIN SELECT id, stuno, name FROM student WHERE name=CAST(123 as CHAR);
 
#2.自动类型转换导致索引失效。name字段类型是varchar，你赋值成数字它会默认转成字符串导致索引失败
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 索引优化成目标字符串，走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

假设某手机号列创建时是num varchar(15)
如果上面的手机号没有加引号，查询的时候是字符串跟数字的比较，它们类型不匹配，MySQL 会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。

2. 查询条件包含 or，可能导致索引失效

select * from test where mul=1 or noidx=2; # 可能失效，当mul设为索引列而noidx不是索引列时

• 索引+or+无索引的列：会先走索引列，但无索引的列会进行全表扫描，所以还不如不走索引，直接都全表扫描完事。如果or前后都有索引，那么可能走索引，也可能不走索引。
• 如果它一开始就走全表扫描，直接一遍扫描就完事。Mysql 优化器出于效率与成本考虑，遇到 or 条件，让索引失效，看起来也合情合理。
• 用or连接的两个含null索引字段，不走索引。但是，单个索引含null字段，是走索引的。

注意：如果 or 条件的列都加了索引，索引可能会走也可能不走，平时大家使用的时候，还是要注意一下这个 or，学会用 explain 分析。遇到不走索引的时候，考虑拆开两条 SQL。

MySQL里，即使or左边条件满足，右边条件依然要进行判断。
在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。
index_merge：OR前后的两个条件中的列都是索引时，查询中才使用索引，索引类型是“index_merge”，将两个索引字段分别扫描，然后合并。
因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此所以的条件列也会失效。

3. like 通配符可能导致索引失效

并不是用了 like 通配符，索引一定会失效，而是 like 查询是以 % 开头，才会导致索引失效。

因为字符串开头都不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，索引效率优于全表扫描聚簇索引树。

覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。

没覆盖索引的情况下，like以通配符“%”或“_”开头，导致索引失效。在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为'%'，索引就不会起作用。只有'%'不在第一个位置，索引才会起作用。

原因：没覆盖索引的情况下，字符串开头都不知道是什么，走索引再回表效率不如直接全表扫描

没覆盖索引的情况下，左模糊查询导致索引失效

 #没覆盖索引的情况下，左模糊查询导致索引失效
CREATE INDEX idx_age_name ON student(age, NAME);
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

覆盖索引情况下，左模糊查询索引生效
主要原因也是因为走非聚簇索引B+树遍历叶节点，不回表，效率会比全表扫描时高，查询优化器选择效率高的方案。

#有覆盖索引的情况下，左模糊查询索引生效
CREATE INDEX idx_age_name ON student(age, NAME);
EXPLAIN SELECT id,age,NAME FROM student WHERE NAME LIKE '%abc';

使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';

未使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';

4. 查询条件不满足联合索引的最左匹配原则

MySQl 建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了 (a)、(a,b)、(a,b,c) 三个索引。

5. 在索引列login_time上使用 mysql 的内置函数

select * from user where DATE_ADD(login_time,INTERVAL 1 DAY) = '2022-11-08 00:00:00'; # 失效
select * from user where login_time = DATE_ADD('2022-11-08 00:00:00',INTERVAL 1 DAY); # 有效

6. 对索引列age进行列运算（如，+、-、*、/）, 索引不生效

select * from user where age-1 = 39; # 失效

7. 索引字段age上使用（！= 或者 < >, not in），索引可能失效

select * from user where age != 18; # 有可能失效

其实这个也是跟 mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。平时我们用！= 或者 < >，not in 的时候，要留点心眼。

8. 索引字段上使用 is null， is not null，索引可能失效 （查询结果行数）

很多时候，是因为数据量问题，导致了 MySQL 优化器放弃走索引。同时，平时我们用 explain 分析 SQL 的时候，如果 type=range, 要注意一下哈，因为这个可能因为数据量问题，导致索引无效。

9. 左右join连接，关联的字段编码格式不一样

如user 表的 name 字段编码是 utf8mb4，而 user_job 表的 name 字段编码为 utf8。

10. 索引自身失效

虽然索引有自我维护的能力，但数据表内容修改和更新频繁的情况下，也有可能索引失效，此时需要删除索引，重新建立索引。

11. 要尽量满足全值匹配

查询age and classId and name时，(age,classId,name)索引比(age,classId)快。
查询顺序不重要，例如查询age and classId and name和查询classId and name and age是一样的。

12.要满足最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
例如索引(a,b,c)，只有查询(a),(a,b),(a,b,c)会走索引，而(b),(b,c),(c)都不会走索引。

MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。

13.主键插入顺序尽量自增（避免页面分裂）

对于一个使用 InnoDB 存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在 聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录 主键值从小到大 的顺序进行排序所以如果我们 插入 的记录的 主键值是依次增大 的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的 主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100 之间:

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ， 比如： person_info 表：

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

14.范围条件右边的列索引失效

例如（a,b,c）联合索引，查询条件a,b,c，如果b使用了范围查询，那么b右边的c索引失效。这里右边看的联合索引的键右边。

解决办法：新建联合索引（a,c,b）或（c,a,b），把需要范围查询的字段放在最后
范围包括：(<) (<=) (>) (>=) 和 between。
注意：模糊查询“like”不是范围查询。

CREATE INDEX idx_age_classid_name ON student(age,classId,name);
 
#都不使用范围查询。key_len为73
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId=4 AND student.name = 'abcd';
 
#联合索引第一个参数age使用范围查询，全部索引失效。ken_len为0，全表扫描。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age>30 AND student.classId=4 AND student.name = 'abcd';
 
 
#联合索引第二个参数classId使用范围查询，name索引失效。key_len为10，即只用age和classId。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>3 AND student.name = 'abcd';
 
#联合索引第三个参数name使用范围查询，索引都生效。key_len为73
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId=4 AND student.name > 'abcd';
 

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。创建的联合索引中，务必把范围涉及到的字段写在最后，然后将范围查询条件放置where语句最后。

15.没覆盖索引时，“不等于”导致索引失效

因为“不等于”不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，索引效率优于全表扫描聚簇索引树。
覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。

不等于符号：!= 或者<>，这两个符号意义相同。

没覆盖索引的情况下，使用“不等于”导致索引失效。因为如果使用索引，则需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，找到记录后还要回表，加在一起效率不如全表扫描，所以查询优化器就选择全表扫描了。

**案例： **

 没覆盖索引的情况下，使用“不等于”导致索引失效。因为如果使用索引，则需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，找到记录后还要回表，加在一起效率不如全表扫描，所以查询优化器就选择全表扫描了。
 CREATE INDEX idx_age_name ON student(age, NAME);
#查所有字段，并且使用“不等于”，索引失效
EXPLAIN SELECT * FROM student WHERE age <> 20;

覆盖索引，查的两个字段被联合索引给覆盖了，性能更高。虽然还是需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，但是不需要回表了，整体效率比不用索引更高，查询优化器就又使用索引了。

CREATE INDEX idx_age_name ON student(age, NAME);
#查的两个字段正好被联合索引“idx_age_name ”覆盖了，索引成功
EXPLAIN SELECT age,name FROM student WHERE age <> 20;

案例2：

为name字段创建索引
CREATE INDEX idx_name ON student(NAME);
查看索引是否失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';

或者

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';

场景举例：用户提出需求，将财务数据，产品利润金额不等于0的都统计出来。

16 没覆盖索引时，is not null、not like导致索引失效

因为is not null、not like不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，索引效率优于全表扫描聚簇索引树。覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。

is null可以使用索引，is not null无法使用索引
原因：

在进行索引扫描时，MySQL 会优先利用索引中已经存在的值进行查询，在查询时直接跳过为 NULL 的那些行。但是，如果使用了 IS NOT NULL 条件，那么 MySQL 无法在索引中找到 NULL 值，也就是说，MySQL 无法像前面那样跳过那些为 NULL 的行，只能扫描整张表来找到符合条件的行，因此无法使用索引。

解决方案：

• 设计数据库的时候就将字段设置为 NOT NULL 约束
• 将 INT 类型的字段，默认值设置为0。
• 将字符类型的默认值设置为空字符串('')。

扩展：同理，在查询中使用not like也无法使用索引，导致全表扫描。

• IS NULL: 可以触发索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

• IS NOT NULL: 无法触发索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

17.不同字符集导致索引失败，建议utf8mb4

不同的字符集进行比较前需要进行转换会造成索引失效。

数据库和表的字符集统一使用utf8mb4。统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。

总结

• 尽量全值匹配：查询age and classId and name时，(age,classId,name)索引比(age,classId)快。
• 考虑最左前缀：联合索引把频繁查询的列放左。索引（a,b,c），只能查(a,b,c),(a,b),(a)。
• 主键尽量自增：如果主键不自增，需要查找目标位置再插入，并且如果目标位置所在数据页满了就必须得分页，造成性能损耗。
• 计算、函数导致索引失效：计算例如where num+1=2，函数例如abs(num)取绝对值
• 类型转换导致索引失效：例如name=123，而不是name='123'。又例如使用了不同字符集。
• 范围条件右边的列索引失效：例如（a,b,c）联合索引，查询条件a,b,c，如果b使用了范围查询，那么b右边的c索引失效。建议把需要范围查询的字段放在最后。范围包括：(<) (<=) (>) (>=) 和 between。
• 没覆盖索引时，“不等于”导致索引失效：因为“不等于”不能精准匹配，全表扫描二级索引树再回表效率不如直接全表扫描聚簇索引树。但使用覆盖索引时，联合索引数据量小，加载到内存所需空间比聚簇索引树小，且不需要回表，索引效率优于全表扫描聚簇索引树。覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。
• 没覆盖索引时，左模糊查询导致索引失效：例如LIKE '%abc'。因为字符串开头都不能精准匹配。跟上面一个道理。
• 没覆盖索引时，is not null、not like无法使用索引：因为不能精准匹配。跟上面一个道理。
• “OR”前后存在非索引列，导致索引失效：MySQL里，即使or左边条件满足，右边条件依然要进行判断。
• 不同字符集导致索引失败：建议utf8mb4，不同的字符集进行比较前需要进行 转换 会造成索引失效。
  关于索引失效原因有很多，以上也只是简单介绍了一下，具体失效原因，还得去自己分析，具体方法就是SQL的执行计划 EXPLAIN 关键字了。
  Mysql提供了这个关键字让我们优化索引，使查询更快，分析优化器的表连接，使它采用最优的顺序。使用这个 explain 关键字可以查看查询语句是否走索引了以及走了哪个索引。

一般性建议

• 对于单列索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择能够当前query中where子句中更多的索引。
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

# 命令行执行以下语句即可查看查询语句是否走了索引，在查询语句最前面加上 explain 即可

mysql> explain select * from sampleInfo where agents = "XXX中心有限公司";