建立了索引怎么使用_一文带你了解MySQL InnoDB索引原理

用过数据库的同学对于索引想必都不陌生，sql语句执行慢了，首先想到的就是先去加个索引，虽然可能没什么效果。但对于索引为什么么能提升sql速度，怎么选用合适的索引，可能就没有太清楚了。

首先创建一张表,并插入几条数据：

create table user(id int primary key,                   age int not null,                   name varchar(16),                  index (age))engine=InnoDB;                   insert into user values(1,10,'张三'); insert into user values(3,30,'王五')；  insert into user values(5,40,'马六')； insert into user values(7,20,'李四')；

接下来了一些例子我都会使用上面这张user表。

索引简介

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

数据结构

想一下我们之前了解的数据结构，哪一种可以快速查找数据呢？ Hash，数组，二叉树... 等都可以。这几种数据结构作为索引优惠有哪些优点和不足呢？

• 哈希(hash)：hash结构可以通过key与O(1)时间复杂度找到相应的value，效率可谓非常高所以使用hash作为索引满足快速查找的要求。但hash是可能会产生冲突的，冲突后采用拉链法解决冲突，这时候链表的数据其实是无序的，因此我们需要进行范围查找的话就没法做到了。所以hash只适合等值查找而不能做范围查找。

hash结构

• 数组：数组也是一种常用的数据结构，可以根据下标直接找到对应的值，而且数组也可以是有序的，范围查找也没有问题，但这样的话如果我们添加、删除一个元素的话，比如我需要在a[2]和a[3]之间添加一个元素，此时a[3]后面的所有元素都需要向后移动，删除也是同理，效率非常低。综合来看，如果我们的数据知识查询，基本不会有变动的话，数组做索引是很合适的。

数组结构

• 二叉树：二叉树可以以o(n*lgN)的时间复杂度查找元素，如下图，我们要查找元素12，此时就需要 1->3->6->12，查找四次，数据数的数据都是存在磁盘的，也就是所需要4次查找磁盘，如果有百万条数据，可以查找磁盘的次数就会非常多，磁盘的查找速度向来比较慢，这样的话效率就会非常低了。

二叉查找树

