基于query语句解析mysql工作原理

浏览数：21 / 时间：2015年06月12日

一、前言

在之前的博文中己经介绍过了mysql的编译安装，那么这次将介绍一下从一条query语句执行过程来剖析mysql是如何工作的。

MySQL（官方发音为/maskjul/ "My S-Q-L",[1]，但也经常读作/masikwl/ "My Sequel"）原本是一个开放源代码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，该公司于2008年被升阳微系统（Sun Microsystems）收购。2009年，甲骨文公司（Oracle）收购升阳微系统公司，MySQL成为Oracle旗下产品。

MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用，比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

但被甲骨文公司收购后，Oracle大幅调涨MySQL商业版的售价，且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展，因此导致自由软件社区们对于Oracle是否还会持续支持MySQL社区版（MySQL之中唯一的免费版本）有所隐忧，因此原先一些使用MySQL的开源软件逐渐转向其它的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库

三、mysql特性（来源于维基百科）

使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性。
支持AIX、BSDi、FreeBSD、HP-UX、Linux、Mac OS、Novell NetWare、NetBSD、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统。
为多种編程语言提供了API。这些編程语言包括C、C++、C#、VB.NET、Delphi、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等。
支持多線程，充分利用CPU资源，支持多用户。
優化的SQL查询算法，有效地提高查询速度。
既能够作为一个单独的应用程序在客户端服务器网络环境中运行，也能够作为一个程序库而嵌入到其他的软件中。
提供多语言支持，常见的编码如中文的GB 2312、BIG5，日文的Shift JIS等都可以用作数据表名和数据列名。
提供TCP/IP、ODBC和JDBC等多种数据库连接途径。
提供用于管理、检查、優化数据库操作的管理工具。
可以处理拥有上千万条记录的大型数据库。

四、工作原理（之前己经介绍过如何编译安装mysql）

1、先上架构图。

2、逻辑模块组成

2.1宏观解析

总的来说，msyql可以看成二层架构，第一层我们通常叫做sql layer，在mysql数据库系统处理底层数据之间的工作都是在这一层完成的，包括权限判断，sql解析，执行的计划优化，query cache的处理等等；第二层就是存储引擎，我们通常叫做Storage Engine layer,也就是底层数据存取操作实现部分，由多种存储引擎共同组成。

sql layer中包含了多个子模块，下面做一下简单的介绍

1、初始化模块

初始化模块就是在mysql server启动的时候，对整个系统做各种各样的初始化操作，比如各种buffer,cache结构的初始化和内存空间的申请，各种系统变量的初始化设定，各种存储引擎的初始化设置，等等。

2、核心API

核心API模块主要是为了提供一些需要非常高效的底层操作功能的优化实现，包括各种的底层的数据结构的实现，特殊算法的实现，字符串处理，数字处理等，小文件I/O，格式化输出，以及最重要的内存管理部分。

3、网络交互模块

底层网络交互模块抽象出底层网络交互所使用的接口API，实现底层网络数据的接收与发送，以方便其他各个模块调用，以及对这一部分的维护，所有源代码都在vio文件夹下面。

4、C/S交互协议模块

mysql的C/S交互协议模块部分，实现了客户端与mysql交互过程中的所有协议。当然这些协议都是建立在现有的OS和网络协议之上的，如TCP/IP以及Unix Socket.

5、用户模块

用户模块实现的功能，主要包括用户的登录连接权限控制和用户的授权管理，他就像mysql 的大门守卫一样，决定是否给来访者“开门”。

6、访问控制模块

实现的功能就是根据用户模块中各用户的授权信息，以及数据库自身特有的各种约束，来控制用户对数据的访问，用户模块和访问控制模块两者结合起来，组成了mysql 整个数据库系统的权限安全管理的功能。

7、连接管理

连接管理模块负责监听对mysql server的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上mysql server的客户端请求都会分配一个独立连接线程，而连接线程的主要工作就是负责mysql server与客户端通信，接受客户端的命令请求，传递server端的结果信息。线程管理模块则负责维护这些连接线程，包括线程创建，线程的cache等。

