MySQL最左匹配原则深入分析

目录

• 前言
• 全列匹配
• 最左前缀匹配
• 精确匹配
• 查询条件没有指定索引第一列
• 匹配某列的前缀字符串
• 范围查询
• 查询条件中含有函数或表达式

前言

接下来我们通过几种情况来描述最左匹配原则的使用。首先如下所示，为userName、phone以及userDate创建联合索引。

全列匹配

explain select * from user where userName ='admin' and phone ='13413413400' 
and userDate ='2000-04-29-12:53'

很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒,其效果是一样的。

explain select * from user where userName ='admin'  
and userDate ='2000-04-29-12:53' and phone ='13413413400'

最左前缀匹配

比如我们where条件中只有userName：

explain select * from user where userName ='admin' 

当查询条件精确匹配索引的左边连续一个或几个列时，如userName，索引可以被用

到，但是只能用到一部分，即条件所组成的最左前缀。

上面的查询从分析结果看用到了 const 索引，key_len为152，说明只用到了索引的第一列前缀。

精确匹配

查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

比如下面我们没有phone：

explain select * from user where userName ='admin' 
and userDate ='2000-04-29-12:53'

注意，上图Extra中值是Using index condition，说明MySQL正在使用覆盖索引，它只扫描索引的数据而不是按索引次序的每一行。它比按索引次序全表扫描的开销要少很多。

此时索引使用情况和情况二相同，因为phone未提供，所以查询只用到了索引的第一列，而后面的userDate虽然也在索引中，但是由于phone不存在而无法和左前缀连接，因此需要对结果进行扫描过滤userDate。

如果想让userDate也使用索引而不是where过滤，可以增加一个辅助索引<userName, userDate>，此时上面的查询会使用这个索引。

查询条件没有指定索引第一列

由于不是最左前缀，索引这样的查询显然用不到索引。

explain select * from user where userDate ='2000-04-29-12:53'

Using where：使用了用where子句来过滤结果集。这意味着MySQL服务器将在存储引擎检索行后再进行过滤。

匹配某列的前缀字符串

explain select * from user where userName ='admin' and phone like '134%' 
and  userDate ='2000-04-29-12:53'

此时可以用到索引，如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀。

范围查询

explain select * from user where userName ='admin' and phone >'134' 
and  userDate ='2000-04-29-12:53'

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

explain select * from user where userName ='admin' and phone 
between '13413413400' and '13513513500' and  userDate ='2000-04-29-12:53'

看起来是用了两个范围查询，但作用于phone上的“BETWEEN”实际上相当于“IN”，也就是说phone实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

查询条件中含有函数或表达式

如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引。

explain select * from user where userName ='admin' 
and left(phone ,6) and  userDate ='2000-04-29-12:53'

可以看到这个其实就是只用到了userName这一列的索引。

关于explain中各个关键字说明可以参考：认真学习传送门

到此这篇关于MySQL最左匹配原则深入分析的文章就介绍到这了,更多相关MySQL最左匹配内容请搜索自由互联以前的文章或继续浏览下面的相关文章希望大家以后多多支持自由互联！