避免索引失效的一些原则

声明：本文中的所有案例都在MySQL5.7版本下进行实践

索引在数据库优化上提供了很大的帮助，了解一些常见的索引失效原则，对SQL优化会有很大的帮助

复合索引：

1、首先复合索引遵循最佳左前缀匹配原则，即，如果有一个复合索引顺序为（a1,a2,a3），则在使用时，必须要使用上a1列索引，才能使用上a2列索引，如果a1列索引失效了，a2,a3都会失效。复合索引不要跨列使用或者无序使用

2、复合索引，尽量使用全索引匹配，即，如果建立了（a1,a2,a3）复合索引，则尽量将a1,a2,a3三列都使用上

复合索引建立的小技巧

准备数据，创建一张book表，字段如下所示

create table book
(
	bid int(4) primary key,
	name varchar(20) not null,
	authorid int(4) not null,
	publicid int(4) not null,
	typeid int(4) not null 
);

执行查询语句：查询authorid=1且typeid为2或3的 bid字段，按照typeid降序，SQL语句explain如下

 

下面我们对这条SQL语句进行优化，加上索引。我们观察到，SQL中使用到了bid，typeid，authorid列，所以我们建立一个复合索引，包含着三列，但是复合索引的顺序应该是怎么样的呢？可以这样吗（bid，typeid，authorid）？ 这样是错的，在SQL语句中虽然编写的时候bid在最前面，但是实际解析的时候，应该是先解析where子句，然后在解析select语句的，所以索引的顺序应该是（typeid，authorid，bid），建立好索引我们再次执行上面的SQL。发现使用到了索引，并且长度为12

alter table book add index idx_tab (typeid,authorid,bid);

那么现在就是最优的了吗？不一定，在where子句中，typeid字段使用到了in关键字，而这个关键字可能会让索引失效，一旦typeid列的索引失效了，等于没有使用上索引（参照复合索引的最佳左前缀原则） 

所以我们可以这样子，将typeid字段和authorid字段换一下位置，将原来的复合索引删掉，创建新顺序的复合索引（authorid，typeid，bid），这样子，即使typeid列的索引失效了，我们也可以使用到authorid列的索引，因为现在是authorid列在前面

小结：

最佳左前缀，保持索引的定义和使用的顺序一致性

索引需要逐步优化

将含in的范围查询 放到where条件的最后，防止失效

 

不要在索引列上做任何操作（计算、函数、类型转换等）

现在我们在book表中，只有（typeid,authorid,bid）顺序的复合索引

执行下面的SQL，由于authorid字段使用了乘法，所以只使用了typeid列的索引，故key_len=4

 执行以下SQL，由于在typeid字段上使用了乘法，所以复合索引直接全部失效（最佳左前缀），故key的值为null

 

不要在索引列上使用不等于或者is null，否则索引失效

现在我们在book表中，只有（typeid,authorid,bid）顺序的复合索引

执行以下SQL，因为我们在typeid列上使用了！=，导致复合索引失效

 

尽量不要使用类型转换（显示、隐式），否则索引失效

在book表的name字段建立单值索引

alter table book add index name_index(name);

执行以下SQL，name字段的类型是varchar，而我们却写name=123，让name进行了一次类型转换，所以索引失效。

 

尽量不要使用or，否则索引失效

在book表中现在有三个索引，name列的单值索引，bid列的主键索引，（typeid，authorid，bid）复合索引

执行以下SQL，我们使用了or关键字，导致索引全部失效

 

我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因  会导致该结论不是100%正确。