数据库索引原理
先看个例子
在下面这个表T中，如果我执行 select* from t where k between3and5，需要执行几次树的搜索操作，会扫描多少行？
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'),(208,2，'bb'),(300,3,'cc'),(509,5,'ee'），（600,6,'ff')，（788,7，'gg')
表结构如下所示：
image
现在，我们一起来看看这条SQL查询语句的执行流程：
- 在k索引树上找到k=3的记录，取得ID=300
- 再到ID索引树查到ID=300对应的R3
- 在k索引树取下一个值k=5，取得ID=500
- 再回到|D索引树查到|D=500对应的R4
- 在k索引树取下一个值k=6，不满足条件，循环结束
在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了k索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）。
如何进行索引优化，避免回表？
什么是覆盖索引？
如果执行的语句是select ID from T where k between 3 and 5， 这时只需要查ID的值， 而ID的值已经在k索引树上了， 因此可以直接提供查询结果， 不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求， 我们称为覆盖索引。
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',
`age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
PRIMARY KEY (`id`),
KEY `I_name` (`name`)
) ENGINE=InnoDB;
INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);
主键索引和普通索引的结构如下图：
如何使用覆盖索引
创建联合索引，可以使用上覆盖索引。
ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);
联合索引结构如下图所示：
SELECT age FROM student WHERE name = '小李';
- 在name,age联合索引树上找到名称为小李的节点
- 此时节点索引里包含信息age 直接返回 12
image
可以看到Extra中Using index表明我们成功使用了覆盖索引。
索引原则
最左前缀原则
B+树这种索引结构， 可以利用索引的“最左前缀”， 来定位记录。为了直观地说明这个概念， 我们用（name， age） 这个联合索引来分析
image
当你的逻辑需求是查到所有名字是“张三”的人时， 可以快速定位到ID4， 然后向后遍历得到所有 需要的结果。
如果你要查的是所有名字第一个字是“张”的人， 你的SQL语句的条件是"where name like ‘张%’"。这时， 你也能够用上这个索引， 查找到第一个符合条件的记录是ID3， 然后向后遍历， 直到不满足条件为止。
只要满足最左前缀， 就可以利用索引来加速检索。在建立联合索引的时候， 如何安排索引内的字段顺序。
索引复用能力
这里我们的评估标准是， 索引的复用能力。因为可以支持最左前缀， 所以当已经有了(a,b)这个联合索引后， 一般就不需要单独在a上建立索引了。因此， 第一原则是， 如果通过调整顺序， 可以少维护一个索引， 那么这个顺序往往就是需要优先考虑采用的。
索引空间占用
如果既有联合查询， 又有基于a、 b各自的查询呢？查询条件里面只有b的语句， 是无法使 用(a,b)这个联合索引的， 这时候你不得不维护另外一个索引， 也就是说你需要同时维护(a,b)、 (b) 这两个索引，我们要考虑的原则就是空间了。name字段是比age字段大的 ， 那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引。（name）字段索引需要的空间会大一些，所以推进创建一个（name,age)+(age)。
索引下堆
MySQL 5.6 引入的索引下推优化（indexcondition pushdown)， 可以在索引遍历过程中， 对索引中包含的字段先做判断， 直接过滤掉不满足条件的记录， 减少回表次数。
现在假设有这样一个表：
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
现在要进行如下查询：
select * from tuser where name like '张%' and age=10 and ismale=1;
如果没索引下堆优化的情况是怎么执行的呢？
只能从ID3开始一个个回表。 到主键索引上找出数据行， 再对比字段值。
image
有索引下堆优化的情况
把 age !=10 的先过滤掉，然后再回表查询。
索引下堆优化