阶段五：MySQL8底层架构与调优

任务背景

随着用户量和数据量的快速增长，系统的MySQL数据库面临着越来越多的查询性能问题，特别是在高并发情况下，查询响应时间显著增加，影响了系统的稳定性和用户体验。运维团队的主要任务是通过SQL查询的监控与优化，确保数据库在大数据量和高并发环境下仍然能够保持良好的性能表现。

任务拆解

MySQL体系结构（了解）

查询性能监控与分析（重点） => 优化手段

启用MySQL的慢查询日志，定期分析执行时间较长的SQL查询，识别出性能瓶颈。

使用EXPLAIN命令分析复杂查询的执行计划，识别全表扫描等问题。

通过SHOW PROCESSLIST工具，实时监控正在运行查询，找出占用系统资源较多的SQL语句，分析其影响。

任务目标

[ ] 通过慢查询日志和性能监控工具，识别并分析查询性能问题。

MySQL体系结构

面试题

MySQL底层，一条SQL语句的执行流程/原理？

答：经过四层，分别是连接层（连接池）、服务层（查询缓存、分析器、优化器、执行器）、引擎层、存储层。

数据：相当于以前的汉语词典中的汉字。

索引：相当于以前的汉语词典中的目录

全表扫描：没有经过索引的操作，叫做全表扫描

走索引（主键索引、唯一索引、普通索引、前缀索引、联合索引...）

1、客户端

2、服务端

1、连接层

2、服务层

查询缓存

1、分析器（词法、语法分析）

优化器

2、执行器

3、引擎层（重点）

4、存储层

1、客户端(连接者)

MySQL的客户端可以是某个客户端软件（DataGrip）

MySQL的客户端可以是不同的编程语言(Python/Java等)编写的应用程序

MySQL的客户端还可以是一些API的接口

2、连接层

主要作用：管理和缓冲用户连接，为客户端请求做连接处理；身份认证等。

面试过程：进程和线程？

进程：一个应用软件，启动后往往会产生1个甚至多个进程，进程需要消耗一定的计算机资源（CPU、内存、磁盘、网络），适合CPU密集型应用（大量的计算程序，需要消耗资源）

线程：一个进程可以产生多个线程，线程不能单独存在，必须依赖进程。所有线程共享进程资源，线程启动、停止快，资源开销小。适合IO密集型应用（文件操作、网络爬虫、数据库连接）

连接层中的缓存池，就是为了优化数据库连接而设置的机制，专门用来缓存和复用已建立的连接。其主要作用是：

1. 减少连接开销：避免每次新建连接带来的系统负担，提升性能。 2. 提升响应速度：连接池里有现成的连接，随取随用，减少等待时间。 3. 优化资源利用：通过控制最大连接数，防止过多连接耗尽系统资源。

工作流程很简单：创建连接→用完放回池中→再次复用，保持高效循环。

总结就是：连接池通过缓存连接，减少开销、加快响应、合理利用资源，让数据库更高效应对高并发。

连接池配置相关参数：

1. max_connections：指定MySQL可以同时处理的最大连接数，控制最大连接数目。 2. wait_timeout：定义一个连接在闲置状态下最多可以等待的时间，超过这个时间将被关闭。 3. thread_cache_size：控制线程缓存池的大小，以缓存空闲线程，避免频繁创建和销毁线程所带来的开销。

thread_cache_size：根据系统并发情况设置，通常设置为CPU核心数的2倍（超线程），以便高效处理并发请求

3、服务层

主要作用：接受用户的SQL请求，查询分析，权限处理，优化，结果缓存等。

4、引擎层（重要）

什么是存储引擎？

1）存储引擎说白了就是如何管理操作数据(存储数据、如何更新、查询数据等)的一种方法和机制。

2）在MySql数据库中提供了多种存储引擎，各个存储引擎的优势各不一样 => show engines

3）用户可以根据不同需求为数据表选择不同的存储引擎，也可以根据自己需要编写自己的存储引擎。

4）甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

mysql> show engines;

XA：XA （eXtended Architecture 扩展架构）是X/Open组织提出的分布式数据库的一种协议标准；

Savepoints：保存点，是事务中的标记点，允许部分回滚（回滚到指定标记，而非整个事务），可以更精准地进行事务记录和回滚。

最常用的存储引擎是InnoDB和MyISAM

