MYSQL索引失效整理一

在mysql5.7中，一般为了查询速度，通常会引入索引，但是有时候我们的索引并没有引起效果，所以整理下一般在什么情况下会引起索引失效，即索引没有被使用。

一.发生隐式类型转换

现在我们有一个名为t_test的数据表，他的字段与数据存储类型如下所示，并且在字段phone字段建立索引：

名称	类型	长度
id	bigint	0
name	varchar	10
phone	varchar	20
age	int	0
adreess	varchar	50

 

 

 

 

 

 

 

我们查询phone字段为具体数值的查询，可以为：

select * from t_test where phone =13555455506

可以正常得到我们想要的结果，但是由于phone为varchar字符类型，而不是长整型，所以我们查看下这个sql的执行计划：

explain select * from t_test where phone =13555455506

并且可以得到以下计划：

id	select_type	table	partitions	type	possible_keys	key	key_length	ref	rows	filtered	Extra
1	SIMPLE	t_test		ALL	index_phone				1	100	Using where

 

 

 

我们可以通过上述列表看到possible_keys为执行时预计可能用到的索引为phone上的索引，key为实际用到的索引为空，则表示实际查询不会用到索引，则是因为MySQL会将我们传入的参数隐式转换为数据表中存储的类型，索引时根据Btree的有序性进行遍历的，但是转换之后的参数破环了有序性的规则。

所以我们要将传入参数加上引号，以表示此参数为字符串类型，如下：

select * from t_test where phone ='13555455506'

二.表达式计算

还是使用上面的表，我们在age字段上加入索引，现在我们有一个查询，要求查询年龄age在两年前为18的数据，则查询语句如下：

select * from t_test where age -2=18

可以正常得到我们想要的结果，但是由于我们在age查询的时候给他赋予了表达式，所以我们查看下这个sql的执行计划：

explain select * from t_test where age -2=18

并且可以得到以下计划：

id	select_type	table	partitions	type	possible_keys	key	key_length	ref	rows	filtered	Extra
1	SIMPLE	t_test		ALL					1	100	Using where

 

 

 

我们可以清楚看到，索引并没有使用，那么我们应该如何修改以达到要求呢，我们只需要把计算挪到等号右侧即可：

 select * from t_test where age =18+2

原因与第一个类似。数据库在计算时无法保证与索引树一致，只好不用索引。

三.使用函数

现在我们有这样一个查询，查询出手机号码前三位为158的数据，则查询语句如下：

select * from t_test where LEFT(phone,3)='158'

可以正常得到我们想要的结果，但是由于我们使用了函数，所以我们查看下这个sql的执行计划，语句省略，直接看结果：

id	select_type	table	partitions	type	possible_keys	key	key_length	ref	rows	filtered	Extra
1	SIMPLE	t_test		ALL					1	100	Using where

 

 

 

我们可以修改为如下语句：

select * from t_test where phone like '158%'

原因同上面两个类似，这正是用到了我们msyql的索引最左原则进行匹配。

四.左或者左右模糊匹配

现在我们有这样一个查询，查询出地址包含天安门的数据，则查询语句如下：

select  *  from t_test where adreess like '%天安门%'

可以正常得到我们想要的结果，但是由于我们使用了左右模糊匹配，则可以查看sql的执行计划如下，查看结果：

id	select_type	table	partitions	type	possible_keys	key	key_length	ref	rows	filtered	Extra
1	SIMPLE	t_test		ALL					1	100	Using where

 

 

 

这里面也是由于不符合最左前缀匹配原则。如果我们只查询主键id与该索引字段，仍可使用索引，查询如下：

select  id,adreess  from t_test where adreess like '%天安门%'

由于不符合最左前缀，即使使用索引树，查询只能跟随Btree数依次遍历。

五.联合索引

联合索引我们要依据最左前缀原则，即最左字段，最左数据

如果我们删除age与phone上的单列索引，新增（age,phone）联合索引，则有以下规律

select * from t_test where age =20 //使用了该联合索引

select * from t_test where phone like '131%' //没有使用该联合索引

select * from t_test where age =20 and  phone like '131%'//使用了该联合索引