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

通过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也没有效果，所以说索引的中间字段不能跳过是这个意思。

总结一下最佳左前缀法则，有两点：

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

二、不跳过索引中的列。