MySQL架构

最新推荐文章于 2022-11-16 22:29:29 发布
最新推荐文章于 2022-11-16 22:29:29 发布
阅读量155 收藏
点赞数 1
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cfy1024/article/details/115335574
版权

MySQL架构

1. mysql语句的执行流程

整体流程：
在这里插入图片描述
（1）、客户端与服务端连接（mysql的端口号：3306）
可选择的通信类型：同步/异步
可选择的连接方式：长连接/短连接
可选择的协议：TCP/Unix Socket

tips：一些小技巧
（1）、服务端查看连接了多少客户端
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’; // 交互式的超时时间，如navicat应用程序
在这里插入图片描述
在这里插入图片描述
服务端：默认允许的连接数，是151，最大是10000
（2）、尝试命中缓存
mysql是有缓存机制的，但默认是关闭的，且mysql是不推荐使用的。缓存比较鸡肋。
在这里插入图片描述
（3）、解析器（parser）
词法解析：把一个完整的sql语句打碎成一个个的单词
语法解析：语法检查
得到一个解析树
（4）、预处理器（pre processor）
语义解析，检查表是否存在等
（5）、优化器（query optimizer）
生成不同的执行路径，比如选择索引、表关联选择基准表、语法优化等
子查询优化、条件化简等等
得到执行计划
explain select * from table;
explain format=json select * from table;

使用步骤：
set optimizer_trace = ‘enabled=on’;
select * from information_schema.optimizer_trace;
在这里插入图片描述
trace如下：

{
  "steps": [
    {
      "creating_tmp_table": {
        "tmp_table_info": {
          "row_length": 308,
          "key_length": 0,
          "unique_constraint": false,
          "location": "memory (heap)",
          "row_limit_estimate": 54471
        }
      }
    },
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `information_schema`.`PROFILING`.`STATE` AS `状态`,round(sum(`information_schema`.`PROFILING`.`DURATION`),7) AS `期间`,concat(round(((sum(`information_schema`.`PROFILING`.`DURATION`) / 0.001131) * 100),3),'%') AS `百分比` from `INFORMATION_SCHEMA`.`PROFILING` where (`information_schema`.`PROFILING`.`QUERY_ID` = 3) group by `information_schema`.`PROFILING`.`STATE` order by `information_schema`.`PROFILING`.`SEQ`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`information_schema`.`PROFILING`.`QUERY_ID` = 3)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(3, `information_schema`.`PROFILING`.`QUERY_ID`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(3, `information_schema`.`PROFILING`.`QUERY_ID`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(3, `information_schema`.`PROFILING`.`QUERY_ID`)"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`INFORMATION_SCHEMA`.`PROFILING`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`INFORMATION_SCHEMA`.`PROFILING`",
                "table_scan": {
                  "rows": 2,
                  "cost": 10
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`INFORMATION_SCHEMA`.`PROFILING`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 10.5,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 10.5,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`information_schema`.`PROFILING`.`QUERY_ID` = 3)",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`INFORMATION_SCHEMA`.`PROFILING`",
                  "attached": "(`information_schema`.`PROFILING`.`QUERY_ID` = 3)"
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`information_schema`.`PROFILING`.`SEQ`",
              "items": [
                {
                  "item": "`information_schema`.`PROFILING`.`SEQ`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`information_schema`.`PROFILING`.`SEQ`"
            }
          },
          {
            "clause_processing": {
              "clause": "GROUP BY",
              "original_clause": "`information_schema`.`PROFILING`.`STATE`",
              "items": [
                {
                  "item": "`information_schema`.`PROFILING`.`STATE`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`information_schema`.`PROFILING`.`STATE`"
            }
          },
          {
            "refine_plan": [
              {
                "table": "`INFORMATION_SCHEMA`.`PROFILING`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 126,
                "key_length": 92,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 133152
              }
            }
          },
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "intermediate_tmp_table",
                "field": "SEQ"
              }
            ],
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "rows": 6,
              "examined_rows": 6,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 320,
              "sort_mode": "<sort_key, rowid>"
            }
          }
        ]
      }
    }
  ]
}

