MySql 查询流程

前言

 

在实际开发过程中，我们不可避免的与数据产生交互。读取数据，写数据。而整个数据存储在我看来，又统分为两大块。一块为缓存数据，一块为物理数据。

缓存数据：服务器缓存，处于内存之中，由服务器关闭而失效。缓存数据的最大作用，在于抗并发，减少数据库IO交互。但是对于那些经常容易发生变化的数据， 不宜采用缓存数据，容易引起不可重读读，脏读，幻读

磁盘数据：磁盘数据为具体物理存储数据，写入到数据存储介质上（磁盘）。但是受限于磁盘转速等问题，存储数据通常相对来说比较慢。而频繁的和数据库产生IO交互，也是程序开发中的性能瓶颈。

 

在我理解中，大量的数据，杂乱无章的存储在磁盘上时，是无意义的。因此在软件开发过程中，我们把数据库中有一定业务含义的数据结构称为表，表与表之间的关系 等同于业务单据与业务单据的关系（诸如 销售订单主表和销售订单明细表）。而这样一种定义数据结构的方式，则是关系型数据库的由来。

 

在很久之后的一段时间内，数据库一般来说指的都是关系型数据库。表与表之间支撑起了复杂的业务关系。所以应运而生了一些管理表关联的工具，诸如powerDesginer。

 

但是随着业务的继续膨胀，随着数据量级的增加，仅仅靠传统的关系型数据库，显得力不从心。虽然我们有分区键，有分库分表策略，有空间换时间的思想。于是，产生了一个叫NoSQL的东西，他的本名叫做非关系型数据库，也被not only sql。

他去除了庞大而又臃肿的数据表之间的关联关系，致力于用高效的性能达到更快的存储。常见的nosql ，有redis和mongoDB。他们也是一种数据存储，只不过，好像我们更加喜欢称呼他redis缓存。虽然他们存储数据快，但是笔者从来没有见过哪个正式项目直接用它代替数据库。他存在的意义在于分摊数据库压力，存储热点数据，同时由于分布式token问题，我们有些时候会将token存储到redis中。而且由于redis等nosql没有ACID，也就是说他们并没有事务特性。（不过在分布式项目中，单独依赖单据数据库的事务，是不现实的。我们可能需要TCC事务补偿机制或者事务MQ之类的，但是有大佬说，这些都过时了，市面上有更好的处理机制，这块不甚了解）

 

市面上流行的关系型数据库管理工具，大致有3个，分别是oracle，mysql与sqlServer。笔者在上学期间用过SqlServer，实际工作后，都是围绕着oracle和mysql来的。我们这边这次聊一聊mysql的执行流程。

 

图形概念

 

1.客户端发送链接请求到服务端

2.数据库查询自己的缓存（如果有开启缓存的话）

3.数据库进行词法解析，校验输入的sql是否存在词法问题

4.数据库进行预处理，在输入查询语句均通过正常的情况下，校验表名，字段名称

5.查询优化器，由数据库对该查询语句进行sql优化

6.执行计划，数据库在查询优化器完成之后，会生成多个执行计划，这里数据库会有一个算法选择最合适的那个执行计划

7.在数据库选择完对应执行计划之后，进入到执行引擎中，mysql有很多执行引擎，但是通常说的有两个分别是默认的Inndb与MyIsam，不同的执行引擎，最终存储的数据文件都不一样，他们有着自身不同的适用场景

8.最终，我们在存储引擎中，获取到对应的数据，返回给缓存或者是客户端

 

以上这些是对整个查询的简写版本。如果有兴趣的话，你们可以看看下面的详情分析

 

通讯协议

通讯类型

同步：1、同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库，线程会阻塞，等待数据库的返回。

          2、一般只能做到一对一，很难做到一对多的通信。

异步：1、异步可以避免应用阻塞等待，但是不能节省 SQL 执行的时间。

           2、如果异步存在并发，每一个 SQL 的执行都要单独建立一个连接，避免数据混乱。但是这样会给服务端带来巨大的压力（一个连接就会创建一个线程，线程间切换会占用大量 CPU 资源）。另外异步通信还带来了编码的复杂度，所以一般不建议使用。如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接。一般来说我们连接数据库都是同步连接。

 

