【MySQL】逻辑架构与存储引擎

于 2024-07-07 16:09:02 发布
阅读量1.1k 收藏 30
点赞数 26
分类专栏： Mysql 文章标签： mysql 架构 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/decade0712/article/details/140230520
版权

一、逻辑架构

1、MySQL逻辑架构

在这里插入图片描述
我们可以根据上图来对sql的执行过程进行分析

  • 第一步：客户端与服务器建立一个连接，从连接池中分配一个线程处理SQL语句
  • 第二步：SQL接口接受SQL指令
  • 第三步：如果是5.7版本，就会先去缓存中检查是否已经有查询结果存在，如果存在就返回此结果给客户端。如果是8.0版本，就会使用解析器，将SQL语句解析成语法树，如果SQL语法有问题，就会在此报错
  • 第四步：SQL会使用优化器生成执行计划，例如决定索引的使用，表之间的连接顺序等
  • 第五步：调用存储引擎，去内存中查询数据（由文件系统加载到内存中）
  • 第六步：查询到结果后，如果是5.7版本，会将结果写到缓存中，再走到SQL接口，释放占用的工作线程，将结果返回给客户端（如果是8.0，就跳过写入缓存的步骤）

2、MySQL服务架构

  • 第一层：连接层
    • 对客户端的请求进行身份认证
    • 从权限表中查询当前客户端的权限信息
    • 提供控制客户端连接数量的连接池以及处理SQL请求的线程池
  • 第二层：服务层，包括上面介绍的SQL接口、解析器、优化器和缓存
  • 第三层：引擎层，真正的负责了MySQL中数据的存储和提取，对物理服务器维护的底层数据进行操作

3、查看SQL执行耗时

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql>
mysql>
mysql> SET @@session.profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

首先，我们要打开profiling这个变量。便于我们查看SQL执行的耗时
show profiles：查看所有的查询语句的信息
show profile;：查看最近一次查询语句的执行耗时
show profile for query id编号：查看指定id的查询语句执行耗时

  • MySQL8.0，可以发现同样的查询语句执行流程是一样的
mysql> select * from test1;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 十年   |
+------+--------+
1 row in set (0.01 sec)

mysql>
mysql> select * from test1;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 十年   |
+------+--------+
1 row in set (0.00 sec)

mysql>
mysql> show profiles;
+----------+------------+---------------------+
| Query_ID | Duration   | Query               |
+----------+------------+---------------------+
|        1 | 0.00018475 | select @@profiling  |
|        2 | 0.00150575 | SELECT DATABASE()   |
|        3 | 0.00339475 | show databases      |
|        4 | 0.00165200 | show tables         |
|        5 | 0.00973450 | select * from test1 |
|        6 | 0.00024725 | select * from test1 |
+----------+------------+---------------------+
6 rows in set, 1 warning (0.00 sec)

mysql>
mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000108 |
| Executing hook on transaction  | 0.000003 |
| starting                       | 0.000006 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000027 |
| init                           | 0.000004 |
| System lock                    | 0.000006 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000009 |
| preparing                      | 0.000014 |
| executing                      | 0.000033 |
| end                            | 0.000002 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000007 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000007 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.01 sec)

mysql>
mysql> show profile for query 5;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000075 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000009 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000037 |
| init                           | 0.000006 |
| System lock                    | 0.000010 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000014 |
| preparing                      | 0.000018 |
| executing                      | 0.009485 |
| end                            | 0.000014 |
| query end                      | 0.000004 |
| waiting for handler commit     | 0.000010 |
| closing tables                 | 0.000010 |
| freeing items                  | 0.000016 |
| cleaning up                    | 0.000009 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

mysql>
mysql> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000108 |
| Executing hook on transaction  | 0.000003 |
| starting                       | 0.000006 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000027 |
| init                           | 0.000004 |
| System lock                    | 0.000006 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000009 |
| preparing                      | 0.000014 |
| executing                      | 0.000033 |
| end                            | 0.000002 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000007 |
| closing tables                 | 0.000006 |
| freeing items                  | 0.000007 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