steps:
sql准备阶段 join_preparation、
sql优化阶段 join_optimization、
sql执行阶段 join_execution
（6）、执行器 executor
执行器调用存储引擎 storage engine
表类型，即存储引擎
innoDB
memory
myisam
表类型可以修改。-- 规范都是一样的，所以可以换的。（自己也可以根据规范创建索引）
当然，也可以通过c语言按照规范自己写索引。
在这里插入图片描述
更新（更新/插入/删除）语句如何执行？

（1）、预读取：局部性原理（默认认为取得这个部分周围的部分也是马上要用的）
page的默认值：16kb
（2）、redo log（innoDB独有）
作用：保证了内存数据的安全性，延迟刷盘时机，提升系统的吞吐量
1、为innoDB提供了崩溃恢复的可能性，实现持久化
2、redo log 记录的是在某个数据页上做了什么样的修改，属于物理日志
3、redo log 的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发buffer pool到磁盘的同步，以便腾出空间记录后面的修改
（3）、undo log
记录事务发生之前的数据状态，发生异常回滚，保证原子
（4）、具体步骤
事务开始，从内存/磁盘里取到包含这条数据的数据页，并返回给执行器。
执行器修改数据页的这一行数据。
将旧的数据记录到undo log
将新的数据记录到redo log
调用存储引擎接口，记录数据页到内存
事务提交
在这里插入图片描述

2.InnoDB存储引擎的磁盘和内存结构

在这里插入图片描述
（1）、内存空间解析

内存淘汰机制：LRU - 冷热比例
在这里插入图片描述
新数据先放入到冷数据区，然后被访问了才放入到热数据区。

Change Buffer ：存放非唯一索引的一些数据（update insert delete）。

log buffer 刷盘时机，通过参数控制刷盘频率，默认是1
0 - 》 每秒刷一次
1 - 》只要事务提交，立即刷
2 -》 只要有事务提交，先写到操作系统的缓冲区，操作系统每秒刷一次到磁盘

（2）、磁盘空间解析
System Tablespace 系统表空间
DoubleWrite Buffer 双写缓冲区 防止页部分写失败
File-Per-Table Tablespace 独占表空间，每个表有自己的空间
（3）、mysql - binlog
binlog：所有的存储引擎都可以使用
binlog以事件的形式记录了所有的DDL和DML语句，可以用来做主从复制和数据恢复。

update语句的执行过程
在这里插入图片描述
在崩溃时，恢复的原则如下：
1、binlog无记录，redolog无记录 --》回滚事务
2、binlog无记录，redolog状态为prepare --》回滚事务
3、binlog有记录，redolog状态为prepare - 》提交事务
3、binlog有记录，redolog状态为commit - 》提交事务

