MySQL 进阶学习文档

一、存储引擎

1.1 核心架构

• 四层架构：连接层 → 服务层 → 引擎层 → 存储层
• 插件式存储引擎：不同引擎独立管理数据存储，可动态选择

1.2 主流引擎对比

特性	InnoDB（默认）	MyISAM	Memory
事务支持	✅ 支持	❌ 不支持	❌ 不支持
锁粒度	行锁	表锁	表锁
外键支持	✅ 支持	❌ 不支持	❌ 不支持
存储位置	磁盘	磁盘	内存
适用场景	高并发事务	读多写少	临时数据缓存

选择建议：

• 优先选 InnoDB（支持事务和外键）
• 读多写少且无需事务选 MyISAM
• 临时数据用 Memory

二、索引优化

2.1 索引原理

• B + 树结构：
  • 叶子节点存储完整数据（聚集索引）
  • 非叶子节点仅存储索引值和指针
• Hash 索引：
  • 快速等值查询（WHERE name='xxx'）
  • 不支持范围查询（BETWEEN...）

2.2 索引类型

类型	说明	示例
主键索引	唯一标识记录（自动创建）	PRIMARY KEY (id)
唯一索引	保证值唯一	UNIQUE KEY (email)
组合索引	多字段联合索引	INDEX idx_name_age (name, age)
全文索引	文本关键词搜索	FULLTEXT INDEX (content)

索引类型	唯一性	字段类型	适用场景
主键索引	强制唯一	整数、UUID 等	表的唯一标识
唯一索引	唯一	邮箱、手机号等	确保字段值不重复
常规索引	非唯一	常用查询字段	加速WHERE、ORDER BY
全文索引	非唯一	文本类型	关键词搜索（如文章内容）

为什么需要不同索引？

• 主键索引：确保数据唯一性，加速数据定位。
• 唯一索引：约束业务规则（如邮箱不能重复）。
• 常规索引：提升查询效率，减少全表扫描。
• 全文索引：支持复杂文本搜索（如LIKE '%关键词%'的优化）

2.3 索引优化策略

1. 最左前缀原则：组合索引需按顺序使用最左字段

   -- 有效
   SELECT * FROM users WHERE name='Tom' AND age=20;
   -- 无效（跳过age）
   SELECT * FROM users WHERE name='Tom' AND gender='M';
   

2. 避免索引失效：

   • 不要在索引列上做运算（WHERE age+1=20）
   • 字符串不加引号（WHERE phone=13812345678）
   • 模糊查询前导 %（WHERE name LIKE '%Tom'）

3. 覆盖索引：查询字段全部包含在索引中

   CREATE INDEX idx_user_info ON users(name, age, email);
   SELECT name, age FROM users WHERE email='[e-mail]';
   

三、SQL 性能优化

3.1 执行计划分析

使用EXPLAIN分析查询性能：

EXPLAIN SELECT * FROM orders WHERE status=1;

关键指标解读：

• type: 连接类型（ref优于range优于all）
• key: 实际使用的索引
• rows: 预估扫描行数
• Extra: Using index表示覆盖索引

3.2 分页优化

问题：LIMIT 100000,10性能差
优化：

-- 基于覆盖索引
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders LIMIT 100000,1)
LIMIT 10;

3.3 锁优化

• 行锁：InnoDB 默认使用行锁（基于索引）
• 表锁：MyISAM 使用表锁，适合低并发场景
• 意向锁：InnoDB 通过意向锁减少锁冲突

四、事务与隔离级别

4.1 事务特性（ACID）

• 原子性：通过undo log实现回滚
• 一致性：事务前后数据状态一致
• 隔离性：通过锁和 MVCC 实现
• 持久性：通过redo log保证数据持久化

4.2 隔离级别对比

隔离级别	脏读	不可重复读	幻读	实现方式
Read Uncommitted	✅	✅	✅	无锁
Read Committed	❌	✅	✅	行锁
Repeatable Read	❌	❌	✅	MVCC + 行锁
Serializable	❌	❌	❌	全表锁

推荐：默认使用Repeatable Read，兼顾一致性和性能

五、高级功能

5.1 视图

作用：简化复杂查询，提高安全性

CREATE VIEW v_user_info AS
SELECT id, name, email FROM users WHERE status=1;

5.2 存储过程

示例：计算员工平均薪资

DELIMITER $$
CREATE PROCEDURE GetAvgSalary()
BEGINSELECT AVG(salary) AS avg_salary FROM employees;
END$$
DELIMITER ;

5.3 触发器

示例：记录用户操作日志

CREATE TRIGGER log_user_operation
AFTER UPDATE ON users
FOR EACH ROW
INSERT INTO user_logs(user_id, operation, timestamp)
VALUES(OLD.id, 'update', NOW());

六、InnoDB 引擎深度解析

6.1 核心组件

• Buffer Pool：缓存数据页和索引页
• Change Buffer：优化非唯一索引的写操作
• Redo Log：保证事务持久性
• Undo Log：支持回滚和 MVCC

6.2 MVCC 原理

• 版本链：通过DB_TRX_ID和DB_ROLL_PTR实现多版本控制
• ReadView：记录活跃事务 ID，决定可见性规则

七、管理工具

7.1 备份恢复

全库备份：

mysqldump -uroot -p --all-databases > full_backup.sql

恢复数据：

mysql -uroot -p < full_backup.sql

7.2 性能监控

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log';-- 查看索引使用情况
SELECT index_name, rows_read, rows_index_first 
FROM information_schema.table_statistics 
WHERE table_schema='your_db';