mysql>
  • MySQL5.7

因为这个版本存在缓存，所以我们开启一下看看同样的SQL，两次执行会有什么区别
我们需要打开缓存配置，将其设置为query_cache_type=2，按需开启，重启MySQL服务

[root@myLinux1 ~]# vim /etc/my.cnf
[root@myLinux1 ~]# systemctl restart mysqld
[root@myLinux1 ~]#

重复执行2次使用缓存的查询，可以看出，使用缓存的情况下，如果SQL语句一样并且缓存中已有结果，就不会继续往下执行了

mysql> select SQL_CACHE * from test1 WHERE id = 1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | decade |
+------+--------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> select SQL_CACHE * from test1 WHERE id = 1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | decade |
+------+--------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.00017600 | SELECT DATABASE()                          |
|        2 | 0.00025400 | show databases                             |
|        3 | 0.00007900 | show tables                                |
|        4 | 0.00016950 | select * from test1                        |
|        5 | 0.00022650 | select * from test1                        |
|        6 | 0.00320425 | select SQL_CACHE * from test1 WHERE id = 1 |
|        7 | 0.00004550 | select SQL_CACHE * from test1 WHERE id = 1 |
+----------+------------+--------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

mysql>
mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000018 |
| Waiting for query cache lock   | 0.000002 |
| starting                       | 0.000001 |
| checking query cache for query | 0.000006 |
| checking privileges on cached  | 0.000002 |
| checking permissions           | 0.000008 |
| sending cached result to clien | 0.000006 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

mysql>
mysql>
mysql>
mysql> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| Waiting for query cache lock   | 0.000003 |
| starting                       | 0.000002 |
| checking query cache for query | 0.002889 |
| checking permissions           | 0.000014 |
| Opening tables                 | 0.000017 |
| init                           | 0.000020 |
| System lock                    | 0.000007 |
| Waiting for query cache lock   | 0.000002 |
| System lock                    | 0.000014 |
| optimizing                     | 0.000088 |
| statistics                     | 0.000014 |
| preparing                      | 0.000010 |
| executing                      | 0.000001 |
| Sending data                   | 0.000030 |
| end                            | 0.000002 |
| query end                      | 0.000005 |
| closing tables                 | 0.000004 |
| freeing items                  | 0.000031 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000012 |
| Waiting for query cache lock   | 0.000001 |
| freeing items                  | 0.000001 |
| storing result in query cache  | 0.000002 |
| cleaning up                    | 0.000010 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)

mysql>
mysql>
mysql> select SQL_NO_CACHE * from test1 WHERE id = 2;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 十年   |
+------+--------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000078 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000020 |
| init                 | 0.000023 |
| System lock          | 0.000007 |
| optimizing           | 0.000009 |
| statistics           | 0.000015 |
| preparing            | 0.000011 |
| executing            | 0.000002 |
| Sending data         | 0.000041 |
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000006 |
| freeing items        | 0.000013 |
| cleaning up          | 0.000014 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

mysql>

4、数据库缓冲池

  • 因为磁盘I/O会消耗很多时间，所以DBMS会申请使用内存作为数据缓冲池（数据库缓冲池和查询缓存不是一回事），减少直接与磁盘进行I/O
  • InnoDB是以页为单位来管理存储空间的，我们进行的增删查改操作本质上都是在访问内存中一页一页的数据
  • 当执行更新操作时，会先刷新缓冲池中的数据，然后再按照一定的频率同步到磁盘的文件系统
  • 那如果同步到一半断电了怎么办？那就要用到下面两个文件
    • Redo.log—记录要重新同步的动作
    • Undo.log—记录要回滚的动作

在多线程情况下，可能要申请多个buffer pool，通过改变变量innodb_buffer_pool_instances为每个线程去申请独立的内存空间，避免相互影响

mysql> show variables like '%innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set (0.00 sec)

mysql>

查看缓冲池大小innodb_buffer_pool_size
当buffer pool实例数量发生变化时，每个实例分配到的缓冲池大小是此变量/instance数量

