MySQL查询SQL执行流程_mysql10.0查询sql流程

一条查询 SQL 语句是如何执行的？

程序或者工具要操作数据库，第一步要做什么事情？跟数据库建立连接

通信协议

首先，MySQL 必须要运行一个服务，监听默认的 3306 端口

在我们开发系统跟第三方对接的时候，必须要弄清楚的有两件事：

第一个就是通信协议，比如我们是用 HTTP 还是 WebService 还是 TCP？
第二个是消息格式，比如我们用 XML 格式，还是 JSON 格式，还是定长格式？报文头长度多少，包含什么内容，每个字段的详细含义

MySQL 是支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接

• 通信 类型： 同步 或者 异步

同步通信的特点：
1、同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库，线程会阻塞，等待数据库的返回
2、一般只能做到一对一，很难做到一对多的通信

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

一般来说我们连接数据库都是同步连接

• 连接方式： 长连接或者短连接

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

show global variables like 'wait_timeout'; -- 非交互式超时时间，如 JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具
 
默认都是 28800 秒，8 小时

我们怎么查看 MySQL 当前有多少个连接？

可以用 show status 命令：
show global status like 'Thread%';
 
Threads_cached：缓存中的线程连接数
Threads_connected：当前打开的连接数
Threads_created：为处理连接创建的线程数
Threads_running：非睡眠状态的连接数，通常指并发连接数。
 
每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。反过来，如果要杀死会话，就是 Kill 线程

有了连接数，怎么知道当前连接的状态？

可以使用 SHOW PROCESSLIST; （root 用户）查看 SQL 的执行状态。  参考：https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

一些常见的状态：https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html

MySQL 服务允许的最大连接数是多少呢？

在 5.7 版本中默认是 151 个，最大可以设置成 16384（2^14）。

show variables like 'max_connections';

show 的参数说明：

1、级别：会话 session 级别（默认）；全局 global 级别
2、动态修改：set （set global max_connections = 1000;），重启后失效；永久生效，修改配置文件/etc/my.cnf

MySQL 支持哪些通信协议呢？

第一种是 Unix Socket

比如我们在 Linux 服务器上，如果没有指定-h 参数，它就用 socket 方式登录（省略了-S /var/lib/mysql/mysql.sock）。它不用通过网络协议，也可以连接到 MySQL 的服务器，它需要用到服务器上的一个物理文件（/var/lib/mysql/mysql.sock）

select @@socket;

如果指定-h 参数，就会用第二种方式，TCP/IP 协议

mysql -h192.168.1.6 -uroot -p123456

我们的编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的，比如 mysql-connector-java-x.x.xx.jar

另外还有命名管道（Named Pipes）和内存共享（Share Memory）的方式，这两种通信方式只能在 Windows 上面使用，一般用得比较少

 

通信方式

• 单工

在两台计算机通信的时候，数据的传输是单向的。生活中的类比：遥控器

• 半双工

在两台计算机之间，数据传输是双向的，你可以给我发送，我也可以给你发送，但是在这个通讯连接里面，同一时间只能有一台服务器在发送数据，也就是你要给我发的话，也必须等我发给你完了之后才能给我发。生活中的类比：对讲机

• 全双工

数据的传输是双向的，并且可以同时传输。生活中的类比：打电话

 

MySQL 使用了半双工的通信方式？

要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能同时发生。所以客户端发送 SQL 语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管你的 SQL 语句有多大，都是一次性发送

比如我们用MyBatis动态SQL生成了一个批量插入的语句，插入10万条数据，values 后面跟了一长串的内容，或者 where 条件 in 里面的值太多，会出现问题
这个时候我们必须要调整 MySQL 服务器配置 max_allowed_packet 参数的值（默认是 4M），把它调大，否则就会报错

另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想要的数据就中断操作，这个时候会对网络和内存产生大量消耗

所以，我们一定要在程序里面避免不带 limit 的这种操作，比如一次把所有满足条件的数据全部查出来，一定要先 count 一下。如果数据量的话，可以分批查询

执行一条查询语句，客户端跟服务端建立连接之后呢？下一步要做什么？

查询缓存

MySQL 内部自带了一个缓存模块。缓存的作用我们应该很清楚了，把数据以 K V 的形式放到内存里面，可以加快数据的读取速度，也可以减少服务器处理的时间

但是 MySQL 的缓存好像比较陌生，从来没有去配置过，也不知道它什么时候生效？比如 user_innodb 有 500 万行数据，没有索引。我们在没有索引的字段上执行同样的查询，大家觉得第二次会快吗？

缓存没有生效，为什么？MySQL 的缓存默认是关闭的

show variables like 'query_cache%';

默认关闭的意思就是不推荐使用，为什么 MySQL 不推荐使用它自带的缓存呢？

主要是因为 MySQL 自带的缓存的应用场景有限

第一个是它要求 SQL 语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的 SQL
第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合
所以缓存这一块，我们还是交给 ORM 框架（比如 MyBatis 默认开启了一级缓存），或者独立的缓存服务，比如 Redis 来处理更合适

在 MySQL 8.0 中，查询缓存已经被移除了

语法解析和预处理 (Parser & Preprocessor)

为什么我的一条 SQL 语句能够被识别呢？它是怎么知道我输入的内容是错误的？

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

• 词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词

比如一个简单的 SQL 语句：

select name from user where id = 1;

它会打碎成 8 个符号，每个符号是什么类型，从哪里开始到哪里结束

• 语法解析

语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL 定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树（select_lex）

