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

前言

后端面试必问MySQL。
在之前的面试中，有几个面试官反映我不擅长MySQL基础知识。
今天我又把我的薄弱技能再练一遍。
索引优化是MySQL优化中非常重要的方法。
不管公司规模有多大，只要后端项目使用了MySQL，几乎总是需要优化MySQL查询。
有时候前端业务没有压力，但我在管理后端逻辑中经常遇到MySQL统计查询的压力。
可能是代码写得太差了，哈哈。
在日常工作中，遇到同事问我设置了索引后，某个查询条件能否命中索引。
我只能说，我依稀记得最左匹配原理，也无法准确告诉别人是否能命中索引。
我今天打算彻底解决这个问题。

如何检查共享索引的有效性

使用EXPLAIN并且在SELECT语句之前使用EXPLAIN关键字时，会返回SQL语句的执行计划信息，而不是执行SQL。

这里我们简单练习一下，选择一个表：

有兴趣的同学可以用这个SQL语句生成一个一模一样的表：

CREATETABLE`视频`(`id`intunsignedNOTNULLAUTO_INCRMENT，`路径`varchar(255)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciDEFAULTNULL,`name`varchar(255)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciDEFAULTNULL,`用户`varchar(50)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciDEFAULTNULL,`like`intDEFAULTNULL,`其他as`intDEFAULTNULL,`status`tinyint(1)DEFAULTNULL,`count`intDEFAULT'0',`type`tinyintDEFAULT'1'COMMENT'1美2励志',PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBAUTO_INCRMENT=36247DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_general_ci;

该表的内容是部分抖音视频的视频名称、作者、存储路径、状态等信息。

我们尝试使用EXPLAIN关键字执行以下SQL语句：

explainselect*fromvideoswhere`user`like'%BY2girl%'信息显示：

由于文章主题原因，这里不详细解释显示的详细信息，虽然给别人的信息很容易理解。
即使我复制了，我也记不住。

接下来我将尝试为此用户添加索引：

这里我会做一些额外的解释它直接创建一个新的B树索引。
B树索引一般是默认的索引类型，因为B树索引相比哈希索引能够实现更稳定、更好的查询速度，并且哈希索引更适合精确查询

不建立索引查看相同查询的结果和与索引上有什么区别lain:

解释selectcount(*)fromvideoswhere`用户`喜欢'%BY2girl%';

可以看到keyword列使用了我自己命名的user_key索引。

多个单独的索引进行验证

接下来，再添加两个索引：

让我们看看哪些简单的查询命中了索引:

解释select*fromvideoswhere`user`='BY2'和`path`='BY2'和`name`='BY2'

的课程，使用了3个索引，但当我问时我有一个问题。
使用一个中间查询条件模糊返回查询，看看命中了哪个索引：

explainselect*fromvideoswhere`user`='BY2'and`path`like'%BY2%'and`name`='BY2'

结论MySQL自动优化SQL语句，将能满足的查询条件放在开头，以便到达索引能。
提高查询速度。
对这样的字段添加索引无疑增加了表的空间，增加了表记录的新增和变更操作的压力。
接下来我们将讨论共享索引。

普通索引

普通索引是指将表中的多个字段视为一个索引：

联合远解释左匹配原理：以索引字段作为查询条件。
当出现范围查询（>、<、Between、like）时，匹配结束explainselect*fromvideoswhere`user`='BY2'and`path`='BY2'and`name`='BY2'

不用说，这肯定会起作用。
点击这个通用索引，然后使用中间步骤尝试如下：

explainselect*fromvideoswhere`user`='BY2'and`path`like'%BY2%'and`name`='BY2'

完全没有生命力，我以为它会击中一个用户，也会击中整个共享索引。
我还以为MySQL会优化前面的name和user字段来实现影响整个共享索引的原理，现在在最后添加这个类似的查询：

explainselect*fromvideoswhere`user`='BY2'and`path`='BY2'and`name`like'%BY2%'

似乎已经实现了这个通用索引：命中两个索引直接命中整个共享索引，验证成功。

从网站上了解到，我设置索引的顺序和最左匹配原则的顺序不顺序匹配。
User和Path这两个字段可以优化顺序。
然而我设置的共享索引顺序是路径、名称、用户。
在用户和路径中间有一个“Links”字段的索引，该索引基于与where条件的顺序相关的查询条件！

总结

在日常工作中，我发现阿里云的云数据库会根据数据库热点查询数据自动添加索引，这也缓解了一些不知道如何创建索引的人的压力，或者减少了虚假索引的数量，同时数据库压力自动减轻，哈哈。
索引是MySQL的一个非常复杂的知识。
如果以后遇到问题，需要记录下来并自行练习，以提高印象。
感觉今天复习过程中跳过了很多复杂的知识，为了解释一些很重要的信息的意思，等以后遇到了，再仔细研究一下。
这就是今天的全部内容。

面试中常被提到的最左前缀匹配原则

最左前缀匹配原则，面试中经常提到的，是MySQL构建连接索引时遵循的规则。
当使用联合索引检索数据时，MySQL从索引最左边的字段开始匹配，并向右延伸。
了解这个原理将有助于您优化查询性能。
索引的基本原理是B+树结构，其中键值存储在连接索引的B+树节点中。
数据库首先使用最左边的字段进行排序，然后根据后续字段进行排序。
例如，如果创建(a,b)连接索引，即使查询不按索引顺序，只要查询条件从左到右匹配，MySQL就可以使用该索引。
解释结果中的type字段表示查询的连接类型，key_len表示实际使用的索引长度。
精确值匹配和最左列匹配遵循最左原则，但不遵循最左匹配原则的范围查询可能会使索引无效，并可能将其转换为全表扫描或范围扫描。
例如，如果查询ID为'A%'，则索引将用于前缀匹配，查询ID='A%'将执行全表扫描。
ID范围查询可以使用范围索引，只要ID正确并且范围内的其他字段也按顺序排列即可。
否则，将执行整个表的扫描。
总之，了解最左前缀匹配的原理对于优化查询性能非常重要，因为它影响数据库如何有效地利用连接索引进行数据检索。

mysql为什么左前缀

MySQL建立连接索引时，遵循最左前缀匹配原则，或者说最左优先。
同时也匹配范围查询（>、<等之间的查询）。
等），匹配停靠点。
。

首先说一下我读到的文章的理解。

例如，我创建了一个具有三个属性的表：A、B、C。
在此基础上创建连接索引（A、B、C）。
我们实际上创建了三个索引：(A)(A,B)(A,B,C)。
您可以通过使用索引运行查询来检查(A=?,B=?)。
SQL语言还能够自动检测输入顺序何时与索引顺序不同，例如(B=)。
?,A=?),C=?)，也可以根据索引（A,B,C）进行查找。
然而，当搜索（B=？，C=？）时，使用完整索引而不是创建的索引（A，B，C）。
这涉及到下面解释的基本原理。
。
如果您有另一个查询（A，B，C）并且B是范围搜索，则索引只会到达A和B，并且后续字段将停止匹配，因为B是范围搜索。

