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

发布时间:2024年01月06日

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

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

那么,恭喜你,回去等待面试结果吧!!!

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

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

数据准备

(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(5, true, false);
        String d = String.valueOf(System.currentTimeMillis());
        sql.append(String.format(value, a, b, c, d)).append(s);
    }

工具类RandomStringUtils的依赖如下:

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.11</version>
        </dependency

使用分析

下面的分析是通过explain查看执行计划,如果对执行计划不太了解的可以去先复习下。

条件是a,b,c

条件是a,b,c三个,查询abc所有的排列组合情况。

mysql> explain select * from t_demo where a = '8304' and b = 'iS' and c = 'qbAhz';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where b = 'iS' and a = '8304'  and c = 'qbAhz';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where a = '8304' and c = 'qbAhz' and b = 'iS' ;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where c = 'qbAhz' and  a = '8304' and b = 'iS' ;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where  c = 'qbAhz' and  b = 'iS' and a = '8304';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where  b = 'iS' and c = 'qbAhz' and  a = '8304';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 144     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

条件是a,b,c其中的两个

mysql> explain select * from t_demo where a = '8304' and b = 'iS';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 96      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where a = '8304' and c = 'qbAhz';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   12 |    10.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where b = 'iS' and a = '8304';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 96      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where  b = 'iS' and c = 'qbAhz';
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |     1.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where c = 'qbAhz' and  a = '8304';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   12 |    10.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where  c = 'qbAhz' and  b = 'iS';
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |     1.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

条件是a,b,c其中的一个

mysql> explain select * from t_demo where a = '8304';
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_demo | NULL       | ref  | INDEX_A_B_C   | INDEX_A_B_C | 48      | const |   12 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_demo where b = 'iS';
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from t_demo where c = 'qbAhz';
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t_demo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99918 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结果分析

  • 查询条件是a、b、c时,无论是什么顺序,由于优化器优化,都会走INDEX_A_B_C联合索引;
  • 查询条件是a、b时,会走联合索引;
  • 查询条件是a、c时,也会走联合索引,但是Extra信息里面多了一行:Using index condition,意思是先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用WHERE子句中的其他条件去过滤这些数据行,这种情况只有a条件用到联合索引,c条件回表到聚簇索引过滤
  • 查询条件是b、c时,不走联合索引;
  • 查询条件是a时,会走联合索引;
  • 查询条件是b时,不走联合索引;
  • 查询条件是c时,不走联合索引;、

经过上面的结果,我们可以清楚的看到,如果联合索引的字段全部在查询条件中,那么优化器会进行优化,都会走联合索引。

即使缺了中间的字段,也是会走联合索引的。

文章来源:https://blog.csdn.net/qq_52805594/article/details/135415222
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。