MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。

一、前言背景

二、客户端发起SQL查询

三、服务端-SQL接口

四、服务端-SQL解析器

五、服务端-SQL优化器

六、服务端-存储引擎

七、服务端-日志数据

7.1 binlog日志

7.2 redoLog重做日志

7.3 undoLog回滚日志

7.4 slowLog慢查询日志

7.5 relayLog备份中继日志

八、MySQL实用管理命令参数集


读书心得笔记:之前发生了什么不重要,重要的是你赋予过去的意义和看法。你的未来,取决于‘此时此刻’的你怎么想和如何做。目的论是一种彻底颠覆正统心理学因果论的思想,阿德勒的目的论,核心就是勇气!

一、前言背景

      家喻户晓的MySQL,不管师出何门、身居哪派,JAVA、C、C++、Python、PHP、Go、Shell开发,都要用到数据库。MySQL的免费开源深得人心,让大公司、中小企业,或者说有系统数据读写需求的地方,都得来一套MySQL存储数据。相比其他付费型关系型数据库Oracle、SQLServer、DB2,MySQL的用户群体更广泛、更接地气实用。相比同样是开源的PostgresSQL,MySQL在中小型应用上,显得更轻巧易用。

    今天,由架构原理+SQL执行过程开篇,从0到1、由浅入深循序渐进的和大家学习分享MySQL的实战经验架构技术系列。希望对不管处于跳槽求职面试、还是刚入职场的研发小白、或者想精进研究技术的同学都有所帮助启发。

   「拉丁解牛,坚持实用至上,用最简洁直白的文字+最少的代码示例分享技术干货。」

    轻松愉快无八股,有图有料有收获。正式开启《MySQL进阶突击》系列专栏快乐分享之旅。

二、客户端发起SQL查询

    我们JAVA 客户端执行以下查询用户id=1的简单查询。

select id,name,age from user where id =1;

     首先我们的客户端程序,需要内置安装对应服务端版本的驱动,以及通过数据库连接池(实际就是TCP/IP的网络连接池)向MySQL服务端发起查询请求。

比如在maven pom设置依赖驱动:

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.15</version>
</dependency>

以及配置相关数据库连接池高效复用SQL连接,比如Springboot默认的是hikari连接池:

