mysql基础学习

一、MySQL架构全景图
mermaid

graph TD
A[Client] --> B[Connector]
B --> C[Query Cache]
C --> D[Parser]
D --> E[Optimizer]
E --> F[Storage Engine]
F -->|InnoDB| G[Buffer Pool]
F -->|InnoDB| H[Redo Log]
F -->|InnoDB| I[Undo Log]
1.1 连接层（Connector）
线程模型：每个连接对应一个线程（Thread-Per-Connection）

连接池参数：

SHOW VARIABLES LIKE ‘thread%’;
– thread_cache_size: 线程缓存数量
– thread_pool_size: 线程池大小（企业版特有）
认证机制：SHA256密码加密（MySQL 8.0+）

二、SQL执行引擎核心原理
2.1 查询缓存（Query Cache）
淘汰机制：表级失效（任何DML操作都会使相关表缓存失效）

性能陷阱：

– 高并发写入场景建议关闭
SET GLOBAL query_cache_size = 0;
2.2 解析器（Parser）
词法分析：将SQL转换为token流

语法树生成：Bison语法解析器构建AST

2.3 优化器（Optimizer）
成本模型
– 查看优化器成本估算
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;
统计信息：mysql.innodb_index_stats存储索引基数

JOIN优化：BNL（Block Nested Loop）算法

索引选择陷阱

– 可能出现索引选择错误
ANALYZE TABLE users; – 重新收集统计信息
三、InnoDB存储引擎深度剖析
3.1 内存结构
Buffer Pool

– 关键参数
SHOW VARIABLES LIKE ‘innodb_buffer_pool%’;
– innodb_buffer_pool_size: 建议配置为物理内存的70-80%
– innodb_buffer_pool_instances: 多实例减少锁争用
LRU算法改进：

冷热数据分离（5/3分界）

防止全表扫描污染缓存

Change Buffer
对非唯一索引的DML操作缓存

合并时机：读取页到内存时

3.2 事务机制
ACID实现原理
特性 实现机制
原子性 Undo Log
隔离性 MVCC + Lock
持久性 Redo Log
一致性 前三个特性保证
隔离级别与锁

– 查看当前隔离级别
SELECT @@transaction_isolation;
MVCC核心字段：

DB_TRX_ID：事务ID

DB_ROLL_PTR：回滚指针

DB_ROW_ID：隐藏自增ID

3.3 物理存储结构
表空间管理

– 查看表空间文件
SHOW VARIABLES LIKE ‘innodb_data_file_path’;
段（Segment）：索引/数据段

区（Extent）：1MB（64个连续页）

页（Page）：16KB（默认）

行格式对比

– 查看行格式
SHOW TABLE STATUS LIKE ‘users’\G
Compact vs Dynamic：

Dynamic支持溢出页（off-page）存储大字段

四、日志系统工作原理
4.1 Redo Log（重做日志）
环形缓冲区结构：

SHOW VARIABLES LIKE ‘innodb_log%’;
– innodb_log_file_size: 单个日志文件大小（建议1-2GB）
– innodb_log_files_in_group: 日志组文件数（通常2）
刷盘策略：

innodb_flush_log_at_trx_commit=1（最安全）

4.2 Undo Log（回滚日志）
多版本链：构建ReadView的基础

** purge机制**：innodb_purge_threads控制清理线程

4.3 Binlog（归档日志）

– 查看binlog格式
SHOW VARIABLES LIKE ‘binlog_format’;
– ROW/STATEMENT/MIXED
两阶段提交：

mermaid
sequenceDiagram
participant TC as Transaction Coordinator
participant InnoDB
participant Binlog
TC->>InnoDB: prepare
TC->>Binlog: write
TC->>InnoDB: commit
五、性能调优实战要点
5.1 索引优化
B+树特性
3层B+树可存储约2000万行数据（假设主键8B，页16KB）

索引合并：

– 可能出现低效合并
SET optimizer_switch=‘index_merge=off’;
5.2 参数调优
sql
复制
– 关键内存参数
SET GLOBAL innodb_buffer_pool_size = 12G;
SET GLOBAL innodb_io_capacity = 2000; – SSD建议值
5.3 监控命令

– 查看锁等待
SELECT * FROM performance_schema.events_waits_current;
– 查看热点SQL
SELECT * FROM sys.statement_analysis LIMIT 10;
六、总结与最佳实践
设计原则：

所有表必须要有主键（建议自增INT/BIGINT）

避免SELECT *，只查询必要字段

参数基准：

innodb_buffer_pool_size = 总内存 * 0.7
innodb_io_capacity = 磁盘IOPS * 0.7
故障排查流程：

慢SQL → EXPLAIN分析 → 检查锁等待 → 查看引擎状态