大家好,又见面了,我是你们的朋友全栈君。
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,或者条件中使用函数等。
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/135933.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...