MySQL 数据库 6（Day54）

主题：授权库（mysql 系统库）与五级授权范围、GRANT/REVOKE 权限分配、扩展权限（GRANT OPTION 与资源限制）、最小权限原则、存储引擎基本管理与转换、InnoDB 逻辑架构（Buffer Pool/Change Buffer/Log Buffer/双写缓冲）、Redo/Undo Log 与崩溃恢复、脏页刷盘、页/区/段/表空间逻辑结构

---

结构总览

MySQL 数据库 6
  ├─ 授权库与授权范围
  │    ├─ 账号 = 用户名 + 主机来源（'egon'@'localhost' ≠ 'egon'@'%'）
  │    └─ 五级范围：全局 *.* / 库 db1.* / 表 db1.blog / 列 / 存储过程
  ├─ 扩展权限：WITH GRANT OPTION / MAX_USER_CONNECTIONS / 每小时配额
  ├─ 权限分配：GRANT / REVOKE + 最小权限原则
  ├─ 存储引擎介绍与基本管理
  │    ├─ SHOW ENGINES / SHOW TABLE STATUS / information_schema.TABLES
  │    └─ 建表指定 ENGINE= / ALTER TABLE ... ENGINE=InnoDB 转换
  ├─ InnoDB 逻辑架构（一）：请求流程 + 内存结构 + 磁盘结构
  └─ InnoDB 逻辑架构（二）：内存/OS Cache/硬盘三层视角
       ├─ 写入路径：脏页 → Redo Log → 刷盘 → 崩溃恢复
       ├─ Doublewrite Buffer 防部分写损坏
       └─ 逻辑结构：表→索引→段→区→页→表空间；聚簇索引与回表

关键要点

授权库与账号模型： 权限信息保存在系统数据库 mysql 中。MySQL 账号由用户名 + 主机来源共同组成，'egon'@'localhost' 和 'egon'@'%' 是两个不同账号；权限由「用户是谁 + 从哪里连 + 访问对象范围」三者共同决定。

SELECT USER(), CURRENT_USER();     -- 当前用户
SHOW GRANTS;                        -- 当前权限
SELECT User, Host FROM mysql.user;  -- 全部账号

五级授权范围与对应系统表：

授权范围	对象写法	对应授权表	说明
全局级	*.*	mysql.user	整个 MySQL 服务
数据库级	db1.*	mysql.db	某个数据库
数据表级	db1.blog	mysql.tables_priv	某张表
数据列级	db1.blog(列)	mysql.columns_priv	某些列，控制最细
存储过程级	PROCEDURE db1.p1	mysql.procs_priv	存储过程/函数

权限范围越具体控制越精细；全局级覆盖最大。优先用 CREATE USER/GRANT/REVOKE 管理，不建议直接改系统表。

-- 各级授权示例
CREATE USER 'egon'@'%' IDENTIFIED BY '123';
GRANT ALL PRIVILEGES ON *.* TO 'egon'@'%';                    -- 全局
CREATE USER 'tom'@'%' IDENTIFIED BY '123';
GRANT ALL PRIVILEGES ON db1.* TO 'tom'@'%';                   -- 库级
CREATE USER 'lili'@'%' IDENTIFIED BY '123';
GRANT ALL PRIVILEGES ON db1.blog TO 'lili'@'%';               -- 表级
CREATE USER 'xxx'@'%' IDENTIFIED BY '123';
GRANT SELECT (id, sub_time), UPDATE (id) ON db1.blog TO 'xxx'@'%';  -- 列级
SHOW GRANTS FOR 'tom'@'%';                                    -- 查看指定用户

注意：高版本 MySQL 应先 CREATE USER 再 GRANT（旧版才支持 GRANT … IDENTIFIED BY 直接建用户）；8.0 中 CREATE USER/GRANT 后即时生效，直接改授权表才需要 FLUSH PRIVILEGES。列级 UPDATE 通常还需配套 SELECT（更新时要读原值）。

扩展权限与资源限制：

配置	作用
WITH GRANT OPTION	允许用户把自己拥有的权限继续授予他人（只给授权管理员，不给业务账号）
WITH MAX_USER_CONNECTIONS n	限制该用户同时建立的连接数
WITH MAX_QUERIES_PER_HOUR n	每小时查询次数限制
WITH MAX_UPDATES_PER_HOUR n	每小时更新次数限制
WITH MAX_CONNECTIONS_PER_HOUR n	每小时连接次数限制

GRANT ALL PRIVILEGES ON *.* TO 'yyy'@'%' WITH GRANT OPTION;
ALTER USER 'zzz'@'%' WITH MAX_QUERIES_PER_HOUR 100;
SHOW CREATE USER 'zzz'@'%';                 -- 查看资源限制
REVOKE GRANT OPTION ON *.* FROM 'yyy'@'%';  -- 收回转授能力

权限分配原则： 常见权限 SELECT/INSERT/UPDATE/DELETE/CREATE/DROP/ALTER/INDEX/EXECUTE/REFERENCES/ALL PRIVILEGES。回收用 REVOKE。排错：REVOKE 后仍可访问 → 更高范围（如 db1.*）的同类授权仍覆盖该表；遵循最小权限原则（Principle of Least Privilege）——业务账号绝不给 ALL ON *.*。

存储引擎基本管理：

