MySQL导致索引失效的几种情况

目录
  • 一、准备工作
  • 二、索引失效规则
    • 1.优先使用联合索引
    • 2.最左匹配原则
    • 3.范围条件右边的列索引失效
    • 4.计算、函数导致索引失效
    • 5.类型转换导致索引失效
    • 6.不等于(!= 或者<>)索引失效
    • 7.is null可以使用索引,is not null无法使用索引
    • 8.like以%开头,索引失效
  • 9.OR前后存在非索引的列,索引失效
    • 10.字符集不统一
  • 三、建议

一、准备工作

首先准备两张表用于演示:

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

二、索引失效规则

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树中找到所有name为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='11111' 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<>'zhangsan';
explain select * from student_info where name!='zhangsan';

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

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导致索引失效的几种情况的文章就介绍到这了,更多相关MySQL 索引失效内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2022-06-22

浅谈mysql哪些情况会导致索引失效

下面有一些培训教学机构的口诀和我个人的一些总结: 为了讲解以下索引内容,我们先建立一个临时的表 test02 CREATE TABLE `sys_user` ( `id` varchar(64) NOT NULL COMMENT '主键', `name` varchar(64) DEFAULT NULL COMMENT '名字', `age` int(64) DEFAULT NULL COMMENT '年龄', `pos` varchar(64) DEFAULT NULL COMMENT '职位

mysql索引失效的五种情况分析

索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 3.like查询是以%开头 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况 show status lik

Mysql 5.6 &quot;隐式转换&quot;导致的索引失效和数据不准确的问题

背景 在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢.这个varchar字段有一个复合索引.其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据. 使用的是mysql 5.6版本,innoDB引擎 实际情况如下 下面我们来看一下执行的结果 在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字.不然就会报错 还有可能查出来的数据不是我们想要的数据.如下图 分析 从执行结果来

mysql索引失效的几种情况分析

1.最佳左前缀原则--如果索引了多列,要遵守最左前缀原则.指的是查询要从索引的最左前列开始并且不跳过索引中的列. 前提条件:表中已添加复合索引(username,password,age) 分析:该查询缺少username,查询条件复合索引最左侧username缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描 分析:查询条件缺少username,password,查询条件复合索引最左侧username,password缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描

MySQL索引失效原理

目录 1.索引失效原因 2.再来看看哪些情况会破坏索引的有序性. - 对索引字段做函数操作 - 隐式类型转换 - 隐式字符编码转换 3.总结 1.索引失效原因 首先看看哪些情况下,将会导致查找不能利用索引的有序性. 假设一个表test中有a,b,c,d四个字段,c是主键. 在a,b字段上建立联合索引(a,b):CREATE index idx_a_b on test(a,b); B+树联合索引.JPG 可以得到的规律是:优先按a字段从小到大排序,a字段相等的按b字段从小到大排序: 分析以下情况,

mysql的in会不会让索引失效?

mysql的in会让索引失效吗?不会! 看结果: mysql> desc select * from tb_province where name in ('lily3', 'lily2', 'lily1'); +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_t

mysql为字段添加和删除唯一性索引(unique) 的方法

1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

MySQL中索引失效的常见场景与规避方法

前言 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时

导致MySQL索引失效的一些常见写法总结

前言 最近一直忙着处理原来老项目遗留的一些SQL优化问题,由于当初表的设计以及字段设计的问题,随着业务的增长,出现了大量的慢SQL,导致MySQL的CPU资源飙升,基于此,给大家简单分享下这些比较使用的易于学习和使用的经验. 这次的话简单说下如何防止你的索引失效. 再说之前我先根据我最近的经验说下我对索引的看法,我觉得并不是所以的表都需要去建立索引,对于一些业务数据,可能量比较大了,查询数据已经有了一点压力,那么最简单.快速的办法就是建立合适的索引,但是有些业务可能表里就没多少数据,或者表的使用

MySQL索引失效的几种情况汇总

一.索引不存储null值 更准确的说,单列索引不存储null值,复合索引不存储全为null的值.索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描. 为什么索引列不能存Null值? 将索引列值进行建树,其中必然涉及到诸多的比较操作.Null值的特殊性就在于参与的运算大多取值为null. 这样的话,null值实际上是不能参与进建索引的过程.也就是说,null值不会像其他取值一样出现在索引树的叶子节点上. 二.不适合键值较少的列(重复

oracle数据库索引失效

今天一个同事突然问我索引为什么失效.说实在的,失效的原因有多种: 但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表) 2. 统计信息失效      需要重新搜集统计信息 3. 索引本身失效      需要重建索引 下面是一些不会使用到索引的原因 索引失效 1) 没有查询条件,或者查询条件没有建立索引 2) 在查询条件上没有使用引导列 3) 查询

mysql之explain使用详解(分析索引)

explain显示了mysql如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句. 使用方法,在select语句前加上explain就可以了,如: explain select * from statuses_status where id=11; explain列的解释 table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型.从最好到最差的连接类型为const.eq_reg.ref.range.indexhe和all

MySQL中因字段字符集不同导致索引不能命中的解决方法

什么是索引?为什么要建立索引? 索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间. 例如:有一张person表,其中有2W条记录,记录着2W个人的信息.有一个Phone的字段记录每个人的电话号码,现在想要查询出电话号码为xxxx的人的信息. 如果没有索引,那么将从表中第一条记录

如何提高MYSQL数据库的查询统计速度 select 索引应用

数据库系统是管理信息系统的核心,基于数据库的联机事务处理(OLTP)以及联机分析处理(OLAP)是银行.企业.政府等部门最为重要的计算机应用之一.从大多数系统的应用实例来看,查询操作在各种数据库操作中所占据的比重最大,而查询操作所基于的SELECT语句在SQL语句中又是代价最大的语句.举例来说,如果数据的量积累到一定的程度,比如一个银行的账户数据库表信息积累到上百万甚至上千万条记录,全表扫描一次往往需要数十分钟,甚至数小时.如果采用比全表扫描更好的查询策略,往往可以使查询时间降为几分钟,由此可见