8、Query解析和转发模块

在mysql里我们习惯将所有client端发送给server端的命令都称为query，在mysql server里面，连接线程接收到客户端的一个query后，会直接将该query传递给专门负责将各种query进行分类然后转发给各个对应的处理模块，这个模块就是query解析和转发模块，其主要工作就是将query语句进行语义和语法的分析，然后按照不同的操作类型进行分类，然后做出针对性的转发。

9、query Cache模块

query Cache模块在mysql中是一个非常重要的模块，他的主要功能是将客户端提交给 MySQL的select类query请求的返回结果集cache到内存中，与该query的一个hash值做一个对应。该query所取数据的基表发生任务数据的变化之后，MySQL会自动使该query的cache失效，在读写比例非常高的应用系统中，query cache 对性能的提高是非常显著的，当然它对内存的消耗也是非常大的。

10、Query优化器模块

query 优化器，顾名思义，就是优化客户端请求的query，根据客户端请求的query语句，和数据库中的一些统计信息。在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个query语句的结果。

11、表变更管理模块

表变更管理模块主要中负责完成一些DML和DDL的query，如：update，delete,insert,create table alter table等语句的处理。

12、表维护模块

表的状态检查，错误修复，以及优化和分析等工作都是表维护模块需要做的事情。

13、系统状态管理模块

系统状态管理模块负责在客户端请求系统状态的时候，将各种状态的数据返回给用户，像DBA常用的各种show status命令，show variables命令符，所得到的结果都是由这个模块返回的。

14、表管理器

这个模块从名字上看来很容易和上面的表变更和表维护模块相混淆，但是其功能与变更及维护模块却完全不同，每一个mysql的表都是一个表的定义文件，也就是*.frm文件。表管理器的工作主要就是维护这些文件。以及一个cache，该chace中的主要内容是各表的结构信息。此外，它还维护table级别的锁管理。

15、日志记录模块

日志记录模块主要负责整个系统级别逻辑层的日志的记录，包括error log,binary log,slow log等。

16、复制模块

复制模块又分为master模块和slave模块两部分，master模块主要负责在replicatin环境中读取master端的binary日志，以及与slave端的I/O线程交互等工作。slave模块比master模块所要做的事情稍多一些，在系统中主要体现在两个线程上面。一个是负责从master请求和接受binary日志，并写入本地relay log的I/O线程。另外一个是负责从relay log中读取相关的日志事件，然后解析成可以在slave端正确执行并得到和master端完全相同的结果的命令并再交给slave执行的过程。

17、存储引擎接口模块

基本上只有mysql可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一抽象类，但正因为它成功的将各种数据处理高度抽象化。才成就了今天mysql可插拔储引擎的特色。

2.2微观解析

1、发起连接

当client apps发起一条sql语句为例(select * from mysql.user;)由监听客户端的连接管理模块会将连接请求转给线程管理模块，去请求一个连接线程，而这时就到了线程管理模块，连接线程模块在接在连接请求后，首先会检查当前连接线程池中是否有被cache的空闲的连接线程，如果有，就取出一个和客户端请求连接上，如果没有空闲的连接线程，则建立一个新的连接请求。当然，连接线程模块并不是在收到连接请求后马上就会取出一个连接线程和客户端连接，而是首先通过调用用户模块来进行授权检查，只有客户端请求通过了授权检查后，他才会将客户端请求和负责请求的连接线程连上。

2、请求Query

①、如果是一个 Query 类型的请求，会将控制权交给 Query 解析器。 Query 解析器首先解析,而解析（包含对语法，表、视图是否存在判断，怎么执行，比较那个方案更优)会消耗很多资源：cpu、IO、memory，如果还有其它用户执行同样的操作，那么第一次执行的语句将被缓存在cache中(是否缓存可以通过SQL_CACHE和SQL_N0_CACHE手动控制缓存)，如果存在，就直接执行执行计划，将cache中的数据返回给连接线程模块，然后通过与客户端的连接的线程将数据传输给客户端。（cache的作用是缓存sql语名,或sql语句所对应的执行计划;）

