欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql复合索引

程序员文章站 2024-03-16 20:58:28
...

说明:本文中所有叙述均基于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

mysql复合索引

SELECT *

FROM test2

WHERE a='wenrui'

      AND c='wangzhen'

走索引

explain type=ref

mysql复合索引

SELECT *

FROM test2

WHERE a='wenrui'

      AND b='wuhuanhong' 

      AND c='wangzhen'

走索引

explain type=ref

mysql复合索引

SELECT *

FROM test2

WHERE b='wuhuanhong'

      AND c='wangzhen'

不走索引

explain type=index

mysql复合索引

对于上述表格中最后一个例子(红色标识):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

mysql复合索引

 

举例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

mysql复合索引

SELECT b,c

FROM test3

WHERE b='wuhuanhong'                AND c='wangzhen'

select的列包含在

索引ind_test3中,explain type=index

mysql复合索引

SELECT a,b

FROM test3

WHERE b='wuhuanhong'                AND c='wangzhen'

select的列包含在

索引ind_test3中,explain type=index

mysql复合索引

SELECT a

FROM test3

WHERE b='wuhuanhong'                AND c='wangzhen'

select的列包含在

索引ind_test3中,explain type=index

mysql复合索引

SELECT b

FROM test3

WHERE b='wuhuanhong'                AND c='wangzhen';

select的列包含在

索引ind_test3中,explain type=index

mysql复合索引

SELECT c

FROM test3

WHERE b='wuhuanhong'                AND c='wangzhen'

select的列包含在

索引ind_test3中,explain type=index

mysql复合索引

SELECT *

FROM test3

WHERE b='wuhuanhong'                AND c='wangzhen'

select的列不包含在索引ind_test3中,explain type=all,全表扫描

mysql复合索引

SELECT d

FROM test3

WHERE b='wuhuanhong'                AND c='wangzhen'

select的列不包含在索引ind_test3中,explain type=all,全表扫描 mysql复合索引