任何数据库的中间件，比如 Mycat，Sharding-JDBC（用到了 Druid Parser），都必须要有词法和语法分析功能，在市面上也有很多的开源的词法解析的工具（比如 LEX，Yacc）

• 预处理器

如果我写了一个词法和语法都正确的 SQL，但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？ 比如：select * from abc;

解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？实际上还是在解析的时候报错，解析 SQL 的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树

查询优化（Query  Optimizer）与查询执行计划

• 优化器

得到解析树之后，是不是执行 SQL 语句了呢？一条 SQL 语句是不是只有一种执行方式？或者说数据库最终执行的 SQL 是不是就是我们发送的 SQL？

这个答案是否定的。一条 SQL 语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的

但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

这个就是 MySQL 的查询优化器的模块（Optimizer）
查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种

https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Last_query_cost

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

show status like 'Last_query_cost';

• 优化器可以做什么

MySQL 的优化器能处理哪些优化类型呢？
1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表
2、有多个索引可以使用的时候，选择哪个索引

实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标，但是优化器也不是万能的，并不是再垃圾的 SQL 语句都能自动优化，也不是每次都能选择到最优的执行计划，大家在编写 SQL 语句的时候还是要注意

 

优化器是怎么得到执行计划的？

https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

首先我们要启用优化器的追踪（默认是关闭的）：

SHOW VARIABLES LIKE 'optimizer_trace';
 
set optimizer_trace='enabled=on';

注意开启这开关是会消耗性能的，因为它要把优化分析的结果写到表里面，所以不要轻易开启，或者查看完之后关闭它（改成 off）

注意：参数分为 session 和 global 级别

接着我们执行一个 SQL 语句，优化器会生成执行计划：

select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

这个时候优化器分析的过程已经记录到系统表里面了，我们可以查询：

select * from information_schema.optimizer_trace\G

它是一个 JSON 类型的数据，主要分成三部分，准备阶段、优化阶段和执行阶段

join_preparation-SQL准备阶段

join_optimization-SQL优化阶段

join_execution-SQL执行阶段

分析完记得关掉它

set optimizer_trace="enabled=off";
SHOW VARIABLES LIKE 'optimizer_trace';

• 优化器得到的结果

优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。当然，这个执行计划是不是一定是最优的执行计划呢？不一定，因为 MySQL 也有可能覆盖不到所有的执行计划。

怎么查看 MySQL 的执行计划呢？比如多张表关联查询，先查询哪张表？在执行查询的时候可能用到哪些索引，实际上用到了什么索引？

MySQL 提供了一个执行计划的工具。我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息

EXPLAIN select name from user where id=1;

注意： Explain 的结果也不一定最终执行的方式

存储引擎

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

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

• 存储引擎基本介绍

在关系型数据库里面，数据是放在什么结构里面的？

放在表 Table 里面的，我们可以把这个表理解成 Excel 电子表格的形式。所以表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型

在 MySQL 里面，支持多种存储引擎，他们是可以替换的，所以叫做插件式的存储引擎

• 查看存储引擎

show table status from `vincent`;

或者通过 DDL 建表语句来查看
在 MySQL 里面，我们创建的每一张表都可以指定它的存储引擎，而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且，创建表之后还可以修改存储引擎

一张表使用的存储引擎决定我们存储数据的结构，那在服务器上它们是怎么存储的呢？

我们先要找到数据库存放数据的路径：

show variables like 'datadir';

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

不同的存储引擎存放数据的方式不一样，产生的文件也不一样，innodb 是 1 个，memory 没有，myisam 是两个

 

存储引擎比较

• 常见存储引擎

MyISAM 和 InnoDB 是我们用得最多的两个存储引擎，在 MySQL 5.5 版本之前，默认的存储引擎是 MyISAM，它是 MySQL 自带的。我们创建表的时候不指定存储引擎，它就会使用 MyISAM 作为存储引擎
MyISAM 的前身是 ISAM（Indexed Sequential Access Method：利用索引，顺序存取数据的方法），5.5 版本之后默认的存储引擎改成了 InnoDB，它是第三方公司为 MySQL 开发的。

为什么要改呢？

最主要的原因还是 InnoDB 支持事务，支持行级别的锁，对于业务一致性要求高的场景来说更适合

• 数据库支持的存储引擎

我们可以用这个命令查看数据库对存储引擎的支持情况：show engines ;

其中有存储引擎的描述和对事务、XA 协议和 Savepoints 的支持

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，提升查询效率

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

 

• Memory （1个文件）

将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而 ndbcluster 为大型分布式数据集提供了快速的键值查找

特点：

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

 

• CSV （3 个文件）

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

特点：

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

 

• Archive （2 个文件）

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

特点：

不支持索引，不支持 update delete

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

 

如何选择存储引擎

• 如果对数据一致性要求比较高，需要事务支持，可以选择 InnoDB
• 如果数据查询多更新少，对查询性能要求比较高，可以选择 MyISAM
• 如果需要一个用于查询的临时表，可以选择 Memory
• 如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用 C 语言开发一个存储引擎：https://dev.mysql.com/doc/internals/en/custom-engine.html

 

执行引擎（Query Execution  Engine），返回结果

存储引擎，它是我们存储数据的形式，是谁使用执行计划去操作存储引擎呢？
这就是我们的执行引擎，它利用存储引擎提供的相应的 API 来完成操作

为什么我们修改了表的存储引擎，操作方式不需要做任何改变？

因为不同功能的存储引擎实现的 API 是相同的。最后把数据返回给客户端，即使没有结果也要返回