精华内容
下载资源
问答
  • 索引失效

    2020-08-29 12:13:22
    where条件中包含or时,可能会导致索引失效 尽量避免使用or语句,可以根据情况使用union或in等来代替,这两个语句的执行效率也比or高 不同版本MySQL,关于or走索引情况不一致,需要具体分析 MySQL 5.7版本中,...
    • where 条件中包含 or 时,可能会导致索引失效

      • 尽量避免使用 or 语句,可以根据情况使用 union 或 in 等来代替,这两个语句的执行效率也比 or 高
      • 不同版本 MySQL,关于 or 走索引情况不一致,需要具体分析
      • MySQL 5.7 版本中,当 or 两边都是单列索引,索引生效
    • where 语句中索引列使用了负向查询,可能会导致索引失效

      • 负向查询包括:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE 等
      • 其中负向查询并不绝对会索引失效,这要看优化器判断,全表扫描或者走索引哪个成本低了
    • 索引字段可以为 null,使用 is null 或 is not null,可能会导致索引失效

      • null 的列使索引/索引统计/值比较更加复杂,对 MySQL 来说更难优化
      • null 在 MySQL 内部需要特殊处理,增加数据库处理记录的复杂性,同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多
      • null 值需要更多的存储空间,无论是表中还是索引中每行中的 null 的列都需要额外的空间来标识
      • 对 null 的处理,只能采用 is null 或 is not null,而不能使用 =、in、<、<>、!=、not in 这些符号操作
    • 在索引列上使用内置函数,一定会导致索引失效

      • 建议尽量在应用程序中进行计算和转换
    • 隐式类型转换导致的索引失效

      • 索引列 user_id 为 varchar 类型,不会命中索引,因为 MySQL 做了隐式类型转换,调用函数将 user_id 做了转换
      • select * from `user` where user_id= 12;
      • select from `user` where CAST(user_id AS signed int) = 12;
    • 隐式字符编码转换导致的索引失效

      • 当两个表之间做关联查询时,如果两个表中关联的字段字符编码不一致的话,MySQL 可能会调用 CONVERT 函数,将不同的字符编码进行隐式转换从而达到统一;作用到关联的字段时,就会导致索引失效
      • Demo : 

        • select l.operator from `trade_log` l, `trade_detail` d where d.tradeid = l.tradeid and d.id = 4;
        • 其中 d.tradeid 字符编码为 utf8,而 l.tradeid 的字符编码是 utf8mb4
        • 因为 utf8mb4 是 utf8 的超集,所以 MySQL 在做转换时会用 CONVERT 将 utf8 转成 utf8mb4;简单来看,就是 CONVERT 作用到了 d.tradeid 上,因此索引失效
      • Suggest :

        • 将关联的字段的字符编码统一
        • 若无法统一字符编码时,手动将 CONVERT 函数作用于关联式 = 的右侧,起到字符编码统一的目的;这里是强制将 utf8mb4 转为 utf8,注意:从超集向子集转换是有数据截断的风险的

          • select l.operator from `trade_log` l, `trade_detail` d where d.tradeid = CONVERT(l.tradeid USING utf8) and d.id = 4;
    • 对索引列进行运算,一定会导致索引失效

      • 运算符如:+、-、*、/ 等
      • Demo : 
        • select from `user` where age - 1 = 10;
      • Suggest : 优化,将运算放在值上,或者在应用程序中算好

        • select from `user` where age = 10 - 1;
    • like 通配符可能会导致索引失效

      • 以 % 开头,索引失效
      • Suggest :

        • 去掉开头的 %

          • select from `user` where `name` like '李%';
        • 利用覆盖索引来名中索引

          • select name from `user` where `name` like '%李%';

        

    • 联合索引中,违背最左匹配原则,一定会导致索引失效
    • MySQL 优化器的最终选择,不走索引

      • 即使完全符合索引生效的场景,考虑到实际数据量等原因,最终是否使用索引还要看 MySQL 优化器的判断;也可以在 SQL 中强制表明走哪个索引

    优化索引的建议:

    • 禁止在更新十分频繁,区分度不高的属性上建立索引

      • 更新会变更 B+树,更新频繁的字段建立索引会大大降低数据库性能
      • "性别"这种区分度不大的属性,建立索引是没有意义的,不能有效过滤数据,性能和全表扫描类似
    • 建立联合索引,必须把区分度高的字段放在前面
    展开全文
  • 自己整理有关MySQL的索引失效相关的信息,重点在组合索引的失效,有兴趣可以看看,看看是否对你有些许帮助
  • mysql 联合索引生效的条件、索引失效的条件

    万次阅读 多人点赞 2019-02-23 10:11:30
    1.联合索引失效的条件 联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key...

    1.联合索引失效的条件

    联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。

     

    对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。

     

        利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不姓,电话簿将没有用处。

        所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。

        如:建立 姓名、年龄、性别的复合索引。

        create table myTest(

             a int,

             b int,

             c int,

             KEY a(a,b,c)

        );

    (1)    select * from myTest  where a=3 and b=5 and c=4;   ----  abc顺序
    abc三个索引都在where条件里面用到了,而且都发挥了作用


    (2)    select * from myTest  where  c=4 and b=6 and a=3;
    where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样


    (3)    select * from myTest  where a=3 and c=7;
    a用到索引,b没有用,所以c是没有用到索引效果的


    (4)    select * from myTest  where a=3 and b>7 and c=3;     ---- b范围值,断点,阻塞了c的索引
    a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引


    (5)    select * from myTest  where b=3 and c=4;   --- 联合索引必须按照顺序使用,并且需要全部使用
    因为a索引没有使用,所以这里 bc都没有用上索引效果


    (6)    select * from myTest  where a>4 and b=7 and c=9;
    a用到了  b没有使用,c没有使用


    (7)    select * from myTest  where a=3 order by b;
    a用到了索引,b在结果排序中也用到了索引的效果,a下面任意一段的b是排好序的


    (8)    select * from myTest  where a=3 order by c;
    a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort


    (9)    select * from mytable where b=3 order by a;
    b没有用到索引,排序中a也没有发挥索引效果

    2.索引失效的条件

    • 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
    • 存储引擎不能使用索引范围条件右边的列
    • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
    • mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

    • is null,is not null也无法使用索引   ----  此处存在疑问,经测试确实可以使用,ref和const等级,并不是all

    • like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。问题:解决like‘%字符串%’时索引不被使用的方法?

    • 字符串不加单引号索引失效   

    SELECT * from staffs where name='2000';  -- 因为mysql会在底层对其进行隐式的类型转换

    SELECT * from staffs where name=2000;  --- 未使用索引

    一般性建议

    1. 对于单键索引,尽量选择针对当前query过滤性更好的索引
    2. 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
    3. 在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引
    4. 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

     

    展开全文
  • 这篇文章主要介绍了oracle数据库索引失效的原因及如何避免索引失效,有需要的小伙伴参考下。今天一个同事突然问我索引为什么失效。说实在的,失效的原因有多种:但是如果是同样的sql如果在之前能够使用到索引,那么...

    这篇文章主要介绍了oracle数据库索引失效的原因及如何避免索引失效,有需要的小伙伴参考下。

    今天一个同事突然问我索引为什么失效。说实在的,失效的原因有多种:

    但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况:

    1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表)

    2. 统计信息失效      需要重新搜集统计信息

    3. 索引本身失效      需要重建索引

    下面是一些不会使用到索引的原因

    索引失效

    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 =一个值),

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

    展开全文
  • 你可以简单理解为"排好序的快速查找的数据结构",因此,当我们建立了索引之后,我们应该如何避免索引失效呢? 二:如何避免索引失效 1:最佳左前缀法则:如果索引了多列,要遵守最左前缀法则,指的是查询从索引的...

    一:索引

    MySql官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构,可以得到索引的本质:索引是数据结构。

    你可以简单理解为"排好序的快速查找的数据结构",因此,当我们建立了索引之后,我们应该如何避免索引失效呢?

    二:如何避免索引失效

    1:最佳左前缀法则:如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引的列

    表的结构大致是这个样子

    这是建立的索引(索引字段顺序,name,age,pos):

    A:当想要通过年龄和位置去查询时:

    发现type是ALL,查询效果最差的,并且没有用到索引

    B:当想要通过位置去查询时:

    发现type是ALL,查询效果最差的,并且没有用到索引,和上述的结果一样

    C:当想要通过姓名去查询时

    发现了type是ref,并且用到了索引,key_len是74,这是为什么呢?

    正如我们上面所介绍的,索引建立以后,应该从最左开始,建立的字段不能丢失。比如火车不能没有火车头

    D:再举一个例子

    我们查询了索引字段中的第一个和第三个,发现key_len是74,按理来说查询的精度越高应该key_len就越长,并没有将索引全部用到。因此不能跳过索引中的列。

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

    从上面可以看出下面用到了函数查询,从左开始取四位,两次的查询结果一致,但是用了Explain之后发现一个type是ref,一个是ALL,上面的用到了索引,下面用到了Mysql函数后取截取name,导致索引失效。也印证了上面的话。

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

    如果索引都用到了的话,key_len应该是140,而此时是78,表示只用到了两个字段,范围查找导致后面的索引失效

    4:like以通配符开头('%abc/...')mysql索引失效会变成全表扫描的操作

    5:尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*

    6:mysql在使用不等式(!=或者<>)的时候无法使用索引而导致全表扫描

    可以看出用到了不等式,从表中可以看出key是null,并没有用到索引

    7:is,null,is not null也无法使用索引

    理论上用到索引,实际上并没有用到

    8:like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

    但在右边写%的话

    如果想要使用%%的话如何建立索引呢?

    我们建立覆盖索引在name,age字段上,id是主键

    当我们查询的字段在我们的索引的字段里的话,就会用到索引

    当查找全部的时候,查找的字段超过了索引的范围就会让索引失效,所以如何建立索引要从实际情况考虑。

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

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

    展开全文
  • 索引失效原因及解决索引失效方法

    万次阅读 2018-04-08 14:20:19
    原文:https://www.2cto.com/database/201712/702834.html索引失效原因一.准备工作创建student表,id是主键创建复合索引?1create index idx_name_age on student...索引失效原因1.全值匹配我最爱?1explain select * ...
  • oracle索引使用及索引失效总结容易引起oracle索引失效的原因很多:1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等,对索引列进行运算.需要建立函数索引就可以解决了。2、新建的表还没来得及生成统计信息,分析一下...
  • 索引在我们使用MySQL数据库时可以极大的提高查询效率,然而,有时候因为使用上的一些瑕疵就会导致索引的失效,无法达到我们使用索引的预期效果,今天介绍几种MySQL中几种常见的索引失效的原因,可以在以后的工作中尽...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,355
精华内容 2,542
关键字:

索引失效