1500字范文 > 带你了解什么是MySQL数据库（六）索引原理如何建立与使用索引

带你了解什么是MySQL数据库（六）索引原理如何建立与使用索引

时间：2021-02-03 18:45:58

文章目录

前言索引原理介绍查找二叉树、平衡二叉树、B树、B+树聚集索引与非聚集索引索引管理测试索引正确使用索引联合索引索引下推技术索引优化神器常见慢查询优化

前言

学习过某一门编程后相信我们对索引不会陌生，如Python内的列表通过索引取值，其目的也很简单，就是在众多数据中快速取到我们需要的内容。如果没有索引的话，我们只能一个一个值的去找，这无疑大幅度降低了效率。那么我们本章节来了解一下MySQL内的索引机制。

索引原理

介绍

什么是索引？

索引在MySQL中又称为’键’，是存储引擎快速找到记录的一种数据结构、或者说数据的组织方式。建立索引会消耗空间和时间，但是好处是索引可以快速找到我们想要的内容。

抽象理解：

表 > 书记录 > 每一页的内容索引 > 目录（用于快速查询哪一页的内容）

为何要用索引？

主要为了优化查询效率
ps：创建完索引以后会降低增、删、改的效率，但是通常大部分时间都在查询，而增、删、改的使用频率较低。
如：我们浏览页面，注册使用次数很少，大部分时间都在网页查询我们想要的内容。

如何正确看待索引？

开发人员最了解业务逻辑，任何一个软件都有吸引用户的亮点
亮点背后对应的就是热数据，这一点开发人员是最清楚的
开发人员最了解热数据对应的数据库字段有哪些
所以应该在开发软件的过程中就提前为相应的字段加上索引，而不是等软件上线后
让DBA发现慢查询SQL后再做处理，因为：
一个软件慢会影响用户体验，但是慢的原因有很多，你不能立即确定是SQL的问题，所以等定位到SQL问题后，问题已经被拖了很久了因为大多数DBA都是管理型DBA而非开发型，所以即便是DBA从日志中看到了慢查询SQL，也会因为其不同业务而很难分析出慢的原因，最后的背锅的还是开发人员。

索引到底是一种怎样的数据结构？

二叉树平衡二叉树B树B+树

首先我们索引在MySQL中分为三类：

B+树索引HASH索引全文索引

我们今天要介绍的是工作开发中最常接触到innodb存储引擎中的的B+树索引。

查找二叉树、平衡二叉树、B树、B+树

要介绍B+树索引，就不得不提二叉查找树，平衡二叉树和B树这三种数据结构。B+树就是从他们仨演化来的。

二叉树

我们首先来一张图

图中的圆为查找二叉树的节点，节点中存储了键(key)和数据(data)

既然称之为"树"，那么它就有层次结构：

顶端节点称为：“根节点”中间节点称为：“子节点”末尾节点称为：“叶子节点”

二叉树的特点：

任何节点的左子节点的键值都小于当前节点的键值右边节点的键值都大于当前节点键值

举例理解：

再来看User表，如果按正常逻辑，我们需要找到id=12字段的记录，那么就需要从上向下依次查找，经历6次以后才能找到。但是分成二叉树结构后的查询顺序：
把根节点当做当前节点，将12与当前节点键值对比，很明显，12大于10，那么就把当前节点右边的子节点作为当前节点将12与当前节点键值对比，很明显，12小于13，那么就把当前节点左边的子节点作为当前节点将12与当前节点键值对比，发现12等于12，满足查询条件，那我们就从当前节点取出data：12,xm
利用二叉查找树我们只需要3次即可找到匹配的数据。

平衡二叉树

上面我们讲解了利用二叉查找树可以快速的找到数据。但是，如果上面的二叉查找树是这样的构造：

如果我们想要查询id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率不稳定。为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。平衡二叉树又称：“AVL树”，在满足二叉查找树的特性基础上，要求每个节点的左右子树的高度不能超过1。下面是平衡二叉树和非平衡二叉树的对比：

