MySQL进阶（三）MySQL索引

四、MySQL索引

（以下部分为面试常问的数据库优化问题相关）

4.1 计算机硬盘原理

4.1.1 磁盘IO问题

MySQL的数据一般以文件形式存储在磁盘上，检索需要磁盘IO操作。与内存不同，磁盘IO存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

4.1.2 磁盘的一些概念

4.1.2.1 盘片、片面和磁头

硬盘中一般会有多个盘片组成，每个盘片包含两个面，每个盘面都对应地有一个读/写磁头。受到硬盘整体体积和生产成本的限制，盘片数量都受到限制，一般都在5片以内。盘片的编号自下向上从0开始，如最下边的盘片有0面和1面，再上一个盘片就编号为2面和3面。

如下图：

4.1.2.2 扇区和磁道

下图显示的是一个盘面，盘面中一圈圈灰色同心圆为一条条磁道，从圆心向外画直线，可以将磁道划分为若干个弧段，每个磁道上一个弧段被称之为一个扇区（图践绿色部分）。扇区是磁盘的最小组成单元，通常是512字节。（由于不断提高磁盘的大小，部分厂商设定每个扇区的大小是4096字节）

4.1.2.3 磁头和柱面

硬盘通常由重叠的一组盘片构成，每个盘面都被划分为数目相等的磁道，并从外缘的“0”开始编号，具有相同编号的磁道形成一个圆柱，称之为磁盘的柱面。磁盘的柱面数与一个盘面上的磁道数是相等的。由于每个盘面都有自己的磁头，因此，盘面数等于总的磁头数。 如下图

4.1.3 磁盘容量计算

存储容量 ＝ 磁头数 × 磁道(柱面)数 × 每道扇区数 × 每扇区字节数

图3中磁盘是一个 3个圆盘6个磁头，7个柱面（每个盘片7个磁道） 的磁盘，图3中每条磁道有12个扇区，所以此磁盘的容量为：

存储容量 6 * 7 * 12 * 512 = 258048

每个磁道的扇区数一样是说的老的硬盘，外圈的密度小，内圈的密度大，每圈可存储的数据量是一样的。新的硬盘数据的密度都一致，这样磁道的周长越长，扇区就越多，存储的数据量就越大。

4.1.4 磁盘读取相应时间

1. 寻道时间：磁头从开始移动到数据所在磁道所需要的时间，寻道时间越短，I/O操作越快，目前磁盘的平均寻道时间一般在3－15ms，一般都在10ms左右。
2. 旋转延迟：盘片旋转将请求数据所在扇区移至读写磁头下方所需要的时间，旋转延迟取决于磁盘转速。普通硬盘一般都是7200rpm，慢的5400rpm。
3. 数据传输时间：完成传输所请求的数据所需要的时间。
   小结一下：从上面的指标来看、其实最重要的、或者说、我们最关心的应该只有两个：寻道时间；旋转延迟。

读写一次磁盘信息所需的时间可分解为：寻道时间、延迟时间、传输时间。为提高磁盘传输效率，软件应着重考虑减少寻道时间和延迟时间。

4.1.5 块/簇

概述

磁盘块/簇（虚拟出来的）。 块是操作系统中最小的逻辑存储单位。操作系统与磁盘打交道的最小单位是磁盘块。
通俗的来讲，在Windows下如NTFS等文件系统中叫做簇；在Linux下如Ext4等文件系统中叫做块（block）。每个簇或者块可以包括2、4、8、16、32、64…2的n次方个扇区。

为什么存在磁盘块？

读取方便：由于扇区的数量比较小，数目众多在寻址时比较困难，所以操作系统就将相邻的扇区组合在一起，形成一个块，再对块进行整体的操作。

分离对底层的依赖：操作系统忽略对底层物理存储结构的设计。通过虚拟出来磁盘块的概念，在系统中认为块是最小的单位。

4.1.6 page

操作系统经常与内存和硬盘这两种存储设备进行通信，类似于“块”的概念，都需要一种虚拟的基本单位。所以，与内存操作，是虚拟一个页的概念来作为最小单位。与硬盘打交道，就是以块为最小单位。

