其实 MySQL 中的 like 关键字也能用索引

最新推荐文章于 2024-09-28 00:31:58 发布

2401_84010901

最新推荐文章于 2024-09-28 00:31:58 发布

阅读量1.7k

点赞数 7

分类专栏：程序员文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2401_84010901/article/details/137209453

版权

程序员专栏收录该内容

233 篇文章 0 订阅

订阅专栏

文章讨论了MySQL中如何利用复合索引来优化SQL查询，特别是在处理带有LIKE操作符时，强调了最左匹配原则以及复合索引在查询username时的自动使用。作者通过实例分析了等值匹配、模糊匹配和索引范围扫描的区别。

摘要由CSDN通过智能技术生成

PRIMARY KEY (id),

KEY username (username,age)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

username 和 age 组成了复合索引，复合索引名为 username，下文提到的 username 索引都是指该复合索引。

根据上篇文章（是时候检查一下使用索引的姿势是否正确了！）的讲解，我们知道，对于如下 SQL：

select username,age from user2 where username=‘javaboy’ and age=99;

这个 SQL 在查询的过程中，会用到覆盖索引，避免回表，提高查询效率。

那么现在问题来了，如果我单纯的只是想通过 username 字段查询用户呢，是否需要为 username 字段单独建立一个索引？

我们来看如下一条 SQL：

select username,age from user2 where username=‘javaboy’;

由于我的表中没有为 username 字段建立的索引，那么它会不会使用已有的复合索引呢？我们来看下执行计划：

可以看到，这里其实用到了 username 复合索引，通过 Extra 字段的值还能看到使用到了覆盖索引。

为啥会这样呢？在 B+Tree 这种索引结构中，可以利用索引的“最左匹配”来定位记录。最左匹配既可以是匹配复合索引中的前几个字段，也可以是匹配第一个字段的前几个字符，在上面的案例中，我们匹配的是复合索引中的第一个字段。

当然我们也可以匹配第一个字段的前几个字符，如下：

select username,age from user2 where username like ‘j%’;

执行计划如下：

从这执行计划中首先可以确认这个查询也用到了 username 复合索引。

不过这里的查询计划和前面的不太一样，两条 SQL 的区别在于一个是等于号一个是模糊匹配，查询计划的主要区别在于 type 和 Extra：

前面的 type 为 ref 表示通过索引查找数据，一般出现等值匹配的时候，type 会为 ref；后面这个 type 为 range 表示这是一个索引的范围扫描（因为是模糊匹配，而模糊匹配可以形成扫描区间）。
前面的 ref 为 const 表示与索引列进行等值匹配的是一个常量。
前面的 Extra 为 Using index 表示使用到了覆盖索引；后面的 Extra 为 Using where;Using index，表示用到了索引，但是还需要进行过滤。

对于第一点中加粗的字体，我再来和大家多说两句。为什么说模糊匹配就能形成扫描区间呢？因为我们是按照 username 和 age 建立的复合索引，username 在前 age 在后，具体存的时候，是按照 username 排序存储，如果 username 相同，则按照 age 排序存储，结构类似下面这样：

| username | age |

| :-- | :-- |

| a | 88 |

| b | 89 |

| c | 89 |

| c | 90 |

| c | 99 |

| d | 88 |

| d | 99 |

所以当想要搜索以 j 开头的 username 时，只需要定位到第一个以 j 开头的 username，然后利用 B+Tree 叶子结点之间的双向链表继续向后读取，读到第一个不是以 j 开头的 username 时截止，这就是扫描区间。

大家看到了，在上面的执行计划中，like 'j%' 其实也用到了索引，那么如果是 like '%j' 或者 like '%j%' 会用到索引吗？我们来看一个例子：

咦！看执行计划似乎也用上索引了！难道只要字段上有索引，like 就能用索引？

当然不是！

大家来看松哥下面这个辅助案例，看懂了就明白了。

2. 辅助案例

为了让大家更好的理解上面所说的最左匹配，松哥再来举一个例子。

还是上面的表和数据结构，但是现在假如我想按照 age 来做查询，SQL 如下：

select username from user2 where age=99;

select username from user2 where age>99;

我举了两个查询的例子，大家一起来看下这两条 SQL 的执行计划，其实没啥差异：

这个查询计划我们该如何解释呢？其实这两个查询计划没啥区别，我就解释一个吧。

首先大家想一下，username 和 age 建立的是复合索引，username 在前 age 在后，具体在 B+Tree 中存储的时候，首先是按照 username 排序的，当 username 相同的时候，再按照 age 来排序，所以这个复合索引最终存储的结果就是，username 是有序的，而 age 是无序的，再来回顾下这个表格：

| username | age |

| :-- | :-- |

| a | 88 |
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。