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

作者: 马小莫QAQ | 来源:发表于2022-08-02 15:30 被阅读0次

索引
联合索引的最左前缀匹配原则
索引优化笔记！
innodb联合索引
验证Mysql中联合索引的最左匹配原则
Mysql联合索引最左匹配原则
MySQL 索引——联合索引的最左匹配原则
Mysql —— 联合索引的最左匹配原则
联合索引
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会把name和user两个字段优化在最前面实现最左原则从而命中整个联合索引，学到了，接下来把这个like查询放在最后：

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

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

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

总结

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