数据库索引原理

先看个例子

在下面这个表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查询语句的执行流程：

1. 在k索引树上找到k=3的记录，取得ID=300
2. 再到ID索引树查到ID=300对应的R3
3. 在k索引树取下一个值k=5，取得ID=500
4. 再回到|D索引树查到|D=500对应的R4
5. 在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 = '小李';

1. 在name,age联合索引树上找到名称为小李的节点
2. 此时节点索引里包含信息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 的先过滤掉，然后再回表查询。

索引下堆优化