MySQL综合知识汇总

最新推荐文章于 2024-08-06 15:20:26 发布

哟哟之名

最新推荐文章于 2024-08-06 15:20:26 发布

阅读量209

点赞数

分类专栏： MySQL数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43646427/article/details/113960222

版权

MySQL数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、逻辑架构图

MySQL逻辑架构图

二、MySQL架构各层职责

连接层：连接处理、授权认证及相关的安全方案。
服务层：MySQL对自身的备份、安全恢复、主从复制等；对SQL进行解析、转换、优化、缓存等。
引擎层：负责数据的存储以及提取方式，不同的存储引擎拥有不同的功能。例如使用MyISAM存储引擎创建的表在存储层的格式为（.frm文件保存表的定义-是服务器的，不是引擎的；.MYD保存表的数据；.MYI是表的索引文件）。
存储层：数据存储层，结合文件系统对数据进行存储。

三、一条SQL语句在各层的执行顺序

mysql 客户端通过协议与 mysql 服务器建连接，发送查询语句，先查询缓存，如果命中，直接返回结果；否则进行语句解析、优化以及执行。
执行器与存储引擎交互的细节示意图如下（MVCC原理涉及到此处的undo log、read view、锁），这篇MVCC讲的很透彻

四、上图交互细节详解

为方便理解，我们模拟一条sql进行详述（以InnoDB引擎举例）：

update t_user set username='a' where id=1;

首先去缓冲池查询，是否存在这条sql（缓冲池类似于redis，以key-value形式存储，简单理解key为sql语句，value为数据页地址），关于缓冲池的淘汰策略LRU（最近最少使用）以后再详述。如果id=1的数据没有在缓冲池，那么就会在磁盘中找到它并对其加一把行锁（排它锁）。
把id=1这行记录的值写入undo log日志文件（方便事务回滚）。
在缓冲池对这条记录的值进行修改。
将修改后的值记入redo log buffer（这是另一个缓存，也类似于redis）。
将redo log buffer中的数据写入redo log日志文件。
将本次修改操作写入到bin log日志文件（bin log有3种模式：STATMENT - sql语句、ROW - 数据行、MIXED - sql语句+数据行）。
将本次修改操作在bin log中的位置，bin log文件名写入到redo log，并添加commit标记。然后将数据磁盘上的这条数据进行解锁。
如果数据库在commit时宕机，重启后mysql会根据redo log先将数据恢复到缓冲池，然后会通过一个IO线程随机的刷盘到数据磁盘进行修正。

五、B+Tree数据结构

B+Tree：数据只在叶子节点存储，非叶子节点只存储id与指针。

InnoDB存储引擎默认的数据页大小为16KB，也就是上图的磁盘块大小为16KB。
一般表的主键类型为int或bigint；指针大小也一般为4B或8B，其实也就是主键；一行数据data我们假设大小为1KB；
那么一个页中大概存储 16KB/(主键8B+指针8B)=1K 个键值。一个高度为3的B+Tree结构为：第一层是主键+指针=1K，第二层是主键+指针=1K，第三层是主键+指针+数据=16 KB/ 1KB=16，这样B+Tree相当于维护了1024 * 1024 * 16 ≈ 1700w条记录。

聚簇索引（主键索引）：叶子节点存储的是一整行记录，将数据与主键ID放到了一起，找到ID也就找到了数据。
非聚簇索引（辅助索引）：叶子节点只存储指定的字段数据以及主键ID值。

六、数据在磁盘上是如何存储的？

计算机知识小Tips：
1、处理器、操作系统、应用软件选择合适的型号进行搭配才能发挥最大功效。
2、处理器：32位CPU - 能在单位时间内一次处理的二进制数据是32bit，也就是4B。

地址总线：计算机最小存储单元是1B，假如CPU有 n 根地址总线，那么对应的寻址空间为2ⁿB；例如一个CPU拥有32根地址总线（一般情况下32位的CPU的地址总线是32位，也有部分32位的CPU地址总线是36位的，比如用做服务器的CPU），那么提供的可寻址物理地址范围为 2³²=4GB。自从64位CPU出现之后，一次便能够处理64位的数据了，其地址总线一般采用的是36根或者40根（即CPU能够寻址的物理地址空间为64GB或者1TB）。CPU访问任何存储单元必须知道其物理地址。

3、操作系统：32位的windows操作系统，其逻辑地址编码采用的地址位数是32位的，那么操作系统所提供的逻辑地址寻址空间是4GB。而在intel x86架构下，采用的是内存映射技术(Memory-Mapped I/O, MMIO)，也就说将4GB逻辑地址中一部分要划分出来与BIOS ROM、CPU寄存器、I/O设备这些部件的物理地址进行映射，那么逻辑地址中能够与内存条的物理地址进行映射的空间肯定没有4GB了。

用户在使用计算机时能够访问的最大内存不单单是由CPU地址总线决定的，还需要考虑操作系统的实现。实际上用户在使用计算机时，进程所访问到的地址是逻辑地址，并不是真实的物理地址，这个逻辑地址是操作系统提供的，CPU在执行指令时需要先将指令的逻辑地址变换为物理地址才能对相应的存储单元进行数据的读取或者写入（注意逻辑地址和物理地址是一一对应的）。

4、好了，现在开始说正事，数据在磁盘上到底是怎么存储的？

简单理解为：B+Tree上一层节点对应磁盘上的一个簇。
磁盘示意图如下：硬盘表面会涂一层磁粉，进行数据写入时，磁头线圈上加电，在周围产生磁场，磁化磁盘上的磁粉，电流的方向不同，所以磁场的方向也不同，这样就能让磁粉表示0/1。进行数据读取时，磁头线圈切割磁场线产生感应电流，磁性材料的磁场方向不同，所以产生的感应电流方向也不同，从而就能读取到之前存储的0或1。
一般来说一个扇区能存储512B的数据；而Mysql页大小默认是16KB，也就是说mysql与磁盘进行1次交互所读取的数据大小为16KB。

A是磁道，红色的同心圆区域；它不是线，是有面积的！
B是扇面，蓝色区域。
C是扇区，红色与蓝色重叠的区域；也是磁盘存储的最小单位，通常是512KB。
D是簇，绿色区域；由2ⁿ个连续扇区组成。上面说的MySQL数据页就是这里的簇。
查询数据时间 = 寻道时间（磁头臂移动到对应磁道） + 盘片旋转时间 + 数据传输时间
举个例子：一个7200rpm的磁盘，转速为7200转/分钟，即120转/秒。硬盘旋转一周所需要的时间为1/120 s≈85 ms。一般我们认为硬盘旋转半周即可找到数据所在的扇区，考虑到寻道时间与传输时间，近似的认为查找时间就为85ms（一般来说查找一次数据的平均时间在90~110ms之间）。一台2015年的MacPro（CPU配置：2.7 GHz Dual-Core Intel Core i5）在1s内可以进行近5亿次计算，但是与磁盘却只能交互120次，硬件条件短时间内无法再提升，因此只能减少磁盘访问次数来降低数据访问时间，由此SQL语句的编写就显得尤为重要了！