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

目录
  • 前言
  • 如何验证联合索引的有效性
  • 多个单一索引进行验证
  • 联合索引
  • 总结

前言

后端面试中一定是必问mysql的,在以往的面试中好几个面试官都反馈我Mysql基础不行,今天来着重复习一下自己的弱点知识。在Mysql调优中索引优化又是非常重要的方法,不管公司的大小只要后端项目中用到了mysql,几乎都会遇到Mysql查询需要优化的需求。经常有时候前端业务没有压力,经常会在管理后台逻辑中遇到mysql统计查询压力,可能是代码写太烂了,哈哈。在日常工作中我遇到过同事建立索引后问我某个查询条件是否能命中索引,我只能说模糊记得最左匹配原则不能准确地告诉别人是否能命中索引,我今天就打算彻底解决这个问题。

如何验证联合索引的有效性

使用explain,在select语句之前使用explain关键字,就会返回sql语句执行计划的信息,而不是执行sql。

这里我们简单实践一下,选取一张表:

 有兴趣的同学可以拿这个sql语句生成一个一模一样的表:

CREATE TABLE `videos` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `like` int DEFAULT NULL,
  `unlike` int DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `count` int DEFAULT '0',
  `type` tinyint DEFAULT '1' COMMENT '1美女2励志',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=36247 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

这个表的内容是一些抖音的视频的视频名称,作者,保存路径,状态等等信息。
来使用explain关键字试一下执行以下sql语句:

explain select * from videos where `user` like'%BY2girl%'

展示信息: 

其中展示的详细信息根据文章主题这里不做详细说明吧,就算根据其他资料稍微理解复制过来,我也记不住。

接下来我尝试把这个user这个加一个索引试试:

这里补充说明一下,我直接新建一个B树索引,B树索引一般是默认创建的索引类型,因为相对于哈希索引B树索引可以获得稳定且较好的查询速度,哈希索引更适合适合做精确查询

看看不加索引和加索引同一个查询的explain有什么区别:

explain  select count(*) from videos where `user` like'%BY2girl%';

可以看到key关键字那一列使用到了我自己命名的user_key这个索引

多个单一索引进行验证

接下再加两个索引:

看下简单使用哪些查询会命中索引:

explain  select * from videos where `user` ='BY2' and `path` = 'BY2' and `name`  = 'BY2'

果然使用到了3个索引,但是我一直有一个疑问,在中间的查询条件使用like模糊返回查询,看看命中了哪个索引:

explain  select * from videos where `user` ='BY2' and `path` like '%BY2%' and `name`  = 'BY2'

结论Mysql会自动对sql语句进行优化,把可以命中的查询条件放在最前面让它们命中索引,用来提高查询速度。这样一个字段增加一个索引无疑增加了表的空间,给表记录的新增和修改操作增加了压力,联合索引可以稍微解决这个问题,接下来就要说联合索引。

联合索引

联合索引指的是对一张表上把多个字段当制作成一个索引:

联合最左匹配原则解释:以建立索引的字段为查询条件,执行查询时候左边的为起点任何连续的索引都能匹配上,当遇到范围查询(>、<、between、like)就会停止匹配

explain  select * from videos where `user` ='BY2' and `path` = 'BY2' and `name`  = 'BY2'

 不用说,这样一定会命中这个联合索引,接下来中间使用一个like试试:

explain  select * from videos where `user` ='BY2' and `path` like '%BY2%' and `name`  = 'BY2'

完全没有命中索引,中间被打断了,我自己以为会命中了一个user也会命中整个联合索引,我还以为mysql会把nameuser两个字段优化在最前面实现最左原则从而命中整个联合索引,学到了,接下来把这个like查询放在最后:

explain  select * from videos where `user` ='BY2' and `path` = 'BY2' and `name`  like '%BY2%'

看来是命中了这个联合索引,两个索引的命中直接命中了整个联合索引,验证成功。

在其中侧面了解到,我设置索引的顺序和最左匹配原则的顺序不是一一匹配的,userpath这两个字段可能会优化顺序。但是我设置的联合索引的顺序是pathnameuser,其中userpath中间有一个name字段的索引,最左匹配原则是依据查询条件来的,跟where 条件顺序相关!

总结

