课程内容概览 模块 核心内容 01 索引功能 B+Tree原理、聚簇索引 vs 辅助索引、索引压测 02 日志管理 错误日志、通用日志、慢查询日志、二进制日志（重点） 03 备份恢复 日志截取恢复、闪回技术
01 数据库索引功能 1.1 什么是索引？ 索引是数据库中的目录结构 ，可以快速定位需要查询的数据，减少磁盘IO，提高查询效率 。
类比 ：书籍的目录 → 通过目录快速找到章节，无需翻遍全书
1.2 索引的核心算法：B+Tree 数据存储单位 层级 单位 大小 内容 行 Row 可变 一行数据 页 Page 16KB 多行数据的集合（InnoDB最小IO单位） 区 Extent 1MB（64个页） 连续存储空间 段 Segment 可变 多个区组成
B+Tree 三层结构 ┌─────────────────┐ │ 根节点（Root） │ ← 存储索引范围 + 指针 │ (1-20) (21-40) │ └────────┬────────┘ │ ┌──────────────┼──────────────┐ ▼ ▼ ▼ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ 支节点 │ │ 支节点 │ │ 支节点 │ ← 存储索引范围 + 指针 │(1-10指针)│ │(11-20指针)│ │(21-30指针)│ └────┬─────┘ └────┬─────┘ └────┬─────┘ │ │ │ ▼ ▼ ▼ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ 叶节点 │ │ 叶节点 │ │ 叶节点 │ ← 存储完整数据 或 索引+主键 │ 数据页 │◄──►│ 数据页 │◄──►│ 数据页 │ ← 双向链表（支持范围查询） └──────────┘ └──────────┘ └──────────┘B+Tree 核心特点 特点 说明 等值查询高效 任何数据的查询路径长度一致（树的高度） 范围查询高效 叶节点之间通过双向链表连接，无需回溯根节点 高扇出 每个节点可存储大量索引，树高度通常为3-4层 非叶节点不存数据 可容纳更多索引项，降低树高度
面试题 ：B+Tree 为什么比 B-Tree 更适合作为数据库索引？
B+Tree 非叶节点不存储数据，可存储更多索引，树更矮 B+Tree 叶节点有链表指针，范围查询性能更好 1.3 索引类型对比 索引类型 分类 叶节点存储内容 数量限制 特点 聚簇索引 主键索引 完整行数据 有且仅有1个 数据按主键顺序存储 辅助索引 普通索引 索引列 + 主键值 多个 需要回表查询完整数据 唯一索引 索引列 + 主键值 多个 索引列值不能重复 联合索引 多个列 + 主键值 多个 多列组合，遵循最左前缀原则
回表查询 使用辅助索引查询流程： 1. 在辅助索引B+Tree中找到符合条件的记录 → 获取主键值（id=5） 2. 根据主键值到聚簇索引中查找 → 获取完整行数据 SELECT * FROM t WHERE name='oldboy'; │ ▼ ┌─────────────────┐ ┌─────────────────┐ │ 辅助索引(name) │ → │ 聚簇索引(id) │ │ name→id │ │ id→完整数据 │ └─────────────────┘ └─────────────────┘联合索引最左前缀原则 -- 联合索引：idx_na_ag_ge (name, age, gender) -- ✅ 使用索引 WHERE name= 'oldboy' WHERE name= 'oldboy' AND age= 25 WHERE name= 'oldboy' AND age= 25 AND gender= 'M' -- ❌ 不使用索引（跳过了最左列） WHERE age= 25 WHERE gender= 'M' WHERE age= 25 AND gender= 'M' 1.4 索引管理SQL -- 主键索引（聚簇索引） create table t1( idint primary key , namevarchar ( 20 ) ) ; -- 建表时 alter table t1add primary key ( id) ; -- 建表后 alter table t1drop primary key ; -- 删除 -- 普通索引（辅助索引） create table t1( idint , namevarchar ( 20 ) , index idx_name( name) ) ; -- 建表时 alter table t1add index idx_name( name) ; -- 建表后 alter table t1drop index idx_name; -- 删除 -- 唯一索引（辅助索引） alter table t1add unique index idx_name( name) ; -- 联合索引（辅助索引） alter table t1add index idx_name_age( name, age) ; -- 查看索引 desc t1; -- Key列显示 PRI/MUL/UNI show index from t1; -- 详细信息 Key列标识 含义 PRI 主键索引（聚簇索引） MUL 普通索引/联合索引（辅助索引） UNI 唯一索引（辅助索引）
1.5 索引压测（mysqlslap） # 无索引压测 mysqlslap --defaults-file= /etc/my.cnf\ --concurrency = 100 \ --iterations = 1 \ --create-schema= 'oldboy' \ --query = "select * from oldboy.t100w where k2='VWlm'" \ --number-of-queries= 2000 \ -uroot -p123456 -h10.0.0.52 -verbose # 创建索引后重新压测对比 alter table t100wadd index idx_k2( k2) ; 参数 说明 --concurrency模拟并发数 --iterations测试重复次数 --number-of-queries总查询次数 --verbose显示详细结果（耗时等）
02 数据库日志管理 2.1 日志类型与作用 日志类型 默认状态 作用 应用场景 错误日志 ✅ 开启 记录启动/运行错误 故障排查 通用日志 ❌ 关闭 记录所有操作（审计） 安全审计 慢查询日志 ❌ 关闭 记录执行慢的SQL 性能优化 二进制日志 ✅ 开启 记录数据变更 数据恢复、主从复制
2.2 日志配置与管理 # 创建日志目录 mkdir -p /data/3306/log/&& chown -R mysql.mysql /data/3306/log/错误日志 # /etc/my.cnf [mysqld] log_error = /data/3306/log/mysql-error.logtail -f /data/3306/log/mysql-error.log# 实时查看 通用日志（审计日志） general_log = 1 # 开启通用日志 general_log_file = /data/3306/log/mysql-general.logset global general_log= 1 ; -- 动态开启（无需重启） 慢查询日志 slow_query_log = 1 slow_query_log_file = /data/3306/log/mysql-slow.log long_query_time = 2 # 超过2秒记录 log_queries_not_using_indexes = 1 # 记录未使用索引的查询# 分析慢查询日志 mysqldumpslow /data/3306/log/mysql-slow.log2.3 二进制日志（Binlog）——重点 配置开启 [mysqld] log_bin = /data/3306/log/mysql-bin # 前缀路径 binlog_format = ROW # ROW格式（默认） max_binlog_size = 256M # 单个文件最大256M binlog_expire_logs_seconds = 2592000 # 保留30天（秒）binlog格式对比 格式 记录方式 优点 缺点 生产推荐 ROW 记录行数据变更前后 数据一致性强 日志量大 ⭐ 推荐 STATEMENT 记录执行的SQL语句 日志量小 可能主从不一致 不推荐 MIXED 混合使用 两者兼顾 复杂 较少用
-- 查看当前格式 show variableslike 'binlog_format' ; -- ROW格式下查看DML mysqlbinlog--base64-output=decode-rows -vvv mysql-bin.000001 binlog查看方法 -- 方法1：SQL语句查看 show binary logs; -- 查看所有binlog文件 show masterstatus ; -- 查看当前正在写入的binlog show binlog eventsin 'mysql-bin.000002' ; -- 查看指定文件内容 # 方法2：mysqlbinlog命令查看 mysqlbinlog /data/3306/log/mysql-bin.000001# 解码ROW格式的DML语句 mysqlbinlog --base64-output= decode-rows-vvv /data/3306/log/mysql-bin.000001binlog管理操作 -- 切割日志（生成新文件） flush logs; -- 清理日志 purge binary logsto 'mysql-bin.000010' ; -- 删除到指定文件之前 purge binary logs before'2026-05-01 00:00:00' ; -- 删除指定时间之前 -- 临时关闭记录（慎用） set sql_log_bin= 0 ; -- 当前会话不记录binlog set sql_log_bin= 1 ; -- 恢复 2.4 binlog远程备份 # 在备份服务器上执行 mkdir -p /backup/binlog/10.0.0.52/cd /backup/binlog/10.0.0.52/ mysqlbinlog-R \ --host = 10.0 .0.52\ --user = root\ --password = 123456 \ --raw \ --stop-never\ mysql-bin.000003& 参数 说明 -R远程读取binlog --raw原始格式保存 --stop-never持续运行，实时同步 &后台运行
2.5 binlog数据恢复（日志截取） # 根据位置点截取 mysqlbinlog --start-position= 629 --stop-position= 1761 mysql-bin.000007> /backup/recover.sql# 根据时间点截取 mysqlbinlog --start-datetime= "2026-05-11 10:00:00" --stop-datetime= "2026-05-11 12:00:00" mysql-bin.000007> /backup/recover.sql# 恢复数据 mysql-uroot -p123456 < /backup/recover.sql2.6 binlog闪回（误操作修复） 场景 ：误执行DELETE FROM t1 WHERE id=4，需要恢复
# 1. 安装 binlog2sql git clone https://github.com/danfengcao/binlog2sql.gitcd binlog2sql pip3install -r requirements.txt# 2. 查看误操作 python3 binlog2sql.py\ -h 10.0 .0.52-P 3306 -uroot -p123456 \ -d xiaoX-t t1\ --start-file= 'mysql-bin.000008' \ --sql-type delete# 3. 生成反向SQL（delete → insert） python3 binlog2sql.py\ -h 10.0 .0.52-P 3306 -uroot -p123456 \ -d xiaoX-t t1\ --start-file= 'mysql-bin.000008' \ --sql-type delete-B > /backup/flashback.sql# 4. 执行恢复 mysql-uroot -p123456 < /backup/flashback.sqlbinlog2sql参数 说明 -d指定数据库 -t指定表 --start-file指定binlog文件 --sql-type过滤SQL类型（insert/update/delete） -B生成反向SQL（闪回）