存储引擎	描述
InnoDB（MySQL5.6版本及以后）	支持拥有ACID特性事务的存储引擎，并且提供行级的锁定，支持外键、应用广泛。侧重于数据安全，默认引擎
MyISAM（MySQL5.5及之前版本）	查询速度快，有较好的索引优化和数据压缩技术；但不支持事务、不支持外键约束。 <br/>适用于读多写少的应用场景
NDB	用于MySQL Cluster的集群存储引擎，提供数据层面的高可用性
MEMORY	存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。 <br/>适合于需要快速的访问或临时表。
BLACKHOLE	黑洞存储引擎，写入的任何数据都会消失，应用于主备复制中的分发主库（中继slave）

面试：你用过哪些数据库引擎？各自特点？

答：早期MySQL5.5版本使用过MyISAM引擎，后期MySQL5.7、MySQL8.0等等都是使用InnoDB引擎，偶尔也了解MEMORY引擎。

① MyISAM引擎，擅长数据查询，支持较好的索引优化、数据压缩、支持表级锁以及全文索引技术，安全性相对于InnoDB略差一些。

索引优化 => 主键索引（图书目录），有索引，查询速度会更快

数据压缩 => 减少存储空间占用

表级锁 => 只能进行表级锁，就是锁表时，要锁定整个数据表，在这个过程中，这个表只能进行查询操作，而不能进行增删改等操作，但是粒度太大，对并发有一定的影响。

全文索引 => 从一篇文章中搜索指定内容，类似模糊查询，更加强大一些。

② InnoDB引擎，擅长数据安全，支持支持行级锁，支持事务处理，支持外键约束等等，强调安全性。

行级锁 => 只会对某一行进行锁定，不会全表锁定，粒度更细，并发能力更强。

事务处理 => 一种数据安全策略，保证数据安全

外键约束

③ Memory引擎，擅长数据缓存，加快数据查询，但是由于数据放置于内存，所以安全性没有MyISAM以及InnoDB好。

扩展：InnoDB事务处理

应用场景：银行转账（最典型）

我的银行卡：0.10

李文凯银行卡：2000.00

发生一系列操作：① 李文凯发起转账，扣款1000，余额-1000 ② 银行接收任务，处理（ATM） ③ 我的银行卡接收到1000，余额+1000

update bank set money=money-1000 where name = "李文凯"

ATM停电了

update bank set money=money+1000 where name = "我"

操作步骤：事务处理配合Python/Java程序一起使用，就是把所有要执行的SQL语句当做一个整体，要么全部成功，要么全部失败。

① 开启事务处理功能 => start transaction;

② 执行一系列的SQL语句（多条）

update bank set money=money-1000 where name = "李文凯"

update bank set money=money+1000 where name = "我"

③ 判断SQL语句是否全部执行成功，如果成功则提交事务 => commit; 失败，则回滚事务 => rollback;。

-- 代码示例：
start transaction;
update bank set money=money-1000 where name = "李文凯";
update bank set money=money+1000 where name = "我";
commit;

5、存储层（物理层）

核心作用：物理层负责与底层的操作系统交互，将数据存储到磁盘上，并确保数据的物理安全。

默认存储在/export/server/mysql/data数据目录下

工作方式：

将数据以物理文件的形式存储在磁盘上（如表空间文件、数据文件、日志文件等）。

通过文件系统与操作系统进行交互，管理数据的读写、缓存、索引文件等。

总结

MySQL体系结构分为哪几层？四层（连接层、服务层（分析器、执行器）、引擎层、物理层）

每一层是如何工作的？

MySQL8.0 版本以后默认的存储引擎是哪个？有什么特点？除了这种引擎，你还了解哪些其他引擎？（至少说出2-3种）

扩展：生产环境下，MySQL到底应该如何配置呢？

答：这里所谓的配置主要是针对/etc/my.cnf（MySQL优化、处理等等都是由my.cnf决定的）。

数据引擎

MySQL体系结构

1、存储引擎层

存储引擎层：简单来说，就是数据的存储方式。在MySQL中，我们可以使用 show engines 查看当前数据库版本支持哪些引擎，常见的数据存储引擎：InnoDB、MyISAM等等。

MyISAM与InnoDB 引擎的对比表：

特性	MyISAM	InnoDB
事务支持	不支持	支持（ACID特性）
外键支持	不支持	支持
锁机制	表级锁	行级锁
适用场景	读多写少的应用，查询性能好	高并发写操作的应用
崩溃恢复	数据损坏风险高，无法自动恢复	自动恢复，数据更安全
存储结构	每个表有单独的文件	表和索引存储在共享表空间
性能	查询性能较好，但不支持事务处理	支持事务处理，写操作性能较高

面试题：MySQL中，MyISAM、InnoDB引擎的区别？

参考：MyISAM 不支持事务和外键，索引与数据分离，查询快但安全性低；InnoDB 支持事务、外键，聚簇索引，适合高并发场景。

