MySQL联合索引最左匹配原则使用详解

欧克小奥

已于 2024-03-12 18:42:43 修改

阅读量1.7k

点赞数 31

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-01-05 17:53:06 首次发布

本文链接：https://blog.youkuaiyun.com/qq_52805594/article/details/135415222

本文详细介绍了MySQL联合索引最左匹配原则。先复习了MySQL索引失效的情况，接着通过场景题引出对最左匹配原则的研究。经数据准备和使用分析，得出查询条件含联合索引全部字段或缺中间字段时，优化器会使查询走联合索引，遇到范围查询，其后字段不走索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

☆* o(≧▽≦)o *☆嗨~我是小奥🍹
📄📄📄个人博客：小奥的博客
📙📙📙Github：传送门
📅📅📅面经分享（牛客主页）：传送门
🍹文章作者技术和水平有限，如果文中出现错误，希望大家多多指正！
📜 如果觉得内容还不错，欢迎点赞收藏关注哟！ ❤️

MySQL联合索引最左匹配原则使用详解

前言

很多小伙伴在面试的过程中都会被问到：MySQL的索引的失效的情况有哪些？

这里顺便带大家复习一下这个八股文：

（1）联合索引不满足最左匹配原则。联合索引的使用遵循最左匹配原则，在联合索引中，最左侧的字段优先匹配。在查询的时候，如果想让查询条件走索引，那么最左边的字段要出现在查询条件中。

（2）运算操作或者函数操作。原因是因为如果没有进行运算操作，那么MySQL会直接走索引去B+树中查询数据。但是如果我们对字段进行了运算操作或者函数操作，实际上是拿新的字段去做查询，如果走索引，那么需要一个一个去对比，MySQL认为既然都要对比，不如直接全表扫描。

（3）字符串类型字段不加引号。字符串字段如果不加引号，会发生隐式转换成int类型，参数类型与字段类型不匹配。

（4）模糊查询匹配头部。索引本身相当于目录，将字符串字段从左到右依次排序，而左侧的占位符模糊匹配，导致无法正常按照目录进行匹配。所以索引会失效。

（5）or连接的条件。如果or连接的条件左右其中有一个不是索引字段，那么如果是单独使用的话肯定是要走全表扫描的，连接在一起再次进行索引查询反而是浪费性能了，所以索引会失效。

（6）索引列做 < 、> 或者<> 比较。

（7）查询使用is not null。如果使用is null正常走索引，但是使用is not null索引会失效。

（8）查询条件使用not in时，如果是主键走索引；如果是普通索引，则索引失效。

然后面试官很快就会给出一个场景题：

给你一个数据表，有a，b，c三个字段，然后我现在给这三个字段加上联合索引 index(a,b,c)，如果where后面跟着条件的顺序是(a,c,b)，那么索引会生效吗？

我们在学习MySQL索引的时候都会学习到最左匹配原则，带大家简单复习一下这个概念：

最左匹配原则：

最左匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、< ）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

所以，对于上面的场景题，我们会肯定的给出答案：不会生效。

那么，恭喜你，回去等待面试结果吧！！！

啊？难道结果不是这样吗？当然不是！

下面我们就一起来研究一下最左匹配原则的各种场景，看看联合索引是否会生效。

数据准备

（1）建表语句

CREATE TABLE `t_demo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` varchar(15) DEFAULT NULL,
  `b` varchar(15) DEFAULT NULL,
  `c` varchar(15) DEFAULT NULL,
  `d` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `INDEX_A_B_C` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

（2）快速产生测试数据10w条

执行下面的main方法后，产生数据文件为demo.sql，存放位置为E盘，我们只需要拿着这个数据直接去执行sql文件即可。

    public static void main(String[] args) throws IOException {
   
   
        for (int x = 1; x <= 100; x++) {
   
   
            StringBuilder sql = new StringBuilder("INSERT INTO `t_demo`(a, b, c, d) VALUES ");
            for (int i = 1; i <= 999; i++) {
   
   
                splice(sql, ",");
            }
            splice(sql, ";");
            sql.append("\r\n");
            //System.out.println(sql);
            File file = new File("E:/demo.sql");
            FileWriter fw = new FileWriter(file, true);
            BufferedWriter bw = new BufferedWriter(fw);
            bw.write(sql.toString());
            bw.close();
            fw.close();
        }
    }
 
    private static void splice(StringBuilder sql, String s) {
   
   
        String value = "('%s', '%s', '%s', '%s')";
        String a = RandomStringUtils.randomNumeric(4);
        String b = RandomStringUtils.random(2, true, false);
        String c = RandomStringUtils.random(