如上所示，最左前缀原则是基于检索数据时指定的索引顺序。

这里有一篇文章详细介绍了它。
本文包含更详细的测试示例。

网页链接

基本原则

首先，最左前缀原则是为了协同索引，而实现原则是为了索引。
知道你需要知道什么。
的联合索引。

底层是一棵B+树，但是key值的个数大于1。
由于构建B+树仅基于一个键值，因此数据库根据联合索引的最左边字段构建B+树。

下面是(A,B)的连接索引。

可以看到A是有序的，但B是无序的。
B的顺序以A为基础。
所以最左边前缀的原则是先匹配A，再根据其索引匹配B。
如果A缺失，直接查找B，则不使用(A,B)索引，使用全索引。

用简单例子带你了解联合索引查询原理及生效规则

为了优化数据库性能并减少磁盘使用，通常会设计全局索引并尽量避免单字段索引。
本文以学生成绩表为例，讲解通用索引查询的有效原理和规则。
查询特定班级、姓名、学科的学生成绩时，可以创建一个包含[学生班级、学生姓名、学科名称]的全局索引。
索引页存储最小值，数据页形成有序链表。
搜索过程遵循等值匹配规则。
如果SQL查询的条件与索引字段完全匹配，并且使用相同顺序的等号，那么将直接使用全局索引进行高效搜索。
使用全局索引的关键是相等匹配原则和最左边的列。
只要查询最左边的索引字段（比如类），即使顺序不同，MySQL也会自动优化它。
范围搜索仅限于全局索引的最左列，例如`class_name>'Class1'和class_name'Class5'`。
您可以使用索引来查找某个范围内的数据。
当范围查询与其他字段结合使用时，例如`student_name>''`，则无法使用索引。
在实际使用中，应该编写诸如`SELECT*FROMstudent_scoreWHEREclass_name='1Class'ANDsubject_name<'Math'`这样的SQL语句，以确保范围查询只针对全局索引的最左边一列，以充分利用该索引来提高查询效率效率。