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

    万次阅读 多人点赞 2018-05-10 19:02:32
    前提条件:表中已添加复合索引(username,password,age)分析:该查询缺少username,查询条件复合索引最左侧username缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描分析:查询条件缺少username,...

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

    前提条件:表中已添加复合索引(username,password,age)


    分析:该查询缺少username,查询条件复合索引最左侧username缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描


    分析:查询条件缺少username,password,查询条件复合索引最左侧username,password缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描


    分析:该查询只有一个username条件,根据最佳左前缀原则索引能够被使用到,但是是部分使用

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


    分析:第一个图索引列不使用函数,遵循左前缀原则,能够使用索引。第二张图索引列上使用了函数,即使遵循左前缀原则,索引还是失效

    3、存储引擎不能使用索引中范围条件右边的列,范围之后索引失效。(< ,> between and)



    分析:图一索引全部使用到。图二索引使用到username和age,但是username是使用索引检索,而age着重索引排序,这时age为范围查找,password索引将失效

    4、mysql使用不等于(!= 或者<>)的时候,无法使用索引,会导致索引失效



    5、mysql中使用is not null 或者 is null会导致无法使用索引


    分析:对username列做了普通索引,查询带is not null,结果索引不生效

    6、mysql中like查询是以%开头,索引会失效变成全表扫描,覆盖索引。


    分析:对username列做了普通索引,以%开头进行查询,结果索引失效被覆盖

    7、mysql中,字符串不加单引号索引会失效。正确写法:select * from t_user where username = 'lujin';

    8、mysql中,如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引


    9、如果mysql使用全表扫描要比使用索引快,则不会使用到索引

    展开全文
  • mysql 索引失效情况

    2018-03-19 16:54:56
    基于MySQL5.5.33数据量: 索引失效情况总结: 创建索引sql:ALTER TABLE `table_name` ADD INDEX index_name ( `column` ),表中先创建2个字段的普通索引 1.sql语句中or的语句使用,执行sql: 1).生效 2).未生效...

    基于MySQL5.5.33


    数据量:

        

    索引失效情况总结:

        创建索引sql:ALTER TABLE `table_name` ADD INDEX index_name ( `column` ),表中先创建2个字段的普通索引             

            1.sql语句中or的语句使用,执行sql:

                1).生效

        

                2).未生效

                    

            总结:or的使用2边必须都是索引列,如果有一列不是,则索引无法生效,进行了全表扫描

            2.like的使用:

                1).生效


                2).未生效:

                


            总结:like的百分号不能写在前面,否则无法生效,进行全表扫描

            3.索引列上进行计算:

                1).生效


                2).未生效


             总结:索引列上不能进行计算,可以将计算放到条件上

            4.索引列是varchar类型

                1).生效


                2).未生效


            总结:索引列如果是varchar字符串类型,则必须加上''进行包含

            5.组合索引:创建索引sql(ALTER TABLE `table_name` ADD INDEX index_name(`column`,`column`,`column`);),遵从最左前缀利用索引中最左边的列集来匹配行,这样的列称为最左前缀,例如由id,name,age3个字段构成的索引,索引行中按id/name/age的顺序存放,索引可以搜索下面字段组合:(id,name,age)、(id,name)、或者id。如果列不构成索引最左面的前缀,MySQL不能使用局部索引,如(age)或者(name,age)组合则不能使用索引查询

        

                1)生效:

       





                2).未生效

                    1.未按照最左前缀的原则:

                        


                    2.按照最左前缀使用or进行查询:

      

                    3.遵从原则,在索引列上进行计算


               

            

            

    展开全文
  • MySQL索引失效情况分析:前几天面试官问我,MySQL索引是失效的原因有哪些,脑子突然短路,平时也没仔细考虑,现在上网搜了一些资料: where语句使用or,not in(),not exist 使用like %在前面 使用函数或者+-*\运算...

    MySQL索引失效情况分析:

    前几天面试官问我,MySQL索引是失效的原因有哪些,脑子突然短路,平时也没仔细考虑,现在上网搜了一些资料:

    • where语句使用or,not in(),not exist

    • 使用like %在前面

    • 使用函数或者+-*\运算 如where id+1<6;

    • 索引列是字符串类型的,但是使用时没有加引号,如where name=123;

    • MySQL估计全表扫描比使用索引更快,比如使用索引获得的数据超过全表信息的30%;

    • 索引本身失效

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

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

    • 对小表查询

    展开全文
  • 3.在索引列上做任何操作(计算、函数、自动或手动类型转换),会导致索引失效而转向全表扫描。 4.不能使用索引中范围条件右边的列。(范围条件之后的索引全失效,但是范围条件之前的和范围条件的还是用到索引的。)...

    目录

    一、准备工作,准备sql

    二、索引是什么、使用explain关键字来解析sql使用索引情况

    三、索引的使用与失效场景

    1.使用全部复合索引(索引正常使用)

    2.最佳左前缀法则

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

    4.不能使用索引中范围条件右边的列。(范围条件之后的索引全失效,但是范围条件之前的和范围条件的还是用到索引的。)

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

    6.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。(8.0似乎优化了,不等于是范围了,待验证)

    7.is null、is not null也无法使用索引(这里不知道为什么is null还是走了索引。。有明白的小伙伴可以解释一下)。

    8.like以通配符开头('%abc...')会导致索引失效。

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

    10.关于in关键字

    ​四、总结



    一、准备工作,准备sql

    这里我使用的mysql版本是 5.5.40。

    CREATE TABLE `student`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20),
      `age` int(11),
      `address` varchar(25),
      `crtdate` timestamp,
      PRIMARY KEY (`id`) USING BTREE
    );
    
    insert into student(name,age,address,crtdate) values('张三', 15, '山东青岛', now());
    insert into student(name,age,address,crtdate) values('李四', 17, '山东济南', now());
    insert into student(name,age,address,crtdate) values('王五', 18, '山东潍坊', now());
    
    -- 创建复合索引
    alter table student add index idx_stu_nameAgeAddr(name,age,address);

    二、索引是什么、使用explain关键字来解析sql使用索引情况

    explain如何使用,请阅读这篇文章:

    MySQL高级-索引是个什么东西?explain到底怎么用-MySQL查询优化大全

    三、索引的使用与失效场景

    1.使用全部复合索引(索引正常使用)

    之前创建了name、age、address三个字段的复合索引,查询条件根据这三个字段,是可以直接使用索引的。

    -- sql贴在这里了
    explain select * from student where name='张三' and age=15 and address = '山东青岛';

    2.最佳左前缀法则

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

    -- sql贴在这里了
    explain select * from student where name='张三' and age=15;
    explain select * from student where name='张三';
    explain select * from student where age=15 and address = '山东青岛';
    explain select * from student where name='张三' and address = '山东青岛';

    以下这两种查询都是没问题的,因为遵守最佳左前缀法则,条件字段从左到右,从第一个索引字段name开始并且也不跳过索引中间的列:

    以下会造成索引完全失效,因为没有使用复合索引的第一列name:

    以下会造成索引部分失效,第一个条件name是走索引的,但是address因为跳过了age所以address这个字段索引失效:

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

    -- sql贴这里了
    explain select * from student where left(name,1)='张';

    索引列上使用了表达式,如where substr(name, 1, 3)='张小四'、where num = num +1,表达式是一大忌讳,再简单的表达式mysql也会造成索引失效。

    有时数据量不是大到严重影响速度时,一般可以先查出来,比如先查询所有订单记录的数据,再在程序中筛选。

    字符串不加单引号索引失效:(做了隐式的类型转换)

    4.不能使用索引中范围条件右边的列。(范围条件之后的索引全失效,但是范围条件之前的和范围条件的还是用到索引的。)

    -- sql贴这里了
    explain select * from student where name='张三';
    explain select * from student where name='张三' and age=15;
    explain select * from student where name='张三' and age=15 and address = '山东青岛';
    explain select * from student where name='张三' and age>15 and address = '山东青岛';

    很显然,最后一条sql,只有name、age走了索引,但是address失效了。

     

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

    我们都知道,Extra中出现using index是好现象,对查询效率会增强。

    而且有范围查询时,不用*的时候,key_len会更小,这意味着会增加查询效率。

    6.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。(8.0似乎优化了,不等于是范围了,待验证)

    -- sql贴这里了
    explain select * from student where name='张三';
    explain select * from student where name!='张三';
    explain select * from student where name<>'张三';

    7.is null、is not null也无法使用索引(这里不知道为什么is null还是走了索引。。有明白的小伙伴可以解释一下)。

    -- sql贴这里了
    explain select * from student where name='张三';
    explain select * from student where name is null;
    explain select * from student where name is not null;

    8.like以通配符开头('%abc...')会导致索引失效。

    like以通配符开头会导致索引失效,但是通配符放在后面索引就会正常使用。

    -- sql在这里
    explain select * from student where name='张三';
    explain select * from student where name like '%张';
    explain select * from student where name like '张%';
    explain select * from student where name like '%张%';

    但是,这种情况like前面使用通配符,也是会走索引的:(select查询的字段刚好是复合索引的字段,所以为什么在查询的时候不建议使用*)

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

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

    10.关于in关键字

    in关键字在mysql中是个很神奇的存在

    -- 主键
    explain select * from student where id in (1,2);
    explain select * from student where id in (1,2,3,4,5,6,7,8,9);
    -- 复合索引
    explain select * from student where name in ('李四');
    explain select * from student where name in ('李四', '王五');

    我们可以看到,in使用关键字少的时候,会走索引的,但是内容较多,就会造成索引失效

    四、总结

    展开全文
  • 主要介绍了mysql索引失效的5种情况分析,需要的朋友可以参考下
  • Mysql索引失效情况

    千次阅读 2017-12-24 22:27:38
    最近做项目的时候,写sql语句的时候,有些字段明明加了索引,但是查询却依旧很慢....下面就转载了一些mysql索引失效情况. 转载地址:http://blog.csdn.net/guoxingege/article/details/51034387
  • Mysql 索引失效情况总结

    千次阅读 2018-03-08 14:59:19
    1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表)2. 统计信息失效 需要重新搜集统计信息3. 索引本身失效 需要重建索引下面是一些不会使用到索引的原因 ...
  • MySQL索引的创建与使用 https://blog.csdn.net/justry_deng/article/details/81458470 mysql索引失效情况 https://blog.csdn.net/qq_34258346/article/details/80272198
  • mysql索引失效的场景

    2021-01-04 14:34:30
    前言 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效。还有一些MySQL“军规”或者...MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时,MySQL
  • MYSQL索引失效

    2018-06-28 14:30:47
    计算,函数,类型转换等,都会导致索引失效3、存储引擎不能使用索引中范围条件最右边的列在范围查找后,会导致其后的索引失效4、减少select *的使用只访问索引列的查询,尽量使用覆盖索引5、MYSQL 中!=,&lt;&...
  • mysql索引失效

    千次阅读 2017-10-20 17:03:37
    mysql索引失效情况分析  索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引...
  • 【索引失效】什么情况下会引起MySQL索引失效 索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)  注意:要想...
  • MySQL索引失效5种情况

    2020-09-01 11:48:25
    MySQL索引失效5种情况: 1.索引字段在查询时存在隐式的类型转换 比如student表有字段phone定义为vachar类型,且定义为辅助索引,当查询语句写成: select id,name from student where phone =18628562245 此时用...
  • mysql索引失效情况

    千次阅读 2014-10-11 12:09:12
    =...),MYSQL将无法使用索引; 2、WHERE字句的查询条件里使用了函数的列(如:WHERE DAY(column)=...),MYSQL将无法使用索引; 3、在JOIN操作中,主键和外键的数据类型不相同,MYSQL将无法使用索引; 4、WHERE...
  • MySQL索引的建立对于...以下就列举出几种索引失效情况: 在举例之前,先简单的介绍一下MySQL中的explain,explain这个命令可以查看SQL语句的执行计划,结果如图所示: id: SELECT 查询的标识符. 每个 SELECT ...
  • Mysql索引失效

    2021-02-22 17:02:48
    一让走了索引,(所以不能轻易的说使用or索引失效了) 当我将or两边的查询条件换了,account_id并没有创建索引,此时查询没有走索引,走的全表扫描。 总结:or条件2边都创建索引,他还是走索引的,就如我第二...
  • =, 索引失效 2、对索引列进行计算、函数、类型转换(手动或自动,字符串不加引号时,会自动转型),索引失效 3、在有范围值时,范围条件后面的索引失效 4、like以通配符开头,索引失效 5、少用or、用or...
  • mysql索引失效情况及使用

    千次阅读 2018-12-28 01:07:36
    索引失效的几种情况  1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)  要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引  2.对于多列索引,不是使用的第一...
  • MySQL 索引失效的场景

    2021-04-15 15:20:12
    MySQL 查询索引失效情况 使用 or 导致索引失效 select * from user where user_id = 1 or age = 20; user_id和age是索引,以上语句索引失效。 where语句中索引列使用了负向查询,可能会导致索引失效 负向查询...
  • MySQL 索引失效案例

    千次阅读 2019-10-29 11:10:56
    索引失效与优化 1、全值匹配我最爱 2、最佳左前缀法则(带头索引不能死,中间索引不能断) 如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且 不跳过索引中的列。 正确的示例参考上...
  • Mysql索引失效的原因

    千次阅读 2018-11-25 20:22:26
    Mysql索引失效的原因 1、最佳左前缀原则——如果索引了多列,要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。 2、不在索引列上做任何操作(计算,函数,(自动或者手动)类型装换),...
  • 今天项目需要,历史数据有一张表,数据量大概80W条左右,由于项目架构要求查询不能超过3秒,80万数据分页查询时候,时间大概在5秒左右,显然不达标,后来增加了索引,时间提升至500ms左右,提升性能10倍左右,非常爽...
  • MySQL索引原理、失效情况

    万次阅读 多人点赞 2019-04-19 17:19:17
    1 mysql索引知识 1.1 B+Tree索引 1.2 主键索引和普通索引的区别 1.3 唯一索引vs普通索引 2 mysql索引优化 2.1 查看索引使用情况 2.2 mysql索引使用策略 2.3 mysql索引使用原则 1 mysql索引知识 1.1 ...
  • mysql版本8.0.11为例...= 会使索引失效 然而如果是主键索引 &lt;&gt;、not in、 != 同样能生效 in, is null, is not null,&gt;,&gt;=,&lt;,&lt;=,= 索引都能生效 2. where name like ...
  • mysql索引失效的原因

    千次阅读 2018-12-19 19:37:10
    mysql索引失效总结 一、单表查询时索引失效 1、mysql查询单表时,查询得到的结果集占数据总量很大比例,mysql会认为全表扫描会优于索引,则不走索引。 例:比如企业人员信息表 (userInfo),字段(user_id、...
  • mysql 索引失效原因

    千次阅读 2018-05-24 16:27:53
    索引失效原因总结1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表)2. 统计信息失效 需要重新搜集统计信息3. 索引本身失效 需要重建索引下面是一些不会...
  • 索引一失效情况:1、 对单字段建了索引,where条件多字段。2、 对索引列运算,运算包括(+、-、*、/、!、&lt;&gt;、%、like'%_'(%放在前面)、or、in、exist等),导致索引失效。3、类型错误,如字段类型...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 50,220
精华内容 20,088
关键字:

mysql索引失效的情况

mysql 订阅