面试的时候总会问到索引的问题，而且一般会问到关于索引失效、哪种查询会走索引，哪种方式不会这种问题。今天早上闲来无事对联合索引进行了一些简单的实验，要测试的话肯定还有很多场景，在这里抛砖引玉。以后有机会再来完善此篇博客。
数据库版本
建表语句
CREATE TABLE `union_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
`b` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
`c` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
`createTime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `union_index` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- records
INSERT INTO `union_test` VALUES ('1', 'a', 'b', 'c', '2017-08-16 10:41:48');
INSERT INTO `union_test` VALUES ('2', 'aa', 'bb', 'cc', '2017-08-16 10:41:56');
测试结果
EXPLAIN SELECT a,b,c FROM union_test WHERE a = "a" AND b = "b" AND c = "c" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE a = "a" AND b LIKE "b%" AND c LIKE "c%" -- 走索引，这里三个变量如果第一个是like就不走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE a = "a" AND c = "c" AND b = "b" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE b = "b" AND a = "a" AND c = "c" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE b = "b" AND c = "c" AND a = "a" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE c = "c" AND a = "a" AND b = "b" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE c = "c" AND b = "b" AND a = "a" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE a = "a" AND b = "b" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE b = "b" AND a = "a" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE a = "a" AND c = "c" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE c = "c" AND a = "a" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE b = "b" AND c = "c" -- 不走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE c = "c" AND b = "b" -- 不走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE a = "a" -- 走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE a LIKE "a%" -- 不走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE b = "b" -- 不走索引
EXPLAIN SELECT a,b,c FROM union_test WHERE c = "c" -- 不走索引
个人看法
这里可以看出如果是联合索引的条件都存在的话，顺序就不是问题了，似乎我之前理解的最左索引原则有错误，现在想想，如果条件里面含有联合索引的第一个索引字段，那么不管where条件的顺序和个数，则这次查询都会走索引。上述如果使用了like，比如这样SELECT a,b,c FROM union_test WHERE a LIKE "a%" AND b = "b%" AND c = "c%"会导致索引失效，单个索引的后半模糊不会导致索引失效，这里却导致了索引失效，我没想明白为什么，还是说如果是联合索引，最左的索引字段就一定要是精确匹配呢？如果大神知道，还望告知，谢谢。
----后记
mysql中一般使用的都是B+树来作为索引的数据存储结构，所以一定要有一个索引的引导列，应该也就是B+树的root节点，只有根节点存在才可以使用到联合索引。
