MySQL索引

 

目录

1 、为什么要用索引

1. 1内存地址是随机的会带来什么问题呢？？？

1.2.总结

2、什么是索引

3、简单说说HASH索引

4、 非HASH索引为什么选用的数据结构为B+树?

4.1 为什么不是其他数据结构

4.2为什么是B+树而不是B树呢？

4.2.2 B+树数据结构，以及为什么选择B+树

4.2.3 一个错误的观点：B树和B+树的区别之一为B树的非叶子节点存储数据

4.3 简单猜想：为什么索引中每个节点在内存中的地址是随机的

5 、MySQL索引的体现形式

5.1 MyISAM存储引擎

5.2 InnoDB存储引擎

5.2.2 为什么InnoDB的非主键索引绑定的是该索引对应的主键值★★★

5.2.3 InnoDB表创建主键应该注意什么

5.2.4 InnoDB主键索引再思考 ★★★

---

1 、为什么要用索引

在实际业务中，当一张数据库表建好后，里面的数据往往是在不同时间点进行插入的，因此每张表里一行行的数据在磁盘中并不是按照顺序依次排列的。也就是说对于一张数据库表而言，它每一行的内存地址都是随机的，如下图所示：

1. 1内存地址是随机的会带来什么问题呢？？？

 

相信每个人都知道，cpu在读取磁盘的数据时，都是按照N*页（64位系统，一页为4K）来读取的 — 即预读（之所以有预读是因为在磁盘上的数据通常遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据，这就是所谓的“局部性原理”，它表明提前加载是有效的，确实能够减少磁盘IO）。但是这个N肯定不能很大，因为太大肯定就会影响读取效率，mysql的默认大小为4，即每次读取16K的数据。

而由于表中每一行的内存地址都是随机的，那即使cpu读取数据时有预读，也会大概率无法一次读取到表中的多条数据。因此假如我们要查询上图中col2 = 23的那一行数据时，极端情况下就要经历7次磁盘I/O：

•    取出第一行的地址，经历一次I/O，拿着第一行的数据，判断col2是否为23
•    再取出第二行的地址，经历一次I/O，拿着第二行的数据，判断col2是否为23 … 直到读取到最后一个地址，并查找的满足条件的数据。

1.2.总结

        也就是说在没有索引的情况下想查询某张数据库表里满足条件的数据，需要遍历整张表，并进行多次I/O。而众所周知的是I/O是非常消耗时间的，因此想要快速查询表中的数据索引是必不可少的。

2、什么是索引

 

人们一般会将索引比作书的目录。但是由于数据库表的行数往往会达到十万、百万的量级。因此索引假如仅仅只如书的目录一样，要想从十万、百万量级的索引中找到目标索引，再根据目标索引找到真实的数据，也需要很多次I/O。

那到底什么是索引呢？mysql官方给出的定义如下：

索引是帮助MySQL高效的获取数据的数据结构。

这句话可以这样来理解：

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

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

这里要注意的是：一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。 — 在介绍mysql存储引擎的时候还会再提及。

 

 

3、简单说说HASH索引

 

众所周知，mysql有两种索引：HASH索引和BTREE索引（BTREE索引的底层数据结构为B+Tree）。

HASH索引本文不过多去说，仅仅指出如下几点：

（1）如果键值唯一，哈希索引明显有绝对优势

（2）无法完成范围查询检索

（3）无法利用索引完成排序，以及like ‘xxx%’ 这样的部门模糊查询

（4）不支持多列联合索引

（5）因为会有哈希碰撞问题，所以当发生哈希碰撞时，查询效率会降低

 

4、 非HASH索引为什么选用的数据结构为B+树?

 

 

4.1 为什么不是其他数据结构

 

mysql底层构建索引并没有采用第2小结示例中所说的二叉树，也没有采用对大家来说可能相对比较熟悉的平衡二叉树如：AVL树、红黑树。这是为什么呢？通过下面几张图相信大家肯定会有所感悟：

 

首先要明确的一点是，每个索引节点在磁盘中的位置也是随机的。 这句话在介绍完B Tree、B+Tree数据结构时，我会展开说一下我的想法。

看到上图相信大家应该都可以看出来，二叉树、AVL树或者红黑树都至少有两个缺点：

• 每个节点都只有一个索引值，而cpu每次从磁盘上至少读取1页（64位系统为4K）的数据，但是由于每个节点在磁盘中的位置是随机的，所以有很大概率cpu每次仅仅只能从磁盘读取到一个索引值。—> 没有很好的利用cpu与磁盘交互的特性。
• 正因为每个节点只能有一个索引值，所以无论二叉树、AVL树还是红黑树，当数据量达到十万、百万量级时，树的高度都会变得很高，从而会发生多次磁盘I/O —> 极端情况下，如果索引正好是顺序构建的，那二叉树就将成为链表（非平衡树的原因），AVL树和红黑树的高度也会变得很高很高。

正是基于这样的弊端，mysql并没有采用二叉树、AVL树或者红黑树，而是采用了可以在一个节点存放更多数据的多叉平衡树。

 

