mysql企业架构原理TXT下载_MySQL架构原理

架构图

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语句的执行则直接返回结果。

若开启了查询缓存，先将查询结果做缓存操作

返回结果过多，采用增量模式返回