MySQL架构原理

2020-09-05 本文已影响0人可靠的千秋

架构图

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语句的执行则直接返回结果。
```
若开启了查询缓存，先将查询结果做缓存操作
返回结果过多，采用增量模式返回
```

MySQL架构原理

MySQL Server架构

MySQL的运行机制

猜你喜欢

热点阅读