MySQL 架构与 SQL 执行流程
in MySQL with 0 comment

MySQL 架构与 SQL 执行流程

in MySQL with 0 comment

我们操作数据库有各种各样的方式,比如 Linux 系统中的命令行,比如数据库工具
Navicat,比如程序: Java 语言的 JDBC API 或者 ORM 框架。
当工具或者程序连接到数据库之后,实际上发生了什么事情?它的内部是怎么工作的?

以一条查询语句为例,我们来看下 MySQL 的工作流程是什么样的。

1. 一条查询SQL语句是如何执行的?

MySQLPerform

程序或者工具要操作数据库,第一步要跟数据库建立连接。

1.1.通信协议

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

1.1.1.通信协议

MySQL 支持多种通信协议,可以使用同步/异步的方式,支持长连接/短连接。通信类型:同步或者异步

一般来说我们的客户端连接数据库都是同步连接。

连接方式:长连接或者短连接

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

保持长连接会消耗内存。长时间不活动的连接,MySQL 服务器会断开。

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

默认都是 28800 秒,8 小时。
怎么查看 MySQL 当前有多少个连接?

show global status like \'Thread%\';

每产生一个连接或者一个会话,在服务端就会创建一个线程来处理。反过来,如果要杀死会话,就是 Kill 线程。

MySQL 服务允许的最大连接数是多少?
在 5.7 版本中默认是 151 个,最大可以设置成 100000。

show variables like \'max\_connections\';

image.png

show 的参数说明:

  1. 级别:会话 session 级别(默认);全局 global 级别
  2. 动态修改:set,重启后失效;永久生效,修改配置文件/etc/my.cnf
set global max\_connections = 1000;

通信协议

MySQL 支持哪些通信协议呢?第一种是 Unix Socket。比如我们在 Linux 服务器上,如果没有指定-h 参数,它就用 socket 方式登录。

image.png

它不用通过网络协议,也可以连接到 MySQL 的服务器,它需要用到服务器上的一个物理文件(/var/lib/mysql/mysql.sock)。

select @\@socket;

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

mysql -h192.168.8.211 -uroot -p123456

1.1.2.通信方式

第二个是通信方式

image.png

单工:

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

半双工:

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

全双工:

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

MySQL 使用了半双工的通信方式。要么是客户端向服务端发送数据,要么是服务端向客户端发送数据,这两个动作不能同时发生。所以客户端发送 SQL 语句给服务端的时候,在一次连接里面数据是不能分成小块发送的,不管你的 SQL 语句有多大,都是一次性发送。

比如我们用 MyBatis 动态 SQL 生成了一个批量插入的语句,插入 10 万条数据,values 后面跟了一长串的内容,或者 where 条件 in 里面的值太多,会出现问题。

这个时候我们必须要调整 MySQL 服务器配置 max_allowed_packet 参数的值(默认是 4M),把它调大,否则就会报错。
image.png

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

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

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

1.2.查询缓存

MySQL 内部自带了一个缓存模块。执行相同的查询之后我们发现缓存没有生效,为什么?MySQL 的缓存默认是关闭的。

show variables like 'query_cache%';

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

主要是因为 MySQL 自带的缓存的应用场景有限,第一个是它要求 SQL 语句必须一模一样,中间多一个空格,字母大小写不同都被认为是不同的的 SQL。

第二个是表里面任何一条数据发生变化的时候,这张表所有缓存都会失效,所以对于有大量数据更新的应用,也不适合。

所以缓存还是交给 ORM 框架(比如 MyBatis 默认开启了一级缓存),或者独立的

缓存服务,比如 Redis 来处理更合适。

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

1.3 语法解析和预处理(Parser & Preprocessor) 为什么一条 SQL 语句能够被识别呢?假如随便执行一个字符串 penyuyan,服务器

报了一个 1064 的错:
image.png

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

1.3.1.词法解析

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

select name from user where id = 1;

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

