精华内容
参与话题
问答
  • MySQL高级 之 索引失效与优化详解

    万次阅读 多人点赞 2017-05-16 18:40:27
    索引失效与优化1、全值匹配我最爱2、最佳左前缀法则(带头索引不能死,中间索引不能断)如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且 不跳过索引中的列。 正确的示例参考上图。...

    案例所用的表结构、索引、与数据如下:

    这里写图片描述
    这里写图片描述

    索引失效与优化

    这里写图片描述

    1、全值匹配我最爱

    这里写图片描述

    2、最佳左前缀法则(带头索引不能死,中间索引不能断)

    如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且 不跳过索引中的列。
    正确的示例参考上图。

    错误的示例:
    带头索引死:
    这里写图片描述
    中间索引断(带头索引生效,其他索引失效):
    这里写图片描述

    3、不要在索引上做任何操作(计算、函数、自动/手动类型转换),不然会导致索引失效而转向全表扫描

    这里写图片描述

    4、mysql存储引擎不能继续使用索引中范围条件(bettween、<、>、in等)右边的列

    这里写图片描述

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

    这里写图片描述

    6、索引字段上使用(!= 或者 < >)判断时,会导致索引失效而转向全表扫描

    这里写图片描述

    7、索引字段上使用 is null / is not null 判断时,会导致索引失效而转向全表扫描

    这里写图片描述

    8、索引字段使用like以通配符开头(‘%字符串’)时,会导致索引失效而转向全表扫描

    这里写图片描述
    由结果可知,like以通配符结束相当于范围查找,索引不会失效。与范围条件(bettween、<、>、in等)不同的是:不会导致右边的索引失效。

    问题:解决like ‘%字符串%’时,索引失效问题的方法?
    使用覆盖索引可以解决。
    这里写图片描述
    这里写图片描述

    9、索引字段是字符串,但查询时不加单引号,会导致索引失效而转向全表扫描

    这里写图片描述

    10、索引字段使用 or 时,会导致索引失效而转向全表扫描

    这里写图片描述

    小总结

    这里写图片描述

    这里写图片描述

    展开全文
  • 索引在什么情况下会失效

    千次阅读 2018-11-17 23:03:50
    1.对于创建的多列索引(复合索引),不是使用的第一部分就不会使用索引 alter table student add index my_index(name, age) // name左边的列,age右边的列 select * fro...

    1. 对于创建的多列索引(复合索引),不是使用的第一部分就不会使用索引

           alter table student add index my_index(name, age)   // name左边的列, age 右边的列                                                              

           select * from student where name = 'aaa'     // 会用到索引

           select * from student where age = 18          //  不会使用索引

     

    2. 对于使用 like 查询, 查询如果是 ‘%aaa’ 不会使用索引,而 ‘aaa%’ 会使用到索引。 

          select * from student where name like 'aaa%'     // 会用到索引

           select * from student where name like '%aaa'        或者   '_aaa'   //  不会使用索引

     

    3. 如果条件中有 or, 有条件没有使用索引,即使其中有条件带索引也不会使用,换言之, 就是要求使用的所有字段,都必须单独使用时能使用索引。

     

    4. 如果列类型是字符串,那么一定要在条件中使用引号引用起来,否则不使用索引。

     

    5. 如果mysql认为全表扫面要比使用索引快,则不使用索引。

          如:表里只有一条数据。

     

    展开全文
  • 1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引  2.对于多列索引,不是使用的第一部分,则不会使用索引  3....

          1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

    要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

      2.对于多列索引,不是使用的第一部分,则不会使用索引

      3.like查询以%开头

    假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件,由于前面是

      模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫

      描。如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,当碰到B开头的

      数据时,就可以停止查找了,因为后面的数据一定不满足要求。这样就可以利用索引了。

      4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

      5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

     

     

    索引的优点

    • 大大减少了服务器需要扫描的数据量
    • 可以帮助服务器避免排序或减少使用临时表排序
    • 索引可以随机I/O变为顺序I/O

    索引的缺点

    • 需要占用磁盘空间,因此冗余低效的索引将占用大量的磁盘空间
    • 降低DML性能,对于数据的任意增删改都需要调整对应的索引,甚至出现索引分裂
    • 索引会产生相应的碎片,产生维护开销
    展开全文
  • 索引失效原因及解决索引失效方法

    万次阅读 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 * ...

    原文:https://www.2cto.com/database/201712/702834.html

    索引失效原因

    一.准备工作

    创建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 '%张%';

    这里写图片描述

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

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

    这里写图片描述

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

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

    这里写图片描述

    展开全文
  • 索引失效和注意事项

    千次阅读 2018-08-24 16:27:03
    索引失效的情况 如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表) ...
  • 索引失效,可以从一下几种原因中分析: 在where条件的等号左侧中使用函数 where date(create_at) = '2019-01-01' 带有列的函数计算值,导致索引无法使用。 表关联字符集格式以及排序方式不一致 关注CHARSET和...
  • 索引失效的几种情况

    千次阅读 2019-05-10 16:53:20
    1、 最好全值匹配——索引怎么建我怎么用 分析 三次查询均使用到索引。... 第一次执行缺少name,查询条件复合索引最左侧name缺少,违反了最佳左前缀法则,导致索引失效,变为ALL,全表扫描。 同...
  • 索引失效原因总结

    万次阅读 多人点赞 2012-02-28 10:38:23
    1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表) 2. 统计信息失效 需要重新搜集统计信息 3. 索引本身失效 需要重建索引 下面是一些不会使用到索
  • 索引失效的原因

    千次阅读 2019-01-31 09:53:07
    索引失效的原因 1.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. ...
  • 索引失效的原因和如何避免

    千次阅读 2019-02-20 11:28:18
    2.创建User表的单索引或者双重索引 create index idIndex on User(id); //单索引 create index index_id_name on User(id,name); //双重索引 3.检查索引是否创建成功 show index from User; 显示下面结果表示...
  • 1、条件中用or,即使其中有条件带索引,也不会使用索引查询 注意:要想使用or,又想索引生效,只能将or条件中的每个列都加上索引 2、对于多列索引,不是使用的第一部分,则不会使用索引 3、like查询是以%开头 4...
  • 一、什么是索引? 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来...二、索引失效几种情况 1.如果条件中有多个字段使用or,即使其...
  • mysql 联合索引生效的条件、索引失效的条件

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

    千次阅读 2020-02-16 11:52:53
    a、b、c建立索引 有效索引: ① a ②a,b ③a,b,c 解释:复合索引建立的存储结构,第二个是依赖于在第一个索引使用的基础上,以此类推。 2、like模糊查询,使用最左精确匹配l ike ‘name%’ ,其他方式全表扫描。 3、...
  • mysql索引失效几种情况(总结)

    万次阅读 2016-12-18 19:11:38
    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效:  1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)  注意:要想使用or,又想让索引生效,只能将or条件中的每...
  • 虽然你这列上建了索引,查询条件也是索引列,但最终执行计划没有走它的索引。下面是引起这种问题的个关键点。 列与列对比 某个表中,有两列(id和c_id)都建了单独索引,下面这种查询条件不会走索引 select * from...
  • 导致MySQL索引失效几种常见写法

    千次阅读 多人点赞 2020-09-06 15:15:41
    最近一直忙着处理原来老项目遗留的一些SQL优化问题,由于当初表的设计以及字段设计的问题,随着业务的增长,出现了大量的慢SQL,导致MySQL的CPU资源飙升,基于此,给大家简单分享下这些...
  • mysql索引失效情况

    万次阅读 2018-05-10 19:02:32
    前提条件:表中已添加复合索引(username,password,age)分析:该查询缺少username,查询条件复合索引最左侧username缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描分析:查询条件缺少username,...
  • 1.索引无法存储null值  a.单列索引无法储null值,复合索引无法储全为null的值。  b.查询时,采用is null条件时,不能利用到索引,只能全表扫描。 2.为什么索引列无法存储Null值?  a.索引是有序的。NULL值进入...
  • 索引什么时候不会生效,以下集中情况会导致索引失效: 1.条件中用or,即使其中有条件带索引,也不会使用索引查询(这就是查询尽量不要用or的原因,用in吧); 注意:使用or,又想索引生效,只能将or条件中的每个...

空空如也

1 2 3 4 5 ... 20
收藏数 104,592
精华内容 41,836
关键字:

索引失效