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最左匹配内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL数据库索引的最左匹配原则

    目录 一. 联合索引说明 二. 那ac是否能用到索引呢? 三. 思考 四. 最左匹配原则的成因 一. 联合索引说明 建立三个字段的联合索引 联合索引(a,b,c)相当于建立了索引:(a),(a,b),(a,b,c) 二. 那ac是否能用到索引呢? 先给出结论:a可以命中联合索引(a,b,c),c无法命中,所以ac组合无法命中联合索引. 1.建立abc联合索引(province,city,district) ac索引查询 SELECT * FROM user_address WHERE provi

  • MySQL组合索引与最左匹配原则详解

    前言 之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章,自以为就了解了其原理,最近面试时和面试官交流,发现遗漏了些东西,这里自己整理一下这方面的内容. 什么时候创建组合索引? 当我们的where查询存在多个条件查询的时候,我们需要对查询的列创建组合索引 为什么不对没一列创建索引 减少开销 覆盖索引 效率高 减少开销:假如对col1.col2.col3创建组合索引,相当于创建了(col1).(col1,col2).(col1,col2,col3)3个索引 覆盖索引:假如查询SELECT

  • 验证Mysql中联合索引的最左匹配原则详情

    目录 前言 如何验证联合索引的有效性 多个单一索引进行验证 联合索引 总结 前言 后端面试中一定是必问mysql的,在以往的面试中好几个面试官都反馈我Mysql基础不行,今天来着重复习一下自己的弱点知识.在Mysql调优中索引优化又是非常重要的方法,不管公司的大小只要后端项目中用到了mysql,几乎都会遇到Mysql查询需要优化的需求.经常有时候前端业务没有压力,经常会在管理后台逻辑中遇到mysql统计查询压力,可能是代码写太烂了,哈哈.在日常工作中我遇到过同事建立索引后问我某个查询条件是否能命

  • 深入浅析Mysql联合索引最左匹配原则

    前言 之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章,自以为就了解了其原理,最近面试时和面试官交流,发现遗漏了些东西,这里自己整理一下这方面的内容. 最左前缀匹配原则 在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例: 对列col1.列col2和列col3建一个联合索引 KEY test_col1_col2_col3 on test(col1,col2,col3); 联合索引 test_col1_col2_col3 实际建

  • MySQL索引最左匹配原则实例详解

    目录 简介 准备 理论详解 聚集索引和非聚集索引 回表查询 索引覆盖 最左匹配原则 详细规则 补充:为什么要使用联合索引 总结 简介 这篇文章的初衷是很多文章都告诉你最左匹配原则,却没有告诉你,实际场景下它到底是如何工作的,本文就是为了阐述清这个问题. 准备 为了方面后续的说明,我们首先建立一个如下的表(MySQL5.7),表中共有5个字段(a.b.c.d.e),其中a为主键,有一个由b,c,d组成的联合索引,存储引擎为InnoDB,插入三条测试数据.强烈建议自己在MySQL中尝试本文的所有语句

  • MySQL最左匹配原则深入分析

    目录 前言 全列匹配 最左前缀匹配 精确匹配 查询条件没有指定索引第一列 匹配某列的前缀字符串 范围查询 查询条件中含有函数或表达式 前言 接下来我们通过几种情况来描述最左匹配原则的使用.首先如下所示,为userName.phone以及userDate创建联合索引. 全列匹配 explain select * from user where userName ='admin' and phone ='13413413400' and userDate ='2000-04-29-12:53' 很明

  • MySQL索引设计原则深入分析讲解

    哪些情况适合创建索引? 字段的数值有唯一性的限制 索引本身可以起到约束的作用,比如唯一索引,主键索引都是可以起到唯一性约束的,因此在我们的数据表中如果某个字段是唯一性的,就可以直接创建唯一性索引,或者主键索引.这样可以更快速地通过该索引来确定某条记录. 业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引.(来源:Alibaba) 说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的. 频繁作为 WHERE 查询条件的字段 某个字段在SELECT

  • MySQL数据类型优化原则

    MySQL支持的数据类型很多,选择正确的数据类型对于高性能至关重要.下面几个简单的原则都有助于做出更好的选择. 更小的通常更好 应该尽量使用可以正确储存数据的最小数据类型.更小的数据类型通常更快,因为它们占用更少的磁盘.内存和CPU缓存,并且处理时需要的CPU周期也更少.如果无法确定哪个数据类型时最好的,就选择你认为不会超过范围的最小类型. 简单就好 简单数据类型的操作通常需要更少的CPU周期.例如,整形比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整形更复杂.比如用MySQ内

  • 一文弄懂MySQL索引创建原则

    目录 一.适合创建索引 1.字段的数值有唯一性限制 2.频繁作为Where查询条件的字段 3.经常Groupby和Orderby的列 4.Update.Delete的where条件列 5.Distinct字段需要创建索引 6.多表Join连接操作时,创建索引注意事项 7.使用列的类型小的创建索引 8.使用字符串前缀创建索引 9.区分度高的列适合作为索引 10.使用最频繁的列放到联合索引的左侧 11.在多个字段都要创建索引的情况下,联合索引由于单值索引 二.不适合创建索引 1.在where中使用不

  • MySQL锁阻塞的深入分析

    日常维护中,经常会碰到线程被阻塞,导致数据库响应非常慢,下面就看看如何获取是哪个线程导致了阻塞的. 1. 环境说明 RHEL 6.4 x86_64 + MySQL 5.6.19 事务隔离级别:RR 2. 测试过程 3. 查看锁阻塞线程信息 这里用几中方法进行分析: 3.1  使用show processlist查看 MySQL [(none)]> show processlist; +----+------+-----------+------+---------+------+--------

随机推荐