MySQL系列之体系结构原理学习笔记

骑猪看日落 2021-09-02 10:53 362阅读 0赞

MySQL系列之体系结构原理学习笔记

引用来自官网的图，MySQL Server架构从上到下依次为网络连接层(Connectors)、服务层（MySQL Server）、存储引擎层(Plugable Storage Engines)、系统文件层(File System)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center]  
中文图示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 1]  
画思维导图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 2]

### 一、网络连接层（Connectors） ###

*  客户端连接器：处理客户端与mysql服务端的连接、授权认证、安全等，需要编写api、客户端驱动程序实现与服务端的连接
 *  支持的编程语言：java、C、Python、.Net等等，基本支持所有主流的编程语言

### 二、服务层（MySQL Server） ###

这一层是MySQL的核心组件，主要包括系统管理和控制工具(Management Services&Utilities)、连接池（Connection Pool）、SQL接口（SQL Interface）、解析器（Parser）、查询解析器（Optimizer）、缓存（Cache&Buffer）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 3]

*  连接池（Connection Pool）

负责存储和管理客户端与数据库的连接，一个线程管理一个连接

*  系统管理和控制工具(Management Services&Utilities)

系统管理和控制工具负责备份恢复、安全管理、集群管理等

*  SQL接口（SQL Interface）

用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结  
果。比如DML、DDL、存储过程、视图、触发器等。

*  解析器（Parser）

负责将请求的SQL解析生成一个"解析树"，然后根据一些MySQL规则进一步检查解析树是否合法

*  查询解析器（Optimizer）

当“解析树”通过解析器语法检查后，再通过优化器将其转化成执行计划，然后与存储引擎交互

*  缓存（Cache&Buffer）

MySQL的缓存机制由多部分组成，比如表缓存，记录缓存，权限缓存，引擎缓存等，查询语句一般先查缓存，缓存查不到才会进行处理，缓存的目的是避免硬解析，提供系统性能

### 三、存储引擎层（Plugable Storage Engines） ###

Mysql架构的存储引擎是设计得很灵活的，是一种Plugable的，可以支持拓展的，比如阿里内部就自行进行拓展以适应高并发，详情可以了解一下alisql

Mysql的事务回滚等处理是给存储引擎负责的，下面介绍一下几种常见的MySQL存储引擎：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 4]

*  InnoDB存储引擎  
    InnoDB存储引擎是mysql默认的存储引擎，也是使用最广泛的存储引擎，前面也说了，InnoDB是一种可以支持事务的存储引擎，InnoDB是mysql 5.1才开始出现的，之前名称是称为InnoDB plugin，直到被oracle收购mysql后，才正式命名为InnoDB。InnoDB采用MVCC来支持事务一致性和并发，并且实现了4个基本的隔离级别，默认级别是PREATABLE READ(可重复读)，并且通过间隙锁(next-key loking)策略来防止欢读的出现，
 *  MyISAM存储引擎  
    在MySQL5.1之前版本，MyISAM是默认的存储引擎，MyISAM提供了全文索引、压缩、空间函数等，但是MyISAM是不支持事务和行级锁的，MyISAM会将表存储在两个文件中，数据文件和索引文件，拓展名分别是.MYD和.MYI，MyISAM虽然没有行级锁，不过是可以支持表锁的，所以在一些业务系统还是可以做到并发控制的

<table> 
 <thead> 
  <tr> 
   <th>对比项</th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主外键</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>行表锁</td> 
   <td>使用表锁，操作一条记录就会锁住整张表，不适合做高并发操作</td> 
   <td>使用行锁，操作记录，只锁一行记录，适合做高并发处理</td> 
  </tr> 
  <tr> 
   <td>表空间</td> 
   <td>相对比较小</td> 
   <td>相对比较大</td> 
  </tr> 
 </tbody> 
</table>

*  Archive存储引擎  
    Archive存储引擎只支持INSERT和SELECT操作，在Mysql5.1版本之前不支持索引的，Archive引擎会缓存所有的写数据，利用zlib对写入的数据进行压缩，所以需要的磁盘/IO比MyISAM更少的，但是SELECT是全表扫描的，因此Archive表适合用于日志和数据采集类的应用，Archive引擎是支持行级锁的
 *  Blackhole存储引擎  
    Blackhole引擎没有存储机制，不做保存处理，但是服务器还是会记录Blackhole表的日志，所以可以用于复制数据到备库，或者用于记录日志
 *  CSV存储引擎  
    CSV引擎可以将普通的CSV文件作为MySql的表处理，可以在数据库运行时拷贝文件，将Excel表另存为CSV文件，然后就可以直接复制到数据库，不过这种表是不支持索引的
 *  Federated存储引擎  
    Federated引擎是访问其它服务器的代理，会 创 建 一 个 到 远 程 MySQL 服 务 器 的 客 户 端 连 接， 并 将 查 询 传 输 到 远 程 服 务 器 执 行， 然 后 提 取 或 者 发 送 需 要 的 数 据。
 *  Memory存储引擎  
    Memory引擎适用与需要快速访问数据，这些数据是保存在内存的，Memory支持hash索引，所以查询速度非常快，不过不支持BLOB或者TXT类型数据，是用表锁做并发的
 *  Merge存储引擎  
    Merge引擎是由多个MyISAM表合并而来的表，可以用于存储日志或者用于当数据存库
 *  NDB集群存储引擎  
    这是Mysql推出的可以用于集群的存储引擎，提供了SQL和NDB协议之间的接口，原先是收购了NDB数据库，然后发展来的
 *  自定义存储引擎  
    Mysql的存储引擎是支持可以自己开发和修改的，比如阿里的mysql就不是用默认InnoDB