由于平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一颗"平衡二叉树"。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致平衡二叉树变的不平衡时，而平衡二叉树会进行调整树上的节点来保持平衡。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

二叉树潜在问题：

因为内存的易失性。一般情况下，我们都会选择将表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

归根结底就是说：让磁盘块能尽量多存一些内容，因为每次硬盘进行IO时间为：平均寻道时间 + 平均延迟时间(一般为9ms)，如果能在一个磁盘块内尽量取出内容是最好的，因为每次为了获取一个节点内容就浪费9ms实属不值。

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的B树。

B树

B树（Balance Tree）即为平衡树的意思，不要和平衡二叉树弄混淆了，这是一种新的数据结构，下图即是一颗B树。

注意：

图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。图中的每个节点称为页，页就是我们上面说的磁盘块，在MySQL中数据读取的基本单位都是页，所以我们这里叫做页更符合MySQL中索引的底层数据结构。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：

先找到根节点也就是页1，判断28在键值17和35之间，那么我们根据页1中的指针p2找到页3。

将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。

将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

特点：

单一节点存储更多元素，减少了IO次数树的高度比之二叉树更为缩短了，查找次数也随之减少

B+树(innodb存储引擎默认索引方式)

B+树是对B树的进一步优化。让我们先来看下B+树的结构图：

根据上图我们来看下B+树和B树有什么不同：

1、B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。

2、因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

有心的读者可能还发现上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）

B+树特点：

非叶子节点包含的信息更少，如果把同一节点的所有信息放在一个磁盘块中，则可以比B树放入更多的关键码。一次读入内存当中(读一个块)就能读入更多的关键码，所以降低了磁盘I/O总数。对任何关键字的查找都必须从根节点走到叶子节点，路径长度相同，所以对每条数据的查询效率相当。遍历叶节点就可以将整个B+树遍历完，因为叶节点之间通过链表进行了关联，里面存在当前表所有数据，而在数据库中基于范围的查询是非常频繁的，B+树就能更好的支持。

聚集索引与非聚集索引

什么是聚集索引与非聚集索引

在上序提到的B+树索引的时候，我们图中的索引其实就是聚集索引的方式实现，聚集索引也可以叫"聚簇索引"。

在MySQL中，以innerdb存储引擎作的表，而B+树也是其索引的方式之一，而B+树的索引又分为两种：聚集索引、非聚集索引

1、聚集索引（聚簇索引、主键索引）

以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮助你创建一个隐式主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中存储了表中所有数据（在B+树的特点内有提到），我们称之为：“聚集索引”

2、非聚集索引（非聚簇索引、辅助索引、二级索引）

以主键以外的列值作为键值构造的B+树索引，我们称之为：“非聚集索引”。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表的数据，而是存放该列对应的主键值，想要查找数据，我们还需要根据主键再去聚集索引查找，这个再根据主键值回去聚集索引里面查找数据的过程，我们称为：“回表”。如果需要查找的数据在当前索引内可以找到，就不需要进行回表了，这种我们称为：“覆盖索引”。如：我们是通过name这个辅助字段查找数据的，且最后我们提取出来的也只有name字段或主键值，那么这个时候就不需要回表查询数据了，因为在当前索引内就可以找到，所以这个操作就是"覆盖索引"

利用聚集索引查找数据

首先观察下图，当我们执行的SQL语句为：select * from user wherer id >= 18 and id < 40;聚集索引的搜索流程

一般根节点都是存于内存中的，也就是说页1已经在内存中了，所以我们首先需要找的就是大于或者等于18的键值了，当找到之后在顺着指针查找，最后找到叶子节点那里，一但到达叶子节点的位置后，那么此时找寻数据就可以在叶子节点之间来查找了，因为需要找值的是范围比较大，所以会不断向右面的页查找符合该范围的键值并拿到数据，一但找到某个值超出指定范围时，则会停止查找。

利用非聚集索引查找数据

观察下图，我们将sign字段设置为辅助索引，此时我们需要执行：select * from user where sign = 33;的数据

