MySQL技术知识点

一、慢查询诊断

• slow_query_log：记录执行时间超过指定阈值的SQL

• long_query_time：设置慢查询阈值，默认10秒

• 通过slow_log分析SQL性能瓶颈，找出需要优化的查询

二、SQL消耗阶段分析

• InnoDB聚簇索引：数据按主键顺序存储，二级索引包含主键值

• 排序原理：
• 优先队列：适用于limit查询
• 归并排序：适用于全量数据排序

• Join算法：
• Nested Loop：逐行扫描，适合小表
• Hash Join：构建哈希表，适合大表连接

三、DDL操作

• Copy：创建临时表，数据拷贝，适用于大幅度修改

• Inplace：原地修改，减少数据拷贝

• Instant：元数据修改，最快速的DDL操作

四、在线DDL

• 原理：通过触发器记录变更

• 过程：建立新表结构→数据迁移→增量同步→切换

• 优势：业务无感知，最小化停机时间

五、Binlog机制

• 格式：
• ROW：记录行变更
• STATEMENT：记录SQL语句
• MIXED：混合模式

• 主从复制：异步/半同步/同步复制

• 数据恢复：通过binlog时间点恢复

六、水平扩展

• 读写分离：主库写入，从库读取

• 分表策略：范围/哈希/时间/列表

• 并行复制：基于组提交的并行复制

• 分区表：物理分区，逻辑统一

七、水平分库

• 分库原则：业务解耦，负载均衡

• 路由策略：直连/中间件代理

• 分布式事务：XA/TCC等解决方案

八、MySQL线程管理

• 连接线程：每个连接一个线程

• 后台线程：刷新/清理/监控等

• 线程池：复用线程，减少开销

九、InnoDB线程

• Buffer Pool：内存缓冲池，减少磁盘IO

• Change Buffer：二级索引更新缓存

• 后台线程：
• Master线程：刷新脏页
• IO线程：异步IO处理
• Purge线程：清理旧版本

十、锁机制

• 锁类型：
• 共享锁(S)：读锁
• 排他锁(X)：写锁
• 意向锁(IS/IX)：表级锁

• Gap Lock：防止幻读

• 死锁检测：等待图检测

十一、性能优化

• 配置优化：内存/缓冲池/线程等

• 索引优化：覆盖索引/索引合并

• SQL优化：避免全表扫描/子查询优化

十二、MySQL规范

• 命名规范：小写+下划线

• 索引规范：合理使用索引

• 字段规范：合适的字段类型

• 安全规范：最小权限原则

十三、重要参数

• innodb_buffer_pool_size：缓冲池大小

• innodb_log_file_size：redo日志大小

• max_connections：最大连接数

• sync_binlog：binlog刷盘策略

十四、MVCC

• 实现原理：隐藏字段(DB_TRX_ID/DB_ROLL_PTR)

• 读取规则：
• 当前读：最新数据
• 快照读：历史版本

• 版本链：undo log维护历史版本

十五、常用命令

• EXPLAIN：查看执行计划

• SHOW PROCESSLIST：查看线程状态

• SHOW ENGINE INNODB STATUS：查看引擎状态

• mysqldump：逻辑备份工具

关键技术要点：

1. InnoDB是事务性存储引擎，支持ACID

2. Buffer Pool是性能优化核心

3. MVCC实现了事务隔离

4. 主从复制保证高可用

5. 分库分表实现扩展性

6. 锁机制保证并发安全

7. 参数配置影响整体性能

8. 规范化建设保证可维护性

资料

极客时间训练营-让优秀的人一起学习

极客时间训练营希望让优秀的人一起学习，我们的理念是让大家“学会并学有所成”，我们的品牌承诺是“老师好、课程好、服务好”。

https://u.geekbang.org/subject/mysql/1008017?utm_source=undefined&utm_medium=menu&utm_term=timewebmenu_h5

MySql实战45讲 | MySql实战45讲

林晓斌 网名丁奇，前腾讯云数据库负责人

https://jums.gitbook.io/mysql-shi-zhan-45-jiang