在日常工作中发现阿里云的云数据库会根据数据库热点查询数据自动增加索引,又减轻了某些不会建立索引的人的压力或者减少了建立错误索引的情况,同时自动减少了数据库压力,哈哈。 索引是mysql非常复杂的知识,它又非常重要,后面遇到问题一定要记录下来,亲自实践增加印象,感觉在今天的验证过程中略过了好多复杂的知识,例如一些explain信息的意思,很重要,等到后面遇到了再仔细研究,今天就到这。

到此这篇关于验证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聚集索引、辅助索引、覆盖索引、联合索引的使用

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

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

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

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

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

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

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

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

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

  • mysql的联合索引(复合索引)的实现

    联合索引 本文中联合索引的定义为(MySQL): ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`); 联合索引的优点 若多个一条SQL,需要多个用到两个条件 SELECT * FROM `user_info` WHERE username='XX',password='XXXXXX'; 当索引在检索 password字段的时候,数据量大大缩小,索引的命中率减小,增大了索引的效率. 符合索引的索引体积比单独索引的体积要小,而且只是一

  • 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中字符串索引对update的影响分析

    本文分析了MySQL中字符串索引对update的影响.分享给大家供大家参考,具体如下: 对某一个类型为varchar的字段添加前缀索引后,基于该子段的条件查询时间基本大幅下降:但对于update操作,所耗的时间却急剧上升,主要原因是在更新数据的同时,mysql会执行索引的更新. 下面做了一个简单的试验. (1)首先对某个亿级记录的表字段所有记录执行更新: for idx in range(1, count+1): sql = "update tbl_name set platforms='&qu

  • MySQL入门(五) MySQL中的索引详讲

    序言 之前写到MySQL对表的增删改查(查询最为重要)后,就感觉MySQL就差不多学完了,没有想继续学下去的心态了,原因可能是由于别人的影响,觉得对于MySQL来说,知道了一些复杂的查询,就够了,但是我认为,不管有没有用,现在学着不懂的东西,说明就是自己薄弱的地方,多学才能比别人更强 --WH 一.什么是索引?为什么要建立索引? 索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有

  • MySQL中对于索引的基本增删查改操作总结

    创建索引 MySQL创建索引的语法如下: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name,...) 其中对应的语法变量信息如下: [UNIQUE|FULLTEXT|SPATIAL] 中括号中的这三个关键字表示创建的索引类型,它们分别表示唯一索引.全文索引.空间索引三种不同的索引类型.如果我们不指定任何关键字,则默认为普通索引. index_name

  • MySQL中的主键自增机制详情

    目录 主键自增 自增主键保存在哪里 自增值修改机制 自增值的修改时机 如何修改自增主键值 主键自增 MySQL 提供了主键自增机制 AUTO_INCREMENT. 对主键使用, 保证了主键的唯一性. 注意:自增长必须与主键字段配合使用. 默认的主键的起始值为 1, 每次增量为 1, 也可以手动指定其自增起始值 auto_increment_offset 和自增步长 auto_increment_increment. -- 设置主键自增 CREATE TABLE USER( id INT UNSI

  • MySql中的json_extract函数处理json字段详情

    目录 前言 1. 使用方式 2. 使用演示 3. 小结 前言 在db中存储json格式的数据,相信大家都或多或少的使用过,那么在查询这个json结构中的数据时,有什么好的方法么?取出String之后再代码中进行解析? 接下来本文将介绍一下Mysql5.7+之后提供的json_extract函数,可以通过key查询value值 1. 使用方式 数据存储的数据是json字符串,类型为我们常用的varchar即可 语法: JSON_EXTRACT(json_doc, path[, path] -) 若

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

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

  • Mysql中的索引精讲

    前言 开门见山,直接上图,下面的思维导图即是现在要讲的内容,可以先有个印象- 常见索引类型(实现层面) 索引种类(应用层面) 聚簇索引与非聚簇索引 覆盖索引 最佳索引使用策略 1.常见索引类型(实现层面) 首先不谈Mysql怎么实现索引的,先马后炮一下,如果让我们来设计数据库的索引,该怎么设计? 我们首先思考一下索引到底想达到什么效果?其实就是想能够实现快速查找数据的策略,所以索引的实现本质上就是一个查找算法. 但是跟普通的查找有所不同,因为我们的数据有一下特征: 1.存储的数据是非常非常多的

随机推荐