mysql> show variables like '%innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql>

二、存储引擎

1、存储引擎的查看

1）如下所示，我们可以查看当前系统默认存储引擎和系统支持哪些存储引擎
Transactions：是否支持事务
XA：是否支持分布式事务
Savepoints：保存点，回滚时使用

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

mysql>
mysql>
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

mysql>

2）在建表时，如果没有显式指明存储引擎，那么就会使用系统默认的存储引擎

CREATE TABLE table_name(
 id INT,
 name VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、存储引擎的差别

1）InnoDB

  • 优势：
    • 支持外键
    • InnoDB是MySQL的默认事务型引擎，它被设计用于处理大量的短期事务，可以保证事务的完整提交和回滚
    • 如果业务涉及大量更新和删除操作，推荐使用InnoDB
    • InnoDB是行级锁，操作时只锁住某一行的数据，不影响其他行，适合高并发的场景。而MyISAM是表级锁，即使操作一条记录也会锁住整个表，不适合高并发
  • 劣势：
    • 对比MyISAM，InnoDB在写数据方面的效率差一些，并且InnoDB会占用更多的磁盘空间保存数据和索引
    • MyISAM索引和数据是独立开的，只缓存索引。InnoDB因为索引和数据都在一个文件中，所以不仅缓存索引还缓存真实数据，对内存要求较高，内存大小对性能会有很大影响

2）MyISAM

  • 优势：访问速度快，对事务没有安全要求且主要以SELECT、INSERT为主的应用比较适合
  • 劣势：不支持外键、事务、行级锁，所以崩溃后无法安全恢复

两种存储引擎的数据结构可以参考博主之前写的【MySQL】Linux下MySQL的目录结构、用户、权限与角色，此博客第一节介绍了两种存储引擎的数据结构

如有错误，欢迎指正！！！

MySql逻辑架构
shengyin714959的博客
09-15 636
比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。>延迟更新索引键：在创建表时，如果指定了DELAY_KEY_WRITE，在每次修改执行完成时，不会立刻将修改的数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。不同引擎只会简单的响应上层服务器的请求，而不会相互通信。
MySQL逻辑架构
weixin_55705029的博客
04-17 1603
逻辑架构
MySql-逻辑架构
wxd_1024的博客
12-07 3147
和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。 逻辑架构分层 1.连接层 最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类...
MySQL 逻辑架构
gaoyunshan163的博客
04-29 131
下面就是MySQL的逻辑架构，sql layer主要负责如下功能：权限判断、sql解析、执行计划优化、query cache的处理等操作，这些操作都是在数据库系统处理底层数据之前的工作； Storage Engine Layer主要负责底层数据存取的实现，由多种存储引擎共同组成。 SQL Layer 中包含了多个子模块，下面我将逐个做一下简单的介绍： 结构图如下： 1、初始化模块 顾名思议，初始化模块就是在mysqlServer 启动的时候，对整个系统做各种各样的初始化操作，比如...
MySQL高级之逻辑架构
weixin_44446626的博客
05-03 175
MySQL的逻辑架构、执行过程、缓冲池一、概览逻辑架构附加 Connectors1. 连接层1.1 过程1.2 总结2. 服务层2.1 SQL Interface: SQL接口2.2 Parser: 解析器2.3 Optimizer: 查询优化器2.4 Caches & Buffers： 查询缓存组件（废弃）3. 引擎层附加 存储层二、 MySQL执行流程1. SQL的执行流程2. 总结三、数据库缓冲池1. 引言2. 缓冲池的数据读取流程 一、概览逻辑架构 附加 Connectors 利用外部语
mysql的逻辑架构
eli的bolg
11-27 504
1. 客户端发出请求,首先与服务器上的MYSQL进行链接。 2. MYSQL会判断客户端发来的请求语句是否已存在缓存，当缓存存在且没过期，直接返回缓存数据。 3. 当缓存不存在时,MYSQL利用解析器将客户端请求进行解析、分析所有的内置函数并进行缓存。存储过程、视图、触发器都在这一步进行处理。 4. 将3中的执行结果交给优化器，优化器在优化后交给对应的存储引擎进行处理。  5. 将存储引擎
mysql架构与存储引擎（MySQL逻辑架构、InnoDB引擎、MyISAM引擎、存储引擎选择）.docx
最新发布
07-24
#### 一、MySQL逻辑架构 MySQL作为一款广泛使用的开源关系型数据库管理系统，其内部架构设计非常精妙，旨在提供高效、可靠的数据存储与管理能力。 ##### 1、逻辑架构图 MySQL采用了典型的客户端/服务器(C/S)架构...
MySQL逻辑架构-存储引擎-用户权限密码详解
12-25
MySQL作为广泛使用的开源...以上内容总结了MySQL的逻辑架构、存储引擎、并发控制、配置文件、数据库文件、用户权限密码管理以及客户端工具等方面的知识点。理解这些知识点对于掌握MySQL数据库的管理和操作至关重要。
03-MySQL逻辑架构和Innodb存储引擎1
08-03
【MySQL逻辑架构】 MySQL的逻辑架构分为多个组件，这些组件协同工作以处理用户请求并返回结果。以下是主要组件的简要介绍： 1. **连接池组件（Connection Pool）**：负责管理用户连接，缓存线程，以提高系统处理...
Mysql逻辑结构
lcl_yuxiang的专栏
05-29 973
灵活性：适应高要求环境如web应用，嵌入式应用、数据仓库、内容索引和分发软件等。 众多硬件平台上良好的配置和运行，支持多种数据类型不支持对象类型。 特  征：存储引擎架构，这种架构将查询处理和各类服务器任务与数据存储/提取分离。 结构图： 一层：基于网路的客户端、服务器工具或者服务器都需要的服务，比如连接处理、授权认证、安全等。 二层：核心，查询解析、分析、优化、缓存以及
MYSQL 逻辑架构
qq_17271573的博客
08-06 471
思维导图      前言   》 Mysql并非尽善尽美，但足够灵活，能适应高要求环境，如Web应用。 》 Mysql在众多平台上运行良好，支持多种数据类型，但不支持对象类型（Mongodb支持） 》 Mysql的存储引擎可以基于表建立，以满足对数据存储，性能，特征及其他特性的各种需要。    架构逻辑视图   每个虚线框为一层
Mysql逻辑架构
xiaowanziddd的博客
07-09 870
一、逻辑架构剖析 Mysqi是典型的C/S架构，服务端程序使用的mysqld. 不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一点文本（sql语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。 mysql简化为三层结构： 1.连接层：客户端和服务器建立连接，客户端发送SQL至服务器端； 2.SQL(服务层)：对DQL语句进行查询处理；与数据库文件的存储方式无关 3.存储引擎层：与数据库文件打交道，负责数据的存储和读取。 二、SQL执行流程SQ
Mysql 逻辑架构
Janz_Zhu的博客
03-07 476
       MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。       MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。       最下层为存储引擎，其负责MySQL中的数据存储和提取。和...
MySQL（三）架构
dlx29376的博客
07-31 166
和其他数据库相比，MySQL有点与众不同，他的架构可以在多种不同场景中应用并发挥好的作用，但同时也会带来一点选择上的困难。MySQL并不完美，却足够灵活，能够适应高要求的环境。为了充分发挥MySQL的性能并顺利地使用，就必须理解其设计。MySQL的灵活性体现在很多方面。例如，你可以通过配置使它在不同的硬件上都运行得很好，也可以支持多种不同的数据类型。但是，MySQL中重要、最与众不同的特...
MySQL深度解析：逻辑架构与存储引擎
"本文详细介绍了MySQL的逻辑架构和存储引擎，包括连接层、服务层的功能以及各种存储引擎的特点，如MyISAM和...通过对MySQL逻辑架构和存储引擎的深入理解，开发者可以更好地优化数据库性能，提升应用程序的整体效率。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值