时间戳

首页 > 技术文章 > MySQL执行SQL的流程

MySQL执行SQL的流程

snail-gao 2020-01-15 19:38 原文

1.一条SQL语句的执行流程

1.1通信协议

MySQL 是支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接。
# 通信类型：同步或者异步
同步通信的特点：
1、同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库，线程会阻塞，等待数据库的返回。
2、一般只能做到一对一，很难做到一对多的通信。

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

# 连接方式：长连接或者短连接
MySQL 既支持短连接，也支持长连接。
保持长连接会消耗内存。长时间不活动的连接，MySQL 服务器会断开。

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

查看 MySQL 当前有多少个连接?

show global status like 'Thread%';

Threads_cached：缓存中的线程连接数。
Threads_connected：当前打开的连接数。
Threads_created：为处理连接创建的线程数。
Threads_running：非睡眠状态的连接数，通常指并发连接数。

查询当前连接的状态？

SHOW PROCESSLIST;
-- https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

常见的状态：
https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
状态 含义
Sleep 线程正在等待客户端，以向它发送一个新语句
Query 线程正在执行查询或往客户端发送数据
Locked 该查询被其它查询锁定
Copying to tmptable on disk 临时结果集合大于 tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式，以节约存储器
Sending data 线程正在为 SELECT 语句处理行，同时正在向客户端发送数据
Sorting for group 线程正在进行分类，以满足 GROUP BY 要求
Sorting for order 线程正在进行分类，以满足 ORDER BY 要求

MySQL 服务允许的最大连接数是多少呢？
在 5.7 版本中默认是 151 个，最大可以设置成 16384（2^14）.

show variables like 'max_connections';

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

# MySQL 支持哪些通信协议?
第一种是 Unix Socket。
比如我们在 Linux 服务器上，如果没有指定-h 参数，它就用 socket 方式登录。
如果指定-h 参数，就会用第二种方式，TCP/IP 协议。
命名管道（Named Pipes）和内存共享（Share Memory）的方式，这两种
通信方式只能在 Windows 上面使用，一般用得比较少。

1.2通信方式

MySQL 使用了半双工的通信方式？
  要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能同时发生。所以客户端发送 SQL 语句给服务端的时候，
（在一次连接里面）数据是不能分成小块发送的，不管你的 SQL 语句有多大，都是一次性发送。比如我们用MyBatis动态SQL生成了一个批量插入的语句，
插入10万条数据，values后面跟了一长串的内容，或者 where 条件 in 里面的值太多，会出现问题。这个时候我们必须要调整 MySQL 服务器配置 
max_allowed_packet 参数的值（默认是 4M），把它调大，否则就会报错。
  另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想要的数据就中断操作，
这个时候会对网络和内存产生大量消耗。所以，我们一定要在程序里面避免不带 limit 的这种操作，比如一次把所有满足条件的数据全部查出来，
一定要先 count 一下。如果数据量的话，可以分批查询。

1.3查询缓存

show variables like 'query_cache%';

# 默认关闭的意思就是不推荐使用，为什么 MySQL 不推荐使用它自带的缓存呢？
   主要是因为 MySQL 自带的缓存的应用场景有限，第一个是它要求 SQL 语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的 SQL。
   第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。
   所以缓存这一块，我们还是交给 ORM 框架（比如 MyBatis 默认开启了一级缓存），或者独立的缓存服务，比如 Redis 来处理更合适。
   在 MySQL 8.0 中，查询缓存已经被移除了。

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

使用 MySQL 的 Parser 解析器和 Preprocessor 预处理模块

1.4.1 词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。
比如一个简单的 SQL 语句：

select name from user where id = 1;

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

1.4.2 语法解析

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


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

1.4.3 预处理器

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

select * from user;

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

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

1.5.1 什么是优化器？

得到解析树之后，是不是执行 SQL 语句了呢？一条 SQL 语句是不是只有一种执行方式？或者说数据库最终执行的SQL是不是就是我们发送的 SQL？
这个答案是否定的。一条 SQL 语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，
这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？
这个就是 MySQL 的查询优化器的模块（Optimizer）。查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），
然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种。

查看查询开销的命令：

show status like 'Last_query_cost';
-- https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Last_query_cost

1.5.2 优化器可以做什么?

1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。
2、有多个索引可以使用的时候，选择哪个索引。实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。

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

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 类型的数据，主要分成三部分，准备阶段、优化阶段和执行阶段。

expanded_query 是优化后的 SQL 语句。
considered_execution_plans 里面列出了所有的执行计划。
分析完记得关掉它：

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

1.5.4 优化器得到的结果

优化完之后，得到一个什么东西呢？
优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。当然，这个执行计划是不是一定是最优的执行计划呢？不一定，因为 MySQL 也有可能覆盖不到所有的执行计划。
我们怎么查看 MySQL 的执行计划呢？比如多张表关联查询，先查询哪张表？在执行查询的时候可能用到哪些索引，实际上用到了什么索引？
MySQL 提供了一个执行计划的工具。我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。

EXPLAIN select name from user where id=1;

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

1.6存储引擎

在关系型数据库里面，数据是放在Table表里面的。我们可以把这个表理解成 Excel 电子表格的形式。所以我们的表在存储数据的同时，
还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。
在 MySQL 里面，支持多种存储引擎，他们是可以替换的，所以叫做插件式的存储引擎。

1.6.1 查看存储引擎

show table status from `user`;

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

数据库存放数据的路径：

show variables like 'datadir';

在MySQL 8.0之前任何一个存储引擎都有一个frm文件，这个是表结构定义文件。

但MySQL8.0之后数据库的innodb表全部放至在datadir下的mysql.ibd中；将不再把表结构放在.frm文件中，而是存放在元数据表中。
更多特性官网地址：https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

1.6.2 存储引擎的比较

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

应用范围比较小。表级锁定限制了读/写的性能，因此在 Web 和数据仓库配置中，它通常用于只读或以读为主的工作。
特点：
   支持表级别的锁（插入和更新会锁表）。不支持事务。
   拥有较高的插入（insert）和查询（select）速度。
   存储了表的行数（count 速度更快）。
（怎么快速向数据库插入 100 万条数据？我们有一种先用 MyISAM 插入数据，然后修改存储引擎为 InnoDB 的操作。）
适合：只读之类的数据分析的项目。

InnoDB

mysql 5.7+和8.0 中的默认存储引擎。InnoDB 是一个事务安全（与 ACID 兼容）的 MySQL存储引擎，它具有提交、回滚和
崩溃恢复功能来保护用户数据。InnoDB 行级锁（不升级为更粗粒度的锁）和 Oracle 风格的一致非锁读提高了多用户并发性
和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了保持数据完整性，InnoDB 还支持
外键引用完整性约束。
特点：
   支持事务，支持外键，因此数据的完整性、一致性更高。
   支持行级别的锁和表级别的锁。
   支持读写并发，写不阻塞读（MVCC）。
   特殊的索引存放方式，可以减少 IO，提升查询效率。
适合：经常更新的表，存在并发读写或者有事务处理的业务系统。

Memory

将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。
其使用案例正在减少；InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，
而 ndbcluster 为大型分布式数据集提供了快速的键值查找。
特点：
把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。
将表中的数据存储到内存中

CSV

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

Archive

这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。
特点：不支持索引，不支持 update delete。

1.6.3 存储引擎的选择

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

1.7 执行引擎，返回结果

执行引擎，它利用存储引擎提供的相应的 API 来完成操作，不同功能的存储引擎实现的 API 是相同的。
最后把数据返回给客户端，即使没有结果也要返回。

推荐阅读