5.MySQL 基础结构

MySQL简单结构

1、底层结构的简单理解

如下图所示：

MySQL = 客户端 + 服务端
客户端 = Connection(语言连接器例如 ：PHP-pdo , Mysli)
服务端 = SQL层 + 存储引擎
SQL层 = 链接/线程处理 + 查询缓存 + 解析器 + 优化器
存储引擎 = InnoDb + MariaDB +….

Connection：这一块其实主要是其他语言的连接，并不属于MySQL本身；主要是其他语言对于MySQL的连接操作的工具比如PHP中的：pdo，mysqli或者Navicat for MySQL
SQL层：功能主要包括权限判断，SQL解析功能和查询优化功能等。

1. 链接/线程处理：主要用来处理客户端的请求、身份验证和数据安全性的验证等。
2. 查询缓存和查询分析器是SQL层的核心部分，其中主要涉及查询的解析、优化、缓存、以及所有内置的函数，存储过程，触发器，视图等
3. 优化器：主要负责存储和获取所有存储在MySQL中的数据

2 MySQL物理文件类型

日志文件包括：

1. 错误日志（Error log）
2. 二进制日志（Binary Log）
3. 事务日志（InnoDB redo Log & undo Log）
4. 慢查询日志（Slow Query Log）
5. 查询日志（Query Log）

数据库文件：

1. ‘.frm’文件
2. ‘.MYD’文件
3. ‘.MYI’文件
4. ‘.ibd’文件和’.ibdata’文件

其他配置文件

1. 系统配置文件（my.ini或者my.cnf）
2. pid,err文件
3. socket文件

2.1日志文件

2.1.1 错误日志

在数据库文中，错误日志功能是默认开启的。默认情况下，错误日志存储在mysql 数据库的数据目录中。错误日志文件通常的名称为 hostname.err ，hostname也就是主机名。
错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过 log_error 和 log_warnings 来定义的，其中log-error是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。
默认情况下，错误日志主要记录一下信息

1. 服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）
2. 服务器运行过程中的错误信息、时间调度器运行一个事件时产生的信息、在服务器上启动进程时产生的信息

   show global variables like '%log_error%';

2.1.2 二进制日志

二进制日志，也就是我们常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录日志在日志文件中，其中还包括每个调语句所执行的时间和消耗的资源，以及相关的事务信息。
默认情况下二进制日志功能是没有开启的，启动可以配置log-bin[=file_name]开启，

作用：

1. 以二进制形式记录更改数据库的SQL语句（insert，update，delete，create，drop，alter等）。
2. 用于MySQL主从复制。
3. 增量数据备份及恢复。

设置二进制日志是在在my.cnf或者my.ini 中添加配置

server-id=1
log_bin=mysql-bin

启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。有些类似于oracle开启归档模式。
命令

-- 查看所有二进制文件信息
show binary logs;
-- 查看最新二进制文件
show master status;
-- 刷新日志
flush logs;

-- 查看二进制日志信息
语法格式：SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

-- show binlog events用于在二进制日志中显示事件。如果未指定'log_name'，则显示第一个二进制日志。
help show binlog events;  --获取帮助信息
show binlog events\G;
show  binlog events in 'mysql-bin.000014'\G;

命令行查：

官网二进制文件恢复数据 dev.mysql.com/doc/refman/5.7/en/po...

2.1.3. 事务日志(InnoDB)

查看存储引擎：show engines;

InnoDB引擎在线Redo日志记录了InnoDB所做的所有物理变更和事务信息。通过Redo日志和Undo信息，InnoDB大大地加强了事务的安全性。InnDB在线Redo日志默认存放在data命令下，可通过设置innodb_log_griyo_home_dir选项来更改日志文件存放位置，通过innodb_log_files_in_group选项来说何止日志的数量

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的。

2.1.4 慢查询日志

顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slowquery。

