MySQL索引可能失效之or、is null、is not null、不等于(!=,<>)、联合索引

这篇具有很好参考价值的文章主要介绍了MySQL索引可能失效之or、is null、is not null、不等于(!=,<>)、联合索引。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1、如果 A,B 两列都有索引,那么 

select * from Table where A=a or B=b; 

会走索引吗?

答案:会,因为 A,B都有索引;

2、如果 A,B有索引,但是C没有索引;

select * from Table where A=a or B=b or C =c;

会走索引吗?

答案:不会走,因为C是or的形式,且没有索引

3、如果 建了A B联合索引,但是查询的时候 是B A,会走索引吗?

select * from Table where  B=b and A=a ;

答案: 会走索引,mysql会自动优化;

4、如果 建了A B联合索引,也建了 B,A 联合索引,但是查询的时候 是B A,会走那个索引吗?

select * from Table where  B=b and A=a ;

答案: 不一定走 BA 索引,走那个索引,需要mysql会把sql优化,看 A列的数据过滤多,还是B的过滤多;如果 A的列数据能过滤更多数据,那么会走AB,如果B的列能过滤更多数据,则走BA;

5、给主键name字段、card字段加上索引,

select * from user where name !='123';

select * from user where name not in (7,8);

select * from user where age !='18' ;

select * from user where age not in (7,8);

答案: name字段为主键,索引不失效(range索引) ;age字段不是主键,即使age字段设索引也会失效。另外mysql5.7和5.8不同的版本效果不一样,5.7中sql执行结果是全表扫描,而5.8中使用了 range 类型索引。5.7中如果想使用索引将where条件后的不等于用大于和小于代替。

6、给单个name字段、单个card字段加上索引,查询name为非空的语句(card同样)

select * from user where name is not null

答案: 会走索引

7、给单个name字段、单个card字段加上索引,查询name为非空或card为非空的语句

select * from user where name is not null or card is not null;

答案: 不会走索引。它两用or连接起来,索引就失效了。很多时候,也是因为数据量问题,导致了MySQL优化器放弃走索引。同时,平时我们用explain分析SQL的时候,如果type=range,要注意一下哈,因为这个可能因为数据量问题,导致索引无效。

8、给单个name字段加上索引,

select * from user where name is not null索引是否失效?

答案:如果name字段不允许为空, is null 和 is not null这两种情况索引都会失效(索引一般加在非空列!)

如果name字段允许为空,则is null走 ref 类型的索引,而is not null走 range 类型的索引(如果返回数据量过大,也会索引失效)

补充:

导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描,于是mysql宁可全表扫描也不愿意使用索引。使用索引的时间成本高于全表扫描的临界值(10%~30% 左右)。而is null、is not null、!=这些判断条件经常会出现在这些回表范围很大的场景,然后被人误解为是这些判断条件导致的索引失效。

9、联合索引不满足最左匹配原则相关

建立一个联合索引:idx_code_age_name( code , age , name )

#where 条件后的字段包含了联合索引的所有索引字段,并且顺序是按照: code 、 age 、name
explain  select * from test1 where code='001' and age=18  and  name='张飞'
#可以看出已经走了联合索引idx_code_name_age,索引是使用充分的,索引使用效率最佳

#where 条件后的字段包含了联合索引的所有索引字段,顺序是不按照: code 、 age 、name。
explain  select * from test1 where code='001' and  name='张飞' and age=18
#可以看出执行结果跟第一种情况一样。注意:这种情况比较特殊,在查询之前mysql会自动优化顺序

#where 条件后的字段包含了联合索引中的: code 字段
explain  select * from test1 where code='001'
#也走了索引,但是索引长度有所变化,现在变成了 92 , 92 = 30*3 + 2 ,只用到了一个索引字段code,索引使用不充分

#where 条件后的字段包含了联合索引中的: age 字段 或者 name 字段
explain  select * from test1 where age=18
explain  select * from test1 where name='张飞';
#全表扫描,所有的索引都失效了

#where 条件后的字段包含了联合索引中的: code 和 age 字段
explain  select * from test1 where code='001' and age=18;
#走了索引,但是索引长度变成了: 96 , 96 = 30*3 + 2 + 4 ,只用到了两个索引字段code和age,索引使用也不充分。

#where 条件后的字段包含了联合索引中的: code 和 name 字段
explain  select * from test1 where code='001' and  name='张飞';
#索引长度跟第3种情况一样,长度也是 92 。也就是说只用到了一个索引字段 code ,而 age 字段的索引失效了

#where条件后的字段包含了联合索引中的:age 和 name 字段
explain  select * from test1 where age=18  and  name='张飞';
#全表扫描,所有的索引都失效了

10、联合索引的范围查询放到最后

例如联合索引 name,age,height 其中 age需要范围查询(14-18)岁的学生,这时候,要把age放到联合索引列的最后(联合索引中的age列放在最后,不是指where条件中的age放最后。如果联合索引中的范围列放在最后了,即使where条件中的范围列没放最后也能正常走到索引)

