当前位置:   article > 正文

MYSQL 索引失效的十个场景(一)_mysql索引失效场景

mysql索引失效场景

一、查询条件包含or,可能导致索引失效

新建一个student表,它有一个普通索引userId,结构如下:

  1. CREATE TABLE `student` (
  2. `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  3. `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  4. `score` decimal(10,2) DEFAULT NULL,
  5. PRIMARY KEY (`id`),
  6. KEY `student_name_IDX` (`name`) USING BTREE
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  • 执行查询sql(未包含or),它是会走索引的,如下图所示: 
explain select * from student s where name ='liuliu' 

  •  执行查询sql(包含or,且score未添加索引),它就不走索引,如下图所示:
explain select * from student s where name ='liuliu' or score =80

分析&结论:

  • 对于or+score没有索引的这种情况,假设它走了name的索引,但是走到score查询条件时,它还得全表扫描,假如走索引也就是需要三步过程:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,就只需一步;mysql的查询优化器,处于效率与成本考虑,遇到这种情况,明显全表扫描更合理。

注意: 如果or条件的列都加了索引,索引有可能会走,也有可能不会走。

 二、字段类型不匹配,可能导致索引失效

  • 执行查询sql(name类型是vachar,传入是数值类型)

分析与结论:

  • 类型不匹配,MySQL会做隐式的类型转换,索引失效

 三、like通配符(前缀模糊匹配)可能导致索引失效

并不是用了like通配符,索引一定失效,而是like查询是以%开头(前缀模糊匹配),才会导致索引失效。

  • like查询是以%开头
explain select * from student s where name like '%zhang%'

 

  •  like查询是不以%开头
explain select * from student s where name like 'zhang%'

四、联合索引,查询时的条件列不包含联合索引中的第一个列,索引失效 

  1. CREATE TABLE `student` (
  2. `id` bigint(20) NOT NULL AUTO_INCREMENT,
  3. `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  4. `score` decimal(10,2) DEFAULT NULL,
  5. `subject` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科目',
  6. `create_time` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建时间',
  7. PRIMARY KEY (`id`),
  8. KEY `student_name_IDX` (`name`) USING BTREE,
  9. KEY `student_subject_IDX` (`subject`,`score`) USING BTREE,
  10. KEY `student_create_time_IDX` (`create_time`) USING BTREE
  11. ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

表结构student有一联合索引: `student_subject_IDX` (`subject`,`score`)

  • 包含联合索引的第一列:走联合索引
explain select * from student s where score >80 and subject ='英语'

  • 不包含联合索引的第一列:不会走联合索引
explain select * from student s where score >80 

 

分析与结论:

  • 当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则

  • 联合索引不满足最左原则,索引一般会失效。

 五、在索引列上使用mysql的内置函数,索引失效。

  1. CREATE TABLE `student` (
  2. `id` bigint(20) NOT NULL AUTO_INCREMENT,
  3. `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  4. `score` decimal(10,2) DEFAULT NULL,
  5. `subject` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科目',
  6. `create_time` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建时间',
  7. PRIMARY KEY (`id`),
  8. KEY `student_name_IDX` (`name`) USING BTREE,
  9. KEY `student_subject_IDX` (`subject`,`score`) USING BTREE,
  10. KEY `student_create_time_IDX` (`create_time`) USING BTREE
  11. ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

表结构student有一索引:`student_create_time_IDX` (`create_time`)

  • 不使用mysql函数:会走索引
explain select * from student s where create_time='2023-02-02'

  • 使用mysql函数:不会走索引
explain select * from student s where date(create_time)='2023-02-02'

注意:从 MySQL 8.0 开始,索引特性增加了函数索引,即可以针对函数计算后的值建立一个索引,也就是说该索引的值是函数计算后的值,所以就可以通过扫描索引来查询数据 

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Monodyee/article/detail/381437
推荐阅读
相关标签
  

闽ICP备14008679号