一条查询sql的完整执行流程（从连接到引擎，穿插涉及到的知识，超详细）

文章目录

• 1.连接
• 2.查询缓存
• 3. 语法解析和预处理
  • 3.1 词法解析
  • 3.2 语法解析
  • 3.3预处理器
• 4.查询优化(Query Optimizer)与查询执行计划
  • 4.1 什么是优化器
  • 4.2 优化器可以做什么？
• 5.存储引擎
  • 5.1 存储引擎基本介绍
    • 5.1.1 MyISam引擎 （3个文件）
    • 5.1.2 InnoDB引擎（两个文件）
    • 5.1.3 Memory引擎（一个文件）
  • 5.2 如何选择存储引擎
• 5 执行引擎(Query Execution Engine),返回结果

当我们的工具或者程序连接到数据库之后，实际上发生了什么事情？它的内部是怎么工作的？ 就像我们到餐厅去吃饭，点了菜以后，过一会儿菜端上来了，后厨里面有哪些人？ 他们分别做了什么事情？这个就是MySQL的整体架构和工作流程了。 先贴个整体流程，大家大概有个印象：

在这里插入图片描述

1.连接

MySQL服务监听的端口默认是3306，客户端连接服务端的方式有很多。 可以是同步的也可以是异步的，可以是长连接也可以是短连接，可以是TCP也可以是Socket，MySQL有专门处理连接的模块，连接的时候需要验证权限。

我们怎么查看MySQL当前有多少个连接? 可以用show status命令，模糊匹配Thread:

show global status like 'Thread%';

建个本地库，执行以下给你们瞅瞅：

在这里插入图片描述

查询结果含义如下：

在这里插入图片描述

那么问题来了。为什么连接数是查看线程？客户端的连接和服务端的线程有什么关系?

客户端每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。 反过来， 如果要杀死会话，就是Kill线程。 既然是分配线程，保持连接肯定会消耗服务端的资源。 MySQL会把那些长时间 不活动的（SLEEP）连接自动断开。

顺便带你们看下连接超时时间

show global variables like 'wait_timeout';//非交互式超时时间，如 JDBC 程序
show global variables like 'interactive_timeout';//交互式超时时间，如数据库工具

在这里插入图片描述

可以看到默认都是28800秒，8小时。 既然连接消耗资源，MySQL服务允许的最大连接数（也就是并发数）默认是多少呢？

show variables like 'max_connections';

上图：

在这里插入图片描述

这个不同版本是不一样的，我的是200，你们可以看下你们的 但是最大上限都是100000（别数了，十万） 私下有人问参数，再顺便解释一下

在这里插入图片描述

MySQL中的参数（变量）分为session和global级别，分别是在当前会话中生效和 全局生效 但是并不是每个参数都有两个级别，比如max_connections就只有全局级别。 当没有带参数的时候，默认是session级别，包括查询和修改。

修改了一个非全局参数以后，在本窗口査询已经生效，但是其他窗口不生效，你们可以自己执行下，新建查询看看效果 例如：

show VARIABLES like 'autocommit';
set autocommit = on;

所以，如果只是临时修改，建议修改session级别。 如果需要在其他会话中生效，必须显式地加上global参数。

2.查询缓存

MySQL内部自带了一个缓存模块。 有一张500万行数据的表，没有索引，如果我两次执行一模一样的SQL语句，第二 次会不会变得很快？ 不会，因为MySQL的缓存默认是关闭的。 既然默认关闭，那肯定就是不推荐使用了，为什么呢？ 主要是因为MySQL自带的缓存的应用场景有限

第一个是它要求SQL语句必须一 模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL。 第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对 于有大量数据更新的应用，也不适合。

所以缓存这一块，我们还是交给ORM框架(比如MyBatis默认开启了一级缓存)， 或者独立的缓存服务，比如Redis来处理更合适。

而且，MySQL 8.0中，缓存已经被移除了！！！！！！！！！ 记得张三丰教张无忌太极拳时说的话吗？ 你还记得多少，忘记这块吧，已经没有实际使用意义了，除了面试 如同面试官问你有JVM调优经验吗，我这个初级开发会回答：调优经验多少取决于我近期的面试频率

3. 语法解析和预处理