专业一点：慢查询日志是值所有SQL执行的实际超过long_query_time变量的语句和达到min_examined_row_limit条举例的语句。用户可以针对这部分语句性能调优。慢查询日志通过设置log-slow_queries[=file_name]选项开启后，将记录日志所在的路劲和名称。MySQL系统默认的慢查询日志的文件名是show.log，默认目录是data目录。
命令：

-- 查看慢查询是否开启
show variables like "log_slow_queries";
-- 查看慢查询日志的定义：
show global variables like  '%slow_query_log%';
-- 查看long_query_time设置时间
show global variables like '%long%';
-- 查看具体信息
-- slow_query_log： off关闭状态  on开启状态
-- slow_query_log_file  慢查询日志存放地点
show variables like "%slow%" ;

开启慢查询：

slow_query_log
slow_query_log_file=[file_name] 文件地址
long_query_time=2 -- 最大等待时间

2.2 数据文件

MySQL 数据库会在data 目录下建立一个以数据库为名的文件来存储数据库中的表数据。不同的数据引擎，每个表的扩展名也不一样，例如 MyISAM 用 “.MYD”作为扩展名，Innodb 用“.ibd”，Archive 用“.arc”，CSV 用“.csv

2.2.1 “.frm”文件

无论是那种存储引擎，创建表之后就一定会生成一个以表明命名的’.frm’文件。frm文件主要存放与表相关的数据信息，主要包括表结构的定义信息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。

2.2.2 “.MYD”

“.MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起。

2.2.3 “.MYI”

“.MYI”文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM存储来说，可以被cache 的内容主要就是来源于“.MYI”文件中。每一个MyISAM表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。

2.2.4 “.ibd”文件和ibdata文件

这两种文件都是存放Innodb数据的文件，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和MyISAM数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata文件。
ibdata文件可以通过innodb_data_home_dir（数据存放目录）和innodb_data_file_path(配置每个文件的名称)两个参数配置组成

innodb_data_file_path中可以一次配置多个ibdata文件

#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
配置方式

共享表空间以及独占表空间都是针对数据的存储方式而言的。
共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中。
独占表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容。
两者对比
（1.）共享表空间：
优点：

可以放表空间分成多个文件存放到各个磁盘上。数据和文件放在一起方便管理。
缺点：

所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
（2.）独立表空间：
优点:

1. 每个表都有自已独立的表空间。
2. 每个表的数据和索引都会存在自已的表空间中。
3. 可以实现单表在不同的数据库中移动。
4. 空间可以回收
   
   a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:altertable TableName engine=innodb;回缩不用的空间。
   
   b) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：单表增加过大，如超过100个G。

相比较之下，使用独占表空间的效率以及性能会更高一点

共享表空间和独立表空间之间的转换

show variables like “innodb_file_per_table”; ON代表独立表空间管理，OFF代表共享表空间管理；

修改数据库的表空间管理方式

修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和独立表空间；

innodb_file_per_table=1 为使用独占表空间

innodb_file_per_table=0 为使用共享表空间

2.3 其他文件

1. 系统核心配置文件
   位置：linux/mac : etc/my.cnf;windows : mysql/my.ini
   MySQL 的系统配置文件一般都是my.cnf，默认存放在”/etc”目录下，my.cnf文件中包含多种参数选项组（group），每一种参数组都通过中括号给定了固定的组名，如“[mysqld]”组中包括了mysqld服务启动时候的初始化参数，“[client]”组中包含着客户端工具程序可以读取的参数。

2. socket file
   MySQL服务器启动后socket文件会自动生成，该文件主要用来连接客户端
   在有些时候连接MySQL会出现如下的问题

通常的解决办法，可以尝试重启一下MySQL的服务器

3.MySQL 运行流程

MySQL结构图：

执行流程分析：

3.1.1 启动

1. 通过命令net start mysql(windows) / service mysql start(linux)启动MySQL服务
2. 调用初始模块；初始化模块就是在数据库启动的时候，对整个数据库做的一些初始化操作，比如各种系统环境变量的初始化，各种缓存，存储引擎初始化设置等。

