一步一步带你入门MySQL中的索引和锁

索引

索引常见的几种类型

索引常见的类型有哈希索引，有序数组索引，二叉树索引，跳表等等。本文主要探讨 MySQL 的默认存储引擎 InnoDB 的索引结构。

InnoDB的索引结构

在InnoDB中是通过一种多路搜索树——B+树实现索引结构的。在B+树中是只有叶子结点会存储数据，而且所有叶子结点会形成一个链表。而在InnoDB中维护的是一个双向链表。

你可能会有一个疑问，为什么使用 B+树 而不使用二叉树或者B树？

首先，我们知道访问磁盘需要访问到指定块中，而访问指定块是需要 盘片旋转 和 磁臂移动 的，这是一个比较耗时的过程，如果增加树高那么就意味着你需要进行更多次的磁盘访问，所以会采用n叉树。

而使用B+树是因为如果使用B树在进行一个范围查找的时候每次都会进行重新检索，而在B+树中可以充分利用叶子结点的链表。

在建表的时候你可能会添加多个索引，而 InnDB 会为每个索引建立一个 B+树 进行存储索引。

比如这个时候我们建立了一个简单的测试表

create table test(  
  id int primary key,  
  a int not null,  
  name varchar,  
  index(a)  
)engine = InnoDB; 

这个时候 InnDB 就会为我们建立两个 B+索引树

一个是 主键 的 聚簇索引，另一个是 普通索引 的 辅助索引，这里我直接贴上 MySQL浅谈（索引、锁） 这篇文章上面的贴图(因为我懒不想画图了。。。)

可以看到在辅助索引上面的叶子节点的值只是存了主键的值，而在主键的聚簇索引上的叶子节点才是存上了整条记录的值。

回表

所以这里就会引申出一个概念叫回表，比如这个时候我们进行一个查询操作

select name from test where a = 30; 

我们知道因为条件 MySQL 是会走 a 的索引的，但是 a 索引上并没有存储 name 的值，此时我们就需要拿到相应 a 上的主键值，然后通过这个主键值去走 聚簇索引 最终拿到其中的name值，这个过程就叫回表。

我们来总结一下回表是什么？MySQL在辅助索引上找到对应的主键值并通过主键值在聚簇索引上查找所要的数据就叫回表。

索引维护

我们知道索引是需要占用空间的，索引虽能提升我们的查询速度但是也是不能滥用。

比如我们在用户表里用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则是8个字节。也就是说如果我用整型后面维护了4个g的索引列表，那么用身份证将会是20个g。

所以我们可以通过缩减索引的大小来减少索引所占空间。

当然B+树为了维护索引的有序性会在删除，插入的时候进行一些必要的维护(在InnoDB中删除会将节点标记为“可复用”以减少对结构的变动)。

比如在增加一个节点的时候可能会遇到数据页满了的情况，这个时候就需要做页的分裂，这是一个比较耗时的工作，而且页的分裂还会导致数据页的利用率变低，比如原来存放三个数据的数据页再次添加一个数据的时候需要做页分裂，这个时候就会将现有的四个数据分配到两个数据页中，这样就减少了数据页利用率。

覆盖索引

上面提到了 回表，而有时候我们查辅助索引的时候就已经满足了我们需要查的数据，这个时候 InnoDB 就会进行一个叫 覆盖索引 的操作来提升效率，减少回表。

比如这个时候我们进行一个 select 操作

select id from test where a = 1; 

这个时候很明显我们走了 a 的索引直接能获取到 id 的值，这个时候就不需要进行回表，我们这个时候就使用了 覆盖索引。

简单来说 覆盖索引 就是当我们走辅助索引的时候能获取到我们所需要的数据的时候不需要再次进行回表操作的操作。

联合索引

这个时候我们新建一个学生表

CREATE TABLE `stu` (  
  `id` int(11) NOT NULL,  
  `class` int(11) DEFAULT NULL,  
  `name` varchar(255) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  KEY `class_name` (`class`,`name`) USING BTREE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  

我们使用 class(班级号) 和 name 做一个 联合索引，你可能会问这个联合索引有什么用呢？我们可以结合着上面的 覆盖索引 去理解，比如这个时候我们有一个需求，我们需要通过班级号去找对应的学生姓名 。

select name from stu where class = 102; 

这个时候我们就可以直接在 辅助索引 上查找到学生姓名而不需要再次回表。

总的来说，设计好索引，充分利用覆盖索引能很大提升检索速度。

最左前缀原则

这个是以 联合索引 作为基础的，是一种联合索引的匹配规则。

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!