写在前面

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

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

表结构:

FieldTypeNullKeyDefault
idint(11)NOPRINULL
titlevarchar(100)NO NULL
authorvarchar(30)NOMULNULL
create_timedatetimeNO NULL
viewsint(30)NO NULL

建索引:

create index idx_blog_acv on blog(author,create_time,views);
show index from blog;
TableNon_uniqueKey_nameSeq_in_indexColumn_nameIndex_type
blog0PRIMARY1idBTREE
blog1idx_blog_acv1authorBTREE
blog1idx_blog_acv2create_timeBTREE
blog1idx_blog_acv3viewsBTREE

一、最左前缀匹配法则

用到了所建立的全部索引

explain select * from blog where author ="张三" and create_time="2020-05-08" and views = 10000000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEblogrefidx_blog_acvidx_blog_acv101const,const,const1Using index condition

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

explain select * from blog where author ="张三" and views = 10000000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEblogrefidx_blog_acvidx_blog_acv92const1Using index condition

二、在索引列上计算(函数,类型转换)会导致索引失效

未计算:

explain select * from blog where author = "张三";
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEblogrefidx_blog_acvidx_blog_acv92const1Using index condition

已计算:

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

导致全表扫描:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEblogALLNULLNULLNULLNULL3Using where

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

未使用范围条件:

explain select * from blog where author="张三" and create_time = "2020-5-8" and views = 10000000;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEblogrefidx_blog_acvidx_blog_acv101const,const,const1Using index condition

使用范围条件:

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

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

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEblograngeidx_blog_acvidx_blog_acv97NULL1Using index condition

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

!=:

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

is null:

explain select * from blog where author is null;
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLENULLNULLNULLNULLNULLNULLNULLImpossible WHERE

is not null:

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

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

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

最后修改:2021 年 05 月 25 日 03 : 03 PM
如果觉得我的文章对你有用,请随意赞赏