精华内容
下载资源
问答
  • oracle 索引失效的原因
    2021-05-08 05:19:36

    引起oracle索引失效的原因很多: 1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等,对索引列进行运算.需要建立函数索引就可以解决了。 创建函数索引方式: 表dept,有col_1,col_2,现在对col_1做upper函数索引 这样就行了 CREATE INDEX index_name ON dept(upper(col_1)); 函数索引是基于代价的优化方式-CBO,(在Oracle8及以后的版本,Oracle强列推荐用CBO的方式,而非RBO),所以表必须经过analyze才可以使用,或者使用hints才可以 2、新建的表还没来得及生成统计信息,分析一下就好了 3、基于cost的成本分析,访问的表过小,使用全表扫描的消耗小于使用索引。 4、使用<>、not in 、not exist,对于这三种情况大多数情况下认为结果集很大,一般大于5%-15%就不走索引而走FTS。 5、单独的>、

    -------------------------------------------------------------------------------------------------------------------------------------------------------------

    索引失效

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

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

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

    4) 索引本身失效

    5) 查询条件使用函数在索引列上(见12)

    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';

    11)对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+,-,*,/,! 等)

    错误的例子:select * from test where id-1=9;

    正确的例子:select * from test where id=10;

    12)使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

    错误的例子:select * from test where round(id)=10;

    说明,此时id的索引已经不起作用了 正确的例子:首先建立函数索引,

    create index test_id_fbi_idx on test(round(id));

    然后 select * from test where round(id)=10; 这时函数索引起作用了 1,<> 2,单独的>,

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

    4,表没分析.

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

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

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

    8,not in ,not exist.

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

    10, 索引失效。

    11,基于cost成本分析(oracle因为走全表成本会更小):查询小表,或者返回值大概在10%以上

    12,有时都考虑到了 但就是不走索引,drop了从建试试在

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

    14,联合索引 is not null 只要在建立的索引列(不分先后)都会走,

    in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,

    其他建立索引的列可以是is null(但必须在所有列 都满足is null的时候),

    或者=一个值;当建立索引的第一位置是=一个值时,其他索引列可以是任何情况(包括is null =一个值),

    以上两种情况索引都会走。其他情况不会走。

    更多相关内容
  • 索引无效原因 最近遇到一个Oracle SQL语句的性能问题,修改功能之前的运行时间平均为0.3s,可是添加新功能后,时间达到了4~5s。虽然几张表的数据量都比较大(都在百万级以上),但是也都有正确创建索引,不知道到底...
  • 主要给大家介绍了关于网上流传的某种sql写法会导致索引失效的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用sql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • 索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)    注意:要想使用or,又想让索引生效,只能将or条件中的每个...
  •  二、索引失效的几种情况  1.如果条件中有多个字段使用or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。多条件查询用同一字段时使用or索引会生效。 注意:要想使用or,又想让索引生效,只能...
  • oracle数据库索引失效

    2021-01-19 22:45:53
    今天一个同事突然问我索引为什么失效。说实在的,失效的原因有多种: 但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 1. 随着表的增长,where条件出来的数据太多,大于15%...
  • 这次的话简单说下如何防止你的索引失效。 再说之前我先根据我最近的经验说下我对索引的看法,我觉得并不是所以的表都需要去建立索引,对于一些业务数据,可能量比较大了,查询数据已经有了一点压力,那么最简单、...
  • 更准确的说,单列索引不存储null值,复合索引不存储全为null的值。索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描。 为什么索引列不能存Null值? 将索引列...
  • 没有特殊说明,测试环境均为MySQL8.0,早期版本可能会有更多情况导致索引失效。8.0失效的情况,早期版本也失效;8.0不失效的情况,早期版本可能失效。 所有测试默认不考虑表为空的情况,特殊情况文中会有说明。 本文...
  • 今天小编就为大家分享一篇关于mysql的in会不会让索引失效?,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
  • 这个varchar字段有一个复合索引。其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据。 使用的是mysql 5.6版本,innoDB引擎 实际情况如下 下面我们来看一下执行的结果 在上面的描述中我们还得...
  • 索引失效的几种情况

    千次阅读 2021-06-17 15:55:04
    索引失效 本文参考尚硅谷视频 口诀:带头大哥不能死,中间兄都不能断,索引列上无操作,范围右边全失效,like百分加右边,字符串中有引号 准备: 建立员工记录表staffs(id,name,age,pos,add_time) 给表中name,age,...

    索引失效

    本文参考尚硅谷视频

    口诀:带头大哥不能死,中间兄都不能断,索引列上无操作,范围右边全失效,like百分加右边,字符串中有引号

    准备:

    建立员工记录表staffs(id,name,age,pos,add_time)

    给表中name,age,pos字段添加索引(注意三个字段的顺序)

    alter table staff
    add index idx_staffs_nameAgePos(name,age,pos)
    

    ①最佳左前缀法则

    指的是查询从索引的最左前列开始并且不跳过索引中的列

    (带头大哥不能死,中间兄弟不能断)

    (火车头带着车身跑,不能没有火车头,中间也不能缺少一段火车身)

    例:

    第一种情况,name字段索引一直被使用

    mysql>EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’

    在这里插入图片描述

    上图可知,idx_staffs_nameAgePos索引有三个字段,name,age,pos

    其中,只查找name可以使用索引

    只查找name和age也可以使用索引

    查找name,age,pos三个字段也可以使用索引

    第二种情况,当不查找name而去查找后面age和pos字段时

    在这里插入图片描述

    索引失效了

    第三种情况,查找了name字段和pos字段,而缺少了中间的age字段

    在这里插入图片描述

    上图对比了分别查找name字段和name,pos字段的两种情况,发现虽然都用到了idx_staffs_nameAgePos索引,但是key_len长度没变,ref索引用到的字段也没变,说明针对字段pos的索引失效了,原因是缺少了中间的age索引字段

    ②不要再索引列上做任何操作

    不要在索引列上左任何操作(计算,函数,自动or手动类型转换),会导致索引失效转换为全表扫描

    例:

    在这里插入图片描述

    上图可以看到,没用left函数之前,是一个正常的索引引用,使用函数之后,索引失效了

    ③存储引擎不能使用索引中范围条件右边的列

    与上面单表索引优化分析中的例子一样

    在这里插入图片描述

    第一个查询全等值匹配没问题

    第二个查询中间使用了范围条件,结果导致manger的索引失效

    按照BTree索引的工作原理,先排序name,如果遇到相同的name则再排序age,如果遇到相同的age则再排序pos,当age字段在复合索引中间时,因age>25条件是一个范围值(所谓range),MySQL无法利用索引再对后面的pos部分进行建索,即range类型查询字段后面的索引无效

    ④尽量使用覆盖索引

    只访问索引列的查询,索引列和查询列一致,避免select *

    在这里插入图片描述

    当只访问了索引列后,Extra中出现了Using index,表明是覆盖查询,这是好的

    ⑤Mysql在使用(<>或!=或is null或is not null)时无法使用索引会导致全表扫描(现在改了)

    注:新版本的MySQL中这些已经可以使用索引了

    在这里插入图片描述

    ⑥like以通配符(%)开头的索引会失效变成全表扫描

    以%开头会失效,变成全表扫描

    通配符出现最前面说明所有都适配,所有都要扫描

    在这里插入图片描述

    不以%开头则不失效

    在这里插入图片描述

    如何解决?

    可以使用覆盖索引

    tb1_user表中有id,name,age,email四个字段,对name,age字段设置索引,id自动为主键索引

    那么查询id,name,age字段都是覆盖索引,都可以使索引生效

    在这里插入图片描述

    但是如果使用explain select * from tb1_user where name like '%aa%'就会索引失效,因为此时不是覆盖索引,不是覆盖索引使用%会失效

    ⑦字符串不加单引号会导致索引失效

    name字段为varchar类型

    假设有一行name=‘2000’,这时如果用name=2000条件查找也会有结果

    在这里插入图片描述

    用EXPLAIN分析

    在这里插入图片描述

    可以看出,用name=2000作为判断条件,不加单引号导致索引失效了,为什么?

    因为name=2000也可以查出来是因为MySQL在底层做了一次类型转换,把整形2000转换成了字符串’2000’

    违背了上面第②条的不要在索引列上左任何操作的原则,所以索引失效了

    练习

    在这里插入图片描述

    2.条件顺序改变

    假设表student中id,name,age三个字段建立了一个复合索引

    那么条件列的顺序换换索引是否还能有效?

    explain select * from student where id = 1 and name = "张三" and age = 22explain select * from student where name = "张三"  and id = 1 and age = 22
    

    答案:yes,M有SQL的优化器会自动给列排序为创建索引时的列,但是还是建议顺序与索引列顺序一致,提高效率

    3.order by 只排序,不查询

    建表test03(c1,c2,c3,c4)

    对c1,c2,c3,c4加复合索引

    在这里插入图片描述

    先是MySQL优化器对where后面的条件进行排序,c1,c2,c3,c4

    由于索引功能有两个,分别是排序与查询,对c3进行排序并没有查询,但也是用到了索引,所以实际上c1,c2,c3都用到了索引,而c4由于c3没有等值判断所以用不到索引,索引对c4失效了。

    4.order by 中的字段顺序不能调换

    在这里插入图片描述

    由order by 的特性可知,order先对c3排序后再对c2排序,所以优化器不会把c3,c2顺序互换,这就导致了最终的是顺序是c1,c3,c2,c5

    只有c1用到了索引,c3与c1中间缺少了c2,所以c3用不到索引,c5本身就没有索引

    所以对c3和c2的排序没有用到内部索引,使用了外部索引Using filesort

    在这里插入图片描述

    这里虽然order by 中是先c3再c2,但是前面条件查询时已经用到了c2,所以实际用到索引的顺序是c1,c2,c3

    所以c1,c2都使用到了索引,ref为两个const,Extra也没有出现Using filesort

    5.group by先排序后分组,用法与order by一样

    6.like的%位置关系

    在这里插入图片描述

    第一条sql语句与第四条效果一样,range范围类型,用到了三个索引列,ref为null

    为什么这里ref为null?明明三个索引都被使用了,前文提到过,range范围后的索引都会失效,但是like是特例,由于%不在第一个位置上,第一个位置上的是k,k是一个确定的值,所以可以算作查询被使用索引,所以能连通c3,c3也能使用索引

    第二条sql语句与第三条效果一样,ref一对多结果,用到了一个索引列,ref为const

    为什么这里ref为const

    因为like后面%在第一个位置上,导致like后面的索引全失效,所以只有c1的索引有效

    小表驱动大表

    5 X 1000 == 1000 X 5 ,但是在数据库中第一种比第二种好,连接5次数据库,每次查1000条,连接1000次数据库,每次查5条,肯定第一种好

    举例:查找华为公司各部门的员工信息(员工表A,部门表B)

    第一种:用in函数(A表数量大于B表,用in,先查in括号里面的少的表)

    select * from A where A.id in (select B.id from B)

    第一步:select B.id from B

    第二步:select * from A where A.id = B.id

    部门表的数量肯定比员工表少,所以用部门表驱动员工表,小表驱动大表

    如果先查员工表,那么需要对员工表进行全表搜索,相比较部门表,肯定效率低

    第二种:用exist函数(B表数量小于A表,先查B表,再判断B表中是否有满足条件的数据)

    select * from B where exists(select 1 from A where A.id = B.id)

    第一步:select * from B

    第二步:select * from B where A.id = B.id

    exist()子查询只返回true或false,因此select 1也可以是任意select * ,官方说法是忽略select清单

    select ... from table where exist(subquery)
    

    该语法可以理解为:将主查询的数据放到子查询中做条件验证,根据验证结果(true或false)来决定主查询的数据结果是否得以保留

    排序优化

    1.order by后面的顺序必须和索引顺序一致,否则会造成使用order by的内部排序(file sort)

    #建表
    create table tblA(
    	#id int primary key not null auto_increment,
    	age int,
    	birth TIMESTAMP not null
    )
    #创建age,birth的索引
    create index idx_tblA_age_birth on tblA(age,birth)
    
    #1.只对age排序,没问题,用上索引
    explain select * from tblA where age > 20 order by age
    #2.先对age排序,再对birth排序,没问题
    explain select * from tblA where age > 20 order by age,birth
    #3.顺序一变,出现了filesort,危险的文件排序,索引失效
    explain select * from tblA where age > 20 order by birth,age
    
    #4.带头大哥age不在了,所以索引失效,用到了索引,是索引失效
    explain select * from tblA where birth > '2021-10-10 00:00:00' order by birth
    #5.带头大哥存在,索引生效
    explain select * from tblA where birth > '2021-10-10 00:00:00' order by age
    #6.age升序,birth降序,打乱了索引的结构,索引失效
    explain select * from tblA order by age asc , birth desc
    

    order by支持两种方式的文件排序,FileSort和index,index效率高,filesort效率低

    能成功使用index的情况:1.满足最左前缀法则2.排序统一

    2.filesort有两种排序算法,可能是单路排序的缓存空间太小导致效率降低

    双路排序(mysql4.1之前):两次扫描磁盘,读取待排序的那一列,I/O非常耗时,所以效率低

    单路排序:从磁盘中读取查询需要的所有列,按照order by在buffer对它们进行排序,然后扫描排序后的列表进行输出。它的效率更快,因为第一次读取数据后放到了缓冲中,把随机I/O变成了顺序I/O,但是会占用更多的内存空间

    单路排序可能出现的问题:

    单路算法总体好于双路算法,但是如果一次读取不完所有数据,会造成多次读取,反而效率还低于多路算法,如果sort_buffer_size比较小,单路排序每次只能取出sort_buffer_size大小的数据,取不完就要多次取。

    多路算法也可能超出缓冲区,但是单路算法的风险更大

    3.order by 排序不要用select *

    因为select * 查询所有的,占用的排序缓冲区太大,在单路算法下,很可能超出缓冲区大小,就会多次I/O读取,降低效率

    show profiles诊断sql

    show profiles:查看之前执行的所有sql及运行时间

    1.先执行一条sql

    select * from emp where id = 4
    

    2.进行show profiles,可以看到刚才执行的那条sql的id为52

    show profiles
    

    请添加图片描述

    3.查看刚才那条语句在内存中详细的运行状态

    show profile cpu,block io for query 52;
    

    请添加图片描述

    常用type

    all: 显示所有的开销信息

    block io: 显示块IO相关开销

    context switches: 上下文切换相关开销

    cpu: cpu相关开销

    ipc: 显示发送和接收相关开销

    memory: 显示内存相关开销信息

    page faults: 显示错误页面相关开销信息

    source: 显示和source_function,source_file,source_line相关的开销信息

    swaps: 显示交换次数相关开销的信息

    如果查询详细的运行状态中有一下四个中的任何一个,必须优化

    1.converting HEAP to MyISAM :查询结果太大,内存不够用了,往磁盘上搬

    2.Create tmp table :创建临时表。(数据拷贝到临时表,用完再删除)

    3.Copying to tmp table on disk:把内存临时表复制到磁盘,危险

    4.locked:锁住了

    展开全文
  • 当某张表建立时间比较长以及该表频繁的进行插入,更新操作时,将出现索引失效问题。
  • oracle索引失效的总结

    2017-08-17 10:23:01
    oracle索引失效的总结
  • where条件索引失效情况

    千次阅读 2021-02-05 06:36:02
    不过有些数据库工程师往往会犯一些低级的错误,导致索引失效。如在Where条件子句中设置了不合适的条件,从而在查询等操作时导致原先在表中设置的索引不起作用。笔者以前也多次犯过类似的错误。笔者今天在这里就...

    虽然说索引在使用上可能有种种限制,但是还是在数据库设计中被充分利用。因为在大部分情况下索引还是被用来提高数据库性能的一个工具。不过有些数据库工程师往往会犯一些低级的错误,导致索引失效。如在Where条件子句中设置了不合适的条件,从而在查询等操作时导致原先在表中设置的索引不起作用。笔者以前也多次犯过类似的错误。笔者今天在这里就抛砖引玉,把这些常见的问题总结一下。希望后来的人能够尽量少犯这些错误。

    错误一:在Where子句中使用函数。

    如现在在销售订单表中,有一个订单日期字段,其存储的数据为年月日。假设现在用户需要统计数据,需要统计2009年第一季度每隔月的各个业务员的接单情况。由于在销售订单中没有存储年与月份的数据,而只有订单日期数据,那么就需要利用Extract函数从订单日期字段中获取年份与月份字段,然后再查询处各个业务员在2009年第一季度每个月的销售订单明细。下面的Select语句就是查询2009年1月份各个业务员的接单情况。

    Select 业务员,订单日期,销售订单号码,客户名称,订单金额

    Where Extract(yyyy,订单日期)=2009 and Extract(mouth,订单日期)=1

    但是此时就需要在Where条件语句中采用Extract函数。这是Oracle数据库系统提供的从日期型字段中抽取年或者月份的函数。如果原先在这个日期字段上建立了索引(不是函数索引),那么此时会对数据库的查询产生什么影响呢?

    通常情况下,如果不使用基于函数的索引,那么当SQL语句在的Where子句中队存在索引的列使用函数时,这会让数据库的优化器忽略掉这些索引。也就是说,这种情况下即使只存在着少量的复合条件的信息,数据库仍然会对这张表进行全表扫描,以获取相关的数据。这主要是因为这些索引实际上已经改变了被索引列的值。如一些常见的函数,如SUBSTR、Extract等函数,都会改变索引列的值。此时数据库系统也就无法使用已被函数引用(此时列的值已经发生改变)的索引和列。也即是说,如果在Where子句的条件语句中,采用了函数的话,则即使列采用了索引(不是函数索引),就会让设置在这个列上索引失效。此时数据库就会对这个表进行全表扫描。这个结果可能是一些数据库管理员始料未及的。

    那么该如何避免这种情况呢?最简单的方法,就是数据库管理员在数据库设计的时候就预计到在以后操作中,可能要在Where子句中要使用函数,此时就可以把这个列上的索引设置为函数索引。通常情况下,只要建立了函数索引,则即使在Where语句中采用了函数,这个列上的索引仍然有效。在查询中就可以避免全表扫描。因为函数索引实际上存储了预先计算过的值。也就是说,在索引表中,其实已经存储了年度与月份的值。而不是存储具体的订单日期。那么此时在查询时,数据库就会直接对应索引表中的年度与月份的值。为此索引就不会因为采用了函数而失效。

    错误二:不匹配的数据类型。

    在数据库中,有些数据类型虽然不同,但是数据库会自动进行转换。如现在在一张用户信息表中,可能有公民的×××号码字段,这个字段的类型为字符型。通常情况下,为这个字符类型的字段赋值时需要加入单引号。但是如果把一个纯数字的字符串赋值给一个字符型的字段时,可以不用加单引号。因为此时数据库系统会自动把这串数字转换为字符型数据。现在数据库在这表中已经给这个×××号码字段设置了索引。如果现在用户在对这个表进行查询时,所采用的Where条件语句为 Where ×××号码=123456789900。此时数据库会如何查询呢?

    笔者要非常悲痛的告诉大家,此时数据库会忽略掉设置在×××号码字段上的索引,而采用全表扫描。类似的比较不匹配的数据类型,会导致设置在表中字段上的索引失效,这是很多数据库管理员经常容易犯的错误。Oracle数据库系统在数据类型字段上的兼容性,虽然提高了用户操作数据的便利性,但是毋庸置疑的也给用户留下不少的麻烦。就拿上面这个例子来说,数据库优化器会对以上这个条件语句进行一些转换,如可能会换成:

    To_number(×××号码) =123456789900

    也就是说,会在×××号码字段前面隐性的加入一个函数,把×××号码转换为数字型。然后再与后面提供的×××号码进行比对。此时就相当于对索引列采用了函数,跟上面提到的第一个错误类似。当Where条件语句中采用了函数,则即使这个列中设置了索引(不是函数索引),则数据库优化器也会忽略掉这个索引。此时即使一个×××号码在数据库中只有一条记录,数据库仍然需要进行全表扫描。

    由于类似的错误很隐蔽,故一些经验不深的数据库管理员与程序开发人员经常会犯这个错误。那么该如何避免这种情况呢?其实只要了解有这种风险的存在,那么在处理起来也是比较简单的。如只需要在查询的时候把Where语句写成Where ×××号码=’123456789900’即可,即加入单引号,表示输入的条件是一个字符数据类型即可。此时两者的数据类型一致,数据库就不会利用数据类型转换函数了。不过有时候终端用户并不会这么配合,每次输入×××号码查询的时候,还利用单引号。此时程序开发人员应该把这个单引号在程序设计中实现。即终端用户只需要输入18位的×××号码即可,不需要输入单引号。而应用程序在把这个×××号码传递给数据库系统的时候,应用程序会先给其加上单引号,然后再传递给数据库系统进行查询。为此这个单引号对用户来说就是透明的。

    另外虽然可以修改数据库中的×××字段的数据类型,把其设置为数字型即可。但是通常情况下不建议这么做。因为有些老的×××号码中含有字符,针对这些×××号码就不好存储。而且有时候在×××查询中也只需要进行模糊查询,如只知道出身地与出生年月日,来查询×××号码。如果是数据类型的字段的话,则在实现模糊查询的时候会遇到问题。所以遇到这种情况,最好的处理方式就是应用程序在传递传输的时候,强制加入单引号。从而防止因为比较不匹配的数据类型而导致的全表扫描。

    错误三:在Where子句中使用IS NULL或者IS NOT NULL。

    在数据库设计的时候,允许某些字段为非空。而即使某个字段允许为非空,数据库仍然允许在这个字段上建立索引。但是这种情况下,使用索引就是一个很危险的事情。因为一不小心,就可能使得这个索引失效,在查询时需要用到全表扫描。如在以上这个表中,用户需要查询×××号码为空的纪录,以方便用户补全×××号码。此时用户就需要用到以下这个条件语句:WHERE ×××号码 IS NULL。通过这个语句可以查询出所有×××号码为空的纪录。但是,在Where子句中如果使用IS NULL或者IS NOT NULL等条件语句的话,。为此如果在几百万的信息中,如果只有两条记录没有×××号码,则此事数据库仍然需要进行全表扫描,以查找相关的信息。这主要是因为普通情况下,如果一个字段为空,而且又在这个字段上设置了索引的话,则这个索引的值不会保存在索引表中。因为根本无法保存。为什么呢?因为空值(NULL)在数据库中是一个很特殊的值。其NULL不等于‘’,甚至不等于NULL。

    所以在允许NULL字段上建立索引要特别注意这个情况。为了避免这种情况笔者有几个建议。如允许×××这个字段为NULL,那么最好在这个字段上建立位图索引。因为创建位图索引时,数据库系统会对整个表进行索引,并为索引列的每个取值建立一个位图,包括NULL字段。所以说位图索引通常对于NULL字段的搜索有独到之处。但是位图索引通常情况下是用在基数比较小的情况,即重复数值比较多时。而对于×××号码的话,基本上都是唯一的,也就是说基数很大,此时并不适合采用位图索引。既然不能够采用位图索引,那么就最好能够给这个字段设置默认值。如可以把这个字段默认设置为0。当没有输入×××号而保存这个资料的时候,则数据库中以字符0表示。如此在以后想查询×××号码为空的纪录时,只需要输入0,而不需要用IS NULL,这就可以避免全表扫描了。当然如果对×××字段能够实现非空限制那时最好的了。

    复合索引如 字段A  B  C,建立的组合索引 create index (A,B,C)

    select where A=' '此时索引有效

    但如果 使用 selet where B='' 索引无效

    规则当只是组合索引的个别字段时 只有使用定义索引的首个字段时,索引才有效。

    如果使用如下情形也不行

    如果可以使用索引RBO会尽可能的去用索引而不是全表扫描,但是在下列一些情况RBO只能使用全表扫描:

    如果column1和column2是同一个表的字段,含有条件column1  column2或column1 <= column2或column1 >= column2,RBO会用全表扫描。

    展开全文
  • mysql-实用-索引失效和优化原则
  • 一.准备工作 创建student表,id是主键 创建复合索引 ... create index idx_name_age on student...索引失效原因 1.全值匹配我最爱 1 explain select * from student where name = '张三' and age

    一.准备工作

    创建student表,id是主键

    这里写图片描述

    创建复合索引

    1

    create index idx_name_age on student(name,age);

    查看索引

    1

    show index from student;

    这里写图片描述

    二.索引失效原因

    1.全值匹配我最爱

    1

    explain select * from student where name = '张三' and age = 1;

    这里写图片描述

    2.最佳左前缀法则,带头大哥不能死,中间兄弟不能少。

    1

    explain select * from student where age = 1;

    这里写图片描述

    3.不要在索引列上做任何操作

    1

    explain select * from student where left(name,1) = '张' and age = 1;

    这里写图片描述

    4.范围条件后列上索引失效

    1

    explain select * from student where age > 1 and name = '王五';

    这里写图片描述

    查找级别是范围,name上的索引失效。

    5.尽量使用覆盖索引减少使用select *

    1

    explain select * from student where name = 'zhangsan';

    这里写图片描述

     

    1

    explain select name from student where name = 'zhangsan';

    这里写图片描述

    6.使用不等于(!= 或者<>)不能使用索引

    1

    explain select * from student where name != '张三';

    这里写图片描述

    7.使用 is null 或者 is not null 也不能使用索引

    1

    explain select * from student where name is not null;

    这里写图片描述

    8.like 已通配符开头(%abc)导致索引失效 (解决方法:使用覆盖索引)

    1

    explain select * from student where name like '%张%';

    这里写图片描述

    想用的话解决方法,使用覆盖索引

     

    explain select name from student where name like '%张%';

    这里写图片描述

    9.少用or,用它来连接索引会失效

    1

    explain select * from student where name = '张三' or age = 2;

    这里写图片描述

    展开全文
  • 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效。还有一些MySQL“军规”或者规范写明了某些sql不能这么写,否则索引失效。 绝大部分的内容笔者是认可的,不过部分举例中笔者认为...
  • 文章目录前言1.单个字段测试varchar字段测试name字段name and status 测试name and status and address 测试2.最左前缀法则复合索引测试:打乱顺序测试:跳字段测试(测试以name...覆盖索引6.or分割or分割失效问题总结or
  • 总结一下为什么范围后索引失效 存储引擎不能使用索引中范围条件右边的列 比如说有三个字段 a b c,建立复合索引a_b_c 此时叶子节点的数据排序后可能为 (a=1 b=1 c=1) (a=1 b=2 c=1) (a=1 b=2 c=3) (a=2 b=2 c=3) ...
  • 虽然你这列上建了索引,查询条件也是索引列,但最终执行计划没有走它的索引。下面是引起这种问题的几个关键点。列与列对比某个表中,有两列(id和c_id)都建了单独索引,下面这种查询条件不会走索引select*...
  • Oracle索引失效的原因

    2021-05-07 02:29:07
    容易引起oracle索引失效的原因很多:1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等,对索引列进行运算.需要建立函数索引就可以解决了。2、新建的表还没来得及生成统计信息,分析一下就好了3、基于cost的成本分析...
  • 原标题:Oracle SQL查询时索引失效原因 大家都知道,SQL查询性能和索引关系密切;数据库表中创建了索引,在数据的增删改中,会消耗更多的资源。因此,创建了索引是为了查询速度更优,但有时候查询时索引并没有起到...
  • 索引失效的原因:1、没有查询条件;2、在查询条件上没有使用引导列;3、索引本身失效;4、提示不使用索引;5、统计数据不真实。本文环境:windows7系统、dell g3电脑,该方法适用于所有品牌的电脑。索引失效的原因:...
  • 什么是索引失效: 使用索引查询某行数据,但数据库扫描全表进行查询时 叫索引失效索引失效的几种方式: 1、where中存在 or 2、类型为char,查询条件时用int 3、模糊查询时,%开头的查询 4、not in 5、...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 259,596
精华内容 103,838
关键字:

索引无效