介绍了上面几种索引实现方式，各有优缺点及使用场景，但对于我们的mysql显然都不合适，因此我们的mysql innodb引擎采用了B+树来实现索引，有B+树肯定就有相应的B树，B树这里我就不介绍了，需要了解B树的可以去(https://zhuanlan.zhihu.com/p/27700617)进行扫盲。

B+树索引

下面我们主要介绍B+树在innodb中作为索引的实现。下面先看一张图来直观的了解一下B+树索引。

B+树索引

相比于二叉树，B+树可以说是一个多叉查找树了，这样的话树的高度就会变低，查找磁盘的次数就少多了，一般一个4层的B+树就能存储十亿条数据了。

接下来通过具体的sql语句来分析B+树的执行流程。

user表里面我们建立了两个索引，主键id索引和年龄age上面的索引。对应的innodb就会构建两颗B+树。(每一个索引内部都会构建一颗B+树)

索引树

因为我们建立了两个索引，索引现在mysql构建了两颗B+树。(索引树叶子节点是一个双向链表)

select * from user where id = 7

如果我们要查找id=5这条数据话，就会采用id这个索引，因此会去id索引树上面查找。(需要了解：索引树每层的数据是根据索引字段排序的)。

1. 首先在第一层发现ID=57> 4，因此直接通过指针定位到下一层的id=5这里。
2. 比较Id=7>id=5,因为是排序的，所以指针直接移动到下一个元素继续比较id
3. 此时发现id是符合的，返回对应结果。

接着我们使用age来进行查找，所以mysql会在age索引树上面查找。

select * from user where age = 70

1. 首先在第一层发现age=70> 40，引次直接通过指针定位到下一层的age=50这里。
2. 比较age=70>age=50,因为是排序的，所以指针直接移动到下一个元素继续比较age
3. 此时发现age=70是符合的。
4. 从age索引树我们可以看到底层节点存储的只有索引age字段和主键id两个字段，没有name字段，此时就需要回表操作了。也就是查到age=70 获取对应的主键id=7，然后使用id=7去id索引树使用上面的过程再查找一遍遍然后返回结果。

从上面使用主键索引和使用其他索引查找过程来看，两者是有一定区别的，下面介绍几个概念。

聚簇索引&&非聚簇索引

聚集索引其实也就是主键索引，非主键索引也就是非聚集索引。两者主要的区别是：

• 聚集索引的B+树叶子节点不仅存索引字段，而是会存储整行数据。
• 非聚集索引B+树叶子节点只会存索引字段+对应的主键id，查询可能需要根据主键id回表

回表

正如我们上面select * from user where age = 70 语句，根据age索引，来查找所有数据，因为age索引对应的B+树叶子节点值存储了age字段和主键id，没有其他字段，所以要获取其他字段只能先得到主键id，再根据主键id去查找数据，这一过程就称为回表。

覆盖索引

我们可以看出，回表每次都需要在根据主键id查找一次，这样想染效率很低，如果能直接从age索引树返回结果，不用回表，这样就会减少一次查询，提升效率。

select id,age from user where age = 70;

如果我们的sql语句使用上面的，这样因为age索引树其实已经包含了字段id、age,所以可以直接返回结果，而不需再根据id进行回表。这就是所谓的覆盖索引。所以我们在写sql的时候尽量利用覆盖索引的特性，减少不必要的回表。

通过上面的索引的介绍，应该对索引有个基本的了解的。索引不仅可以使用在单个字段上，也可以使用多个字段，也就是联合索引。

联合索引

alter table user add index idx(age,name);

此时我们就建立了一个age+name的联合索引，我们先通过下面这张图来直观的了解一下联合索引的存储方式。

联合索引

可以看到联合索引和单字段索引基本是一样的，只不过联合索引B+树每个节点会存储所有索引字段，叶子节点同样会存储id用于回表操作。

select * from user where age = 70;

根据上面的语句查找过程和我们上面使用单个age索引过程是一样的。

select * from user where name = '张三'；

但如果使用上面的语句，是不能根据name索引查找的。原因也就是最左前缀。

最左前缀原则

联合索引只能按照索引的顺序从左到右使用索引，而不能跳过左边的索引直接使用左边的字段作为索引。

就比如我们上面建立的所以呢 age+name，可以使用索引age，而直接使用name则用不到索引。

下面我分析分析一下为什么联合索引要符合最左前缀原则。

首先我面用索引建立一颗索引树，索引树中每层节点是根据索引字段从小到大排序的，如果是联合索引多个字段的话，如age+name索引，则首先根据age字段索引，age相再再根据name字段排序。因此如果我们跳过左边的字段直接根据右边的字段在索引树查找的话，mysql就根本不知道怎么去查找了，因为后面的字段可是是无序的。通过这些，相信大家已经充分理解最左前缀的原理，也知道什么情况能使用到索引，什么情况用不到索引了。

同理

select age from user where age + 1 = 7;

这种也是用不到索引的，mysql并不会对我们表达式进行优化，而这样age+1显然是没有这个索引的，也就用不到索引了，但我们可以修改为:

select age from user where age = 7 - 1；

这样就可以用到age索引了。同样使用avg，max等函数也不会使用索引。

部分索引

假设我们在user表里面添加了一个字段description，这个字段内容可能有几百个字符，我们现在这个字段上建索引查找要怎么做呢，如果整个字段建立索引显然很浪费空间，这时我们就可以根据具体数据特点建立部分索引，下面就建立了一个部分索引，这样的话就截取字符串的前40个字符建立索引。

ALTER TABLE user add key (description(40));

但需要注意因为部分索引只是用了字符串的一部分，所以可能并不准确，因此也就无法使用覆盖索引。

上面对innodb的索引做了一个基本的介绍。在实际开发中一定要根据具体情况选中合适的索引，而不要查询慢就建一个索引。

总结

1. 每个索引字段都会在内存建立一颗B+树，因此索引太多会占用大量空间，同时添加元素，删除元素都会对B+树进行重建，过多索引显然会效率很低
2. B+树每层元素是根据索引字段从小到大排序了，因此可以进行范围查找
3. innodb数据都存储在B+树叶子节点，上层节点是不存数据的，只存索引
4. 聚集索引叶子节点存储了数据表的整行数据，非聚集索引只存储索引字段+主键id，因此需要回表才能获取所有数据
5. 联合索引要符合最左前缀原则，否则会用不到索引，进行全表扫描
6. 查询条件有表达式，函数也不会使用索引
7. 前面几篇我们我们也知道sql语句是会有个优化器对我们的sql语句进行优化的，优化会对各种情况进行成本计算，最终决定合适的执行方式，因此在某些情况下也有可能即使建了索引依然会用不到索引的情况，这种就需要结合业务场景对sql进行分析、优化了