运维学习（第九周）-CSDN博客

一、总结mysql的全局变量、会话变量、配置文件选项、启动相关选项

1.全局变量

定义：影响整个MySQL服务器行为的变量，所有连接共享
设置方式：

启动时通过配置文件或命令行参数设置
运行时通过 SET GLOBAL 语句设置（需要SUPER权限）

查看方式：

SHOW GLOBAL VARIABLES;

示例：

SET GLOBAL max_connections = 200;

2.会话变量

定义：只影响当前连接的变量，其他连接不受影响
设置方式：连接建立后通过 SET SESSION 语句设置
查看方式：

SHOW SESSION VARIABLES;

示例：

SET SESSION sql_mode = 'STRICT_ALL_TABLES';

3.配置文件选择

定义：MySQL服务器启动时读取的参数，通常写在配置文件（如 my.cnf 或 my.ini）中
作用：决定MySQL服务的初始行为和参数，部分参数只能通过配置文件设置
常见位置：/etc/my.cnf、/etc/mysql/my.cnf、~/.my.cnf

4.启动相关选项

定义：MySQL服务进程启动时通过命令行参数指定的选项
作用：覆盖配置文件中的同名参数，临时生效
常见用法：

mysqld --port=3307 --max_connections=300

特点：

优先级高于配置文件
只在本次启动有效，重启后失效

5.关系与区别

类型	生效范围	设置方式	优先级	典型用途
全局变量	整个服务器	配置文件/命令行/SQL	中	运行时调整服务器参数
会话变量	当前连接	SQL	低	临时调整当前会话行为
配置文件选项	整个服务器	配置文件	低	持久化服务器参数
启动相关选项	整个服务器	启动命令行	高	临时覆盖配置文件参数

二、总结mysql的不同类型日志的打开、关闭方法以及使用场景

1.错误日志

作用：

记录MySQL服务器启动、关闭、运行过程中的错误信息、警告信息等
诊断MySQL服务异常、崩溃等问题

开启/关闭方法：默认开启，无法关闭
使用场景：

排查MySQL启动失败、崩溃、权限等问题
监控数据库运行状态

2.通用查询日志

作用：记录所有连接、断开、执行的SQL语句
开启/关闭方法：

配置文件开启：

[mysqld]
general_log=1
general_log_file=/var/log/mysql/general.log

运行时开启或关闭：

SET GLOBAL general_log = 'ON';   -- 开启
SET GLOBAL general_log = 'OFF';  -- 关闭

使用场景：

SQL调试、开发测试环境下分析所有SQL语句
生产环境不建议长期开启，会影响性能

3.慢查询日志

作用：记录执行时间超过指定阈值的SQL语句，帮助定位慢SQL
开启/关闭方法：

配置文件开启：

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2  # 超过2秒的SQL被记录

运行时开启或关闭：

SET GLOBAL slow_query_log = 'ON';   -- 开启
SET GLOBAL slow_query_log = 'OFF';  -- 关闭
SET GLOBAL long_query_time = 2;     -- 设置阈值

使用场景：

性能优化，定位慢SQL
生产环境建议开启

4.二进制日志

作用：

记录所有更改数据库数据的操作
用于主从复制、数据恢复

开启/关闭方法：

配置文件开启：

[mysqld]
log_bin=mysql-bin

关闭：注释或删除 log_bin 配置，重启MySQL

注意：不能在线开启/关闭，需重启服务

使用场景：

主从复制、数据恢复、点时间恢复
生产环境建议开启

5.中继日志

作用：仅在从库（slave）上使用，记录主库传来的二进制日志事件
开启/关闭方法：由MySQL复制机制自动管理，无需手动开启/关闭
使用场景：主从复制，从库同步主库数据

6.审计日志

作用：记录用户操作、SQL语句、登录登出等详细信息
开启/关闭方法：需安装审计插件（如 audit_log）

开启：

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';

关闭：

UNINSTALL PLUGIN audit_log;

使用场景：安全合规、操作审计

三、总结mysql执行的原理

1.MySQL就像一个大型餐厅的厨房：

你（客户端）下单（发SQL），服务员（连接器）接单并确认身份，主厨（优化器）设计最快的烹饪步骤（执行计划），然后交给厨师（执行器）按照步骤，从仓库（存储引擎）取食材（数据）进行烹饪（操作），最后上菜（返回结果）

2.四个核心步骤

连接与验证（接单）：MySQL检查你的身份和权限，看你能不能进店、能点哪些菜
解析与优化（设计菜谱）：

MySQL读懂你的SQL指令，确保语法没问题
优化器会想出最高效的执行方法。比如，是查索引快，还是直接全表找比较快

执行（开火做菜）

执行器按照优化器规划好的方案，指挥存储引擎去干活
存储引擎是真正的“仓库管理员”，负责从硬盘上存取数据

返回结果（上菜）：执行器将从存储引擎拿到的结果，返回给你