没有使用缓存的话，就会跳过缓存的模块，下一步要做什么呢？ 我们先想一下，为什么一条SQL语句能够被识别呢？假如随便执行一个字符串 pengyuyan,服务器报了一个1064的错

在这里插入图片描述

这个就是MySQL的Parser解析器和Preprocessor预处理模块。 这步主要做的事情是对语句基于SQL语法进行词法和语法的分析以及语义的解析。

3.1 词法解析

词法分析就是把一个完整的SQL语句打碎成一个个的单词。然后记录位置，每个符号是什么类型，从哪里开始到哪里结束。

3.2 语法解析

第二步就是语法分析，语法分析会对SQL做一些语法检查，比如单引号有没有闭合等, 然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树(select_lex)。 举个栗子：

在这里插入图片描述

词法语法分析是一个非常基础的功能，Java的编译器、百度搜索引擎如果要识别语句，必须也要有词法语法分析功能。 任何数据库的中间件，要解析SQL完成路由功能，也必须要有词法和语法分析功能, 比如Mycat, Sharding-JDBC （用到了 Druid Parser）。在市面上也有很多的开源的词法解析的工具（比如LEX, Yacc）。

3.3预处理器

如果我写了一个词法和语法都正确的SQL,但是表名或者字段不存在，会在 哪里报错？是在数据库的执行层还是解析器？比如： select * from penyuyan; 我想了想，似乎解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？ 实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。 它会检査生成的解析树，解决解析器无法解析的语义。比如，它会检査表和列名是否存在，检査名字和别名，保证没有歧义。 预处理之后得到一个新的解析树。

4.查询优化(Query Optimizer)与查询执行计划

4.1 什么是优化器

得到解析树之后，是不是执行SQL语句了呢？ 这里我们有一个问题，一条SQL语句是不是只有一种执行方式？或者说数据库最终执行的SQL是不是就是我们发送的SQL? 这个答案是否定的。 一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。 但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

这个就是MySQL的査询优化器的模块(Optimizer)。

査询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan),然后选择一种最优的执行计划，MySQL里面使用的是基于开销(cost)的优化器，哪种执行计划开销最小，就用哪种。

可以使用这个命令査看査询的开销：

show status like 'Last_query_cost';

在这里插入图片描述

4.2 优化器可以做什么？

举两个简单的例子: 1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。 2、有多个索引可以使用的时候，选择哪个索引。

实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。

如果对于优化器的细节感兴趣，可以看看《数据库査询优化器的艺术-原理解析与SQL性能优化》14章。

但是优化器也不是万能的，并不是再垃圾的SQL语句都能自动优化，也不是每次都能选择到最优的执行计划，大家在编写SQL语句的时候还是要注意。

优化器只是很有限的帮你优化，关键还是得看你怎么写。

优化完之后，得到一个什么东西呢？优化器最终会把解析树变成一个査询执行计划

查询执行计划是一个数据结构。

我们怎么査看MySQL的査询执行计划呢？比如多张表关联查询，先査询哪张表？在执行査询的时候可能用到哪些索引，实际上用到了什么索引? MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN,就可以看到执行计划的信息。

在这里插入图片描述

如果要得到更详细的信息，还可以用FORMAT=JSON

在这里插入图片描述

得到的就是这样一个东东：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.55"
    },
    "table": {
      "table_name": "t_user",
      "access_type": "ALL",
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 3,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.30",
        "prefix_cost": "0.55",
        "data_read_per_join": "96"
      },
      "used_columns": [
        "id",
        "name"
      ]
    }
  }
}

在这里插入图片描述

还能进一步看更详细的

5.存储引擎

得到执行计划以后，SQL语句是不是终于可以执行了?

问题又来了: 1、从逻辑的角度来说，我们的数据是放在哪里的，或者说放在一个什么结构里面? 2、执行计划在哪里执行？是谁去执行?

5.1 存储引擎基本介绍

比如这三张表，都是二维的存储结构，表面上看起来是一模一样的。

在这里插入图片描述

但是他们的表类型是不一样的。

在这里插入图片描述

表面上一样，底层肯定是有差异的。我们先找到数据库存放数据的路径:

show variables like 'datadir';