寻找记录的过程和聚集索引相同，不同的是，找到之后叶子节点存放的是主键值，那么我们要获取该字段其它数据时，需要拿到主键值回到聚集索引里面查找，也就是上序提到的"回表"

总结：

B+树在通过主键索引找值时，可以找到所有数据。通过辅助索引找值时，只能找到辅助索引键值与主键值，此时如果要找数据有两种方式：

需要找的数据在当前索引内就可以找到，而不需要回表查询，这种查询速度最快，因为辅助索引存放的数据没有聚集索引内那么多，而这也称为：“覆盖索引”需要的数据再当前索引内无法找到，只能拿主键值去聚集索引内查找，这样的查询会降低一些效率，而这种就是：“回表查询”

索引管理

一、功能

索引的功能就是加速查找mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

二、MySQL常用索引

普通索引INDEX：加速查找
唯一索引：
主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）唯一索引UNIQUE:加速查找+约束（不能重复）
联合索引：
PRIMARY KEY(id,name):联合主键索引UNIQUE(id,name):联合唯一索引INDEX(id,name):联合普通索引

三、索引的两大类型hash与bree

我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

四、创建/删除索引的语法

#方法一：创建表时CREATE TABLE 表名 (字段名1 数据类型 [完整性约束条件…],字段名2 数据类型 [完整性约束条件…],[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY[索引名] (字段名[(长度)] [ASC |DESC]) );#方法二：CREATE在已存在的表上创建索引create index 索引名 on 表名(字段名);#方法三：ALTER TABLE在已存在的表上创建索引alter table 表名 add index 索引名(字段名);#删除索引：DROP INDEX 索引名 ON 表名字;

测试索引

通过实验检测有无索引的查询速度

创建如下实验表

# 1、准备表CREATE TABLE `test` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(10),`email` varchar(100),PRIMARY KEY (`id`));# 2、创建存储过程，实现批量插入记录delimiter // # 声明存储过程的结束符号为//CREATE PROCEDURE `ins_test`()BEGINdeclare i int default 1;while (i <= 1000000) DO # 插入百万条记录insert test values(null,'jack',CONCAT('jack@',i,'.com'));set i = i + 1;end while;END // # 结束delimiter ; # 重新声明分号为结束符号# 3、调用存储过程，插入记录call ins_test();

等待中…

开始检测索引是否加速，其中id字段已经设置为索引了（主键），而name与email字段没有索引

明显可见的是，有索引的查询速度大于没有索引的查询速度，那么我们为email字段建立索引，再来查看是否提升了查询速度

建立索引需要一定时间，我们来查看建立后的查询效果

可以看到明显大于没有建立索引时的状态。

正确使用索引

并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题：

范围问题：使用了>、>=、<、<=、!= 、between…and…、like等等，扩大了查询范围，导致查询速度变慢

区分度过低，我们在建立索引时应选择区分度过高的建立索引，如：上面email字段数据，基本没有重复数据，而name字段全部是重复数据，就非常不适合建立索引，因为创建索引也会占用空间，这种就是没有意义的索引，更谈不上提速了。所以应给区分度高的字段建立索引才能提速

索引列不能参与计算，保持列“干净”，如果索引参入计算后，将无法拿到一个明确的值取索引树中查找，每次都得临时计算一下，显然成本太大。

联合索引

联合索引也可以称为：组合索引、复合索引。

其主要目的就是：为多个字段建立一个索引。

create index ix_id_name on test(id,name);

联合索引的本质：最左前缀匹配原则

非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

需要注意的是：上面所说的内容必须在最左边那个字段作为条件查询时才能生效，因为这样才能满足最左前缀匹配。

测试：我们先删除所有索引

mysql> alter table test change id id int;Query OK, 1000003 rows affected (10.08 sec)Records: 1000003 Duplicates: 0 Warnings: 0mysql> alter table test drop primary key;Query OK, 1000003 rows affected (10.38 sec)Records: 1000003 Duplicates: 0 Warnings: 0mysql> drop index ix_em on test;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> drop index ix_name on test;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0