1.3.2.语法解析

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

mysql_select.jpg

1.3.3.预处理器

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

select * from penyuyan;

还是在解析的时候报错,解析 SQL 的环节里面有个预处理器。它会检查生成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。预处理之后得到一个新的解析树。

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

1.4.1. 什么是优化器?

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

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

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

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

show status like 'Last_query_cost';

MySQL官网

1.4.2. 优化器可以做什么?

MySQL 的优化器能处理哪些优化类型呢?举两个简单的例子:

  1. 当我们对多张表进行关联查询的时候,以哪个表的数据作为基准表。

  2. 有多个索引可以使用的时候,选择哪个索引。

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

1.4.3. 优化器得到的结果

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

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

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

EXPLAIN select name from user where id=1;

1.5.存储引擎

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

1.5.1. 存储引擎基本介绍

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

在 MySQL 里面,支持多种存储引擎,他们是可以替换的,所以叫做插件式的存储引擎。为什么要支持这么多存储引擎呢?一种还不够用吗?

1.5.2. 查看存储引擎

数据库里面已经存在的表,怎么查看它们的存储引擎呢?

show table status from `gupao`;

image.png

或者通过 DDL 建表语句来查看。

在 MySQL 里面,每一张表都可以指定它的存储引擎,而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且,创建表之后还可以修改存储引擎。

我们说一张表使用的存储引擎决定我们存储数据的结构,那在服务器上它们是怎么存储的呢?我们先要找到数据库存放数据的路径:

show variables like 'datadir';

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

image.png

不同的存储引擎存放数据的方式不一样,产生的文件也不一样,innodb 是 1 个, memory 没有,myisam 是两个。这些存储引擎的差别在哪呢?

1.5.3. 存储引擎比较数据库支持的存储引擎我们可以用这个命令查看数据库对存储引擎的支持情况:

show engines ;

其中有存储引擎的描述和对事务、XA 协议和 Savepoints 的支持。
XA 协议用来实现分布式事务(分为本地资源管理器,事务管理器)。
Savepoints 用来实现子事务(嵌套事务)。创建了一个 Savepoints 之后,事务就可以回滚到这个点,不会影响到创建 Savepoints 之前的操作。
image.png

这些数据库支持的存储引擎,分别有什么特性呢?
官网

MyISAM(3 个文件)

MySQL 自带的存储引擎,由 ISAM 升级而来。

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 和数据仓库配置中,它通常用于只读或以读为主的工作。

特点:支持表级别的锁(插入和更新会锁表)。不支持事务。拥有较高的插入(insert)和查询(select)速度。存储了表的行数(count 速度更快)。

(怎么快速向数据库插入 100 万条数据?我们有一种先用 MyISAM 插入数据,然后修改存储引擎为 InnoDB 的操作。)适合:只读之类的数据分析的项目。

InnoDB(2 个文件)

[https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html]

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 还支持外键引用完整性约束。

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

Memory(1 个文件)

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

特点:把数据放在内存里面,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失,只适合做临时表。将表中的数据存储到内存中。默认使用哈希索引。

CSV(3 个文件)

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

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

Archive(2 个文件)

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

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

1.5.4 如何选择存储引擎?如果对数据一致性要求比较高,需要事务支持,可以选择 InnoDB。

如果数据查询多更新少,对查询性能要求比较高,可以选择 MyISAM。

如果需要一个用于查询的临时表,可以选择 Memory。

如果所有的存储引擎都不能满足你的需求,并且技术能力足够,可以根据官网内部手册用 C 语言开发一个存储引擎:

官网

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

谁使用执行计划去操作存储引擎呢?这就是执行引擎(执行器),它利用存储引擎提供的相应的 API 来完成操作。

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

最后把数据返回给客户端,即使没有结果也要返回。

2. MySQL体系结构总结

2.1.模块详解

image.png

  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给服务层使用,跟具体的文件打交道。