连接方式

MySQL 既支持短连接，也支持长连接。短连接就是操作完毕以后，马上 close 掉。长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。

保持长连接会消耗内存。长时间不活动的连接，MySQL 服务器会断开。(默认8个小时)

 

通讯协议

mysql支持Unix Socket与TCP/IP协议，区别在于有没有带-h。 mysql -h192 .168.8.211 -uroot -p123456  这种就是TCP/IP的。

 

通讯方式

 

Mysql采取了半双工的连接方式，也就是说，客户端和服务端在同一时间点只能由一方发送。

 

以上的这些，说明了mysql客户端如何向服务端进行请求的，用到的通讯协议是哪些

 

缓存

mysql其实本身自己是用缓存的，笔者曾经执行过一段很复杂的sql，用了两分钟，后来我又执行了一遍，用了100多秒，我很开心的以为这个就是数据库缓存了。然而这个是假的，mysql的缓存模块做的十分鸡肋，并且在8.0的mysql中，已经去除了这个模块了

show variables like 'query_cache%' ;

这条命令用于检查是否开启了缓存

可以看到的是都是关闭的，事实上，mysql的缓存要起效的话，必须sql完全一模一样，包括大小写和空格符。而且，一旦表行记录数据发生变化，缓存就失效了，这个不太友好。

所以，最终数据库的缓存交给了orm框架，比如mybatis的默认一级缓存，以及redis等

 

语法解析

这里假设我输入了一个SQL语句，为select name from use_info where id = 1 and age>20  那么这个是如何解析的呢？

实际上mysql自己有个解析树，它大概长成这样

实际上，笔者之前有做过一个动态指标的东西，和这个很类似，区别在于，我是通过不同的数据组装成一个SQL  而它是把这些关键字段给拆开来了。

语法解析这一层就可以抛出数据库关键字段错误，比如你输错了select from之类的字段。

市面上的数据库中简件，比如MyCat，Sharding-JDBC都有一套自己的词法和语法解析器

 

预处理

预处理是在词法解析之后，当你的整体sql没有问题，但是表名不存在，数据库字段不存在，他就会在这一层抛出问题。

这个就是在用最原始的jdbc的那个preparedStatement（但是除了那年大一考试用到了这个，其他的时候我还真没写过这个）

 

查询优化器与执行器

在我们执行一条查询语句的时候，会有不同的查询方案，主要是由于索引的存在（比如建立联合索引和单个索引）。查询优化器（Optimizer）本质上就是根据解析树生成不同的执行计划（Execution Plan）。

 

其中Query是我执行的SQL,Trace是执行的分析语句，他是一个JSON，主要分为三个部分，准备阶段，优化阶段，执行阶段

 

看一下JSON的参数结构：

 

这里的join_preparation，join_optimization，join_execution 分别对应着三个环节，而其中expanded_query是优化过后的sql。（这里把*号变成了具体的字段，所以查询sql上面不应该放*，不然编译器会做一步解析的）

而 considered_execution_plans则是列举出了所有的执行计划

 

到了这一步，我们通过查询优化器获得了执行计划，现在我们查看一下执行计划。

这东西我们经常用，主要用来看走不走索引，以及哪个连接耗时最大。
 

存储引擎

在查询语句优化完成之后，同时调用执行计划的时候，为我们接下来要做的事情，就是去获取数据。

数据的存储并不是杂乱无章的，简单的往磁盘上面一放就完事了。数据的存储牵扯到存储引擎，也就是说不通的存储引擎，针对着不通的存储数据结构，应对着不同的场景

 

我们所熟悉的有两种存储引擎，分别是InnoDB和MyISAM。在Mysql5.5版本以前MyISAM是默认存储引擎，而5.5版本之后默认就是InnoDB了，InnoDB支持行锁，支持事务。在相关业务场景中，使用的更为广泛。

 

我们先看一下，现在数据库支持的存储引擎有哪些，以及这些存储引擎的特性吧  查询语句  show engines ;