核心api：MySQL数据库核心api主要实现了数据库底层操作的优化功能，其中主要包括IO操作、格式化输出、高性能存储数据结果算法的优化，字符串的处理，其中最重要的是内存管理。

3.1.2 连接

3. 用户发送一条SQL，这个时候会被网络交互模块监听到用户的操作请求，传递给‘连接管理模块’
4. 接收到请求转发到‘进/线程连接模块’
5. 调用‘用户模块’来进行权限检测（访问数据库的权限）
6. 通过检测之后就会去‘连接进/线程模块’从‘线程连接池’中查找空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求
7. 返回连接线程

网络交互模块：对外提供可以接收和发送数据的api接口，其他模块需要交互的时候，可以通过api接口调用

连接管理模块、进/线程连接模块、线程连接池：连接管理模块负责监听MySQL Server的各种请求，根据不同的请求，然后转发到线程管理模块，每个客户请求都会被数据库自动分配一个独立的线程为其单独服务，而连接线程的主要工作就是负责MySQL Server与客户端通信，线程管理模块负责管理这些生成的线程。

3.1.3 处理

8. 在用户权限校验成功之后，并且获得新的连接池之后就会去‘命令分发器’，判断命令的类型如果是select就会去访问‘查询缓存’，如果没有就会往下执行；
9. 如果是select，并且开启’查询缓存’之后就会去缓存中查询是否有与之相匹配的SQL,如果有就会校验用户访问该数据的权限,通过就返回不通过就会返回错误信息. 如果数据没有就会往下执行
10. 会记录过程中的SQL操作过程到日志文件中
11. 在第8,9步 没有满足相应条件之后往下执行进入 ‘命令解析器’,经过词法分析,语法分析后生成解析树
12. 根据操作转到对应的模块处理(预处理阶段)，根据SQL选择执行的模块
13. 模块收到请求后，通过’访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限（是指访问这些数据的权限）
14. 有权限’表管理模块’先查看table cache中是否存在，有则直接对应的表和获取锁，负责重新打开表文件
15. 根据表的ENGINE数据，获取表的存储引擎类型等信息
16. 通过接口调用对应的存储引擎处理
17. 返回查询之后数据内容

用户模块：主要功能是用于控制用户登入连接的权限和用户授权管理。

访问控制模块：主要用于监控用户的每一个操作。访问控制模块实现的功能就是根据用户模块中不同的用户授权，以及根据其数据库的各种约束来控制用户对数据的访问。用户模块和访问控制模块结合起来，就组成了MySQL数据库的权限管理功能。

查询优化器：这个模块主要是讲客户端发送的查询请求，在之前算法的基础上分析，计算出一个最优的查询策略，优化之后会提高查询访问的速度，最后根据其最优策略返回查询语句。

表变更管理模块：主要负责完成DML和DDl的查询，列如，insert，update，delete，create table，alter table等语句处理。

表维护模块：主要用于检测表的状态，分析，优化表结构，以及修复表。

复制模块：复制模块分为Master模块和Slave模块两部分。Master模块主要负责复制环境中读取Master端的binary日志，以及Slave端的I/O线程交互等工作。

状态模块：在客户端请求系统状态的时候，系统状态模块主要负责将各种状态的数据返回给用户。最常用的一些查询状态的命令包括show status，show variable是等，都是通过这个模块负责返回的。

表管理模块：主要就是维护系统生成的表文件。列如MyISAM存储引擎就生成frm，myd，myi文件，维护这些文件，江哥哥表结构的信息缓存起来，另外该模块还管理表级别的锁。

存储引擎接口模块：MySQL实现了其数据库底层存储引擎的插件师管理，将各种数据处理高度抽象画。

3.1.4 结果

18. 命令执行完了之后，将结果集返回给’理解进/线程模块’(返回的也可以是相应标识，成功失败)
19. ‘理解进/线程模块’进行后续的清理工作，并继续等待请求或断开与客户端的连接

插一波广告：it资源需要+我18665744203/15521277662

本作品采用《CC 协议》，转载必须注明作者和本文链接