spring:
    application:
        name: ladingman
    datasource:
        hikari:
            max-lifetime: 1800000
            connection-timeout: 30000
            idle-timeout: 600000
        password: ${DB_PASSWORD:lading999}
        url: ${DB_URL:jdbc:mysql://10.1.8.8:15007/lading?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false}
        username: ${DB_USER:lading}

     客户端有了与SQL服务器端版本对应的驱动,以及设置正确的连接池,就可以实现sql查询。数据库连接池是一个值得深入探讨的部分,不过这里先不展开,后面系列文章再详细分析。

      客户端的SQL请求过来后,服务端也是有个线程池在管理客户端的连接,线程池收到客户端的SQL查询请求后,交给【SQL接口】模块进行处理。

三、服务端-SQL接口

     SQL接口相当于我们日常开发的rest接口,是服务请求的入口和出口。所有数据库请求包括:DDL、DML、DQL、DCL、TCL各种类型SQL语句,都是先到达服务端的【SQL接口】,最后返回数据结果也是由【SQL接口】进行返回。

四、服务端-SQL解析器

      SQL接口收到的原始SQL也是一种语言规范,在解析器阶段会对SQL的词法、语法规范、语法分析生成对应的语法树。具体就是对SQL的关键字,比如select、from、where、=等进行语法分析,类似JVM类加载机制的7个阶段,对.class文件进行语法检查,以及理解SQL具体执行意图。

mysql> selct id,name,age from sys_user;
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the
 right syntax to use near 'selct id,name,age from sys_user' at line 1

五、服务端-SQL优化器

       在解析阶段结束后,根据语义分析,MySQL已经理解了本次查询SQL的意图,是要查询user表,以及查询id=1的数据,并且只要id、age、name三个字段。那具体该如何执行更有高效?在查询优化器阶段这里,优化器将进行分析给出最优执行计划。

比如本SQL:select id,name,age from user where id =1;

查询的执行路径可能有:

路径1、先全表扫描,找到全部id=1的数据。

路径2、id有索引,通过索引树去快速查询id=1的数据。

      SQL优化器,做的就是这个执行评估计划工作,将SQL可能执行的路径进行计算评估,选择最优的执行方案,交给存储引擎执行。比如复杂的SQL多表join、group by、max、多个索引条件组合查询等计算,SQL优化器可以发挥最大作用提高SQL执行效率。

六、服务端-存储引擎

      SQL接口、SQL解析器、SQL优化器这三个有点像我们服务端的Service逻辑,都是通用的。而MySQL的存储引擎是可插拔设计,支持大家按规范自研存储引擎。MySQL官方的存储引擎主要有InnoDB,MyISAM。使用方部署的时候,可以按需修改配置选择存储引擎。

     在SQL优化器得到SQL最优执行计划后,就可以提交执行计划给存储引擎去执行数据查询,或者更新数据。

     存储引擎,可以说是MySQL最核心最复杂的一个部分,里面涉及最重要的缓存、事务、回滚等数据处理。

     在这里简单的说,SQL优化器提交过来的执行计划,MySQL的工作线程池,会有一个工作线程,通过调用存储引擎的API,去读取user表数据。如果存储引擎发现缓存有数据,就直接从缓存拿数据进行返回。如果缓存没数据,就去磁盘数据文件,读取相关数据。

七、服务端-日志数据

     MySQL数据库的架构主要有SQL接口、解析器、优化器、存储引擎四个部分。其中核心优化器和存储引擎两个部分。后面SQL优化、数据库调优,也是围绕这两个进行展开。重点是围绕存储引擎深入探讨MySQL核心架构存储原理。

开篇也顺带分享一下数据库日志,尤其是binLog、redoLog、undoLog的概念理解,这三个面试的时候经常有同学被问到而且被问倒。MySQL中涉及SQL优化、数据恢复备份主要存在五种日志,具体如下:

7.1 binlog日志

     这是二进制日志文件,相当于MySQL服务端对数据的全部增删改操作记录日。假如数据丢了,数据的备份也丢了,如果binLog日志还在,我们依然可以恢复数据。

7.2 redoLog重做日志

     如果设置事务提交后无需落盘,缓存里的最新数据在MySQL服务崩溃时,会导致已提交事务数据丢失。这种未落盘的数据记录,在MySQL服务重启恢复后,将通过redoLog日志进行落盘持久化。

7.3 undoLog回滚日志

     undoLog记录事务提交前数据的备份,比如修改一个id=1的数据name=xxx,先在undoLog记录修改前值,方便后面回滚事务需要。

7.4 slowLog慢查询日志

     慢查询日志,主要记录执行时间较长的SQL。具体多久算慢查询SQL,可以通过参数set global long_query_time设置。sql执行大于该阈值的sql,都会被记录到slow log文件。

7.5 relayLog备份中继日志

     从主服务器的binLog日志同步到从服务器本地的缓存文件,格式和binLog日志差不多,主要用于主从复制备份。


综上,从实用性来分享,关于MySQL的日志数据,后面系列文章有必要出一篇结合SQL,一步一图,以及打开具体日志数据,专门深入演示,方便大家探讨。

八、MySQL实用管理命令参数集

1、show grants; 查看当前连接的权限信息。

2、show processlist; 命令查询所有正在运行的线程。

3、show status like "Threads%"; 查询MySQL客户端的连接数。

4、show variables like '%max_connections%'; 查询目前DB的最大连接数。

5、set GLOBAL max_connections = 200; 修改数据库的最大连接数为指定值。

6、show global variables like "%query_cache_type%"; 查询缓存是否开启。

7、show global variables like "%query_cache_size%"; 查询缓存的空间大小。

8、show status like'%Qcache%'; 命令查询缓存相关的统计信息。

9、show variables like '%storage_engine%'; 命令来查看当前所使用的引擎。

10、show variables like 'slow_query%'; 查询慢查询相关参数。

11、set global slow_query_log='ON';  打开慢查询日志记录。

12、set global long_query_time=1; 设置慢查询sql阈值。

13、set global slow_query_log_file='/xx/slow.log'; 慢查询日志存放的位置。

14、show errors; 查看当前库中记录的错误信息。

15、show variables like '%relay%'; 查询中继日志相关配置。

16、sync_binlog: 为0表示每次提交事务都只 write,不 fsync。1表示每次提交事务都会执行 fsync。为N(N>1) ,表示每次提交事务都 write,但累积 N 个事务后才 fsync。

17、innodb_flush_log_at_trx_commit : 参数用于控制 redo log 的写入策略。为 0 表示每次事务提交时都只是把 redo log 留在 redo log buffer 。为 1 表示每次事务提交时都将 redo log 直接持久化到磁盘,为 2 表示每次事务提交时都只是把 redo log 写到 page cache。

18、analyze [local | no_write_to_binlog] table 表名1; 分析表中键是否合理。

19、optimize [local | no_write_to_binlog] table 表名; 优化表,将delete过的数据彻底从磁盘删除,从而释放存储空间。

20、select @@tx_isolation; 查询事务隔离级别。

21、show variables like '%tx_isolation%'; 查询事务隔离级别。

22、set transaction isolation level 级别: 设置当前连接的事务隔离级别。

23、show variables like 'autocommit'; 查看自动提交事务机制是否开启。

24、set @@autocommit = 0|1|ON|OFF; 开启或关闭事务的自动提交。

25、SHOW PROCEDURE STATUS; 查看当前数据库中的所有存储过程。

26、SHOW PROCEDURE STATUS WHERE db = '库名' AND NAME = '过程名'; 查看指定库中的某个存储过程。

27、SHOW CREATE PROCEDURE 存储过程名;查看某个存储过程的源码。

28、导出MySQL中全部的库数据

mysqldump -uroot -p密码 --all-databases > 备份文件名.sql

29、导出MySQL中一部分的库数据

mysqldump -uroot -p密码 --databases > 备份文件名.sql

30、-导出MySQL单库中的一部分表数据

mysqldump –u 用户名 –h主机名 –p密码 库名[表名1,表名2...]> 备份文件名.sql

31、导出MySQL单表的部分数据

mysqldump -u用户名 -p 库名 表名 --where="条件" > 备份文件名.sql


推荐阅读拉丁解牛_JVM专题系列:

1、JVM进阶调优系列(3)堆内存的对象什么时候被回收?

2、JVM进阶调优系列(2)字节面试:JVM内存区域怎么划分,分别有什么用?

3、JVM进阶调优系列(1)类加载器原理一文讲透

4、JAVA并发编程系列(13)Future、FutureTask异步小王子

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
3月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
3月前
|
存储 监控 算法
园区导航系统技术架构实现与原理解构
本文聚焦园区导航场景中室内外定位精度不足、车辆调度路径规划低效、数据孤岛难以支撑决策等技术痛点,从架构设计到技术原理,对该系统从定位到数据中台进行技术拆解。
110 0
园区导航系统技术架构实现与原理解构
|
4月前
|
存储 消息中间件 canal
zk基础—2.架构原理和使用场景
ZooKeeper(ZK)是一个分布式协调服务,广泛应用于分布式系统中。它提供了分布式锁、元数据管理、Master选举及分布式协调等功能,适用于如Kafka、HDFS、Canal等开源分布式系统。ZK集群采用主从架构,具有顺序一致性、高性能、高可用和高并发等特点。其核心机制包括ZAB协议(保证数据一致性)、Watcher监听回调机制(实现通知功能)、以及基于临时顺序节点的分布式锁实现。ZK适合小规模集群部署,主要用于读多写少的场景。
|
5月前
|
存储 人工智能 自然语言处理
为什么混合专家模型(MoE)如此高效:从架构原理到技术实现全解析
本文深入探讨了混合专家(MoE)架构在大型语言模型中的应用与技术原理。MoE通过稀疏激活机制,在保持模型高效性的同时实现参数规模的大幅扩展,已成为LLM发展的关键趋势。文章分析了MoE的核心组件,包括专家网络与路由机制,并对比了密集与稀疏MoE的特点。同时,详细介绍了Mixtral、Grok、DBRX和DeepSeek等代表性模型的技术特点及创新。MoE不仅解决了传统模型扩展成本高昂的问题,还展现出专业化与适应性强的优势,未来有望推动AI工具更广泛的应用。
1813 4
为什么混合专家模型(MoE)如此高效:从架构原理到技术实现全解析
|
5月前
|
SQL 存储 自然语言处理
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
|
5月前
|
机器学习/深度学习 算法 测试技术
图神经网络在信息检索重排序中的应用:原理、架构与Python代码解析
本文探讨了基于图的重排序方法在信息检索领域的应用与前景。传统两阶段检索架构中,初始检索速度快但结果可能含噪声,重排序阶段通过强大语言模型提升精度,但仍面临复杂需求挑战
139 0
图神经网络在信息检索重排序中的应用:原理、架构与Python代码解析
|
5月前
|
Java 开发者 Spring
Spring框架 - 深度揭秘Spring框架的基础架构与工作原理
所以,当你进入这个Spring的世界,看似一片混乱,但细看之下,你会发现这里有个牢固的结构支撑,一切皆有可能。不论你要建设的是一座宏大的城堡,还是个小巧的花园,只要你的工具箱里有Spring,你就能轻松搞定。
210 9
|
SQL 存储 关系型数据库
MySQL下使用SQL命令进行表结构与数据复制实践
MySQL下使用SQL命令进行表结构与数据复制实践
239 0
|
SQL 关系型数据库 MySQL
MySQl数据库第八课-------SQL命令查询-------主要命脉2
MySQl数据库第八课-------SQL命令查询-------主要命脉
158 0

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多