从sql的执行来分析Mysql架构

Armin
515 阅读14分钟

一条查询sql是如何执行的

首先有个user表，根据id查询这条语句

select * from user_info where id = 1;

执行的过程可分为以下几个步骤

  1. 客户端需要和server端建立通信，让sql语句经过连接器，并通过验证
  2. 查询缓存是否有这条语句，如果有的话直接返回，没有的话就进入解析器
  3. 在分析器中对sql语句进行词法，语法的解析校验
  4. 通过分析器之后，优化器还会对你的sql语句进行优化，让你的sql跑的更快
  5. 最后，进入到执行器，首先会判断你是否有权限，没有直接返回没权限的错误，有的话将会使用存储引擎的接口。等到之后如果有设置缓存，需要将结果返回到缓存中。

连接器

在查询slq语句之前，首先肯定是需要建立连接的，这个就是有连接器来完成的。连接器负责和客户端建立连接，获取权限，维持和管理连接的。

通讯协议

  • 通讯类型：有两种，分别是同步和异步。一般来说客户端与数据库server的连接都是同步的
  • 连接方式：长连接和短连接。MySQL既支持短连接，也支持长连接。一般来说都是长连接，而且会把这个连接放到客户端的连接池，可用一下命令查看有多少连接，另外保持长连接会消耗内存，长时间不活动的连接，Mysql服务器会断开
show global status like 'Thread%'
字段含义
Threads_cached缓存中的线程连接数
Threads_connected当前打开的连接数。
Threads_created为处理连接创建的线程数。
Threads_running非睡眠状态的连接数，通常指并发连接数。
show global variables like 'wait_timeout'; -- 非交互式超时时间，如 JDBC 程序
show global variables like 'interactive_timeout'; -- 交互式超时时间，如数据库工具

默认都是 28800 秒，8 小时。

MySQL 服务允许的最大连接数默认是 151 个，最大可以设置成 100000。

show variables like 'max_connections';

通信方式

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

查询缓存

Mysql内部自带一个缓存模块，现在我们在没有索引的字段执行两次同样的查询（表中有100万数据）

select * from user_innodb where name='test';

执行时间都是0.334s，这是为什么呢？Mysql默认是关闭缓存的。可通过下面语句查询

show variables like 'query_cache%';

缓存按理说能提供作用是很大的，那么为什么mysql默认关闭它呢？有两点原因

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

所以在mysql8.0中，查询缓存已经被移除了

分析器

假如随便输入一个字符串“agsd“，mysql怎么认为它是错的呢？分析器就是对这个sql语句进行解析的

词法解析

词法解析就是将一条完整的sql语句打碎成一个个的单词
比如一个简单的sql语句

select name from user where id = 1;

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

语法解析

第二步就是语法解析，语法解析是对sql进行语法检查，比如单引号有没有闭合，然后根据mysql的语法规则生成一个数据结构。
这个数据结构我们称之为解析树

预处理

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

优化器

一个sql语句有多种执行方式，最终返回相同的结果，他们是等价的。这么多中执行方式，选择哪一个呢？这就是优化器的作用。
优化器根据解析树生成不通的执行计划，然后选择一种最优的执行计划。可以使用这个命令查看查询开销：

show status like 'Last_query_cost';

那么优化器可以处理哪些优化类型呢？举几个例子

  1. 当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表（先访问哪张表）。
  2. 有多个索引可以使用的时候，选择哪个索引。
  3. 对于查询条件的优化，比如移除 1=1之类的恒等式，移除不必要的括号，表 达式的计算，子查询和连接查询的优化。

最终，优化器会把解析树编程一个执行计划，执行计划也是一种数据结构，我们可以在slq语句面前加上explain来查看对应的执行计划。

EXPLAIN select name from user where id=1;

执行器

首先判断你对这个表有没有执行查询的权限，如果没有，就返回没有权限的错误。如果命中查询缓存，会在查询缓存返回结果的时候做权限验证，查询也会在优化器之前调用precheck验证权限
执行流程：

  1. 调用引擎接口获取这个表的第一行，判断id是不是为1，如果不是则跳过，如果是则将结果存入结果集中
  2. 调用引擎接口获取下一行，逻辑同上，直到最后一行
  3. 执行器将上述遍历的结果中满足条件的行返回给客户端

如果是有索引的表，第一次调用的是取满足条件的第一行这个接口，之后循环取满足条件的下一行这个接口

存储引擎

介绍

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

查看存储引擎

查看数据库里面已经存在的表的存储引擎：

show table status from `mydb`;

查看数据库支持的存储引擎

show engines ;

任何一个存储引擎都有一个 frm 文件，这个是表结构定义文件

user_innodb.frm
user_innodb.ibd
user_memory.frm
user_myisam.frm
user_myisam.MYD
user_myisam.MYI

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

存储引擎的比较

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

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

  3. Memory （1个文件）
    数据存放在RAM中。
    特点：
    数据存放在内存中，读写速度快，但是数据库重启或者崩溃，数据会丢失。只适合做临时表
    将表中的数据存储到内存中。
    默认使用哈希索引。

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

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

MySQL 体系结构总结

