MySQL索引以及索引优化分析

一、索引的概念

1、什么是索引?

MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。从官方的定义可以得到索引的本质:索引是数据结构,可以简单理解为索引就是一种数据结构,它可以帮助我们快速的从数据库查询到数据。索引就类似于一本书的目录,通过目录可以快速找到需要查找对内容。

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图就是一种可能的索引方式示例:

左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址 。 为了加快 Col2 的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指 针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。

2、索引的优缺点分析?
优势
  • 提高查询效率:提高对数据的检索效率,降低数据库的IO成本。
  • 天生排序:通过索引对数据进行排序,降低数据的排序成本,从而降低了CPU的消耗。
劣势
  • 降低更新表的速度:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
  • 占用额外的空间:实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间

二、索引的数据结构

索引是在MySQL的存储引擎层实现的,所以每种存储引擎支持的缩影都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型,MySQL目前提供了以下4中数据类型的索引:

  • B+树索引:最常见的索引类型,大部分存储引擎都支持BTREE索引。
  • Hash索引:只有Memory引擎支持,使用场景比较简单。
  • R-tree索引(空间索引):空间索引是MyISAM引擎的一个特殊索引类型,通常使用较少。
  • Full-text索引(全文索引):全文索引也是MyISAM支持的一种特殊索引类型,主要用于全文索引,InnoDB从MySQL5.6开始也支持全文索引。
InnoDB、MyISAM、Memory三种常用存储引擎对各种索引的支持
索引 InnoDB MyISAM Memory
B+树索引 支持 支持 支持
Hash索引 不支持 不支持 支持
R-tree索引 不支持 支持 不支持
Full-tree全文索引 5.6以后支持 支持 不支持

我们平常所说的索引,没有特殊说明,都是指**B+树(多路搜索树)**结构组织组织的索引。下面我们就来详细了解一下B树索引。

这里经常可能会有面试问B+树和Hash索引的区别,这里总结一下。

  • Hash索引的底层数据结构就是hash表,它适用于等值查询,不适用于范围查询;
  • Hash索引没有办法实现排序,但是B+树索引就有这方面的优点;
  • Hash索引不支持多列复合索引的最左前缀规则,如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题
1、B树结构

B树又叫多路平衡搜索树,一颗m叉的B树具有如下特性:

  • 每个节点最多有m-1个关键字
  • 根节点最少可以只有1个关键字。非根节点至少有m/2个关键字。
  • 每个节点中的关键字都按照从小到大的顺序排列,每个关键字的左子树中的所有关键字都小于它,而右子树中的所有关键字都大于它。
  • 所有叶子节点都位于同一层,或者说根节点到每个叶子节点的长度都相同。
  • 每个非叶子节点有n个key与n+1个指针组成,其中m/2向上取整-1<=n<=m-1。

以5叉B树为例,key的个数范围就是:2<=n<=4。当n>4的时候,中间节点分裂成父节点,两边结点做子节点。

这里我们以插入C N G A H E K Q M F W L T Z D P R X Y S数据为例。

【插入过程】

B树的插入,我们只需要记住一个规则:判断当前结点key的个数是否小于等于m-1,如果满足,直接插入即可,如果不满足,将节点的中间的key将这个节点分为左右两部分,中间的节点放到父节点中即可。并且在插入中是根据关键字大小来决定一个关键字位置的。

(1)插入前4个字母:C N G A,结点key的个数<=4,key只需要按字典序排好位置就可以了

(2)插入H ,此时n>4了,中间的结点G应该分裂成为父节点,其余结点按大小分裂到两边做子节点

(3)插入E K Q 不需要分裂

(4)插入M,中间元素M分裂到父节点

(5)插入F W L T不需要分裂

(6)插入Z,中间节点T分裂到父节点

(7)插入D时,中间节点D分裂到父节点,之后接着插入P R X Y都不会导致节点分裂

(8)最后插入S,NPQR节点个数>4了,中间接单Q向上分裂,但是分裂后父节点的关键字个数也大于4了,此时父节点中间元素M再次分裂

至此,B树的构建就完成了,B树相对于二叉树来说,B树的搜索效率更高,因为兑入同等两级的数量,B树的层数更少,因此比较的次数就越少。

【查找过程】

如果要找数据项F,那么首先会把M节点通过一次IO从磁盘加载到内存中,通过二分查找发现F比M小,因此需要到左边的结点去找,于是先把磁盘块D,G加载到内存中,之后又通过有通过二分查找确定F是在D和G之间,于是发生第二次IO把E,F加载到内存中,同时做二分查找,找到F,结束查询。总计3次IO。

然而真实的情况是,3 层的 B+树可以表示上百万的数据,如果上百万的数据查找只需要三次 IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次 IO,那么总共需要百万次的 IO,显然成本非常非常高。

