mysql-5.回表、覆盖索引、最左匹配原则

回表

通过上一章的介绍，我们知道索引可以分为主键索引和非主键索引。非主键索引的叶子节点存储的是主键索引的值。
我们在通过非主键索引查询时候，需要先在非主键索引树找到主键值，然后再到主键索引树根据主键值去查询出整行数据，这个过程中，回到主键索引树搜索的过程，我们称为回表

覆盖索引

#创建一张表
mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

#插入的数据
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

索引树的结构为：

对于上面的表我们执行下面这条查询语句：select ID from T where k between 3 and 5
这个时候，输出列只需要查询ID，而索引树k上面已经有ID信息了，因此可以直接返回结果，不用再去ID主键索引树上搜索了，就避免了回表。
也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

tips:由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
例如： 我们公司的内部通信软件，经常会用员工工号查询员工姓名，这样一个高频搜索需求，那么我们就可以建立一个（工号，姓名）的联合索引，通过合理使用联合索引，来实现覆盖索引，提高查询效率。
当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

最左匹配原则

我们建立一个联合索引 idx_abc（a,b,c）

查询条件	索引是否生效
where a=#{a} and b=#{b} and c=#{c}	idx_abc生效
where c=#{c} and b=#{b} and a=#{a}	idx_abc生效（特殊性情况，优化器会处理,最后又会变成abc顺序）
where a=#{a} and b=#{b}	idx_abc生效
where a=#{a}	idx_abc生效
where a=#{a} and c=#{c}	idx_abc生效
where b=#{b}	idx_abc不生效
where c=#{c}	idx_abc不生效
where b=#{b} and c=#{c}	idx_abc不生效

可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

1. 查询条件的第一个一定要和联合索引顺序匹配上，否则索引失效。这个时候，如果我们字段c才是高频搜索条件呢，我们又不能不建索引，所以只能新建一个idx_c（c）的索引。但是我们是否可以考虑，调整索引顺序，将idx_abc(a,b,c)改为idx_abc(c,b,a)呢？这样我们就不用单独去建立一个idx_c索引了。当然这个是否可以这么修改还是要看实际业务场景。

• 所以就有了第一原则：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

2. 如果对于联合索引idx_ab(a,b),既有联合的查询，又有基于a ,b各自的查询。那我们是建立（a,b）(b)这两个索引还是建立（b,a）(a)这两个索引呢？那么需要考虑单独的这个索引怎么样可以占用更少的磁盘空间

• 这时候我们需要从节省空间角度出发了，单独建立的索引谁站的空间更小。

给大家贴个表结构，大家可以亲自动手试下

CREATE TABLE `t_test` (
  `id` int(11) DEFAULT NULL,
  `a` varchar(255) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  KEY `idx_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_test` (`id`, `a`, `b`, `c`) VALUES ('1', 'a1', 'b1', 'c1');
INSERT INTO `t_test` (`id`, `a`, `b`, `c`) VALUES ('2', 'a2', 'b2', 'c3');

#通过explain来看下索引是否生效
EXPLAIN select * from t_test where a='a1' and b='b1' and c='c1'
EXPLAIN select * from t_test where a='a1' and b='b1'
EXPLAIN select * from t_test where  a='a1'
EXPLAIN select * from t_test where b='b1'
EXPLAIN select * from t_test where c='c1'
EXPLAIN select * from t_test where a='a1' and c='c1'
EXPLAIN select * from t_test where b='b1' and c='c1'
EXPLAIN select * from t_test where c='c1' and b='b1' and a='a1'