MySQL 复合索引实践及运用

说明：本文中所有叙述均基于MySQL 5.6版本 ，Innodb引擎 ，数据库隔离级别为可重复读

众所周知，mysql复合索引查询遵从最左匹配原则。针对复合索引，使用实例说明索引匹配。

举例1：

表结构一共三个字段，这三个字段组合为一个复合索引

-- 如果已存在表 先删除
DROP TABLE IF EXISTS `test2`;

--建表,并建立复合索引ind_test2,索引字段为a,b,c
CREATE TABLE `test2` (
  `a` VARCHAR(20) NOT NULL,
  `b` VARCHAR(20) NOT NULL,
  `c` VARCHAR(20) NOT NULL,
  KEY `ind_test2` (`a`,`b`,`c`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入测试数据
INSERT INTO `test2` (`a`, `b`, `c`) VALUES('sunjiang','xiage','rendi');
INSERT INTO `test2` (`a`, `b`, `c`) VALUES('wenrui','wuhuanhong','wangzhen');
INSERT INTO `test2` (`a`, `b`, `c`) VALUES('xuweiming','changle','xiongzhongquan');

索引匹配结论 

例子	结论	执行计划
SELECT * FROM test2 WHERE a='wenrui'       AND b='wuhuanhong'	走索引 explain type=ref
SELECT * FROM test2 WHERE a='wenrui'       AND c='wangzhen'	走索引 explain type=ref
SELECT * FROM test2 WHERE a='wenrui'       AND b='wuhuanhong'        AND c='wangzhen'	走索引 explain type=ref
SELECT * FROM test2 WHERE b='wuhuanhong'       AND c='wangzhen'	不走索引 explain type=index

对于上述表格中最后一个例子（红色标识）：SELECT * FROM test2 WHERE b='wuhuanhong' AND c='wangzhen' 按照最左匹配原则，是无法走索引的，但是根据执行计划，走了全索引扫描，查看执行计划截图，发现possible_keys为null，而最终key存在值，是由于mysql内部的优化，可参考 https://dev.mysql.com/doc/refman/5.6/en/explain-output.html，简单来说为：

存在执行计划中key列显示的索引在possible_keys中并不存在的可能，这种情况发生在无法匹配索引但是由于查询的select的列中包含在某个索引中，也就是说，这个索引的列包含select查询的列，所以这种情况下虽然无法直接使用这个索引，但是由于索引全扫描起码比全部扫描要好，所以mysql使用索引全扫描 index

 

举例2

该例中验证上面陈述的mysql对于key列显示的索引在possible_keys中不存在的优化。

DROP TABLE IF EXISTS `test3`;

CREATE TABLE `test3` (
  `a` VARCHAR(20) NOT NULL,
  `b` VARCHAR(20) NOT NULL,
  `c` VARCHAR(20) NOT NULL,
  `d` VARCHAR(20) NOT NULL,
  KEY `ind_test3` (`a`,`b`,`c`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `test3` (`a`, `b`, `c`,`d`) VALUES('sunjiang','xiage','rendi','xiaoming');
INSERT INTO `test3` (`a`, `b`, `c`,`d`) VALUES('wenrui','wuhuanhong','wangzhen','xiaoming');
INSERT INTO `test3` (`a`, `b`, `c`,`d`) VALUES('xuweiming','changle','xiongzhongquan','xiaoming');

例子	结论	执行计划
SELECT a,b,c FROM test3 WHERE b='wuhuanhong'       AND c='wangzhen'	select的列包含在 索引ind_test3中，explain type=index
SELECT b,c FROM test3 WHERE b='wuhuanhong'                AND c='wangzhen'	select的列包含在 索引ind_test3中，explain type=index
SELECT a,b FROM test3 WHERE b='wuhuanhong'                AND c='wangzhen'	select的列包含在 索引ind_test3中，explain type=index
SELECT a FROM test3 WHERE b='wuhuanhong'                AND c='wangzhen'	select的列包含在 索引ind_test3中，explain type=index
SELECT b FROM test3 WHERE b='wuhuanhong'                AND c='wangzhen';	select的列包含在 索引ind_test3中，explain type=index
SELECT c FROM test3 WHERE b='wuhuanhong'                AND c='wangzhen'	select的列包含在 索引ind_test3中，explain type=index
SELECT * FROM test3 WHERE b='wuhuanhong'                AND c='wangzhen'	select的列不包含在索引ind_test3中，explain type=all，全表扫描
SELECT d FROM test3 WHERE b='wuhuanhong'                AND c='wangzhen'	select的列不包含在索引ind_test3中，explain type=all，全表扫描