2.2.架构分层

总体上,我们可以把 MySQL 分成三层,跟客户端对接的连接层,真正执行操作的服务层,和跟硬件打交道的存储引擎层(参考 MyBatis:接口、核心、基础)。
image.png

2.1.1.连接层

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

2.1.2.服务层

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

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

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

2.1.3.存储引擎

存储引擎就是我们的数据真正存放的地方,在 MySQL 里面支持不同的存储引擎。再往下就是内存或者磁盘。

3. 一条更新SQL是如何执行的?

更新流程和查询流程有什么不同呢?

基本流程也是一致的,也就是说,它也要经过解析器、优化器的处理,最后交给执行器。区别就在于拿到符合条件的数据之后的操作。

3.1.缓冲池 Buffer Pool

首先,InnnoDB 的数据都是放在磁盘上的,存储引擎要操作数据,必须先把磁盘里面的数据加载到内存里面。

这里就有个问题,是不是我们需要的数据多大,我们就一次从磁盘加载多少数据到内存呢? 磁盘 I/O 的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘的不同的地方,那就意味着会产生很多次的 I/O 操作。

所以,无论是操作系统的文件管理系统也好,还是存储引擎也好,都有一个预读取的概念。也就是说,当磁盘上的一块数据被读取的时候,很有可能它附近的位置也会马上被读取到,这个就叫做局部性原理。那么这样,我们干脆每次多读取一点,而不是用多少读多少。

我们设定了一个存储引擎从磁盘读取数据到内存的最小的单位,叫做页。操作系统也有页的概念。操作系统的页大小一般是 4K,而在 InnoDB 里面,这个最小的单位默认是 16KB 大小,它是一个逻辑单位。

我们要操作的数据就在这样的页里面,数据所在的页叫数据页。
我们对于数据页的操作,不是每次都直接操作磁盘,因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术,也就是把磁盘读到的页放到一块内存区域里面。下一次读取相同的页,先判断是不是在这个内存区域里面,如果是,就直接读取,不用再次访问磁盘。这个内存区域就叫Buffer Pool

BufferPool.jpg

修改数据的时候,先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候,我们把它叫做脏页。InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘,每隔一段时间就一次性地把多个修改写入磁盘,这个动作就叫做刷脏。

3.2 InnoDB内存结构和磁盘结构

官网

image.png

3.2.1 内存结构

Buffer Pool 缓存的是 page 页面信息。查看服务器状态,里面有很多跟 Buffer Pool 相关的信息:

SHOW STATUS LIKE '%innodb_buffer_pool%';

Buffer Pool 默认大小是128M(134217728 字节),可以调整。查看参数(系统变量):

SHOW VARIABLES like '%innodb_buffer_pool%';

3.2.1.1 redo log

思考一个问题:如果 Buffer Pool里面的脏页还没有刷入磁盘时,数据库宕机或者重启,这些数据丢失。

为了避免这个问题,InnoDB 把所有对页面的修改操作专门写入一个日志文件,并且在数据库启动时从这个文件进行恢复操作(实现 crash-safe)------用它来实现事务的持久性

redolog.jpg

这个文件就是磁盘的 redo log(叫做重做日志),对应于/var/lib/mysql/目录下的 ib_logfile0 和 ib_logfile1,每个 48M。

这 种 日 志 和 磁 盘 配 合 的 整 个 过 程 , 其 实 就 是 MySQL 里 的 WAL 技 术(Write-Ahead Logging),它的关键点就是先写日志,再写磁盘

show variables like 'innodb_log%';
含义
innodb_log_file_size指定每个文件的大小,默认48M
innodb_log_files_in_group指定文件的数量,默认为2
innodb_log_group_home_dir指定文件所在路径,相对或绝对。如果不指定,则为 datadir 路径。

问题:同样是写磁盘,为什么不直接写到 db file 里面去?为什么先写日志再写磁盘?我们先来了解一下随机 I/O 和顺序 I/O 的概念。

