精华内容
下载资源
问答
  • mysql索引失效
    2022-07-19 09:29:01

    1:索引字段使用了计算或者函数

    2:索引字段数据类型不一致

    比如索引字段是数字类型却传入了字符串类型

    3:违反了最左匹配原则

    使用(> < like between 等范围查询索引会无效

    4:数据库优化器自我选择是否使用索引查询

    更多相关内容
  • Mysql索引失效

    2022-07-26 16:29:44
    针对Mysql数据库索引失效问题网上,有很多见解和答案。最近面试也是时常会被问道的一个知识点,没有太深的原理简单的整理一下,方便面试,加深印象。

    一.前言

    针对Mysql数据库索引失效问题网上,有很多见解和答案。最近面试也是时常会被问道的一个知识点,简单的整理一下,省的以后忘记。

    二.导致索引失效的几个情况

    1.模糊查询:like '%';like '%%'通配符放在开头会导致索引失效。会进行全表扫描

    2.索引类使用函数:带有函数的索引列记录的是索引的原始值,不是计算后的值,所以mysql不会识别,在mysql8.0增加了函数索引,用来解决这一问题。

    3.数据类型发生转换:当字符类型没有使用引号的时候,mysql会自动将字符转换成数字,会用到隐式转换,相当于使用了函数。(但是在8.0函数索引中会不会有效,还没研究)

    4.WHERE 子句中使用OR连接:当or前后都是索引列的时候不会导致索引失效,但当or前后有一个不是索引列的时候索引就会失效,原因是当使用or的时候,有一个条件不满足就会进行全面扫描。

    5.多列组成组合索引:需要遵循最左匹配原则,从索引最左侧顺序进行检索,在组合索引中是按照列的顺序进行存储的,所以查的时候也需要按照顺序查。

    6.索引列使用运算符,!=或者not:类似使用函数。

    7.多表连接查询没有正确使用mysql规则,也会导致索引失效。

     

    三.总结

    Mysql的InnoDB引擎里主要有主键索引和普通索引类型,底层采用的是B+树的数据结构存储数据,而 B+树中的数据是存储在叶子节点,子节点存储索引,并且通过链表的方式把叶子节点中的数据进行连接。正确的使用索引会提高sql的查询效率,当需要对sql进行优化的时候,可以在当前的sql前加上explain关键字,查看当前sql的执行计划,通过type字段是否为ALL,key字段是否为空检查是否索引命中。适当优化sql的查询结构,提升查询效率。

    展开全文
  • 主要给大家介绍了关于导致MySQL索引失效的一些常见写法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • MySQL索引失效

    2022-03-06 10:57:49
    也就是 like %xx,like %xx% 的方式,会造成索引失效,走全表扫描 因为索引 B+ 树是按照【索引值】有序排列 存储的,所以只能根据前缀进行比较 2.对索引使用函数 select ... where length(name) = 6; 索引失效,...

    1.对索引使用 左 或者 左右模糊匹配

    也就是 like %xx,like %xx% 的方式,会造成索引失效,走全表扫描

    因为索引 B+ 树是按照【索引值】有序排列 存储的,所以只能根据前缀进行比较

    2.对索引使用函数

    select ... where length(name) = 6; 索引失效,全表扫描

    因为索引保存的是索引字段的原始值,不止经过函数计算的值,所以走不了索引

    3.对索引使用表达式计算

    跟函数同样的道理

    4.对索引隐式类型转换

    加入用户列表,phone 字段是字符串类型,但是查询的时候 select ... where phone = 100;

    phone会使用cast函数隐式的转换成整型,而前面说了,函数会导致索引失效,所以索引失效。

    select ... where id = '1';会隐式的将字符串转为数字,索引字段并没有使用函数,所以索引没有失效。

    5.联合索引非最左匹配

    多个普通字段组在一起构成的索引就是联合索引

    where a = 1 and b = 2 符合最左匹配

    因为联合索引的B+树中,数据是按照索引的第一列进行排序的,第一列相同,才会排第二列排序。

    如果不指定第一列,就从第二列开始查,是无法走索引的。

    6.where 中的 or

    where 子句中,or 前面的条件如果是索引列,后面不是索引列,就会索引失效。

    MySQL优化:

    1.使用覆盖索引,避免回表

    2.使用多个列作为条件进行查询的时候,选择联合索引替代单列索引

    3.让选择性强的索引列放最前面

    4.explain分析select查询语句

    第一、优化 SQL 和索引

    总的来说就是:避免索引失效造成全表扫描、避免无效查询、最大化利用索引

            1、只返回必要的行:使用 LIMIT 语句来限制返回的数据。

            2、只返回必要的列,避免select *,将需要查找的字段列出来。

            3、避免索引失效

            4、对于连续数值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5

            5、可通过开启慢查询日志来找出较慢的SQL

            6、大语句拆小语句,减少锁时间;一条大sql可以堵死整个库

            7、使用连接(join)来代替子查询

            8、少用JOIN,多表连接时,尽量小表驱动大表,即小表 join 大表

    第二、加缓存redis

    第三、主从复制、主主复制、读写分离

    第四、垂直分表、水平分表

    展开全文
  • 更准确的说,单列索引不存储null值,复合索引不存储全为null的值。索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描。 为什么索引列不能存Null值? 将索引列...
  • MySQL 索引失效的 15 种场景!

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

    背景

    无论你是技术大佬,还是刚入行的小白,时不时都会踩到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;

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

    • id:为数据库主键;

    • union_idx:为id_no、username、age构成的联合索引;

    • create_time_idx:是由create_time构成的普通索引;

    初始化数据

    初始化数据分两部分:基础数据和批量导入数据。

    基础数据insert了4条数据,其中第4条数据的创建时间为未来的时间,用于后续特殊场景的验证:

    INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1001', 'Tom1', 11, '2022-02-27 09:04:23');
    INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1002', 'Tom2', 12, '2022-02-26 09:04:23');
    INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1003', 'Tom3', 13, '2022-02-25 09:04:23');
    INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1004', 'Tom4', 14, '2023-02-25 09:04:23');

    除了基础数据,还有一条存储过程及其调用的SQL,方便批量插入数据,用来验证数据比较多的场景:

    -- 删除历史存储过程
    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;

    执行结果:

    ac42bf79b9d6afc5369025ac4ed7dc81.png
    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结果:

    9e169a370ac16be7a57e35b9c5dc99e8.png
    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结果:

    6214ed9504c73311011ee281eedbd4f1.png
    explain-02

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

    示例三

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

    explain结果:

    2c67176ce56f4f0c859fafec290063e6.png
    explain-03

    走了union_idx索引,但跟示例一一样,只用到了id_no列。

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

    反向示例

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

    explain结果:

    c35706a3bb64b3783bed643706819bc0.png
    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结果:

    0f367cb22fc61c4b389b44561d21c40a.png
    覆盖索引

    无论查询条件是username还是age,都走了索引,根据key_len可以看出使用了索引的所有列。

    第二种索引失效场景:在联合索引下,尽量使用明确的查询列来趋向于走覆盖索引

    这一条不走索引的情况属于优化项,如果业务场景满足,则进来促使SQL语句走索引。至于阿里巴巴开发手册中的规范,只不过是两者撞到一起了,规范本身并不是为这条索引规则而定的。

    3 索引列参与运算

    直接来看示例:

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

    explain结果:

    fefdabaf939363e2b386701fc7ebba80.png
    索引列计算

    可以看到,即便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结果:

    3fe87f61c2dd0322100fd803d2aadc55.png
    索引-函数

    上述示例中,索引列使用了函数(SUBSTR,字符串截取),导致索引失效。

    此时,索引失效的原因与第三种情况一样,都是因为数据库要先进行全表扫描,获得数据之后再进行截取、计算,导致索引索引失效。同时,还伴随着性能问题。

    示例中只列举了SUBSTR函数,像CONCAT等类似的函数,也都会出现类似的情况。解决方案可参考第三种场景,可考虑先通过内存计算或其他方式减少数据库来进行内容的处理。

    第四种索引失效情况:索引列参与了函数处理,会导致全表扫描,索引失效

    5 错误的Like使用

    示例:

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

    explain结果:

    87cc2e1361b280711fd7b1db5f185082.png
    索引-like

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

    • 方式一:like '%abc';

    • 方式二:like 'abc%';

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

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

    第五种索引失效情况:模糊查询时(like语句),模糊匹配的占位符位于条件的首部

    6 类型隐式转换

    示例:

    explain select * from t_user where id_no = 1002;

    explain结果:

    30eadbfc42142a633719372e4c1210b7.png
    隐式转换

    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结果:

    6832b2bdd2fd62f01c6f8dfa271434c1.png
    or-索引

    看到上述执行结果是否是很惊奇啊,明明id字段是有索引的,由于使用or关键字,索引竟然失效了。

    其实,换一个角度来想,如果单独使用username字段作为条件很显然是全表扫描,既然已经进行了全表扫描了,前面id的条件再走一次索引反而是浪费了。所以,在使用or关键字时,切记两个条件都要添加索引,否则会导致索引失效。

    但如果or两边同时使用“>”和“<”,则索引也会失效:

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

    explain结果:

    780d2e8ae6afc4c5b9ef8a62b7565a07.png
    or-范围

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

    8 两列做比较

    如果两个列数据都有索引,但在查询条件中对两列数据进行了对比操作,则会导致索引失效。

    这里举个不恰当的示例,比如age小于id这样的两列(真实场景可能是两列同维度的数据比较,这里迁就现有表结构):

    explain select * from t_user where id > age;

    explain结果:

    adc94d52b4e45e41549d0309dba6620d.png
    索引-两列比较

    这里虽然id有索引,age也可以创建索引,但当两列做比较时,索引还是会失效的。

    第八种索引失效情况:两列数据做比较,即便两列都创建了索引,索引也会失效

    9 不等于比较

    示例:

    explain select * from t_user where id_no <> '1002';

    explain结果:

    d8c1534f67e7631baf93e645751120f9.png
    索引-不等

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

    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结果:

    e670b002d43754ab3cd0de1574064c5b.png
    不等-ID

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

    10 is not null

    示例:

    explain select * from t_user where id_no is not null;

    explain结果:

    1d726c1f35ff33247af3829e97f39912.png
    索引-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结果:

    3cabecf30483ecb5d9a8aaa0fd73f81c.png
    索引-not in

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

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

    explain结果:

    ae2bc04fa1220d4cc01fe09270da7dfc.png
    主键-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结果:

    cb05a37d936165cd83959674984c239e.png
    索引-not in

    当查询条件使用not exists时,不走索引。

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

    12 order by导致索引失效

    示例:

    explain select * from t_user order by id_no ;

    explain结果:

    cafcdb284acd552ddf7cafdfcca9579d.png
    索引-order by

    其实这种情况的索引失效很容易理解,毕竟需要对全表数据进行排序处理。

    那么,添加删limit关键字是否就走索引了呢?

    explain select * from t_user order by id_no limit 10;

    explain结果:

    92b669b0b35c82dee15b3038a3d5b5fe.png
    order by limit

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

    这里还有一个特例,就是主键使用order by时,可以正常走索引。

    explain select * from t_user order by id desc;

    explain结果:

    80933d7f0902297441f7460c50e3e2dd.png
    主键-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结果:

    4191be4e4f9d095fe88e7ecea7234a07.png
    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结果:

    74d5328543b4458704653747ff1e301c.png
    索引-参数

    可以看到,正常走索引。

    随后,我们将查询条件的参数换个日期:

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

    explain结果:

    2ce8be7ea28e2a177149b65905a7daf6.png
    索引-参数

    此时,进行了全表扫描。这也是最开始提到的奇怪的现象。

    为什么同样的查询语句,只是查询的参数值不同,却会出现一个走索引,一个不走索引的情况呢?

    答案很简单:上述索引失效是因为DBMS发现全表扫描比走索引效率更高,因此就放弃了走索引

    也就是说,当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时,优化器可能会放弃走索引,自动变成全表扫描。某些场景下即便强制SQL语句走索引,也同样会失效。

    类似的问题,在进行范围查询(比如>、< 、>=、<=、in等条件)时往往会出现上述情况,而上面提到的临界值根据场景不同也会有所不同。

    第十四种索引失效情况:当查询条件为大于等于、in等范围查询时,根据查询结果占全表数据比例的不同,优化器有可能会放弃索引,进行全表扫描。

    14 其他

    当然,还有其他一些是否走索引的规则,这与索引的类型是B-tree索引还是位图索引也有关系,就不再详细展开。

    这里要说的其他,可以总结为第十五种索引失效的情况:Mysql优化器的其他优化策略,比如优化器认为在某些情况下,全表扫描比走索引快,则它就会放弃索引。

    针对这种情况,一般不用过多理会,当发现问题时再定点排查即可。

    小结

    本篇文章为大家总结了15个常见的索引失效的场景,由于不同的Mysql版本,索引失效策略也有所不同。大多数索引失效情况都是明确的,有少部分索引失效会因Mysql的版本不同而有所不同。

    因此,建议收藏本文,当在实践的过程中进行对照,如果没办法准确把握,则可直接执行explain进行验证。

    2750eb546564564c5319e10ab6065c45.gif

    往期推荐

    746a1c08ce6372ed1415dd4da092c1c6.png

    为什么wait和notify必须放在synchronized中?


    acbb9f656e0303239493b5a95153a9a8.png

    颜值爆表!Redis 官方可视化工具来啦,功能真心强大!


    56565180d7efb639cb55439bcd29a55a.png

    聊聊并发编程的10个坑


    fd02ba5caa6ab007e06beda6776a777e.gif

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

    2022-07-05 14:16:24
    1.1:索引的概述 MySQL官方对索引的定义:索引(index)是帮助MySQL高效获取数据的数据结构(有效),在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就...
  • MySQL索引失效的几种场景详解; 工作常用,面试必问类型,在这里总结下,头两次面试没问,这次必问,背不背随你;
  • mysql索引失效口诀

    2022-04-24 11:39:48
    空:NUll 索引不存储空值,如果不限制索引not null,也会让索引失效 运:对索引进行加减乘除等运算 最:最左原则,在复合索引中,abc 只用了bc是不生效的,而且顺序最好也保持一样最好。 快:全表扫描更快,就...
  • 对于该语句的可行性以及性能效率方面有至关重要的影响,本篇剖析索引为何失效,有哪些情况会导致索引失效以及对于索引失效时的优化解决方案,其中着重介绍最左前缀匹配原则、MySQL逻辑架构和优化器、索引失效场景...
  • mysql索引失效的几种情况。遵循最左前缀原则、字符匹配原则、不为不等原则、和其他情况
  • 索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)    注意:要想使用or,又想让索引生效,只能将or条件中的每个...
  • mysql索引失效的常见9种原因详解

    千次阅读 2022-07-28 16:05:30
    MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引索引提供了高效访问数据的方法,并且加快查询的速度,因此索引对查询的速度有着至关重要的影响。使用索引可以快速地定位表中的某条记录,从而提高...
  • MySQL索引失效的情况
  • MYSQL索引失效

    2022-01-24 22:49:55
    ⛽️今天的内容是 MYSQL索引失效 ⛽️ 1. MySQL 索引使用有哪些注意事项呢? 哪些情况索引会失效,索引不适合哪些场景以及索引规则 索引哪些情况会失效 like通配符可能导致索引失效。 【当我们使用左或者...
  • 索引列不独立使用了左模糊使用 or 查询部分字段没有使用索引字符串条件没有使用 ''不符合最左前缀原则的查询索引字段没有添加 not null 约束隐式转换导致索引失效索引列不独立是指被索引的这列不能是表达式的一部分...
  • mysql索引失效的几种情况分析

    千次阅读 2022-05-08 16:12:51
    文章来源: 学习通http://www.bdgxy.com/普学网http://www.boxinghulanban.cn/智...分析:该查询缺少username,查询条件复合索引最左侧username缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描 分析:
  • Mysql索引失效的几种情况总结

    千次阅读 2022-07-20 10:20:06
    总结了Mysql索引失效的几种情况,对于新手好上理解,对于老手相当于总结。
  • MySQL索引失效的11种情况

    千次阅读 2022-04-19 10:08:38
    = 或 )索引失效 IS NULL可以使用索引,IS NOT NULL 不能使用索引 LIKE 以 通配符 % 开头索引失效 页面搜索严禁左模糊或者全模糊!!! OR 前后存在非所以的列, 索引失效 数据库和表的字符集统一使用 utf8mb4 统一...
  • MySql索引失效会引起很多很难排查的系统卡顿、慢查询等问题。将项目上和网上查找到的索引失效问题总结为7中场景:索引失效的7字诀(对应7种场景)“模型数空运最快” 模:代表模糊查询,like的模糊查询以%开头,...
  • mysql索引失效的原因

    千次阅读 2022-02-10 15:26:54
    3、组合索引,使用的不是第一列索引,索引失效,即最左匹配规则。 4、数据类型出现隐式转换,如varchar不加单引号的时候可能会自动转换为int类型,这个时候索引失效。 5、在索引列上使用IS NULL或者IS NOT NULL时候...
  • 3.在索引列上做任何操作(计算、函数、自动或手动类型转换),会导致索引失效而转向全表扫描。 4.不能使用索引中范围条件右边的列。(范围条件之后的索引全失效,但是范围条件之前的和范围条件的还是用到索引的。)...
  • 为了验证 MySQL 中哪些情况下会导致索引失效,我们可以借助 explain 执行计划来分析索引失效的具体场景。explain 使用如下,只需要在查询的 SQL 前面添加上 explain 关键字即可,如下图所示:而以上查询结果的列中,...
  • 另外,无论是面试或是日常,Mysql索引失效的通常情况都应该了解和学习。 为了方便学习和记忆,这篇文件将常见的15种不走索引情况进行汇总,并以实例展示,帮助大家更好地避免踩坑。建议收藏,以备不时之需。 数据库...
  • 某些情况下,因为我们自己使用的不当,导致mysql用不到索引,这一般很容易发生在类型转换方面,也许你会说,mysql不是已经支持隐式转换了吗?比如现在有个整型的user_id索引字段,我们因为查询的时候没注意,写成了...
  • MySQL索引失效的几种情况

    千次阅读 2021-08-24 19:57:38
    MySQL索引失效的几种情况 1、全值匹配我最爱 (这种情况下索引不会失效) 全值匹配意思就是联立的复合索引的顺序和个数要和检索的条件顺序和个数相同。 2、最佳左前缀法则(重要) 最佳左前缀法则是指,如果索引了多...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 69,410
精华内容 27,764
关键字:

mysql索引失效

mysql 订阅
友情链接: 读取INI.zip