如何查看mysql试图 mysql查看视图命令

转载

mob64ca140ee96c 2024-05-30 10:05:26

文章标签 如何查看mysql试图 mysql查看视图的结构采用什么命令数据主存时间复杂度 文章分类 MySQL 数据库

摘要：

上一篇(数据库(InnoDb)索引之底层数据结构实现)

继上一篇文章中讲到了常见的查询算法以及相对应的数据结构，今天就来讲解一下为什么大多数的文件系统、数据库引擎采用B/B+ Tree作为索引结构。

第一部分分析B/B+、二叉排序树、二叉平衡树、红黑树按不同数据结构的查询效率差异。

第二部分从主存(内存)和磁盘的存取速度差异、主存存取原理、磁盘存取原理等多个方面进行分析。

第三部分详细介绍InnoDB的索引实现。

第四部分将会介绍重头戏，为什么Innodb 采用B+树作为索引结构而不是平衡树、红黑树、B- Tree呢?

1 B/B+、二叉排序树、二叉平衡树、红黑树查询性能差异

继上篇文章中讲到了各种查询的算法和数据结构，今天这里进行一个简要的总结，方便大家进行对比。

1.1 二叉排序树

时间复杂度：

最好：O(log2n)

最坏：O(n)

平均：O(log2n)

缺点：及其不稳定。

1.2 二叉平衡树

时间复杂度：

最好：O(log2n)

最坏：O(log2n)

平均：O(log2n)

优点：稳定。

缺点：平衡条件苛刻，插入、删除等操作为了达到平衡，树进行旋转(四种旋转)开销比较大。

1.3 红黑树

时间复杂度：

最好：O(log2n)

最坏：O(log2n)

平均：O(log2n)

优点：稳定，平衡条件没有二叉平衡树那么严格，插入删除等操作进行的旋转次数较少。

缺点：只适用于内部排序，O(logn)的查询效率对于磁盘这些，依然不够快。

1.4 B/B+树

阶：m

时间复杂度：

最好：O(logmn)

最坏：O(logmn)

平均：O(logmn)

优点：稳定、O(logmn)的查找效率适用于磁盘等外部查找。

缺点：因为每个B/B+树的节点需要存储m个Key，所以每个节点都比较大，整个树占用的内存更大，所以内部查找不适用。

2 内存和磁盘的读写速度差异、主存读写原理、磁盘读写原理

2.1 内存和磁盘的存取速度差异

2.1.1 硬盘

读写速度：

50-90Mb/s - 5400转/秒
90-190Mb/s - 7200转/秒

2.1.2 主存(内存)

读速度：

60000Mb/s - DDR4/2666MHZ
57000Mb/s - DDR4/2400MHZ

写速度：

40000Mb/s - DDR4/2666MHZ
36000Mb/s - DDR4/2400MHZ

注：以上数据来自：https://news.mydrivers.com/1/447/447688.htm

2.1 主存(内存)读写原理

RAM(随机读写存储器)有两大类，一种称为静态RAM(Static RAM/SRAM)，SRAM速度非常快，一般用作一级、二级缓冲。另一种称为动态RAM(Dynamic RAM/DRAM)，DRAM保留数据的时间很短，速度也比SRAM慢，计算机内存就是DRAM的。下面就介绍一个简单的模型来说明DRAM的读写原理。

从上图可以看到，内存由一系列的超单元组成的二维矩阵，每一个超单元存储固定大小的数据。图1展示了一个 4 * 4 的内存基本模型，通过二维矩阵的行、列来定位到一个超单元的地址，然后进行读写。

如何查看mysql试图 mysql查看视图命令_如何查看mysql试图

图1

现在模拟内存的读写过程：

1 读：当CPU需要读取主存时，则通过存储控制器将地址信号放到地址总线(addr)上传给主存，主存读到地址信号后，解析信号并定位到指定超单元，然后将此存储单元数据放到数据总线(data)上，供其它部件读取。

2 写：当CPU写数据导内存中时，存储控制器将单元地址、数据分别放在地址总线和数据总线上，主存读取两个总线的内容，得到相应的地址和数据，写入到对应的超单元中。上面1中地址总线(addr)中的8代表有8根线，说明内存一次读写最多传递8bit位的数据(一个字节)。

性能分析：从上面的读写分析中可以看出，内存和系统的数据交互与读写次数呈正比，恰恰主存的读写都是电信号，没有机械操作，所以无论数据在二维矩阵的任何地方，都是不影响读写速度的。

2.2 硬盘读写原理

硬盘作为一种非易失性存储器、廉价适合资料的长久存储，所以文件、数据库等一般采用硬盘来存储数据。

索引一般是以文件的形式存储在硬盘上，索引检索就需要硬盘I/O操作，但是由于磁盘的自身结构，每次I/O操作都需要机械运动进行检索，因此硬盘的I/O成本是很高(费时)的。

下图2 就是硬盘一个盘面的结构图：

如何查看mysql试图 mysql查看视图命令_如何查看mysql试图_02

图2

盘面被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。

下图3 就是硬盘的整体架构图：

如何查看mysql试图 mysql查看视图命令_数据_03

图3

一个磁盘由大小相同且同轴的圆形盘片组成，每个盘片都有两个盘面，对应配有读写头。磁盘可以转动。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责读写一个磁盘的内容。磁头不能转动，但是主轴可以转动，每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的。

读写：系统需要磁盘读写数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。图4展示了一个盘面如何读写数据：

如何查看mysql试图 mysql查看视图命令_数据_04

图4

2.3 局部性原理与磁盘预读

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

当一个数据被用到时，其附近的数据也通常会马上被使用。
程序运行期间所需要的数据通常比较集中。

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

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

3 Mysql - InnoDb引擎的索引实现

InnoDb的数据文件本身就是聚集索引文件本身。所有的数据全部存储在主键(聚集索引)的叶子节点。也就是说，表文件本身就是一颗B+树。详细内容在上上篇笔记中有专门介绍过聚集索引。传送门

下图5就展示了在磁盘中一颗B+树的主键索引存储结构：

如何查看mysql试图 mysql查看视图命令_数据_05

图5

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(聚集索引)，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

4 为什么采用B+树作为索引结构而不是平衡树、红黑树、B- Tree呢?

4.1 时间复杂度对比

数据结构	平均时间复杂度	特点
平衡树	O(log2n)	磁盘下I/O次数太多，不合适
红黑树	O(log2n)	磁盘下I/O次数太多，不合适
B- Tree	O(logMn)	每个节点存储key、data，导致相同的节点大小下存储的Key较少，M值变小，时间复杂度变大，查询效率没有B+树高。
B+ Tree	O(logMn)	非叶子节点不存储data，只存储Key，相同的节点大小下，存储的Key个数越多，M的值越大，时间复杂度越小。

4.2 从磁盘页的角度分析B+树如何在磁盘中读写数据

在数据库中，基本的存储单位是页，一个页中可以存储多行记录。下图6展示了行、页、区、段、表空间的关系图：

如何查看mysql试图 mysql查看视图命令_数据_06

图6

在InnoDb中一个页的基本单位是16Kb。在一棵B+树中，每一个节点就是一个页，每次申请一个节点就是申请一个页空间。同一层的节点上通过页中的两个指针构成一个双向链表。(PS:在节点内部数据记录之间是一个有序的单向链表，系统通过索引拿到数据页之后，把数据页的数据加载到内存中然后进行二分查找找到具体对应的数据。)

下图7展示了磁盘中B+树的结构：

如何查看mysql试图 mysql查看视图命令_主存_07