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

当前位置博文首页 > xzh_blog：MySQL联合索引使用分析彻底搞清楚什么情况下会走索引

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

作者：[db:作者] 时间：2021-08-07 19:01

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万条数据

快速生产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.结果分析

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

5.总结

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

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

WHERE a = ?
WHERE a = ? AND b = ?
WHERE a = ? AND b = ? AND c = ?

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

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

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

上一篇：xzh_blog：架构师的核心能力

下一篇：没有了

立即下载 - IIS7 站长工具包