模块详解

  1. Connector：用来支持各种语言和 SQL 的交互，比如PHP，Python，Java 的JDBC；
  2. Management Serveices &Utilities：系统管理和控制工具，包括备份恢复、MySQL 复制、集群等等；
  3. ConnectionPool：连接池，管理需要缓冲的资源，包括用户密码权限线程等等；
  4. SQL Interface：用来接收用户的 SQL命令，返回用户需要的查询结果
  5. Parser：用来解析 SQL 语句；
  6. Optimizer：查询优化器；
  7. Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key 缓存，权限缓存等等；
  8. Pluggable Storage Engines：插件式存储引擎，它提供 API 给服务层使用，跟具体的文件打交道。

架构分层

服务层

包括客户端跟服务端的连接，查询缓存的判断、对 SQL 语句进行词法和语法的解析 （比如关键字怎么识别，别名怎么识别，语法有没有错误等等）。
然后就是优化器，MySQL 底层会根据一定的规则对我们的 SQL 语句进行优化，最 后再交给执行器去执行。

存储引擎

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

一条更新sql如何执行的？

上面我们已经分析查询sql的流程，那么更新流程会和他有什么区别呢？其实它的流程和查询的基本一致，也要经过解析器、优化器最后再交给执行器
区别在于拿到符合数据的之后的操作

缓冲池 Buffer Pool

对于InnoDB的存储引擎来说，数据主要是放在磁盘上的，存储引擎要操作数据，首先先把磁盘的数据加载到内存才能操作。
这当中就会有个问题，不是我们需要多少数据，就从磁盘加载多少数据出来呢？磁盘读取数据到内存是很慢的。所以我们需要的数据分散到不同的地方，意味着很多次的I/O操作。
所以，就有个预读取的概念。也就是 说，当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到 InnoDB设定存储引擎从磁盘读取数据最小单位为页，一个页为16KB，操作系统也有页的概念不过是4KB。
好了东扯扯西扯扯，怎么还不说Buffer Pool?不急不急，这就来了，想下，每次读取页的操作都直接操作磁盘，会不会很慢呢？这就用到了缓冲池（Buffer Pool).也就是把磁盘读到的页放到一块内存区域里面。 下一次读取相同的页，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。

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

redo log

因为刷脏不是实时的，如果Buffer Pool里面的脏页没有刷入磁盘的时候，数据宕机的或者重启的话，这些数据就会丢失。所以内存数据需要有个持久化措施。
InnoDB把所有对页的修改操作专门写入一个日志文件（redo log）,当数据库重启时，有未同步到磁盘的数据会从这个日志文件进行恢复（crash-safe）。我们说的事务的ACID里面的D（Duration 持久性），就是用它实现的。

对应于/var/lib/mysql/目录 下的 ib_logfile0 和 ib_logfile1，默认 2 个文件，每个 48M。 可以使用以下命令查看

show variables like 'innodb_log%';
参数含义
innodb_log_file_size指定每个文件的大小，默认 48M
innodb_log_files_in_group指定文件的数量，默认为 2
innodb_log_group_home_dir指定文件所在路径，相对或绝对。如果不指定，则为datadir 路径
特点：
  1. redo log 是 InnoDB存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复 是 InnoDB 的一个特性。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。
  3. redo log 的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发 buffer pool到磁盘的同步，以便腾出空间记录后面的修改。

另外除了redo log还有个与修改有关的日志，叫undo log。

undo log

undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态（不包括 select）。 如果修改数据时出现异常，可以用 undo log 来实现回滚操作（保持原子性）。
undo log的数据默认在系统表空间ibdata1文件中，因为共享表空间不会自动收缩， 也可以单独创建一个 undo 表空间。

show global variables like '%undo%';
值含义
innodb_undo_directoryundo 文件的路径
innodb_undo_log_truncate设置为 1，即开启在线回收（收缩）undo log 日志文件
innodb_max_undo_log_size如果innodb_undo_log_truncate 设置为 1，超过这个大小的时候会触发 truncate 回收（收缩）动作，如果 page大小是 16KB，truncate后空间缩小到 10M。默认1073741824 字节=1G。
innodb_undo_logs回滚段的数量， 默认128，这个参数已经过时。
innodb_undo_tablespaces表空间格式，最大 95，这个参数已经过时
现在我们可以简单总结一下更新的流程。更新一个name=李四
为name = 张三
update user set name = '张三' where id=1;
  1. 事务开始，内存（buffer pool）或磁盘(data file)取到这条数据，返回给 Server的执行器；
  2. Server 的执行器修改这一行数据的值为张三；
  3. 记录 name=李四 到 undo log；
  4. 记录 name=张三 到 redo log；
  5. 调用存储引擎接口，在内存（Buffer Pool）中修改 name=张三；
  6. 事务提交.

内存和磁盘之间，工作着很多后台线程。其作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。
另外除了InnoDB架构的日志文件，mysql的server层也有给一个日志文件叫做binlog，它可以被所有的存储引擎使用

Binlog

binlog记录了所有的DDL和DML语句，可以用来做主从复制和数据恢复。
和redo log不同的是，它的文件内容是可以追加的，没有固定大小。
binlog还有一个功能使用与实现主从复制，其原理就是从服务器读取主服务器的binlog，然后执行一遍。
有了这个日志之后我们看看更新语句怎么执行，以下省略undo log.

  1. 先从内存或者磁盘拿到这条数据。
  2. 把 name 改成彭于晏，然后调用存储引擎的 API 接口，写入这一行数据到内存，同时记录 redo log。这时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，可以随时提交。
  3. 执行器收到通知后记录 binlog
  4. 然后调用存储引擎接口，设置 redo log 为 commit 状态。更新完成。
avatar
文章
阅读
粉丝