MySQL架构与执行SQL流程

yan_pengfei

于 2021-05-27 09:21:11 发布

阅读量268

点赞数

分类专栏： Mysql 文章标签： MySQL MySQL架构

本文链接：https://blog.csdn.net/MineCodelife/article/details/117281044

版权

Mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

MySQL体系结构

连接

通信协议

MySQL 是支持多种通信协议的TCP/IP 协议，Unix Socket等。可以使用同步/异步的方式，支持长连接/短连接。监听默认的 3306 端口。一般来说我们连接数据库都是同步连接

MySQL 既支持短连接，也支持长连接。短连接就是操作完毕以后，马上 close 掉。长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。保持长连接会消耗内存。长时间不活动的连接，MySQL 服务器会断开。
//默认都是 28800 秒，8 小时
show global variables like 'wait_timeout'; -- 非交互式超时时间，如 JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具
查看 MySQL 当前有多少个连接
show global status like 'Thread%';
当前连接的状态https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
SHOW PROCESSLIST
最大连接数 
show variables like 'max_connections';
通信方式

MySQL使用半双工的

查询缓存
默认关闭的在 MySQL 8.0 中，查询缓存已经被移除了
show variables like 'query_cache%';

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

词法解析

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

语法解析

对 SQL 做一些语法检查生成一个数据结构叫做解析树。

预处理器

检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树

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

查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种
令查看查询的开销https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
show status like 'Last_query_cost';
优化器是怎么工作的，它生成了几种执行计划，每种执行计划的 cost 是多少，应该怎么做？

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

首先我们要启用优化器的追踪（默认是关闭的）查看完之后关闭它（改成 off）我们执行一个 SQL 语句，优化器会生成执行计划：
SHOW VARIABLES LIKE 'optimizer_trace';
set optimizer_trace='enabled=on';
执行一个 SQL 语句，优化器会生成执行计划：
select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
select * from information_schema.optimizer_trace\G
它是一个 JSON 类型的数据，主要分成三部分，准备阶段、优化阶段和执行阶段
优化器最终会把解析树变成一个查询执行计划
因为 MySQL 也有可能覆盖不到所有的执行计划不一定是最优的执行计划

我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息

存储引擎

在关系型数据库里面，数据是放在表 Table 里面的。表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型

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

查看存储引擎
show table status from `mydatabase`;
任何一个存储引擎都有一个 frm 文件，这个是表结构定义文件

存储引擎比较

查看数据库对存储引擎的支持情况https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
show engines ;
MyISAM（3 个文件）

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

特点：

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

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

InnoDB（2 个文件）

mysql 5.7 中的默认存储引擎.InnoDB 行级锁。InnoDB 将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了保持数据完整性InnoDB 还支持外键引用完整性约束。

特点：

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

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

Memory（1 个文件）

特点： 把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。

只适合做临时表。将表中的数据存储到内存中。

CSV（3 个文件）

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

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

Archive（2 个文件）

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

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

InnoDB 的内存结构和磁盘结构

Buffer Pool 主要分为 3 个部分： Buffer Pool、Change Buffer、Adaptive Hash Index，另外还有一个（redo）log buffer

Buffer Pool

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

缓存的是页面信息，包括数据页、索引页。默认大小是 128M

Change Buffer 写缓冲

如果数据库大部分索引都是非唯一索引，可以重复。并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）把 Change Buffer 记录到数据页的操作叫做 merge。写多读少的业务，调大这个值：
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
Adaptive Hash Index

InnoDB 只支持显式创建 B+Tree 索引，对于一些热点数据页， InnoDB 会自动建立自适应 Hash 索引，也就是在 B+Tree 索引基础上建立 Hash 索引，这个过程对于客户端是不可控制的，隐式的
这个开关默认是 ON： show variables like 'innodb_adaptive_hash_index';
Log Buffer（redo）

如果 Buffer Pool 里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用

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

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

问题：同样是写磁盘，为什么不直接写到 db file 里面去？为什么先写日志再写磁盘？

刷盘是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。在 Buffer Pool 里面有一块内存区域（Log Buffer）专门用来保存即将要写入日志文件的数据，默认 16M，它一样可以节省磁盘 IO

log buffer 写入磁盘的时机，由一个参数控制，默认是 1
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
redo log 特点

1、redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。

2、不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志

3、redo log 的大小是固定的，前面的内容会被覆盖

check point 是当前要覆盖的位置。如果 write pos 跟 check point 重叠，说明 redo log 已经写满，这时候需要同步 redo log 到磁盘中

磁盘结构

表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。InnoDB 的表空间分为 5 大类。

系统表空间 system tablespace

包含 InnoDB 数据字典和双写缓冲区，Change Buffer 和 UndoLogs），如果没有指定 file-per-table，也包含用户创建的表和索引数据

独占表空间 file-per-table tablespaces
默认开启
SHOW VARIABLES LIKE 'innodb_file_per_table';
通用表空间 general tablespaces

可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义

临时表空间 temporary tablespaces

存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表。对应数据目录下的 ibtmp1 文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生

undo log tablespace

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