MySQL联合索引遵循最左前缀匹配原则

面试官： 我看你的简历上写着精通MySQL，问你个简单的问题，MySQL联合索引有什么特性？

心想，这还不简单，这不是问到我手心里了吗？

听我给你背一遍八股文！

我： MySQL联合索引遵循最左前缀匹配原则，即最左优先，查询的时候会优先匹配最左边的索引。

例如当我们在 (a,b,c) 三个字段上创建联合索引时，实际上是创建了三个索引，分别是(a)、(a,b)、(a,b,c)。

查询条件中包含这些索引的时候，查询就会用到索引。例如下面的查询条件，就可以用到索引：

select * from table_name where a=?;
select * from table_name where a=? and b=?;
select * from table_name where a=? and b=? and c=?;

其他查询条件不包含这些索引的语句，就不会用到索引，例如：

select * from table_name where b=?;
select * from table_name where c=?;
select * from table_name where b=? and c=?;

如果查询条件包含(a,c)，也会用到索引，相当于用到了(a)索引。

面试官： 小伙子，你的八股文背的挺熟啊。

我： 也没有辣，我只是平常热爱学习知识，经常做一些总结汇总，所以就脱口而出了。

面试官： 别开染坊了，我再问你，MySQL联合索引一定遵循最左前缀匹配原则吗？

我擦，这把我问的不自信了。

我： 嗯，MySQL联合索引可能有时候不遵循最左前缀匹配原则。

面试官： 什么时候遵循？什么时候不遵循？

我： 可能是晴天遵循，下雨了就不遵循了，每个月那几天不舒服的时候也不遵循了……

面试官： 今天面试就到这吧，你先回去等通知，有后续消息会联系你的。

我擦，这叫什么问题啊？

什么遵循不遵循？

难道是面试官跟我背的八股文不是同一套？

回去到MySQL官网上翻了一下，才发现面试官想问的是索引跳跃扫描（Index Skip Scan） 。

MySQL8.0版本开始增加了索引跳跃扫描的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。

造点数据验证一下，先创建一张用户表：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `gender` tinyint NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用户表';

在性别和姓名两个字段上(gender,name)建立联合索引，性别字段只有两个枚举值。

执行SQL查询验证一下：

explain select * from user where name='一灯';

虽然SQL查询条件只有name字段，但是从执行计划中看到依然是用了联合索引。

并且Extra列中显示增加了Using index for skip scan，表示用到了索引跳跃扫描的优化逻辑。

具体优化方式，就是匹配的时候遇到第一列索引就跳过，直接匹配第二列索引的值，这样就可以用到联合索引了。

其实我们优化一下SQL，把第一列的所有枚举值加到where条件中，也可以用到联合索引：

select * from user where gender in (0,1) and name='一灯';

看来还是需要经常更新自己的知识体系，一不留神就out了！