2、数据文件存储

问题：数据库到底是如何保存数据文件的？

mysql> create database db_itheima default charset=utf8;

当数据库创建完毕后，查看/export/server/mysql/data文件夹：

3、MyISAM引擎

mysql> use db_itheima;
mysql> create table tb_test_myisam(id int) engine=myisam default charset=utf8mb4;

查看db_itheima目录结构，如下图所示:

MyISAM引擎：

*.sdi=> 序列化字典信息（Serialized Dictionary Information）表的元数据信息，主要用于数据字典管理；数据表结构、字段、类型等等

*.MYI=> INDEX索引，主要用于存放索引文件；

*.MYD=> Data 数据文件，主要用于存储数据文件；

早期MySQL5.7及以前版本，没有\*.sdi文件，只有\*.frm文件

早期MySQL5.7及以前版本，我们可以通过cp \*.frm、\*.MYI、\*.MYD这三个文件来实现MyISAM引擎表的备份，MySQL8.0以后引入更多复杂的功能，导致没有办法直接copy，只能通过物理备份或逻辑备份！！！

4、InnoDB引擎

mysql> use db_itheima;
mysql> create table tb_user2(id int, name char(1)) default charset=utf8mb4;

InnoDB引擎：

.ibd：每个表都会有一个独立的 .ibd 文件，存储该表的表数据和索引。

ibdata1：用于存储全局的表空间、数据字典和事务日志等。

redo log日志文件（ib_logfile0, ib_logfile1 等）：用于存储事务日志，确保数据一致性和恢复能力。

redo log配置：

[mysqld]
innodb_log_file_size = 50M
innodb_log_files_in_group = 2

innodb_log_group_home_dir = /export/server/mysql/data  # redo log 文件存放路径

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

课堂作业

为什么InnoDB引擎产生的表文件，很多文件大小都是一致的？

慢查询日志

1、启动慢查询日志（重点）

SQL语句：执行过程中，有快有慢，找出慢查询SQL。

作用：慢查询日志记录了所有执行时间超过设定阈值的查询（300ms、1s、5s根据不同业务场景而定），帮助你发现需要优化的慢查询SQL语句，辅助优化。

如何启用：

vim /etc/my.cnf
[mysqld]
...(省略其他原有信息)

# 开启慢查询日志
slow_query_log=1
# 指定慢查询日志文件存放路径
slow_query_log_file=/export/server/mysql/logs/mysql-slow.log
# 设置超过 1 秒的查询被记录
long_query_time=1

# 记录未使用索引的查询（可选）
log_queries_not_using_indexes=1

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

mkdir -p /export/server/mysql/logs
touch /export/server/mysql/logs/mysql-slow.log
# 必须修改权限
chown -R mysql.mysql /export/server/mysql

作业：touch、echo、vim、mkdir -p

命令作用：

创建目录 /export/server/mysql/logs；-p 会同时创建缺失的父目录，并且目录已存在时不报错。

修改目标文件或目录的属主和属组；服务进程需要以正确用户身份读取数据和配置。

执行结果：

成功后目标目录会存在；可用 ls -ld 目录名 查看目录权限和属主。

成功后目标文件的属主或属组会改变；可用 ls -l 验证。

关键参数：

参数	说明
`-p`	核心	递归创建目录；父目录不存在时一并创建，目录已经存在也不报错。

注意事项：

做一个查询，然后查看效果



简单来说：explain执行计划就是用于分析一个SQL语句如何执行的，核心作用：帮助我们提升SQL查询效率，加快查询速度。



学不认识的汉字，会去词典当中寻找



![](/assets/feishu-images/ebda8da79b8db57fc0f8cd08.png)





如何使用：

同样的，在慢日志中，也可以看到记录

没有查看到慢日志，是怎么回事？

把引号当中的命令，执行一下



联合索引，其实是普通索引的扩展。通常适应于加快查询多个字段的结果。


![](/assets/feishu-images/111fec864f88be6810e50113.png)

![](/assets/feishu-images/0e13e01f832a6cf176801518.png)

这个输出告诉我们：


**总结对比**


key_ken长度说明：






面试题：在运维环境中，发现MySQL运行缓慢，如何去解决？

答：







注意：适合执行时间较长的SQL语句

分析重点：

Command：表示查询的状态，比如Query表示正在执行查询，Sleep表示连接空闲。

Time：表示查询的执行时间，时间较长的查询可能是性能瓶颈。


![](/assets/feishu-images/2e6ffc4cd873519ffb72656d.png)




缺点：无法拆分显示子查询的单独性能；