精华内容
下载资源
问答
  • Mysql、Oracle、PostgreSql数据库索引失效场景详细讲解
    2020-10-23 16:23:51

    Mysql、Oracle、PostgreSql数据库索引失效场景详细讲解

    前言

    Mysql、Oracle、PostgreSql数据库索引失效场景详细讲解。废话不多说直接贴:

    1、任何计算、函数、类型转换
    2!=<>
    3、IS NULL或者IS NOT NULL。类似导致索引失效的还有NOT IN,NOT LIKE等,但是NOT EXISTS不会导致索引失效。
    4、模糊查询通配符在开头
    5、索引字段在表中占比较高
    6、多字段btree索引查询条件不包含第一列
    7、在WHERE中使用OR时,有一个列没有索引,那么其它列的索引将不起作用
    8、多字段索引查询条件使用OR
    在索引列上进行大于大于这类的比较后,这个列的索引是有效的,但是其后的索引将失效
    9、覆盖索引不写*
    尽量使用覆盖索引,而不要用select *。示例中,如果我们业务上只需要使用username、age、gender这三个字段,那么我们就使用
    select username, age, gender,而不要用select *。这是因为这三个字段在索引中就已经维护了它们的值,定位索引后,就能检索出
    它们的值。如果使用select *的话,定位到索引后,由于索引没有维护city的值,所以其后还会去检索city的值,造成了时间开销.
    
    更多相关内容
  • mysql索引失效

    前言

    mysql的调优方面包括 表结构优化、索引优化、sql语句优化、分表分库优化等多个维度,本篇重点总结的是索引失效的场景和原因。


    写博客是自己对知识梳理,目前是写给自己看,算是自己学习后的作业,也是为了养成一个良好的习惯。

    一、不满足最左匹配原则

    先复习一下最左匹配原则:
    	1. 创建一个联合索引 index(a,b,c) 则相对于创建了 idx_a(a)idx_a_b(a,b)idx_a_b_c(a,b,c) 3个索引。
    	-------------------------------------------------------
    	select * from table where a='a' and c='c';
    	这种都之会走 idx_a索引,而 字段 c则不会走索引。
    	-------------------------------------------------------
    	select * from table where b='b' and c='c';
    	这种会走全表扫描,index索引失效。
    	-------------------------------------------------------
    
    	2. 联合索引在遇到范围查询之后字段的索引就会失效。
    	-------------------------------------------------------
    	select * from table where a like 'a%' and b='b';
    	这种会走idx_a索引的范围检索,字段b不会走索引。
    	-------------------------------------------------------
    

    二、频繁回表

    	先来分析一下以下sql在mysql下是如何执行的:
    	select * from table where age > 20 ; --其中 字段age 创建索引 
    	会先在索引age字段的b+tree上查询到关于符合条件的id,然后在聚簇索引上通过id回表查询到完整数据。
    	则select * 一定会有回表操作,当查询的结果超过了总数一半以上且总数很多时,这个时候回表开销比全表扫描的开销还大,则mysql优化器会它走全表扫描,即索引失效了。
    

    三、索引列上有计算

    	举例:select * from t_user where age+1=10;
    	这种索引会直接失效。
    

    四、索引列上有函数

    	举例:select * from user where SUBSTR(height,1,2)=17;
    	这种索引会直接失效。
    

    五、字段类型不同

    	举例:select * from user where type = '5';
    	type是整数类型,查询时用字符串,mysql会做隐式转换,索引会直接失效。
    	-------------------------------------------------------
    	举例:select oi.*,ui.name from order_info oi left join user_info ui on oi.user_id = ui.id;
    	其中order_info的user_id是字符串类型,user_info的id是整数类型,即便order_info的user_id创建索引也会由于字段类型不同而做隐式转换,索引会失效。
    

    六、列对比

    	举例:select * from user where id=height;
    	列对比索引会直接失效。
    

    七、常见索引失效

    	诸如索引列 使用了 not in、 like ‘%x’ 、or 都会索引失效
    
    展开全文
  • 下面我们就来说一下在mysql中索引失效的几种场景吧。 首先先来看看测试的表结构 dept部门表 建立的索引(name,number,comment三个字段的复合索引) 测试数据 1.遵从最左前缀...

    学习mysql是作为一名Java工程师必不可少的事情,但是我们只认识mysql的增删查改建表等等的sql语句其实远远不够的,对于进阶mysql来说,索引是一个很重要的部分。下面我们就来说一下在mysql中索引失效的几种场景吧。

    首先先来看看测试的表结构

    dept部门表

    建立的索引(name,number,comment三个字段的复合索引)

    测试数据

     

    1.遵从最左前缀原则

    什么是最左前缀原则?其实所谓的最左前缀原则是对于复合索引来说的,一个复合索引是由两个或以上的列去构建的,此时就有了索引顺序的问题了,比如说我又一个student表,里面有id,name,age,score这四个字段,其中我给name,age,score建立了复合索引,那么我们在去根据索引去查询数据的时候where条件后面就要是name或者name=? and age = ?又或者name=? and age=? and score = ?,而不能是age=?或者score=?或者age=? and score=?,总而言之就是最左边的索引一定要是在第一位。我们下面用explain关键字来查看sql的执行计划。

    我们这里关注的列有type,possible_keys可能用上的索引,key真正用上的索引,key_len(真实使用到的索引的长度,使用到的索引越多这个值越大),ref,row(在表中扫描了多少行,这值越少说明索引的效果越明显),extra。

    我们能看到由于我们建立了name,age,comment的复合索引,而我们在where条件里面也确实使用到了这三个列的值作为条件并且顺序也符合了最左前缀原则,所以type为ref,表示这条sql语句使用了索引去查询数据。假如我们使用name和age作为where条件尼?结果

    可以看出基本没什么变化,唯一变化的就是key_len的值变少了,说明我们的用到的索引少了。

    假如我们不遵守最左前缀原则尼?

    可以发现type变成了all了,即此时的查询为全表扫描,并没有使用到索引了。

    而假如我们跳过了number字段的条件查询尼?

    发现name这个索引依然被使用到,这说明了最左前缀原则就是最左边的索引至少一定要在第一位复合索引才能被使用到。

    对索引进行大小条件的范围比较也是会使得后面的索引无效的,我们可以看

     可以看到完全使用上三个字段的索引时key_len是41,而现在是28,其实是comment这个字段的索引没有用上,因为number使用了“<”的条件范围比较,所以会使得后面的comment字段的索引无效

    其实我们可以自己造点数据去理解最左前缀原则,比如:

    可以把排序后的数据当成就是name,number,comment三个字段的复合索引作用排序后的结果。当我们的查询语句的条件写成name = ‘c’ and comment = ‘c’,我们可以看到,根据索引能直接挑选出name=‘c’的数据行,然后此时我们如果c这一列的值是乱序的,导致了我们comment=‘c’的这一列的索引直接失效,相当扫描了全部name=‘c’的数据行中,comment=‘c’的数据行,而如果我们在name和comment中间加上number=50尼?先查出name=‘c’的数据行,然后在筛选出number=‘50’的数据行,然后此时得到的数据行对于comment这列来说的话就是有序的了,正是因为有序,所以查找comment=‘c’的数据行就不用扫描筛选下来的全部的数据了,此时comment这列的索引就生效了。

    2.不要在索引列上进行函数或者四则运算以及(自动或手动)类型转换

    在索引列上面进行函数或者加减乘除的四则运算也会使得这个索引失效。

    type等于all,即全表扫描,即此时test列上的索引没有用到,索引失效。

    3.尽量不要使用select * 

    在写查询语句的时候,尽量做到要什么字段就select什么字段,而且该字段最好与复合索引的字段顺序保持一致,不要直接用*来查出所有的字段,因为当我们查询的字段与复合索引的字段顺序保持一致的话,那么我们就可以直接去遍历我们的索引文件去查询数据,即全索引查询。

    可以看到我们这里select的字段顺序与复合索引顺序一致,possible_keys为mysql优化器中察觉到我们的sql中并没有使用到索引去查询数据,因为我们并没有添加任何的查询条件,很明显这应该是一个全表查询呀,但是我们发现key的值是我们的复合索引,说明实际上该sql是使用到了索引去查询数据的,type也不是all,而是index(全索引查询),为什么尼?因为当我们select里面的字段和我们建立的复合索引的字段顺序一致时,mysql就会直接去我们遍历我们的索引,而索引其实就是一棵树(mysql是b+Tree),遍历索引就是遍历一棵树,树的叶子节点都包含了我们的数据对应的物理地址,这就是全索引查询。而select * 如果字段不是和复合索引的字段范围之内并且顺序一致的话,就会导致type为all,而index明显是比all性能要高的,因为遍历一棵树所需的时间复杂log2(n)与全表遍历的时间复杂度n来比较的话,明显是遍历索引比较快了,而且全表扫描所付出的磁盘IO消耗比全索引扫描要大得多。 而用到全索引扫描时,我们可以看到Extra该列里面有一个Using index的值表明该sql使用了全索引扫描。

    5.不要在索引列中使用!=导致索引失效,进而导致全表扫描

    type等于all,即全表扫描。

    6.在索引列中使用like %字符串%会使得索引失效进而会全表扫描

    type为all,即全表扫描。

    那么如果我们换成yanfa%的话就能避免type等于all了

    其实很容易理解,%yanfa%表示查找有任意yanfa这个字符串的数据,这样的话去匹配我们的索引的话就不能直接定位到哪条数据了,而只能通过全表扫描来进行一遍遍地区查看哪条数据有yanfa这个字符串了。而使用yanfa%去匹配的话,意思就是查找某列以yanfa开头的数据,至少mysql能通过索引去匹配yanfa开发的数据,这样得到的type就是range(范围查询)了。

    7.字符串不加单引号会索引失效

    这里的testint字段是一个varchar类型的字段,而且在这列上创建了普通索引,按道理来说应该是可以用上索引的,但是我们现在看到type是all,发生了全表扫描,即没有使用到索引。其实我们仔细看一下可以发现testint是一个varchar类型的字段,但是我们的where条件后面的0是一个int类型的值,在mysql底层会自动转换类型为varchar的类型,所以这句sql的查询是没问题的,问题就出在为什么varchar类型的字段,如果值是一个数字的话一定要加上单引号尼,这时我们再回到上面的第二点“自动或手动的类型转换也会使得索引失效”,而我们上面这种情况就属于自动的类型转换,mysql自动地帮我们进行了类型转换,导致该索引失效了。

    那么如果我们的需求只能用%字符串%这种匹配方式来实现尼?这时我们回到这种方式的弊端来想,这种方式最大的坏处就是全表扫描,那么我们就优化成不要全表扫描就好了,这时我们可以想到使用覆盖索引去解决。

    这时type就从all变成了index了。

    8.尽量不要使用or关键字

    使用or关键去添加查询条件的话会导致索引失效,type为性能最差的all。 

    上面说的索引失效的例子,我们在开发中是尽量避免,如果我们的需求只能这样去实现的话,那么我们不能因为这样会导致索引失效而不去使用,这样反而是得不偿失的尼。

     

     

     

     

     

     

    展开全文
  • 另外,无论是面试或是日常,Mysql索引失效的通常情况都应该了解和学习。 为了方便学习和记忆,这篇文件将常见的15种不走索引情况进行汇总,并以实例展示,帮助大家更好地避免踩坑。建议收藏,以备不时之需。 数据库...

    背景
    无论你是技术大佬,还是刚入行的小白,时不时都会踩到Mysql数据库不走索引的坑。常见的现象就是:明明在字段上添加了索引,但却并未生效。
    前些天就遇到一个稍微特殊的场景,同一条SQL语句,在某些参数下生效,在某些参数下不生效,这是为什么呢?
    另外,无论是面试或是日常,Mysql索引失效的通常情况都应该了解和学习。
    为了方便学习和记忆,这篇文件将常见的15种不走索引情况进行汇总,并以实例展示,帮助大家更好地避免踩坑。建议收藏,以备不时之需。
    数据库及索引准备
    创建表结构
    为了逐项验证索引的使用情况,我们先准备一张表t_user:

    CREATE TABLE `t_user` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
      `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
      `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
      `age` int(11) DEFAULT NULL COMMENT '年龄',
      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`id`),
      KEY `union_idx` (`id_no`,`username`,`age`),
      KEY `create_time_idx` (`create_time`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


    在上述表结构中有三个索引:

    -- 删除历史存储过程
    DROP PROCEDURE IF EXISTS `insert_t_user`
    ​
    -- 创建存储过程
    delimiter $
    ​
    CREATE PROCEDURE insert_t_user(IN limit_num int)
    BEGIN
      DECLARE i INT DEFAULT 10;
        DECLARE id_no varchar(18) ;
        DECLARE username varchar(32) ;
        DECLARE age TINYINT DEFAULT 1;
        WHILE i < limit_num DO
            SET id_no = CONCAT("NO", i);
            SET username = CONCAT("Tom",i);
            SET age = FLOOR(10 + RAND()*2);
            INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());
            SET i = i + 1;
        END WHILE;
    ​
    END $
    -- 调用存储过程
    call insert_t_user(100);

    关于存储过程的创建和存储,可暂时不执行,当用到时再执行。

    数据库版本及执行计划

    查看当前数据库的版本:

    select version();
    8.0.18

    上述为本人测试的数据库版本:8.0.18。当然,以下的所有示例,大家可在其他版本进行执行验证。

    查看SQL语句执行计划,一般我们都采用explain关键字,通过执行结果来判断索引使用情况。

    执行示例:

    explain select * from t_user where id = 1;

    执行结果:

    explain


    可以看到上述SQL语句使用了主键索引(PRIMARY),key_len为4;
    其中key_len的含义为:表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到非常重要。
    做好以上数据及知识的准备,下面就开始讲解具体索引失效的实例了。
    1 联合索引不满足最左匹配原则
    联合索引遵从最左匹配原则,顾名思义,在联合索引中,最左侧的字段优先匹配。因此,在创建联合索引时,where子句中使用最频繁的字段放在组合索引的最左侧。
    而在查询时,要想让查询条件走索引,则需满足:最左边的字段要出现在查询条件中。
    实例中,union_idx联合索引组成:

    KEY `union_idx` (`id_no`,`username`,`age`)

    最左边的字段为id_no,一般情况下,只要保证id_no出现在查询条件中,则会走该联合索引。

    示例一

    explain select * from t_user where id_no = '1002';

    explain结果:

    explain-01


    通过explain执行结果可以看出,上述SQL语句走了union_idx这条索引。
    这里再普及一下key_len的计算:

    • id_no 类型为varchar(18),字符集为utf8mb4_bin,也就是使用4个字节来表示一个完整的UTF-8。此时,key_len = 18* 4 = 72;
    • 由于该字段类型varchar为变长数据类型,需要再额外添加2个字节。此时,key_len = 72 + 2 = 74;
    • 由于该字段运行为NULL(default NULL),需要再添加1个字节。此时,key_len = 74 + 1 = 75;

    上面演示了key_len一种情况的计算过程,后续不再进行逐一推演,知道基本组成和原理即可,更多情况大家可自行查看。
    示例二:

    explain select * from t_user where id_no = '1002' and username = 'Tom2';

    explain结果:

    explain-02


    很显然,依旧走了union_idx索引,根据上面key_len的分析,大胆猜测,在使用索引时,不仅使用了id_no列,还使用了username列。
    示例三

    explain select * from t_user where id_no = '1002' and age = 12;


    explain结果:

    explain-03


    走了union_idx索引,但跟示例一一样,只用到了id_no列。
    当然,还有三列都在查询条件中的情况,就不再举例了。上面都是走索引的正向例子,也就是满足最左匹配原则的例子,下面来看看,不满足该原则的反向例子。
    反向示例

    explain select * from t_user where username = 'Tom2' and age = 12;


    explain结果:

    explain-04


    此时,可以看到未走任何索引,也就是说索引失效了。
    同样的,下面只要没出现最左条件的组合,索引也是失效的:

    explain select * from t_user where age = 12;
    explain select * from t_user where username = 'Tom2';


    那么,第一种索引失效的场景就是:在联合索引的场景下,查询条件不满足最左匹配原则


    2 使用了select *
    在《阿里巴巴开发手册》的ORM映射章节中有一条【强制】的规范:
    【强制】在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明。 说明:1)增加查询分析器解析成本。2)增减字段容易与 resultMap 配置不一致。3)无用字段增加网络 消耗,尤其是 text 类型的字段。

    虽然在规范手册中没有提到索引方面的问题,但禁止使用select * 语句可能会带来的附带好处就是:某些情况下可以走覆盖索引
    比如,在上面的联合索引中,如果查询条件是age或username,当使用了select * ,肯定是不会走索引的。
    但如果希望根据username查询出id_no、username、age这三个结果(均为索引字段),明确查询结果字段,是可以走覆盖索引的:

    explain select id_no, username, age from t_user where username = 'Tom2';
    explain select id_no, username, age from t_user where age = 12;


    explain结果:

    覆盖索引


    无论查询条件是username还是age,都走了索引,根据key_len可以看出使用了索引的所有列。
    第二种索引失效场景:在联合索引下,尽量使用明确的查询列来趋向于走覆盖索引
    这一条不走索引的情况属于优化项,如果业务场景满足,则进来促使SQL语句走索引。至于阿里巴巴开发手册中的规范,只不过是两者撞到一起了,规范本身并不是为这条索引规则而定的。
    3 索引列参与运算
    直接来看示例:

    explain select * from t_user where id + 1 = 2 ;


    explain结果:

    索引列计算


    可以看到,即便id列有索引,由于进行了计算处理,导致无法正常走索引。
    针对这种情况,其实不单单是索引的问题,还会增加数据库的计算负担。就以上述SQL语句为例,数据库需要全表扫描出所有的id字段值,然后对其计算,计算之后再与参数值进行比较。如果每次执行都经历上述步骤,性能损耗可想而知。
    建议的使用方式是:先在内存中进行计算好预期的值,或者在SQL语句条件的右侧进行参数值的计算。
    针对上述示例的优化如下:

    -- 内存计算,得知要查询的id为1
    explain select * from t_user where id = 1 ;
    -- 参数侧计算
    explain select * from t_user where id = 2 - 1 ;


    第三种索引失效情况:索引列参与了运算,会导致全表扫描,索引失效


    4 索引列参使用了函数
    示例:

    explain select * from t_user where SUBSTR(id_no,1,3) = '100';


    explain结果:

    索引-函数


    上述示例中,索引列使用了函数(SUBSTR,字符串截取),导致索引失效。
    此时,索引失效的原因与第三种情况一样,都是因为数据库要先进行全表扫描,获得数据之后再进行截取、计算,导致索引索引失效。同时,还伴随着性能问题。
    示例中只列举了SUBSTR函数,像CONCAT等类似的函数,也都会出现类似的情况。解决方案可参考第三种场景,可考虑先通过内存计算或其他方式减少数据库来进行内容的处理。
    第四种索引失效情况:索引列参与了函数处理,会导致全表扫描,索引失效


    5 错误的Like使用
    示例:

    explain select * from t_user where id_no like '%00%';


    explain结果:

    索引-like


    针对like的使用非常频繁,但使用不当往往会导致不走索引。常见的like使用方式有:

    • 方式一:like '%abc';
    • 方式二:like 'abc%';
    • 方式三:like '%abc%';

    其中方式一和方式三,由于占位符出现在首部,导致无法走索引。这种情况不做索引的原因很容易理解,索引本身就相当于目录,从左到右逐个排序。而条件的左侧使用了占位符,导致无法按照正常的目录进行匹配,导致索引失效就很正常了。
    第五种索引失效情况:模糊查询时(like语句),模糊匹配的占位符位于条件的首部


    6 类型隐式转换
    示例:

    explain select * from t_user where id_no = 1002;


    explain结果:

    隐式转换


    id_no字段类型为varchar,但在SQL语句中使用了int类型,导致全表扫描。
    出现索引失效的原因是:varchar和int是两个种不同的类型。
    解决方案就是将参数1002添加上单引号或双引号。
    第六种索引失效情况:参数类型与字段类型不匹配,导致类型发生了隐式转换,索引失效
    这种情况还有一个特例,如果字段类型为int类型,而查询条件添加了单引号或双引号,则Mysql会参数转化为int类型,虽然使用了单引号或双引号:

    explain select * from t_user where id = '2';


    上述语句是依旧会走索引的。


    7、使用OR操作
    OR是日常使用最多的操作关键字了,但使用不当,也会导致索引失效。
    示例:

    explain select * from t_user where id = 2 or username = 'Tom2';


    explain结果:

    or-索引


    看到上述执行结果是否是很惊奇啊,明明id字段是有索引的,由于使用or关键字,索引竟然失效了。
    其实,换一个角度来想,如果单独使用username字段作为条件很显然是全表扫描,既然已经进行了全表扫描了,前面id的条件再走一次索引反而是浪费了。所以,在使用or关键字时,切记两个条件都要添加索引,否则会导致索引失效。
    但如果or两边同时使用“>”和“<”,则索引也会失效:

    explain select * from t_user where id  > 1 or id  < 80;


    explain结果:

    or-范围


    第七种索引失效情况:查询条件使用or关键字,其中一个字段没有创建索引,则会导致整个查询语句索引失效; or两边为“>”和“<”范围查询时,索引失效


    8 两列做比较
    如果两个列数据都有索引,但在查询条件中对两列数据进行了对比操作,则会导致索引失效。
    这里举个不恰当的示例,比如age小于id这样的两列(真实场景可能是两列同维度的数据比较,这里迁就现有表结构):

    explain select * from t_user where id > age;


    explain结果:

    索引-两列比较


    这里虽然id有索引,age也可以创建索引,但当两列做比较时,索引还是会失效的。
    第八种索引失效情况:两列数据做比较,即便两列都创建了索引,索引也会失效


    9 不等于比较
    示例:

    explain select * from t_user where create_time != '2022-02-27 09:56:42';

    explain结果:

    索引-不等


    当查询条件为字符串时,使用”<>“或”!=“作为条件查询,有可能不走索引,但也不全是。

    explain select * from t_user where create_time != '2022-02-27 09:56:42';


    上述SQL中,由于“2022-02-27 09:56:42”是存储过程在同一秒生成的,大量数据是这个时间。执行之后会发现,当查询结果集占比比较小时,会走索引,占比比较大时不会走索引。此处与结果集与总体的占比有关。
    需要注意的是:上述语句如果是id进行不等操作,则正常走索引。

    explain select * from t_user where id != 2;


    explain结果:

    不等-ID


    第九种索引失效情况:查询条件使用不等进行比较时,需要慎重,普通索引会查询结果集占比较大时索引会失效


    10 is not null
    示例:

    explain select * from t_user where id_no is not null;


    explain结果:

    索引-is not null


    第十种索引失效情况:查询条件使用is null时正常走索引,使用is not null时,不走索引


    11 not in和not exists
    在日常中使用比较多的范围查询有in、exists、not in、not exists、between and等。

    explain select * from t_user where id in (2,3);
    ​
    explain select * from t_user where id_no in ('1001','1002');
    ​
    explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);
    ​
    explain select * from t_user where id_no between '1002' and '1003';


    上述四种语句执行时都会正常走索引,具体的explain结果就不再展示。主要看不走索引的情况:

    explain select * from t_user where id_no not in('1002' , '1003');


    explain结果:

    索引-not in


    当使用not in时,不走索引?把条件列换成主键试试:

    explain select * from t_user where id not in (2,3);


    explain结果:

    主键-not in


    如果是主键,则正常走索引。
    第十一种索引失效情况:查询条件使用not in时,如果是主键则走索引,如果是普通索引,则索引失效
    再来看看not exists

    explain select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);


    explain结果:

    索引-not in


    当查询条件使用not exists时,不走索引。
    第十二种索引失效情况:查询条件使用not exists时,索引失效


    12 order by导致索引失效
    示例:

    explain select * from t_user order by id_no ;


    explain结果:

    索引-order by


    其实这种情况的索引失效很容易理解,毕竟需要对全表数据进行排序处理。
    那么,添加删limit关键字是否就走索引了呢?

    explain select * from t_user order by id_no limit 10;


    explain结果:

    order by limit


    结果依旧不走索引。在网络上看到有说如果order by条件满足最左匹配则会正常走索引, 在当前8.0.18版本中并未出现。所以,在基于order bylimit进行使用时,要特别留意。是否走索引不仅涉及到数据库版本,还要看Mysql优化器是如何处理的。
    这里还有一个特例,就是主键使用order by时,可以正常走索引。

    explain select * from t_user order by id desc;


    explain结果:

    主键-order by


    可以看出针对主键,还是order by可以正常走索引。
    另外,笔者测试如下SQL语句:

    explain select id from t_user order by age;
    explain select id , username from t_user order by age;
    explain select id_no from t_user order by id_no;


    上述三条SQL语句都是走索引的,也就是说覆盖索引的场景也是可以正常走索引的。
    现在将idid_no组合起来进行order by

    explain select * from t_user order by id,id_no desc;
    explain select * from t_user order by id,id_no desc limit 10;
    explain select * from t_user order by id_no desc,username desc;


    explain结果:

    orderby多索引


    上述两个SQL语句,都未走索引。
    第十三种索引失效情况:当查询条件涉及到order by、limit等条件时,是否走索引情况比较复杂,而且与Mysql版本有关,通常普通索引,如果未使用limit,则不会走索引。order by多个索引字段时,可能不会走索引。其他情况,建议在使用时进行expain验证。


    13 参数不同导致索引失效
    此时,如果你还未执行最开始创建的存储过程,建议你先执行一下存储过程,然后执行如下SQL:

    explain select * from t_user where create_time > '2023-02-24 09:04:23';


    其中,时间是未来的时间,确保能够查到数据。
    explain结果:

    索引-参数


    可以看到,正常走索引。
    随后,我们将查询条件的参数换个日期:

    explain select * from t_user where create_time > '2022-02-27 09:04:23';


    explain结果:

    索引-参数


    此时,进行了全表扫描。这也是最开始提到的奇怪的现象。
    为什么同样的查询语句,只是查询的参数值不同,却会出现一个走索引,一个不走索引的情况呢?
    答案很简单:上述索引失效是因为DBMS发现全表扫描比走索引效率更高,因此就放弃了走索引
    也就是说,当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时,优化器可能会放弃走索引,自动变成全表扫描。某些场景下即便强制SQL语句走索引,也同样会失效。
    类似的问题,在进行范围查询(比如>、< 、>=、<=、in等条件)时往往会出现上述情况,而上面提到的临界值根据场景不同也会有所不同。
    第十四种索引失效情况:当查询条件为大于等于、in等范围查询时,根据查询结果占全表数据比例的不同,优化器有可能会放弃索引,进行全表扫描。


    14 其他
    当然,还有其他一些是否走索引的规则,这与索引的类型是B-tree索引还是位图索引也有关系,就不再详细展开。
    这里要说的其他,可以总结为第十五种索引失效的情况:Mysql优化器的其他优化策略,比如优化器认为在某些情况下,全表扫描比走索引快,则它就会放弃索引。
    针对这种情况,一般不用过多理会,当发现问题时再定点排查即可。


    小结
    本篇文章为大家总结了15个常见的索引失效的场景,由于不同的Mysql版本,索引失效策略也有所不同。大多数索引失效情况都是明确的,有少部分索引失效会因Mysql的版本不同而有所不同。因此,建议收藏本文,当在实践的过程中进行对照,如果没办法准确把握,则可直接执行explain进行验证。


    最后:

    小编还收集了很多大厂面试题,为帮助开发者们提升面试技能、有机会入职BATJ等大厂公司,特别制作了这个专辑——这一次整体放出。

    所有的面试题目都不是一成不变的,特别是像一线大厂,下面的面试题只是给大家一个借鉴作用,最主要的是给自己增加知识的储备,有备无患。大致内容包括了: Java 集合、JVM、多线程、并发编程、设计模式、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat、spring面试题、spring cloud面试题、spring boot面试题、spring教程 笔记、spring boot教程笔记、最新阿里巴巴开发手册(63页PDF总结)、2022年Java面试手册一共整理了1184页PDF文档。

    需要的私信博主(555)领取,祝大家更上一层楼!!!

    展开全文
  • MySQL-索引失效场景

    2018-08-23 10:09:49
    、%、like'%_'(%放在前面)、or、in、exist等)导致索引失效。 3、多列索引作为条件时,范围查询条件(如&gt;10)后的索引列失效。 4、select 索引列 from table &gt; select * from table &g...
  • MySQL数据库索引失效场景

    千次阅读 2022-01-06 14:13:11
    索引失效场景2.1 常见索引失效的9种情况2.2 索引失效场景总结3. 索引失效验证3.1 全值匹配3.2 最佳左前缀3.3 索引计算3.4 索引范围:索引列上不能有范围查询3.5 索引覆盖:尽量使用覆盖索引3.6 不等: 使用不等于...
  • 今天看到了一篇文章对索引失效的常见场景进行了一个总结,觉得挺好,转过来,方便以后查阅。 点这里:转载链接 MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时,MySQL的优化器...
  • 索引失效场景

    2020-12-01 14:34:18
    我们常见的几个索引失效场景,大致有以下几种: 1、String型数据使用int直接查 2、like ’%a‘的情况 3、组合索引a,b单独使用b 4、命中数据过多到时扫描全表 5、索引列中有计算或者使用了函数 6、a=‘x’ or b=‘y...
  • 数据表的列上建立了索引,查询条件也是索引列。 1,列于列对比 select * from user where u_id = id; 该情况会全表扫描,不会走索引。 如果索引列是可空的,那么很可能是不会给其建立索引的,索引值是少于表的...
  • 问题-索引失效场景

    2022-01-20 08:39:05
    索引失效场景
  • 背景 无论你是技术大佬,还是刚入行的小白,时不时都会踩到Mysql数据库不走索引的坑。常见的现象就是:明明在字段上添加了索引,但却并未生效。前些天就遇到一个稍微特殊的场景,同一条SQL语句...
  • 索引失效 场景

    千次阅读 2017-01-23 22:36:19
    1、查询谓词没有使用索引的主要边界,换句话说就是select *,可能会导致不走索引。 比如,你查询的是SELECT * FROM T WHERE Y=XXX;假如你的T表上有一个包含Y值的组合索引,但是优化器会认为需要一行
  • 索引失效场景 where使用了or 负向查询 != 《》,不大于,不小于,is null ,is not null 隐式类型转换,函数,表达式操作计算 内置函数,like通配符 联合索引中,where中索引列违背最左匹配原则 MySQL优化器的最终...
  • MySQL中索引失效场景 用一句口诀记录:模型数空运最快 模–代表模糊查询 型–代表数据类型 数–代表函数 空–代表NULL 运–代表数值运算 最–代表最左原则 快–代表全表扫描最快 1、like 以%开头,索引无效;当...
  • MySQL 索引失效场景

    2021-03-14 21:36:03
    索引失效场景1.1 隐式类型转换问题1.2 联合索引违反最左前缀匹配原则1.3 对索引列运算1.4 is null 判断1.5 like 使用不当1.6 对索引列使用函数1.7 in 使用不当1.8 or 使用不当1.9 查询优化不走索引2. 没必要建立...
  • 数据库索引失效的一些场景

    千次阅读 2021-04-07 16:47:28
    本篇文章我们将总结一些常见的索引的失效场景。 二、索引失效的场景 【a】存在NULL值条件 在设计数据库时,我们应该尽量避免某个列的值为空,如果非要不可避免的要出现NULL值,我们可以给它一个DEFAULT值,比如-1...
  • 这种是经常出现的场景,例如表t的字段 name(varchar类型),通过条件查询传入数字类型,虽然可以查出所要的结果,但是此时索引没有命中。 反例: select * from t where name=123; 正例: select * from t where ...
  • 索引失效场景

    2021-09-01 16:19:31
    1、存在NULL值的情况,索引值是少于表的count(*)值,...都会使索引失效。 4、在索引列进行函数运算会使索引失效。 5、如果mysql使用全表扫描要比使用索引快,则不会使用到索引。 6、使用or可能会使索引失效。 ...
  • 0. 目录1.使用 or 导致索引失效 1.使用 or 导致索引失效 select * from `user` where user_id = 1 or age = 20; user_id和age是索引,以上语句索引失效
  • MySQL索引失效场景及原因

    千次阅读 2019-03-31 22:38:21
    介绍了常见的索引失效的情况,以及为什么会失效
  • Mysql 索引失效场景

    2021-01-19 05:08:20
    例如:一张USER表 有字段属性 name,age 其中name为索引下面列举几个索引失效的情况1. select * from USER where name=‘xzz’ or age=16;例如这种情况:当语句中带有or的时候 即使有索引也会失效。2.select * from ...
  • MySQL 索引失效场景

    2022-03-31 21:06:24
    1、对索引列运算(如,+、-、*、/),索引失效。 2、查询条件包含or,可能导致索引失效 3、like通配符可能导致索引失效(左侧有%)。 4、左连接查询或者右连接查询查询关联的字段编码格式不一样,可能导致索引...
  • mysql导致索引失效的6个场景
  • 将项目上和网上查找到的索引失效问题总结为7中场景索引失效的7字诀(对应7种场景)“模型数空运最快” 模:代表模糊查询,like的模糊查询以%开头,索引失效。 型:代表数据类型,如字段类型为varchar, where条件...
  • 目录索引1. 索引是什么2. 索引的优点(加快查询效率)3. 索引的缺点(占用空间、更新变慢)4. 索引的类型1. hash2. 平衡二叉树AVL1. 定义2. 问题3. B树1. 定义2. 特点3. B+树(mysql索引推荐的存储结构)1. 定义2. ...
  • mysql 常见索引失效场景

    千次阅读 2020-09-26 21:54:50
    索引失效
  • 本片文章主要围绕索引的使用原则进行深入讲解,包括索引匹配原则、索引失效等。 看完本篇文章你能学到什么? 1、索引匹配的基本原则 2、最左前缀法则 3、索引的全值匹配 4、索引的最佳优化方案 5、以后在公司写...
  • MySQL索引失效场景

    2021-05-25 16:06:11
    MySQL索引失效场景: 针对索引失效场景,便于优化SQL查询 基于以下表结构验证索引失效场景: CREATE TABLE `dynamic_info` ( `id` int(32) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL ...
  • 可能导致索引失效场景: 索引列不独立。独立是指:列不能是表达式的一部分,也不能是函数的参数 使用了左模糊 使用OR查询的部分字段没有索引 字符串条件未使用’'引起来 不符合最左前缀原则的查询 索引字段建议...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 49,231
精华内容 19,692
关键字:

索引失效场景

友情链接: day19.rar