摘抄于:

https://www.cnblogs.com/aspirant/p/16166851.html

MySQL 索引失效详解_is not null 索引失效_王林-wlin的博客-CSDN博客文章来源地址https://www.toymoban.com/news/detail-669635.html

到了这里,关于MySQL索引可能失效之or、is null、is not null、不等于(!=,<>)、联合索引的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 出现 “‘mysql’ is not recognized as an internal or external command, operable program or batch file解决方法

    在 Windows 系统的 DOS 窗口通过命令行的方式登录 MySQL,出现 “‘mysql’ is not recognized as an internal or external command, operable program or batch file” 的提示 意味着系统无法识别或找不到 MySQL 的可执行文件 。这可能是由于以下几个原因导致的: MySQL 未正确安装:确保 MySQL 已正确安装并

    2024年02月07日
    浏览(52)
  • Mysql 报 java.sql.SQLException:null,message from server:“Host ‘‘ is not allowed to connect.解决方案

    这个错误i是因为mysql数据库没有放开远程访问权限引起的,以mysql8为例 首先进入Mysql 安装目录,然后输入命令: mysql -uroot -p ;具体参见下图: 再输入 use mysql; 回车执行, 接着输入, show tables; 回车执行 输入, select host from user; 回车执行,这里特别说明一下,我这个是已经放

    2024年04月28日
    浏览(42)
  • 【MySQL异常】解决java.sql.SQLException: null, message from server: “Host xxx‘ is not allowed to connect to

    Spring Boot + MySQL 本机访问MySQL数据库正常(即使用 localhost 或者 127.0.0.1 两种方式)。局域网内同事的电脑访问我电脑的数据库结果报错。用户使用的是root用户,即超管账户。 查看防火墙设置,是否允许远程访问mysql数据库这个程序。经查看本机确实限制了不允许远程访问msy

    2024年02月04日
    浏览(47)
  • 【nacos升级到2.2.2版本】报错:errCode: 102, errMsg: dataSource or tableName is null

    为解决Nacos存在权限绕过的漏洞问题,需要升级到最新版本 1、下载最新版本【https://github.com/alibaba/nacos/releases/】 2、上传到对应服务器,备份原先的nacos,替换bin,conf,target文件,在conf目录下,修改对应(复制原先)的application.properties文件,再启动即可 或者 上传到对应服务

    2024年02月11日
    浏览(38)
  • MySQL会导致索引失效的情况与解决索引失效的方法

    什么情况会导致索引失效 索引失效也是慢查询的主要原因之一,常见的导致索引失效的情况有下面这些: 1.使用 SELECT * 进行查询; 2.创建了组合索引,但查询条件未准守最左匹配原则; 3.在索引列上进行计算、函数、类型转换等操作; 4.以 % 开头的 LIKE 查询比如 like \\\'%abc\\\'; ; 5.查

    2023年04月08日
    浏览(74)
  • Kafka Group coordinator xxx:9092 (id: xxx rack: null) is unavailable or invalid

    Kafka本身启动和相互通信没有问题,但是在使用Java API 模拟消费者连接到Kafka的时候出现报错 我们可以注意一下控制台给出的域名地址,这个域名可能是因为解析问题或者限于远程服务器的安全策略做了转换 验证这个问题的办法,在windows开启telnet命令,在cmd中 发现是无法连

    2024年02月16日
    浏览(37)
  • mysql联合索引和普通索引的区别

            MySQL中,联合索引和普通索引都是用于加速查询的索引类型。它们之间的区别在于索引的列数和列的顺序。         普通索引只对单个列进行索引,而联合索引则同时对多个列进行索引,这些列可以按照特定的顺序组合在一起。例如,可以为表中的两个列(列

    2024年02月16日
    浏览(37)
  • MySQL查询条件OR导致模糊查询失效

    起因:查出来的列表数据,通过查询条件过滤,发现过滤条件并不起作用 检查发现:where查询条件里面有or的过滤条件,用来进行权限控制,模糊查询条件本来是起效果的,但是OR的条件一执行,就有多了些数据 代码: 期望的结果:先查出来所有符合要求的,然后再根据过滤

    2024年01月22日
    浏览(46)
  • mysql 查询优化 、索引失效

    查询优化 物理查询优化 通过索引和表连接方式等技术来进行优化,这里重点需要掌握索引的使用 逻辑查询优化 通过SQL 等价变换 提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高 索引失效 计算、函数、类型转换(自动或手动)导致索引失效 select sql_no_c

    2024年02月10日
    浏览(49)
  • MySQL 索引失效详解

    一、MySQL索引失效原因汇总 隐式的类型转换,索引失效 查询条件包含or,可能导致索引失效 like通配符可能导致索引失效 查询条件不满足联合索引的最左匹配原则 在索引列上使用mysql的内置函数 对索引进行列运算(如,+、-、*、/) 索引字段上使用 (! = 或者 ),索引可能失效 索

    2024年02月02日
    浏览(45)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包