修改存储引擎SQL:

ALTER TABLE mytable ENGINE = InnoDB;

### 四、系统文件层（File System） ###

系统文件层负责将数据库的数据和日志存储在文件系统，并完成与存储引擎的交互，主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 5]

*  日志文件  
    mysql日志文件  
    ![在这里插入图片描述][20201014142609329.png_pic_center]
    
     *  错误日志（Error log）  
        错误日志默认是开启的，脚本查询：`show variables like '%log_error%';`  
        ![在这里插入图片描述][20201012095913101.png_pic_center]
     *  通用查询日志（General query log）  
        记录一般查询语句，`show variables like '%general%';`![在这里插入图片描述][20201012100255729.png_pic_center]
     *  二进制日志（binary log）  
        二进制日志记录了数据库执行的更新操作，不记录select、show等不修改数据的SQL，同时也有记录执行时间、时长，所以这个特性可以用于数据恢复和主从复制
    
    查询是否开启二进制日志：![在这里插入图片描述][20201012100543486.png_pic_center]  
    二进制日志参数查看：![在这里插入图片描述][20201012100329241.png_pic_center]
    
     *  慢查询日志（slow query log）  
        慢查询日志可以用于排查执行时间超时的sql，默认是10s，主要用于性能调优  
        查询是否开启：`show variables like '%slow_query%';`， 查询时长：`show variables like '%long_query_time%';`  
        要开启慢查询日志，可以通过两种方法：
    
    一种是修改mysql，my.ini配置文件：
    
    > \[mysqld\]  
    > slow\_query\_log =1  
    > slow\_query\_log\_file=/usr/local/mysql/data/localhost-slow.log
    
    另外一种是通过sql命令：
    
    > // 开启慢查询日志  
    > mysql> SET GLOBAL slow\_query\_log=ON;  
    > Query OK, 0 rows affected (0.05 sec)  
    > //设置慢查询超时时间  
    > mysql> SET GLOBAL long\_query\_time=3;  
    > Query OK, 0 rows affected (0.00 sec)
 *  配置文件  
    用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等

my.ini配置文件：

[mysql]
    # 设置mysql客户端默认字符集
    default-character-set=utf8 
    [mysqld]
    #设置3306端口
    port = 3306 
    # 设置mysql的安装目录
    basedir="D:\mysql-8.0.13-winx64"
    # 设置mysql数据库的数据的存放目录
    datadir="D:\mysql-8.0.13-winx64\data"
    # 允许最大连接数
    max_connections=200
    # 服务端使用的字符集默认为8比特编码的latin1字符集
    character-set-server=utf8
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    #log-bin=ON
    binlog-format=ROW
    log-bin=mysqlbinlog

*  数据文件  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 6]  
    mysql数据文件，用于保存表数据等等，可以通过配置my.ini配置存放路径：`datadir="D:\mysql-8.0.13-winx64\data"`
    
     *  db.opt 文件：记录这个库的默认使用的字符集和校验规则
     *  frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等
     *  MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)
     *  MYI 文件 ：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息
     *  ibd文件和 IBDATA 文件：InnoDB存储引擎专用，存放 InnoDB 的数据文件（包括索引）
    
    新建一个数据库之后，如果是 InnoDB引擎的，新建一张表就新建对应的ibd文件，命名为：表名.ibd  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 7]  
    **InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，每一张 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个或多个.ibdata 文件**
    
     *  ibdata1 文件  
        系统表空间数据文件，存储表元数据、Undo日志等  
        ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 8]
     *  ib\_logfile0、ib\_logfile1 文件：Redo log 日志文件  
        ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 9]
 *  pid文件  
    pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，用于保存着自己的进程 id
 *  socket文件  
    socket 文件也是在 Unix/Linux 环境下才有的文件，用户在 Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center]: /images/20210813/142794e4f46841f58ba0e5f418fc395e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210813/72795d76a6984eed9fc6ddc554199c6b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20210813/d3dfe7f4016e48ba82f3796c80ebf93b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20210813/cf2e08618d084ce187841258f9479453.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20210813/46fab8985fe2468a92ba857d569361be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20210813/3a3f4647e6284b7d97eb23b50245d3e6.png
[20201014142609329.png_pic_center]: /images/20210813/0c042fcc170b474fbb6d209d7c3ae45f.png
[20201012095913101.png_pic_center]: /images/20210813/7cd4318c76f3424e86711caca48c8749.png
[20201012100255729.png_pic_center]: /images/20210813/ad5cd003f08e43e2bd48acc5aa1e2253.png
[20201012100543486.png_pic_center]: /images/20210813/474b90e7dbad4378bfee4711025ae856.png
[20201012100329241.png_pic_center]: /images/20210813/1e4c8df2f8be4d56a09f02732e217eed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20210813/3e309693698e455698d6c9a1f2d40da7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20210813/02f9e361ed2643cea78758ec476b7840.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20210813/c78a9bc3924f4a83ade0221d71e36bc1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20210813/ef4c26402dbf4922be0397bc95ae31ea.png