而上面慢的原因是先比较id是否符合，显然大于50的每条记录id都符合，然后再判断name是否符合，email是否符合，每次都要比对这么多，显然很慢。

# 先删除索引，为了后面正确建立索引。drop index tx on test;

分析下面为什么提速的原因：因为遵循最左前缀匹配，也就是会先查询name索引内容，再查询email的，这样就排除了很多不需要的数据，最后再查询id符合的数据。

注意：如果建立了联合索引，那么在通过索引查询的时候，必须命中建立联合索引时最左边的那个字段，才能达到提速的效果。如上面图片里的：必须在查询时将name字段带上才能有索引提速效果。

如果查询时没有携带name字段，这个联合索引也就失去了效果，因为name是最左边的字段。如这种联合索引：

create index m_em_id on test(name,email,id);

以上联合索引可以作为查询条件的是：

name、email、id。name、idname、email。

索引下推技术

简介

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，使用非主键索引（又叫辅助索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

在实验开始前，我们需要先建立如下实验表：

create table user_info(id int,name varchar(20),age int);insert user_info values(1,'陈1',30);insert user_info values(2,'陈2',20);insert user_info values(3,'李1',30);create index name_age_ix on user_info(name,age);

待会我们执行的是：select * from index_test where name like '陈%' and age = 20;这条SQL语句。

我们分析这个SQL语句的执行流程，索引下推技术是在5.6以后产生。

我们先分析一下Mysql5.6及以前版本的执行流程：

首先会忽略age这个字段，直接通过name查询，在非主键索引树上找到第一个满足条件的值时，通过叶子节点记录的主键值再回表到聚集索引查找到对应的数据，再对比是否为当前所要查找的年龄。其执行流程：

Mysql5.6及之后版本

并不会忽略掉age字段，而是在name匹配到对应的值后，age字段再进行匹配，因为它们是一个联合索引内，对age字段不符合20的记录直接跳过，而不是匹配到name之后就回表到主键内再对比。当匹配到两个字段都符合的数据后，再拿主键值回表到聚集索引内获取真实数据，上序执行的SQL语句：整个过程只需要回表一次

什么？上序操作没有设置主键？答：MySQL默认会选择一个不为空，且唯一的字段并将其设置为主键，innodb引擎也会将其作为聚集索引。

上面所述只是理论性的，那么我们来结合实际验证一下

当出现上序字符，表示该查询使用了索引下推技术

总结：

索引下推是在非主键索引上做的优化，其有效减少了我们回表次数，很大程度上提升了我们查询效率。

索引优化神器

对MySQL索引进行优化时，有一个神器，就是explain命令（也可以使用DESC命令，效果一样），它也可以叫SQL语句的执行计划，下面简单介绍下explain命令。

explain命令可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的可以帮我们分析SQL语句或者表结构的性能瓶颈。通过explain命令，我们可以得到：

表的读取顺序

数据读取操作的操作类型

哪些索引可能使用

哪些索引被实际使用

表之间的连接

每张表有多少行被优化器查询

我们对每一列的信息进行说明：

1、id：

包含一组数字，表示查询中执行select子句或操作表的顺序
Example（id相同，执行顺序由上至下）
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2、select_type

示查询中每个select子句的类型（简单OR复杂）

SIMPLE：简单查询（不使用连接或子查询）

PRIMARY：最外层的查询

UNION：连接中第二个或者之后的子查询

DEPENDENT UNION：连接中第二个或者之后的子查询（依赖于外部表查询）

UNION RESULT：连接的结果

SUBQUERY:子查询中的第一个查询

DEPENDENT SUBQUERY：子查询中的第一个查询（依赖于外部表查询）

DERIVED：派生表查询（from子句中的子查询）

MATERIALIZED：被物化的子查询

UNCACHEABLE SUBQUERY：结果不能被缓存并且必须为外部查询的每一行重新评估的子查询

UNCACHEABLE UNION：属于不可缓存子查询的联合中的第二个或更高版本的选择

3、 type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

4、table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

5、possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

6、Key key列显示MySQL实际决定使用的键（索引）