浅谈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 '职位',
  PRIMARY KEY (`id`),
  KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

这个表有四个字段 主键 、名字、年龄、职位

下面我们来讲解第一个口诀:
1.全值匹配我最爱
2.最佳左前缀法则(重要)

全值匹配意思就是联立的复合索引的顺序和个数要和检索的条件顺序和个数相同。
最佳左前缀法则是指,如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
下面我们给这个表建立一个复合索引

ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(name,age,pos);

以下是我们的检索语句:

SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';

我们通过在检索语句前面加关键字 EXLAIN,可以知道是否使用的索引

(1)EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';
(2)EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 ;
(3)EXPLAIN SELECT * FROM sys_user WHERE name='小明'  AND pos ='java';

通过展示的结果我们可以知道,第一个复合索引的三个字段我们都用了,第二个复合索引我们只用到两个字段,第三个复合索引我们只用到一个字段。三个语句我们都用到索引,显然第一种是最优的。

我们再看看哪种情况会失效:

(4)EXPLAIN SELECT * FROM sys_user WHERE age = 22;
(5)EXPLAIN SELECT * FROM sys_user WHERE pos ='java';
(6)EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos ='java';

以上三种情况都变成了全表扫描,原因是违反了最左左前缀原则,因为复合索引最左边的是name,当检索条件name没在前面索引将失效,第一种情况满足了全值匹配,第二种满足了两个字段name和age,第三种因为只满足了name,所以索引只用到name。

3.不在索引列上做任何操作(计算、函数(自动或手动)类型转换),会使索引失效转为全表扫描

(7)EXPLAIN SELECT * FROM sys_user WHERE  LEFT(name,1)='小明';

第七种情况失效是因为索引列做了计算或者函数的操作,导致了全表扫描。

4.存储引擎不能使用索引中范围条件右边的列
可能大家关看上面的文字不知道是什么意思,下面我们执行一下查询语句就清楚了

(8)EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age < 22 AND pos ='java';

从上图我们可以知道type变成了范围级别,也就是说age<22之后的pos字段的索引失效了。

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致),减少select * 的使用
这个就是字面意思,查询具体的字段比查询*效率更高,下面我们坐一下对比

(9)EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';
(10)EXPLAIN SELECT name,age,pos FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';

6.mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
(!= 或者<>)通常会匹配到大量数据,当使用索引的花销大于全表扫描时,mysql则会放弃使用索引而选择全表扫描

(11)EXPLAIN SELECT * FROM sys_user WHERE name !='小明'

结果显示索引失效导致了全表扫描

7.is null,is not null 也无法使用索引
is null,is not null 通常会匹配到大量数据,当使用索引的花销大于全表扫描时,mysql则会放弃使用索引而选择全表扫描

(12)EXPLAIN SELECT * FROM sys_user WHERE name is not null

8.like以通配符开头('%abc…')mysql索引会失效变成全表扫描的操作,(%写右边则可以避免索引失效,如果业务实在需要'%abc…%'则可以用覆盖索引避免索引失效)

(13)EXPLAIN SELECT * FROM sys_user WHERE name like '%明%'
(14)EXPLAIN SELECT * FROM sys_user WHERE name like '明%'
(15)EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%'

从上面的结果,第一种索引失效,第二种只写右边的%则可以避免索引失效,第三种如果业务实在需要‘%abc…%'这种sql,则可以用覆盖索引解决索引失效的问题

9.字符串不加单引号索引会失效

(16)EXPLAIN SELECT * FROM sys_user WHERE name=222;

因为检索字符串是必须加单引号,上面用用了222是int类型,mysql在检索的时候会判断name是varchar的类型会将222转换为'222'进行检索,索引列发生了类型转换,故索引失效。

10.少用or,用它连接时会索引失效

(16)EXPLAIN SELECT * FROM sys_user WHERE name='小明' or age = 22;

到此这篇关于浅谈mysql哪些情况会导致索引失效的文章就介绍到这了,更多相关mysql 索引失效内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql索引失效的十大问题小结

    目录 背景 一.查询条件包含or,可能导致索引失效 二.如何字段类型是字符串,where时一定用引号括起来,否则索引失效 三.like通配符可能导致索引失效. 四.联合索引,查询时的条件列不是联合索引中的第一个列,索引失效. 五.在索引列上使用mysql的内置函数,索引失效. 六.对索引列运算(如,+.-.*./),索引失效. 七.索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效. 八.索引字段上使用is null, is not null,可能导致索引失效. 九.左连

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

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

  • MySQL索引失效的几种情况详析

    1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%') 假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件,由于前面是 模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件.这样会导致全索引扫描或者全表扫 描.如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,当碰到B开头的 数据时,就可以停止查找了,因为后面的数据一定不满足要求.

  • MySQL中有哪些情况下数据库索引会失效详析

    前言 要想分析MySQL查询语句中的相关信息,如是全表查询还是部分查询,就要用到explain. 索引的优点 大大减少了服务器需要扫描的数据量 可以帮助服务器避免排序或减少使用临时表排序 索引可以随机I/O变为顺序I/O 索引的缺点 需要占用磁盘空间,因此冗余低效的索引将占用大量的磁盘空间 降低DML性能,对于数据的任意增删改都需要调整对应的索引,甚至出现索引分裂 索引会产生相应的碎片,产生维护开销 一.explain 用法:explain +查询语句. id:查询语句的序列号,上面图片中只有一

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

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

  • 浅谈MySql整型索引和字符串索引失效或隐式转换问题

    目录 问题概述 问题重现 问题引申 结论 问题概述 今天在上班时,DBA突然找出来一段sql,表示该sql存在隐式转换,不走索引.经过我们的查看后,发现是类型varchar的字段, 我们使用条件传入了数值型的值,由于担心违反保密协议,在此就不贴图了,由我重现一下类似情况给大家看一下. 问题重现 首先我们先创建一张用户表test_user,其中USER_ID为了效果我们设置为varchar类型且加上唯一索引. CREATE TABLE test_user ( ID int(11) NOT NULL

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

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

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

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

  • mysql 联合索引生效的条件及索引失效的条件

    目录 1.联合索引失效的条件 2.索引失效的条件 1.联合索引失效的条件 联合索引又叫复合索引.两个或更多个列上的索引被称作复合索引. 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单

  • mysql回表致索引失效案例讲解

    简介 mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下,需要做回表操作获取记录的所需字段. mysql执行sql前会执行sql优化.索引选择等操作,mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价,从中选择一个mysql认为代价最小的方式进行sql查询操作.而在回表数据量比较大时,经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况. 案例 示例如下,在5.6版本的mysql.1CPU2G内存的Linux环境下,新建一个测试表,并创建将近

随机推荐