SHOW ENGINES;                                          -- 支持的引擎及状态
SHOW VARIABLES LIKE 'default_storage_engine';          -- 默认引擎
SHOW TABLE STATUS FROM db1 LIKE 'blog';                -- 单表引擎
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES WHERE TABLE_SCHEMA='db1';
 
CREATE TABLE t3 (id INT PRIMARY KEY) ENGINE=InnoDB;    -- 建表指定
ALTER TABLE db2.t1 ENGINE=InnoDB;                       -- 转换引擎（重建表）

四引擎对比：InnoDB 支持事务/行级锁/外键/崩溃恢复/MVCC → 事务型业务首选；MyISAM 表级锁、读快但不支持事务外键；MEMORY 数据在内存重启丢失；CSV 以 CSV 文件保存。⚠️ ALTER TABLE ... ENGINE= 会重建整张表，大表转换占用磁盘/CPU/I/O；改默认引擎只影响新建表，已有表需逐个 ALTER。

InnoDB 逻辑架构（第一部分）— SQL 请求流程与结构组成：

客户端发 SQL → 服务端（连接管理/权限验证/SQL 解析/执行计划）→ 执行器调 InnoDB 接口
→ Buffer Pool 有数据页？有则直接访问；无则从磁盘读入缓冲池
→ 修改先发生在内存，之后按日志与刷盘机制落盘

内存结构：Buffer Pool（缓存数据页/索引页，性能核心）、Change Buffer（缓存非唯一二级索引页修改）、Adaptive Hash Index（自适应哈希索引）、Log Buffer（暂存重做日志内容）、Dictionary Cache（元数据缓存）。磁盘结构：表空间文件、重做日志文件、双写缓冲区域、撤销日志区域、数据与索引。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

排错：磁盘 I/O 高 → 检查缓冲池大小与命中情况；COMMIT 后数据页不会立即同步写数据文件——提交、日志持久化、脏页刷盘是三个不同过程。

InnoDB 逻辑架构（第二部分）— 三层数据访问视角与写入/恢复路径：

MySQL 服务端 → InnoDB → 用户态内存空间（Buffer Pool）
操作系统    → 文件系统 → OS Cache
计算机硬件  → 硬盘     → 硬盘结构

写操作流程：事务修改 → 数据页在 Buffer Pool 中变为脏页（Dirty Page） → 修改记录到 Redo Log → 提交时按配置保证日志持久化 → 后台线程逐步把脏页刷回数据文件 → 异常重启时用 Redo Log 恢复已提交但未落盘的数据。

组件	职责
Redo Log	崩溃恢复，记录数据页物理修改（WAL）
Undo Log	事务回滚 + 多版本并发控制（MVCC）
Doublewrite Buffer	降低数据页部分写（partial write）导致页面损坏的风险

关键配置：innodb_flush_log_at_trx_commit（Redo 刷盘策略 0/1/2）、sync_binlog、innodb_log_file_size。

InnoDB 逻辑结构与聚簇索引： 层级为 表(Table) → 索引(Index) → 段(Segment) → 区(Extent，连续多个页) → 页(Page，磁盘/内存管理基本单位)。聚簇索引 B+树：叶子节点存完整行数据，主键查询直达叶子；二级索引查不到的字段需按主键回聚簇索引再取一次——即回表。

常用诊断：

SELECT @@transaction_isolation;                     -- 隔离级别
SELECT * FROM information_schema.INNODB_TRX;        -- 当前事务
SELECT * FROM performance_schema.data_lock_waits;   -- 锁等待
SHOW INDEX FROM db1.innodb_test;
EXPLAIN SELECT id, name FROM db1.innodb_test WHERE id = 1;

排错：死锁 → SHOW ENGINE INNODB STATUS\G 看最近死锁信息，统一事务访问顺序；二级索引查询慢 → EXPLAIN 检查回表与扫描行数。

当日总结

• 权限保存在 mysql 系统库；账号 = 用户名 + 主机来源，两者必须完全匹配才算同一账号
• 授权范围五级：全局 *.* / 库 / 表 / 列 / 存储过程，对应 user/db/tables_priv/columns_priv/procs_priv 五张授权表
• GRANT 授予、REVOKE 回收；WITH GRANT OPTION 可转授权；MAX_USER_CONNECTIONS 及每小时配额做资源限制
• 业务账号遵循最小权限原则，不用 root 连库，不轻易给 ALL ON .
• 存储引擎决定表的存储/读取/加锁/事务实现；建表 ENGINE= 指定，ALTER TABLE … ENGINE= 转换（重建表，代价高）
• InnoDB 内存五大件：Buffer Pool / Change Buffer / 自适应哈希 / Log Buffer / 数据字典缓存
• 写路径：脏页 → Redo Log（WAL）→ 按配置刷盘 → 后台刷脏页；崩溃后靠 Redo 恢复已提交数据
• Undo Log 负责回滚与 MVCC；Doublewrite Buffer 防部分写损坏
• 逻辑单位层级：表→段→区→页→表空间；聚簇索引叶子存全行，二级索引需回表

相关页面

• MySQL 概念页 — 本课核心概念页（授权体系细化/存储引擎管理/InnoDB 架构）
• MySQL 数据库 5（Day53） — 上一课（子查询进阶/视图/触发器/存储过程/SQL 注入）
• MySQL 数据库 2（Day50） — 四引擎功能与文件结构对比（本课 InnoDB 深化的基础）
• MySQL 数据库 1（Day49） — 密码管理与 skip-grant-tables（授权表的实际应用）