4.1.7 扇区、块/簇、page的关系

1. 扇区： 硬盘的最小读写单元
2. 块/簇： 是操作系统针对硬盘读写的最小单元
3. page： 是内存与操作系统之间操作的最小单元。

扇区 <= 块/簇 <= page

4.1.8 计算机读取数据流程

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。

为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点：

​ 1）首先必须找到柱面，即磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，

​ 2）然后目标扇区旋转到磁头下，即磁盘旋转将目标扇区旋转到磁头下。这个过程耗费的时间叫做旋转时间。

4.1.9 局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放 入内存。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存 储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统 会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

下图是计算机硬件延迟的对比图，仅供参考：

4.2 MySQL数据结构的选型

在数据库文件存储在磁盘时，为了提升查询效率，一定会选用合适的数据结构进行文件存储。

4.2.1 数组和链表

肯定不能选，这种最基本的数据结构，各自的劣势太明显。

数据库对查询要求的最高的，所以链表这种查询必须全表遍历的基本数据结构不能使用

数组这种结构在添加数据时成本太大，插入数据时太过频繁

4.2.2 hash

类似于hashmap，速度快，但是只要是hash就会产生无序的问题，不常用

4.2.3 树

比较合理。

二叉查找树，在查找一个数据时，二叉树是读取根结点，小则从左找，大则从右找，每次读取一个数据。没有办法合理利用局部性原理与磁盘预读，IO次数太多。其次树的层次偏高，所以不合适。

每次读多个数据，每个节点存多个数据的结构就只有B-数和B+数。

4.2.4 B-树

B-数，B指balance，B树是一种多路自平衡的搜索树

它类似普通的平衡二叉树，不同的是B-树允许每个节点有更多的子节点。

B-树特点：

1. 所有键值分布在整棵树中；
2. 任何一个关键字出现且只出现在一个节点中；
3. 搜索有可能在非叶子节点结束；
4. 在关键子全集内做一次查找，性能必进二分查找。

4.2.5 B+树

默认是主键，如果没有主键则使用唯一索引，唯一索引也没有则使用rowid，行号。

所以一定要建立主键。

B+树是B-树的变体，也是一种多路查找树，与B-树区别在于：

1.所有关键字存储在叶子节点（非叶子节点并不存储正在的data）

2.为所有叶子节点增加了一个链指针

4.2.6 为什么使用B-/B+ Tree

红黑树等数据结构也可以来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。MySQL是基于磁盘的数据库系统，索引往往以索引文件的形式存储在磁盘上，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，索引的结构组织要尽量减少过程中磁盘I/O的存取次数。为什么使用B-/+Tree，还跟磁盘存取原理有关。

MySQL（InnoDB引擎）将记录安装page（页）的方式进行管理，每页大小默认为16k（可修改）.linux，默认页大小为4k。

4.2.7 为什么使用B+树

1、B+树更适合外部存储，由于内节点无data域，一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确，也意味着B+树单次磁盘IO的信息量大于B-树，效率高；

2、MySQL是一种关系型数据库，区间访问是常见的一种情况，B+树叶子节点增加的链指针，加强了区间访问性，可使用在范围区间查询等，而B-树每个节点key和data在一起，则无法区间查找。

4.3 为什么要使用索引

1、MySQL的数据是存在磁盘上的，磁盘的读取速度是比较慢的，使用主键的读取方式会很快，当查询数据太大的非主键字段是会特别耗时；

2、建立在主键上的B+数结构可以快速查询需要的数据，其他的字段查询一定要这么慢？

4.4 索引的数据结构

4.4.1 索引的数据结构

4.1.2 有了索引怎么查询

• 从索引里自上而下查询
• 走到叶子节点查询到id
• 根据id去聚簇索引中查找真正的数据，这个过程叫回表
• 如果你要的数据索引都有了不需要回表，就叫索引覆盖

4.5 索引的分类和创建

索引的命名：

普通索引：idx字段命名

唯一索引：ux字段名

使用的数据库表

4.5.1 聚簇索引和非聚簇索引

**聚簇索引：**将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

**非聚簇索引：**将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。