4.2为什么是B+树而不是B树呢？

 

说到多叉平衡树，最先想到的是B树，度为3时B树的结构如下：

 

度为m的B树，满足如下条件：

• 每个节点最多拥有 m 个子树
• 根节点至少有2个子树
• 分支节点至少拥有 m/2 颗子树（除根节点和叶子节点外都是分支节点）
• 所有叶子节点具有相同的深度、每个节点最多可以有 m-1 个key，并且以升序排列

B树与二叉树、AVL树或者红黑树相比，可以说比较完美的解决了4.1中提到的两个弊端，但是mysql底层却也没使用B树。

 

4.2.2 B+树数据结构，以及为什么选择B+树

 

度为3时B+树的结构如下：

将B+树与B树进行对比，可以发现区别有三处：

• B+树的父节点和子节点之间存在数据冗余
• 正是因为父节点和子节点之间的数据冗余，所以叶子节点包含了所有非叶子节点的值，并且各个数值从左到右是按序排列的
• 叶子节点之间，从左到右都有一个指针进行连接 —> 即叶子节点之间是一个链表结构，并且其实mysql底层用到的B+树数据结构，叶子节点之间是一个双向链表。

其实我觉得上诉三点主要是来解决一个问题：

范围查找的问题。

为什么这么说呢？

其实很好理解：

• 如果使用B树，以4.2.1中图为例，假如我们想获得到15 —> 28之间的数据，就不得不先读取磁盘块1、磁盘块2、磁盘块7的数据—>然后再倒过来走到磁盘1，从磁盘块1、磁盘块3、磁盘块8进行取数据，这样的话，不仅至少要经历5次I/O，还要经历比较复杂的计算过程。
• 但是如果使用B+树，以上图为例，假如我们想获得20 —> 28或者34或者56或者…之间的数据，只需要读取磁盘块1，磁盘块2，磁盘块7的数据，然后根据叶子节点的指针直接找到磁盘块8、磁盘块9、磁盘块10就可以了。 与B树相比，着实减少了在树的不同高度间来回进行数据查找的过程，肯定也大大提高了查询的效率。

我想肯定正是因为上诉原因，mysql中BTree索引底层的数据结构才最终选择了B+树。

4.2.3 一个错误的观点：B树和B+树的区别之一为B树的非叶子节点存储数据

看过一些资料，有人说B+树和B树还有一个重要的区别在于：

B树的每个非叶子节点都会存储数据，而B+树不会，正是因为这样，所以B+树每一个非叶子节点可以存储更多的索引，从而降低树的高度。

我觉得这肯定是不正确的。因为单从数据结构而言，无论是B树还是B+树，都未规定它们哪个节点必须要存数据，哪个节点不能存储数据。

之所以出现这种错误的说法，我认为这些人其实脑子里是先有了对于innodb引擎的认知，然后回过头来再看B树和B+树才得到了这样一种错误的结论。 即：

• B+树的叶子节点之间存存在双向链表，节点中存放了前后节点的位置信息，这样更加有利于进行范围查询
• 按照innodb引擎来说，索引和数据是存储在一起的，因此为了保证每个索引和其对应的数据都在一起，所以B树的每个节点上都必须既存索引又存数据，还得存指向下一个节点的指针。这样的话，每个节点的索引数必然就会较少，B树的高度就会变高。
• 而B+树由于叶子节点包含了所有的索引，因此只需要将数据和叶子节点的索引值放在一起就行了。这样非叶子节点就不用存储数据，只存储索引关键字和指向下一个节点的指针，那非叶子节点所包含的索引数就会很多，自然B+树的高度就会变得相对较低。

这种解释看着很完美，但在我看来，颠倒了因果关系，所以我认为这个观点肯定是错误的。 

4.3 简单猜想：为什么索引中每个节点在内存中的地址是随机的

之所以写这一小结，是因为在写4.1小结时，我突然有个很大的疑问：

我们知道对于B+树索引来说，每次I/O会读取4页 = 16K的数据，而这4页往往全是索引数据，也就是说mysql在建立索引时肯定是以4页为一个基本单位去申请内存地址的。因为只有这样的话一次I/O才能正好读取4页的索引数据。

想到这里我就有一个问题，为什么二叉树、AVL或者红黑树情况下不能也预先以4页为一个基本单位去申请内存呢？这样的话，是不是二叉树、AVL树或者红黑树的数据结构在mysql中也有一定的用武之地。

其实很快我就想明白了，能是能，但是对于二叉树、AVL或者红黑树来说，虽然这样可以一次I/O读取到更多的索引值，但是它们左右分叉太多，而且树的高度实在太高，所以它们还是无法在mysql中有用武之地。

5 、MySQL索引的体现形式

 

索引其实是由mysql的存储引擎来实现的，mysql的存储引擎被提到的最多的就是MyISAM和InnoDB，本文接下来将讲诉一下索引在这两种引擎中的具体体现形式。

 

5.1 MyISAM存储引擎

 

建表语句如下：

