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)
blank

相关推荐

  • centos系统大量time wait占用的解决

    centos系统大量time wait占用的解决统计在一台前端机上高峰时间TCP连接的情况,统计命令:netstat-n|awk’/^tcp/{++S[$NF]}END{for(ainS)printa,S[a]}’除了ESTABLISHED,可以看到连接数比较多的几个状态是:FIN_WAIT1,TIME_WAIT,CLOSE_WAIT,SYN_RECV和…

  • hybrid.js_js交互是什么意思

    hybrid.js_js交互是什么意思HybridApp(混合模式移动应用)是指介于web-app、native-app这两者之间的app,兼具“NativeApp良好交互体验的优势”和“WebApp跨平台开发的优势”。

  • Linux下安装RabbitMQ

    Linux下安装RabbitMQ一、RabbitMQ介绍RabbitMQ是一个开源的AMQP实现,服务器端用Erlang语言编写,支持多种客户端,如:Python、Ruby、.NET、Java、JMS、C、PHP、ActionScript、XMPP、STOMP等,支持AJAX。用于在分布式系统中存储转发消息,在易用性、扩展性、高可用性等方面表现不俗。AMQP,即AdvancedMessageQ…

    2022年10月22日
  • java中hashmap遍历_map遍历的两种方式

    java中hashmap遍历_map遍历的两种方式在java开发中,hashMap是非常重要的容器类,存储的是键值对(key,value)。HashMap继承AbstractMap,实现了Map、Cloneable、Serializable接口,非线程安全类,但是效率高。HashMap允许null健和null值,允许value重复,但不允许key重复。HashMap有两个参数影响其性能,初始容量和加载因子,当哈希表中的条目数超出加载因子与当前容量的乘积时,要对哈希表进行refresh操作,重建内部数据结构,容量扩大为之前的两倍,加载因子默认值为0.75。

  • PCL 3D-SIFT关键点检测(曲率不变特征约束)

    PCL 3D-SIFT关键点检测(曲率不变特征约束)3D-SIFT关键点检测(基于曲率不变特征约束)!!!博客长期更新,本文最近一次更新时间为:2022年5月26日。通过阅读源码,总结了算法的实现原理及步骤。

  • 使用高通QXDM工具抓取Modem Log的操作方法(独家!)

    使用高通QXDM工具抓取Modem Log的操作方法(独家!)高通QXDM工具包下载链接如下:链接:https://pan.baidu.com/s/1rRNicFvlRSstUhka2JSiOg密码:pssp具体操作步骤如下:1、安装工具包里的QPST和QXDM软件。2、打开工具包里的3.dmc文件来启动QXDM软件,启动过程中会自动打开QPSTConfig。3、打开设备的开发者选项和USB调试选项,将设备连接到PC,取消”媒…

发表回复

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

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