3.执行原理流程图：

客户端
   |
   v
连接管理/权限认证
   |
   v
SQL解析/预处理
   |
   v
查询优化器
   |
   v
执行器
   |
   v
存储引擎
   |
   v
日志/事务管理
   |
   v
返回结果

四、总结DDL、DML

1.核心定义

DDL - 数据定义语言

作用：定义和管理数据库的结构或骨架。用来创建、修改或删除数据库对象，如表、索引、视图等
比喻：建筑图纸。用来设计房子的结构，而不是搬入家具

DML - 数据操纵语言

作用：查询和操作数据库中表里的数据。用来添加、修改、删除或查询记录
比喻：搬运家具。在设计好的房子里添置、更换或扔掉家具和物品

2.常见命令

类型	命令关键字
DDL	CREATE（创建），ALTER（修改），DROP（删除），TRUNCATE（清空）
DML	SELECT（查询），INSERT（插入），UPDATE（更新），DELETE（删除）

3.核心区别对比

特性	DDL（数据定义语言）	DML（数据操纵语言）
操作对象	数据库、表、索引等结构	表中的数据行
事务控制	隐式提交，执行后自动提交事务，无法回滚	需要手动提交，属于事务的一部分，可以用 COMMIT 提交或 ROLLBACK 回滚
执行速度	较慢，因为需要修改数据字典	相对较快，直接操作数据
WHERE子句	通常不使用（ALTER 可能会用）	经常使用，用于筛选操作的行
回滚能力	不可回滚	可以回滚（在 COMMIT 之前）

五、总结一个mysql用户管理场景，例如: 授权开发只读访问某个或某几个库

1.创建用户

创建一个新用户，并设置密码和登录IP

-- 创建一个叫 dev_user 的用户，密码是 your_password
-- '%' 表示可以从任何IP登录（为安全可换成指定IP，如 '192.168.1.%'）
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'your_strong_password';

2.授予权限

给他分配指定数据库的“只读”（SELECT）权限

-- 授权查询 dev_db 库
GRANT SELECT ON dev_db.* TO 'dev_user'@'%';

-- 授权查询 analytics_db 库
GRANT SELECT ON analytics_db.* TO 'dev_user'@'%';

注：db_name.* 的意思是数据库名.所有表

3.刷新生效

让上面的设置立即生效

FLUSH PRIVILEGES;

4.验证和撤销

查看权限：

SHOW GRANTS FOR 'dev_user'@'%';

收回权限：

REVOKE SELECT ON dev_db.* FROM 'dev_user'@'%';

删除用户：

DROP USER 'dev_user'@'%';

六、总结mysql查询时的执行流程

把这个流程想象成你去一个大型图书馆（MySQL）借一本指定的书（查询数据）：

连接器：图书馆门禁。检查你的借书证（用户名/密码）是否有效，确认你有没有资格进馆（权限）。
分析器：图书管理员。他接过你的借书单（SQL语句），检查你写的书名、作者等信息是否规范、有没有写错字（语法分析）。
优化器：最强大脑/路径规划师。他知道所有书架的位置和索引卡片（索引）。他会规划出找到这本书的最快路径（执行计划）。比如是直接去C区3架找，还是先查电脑索引再过去。
执行器：图书管理员的助手。他拿到路径规划图，严格按照指示去跑腿，找到具体的书架。
存储引擎：书架和仓库。这是真正存放书籍的地方。助手（执行器）从书架（存储引擎）上把书（数据）取出来。
返回结果：书找到了，交到你手上。

七、总结mysql explain，尝试给自己的表A写100万行数据，表B写30万数据，使用LEFT JOIN, WHERE, GROUP BY 时，分析慢SQL

1.MySQL EXPLAIN 总结

EXPLAIN 用于分析SQL的执行计划，常见字段如下：

字段	说明
id	查询中每个SELECT子句的标识符，越大优先级越高
select_type	查询类型（SIMPLE、PRIMARY、SUBQUERY、DERIVED等）
table	当前访问的表名
type	连接类型（ALL、index、range、ref、eq_ref、const、system、NULL）
possible_keys	可能用到的索引
key	实际用到的索引
key_len	使用的索引长度
ref	哪个字段或常量与key一起使用
rows	预估需要读取的行数
Extra	额外信息（如Using where、Using index、Using temporary等）

2.尝试

表A：100万行
表B：30万行
SQL涉及：LEFT JOIN、WHERE、GROUP BY

示例：

SELECT A.col1, COUNT(B.col2)
FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE A.status = 1
GROUP BY A.col1;

3.慢SQL常见原因分析

JOIN 关联字段无索引

问题：LEFT JOIN B ON A.id = B.a_id，如果B.a_id没有索引，MySQL会对B全表扫描，导致性能极差
优化：确保 B.a_id 有索引

WHERE 条件无索引

问题：WHERE A.status = 1，如果A.status没有索引，会导致A表全表扫描
优化：为A.status加索引

