sql索引失效的情况以及超详细解决方法

目录
  • 前言
  • 数据准备
  • 1.查询条件中有or,即使有部分条件带索引也会失效
  • 2.like查询是以%开头
  • 3.如果列类型是字符串,那在查询条件中需要将数据用引号引用起来,否则不走索引
  • 4.索引列上参与计算会导致索引失效
  • 5.违背最左匹配原则
  • 6.如果mysql估计全表扫描要比使用索引要快,会不适用索引
  • 7.other
  • 补充:索引注意事项
  • 总结

前言

大家都知道,一条查询语句走了索引和没走索引的查询效率是非常大的,在我们建好了表,建好了索引后,但是一些不好的sql会导致我们的索引失效,下面介绍一下索引失效的几种情况

数据准备

新建一张学生表,并添加id为主键索引,name为普通索引,(name,age)为组合索引

CREATE TABLE `student` (
  `id` int NOT NULL COMMENT 'id',
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_name_age` (`name`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

插入数据

INSERT INTO `student` VALUES (1, '张三', 18, '2021-12-23 17:12:44');
INSERT INTO `student` VALUES (2, '李四', 20, '2021-12-22 17:12:48');

1.查询条件中有or,即使有部分条件带索引也会失效

例:

explain SELECT * FROM `student` where id =1 

此时命中主键索引,当查询语句带有or后

explain SELECT * FROM `student` where id =1 or birthday = "2021-12-23"

发现此时type=ALL,全表扫描,未命中索引

总结:查询条件中带有or,除非所有的查询条件都建有索引,否则索引失效

2.like查询是以%开头

explain select * from student where name = "张三"

非模糊查询,此时命中name索引,当使用模糊查询后

explain select * from student where name like "%三"

发现此时type=ALL,全表扫描,未命中索引

3.如果列类型是字符串,那在查询条件中需要将数据用引号引用起来,否则不走索引

explain select * from student where name = "张三"

此时命中name索引,当数据未携带引号后

explain select * from student where name = 2222

此时未命中name索引,全表扫描

总结:字符串的索引字段在查询是数据需要用引号引用

4.索引列上参与计算会导致索引失效

explain select * from student where id-1 = 1

查询条件为id,但是并没有命中主键索引,因为在索引列上参与了计算

正例

select * from student where id = 2

总结:将参与计算的数值先算好,再查询

5.违背最左匹配原则

explain select * from student where age =18

age的索引是和建立再(name,age)组合索引的基础上,当查询条件中没有第一个组合索引的字段(name)会导致索引失效

正例

explain select * from student where age =18 and name ="张三"

此时才会命中name和(name,age)这个索引

6.如果mysql估计全表扫描要比使用索引要快,会不适用索引

7.other

1) 没有查询条件,或者查询条件没有建立索引

2) 在查询条件上没有使用引导列

3) 查询的数量是大表的大部分,应该是30%以上。

4) 索引本身失效

5) 查询条件使用函数在索引列上,或者 对索引列进行运算, 运算包括(+,-,*,/,! 等) 错误的例子:select * from test where id-1=9; 正确的例子:select * from test where id=10;

6) 对小表查询

7) 提示不使用索引

8) 统计数据不真实

9) CBO计算走索引花费过大的情况。其实也包含了上面的情况,这里指的是表占有的block要比索引小。

10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子:select * from test where tu_mdn=13333333333; 正确的例子:select * from test where tu_mdn='13333333333';

12) 1,<> 2,单独的>,<,(有时会用到,有时不会)

13,like "%_" 百分号在前.

4,表没分析.

15,单独引用复合索引里非第一位置的索引列.

16,字符型字段为数字时在where条件里不添加引号.

17,对索引列进行运算.需要建立函数索引.

18,not in ,not exist.

19,当变量采用的是times变量,而表的字段采用的是date变量时.或相反情况。

20,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走

21,联合索引 is not null 只要在建立的索引列(不分先后)都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null(但必须在所有列 都满足is null的时候),或者=一个值; 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况(包括is null =一个值),以上两种情况索引都会走。其他情况不会走。

补充:索引注意事项

1.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。应该用0、一个特殊的值或者一个空串代替空值。

2.复合索引

比如有一条语句是这样的:select * from users wherearea=’beijing’ and age=22;如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,age,salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减

3.使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4.排序的索引问题

mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引

5.like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like“%aaa%” 不会使用索引而like“aaa%”可以使用索引。

6.不要在列上进行运算

select* from users where YEAR(adddate)

7.不使用NOT IN操作

NOT IN操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替

总结

到此这篇关于sql索引失效的情况及解决的文章就介绍到这了,更多相关sql索引失效解决内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 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中有哪些情况下数据库索引会失效详析

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

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

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

  • 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 NU

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

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

  • 这种sql写法真的会导致索引失效吗

    前言 网上经常能看到一些文章总结在 mysql 中不能命中索引的各种情况,其中有一种说法就是指使用了 or 的语句都不能命中索引. 这种说法其实是不够正确的,正确的结论应该是,从 mysql5.0 后,如果在 or 连接的字段上都有独立的索引的话,是可以命中索引的,这里就是用到了 index_merge 特性. 在 mysql5.0 版本以前一条 sql 只能选择使用一个索引,而且如果 sql 中使用了 or 关键字,那么已有的索引就会失效,会走全表扫描.因为无论走哪个索引,mysql 都不能一

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

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

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

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

  • sql索引失效的情况以及超详细解决方法

    目录 前言 数据准备 1.查询条件中有or,即使有部分条件带索引也会失效 2.like查询是以%开头 3.如果列类型是字符串,那在查询条件中需要将数据用引号引用起来,否则不走索引 4.索引列上参与计算会导致索引失效 5.违背最左匹配原则 6.如果mysql估计全表扫描要比使用索引要快,会不适用索引 7.other 补充:索引注意事项 总结 前言 大家都知道,一条查询语句走了索引和没走索引的查询效率是非常大的,在我们建好了表,建好了索引后,但是一些不好的sql会导致我们的索引失效,下面介绍一下索引

  • SQL索引失效的11种情况详析

    目录 索引失效案例 [1]. 全值匹配 [2]. 最佳左前缀法则 [3]. 主键插入顺序 [4]. 计算.函数.类型转换(自动或手动)导致索引失效 [5]. 类型转换导致索引失效 [6]. 范围条件右边的列索引失效 [7]. 不等于(!= 或者<>)索引失效 [8]. is null可以使用索引,is not null无法使用索引 [9]. like以通配符%开头索引失效 [10]. OR 前后存在非索引的列,索引失效 [11]. 数据库和表的字符集统一使用utf8mb4 总结 数据库调优的大

  • MySQL细数发生索引失效的情况

    目录 索引的存储结构 不合理的模糊查询条件 对索引使用函数 对索引进行表达式计算 对索引使用隐式转换 联合索引非最左匹配 where子句中的or 总结 索引的存储结构 首先了解一下索引的存储结构,知道了索引的存储结构,才方便我们更好地理解索引失效的问题. 索引的存储结构跟MySQL的存储引擎有关,存储引擎的不同采用的结构也会不同. MySQL默认的存储引擎InnoDB采用B+Tree作为索引的数据结构,在创建表时,InnoDB会默认创建一个主键索引,这是一个聚簇索引,其他索引都属于二级索引. M

  • SQL server中提示对象名无效的解决方法

    产生SQL对象名无效的问题大多原因是由于数据迁移导致的,下面我们给出解决方法. 在使用数据库的过程中,经常会遇到数据库迁移或者数据迁移的问题,或者有突然的数据库损坏,这时需要从数据库的备份中直接恢复.但是,此时会出现问题,这里说明几种常见问题的解决方法. 一.孤立用户的问题 比如,以前的数据库的很多表是用户test建立的,但是当我们恢复数据库后,test用户此时就成了孤立用户,没有与之对应的登陆用户名,哪怕你建立了一个test登录用户名,而且是以前的用户密码,用该用户登录后同样没办法操作以前属于

  • 关于C#连接SQL Server时提示用户登录失败的解决方法

    在用C#开发windows端程序并连接SQL Server时有可能会遇到数据库登录失败的问题,报错现象如下图所示: 报错信息如下: System.Data.SqlClient.SqlException: '用户 '' 登录失败.' This exception was originally thrown at this call stack:     [External Code]     MyQQ.DataOperator.ExecSQL(string) in DataOperator.cs

  • mysql8.0忘记密码的详细解决方法

    目录 一.问题: 二:解决 1.关闭mysql服务 2.使用cd指令切换到mysql的bin目录 3.打开一个新的cmd窗口, 4.使用名为mysql的数据库,将对应user表的密码置空 5. 关闭以步骤2mysqld --console --skip-grant-tables --shared-memory打开的mysql服务(直接关掉窗口即可) 6. 重新开启mysql服务,实现无密码登陆了 7.修改密码 总结 一.问题: 太久没用mysql ,忘记了原先的root密码 二:解决 1.关闭m

  • SQL server服务显示远程过程调用失败的解决方法

    刚刚打开SQL Server 2008,想要新建一个数据库,却发现出现了一个问题,这个问题由于之前没有遇到过,所以下面整理解决SQL server服务远程调用失败的几个方法,供大家参考,具体内容如下 先看看出现的问题: 出现上面这个错误的原因可能是由于咱们在装VS2012或者其他版本的时候,这个VS会自动装"Microsoft SQL Server 2013(2012) ExpressLocalDB"服务,所以导致SQL server2008,中SQL server服务显示远程过程调用

  • SQL Server2019安装后出现连接失败的解决方法

    目录 解决SQL Server 连接失败的问题 一般解决方法: 总结 解决SQL Server 连接失败的问题 最近因学习数据库系统原理,下载安装了一个2019版本的,启动服务后,发现使用Aqua Data Studio根本连不上,打开设备管理器 一看1433端口没有监听: 于是在网站上搜索解决方案,基本上都是说在 SQLServer的管理配置界面设置一下,TCP/IP的端口即可.也的确如此:但是我配置完端口之后,依旧无法正常连接. 因为我遇到的情况相对特殊一些,我的计算机上安装了虚拟机,虚拟了

  • sql2005 本地计算机上的SQL SERVER服务启动后又停止了解决方法

    本方法主要是解决SQL Server运行过程中出现的"本地计算机上的MSSQLSERVER服务启动后又停止了.一些服务自动停止,如果它们没有什么可做的"解决方法. 笔者ASP运行环境:操作系统为Windows XP SP2,IIS的版本为默认的5.1,数据库为SQL Server 2005(MDAC版本为2.8) 一.出现"本地计算机上的MSSQLSERVER服务启动后又停止了.一些服务自动停止,如果它们没有什么可做的" 1.开始-->管理管理工具-->

  • SQL Server数据库复制失败的原因及解决方法

    在SQL Server数据库操作中,对数据库复制时出现了以下的错误,错误信息如下图所示: SQL Server数据库复制失败的原因及解决方案 出现上面的提示信息,会是什么原因导致的呢?笔者经过一番推敲终于找到了上述的原因. 原因:在复制数据库时,必须先停止mssqlserver服务,然后才能复制. 解决方法: 开始-->运行-->services.msc打开服务管理器,找到mssqlserver服务,然后停止该服务. 或者 开始-->运行-->cmd-->net stop m

随机推荐