②、如果不是一个可以被cache的query类型，或者cache中没有该query的数据，那么query将被继续传回query解析器（此前查找cache所用的时间将白白浪费），让 query解析器进行相应处理，再通过 query 分发器分发给相关处理模块。

③、如果解析器解析结果是一条未被 cache 的select语句，则将控制权交给 Optimizer，也就是 Query 优化器模块，主要负责对sql语句进行解析（prase）利用内部算法对sql进行解析，生成解析树（parse tree）及执行计划（execution plan）。（解析：一条sql语句有N条执行方案，当选择最优的方案过程，是最消耗资源的）执行sql，交由访问控制模块执行后续操作，并返回结果（execute and return)

④、如果是 DML 或者是 DDL 语句，则会交给表变更管理模块。

⑤、如果是一些更新统计信息、检测、修复和整理类的 query 则会交给表维护模块去处理。

⑥、复制相关的query 则转交给复制模块去进行相应的处理。

⑦、请求状态的query 则转交给了状态收集报告模块。实际上表变更管理模块根据所对应的处理请求的不同，是分别由 insert 处理器、delete处理器、update 处理器、create 处理器，以及 alter 处理器这些小模块来负责不同的 DML和 DDL 的。在各个模块收到 Query 解析与分发模块分发过来的请求后，首先会通过访问控制模块检查连接用户是否有访问目标表以及目标字段的权限，如果有，就会调用表管理模块请求相应的表，并获取对应的锁。表管理模块首先会查看该表是否已经存在于table cache 中，如果已经打开则直接进行锁相关的处理，如果没有在 cache 中，则需要再打开表文件获取锁，然后将打开的表交给表变更管理模块。当表变更管理模块“获取”打开的表之后，就会根据该表的相关 meta 信，判断表的存储引擎类型和其他相关信息。根据表的存储引擎类型，提交请求给存储引擎接口模块，调用对应的存储引擎实现模块，进行相应处理。不过，对于表变更管理模块来说，可见的仅是存储引擎接口模块所提供的一系列 “标准”接口，底层存储引擎实现模块的具体实现，对于表变更管理模块来说是透明的。他只需要调用对应的接口，并指明表类型，接口模块会根据表类型调用正确的存储引擎来进行相应的处理。

3、取回结果

当一条 query 或者一个 command 处理完成（成功或者失败）之后，控制权都会交还给连接线程模块。如果处理成功，则将处理结果（可能是一个 Result set，也可能是成功或者失败的标识）通过连接线程反馈给客户端。如果处理过程中发生错误，也会将相应的错误信息发送给客户端，然后连接线程模块会进行相应的清理工作，并继续等待后面的请求，重复上面提到的过程，或者完成客户端断开连接的请求。如果在上面的过程中，相关模块使数据库中的数据发生了变化，而且 MySQL 打开了 binlog 功能，则对应的处理模块还会调用日志处理模块将相应的变更语句以更新事件的形式记录到相关参数指定的二进制日志文件中。

缓存相关注意事项：

1、cache查找方式

mysql利用内部的hash算法来取得该sql的hash值，然后在cache里查找是否存在该hash值；假设存在，则将此sql与cache中的进行比较；假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是缓存命中的过程。诚然，如果上面的两个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。

2、解析的弊端

创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免优化器创建解析树、生成执行计划的动作。

3、不会缓存的数据

查询语句中有一些不确定数据时，例如NOW(), CURRENT_TIME()；一般来说，如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表，一般都不会缓存；

4、缓存会带来额外开销

每个查询都得先检查是否命中，查询结果要先缓存；

5、如何判断命令率

mysql> SHOW GLOBAL STATUS LIKE‘Qcache%‘;

6、计算命中率

⑴、第一种方式

mysql>SHOW GLOBAL STATUS WHERE Variable_name=‘Qcache_hits‘OR Variable_name=‘Com_select‘;

公式：Qcache_hits/(Com_select+Qcache_hits)