索引优化--最佳左前缀法则

通过explain得到结果的key字段,我们可以知道SQL语句中有没有使用索引,以及使用了哪些索引。有时候索引已经建了,但key值依然是NULL,即实际没有使用到,这是索引失效的情况。

索引失效的情况我们需要避免,此时我们需要了解索引使用的一些规则。本文里了解其中的一种规则,也是比较重要的一种–最佳左前缀法则。

现在有一张保存jar包一些信息的表t_jar:

CREATE TABLE `t_jar` ( 
 `uuid` varchar(50) NOT NULL, 
`name` varchar(200) DEFAULT NULL, 
 `path` text, 
 `updateDate` datetime DEFAULT NULL, 
`type` varchar(100) DEFAULT NULL, 
 `click` int(11) DEFAULT NULL, 
`downHit` int(11) DEFAULT NULL, 
`indexState` int(11) DEFAULT '0', 
 `tagState` int(11) DEFAULT '0', 
`publishStatus` int(11) DEFAULT '0', 
 PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

show index from t_jar 查看其索引情况如下:
在这里插入图片描述目前只有主键,还没有对其他字段建索引。现在用name、indexState、tagState三个字段建一个复合索引:

alter table t_jar add index index_nit(name,indexState,tagState);

索引名称简单点index_nit,现在表索引情况如下:
在这里插入图片描述
现在有三条语句:

#第一条,条件只有name
select * from t_jar where name = 'abbot-0.12.3.jar';
#第二条 条件有name和indexState
select * from t_jar where name = 'abbot-0.12.3.jar' 
 and indexState = '0' 
#第三条 条件有name、indexState和tagState
select * from t_jar where name = 'abbot-0.12.3.jar' 
 and indexState = '0' 
 and tagState = '0'

第一条的分析情况如下:
在这里插入图片描述从图中可以知道:

1、type值是ref,以前分享过type的优劣:system > const > eq_ref > ref > range > index > All。一般而言,我们要保证查询至少达到ranag级别,最好能到达到ref。

2、possible_keys和key的值说明语句使用了索引index_nit。

3、ref值是const,即某个常量被用于查找索引列上的值。

再看第二条语句:

它的分析情况如下:
在这里插入图片描述
key_len虽然比上一条语句的要长一些,ref两个常量,结果更精准。

再看第三条语句的情况:
在这里插入图片描述
依然只是ref和key_len的值变化。

从结果看,以上三条语句的性能和索引使用情况都没有问题。

现在有以下两条SQL:

#一
select * from t_jar where indexState = 0 
 and tagState = 0 ;
 
 #二
select * from t_jar where tagState = 0 ;

两条语句情况如下:
在这里插入图片描述它们的type值都是all全表扫描,key是null即实际没有使用到任何索引,ref也没有值。

两条索引失效了。但如果有使用name字段时,索引是没有失效的,

因为复合索引建的顺序是:name、idexState、tagState。上面的SQL违背了索引的最佳左前缀法则。如果索引了多列,要遵守最佳左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列

文中例子的name字段即是最左前列,可以理解为火车头,是不能丢的。

再看以下SQL:

select * from t_jar where name = ‘spring.jar’ and tagState = 0 ;

SQL中有“火车头”name字段,还有索引中另一个字段tagState,其分析情况如下:
在这里插入图片描述索引用上了,但是ref只有一个常量,说明只使用了部分索引,那就是name,而tagState没有用到。把tagState换成indexState,看结果:
在这里插入图片描述ref两个常量,key_len也变长了,说明SQL中有的索引都用上了。

索引顺序是:name、idexState、tagState,第一条没有了中间的indexState,那么tagState也没有效果,所以说索引的中间字段不能跳过是这个意思。

总结一下最佳左前缀法则,有两点:

一、从索引的最左前列开始。

二、不跳过索引中的列。

  • 4
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值