到此这篇关于MySQL联合索引遵循最左前缀匹配原则的文章就介绍到这了,更多相关MySQL联合索引遵内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL全文索引、联合索引、like查询、json查询速度哪个快

    查询背景 有一个表tmp_test_course大概有10万条记录,然后有个json字段叫outline,存了一对多关系(保存了多个编码,例如jy1577683381775) 我们需要在这10万条数据中检索特定类型的数据,目标总数据量:2931条 SELECT COUNT(*) FROM tmp_test_course WHERE `type`=5 AND del=2 AND is_leaf=1 我们在限定为上面类型的同时,还得包含下面任意一个编码(也就是OR查询) jy157768338177

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

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

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

  • mysql联合索引的使用规则

    mysql联合索引的使用规则

    联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 从一道有趣的题目开始分析: 假设某个表有一个联合索引(c1,c2,c3,c4)以下选项哪些字段使用了该索引: A where c1=x and c2=x and c4>x and c3=x B w

  • mysql聚集索引、辅助索引、覆盖索引、联合索引的使用

    mysql聚集索引、辅助索引、覆盖索引、联合索引的使用

    目录 聚集索引(Clustered Index) 辅助索引(Secondary Index) 覆盖索引(Covering index) 联合索引 <MySQL技术内幕 InnoDB存储引擎>学习笔记 聚集索引(Clustered Index) 聚集索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据. 举个例子,直观感受下聚集索引. 创建表t,并以人为的方式让每个页只能存放两个行记录(不清楚怎么人为控制每页只存放两个行记录): 最后<MySQL技术内幕>

  • mysql 联合索引生效的条件及索引失效的条件

    mysql 联合索引生效的条件及索引失效的条件

    目录 1.联合索引失效的条件 2.索引失效的条件 1.联合索引失效的条件 联合索引又叫复合索引.两个或更多个列上的索引被称作复合索引. 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单

  • Mysql InnoDB聚簇索引二级索引联合索引特点

    Mysql InnoDB聚簇索引二级索引联合索引特点

    目录 一.聚簇索引 特点 1 特点 2 二.二级索引 三.联合索引 接上一篇内容:https://www.jb51.net/article/249934.htm 一.聚簇索引 其实之前内容中介绍的 B+ 树就是聚簇索引. 这种索引不需要我们显示地使用 INDEX 语句去创建,InnoDB 引擎会自动创建.另外,在 InnoDB 引擎中,聚簇索引就是数据的存储方式. 它有 2 个特点: 特点 1 使用记录主键值的大小进行记录和页的排序. 其中又包含了下面 3 个点: 页(包括叶节点和内节点)内的记

  • 如何利用MySQL添加联合唯一索引

    如何利用MySQL添加联合唯一索引

    目录 联合唯一索引 扩展延伸: 附:mysql中如何用命令创建联合索引 总结 联合唯一索引 项目中需要用到联合唯一索引: 例如:有以下需求:每个人每一天只有可能产生一条记录:处了程序约定之外,数据库本身也可以设定: 例如:t_aa 表中有aa,bb两个字段,如果不希望有2条一模一样的记录(即:aa字段的值可以重复: bb字段的值也可以重复,但是一条记录(aa,bb)组合值不允许重复),需要给 t_aa 表添加多个字段的联合唯一索引: alter table t_aa add unique ind

  • MySQL 独立索引和联合索引的选择

    MySQL 独立索引和联合索引的选择

    通常会对多列索引缺乏理解,常见的错误是将很多列设置独立索引,或者是索引列使用错误的次序.我们在下一篇讨论索引列次序的问题,首先看一下多列独立索引的情况,以下面的表结构为例: CREATE TABLE test ( c1 INT, c2 INT, c3 INT, KEY(c1), KEY(c2), KEY(c3), ); 使用这种索引策略通常是一些权威的建议(例如在WHERE条件中用到的条件列增加索引)的结果.事实上,这是大错特错的,要评分的话顶多给1颗星.这种方式的索引与真正优化的索引相比,要慢

  • MySQL联合索引遵循最左前缀匹配原则

    MySQL联合索引遵循最左前缀匹配原则

    面试官: 我看你的简历上写着精通MySQL,问你个简单的问题,MySQL联合索引有什么特性? 心想,这还不简单,这不是问到我手心里了吗? 听我给你背一遍八股文! 我: MySQL联合索引遵循最左前缀匹配原则,即最左优先,查询的时候会优先匹配最左边的索引. 例如当我们在 (a,b,c) 三个字段上创建联合索引时,实际上是创建了三个索引,分别是(a).(a,b).(a,b,c). 查询条件中包含这些索引的时候,查询就会用到索引.例如下面的查询条件,就可以用到索引: select * from tab

  • 总结三道MySQL联合索引面试题

    总结三道MySQL联合索引面试题

    目录 前言: 众所周知MySQL联合索引遵循最左前缀匹配原则,在少数情况下也会不遵循(有兴趣,可以翻一下上篇文章) 创建联合索引的时候,建议优先把区分度高的字段放在第一列. 至于怎么统计区分度,可以按照下面这种方式. 创建一张测试表,用来测试: CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主键', `a` int NOT NULL, `b` int NOT NULL, `c` int NOT NULL, PRIM

  • 图文详解Mysql索引的最左前缀原则

    图文详解Mysql索引的最左前缀原则

    目录 前言 1. 定义 2. 全索引顺序 3. 部分索引顺序 3.1 正序 3.2 乱序 4. 模糊索引 5. 范围索引 总结 前言 之所以有这个最左前缀索引 归根结底是mysql的数据库结构 B+树 在实际问题中 比如 索引index (a,b,c)有三个字段, 使用查询语句select * from table where c = '1' ,sql语句不会走index索引的 select * from table where b =‘1’ and c ='2' 这个语句也不会走index索引

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

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

  • MySQL联合索引功能与用法实例分析

    MySQL联合索引功能与用法实例分析

    本文实例讲述了MySQL联合索引功能与用法.分享给大家供大家参考,具体如下: 联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用

  • MySQL联合索引用法示例

    MySQL联合索引用法示例

    本文实例讲述了MySQL联合索引.分享给大家供大家参考,具体如下: 员工表 userid 部门表 deptid 员工部门表 条件:一个员工可以对应多个部门 问题:怎么样设置数据库,让其不能重复添加 userid 和deptid? uuid userid deptid 111 212 311(这个就不能让其添加) DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dep

  • MySQL 联合索引与Where子句的优化 提高数据库运行效率

    网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了.涉及数据库操作的基本都是变得很慢了,用的人都会觉得躁火~~然后把这个情况在群里一贴,包括机器配置什么的一说,马上就有群友发话了,而且帮我确定了不是机器配置的问题,"深圳-枪手"热心人他的机器512内存过百W的数据里也跑得飞快,甚至跟那些几W块的机器一样牛(吹过头了),呵呵~~~ 在群友的分析指点下,尝试把排序.条件等一个一个去除来做测试,结果发现问题就出在排序部分,去除排序的时候,执行时间由原来的48秒变成0.3x秒,这是

  • MySQL中索引的定义以及操作新手教程

    目录 索引的定义 索引的类型 普通.唯一.主键和全文索引 普通索引(INDEX) 唯一索引(UNIQUE INDEX) 主键索引(PRIMARY KEY) 全文索引(FULLTEXT) 单列索引和组合索引 聚集索引和非聚集索引 索引的创建原则 索引操作 创建索引 查看索引 删除索引 总结 索引的定义 数据库中的索引就像一本书的目录,可以据此快速定位数据库中相关数据的所在位置. 在数据库中,索引被定义为一种特殊的数据结构,由数据库中的一列或多列组合而成,可以用来快速查询数据表中某一特定值的记录.

随机推荐