Mysql联合索引和索引条件下推

1、概念介绍

联合索引

联合索引和聚集索引一样使用B+树结构存储，联合索引的索引节点存储多个键值对，按首列数值排序，叶子节点存放所有的索引值以及对应的主键，如果需要查询非联合索引中的字段需要进行回表查询。联合索引的大小小于聚集索引。

索引覆盖

InnoDB存储引擎支持覆盖索引（covering index,或称索引覆盖），即从辅助索引中就可以得到査询的记录，而不需要査询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

当使用索引覆盖时，可在执行计划的列Extra看到Using index提示。

索引条件下推优化(ICP)

索引条件下推（ICP）是针对 MySQL 使用索引从表中检索行的情况的优化。如果没有 ICP，存储引擎会遍历索引以查找表中的行，并将其返回给 MySQL 服务层， 由MySQL 服务层执行WHERE条件。启用 ICP 后，如果仅使用索引中的列可以评估部分 WHERE 条件，则 MySQL 服务层会将这部分 WHERE 条件下推到存储引擎。然后，存储引擎使用索引条目评估推送的索引条件，并且仅当满足此条件时，才从表中读取行。ICP可以减少回表的次数。

当优化器选择Index Condition Pushdown优化时， 可在执行计划的列Extra看到Using index condition提示。

2、例子

test表

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  `c` varchar(20) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  `e` varchar(255) DEFAULT NULL,
  `f` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`,`b`,`c`),
  KEY `a_2` (`a`,`b`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100012 DEFAULT CHARSET=latin1

test2表

CREATE TABLE `test_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  `c` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`,`b`,`c`),
  KEY `a_2` (`a`,`b`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100012 DEFAULT CHARSET=latin1

test_2表只是比test表多了一些字段

例子1：

select * from test WHERE a="004yy3jzLU" and c = "pe7u0ucBP1";

explain

使用了索引下推

接着对test_2表执行同样的sql语句

select * from test_2 WHERE a="004yy3jzLU" and c = "pe7u0ucBP1"

explain

test_2表使用了索引覆盖，无需回表，因为索引a中包含了所有的需要返回的字段。

例子2：

select a,b,c from test_2 WHERE a="004yy3jzLU" and c = "pe7u0ucBP1"

explain

使用了覆盖索引，无需回表。