1024个秘密
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
MySql 使用SHOW PROFILE 进行SQL 分析
耐得住寂寞才能擒得住繁华.
11-24 831
什么是SHOW PROFILE：     SHOW PROFILE 是MySql 提供可以用来分析当前会话语句执行的资源消耗情况，可以用于SQL 的调优测评依据。在默认的情况下，参数处于关闭状态，并保存着最近15 次的运行结果。我们可以通过 SHOW VARIABLES LIKE ‘PROFILING’; 命令查看参数的开关情况；通过 SET PROFILING = ON; 命令设置开启状态。
mysql dba常用命令_MySql之DBA常用命令集锦
weixin_39618824的博客
01-19 157
Mysql之DBA常用命令集锦show status like 'Threads_connected';show status like 'Threads_running';show full processlist;show status like 'table%';show variables like '%max_connections%';show variables like 'log_...
mysql主从架构分析以及读写分离实践
无名程序员
02-07 260
mysql主从架构分析以及读写分离实践 为什么要使用主从同步 实时灾备，用于故障切换 读写负载均衡 各种独占数据库操作专用 mysql具体内部实现主从原理 master节点主要通过bin-log与slave节点进行交互，每当写master节点，在bin-log中就会记录上相应的操作，bin-log不记录查询操作。slave节点通过复制master的bin-log，解析成...
mysql性能分析
huihui870311的专栏
08-24 271
1、show PROFILES;
MySQL 性能监控
Andy的博客
11-16 319
MySQL调优 性能监控
MySQL架构方案
03-02
slave端的IO线程负责从master读取日志，SQL线程专门负责在slave端应用从master读过来的日志（早期MySQL用一个线程实现，性能问题比较明显）。使用replication必须启用binarylog，MySQL用binarylog向slave分发更新...
MySQL官方文档架构图
09-29
MySQL官方文档架构图
mysql架构1
08-08
mysql架构1
MySQL架构及优化原理详解
02-24
MySQL逻辑架构整体分为三层:1>客户端:并非MySQL所独有，诸如:连接处理、授权认证、安全等功能均在这一层处理2>核心服务:包括查询解析、分析、优化、缓存、内置函数(比如:时间、数学、加密等函数)，所有的跨存储引擎...
mysql数据库日志
程序三两行
11-16 1603
mysql日志在mysql事务章有事务日志相关的记录。初次之外，MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志 、 错误日志 通用查询日志和 查询日志 ，这也是常用的4种。MySQL 8又新增两种支持的日志： 中继日志 和 数据定义语句日志 。使用这些日志文件，可以查看MySQL内部发生的事情
navicat11连接mysql5.7，执行create时报1055错误的解决
quakegao的专栏
01-03 766
环境： MySQL 5.7，CentOS 7.8-2003 一、问题 使用navicat11.1连接MySQL 5.7.x数据库，执行update语句时报了一个错误 [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally d.
MySQL数据库优化(一)
李双喆
03-19 2272
1. 数据库优化的目的      1）数据库慢速查询，解决界面内容加载不出来；    2）增加数据库的稳定性； 2. SQL工具 2.1 慢查日志     慢查日志呢就是MySQL日志查询比较慢的SQL语句的日志。详细的记录了所有的超过某一时间的SQL语句。    1）慢查日志的开启     首先查看慢查日志是否开启：show VARIABLES like '%slow%'，
mysql高性能学习笔记03_【MySQL】《高性能MySQL》 学习笔记,第三章，服务器性能剖析...
weixin_36204513的博客
01-19 184
第三章：服务器性能剖析​ 本章将针对如下三个问题进行解答：​ 如何确认服务器是否达到了性能最佳的状态​ 找出某条语句为什么执行不够快​ 诊断被用户描述成“停顿”，“堆积”，“卡死”的某些间歇性疑难故障1.性能优化简介：​ 针对性能问题，1000个DBA，有1000个回答。诸如，“QPS”,"CPU Load"，“可扩展性”之类。原则一：我们将性能...
MySQL性能分析工具之PROFILE
cuilun4325的博客
03-07 141
分析SQL执行带来的开销是优化SQL的常用手段，在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。 它只能在session级别来设置，设置后影响当前session；当它开启后，后续执行的SQL语句都将记...
mysql 执行sql文件_一文看完mysql数据库SQL执行分析，优化前必备分析
weixin_39775976的博客
11-30 133
概述一般我们在对mysql数据库做优化，肯定需要对慢sql去做分析才能开始优化，那么有什么分析的方法呢？下面通过对sql执行时间和执行情况来做分析。一、SQL 执行时间分析通过找到执行时间长的 SQL 语句，可以直观的发现数据层的效率问题。1.通过 show processlist 来查看系统的执行情况mysql> show processlist;2.通过 profiling 来进行查看这...
使用mysql profiling功能剖析单条查询
weixin_30457881的博客
01-25 177
5.1版本开始引入show profile剖析单条语句功能，支持show profiles和show profile语句，参数have_profiling;控制是否开启： 查看是否支持这个功能（查询为yes表示支持）： mysql > show variables like 'have_profiling';+----------------+-------+| Variable_nam...
mycat mysql架构
最新发布
08-24
在 MyCAT 架构中，前端 MyCAT 节点接收客户端的请求，并将请求转发给后端 MySQL 节点进行查询或更新操作。它具有路由、负载均衡和事务管理等功能。前端节点可以配置多个，以实现高可用和水平扩展。 后端 MySQL 节点...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值