MySQL联合索引使用分析 彻底搞清楚什么情况下会走索引

MySQL联合索引使用分析 彻底搞清楚什么情况下会走索引1.建表CREATETABLE`t_demo`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`a`varchar(15)DEFAULTNULL,`b`varchar(15)DEFAULTNULL,`c`varchar(15)DEFAULTNULL,`d`varchar(15)DEFAULTNULL,PRIMARYKEY(`id`),KEY`INDEX_A_B_C`(`a`,`b`,`c`

大家好,又见面了,我是你们的朋友全栈君。

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.插入10万条数据

MySQL联合索引使用分析 彻底搞清楚什么情况下会走索引

快速生产10万数据方法,执行main方法后,会将sql文件存入E盘,放到MySQL执行就行了

    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);
    }

 

3.插入a、b、c联合索引

ALTER TABLE `t_demo` ADD INDEX `INDEX_A_B_C` ( `a`, `b`, `c` ) USING BTREE;

4.测试

采用explain查看执行计划,其中key就是使用索引情况,如果对explain不太了解可以看这篇:https://blog.csdn.net/Anenan/article/details/114525818

1.WHERE条件是a、b、c三个,查询abc所有排列组合情况:

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND b = "Or" AND c = "tGMvk";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| 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 (0.01 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND c = "tGMvk" AND b = "Or";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| 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 (0.01 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND a = "8166" AND c = "tGMvk";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| 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 (0.02 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND c = "tGMvk" AND a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| 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 (0.02 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND a = "8166" AND b = "Or";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| 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 (0.03 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND b = "Or" AND a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------+
| 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 (0.03 sec)

2.WHERE条件是a、b、c选两个,查询abc两个中所有排列组合情况:

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND b = "Or";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| 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 (0.01 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------------+------+----------+-------+
| 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 (0.02 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166" AND c = "tGMvk";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| 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 |   13 |    10.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| 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 |   13 |    10.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or" AND c = "tGMvk";
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 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 (0.03 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk" AND b = "Or";
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 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 (0.03 sec)

3.WHERE条件是a、b、c其中一个的情况:

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE a = "8166";
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 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 |   13 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE b = "Or";
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 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 (0.02 sec)

mysql> EXPLAIN SELECT * FROM `t_demo` WHERE c = "tGMvk";
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 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 (0.02 sec)

4.结果分析

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

5.总结

联合索引符合最左匹配原则,按照索引建的顺序,一个查询可以只使用索引中的一部份,但只能是最左侧部分。

例如:以a、b、c为顺序建的联合索引,条件为下列情况都能生效:

  1. WHERE a = ?
  2. WHERE a = ? AND b = ?
  3. WHERE a = ? AND b = ? AND c = ?

注意:与WHERE后面的条件顺序无关,优化器会将条件顺序优化成上面三种情况后执行。

另外 WHERE a = ? AND c = ? 也会走联合索引,但是只有a条件命中,c条件不走联合索引。

还有,需要避免索引失效的情况,如:LIKE %xxx,或者条件中使用函数等。

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/135933.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 自动化运维平台搭建(一)「建议收藏」

    自动化运维平台搭建(一)「建议收藏」自动化运维平台搭建(一)。喜欢的就赞我吧,这样我才有动力继续分享额,带你建立一个强大的自动运维平台

  • 微信公众平台开发笔记

    微信公众平台开发笔记

    2021年11月16日
  • 常用八大测试用例设计方法有哪些_测试用例编写方法

    常用八大测试用例设计方法有哪些_测试用例编写方法1、等价类划分(EquivalancePartitioning)测试的思想:将程序的输入域划分为若干个区域(等价类),并在每个等价类中选择一个具有代表性的元素生成测试用例。该方法是常用的黑盒(BlackboxTesting)测试用例(Testcase)设计方法。等价类划分可有两种不同的情况:有效等价类和无效等价类。有效等价类是指对于程序的规格说明来说是合理的、有意义的输入数据构成的集合,它能检验程序是否可以实现规格说明中所规定的功能需求。无效等价类是指对程序的规格说明是不合理的或无意义的输入数据所

    2022年10月12日
  • unity3d吧_unity3d从入门到精通

    unity3d吧_unity3d从入门到精通2-62-72-82-92-103-33-43-5

  • 秒懂mysql中的group by用法

    秒懂mysql中的group by用法文章转载自:https://blog.csdn.net/u014717572/article/details/80687042先来看下表1,表名为test:执行如下SQL语句:SELECTnameFROMtestGROUPBYname你应该很容易知道运行的结果,没错,就是下表2:可是为了能够更好的理解“groupby”多个列“和”聚合函数“的应用,我建议在思考的过程中…

  • 自监督学习 对比学习了解

    自监督学习 对比学习了解简介自监督学习是近几年(2020年)流行起来的一种机器学习方法,很多人认为自监督方法未来一段时间将取代现有的监督方法,成为深度学习中占据主导地位的方法。现在已经有不少采用自监督-对比学习的方法取得了领先的效果。目前(2020.10)来说这个领域貌似还比较新,没有找到很系统的讲解介绍资料。首先介绍一下到底什么是SSL,我们知道一般机器学习分为监督学习,非监督学习和强化学习。而self-supervisedlearning是无监督学习里面的一种。自监督学习的思想非常简单,就是输入的是一堆无监督的数据

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号