默认情况下，每个数据库有一个自己文件夹，举个例子： 任何一个存储引擎都有一个frm文件，这个是表结构定义文件。

在这里插入图片描述

不同的存储引擎存放数据的方式不一样，产生的文件也不一样，innodb是2个, memory1个，myisam是3个。

以本文来按照文件多少大概介绍一下：

5.1.1 MyISam引擎 （3个文件）

These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations. 应用范围比较小。表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，它通常用于只读或以读为主的工作。

特点:

1. 支持表级别的锁（插入和更新会锁表）。不支持事务。
2. 拥有较高的插入（insert）和查询（select）速度。
3. 存储了表的行数（count速度更快）。

（怎么快速向数据库插入100万条数据？我们有一种先用MylSAM插入数据,然后修改存储引擎为InnoDB的操作。）

适合：只读之类的数据分析的项目。

5.1.2 InnoDB引擎（两个文件）

The default storage engine in MySQL 5.7. InnoDB is a transaction-safe （ACID compliant） storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking （without escalation to coarser granularity locks） and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. mysql 5.7中的默认存储引擎。InnoDB是一个事务安全（与ACID兼容）的MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级 为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将 用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性， InnoDB还支持外键引用完整性约束。

特点：

1. 支持事务，支持外键，因此数据的完整性、一致性更高。
2. 支持行级别的锁和表级别的锁。
3. 支持读写并发，写不阻塞读（MVCC）。
4. 特殊的索引存放方式，可以减少IO，提升査询效率。

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

InnoDB本来是InnobaseOy公司开发的，它和MySQL AB公司合作开源了 InnoDB的代码。但是没想到MySQL的竞争对手Oracle把InnobaseOy收购了。后来08年Sun公司（开发Java语言的Sun）收购了 MySQL AB, 09年Sun公司又被Oracle收购了，所以MySQL, InnoDB又是一家了。有人觉得MySQL越来越像Oracle,其实也是这个原因。

5.1.3 Memory引擎（一个文件）

Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets. 将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。这 个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB及其缓冲池内存区域提供了一 种通用、持久的方法来将大部分或所有数据保存在内存中，而ndbduster为大型分布式 数据集提供了快速的键值查找。

特点：

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

再介绍俩还算常见的引擎 CSV （3个文件）

Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage. 它的表实际上是带有逗号分隔值的文本文件。csv表允许以CSV格式导入或转储数据, 以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以通常在正常操作期间将数据保存在innodb表中，并且只在导入或导出阶段使用csv表。

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

Archive （2 个文件）

These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information. 这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。 特点：不支持索引，不支持update deleteo

这是MySQL里面常见的一些存储引擎，我们看到了，不同的存储引擎提供的特性都不一样，它们有不同的存储机制、索引方式、锁定水平等功能。 我们在不同的业务场景中对数据操作的要求不同，就可以选择不同的存储引擎来满足我们的需求，这个就是MySQL支持这么多存储引擎的原因。

5.2 如何选择存储引擎

如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。 如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM。 如果需要一个用于查询的临时表，可以选择Memory。

如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部 手册用C语言开发一个存储引擎： https://dev.mvsql.com/doc/internals/en/custom-engine.html 按照这个开发规范，实现相应的接口，给执行器操作。

也就是说，为什么能支持这么多存储引擎，还能自定义存储引擎，表的存储引擎改了对Server访问没有任何影响，就是因为大家都遵循了一定了规范，提供了相同的操作接口。 每个存储引擎都有自己的服务。

show engine innodb status;

这些存储引擎用不同的方式管理数据文件，提供不同的特性，但是为上层提供相同的接口。

5 执行引擎(Query Execution Engine),返回结果

OK,存储引擎分析完了，它是我们存储数据的形式，继续存储引擎里的第二个问题，是谁使用执行计划去操作存储引擎呢？

就是我们的执行引擎，它利用存储引擎提供的相应的API来完成操作。（这里具体怎么操作的，已经无需再深入了）

为什么我们修改了表的存储引擎，操作方式不需要做任何改变？因为不同功能的存 储引擎实现的API是相同的。

最后把数据返回给客户端。

在这里插入图片描述

还看啥？都返回给客户端了，完事了啊