架构图
MySQL Server架构
MySQL Server架构自上到下大致可分为网络连接层，服务层，存储引擎层和系统文件层。
-
网络连层
客户端连接器，提供与MySQL服务器的连接的支持，支持Java，C，Python等连MySQL。 -
服务层
服务层是MySQL的核心，主要包括了系统管理和控制工具，连接池，SQL接口，解析器，查询优化器，缓存六个部分。1. 连接池，负责存储和管理客户端与数据库的连接， 一个线程负责管理一个连接。 2.系统管理和控制公具，例如备份恢复，安全管理，集群管理等。 3.SQL接口，用于接收客户端发送的各种SQL命令，并且返回用户需要的查询结果，比如DML，DDL，存储过程，触发器，视图等。 4.解析器，负责将SQL请求转换层一个SQL解析树，然后根据MySQL的规则校验SQL是否合法。 5.查询优化器,当解析树通过解析器语法检查之后，将交由优化器转化成执行计划与存储引擎层交互。 6.缓存，缓存机制是由一系列小缓存组成，比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的结果，则直接返回缓存的数据。 -
存储引擎层
存储引擎负责MySQL中的数据存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，可插拔。服务器中的查询存储引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异，现在有多种存储引擎，各有各种特点，常见的是MyISAM和InnoDB。 -
系统文件层
系统文件层主要负责将数据和日志存储的文件系统上，并完成与存储引擎层的交互，是文件的物理存储层。主要包括日志文件，数据文件，配置文件，pid文件，socket文件等。日志文件: 错误日志 默认开启: show variables like '%log_error%' 通用慢查询日志 记录一般的查询日志: show variables like '%general%' 二进制日志 记录了对MySQL的执行的更改操作，并记录了语句的发生时间，执行时常；但他不记录select，show等不改变数据库的SQL。主要用于数据恢复和主从负责。 show variables like '%log_bin%'； //是否开启 show variables like '%binlog%'; //参数查看 show binary logs; //查看日志文件 慢查询日志 记录所有执行时间超时的查询SQL，默认是10s。 show variables like '%slow_query%'; show variables like '%long_query_time%'; 配置文件: 用于存放MySQL所有的配置信息的文件，比如my.cnf,my,ini等。 数据文件: db.out文件: 记录这个库的默认使用的字符集和校验规则。 frm文件: 存储与这个表相关的元数据(meta)信息，包括表结构定义信息等，每一张表都会有一个frm文件。 MYD文件: MyISAM存储引擎专用，存放MyISAM表的数据(data),每一张表都会对应一个.MYD文件。 MYI文件: MyISAM存储引擎专用，用于存放MyISAM表的索引相关的信息，每一张MyISAM表都对应一个.MYI文件。 ibd文件和ibdata文件: 用于存放InnoDB的数据文件(包括索引)。InnoDB存储引擎有两种表空间方式：独享表空间和共享表空间。独享表空间使用.ibd文件来存放数据，且每一张InnoDB表都对应一个.ibd文件。共享表数据使用.ibdata文件，所有的共享表空间共同使用(一个或多个可配置).ibdata文件
MySQL的运行机制
运行图片
1. 建立连接，用过客户端/服务端通信协议与MySQl建立连接。MySQL客户端与服务端的通信方式是“半双工”。对于每一个MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。
通讯机制：
全双工: 能同时发送和接收数据，例如平时打电话。
半双工: 指的某一时刻，要么发送数据，要么接收数据，不能同事，例如bb机。
单工: 只能发送数据或只能接收数据。例如单行道。
线程状态：
show processlist; //查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能查看自己的。
id：线程id。
user：启动这个线程的用户
Host：发送请求的客户端的ip地址和端口号
db：当前命令在哪个库执行
Command：该线程正在执行的操作命令
Create DB：正在创建数据库操作
Drop DB：正在删除数据库操作
Execute：正在执行一个PreparedStatement
Close Stmt：正在关闭一个PreparedStatement
Query：正在执行一个语句
Sleep：正在等待客户端发送语句
Quit：正在推出
Shutdown：正在关闭服务器
Time： 标识该线程处于当前状态的时间，单位是秒
State：线程状态
Updating：正在搜索匹配请求，进行修改
Sleeping：正在等待客户端发送新的请求
Starting：正在执行请求处理
Checking table：正在检索数据表
Closing table: 正在将表中的数据刷新到磁盘中
Locked：被其他查询锁住了记录
Sending data：正在处理select查询，同时将结果发给客户端
Info：一般记录线程执行的语句，默认显示前100个字符。想看完整的使用show full。
-
查询缓存，这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存中查询到了完全相同的SQL语句，则直接将查询结果返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的SQl语句也会由解析器进行语法解析，并生成“解析树”。
缓存select结果和SQL语句 执行select语句查询时，先查询缓存，判断是否有可用的字符集，要求是否完全相同(包括参数值)，这样才会匹配缓存数据命中。 即使开启了查询缓存，以下SQL也不可能缓存 查询语句使用SQL_NO_CACHE 查询结果大于query_cache_limit设置 查询中有一些不确定的参数，比如now() show variables like '%query_cache%'; //查看查询缓存是否可用，空间大小，限制等 show status like '%Qcache%'; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等 -
解析器，将客户端发送的SQL进行语法解析，生成解析树，预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里检查数据表和数据列是否存在，还会解析名字和别名，看看他们是否有歧义，最后生成新的解析树。
-
查询优化器，根据解析树生成最优的查询计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类: 静态优化(编译时优化)，动态优化(运行时优化)
等价变换策略： 5=5 and a > 5 改成 a > 5 a < b and a = 5 改成 b > 5 and a = 5 基于联合索引，调整条件位置等 优化count，min，max函数 InnoDB引擎min函数只需要找到索引最左边 InnoDB引擎max函数只要找到索引最右边 MyISAM引擎count(*),不需要再计算，直接返回 提前终止查询 使用了limit查询，获取limit所需数据，就不再遍历后面的数据 in的优化 MySQL对in查询，会先进行排序，再用二分查找法查找数据，比如对where id in(2,1,3)变成where id in (1,2,3) -
查询执行引擎负责执行SQL语句，此时查询执行引擎会根据SQL语句中表存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件进行交互，得到查询结果并返回客户端。若开开启用查询缓存，这时会将SQL语句和结果完整的保存到查询缓存中，若以后有相同的SQL语句的执行则直接返回结果。
若开启了查询缓存，先将查询结果做缓存操作 返回结果过多，采用增量模式返回
网友评论