CREATE TABLE `tb_user_myisam` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 指定使用MyISAM存储引擎

该表对应的数据库文件如下：

注意：我本机用的mysql版本为8.0.13，该版本里并没有frm文件

MYD文件为数据文件

MYI文件为索引文件

sdi文件存储了数据库表结构、数据库版本等信息

frm文件为结构文件

索引组织形式如下（非聚集索引）：

MyISAM的主键索引和非主键索的组织形式一样，都采用了B+Tree的数据结构，都是在子节点存储数据的物理内存地址。

 

 

5.2 InnoDB存储引擎

建表语句如下：

CREATE TABLE `tb_user_innodb` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 指定使用InnoDB存储引擎

该表对应的数据库文件如下：

注意：我本机用的mysql版本为8.0.13，该版本里并没有frm文件

该文件包含数据文件和索引信息

索引组织形式如下（非聚集索引）：

InnoDB的主键索引底层用的B+Tree数据结构，但是叶子节点包含了该索引对应的整行数据，也就是说找到主键索引，就找到了该行数据 —》InnoDB的主键索引也称为聚集索引。

InnoDB的非主键索引底层也用的B+Tree数据结构，但是叶子节点并没有该索引对应的整行数据，也没有包含该索引对应数据的物理内存地址，它其实包含的是该索引对应的主键值。

 

5.2.2 为什么InnoDB的非主键索引绑定的是该索引对应的主键值★★★

我觉得读到这里应该至少有如下两条疑问。

疑问一：为什么不像主键索引一样直接绑定索引整行数据？

疑问二：感觉就算不绑定整行数据，绑定该索引对应数据的物理地址也行啊？

• 对于疑问一，我的理解：

• • 首先索引本身就很大，如果一张表建立多个索引的话，如果每个索引都绑定该索引对应的整行数据的话，那索引就更大了 —》 即这样可以减少数据文件所占的磁盘空间。
  • 其次，更新、删除、增加数据都会触发索引的变化 —》如果绑定整行数据，势必会降低增、删、改的效率
  • 最后InnoDB是支持事务的，如果非主键索引也绑定整行数据，就会造成一份数据有多个副本，在事务回滚或提交的过程中有可能会造成数据的不一致性。

• 对于疑问二，我的理解：

• • 首先内存地址一般为一个长字符串，其大小很大几率会比主键值大 —》 即使用内存地址更占磁盘空间
  • 其次，一个重要的原因应该是，非主键索引所对应的数据，大概率内存地址是随机的，因此在进行范围查找时，就会经历多次I/O — 而每次I/O读取到的4页数据肯定不会全是该表的数据。 而如果按照mysql现有设计的话，当拿着该索引对应的主键值去主键索引里查询数据时，访问到叶子节点后，每次I/O获得到的4页数据将都是该表的数据，那这个效率肯定就比较高了。

 

5.2.3 InnoDB表创建主键应该注意什么

 

5.2.3.1 首先应该明确，无论怎样InnoDB对应的表都有主键

看一下mysql官网的一段介绍：   当你使用了InnoDB引擎时，无论你建还是不建，mysql都会为你创建一个主键。 并且会为该主键创建聚集索引。

 

5.2.3.2 InnoDB对应的表建议使用整型自增主键

 

为什么是整型：

（1）相比于浮点型，整型占的内存更小，可以在非叶子节点存储更多的索引，从而降低树的高度

（2）从B+树的数据结构来看，如果主键索引为非数字类型，在插入数据时会更容易破坏索引树的结构,有兴趣的可以在下面的网站上模拟一下字符串类型构建B+树的过程：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

为什么是整型自增：

这一点也要结合B+树数据结构的构建过程来考虑，如果是非自增的话，肯定会更容易破坏索引树的结构。

一旦索引树的结构被破坏，那肯定就会重新调整构建索引树 —> 从而势必会影响数据的插入效率，因此基于以上几点再加上 5.2.3.1的内容，强烈建议在使用InnoDB引擎时，一定要为表建立一个主键、且该主键最好是整型自增的。

 

5.2.4 InnoDB主键索引再思考 ★★★

 

理解了本文所讲的以上内容的话，其实我觉得很自然而然的会进行以下思考：

（1）为了尽可能不破坏索引树的结构，在进行删除操作时，最好不要真的把整行数据删掉，而是进行逻辑删除

（2）使用InnoDB引擎创建的表，其实数据是和主键索引的叶子节点在一起的。

（3）真实工作中我们往往会遵循如下规则去创建主键：

这种情况下主键往往是没有实际业务意义的，且当数据量达到一定量级后可能会对当前表进行分库或分表。所以实际生产中，我们几乎甚至可以说肯定不会直接拿主键作为查询条件。

那这就会很容易先出现这样一种情况：

先通过非主键索引树定位到要查询数据的主键索引值，再拿着主键索引值去主键索引树中真正定位到要查询的数据 —> 即很容易出现遍历两个索引树的情况 —> 有的资料上称这为回表查询！！！

当发生回表查询时,肯定会降低数据库的查询效率！！！