这里有详细的说明，比如InnoDB 是默认的（Support Default），支持事务，支持行级别锁，与外键，支持Transaction事务，同时支持XA规范（我之前博客中，有聊过分布式事务和XA规范），

而其中的SavePoints，用来实现子事务（嵌套事务）。创建了一个 Savepoints 之后，事务就可以回滚到这个点，不会影响到创建 Savepoints 之前的操作

 

当然了，这里也有官方的详细文档， https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

MyISAM(3个文件存储)

应用范围比较小。表级锁定限制了读/写的性能，因此在 Web 和数据仓库配置中，它通常用于只读或以读为主的工作。

特点：

支持表级别的锁（插入和更新会锁表）。不支持事务。

拥有较高的插入（insert）和查询（select）速度。

存储了表的行数（count 速度更快）。（怎么快速向数据库插入 100 万条数据？我们有一种先用 MyISAM 插入数据，然后修改存储引擎为 InnoDB 的操作。）

适合：只读之类的数据分析的项目。（这里就让我想起了读写分离）

 

 

InnoDB（2 个文件）

mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全（与 ACID 兼容）的 MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁（不升级为更粗粒度的锁）和 Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了保持数据完整性，InnoDB 还支持外键引用完整性约束。

 

特点：

支持事务，支持外键，因此数据的完整性、一致性更高。

支持行级别的锁和表级别的锁。

支持读写并发，写不阻塞读（MVCC）。

特殊的索引存放方式，可以减少 IO，提升查询效率。

适合：经常更新的表，存在并发读写或者有事务处理的业务系统。

 

关于MVCC

这边参考了这篇博客   https://blog.csdn.net/w2064004678/article/details/83012387

本质上是一种CAS乐观锁的实现，以版本号的方式

 

Memory（1个文件）

将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。

其使用案例正在减少；InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而 ndbcluster 为大型分布式数据集提供了快速的键值查找。

特点：

把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。

 

CSV（3个文件）

它的表实际上是带有逗号分隔值的文本文件。

csv表允许以csv格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为 csv 表没有索引，所以通常在正常操作期间将数据保存在 innodb 表中，并且只在导入或导出阶段使用 csv 表。

特点：不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。就好比是下图

 

 

Archive（2 个文件）

这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。

特点：不支持索引，不支持 update delete

 

以上这些简单的说明了，不同的存储引擎适用不同的应用场景。而如果你的业务足够复杂，你可以自己开发一套,以下是官网 https://dev.mysql.com/doc/internals/en/custom-engine.html

 

执行计划

存储引擎负责数据存储，查询优化器负责通过解析语法树，来找到最合适的执行任务，然后执行计划负责调度执行任务，到存储引擎中拿数据去了。

而它就是一个API，对外暴露出来一致的，所以我们修改了表从存储结引擎，sql语法没有任务变化。

 

那么自此，整个查询流程完毕

 

总体概览 

先来一张很经典的图

 

1、 Connector：用来支持各种语言和 SQL 的交互，比如 PHP，Python，Java 的JDBC；

2、 Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、MySQL 复制、集群等等；

3、 Connection Pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等等；

4、 SQL Interface：用来接收用户的 SQL 命令，返回用户需要的查询结果

5、 Parser：用来解析 SQL 语句；

6、 Optimizer：查询优化器；

7、 Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key 缓存，权限缓存等等；

8、 Pluggable Storage Engines：插件式存储引擎，它提供 API 给服务层使用，跟具体的文件打交道。

 

架构分层

总体上，将Mysql分为三层，客户对接的连接层，执行操作的服务层和硬件交互的存储引擎层

连接层

我们的客户端要连接到 MySQL 服务器 3306 端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。

服务层

连接层会把 SQL 语句交给服务层，这里面又包含一系列的流程：

比如查询缓存的判断、根据 SQL 调用相应的接口，对我们的 SQL 语句进行词法和语法的解析（比如关键字怎么识别，别名怎么识别，语法有没有错误等等）。

然后就是优化器，MySQL 底层会根据一定的规则对我们的 SQL 语句进行优化，最后再交给执行器去执行。

存储引擎

存储引擎就是我们的数据真正存放的地方，在 MySQL 里面支持不同的存储引擎。

再往下就是内存或者磁盘。