2、B+树结构

B+树是B树的变种,他与B树的区别如下:

  • B+树有两种类型的节点:内部结点(也称索引结点)和叶子结点。内部节点就是非叶子节点,内部节点不存储数据,只存储索引,数据都存储在叶子节点。
  • 内部结点中的key都按照从小到大的顺序排列,对于内部结点中的一个key,左树中的所有key都小于它,右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
  • 每个叶子结点都存有相邻叶子结点的指针,叶子结点本身依关键字的大小自小而大顺序链接。
  • 父节点存有右孩子的第一个元素的索引。

下图所示就是一个B+树结构,上面两层节点都不存数据的,只是一个索引,只在最后一层叶子节点存数据。

3、MySQL中的B+树

MySQL索引数据结构对经典的B+树进行了优化。在原来B+树的基础上,增加了一个指向相邻叶子节点的链表指针,就形成了顺序指针的B+树,提高了区间访问性能。

MySQL中的B+树索引结构示意图:

InnoDB默认使用的就是B+树索引,B+树只在叶子节点存放数据,而中间的结点只是索引,其中在叶子节点可以存放主键的值,或者是整行的数据。
在 InnoDB 里,索引B+树的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而 索引B+树的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引。

思考:为什么B+树更适合实际操作系统的文件索引或数据库的索引?

答:

(1)B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点
的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

(2)B+树的查询效率更稳定。

由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当

三、MySQL的索引分类

索引从数据存储方式上说,分成两类:聚簇索引和非聚簇索引

从功能上说,分为6种:单值索引、唯一索引、主键索引、复合索引、外键索引和全文索引

1、聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语“聚簇”表示数据行和相邻的键值聚簇的存储在一起。如下图,左侧的索引就是聚簇索引,因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引和非聚簇索引各自有如下存储特点:

  • 聚集索引。表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。
  • 非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。

聚簇索引的好处
按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不不用从多个数据块中提取数据,所以节省了大量的IO操作。

聚簇索引的限制

对于 mysql 数据库目前只有 InnoDB 数据引擎支持聚簇索引,而 Myisam 并不支持聚簇索引。由于数据物理存储排序方式只能有一种,所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是该表的主键。

为了充分利用聚簇索引的聚簇的特性,所以 innodb 表的主键列尽量选用有序的顺序 id,而不建议用无序的 id,比如 uuid 这种。

2、单值索引、唯一索引、主键索引、复合索引、外键索引、全文索引
2.1 单值索引

概念:即一个索引只包含单个列,一个表可以有多个单列索引。

语法:

创建表
CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT , customer_no VARCHAR(200), customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
建一个单值索引:
create index idx_customer_name on customer(customer_name);
2.2 唯一索引

概念: 索引列的值必须唯一,但允许有空值

语法:

创建表
CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT , customer_no VARCHAR(200), customer_name VARCHAR(200),
PRIMARY KEY(id),KEY (customer_name)
);
建一个单值索引:
create unique index idx_customer_no on customer(customer_no);
2.3 主键索引

概念: 特殊的唯一索引,不允许有空值。设定为主键后数据库会自动建立索引,innodb为聚簇索引。主键索引会随着表的创建而创建。

2.4 复合索引

概念:一个索引中包含多个列。

语法:

创建表
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200), customer_name VARCHAR(200),customer_age int,PRIMARY KEY(id),KEY (customer_name)
);
建一个单值索引:
create index idx_customer on customer(customer_no,customer_name,customer_age);

在mysql建立复合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从复合索引的最左边开始匹配。比如上面这个索引语句其实就是创建了(customer_no)、(customer_no,customer_name)以及(customer_no,customer_name,customer_age)是三个索引。简单来说复合索引有以下几个好处:

(1)**可以减少开销。**每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!

(2)覆盖索引。MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

(3)效率高。索引列越多,通过索引筛选出的数据越少。

四、索引的设计原则

索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑复合这些原则,便于提升索引的使用效率,更高效的使用索引。

  • (1)对查询频次高,且数据量适量的表建立索引。一般来说,小表使用全表扫描更快,中大表才使用索引。超级大表索引基本无效。
  • (2)索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用,过滤效果最好的列的组合。
  • (3)尽量使用唯一性高的字段作为索引,字段区分度越高,索引的效率越高。
  • (4)索引可以有效的提高数据查询的效率,但事事都是物极必反的,过多的索引不仅会占用大量的磁盘空间,还会增加数据库系统维护索引的代价。
  • (5)使用短索引,索引的字段的内容应该尽可能少,不然索引也会占用过多的磁盘空间。
  • (6)利用最左前缀原则,N个列组合而成的复合索引,相当于创建了N个索引,如果查询时where子句中使用了组成该索引的前几个字段,那么查询效率会大大提升。

留言区

还能输入500个字符