自学内容网 自学内容网
自学内容网 > 正文

【数据库】MySQL架构探秘——select语句与update语句的执行流程

🕗 发布于 2025-01-22 10:01 数据库  mysql  架构  

目录

1.select语句的执行流程

1.1.连接层

1.2.缓存层

1.3.解析层

1.4.优化器

1.5.执行器与存储引擎

1.6.MySQL架构总结

2.update语句的执行流程

2.1.buffer pool缓冲区

2.2.redo log日志

2.3.undo log日志

2.4.bin log日志

2.5.总结update语句执行流程

1.select语句的执行流程

1.1.连接层

当MySQL服务器启动（MySQL服务器就是一个进程），等待客户端连接，对于每一个客户的连接请求，服务器都会创建一个新的新的线程处理（如果是线程池的话，则是分配一个空的线程），每个线程都是独立的，拥有各自的内存处理空间

使用客户端访问MySQL数据，第一步是连接上MySQL的服务器

  • 协议问题

连接所使用的协议大多数是TCP协议，也可以使用其他的协议，例如：Unix的Socket

  • 连接问题

连接可以是长连接也可以是短连接，短连接的特点是连接后立即关闭，比较消耗资源。长连接使用后不会立即释放，可以给其他的客户端继续使用，所以MySQL大多数都是使用的长连接

  • 同步问题

MySQL支持同步和异步的方式，大多数使用的是同步的方式，异步会给编程带来复杂性

  • 通过命令查看MySQL服务器端连接相关的默认配置

SHOW GLOBAL STATUS LIKE 'Thread%';

连接在指定超时的时间后，会更新销毁

  • 非交互式超时时间，如JDBC程序方式连接

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

  • 交互式超时时间，如客户端工具

SHOW GLOBAL VARIABLES LIKE 'interaction_timeout';

交互式与非交互式的区别：

交互式：用户通过命令行客户端或图形界面工具直接与MySQL数据库进行交互的连接，如直接进行SQL查询

非交互式：程序与MySQL数据库之间的连接，通常用于批处理、自动化任务、定时任务

MySQL数据库默认支持的并发连接数量是151，是可以修改的

  • 显示默认的最大并发连接数

SHOW VARIABLES LIKE 'max_connections';

注意：

加了Global的命令是查看全局的参数，没有加是查看当前会话级别的参数。如果想设置为全局的，Linux系统下修改/etc/my.cnf配置文件，Windows下修改my.ini配置文件

1.2.缓存层

MySQL数据库中支持缓存，在5.7的版本中默认是关闭状态，在8.0的版本中去掉了MySQL的缓存

  • 查询MySQL缓存

SHOW VARIABLES LIKE 'query_cache%';

在MySQL的配置文件my.ini中也可以找到缓存相关的配置，默认是关闭的，即query_cache_size=0

以上信息是默认配置，MySQL的查询缓存用于缓存SELECT查询结果，在下一次接收到相同的的查询请求时，系统会直接返回缓存结果，而不执行实际查询处理。这种查询缓存能够提高查询速度，优化查询性能，前提是存在大量相同或相似的查询且表数据变化较少。若 SQL 语句有任何更改（例如，修改一个空格），之前的缓存将失效。此外，表中完成的新增或修改操作也会导致缓存失效。

如果想要使用缓存，我们要进行配置：

  • 将query_cache_size设置为具体的大小，具体大小是多少取决于查询的实际情况，但最好设置为1024的倍数

  • 增加一行query_cache_type=1。query_cache_type参数用于控制缓存的类型，注意这个值不能随便设置，必须设置为数字。说明如下：

  1. 设置为0，禁用了缓存

  2. 设置为1，将会缓存所有的结果，除非SELECT语句使用SQL_NO_CACHE禁用了查询缓存

  3. 设置为2，则只会缓存那些在SELECT语句中通过SQL_CACHE指定需要缓存的查询

1.3.解析层

解析器解析编写的sql语句，根据关键字运算符等生成对应的树形结构

词法和语法解析：解析sql语句，生成树形结构

语义解析：判断表名或者字段是否存在、使用的别名是否正确

例如：

SELECT * FROM xxx;

xxx表如果不存在，sql语句会报错

1.4.优化器

编写了一条sql语句，它是有多种执行路径的，只不过最终的执行结果是相同的。多种路径是由哪个组件来生成的，最终选择哪一个路径来执行该sql呢

MySQL架构中使用优化器来根据解析树生成多条执行路径（执行计划），最终会选择一个执行计划去执行

执行计划生成后，通过评估每条路径的成本选择其中最优执行计划进行执行

优化器还会对sql语句进行优化，例如，在进行多表查询（如JOIN）时，优化器会优化查询顺序，决定先查询哪张表、后查询哪张表。当一条sql语句可以使用多个索引时，优化器会选择成本最低的索引。此外，对于类似1=1这样的冗余条件，优化器也会自动去除，以提高执行效率

加入explain以查看优化器生成的执行计划

EXPLAIN SELECT * FROM user;

如果想查看更详细的信息，可以显示JSON的数据格式

EXPLAIN FORMAT=JSON SELECT * FROM user;

1.5.执行器与存储引擎

存储引擎在MySQL的逻辑架构中位于第三层，负责MySQL中数据的存储和提取。MySQL存储引擎有很多，不同的存储引擎保存数据和索引的方式是不同的

  • 查看所有存储引擎

SHOW ENGINES;

有的时候需要快速查询且不需要持久化，对于基于内存的操作，可以使用Memory引擎