GROUP BY 字段无索引

问题：GROUP BY A.col1，如果A.col1基数大且无索引，分组会很慢
优化：为A.col1加索引

关联表数据量大

问题：A、B数据量大，JOIN后数据量更大，导致排序、分组、临时表、文件排序等操作慢
优化：尽量减少JOIN前的数据量（如先用子查询过滤A、B），或考虑分批处理

Extra 字段提示

Using temporary：用到了临时表，通常是GROUP BY 或 ORDER BY导致
Using filesort：用到了文件排序，通常是ORDER BY 或 GROUP BY导致
这两者都说明SQL有优化空间

八、总结mysql事务原理

1.核心概念：

事务 (Transaction) 是一个不可分割的数据库操作序列，这些操作要么全部成功，要么全部失败。它将一组SQL语句打包成一个逻辑工作单元

比喻：银行转账 A给B转账100元，这个操作包含两个步骤：

A的账户 -100 元
B的账户 +100 元

这两个步骤必须是一个事务。如果第一步成功后系统崩溃，第二步没执行，就会凭空“丢钱”。事务保证了这种情况不会发生

2.事务的四大特性 (ACID)

这是事务的理论基石，是面试和理解事务的关键。

原子性 (Atomicity)
- 定义：一个事务中的所有操作，要么全部完成，要么全部不执行。不会停在中间某个环节
- 实现：主要由 Undo Log (回滚日志) 保证。如果事务失败，系统会利用Undo Log将数据恢复到事务开始前的状态
一致性 (Consistency)
- 定义：事务执行前后，数据库都必须处于一种合法的、一致的状态。比如，转账前后，A和B账户的总金额不变
- 实现：由应用程序、数据库约束（如主键、外键）以及其他三大特性（A、I、D）共同保证。一致性是最终目标
隔离性 (Isolation)
- 定义：当多个事务并发执行时，一个事务的执行不应被其他事务干扰。即事务内部的操作对并发的其他事务是隔离的
- 实现：主要由锁机制和多版本并发控制 (MVCC) 保证
持久性 (Durability)
- 定义：一个事务一旦被提交 (COMMIT)，它对数据库的改变就是永久性的。即使系统崩溃，提交的数据也不会丢失
- 实现：主要由 Redo Log (重做日志) 保证

3.核心实现原理 (InnoDB引擎)

MySQL的事务功能主要由InnoDB存储引擎提供，其实现依赖于两个关键的日志文件：

Undo Log (回滚日志)
- 作用：
  - 实现原子性：当事务需要回滚（ROLLBACK）时，系统根据Undo Log记录的旧值来恢复数据
  - 实现隔离性 (MVCC)：当一个事务需要读取某行数据，而另一并发事务正在修改该行时，系统会从Undo Log中读取该行修改前的“快照”版本，从而实现非阻塞读
- 记录内容：记录的是数据的逻辑旧值。比如 UPDATE 就记录修改前的值，INSERT 就记录主键（方便回滚时删除）
Redo Log (重做日志)
- 作用：
  - 实现持久性：当数据被修改时，InnoDB会先将修改记录写入Redo Log（此时数据还在内存Buffer Pool中），然后再择机将内存数据刷到磁盘。即使数据库宕机，也可以通过Redo Log来恢复已提交事务的数据，保证数据不丢失
- 记录内容：记录的是物理层面的修改，即“在哪个数据页的哪个位置做了什么修改”
- 关键技术：WAL (Write-Ahead Logging)，即“先写日志，再写数据文件”，这是保证性能和持久性的关键

九、总结mysql日志相关的查询及分析

日志类型	核心用途 (一句话总结)	如何定位 (查询命令)	如何分析 (核心工具/命令)	分析命令示例
错误日志<br>(Error Log)	"服务起不来/挂了"<br>排查MySQL启动、运行时的崩溃和严重错误。	SHOW VARIABLES LIKE 'log_error';	tail, grep, less <br>(直接看文本文件)	tail -n 100 /path/to/error.log
慢查询日志<br>(Slow Query Log)	"应用变慢了/性能优化"<br>找出执行效率低的SQL。	SHOW VARIABLES LIKE 'slow_query_log%';	mysqldumpslow<br>(官方自带分析工具)	mysqldumpslow -s t -t 10 /path/to/slow.log
通用查询日志<br>(General Log)	"想知道MySQL刚干了啥"<br>审计或调试所有SQL。(警告：严重影响性能)	SHOW VARIABLES LIKE 'general_log%';	tail, grep<br>(直接看文本文件)	tail -f /path/to/general.log
二进制日志<br>(Binary Log)	"数据误删了/主从同步"<br>用于数据恢复和主从复制。	SHOW MASTER STATUS;<br>SHOW BINARY LOGS;	mysqlbinlog<br>(官方自带解析工具)	mysqlbinlog --start-datetime="..." /path/to/mysql-bin.000001