Mysql索引失效的几种情况

迈不过友情╰ 2022-10-15 13:52 318阅读 0赞

写在前面

explain命令中type中的属性，效率从上往下递减

  • system：系统表，少量数据，往往不需要进行磁盘 IO
  • const：常量连接
  • eq_ref：主键索引 (primary key) 或者非空唯一索引 (unique not null) 等值扫描
  • ref：非主键非唯一索引等值扫描
  • range：范围扫描
  • index：索引树扫描
  • ALL：全表扫描 (full table scan)

表结构：
















































Field Type Null Key Default
id int(11) NO PRI NULL
title varchar(100) NO NULL
author varchar(30) NO MUL NULL
create_time datetime NO NULL
views int(30) NO NULL

建索引：

  1. create index idx_blog_acv on blog(author,create_time,views);
  3. show index from blog;













































Table Non_unique Key_name Seq_in_index Column_name Index_type
blog 0 PRIMARY 1 id BTREE
blog 1 idx_blog_acv 1 author BTREE
blog 1 idx_blog_acv 2 create_time BTREE
blog 1 idx_blog_acv 3 views BTREE

一、最左前缀匹配法则

用到了所建立的全部索引

  1. explain select * from blog where author ="张三" and create_time="2020-05-08" and views = 10000000;





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE blog ref idx_blog_acv idx_blog_acv 101 const,const,const 1 Using index condition

由于条件中缺少第二个索引字段，所以author后的所有字段索引失效，变成全表扫描，数据量一大，效率降低

  1. explain select * from blog where author ="张三" and views = 10000000;





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE blog ref idx_blog_acv idx_blog_acv 92 const 1 Using index condition

二、在索引列上计算（函数，类型转换）会导致索引失效

未计算：

  1. explain select * from blog where author = "张三";





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE blog ref idx_blog_acv idx_blog_acv 92 const 1 Using index condition

已计算：

  1. explain select * from blog where left(author,6) = "张三";

导致全表扫描：






























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE blog ALL NULL NULL NULL NULL 3 Using where

三、索引中范围条件右边的列的索引会自动失效（但是范围条件之前的和范围条件还是用到索引的）

未使用范围条件：

  1. explain select * from blog where author="张三" and create_time = "2020-5-8" and views = 10000000;





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE blog ref idx_blog_acv idx_blog_acv 101 const,const,const 1 Using index condition

使用范围条件：

  1. explain select * from blog where author="张三" and create_time > "2020-5-7" and views = 10000000;

create_time后的索引列失效，变成全表查询






























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE blog range idx_blog_acv idx_blog_acv 97 NULL 1 Using index condition

四、索引列使用 ！= ，<>，is null，is not null会导致索引失效

!=：

  1. explain select * from blog where author != "张三";





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE blog ALL idx_blog_acv NULL NULL NULL 3 Using where

is null：

  1. explain select * from blog where author is null;





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

is not null：

  1. explain select * from blog where author is not null;





























id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE blog ALL idx_blog_acv NULL NULL NULL 3 Using where

五、LIKE以%开头会导致索引失效，使用覆盖索引解决，用or连接也会导致索引失效

六、字符串不加单引号会导致索引失效（因为mysql底层会自动将int类型转换为varchar）

发表评论

表情：
评论列表 （有 0 条评论，318人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql索引失效的几种情况

    MySQL索引在某些情况下可能会失效，导致查询性能下降。以下是一些可能导致MySQL索引失效的情况： 列类型不匹配：如果查询中使用的列与索引列的数据类型不匹配，索引可能会失效

    电玩女神电玩女神/ 0 赞/ 87 阅读