对于历史数据，几乎没有新增和查询需求且需要数据压缩时，可以使用Archive引擎

在需要大量读写并发操作和数据一致性的场景下，可以使用InnoDB引擎

1.6.MySQL架构总结

执行SQL语句的流程

MySQL的系统架构图

2.update语句的执行流程

2.1.buffer pool缓冲区

update语句的整体执行流程和select语句是一样的

MySQL想完成数据的修改，会先从存储引擎层读取数据，把数据读取到服务层进行数据的修改，再通过存储引擎层把数据更新到数据库中

MySQL每次读取数据都会读取16384个字节的数据，默认是16KB的数据，也就是一页的数据

在InnoDB引擎中设计了buffer pool缓冲区。MySQL从磁盘中通过IO读取数据到buffer pool中，引擎从buffer pool中获取数据，然后修改，再把数据写入到buffer pool中，从而完成读写的操作。因为是基于内存的操作，所以速度是非常快的

在buffer pool中还没有同步到磁盘中的数据称为脏数据

InnoDB的脏页刷新机制：

  1. 当InnoDB中的脏页比例超过innodb_max_dirty_pages_pct_lwm的值时，InnoDB就会开始刷新脏页到磁盘

  2. 当InnoDB中的脏页比例不仅超过innodb_max_dirty_pages_pct_lwm的值，而且还超过innodb_max_dirty_pages_pct时InnoDB就会进入勤快刷新模式(agressively flush），这个模式下InnoDB会把脏页更快的刷新到磁盘

  3. 还有一种情况叫做sharp checkpoint ,当InnoDB要重用它之前的redo文件时，就会把innodb_buffer_pool中所有与这个文件有关的页面都要刷新到磁盘；这样做就有可能引起磁盘的IO风暴了，轻者影响性能，重者影响可用性

对于控制刷新机制的各个参数的说明：

  1. innodb_max_dirty_pages_pct默认值为75，也就是说当脏页比例超过75%时才会进入勤快刷新模式

  2. innodb_max_dirty_pages_pct_lwm默认值是0，0是一个特殊值，它表示不启用这个功能；由于没有启用这个功能，也就是说innodb_buffer_pool中的脏页比例会操持在75%左右

MySQL会在后台使用若干线程，负责把buffer pool中的数据刷新到磁盘中去。常用到的线程有：

master thread：主线程

IO thread：IO操作的线程

Purge thread：清理数据和日志的线程

Page Cleaner thread：刷脏的线程

查看与脏页相关的配置

SHOW VARIABLES LIKE '%dirty_page%';

查看buffer pool的大小，默认是128M

SHOW VARIABLES LIKE '%innodb_buffer_pool%';

数据存储到buffer pool中，默认是128M，如果buffer pool存满了，那么innodb引擎会使用改良的LRU算法清理数据

官方文档地址：

MySQL :: MySQL 5.7 Reference Manual :: 14.5.1 Buffer Pool

2.2.redo log日志

InnoDB引擎把数据存入到buffer pool中，如果还没来得及把数据刷新到磁盘上，这个时候数据库的服务挂掉了，那么数据就丢失。为了解决这个问题，MySQL设计了redo log日志进行备份，它是基于磁盘的方式存储的

随机磁盘IO和顺序磁盘IO的区别

使用随机磁盘IO会将数据分散到不同的扇区存储，因为它的底层是通过索引顺序来存储，索引会存储到不同的扇区。那么更新数据的时候会增加寻道的时间，写入数据会变慢。

顺序磁盘IO是按着顺序追加写入的

通过命令查看innodb_log相关的信息

SHOW VARIABLES LIKE '%innodb_log%';

日志存储的文件路径是 .\ 表示当前数据目录下，打开文件夹查看

innodb_log_files_in_group的值默认为2，所以产生2个日志文件

每个文件的大小默认是48M（可以修改），数据满了会产生覆盖的效果

站在MySQL事务的角度，redo log日志是事务持久性的保证

log buffer刷盘机制

log buffer刷盘时间间隔

每隔一秒刷盘一次，具体的刷盘策略由innodb_flush_log_at_trx_commit参数来决定

innodb_flush_log_at_trx_commit：用来控制redo log刷新到磁盘的策略

当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差

当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是每秒都会刷新且写入到磁盘中，当系统崩溃，会丢失1秒钟的数据

当设置为2的时候（默认为2），每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk

2.3.undo log日志

undo log可以称为撤销日志或者回滚日志，站在事务的角度，undo log可以保证事务的原子性

日志中记录的反向操作，例如：把username=”张三” 修改成了username=”赵四”，那么undo log中记录的是原来的值，即username=”张三” 这样数据库再发生回滚操作的时候，可以把数据恢复回来

SHOW VARIABLES LIKE '%undo%';

在本地存储的位置：

MySQL的InnoDB存储引擎架构图

2.4.bin log日志

binary log即二进制日志，属于mysql服务层的日志，它是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志，它的主要作用是主从复制和进行数据恢复

bin log不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但我们可以通过查询通用日志来查看MySQL执行过的所有语句

特点：

  • 记录DDL和DML的语句，属于逻辑日志

  • 没有固定大小限制，内容可以追加

  • Server层实现，可以被所有存储引擎使用

SHOW GLOBAL VARIABLES LIKE '%log_bin%';

bin log主从复制的原理流程图

2.5.总结update语句执行流程


原文地址：https://blog.csdn.net/2302_78914800/article/details/145289049

免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2025