如果我们所需要的数据是随机分散在磁盘上不同页的不同扇区中,那么找到相应的数据需要等到磁臂旋转到指定的页,然后盘片寻找到对应的扇区,才能找到我们所需要的一块数据,一次进行此过程直到找完所有数据,这个就是随机 IO,读取数据速度较慢。

假设我们已经找到了第一块数据,并且其他所需的数据就在这一块数据后边,那么就不需要重新寻址,可以依次拿到我们所需的数据,这个就叫顺序 IO。

刷盘是随机 I/O,而记录日志是顺序 I/O(连续写的),顺序 I/O 效率更高。因此先把修改写入日志文件,在保证了内存数据的安全性的情况下,可以延迟刷盘时机,进而提升系统吞吐。

这个 redo log 有什么特点?

除了 redo log之外,还有一个跟修改有关的日志,叫做undo log。redo log 和 undo log 与事务密切相关,统称为事务日志。

3.2.1.2 undo log

tablespaces
innodb-undo-logs

undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态(不包括 select)。如果修改数据时出现异常,可以用 undo log 来实现回滚操作(保持原子性)。

在执行 undo 的时候,仅仅是将数据从逻辑上恢复至事务之前的状态,而不是从物
理页面上操作实现的,属于逻辑格式的日志。 undo Log 的数据默认在系统表空间 ibdata1 文件中,因为共享表空间不会自动收缩,也可以单独创建一个 undo 表空间。

show global variables like '%undo%';

有了这些日志之后,我们来总结一下一个更新操作的流程,这是一个简化的过程。 name 原值是 qingshan。

update user set name = 'penyuyan' where id=1;
  1. 事务开始,从内存或磁盘取到这条数据,返回给 Server 的执行器;
  2. 执行器修改这一行数据的值为 penyuyan;
  3. 记录 name=qingshan 到 undo log;
  4. 记录 name=penyuyan 到 redo log;
  5. 调用存储引擎接口,在内存(Buffer Pool)中修改 name=penyuyan;
  6. 事务提交。

内存和磁盘之间,工作着很多后台线程。

3.2.2 后台线程

后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后台线程分为:master thread,IO thread,purge thread,page cleaner thread。

3.3 Binlog

binary-log

除了 InnoDB 架构中的日志文件,MySQL 的 Server 层也有一个日志文件,叫做 binlog,它可以被所有的存储引擎使用。

binlog 以事件的形式记录了所有的 DDL 和 DML 语句(因为它记录的是操作而不是数据值,属于逻辑日志),可以用来做主从复制和数据恢复。

跟 redo log 不一样,它的文件内容是可以追加的,没有固定大小限制。

在开启了 binlog 功能的情况下,我们可以把 binlog 导出成SQL语句,把所有的操作重放一遍,来实现数据的恢复

binlog 的另一个功能就是用来实现主从复制,它的原理就是从服务器读取主服务器的 binlog,然后执行一遍。

有了这两个日志之后,我们来看一下一条更新语句是怎么执行的(redo 不能一次写入了):整体流程例如一条语句:

update teacher set name='彭于晏' where id=1;

mysql_update

  1. 先查询到这条数据,如果有缓存,也会用到缓存。
  2. 把 name 改成盆鱼宴,然后调用引擎的 API 接口,写入这一行数据到内存,同时记录 redo log。这时 redo log 进入 prepare 状态,然后告诉执行器,执行完成了,可以随时提交。
  3. 执行器收到通知后记录 binlog,然后调用存储引擎接口,设置 redo log为 commit 状态。
  4. 更新完成。

总结一下这张图片的重点:

  1. 先记录到内存(buffer pool),再写日志文件。
  2. 记录 redo log 分为两个阶段(prepare 和 commit)。
  3. 存储引擎和 server 分别记录不同的日志。
  4. 先记录redo,再记录 binlog。