jackssybin 的个人博客

  记录精彩的程序人生

首页 我的开源 我的scdn 教程中心 专题 网址导航 标签墙 存档 关于本站 友情链接 RSS

 第33章：疑难问题解决方案

MySQL教程目录

MySQL教程目录

入门导航

MySQL从新手到专家完整教程 MySQL从新手到专家完整学习路线图

基础入门

第01章：MySQL概述与安装 第02章：SQL基础 - DDL数据定义语言 第03章：SQL基础 - DML数据操作语言 第04章：SQL基础 - DQL数据查询语言 第05章：SQL进阶查询

进阶提升

第06章：索引原理与优化 ⭐⭐⭐⭐⭐ 第07章：存储引擎深入理解 第08章：事务与并发控制 ⭐⭐⭐⭐⭐ 第09章：锁机制 ⭐⭐⭐⭐⭐ 第10章：MySQL架构与执行流程 第11章：视图、存储过程与函数

高级特性

第12章：触发器与事件 第13章：分区表 第14章：字符集与排序规则

备份与恢复

第15章：MySQL日志系统 ⭐⭐⭐ 第16章：备份策略与实践 第17章：数据恢复实战

性能优化

第18章：SQL优化实战 ⭐⭐⭐⭐⭐ 第19章：索引优化进阶 第20章：MySQL服务器优化 第21章：硬件与操作系统优化

高可用架构

第22章：主从复制 ⭐⭐⭐⭐⭐ 第23章：读写分离 第24章：高可用方案 第25章：分库分表

监控与诊断

第26章：MySQL监控体系 第27章：性能诊断工具 第28章：故障排查实战

安全管理

第29章：用户与权限管理 第30章：MySQL安全加固

实战案例

第31章：企业级实战案例 第32章：MySQL面试题精选 第33章：疑难问题解决方案

第33章：疑难问题解决方案

MySQL教程 / 实战案例

第33章：疑难问题解决方案

常见问题的终极解决方案

33.1 性能问题

33.1.1 查询突然变慢

问题现象：

原本很快的查询突然变慢

排查步骤：

-- 1. 查看是否有锁等待
SHOW PROCESSLIST;

-- 2. 查看是否有慢查询
SELECT * FROM information_schema.PROCESSLIST WHERE TIME > 10;

-- 3. 查看表统计信息是否过期
SHOW TABLE STATUS LIKE 'users';

-- 4. 分析执行计划
EXPLAIN SELECT * FROM users WHERE age > 18;

解决方案：

-- 1. 更新统计信息
ANALYZE TABLE users;

-- 2. 重建索引
ALTER TABLE users DROP INDEX idx_age, ADD INDEX idx_age(age);

-- 3. 优化表
OPTIMIZE TABLE users;

-- 4. 检查是否需要添加索引
CREATE INDEX idx_age ON users(age);

---

33.1.2 Buffer Pool命中率低

问题现象：

Buffer Pool命中率<90%，性能差

排查：

-- 查看Buffer Pool命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 计算命中率
-- 命中率 = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

解决方案：

-- 增加Buffer Pool大小（50-70%内存）
SET GLOBAL innodb_buffer_pool_size = 8G;

-- 或修改配置文件
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

---

33.1.3 临时表过多

问题现象：

Created_tmp_disk_tables很高

排查：

-- 查看临时表统计
SHOW STATUS LIKE 'Created_tmp%';

-- Created_tmp_tables：内存临时表
-- Created_tmp_disk_tables：磁盘临时表（慢）

解决方案：

-- 1. 增加临时表大小
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

-- 2. 优化SQL，避免临时表
-- ❌ 使用DISTINCT
SELECT DISTINCT name FROM users;

-- ✅ 使用GROUP BY
SELECT name FROM users GROUP BY name;

-- 3. 添加索引
CREATE INDEX idx_name ON users(name);

---

33.2 数据问题

33.2.1 主键冲突

问题现象：

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

原因：

1. 手动插入了主键值
2. 自增值被重置
3. 主从复制导致

解决方案：

-- 1. 查看当前自增值
SHOW CREATE TABLE users;

-- 2. 修改自增值
ALTER TABLE users AUTO_INCREMENT = 1000;

-- 3. 查找最大ID
SELECT MAX(id) FROM users;

-- 4. 设置自增值为最大ID+1
ALTER TABLE users AUTO_INCREMENT = (SELECT MAX(id) + 1 FROM users);

---

33.2.2 数据不一致

问题现象：

主从数据不一致

排查：

# 使用pt-table-checksum检查
pt-table-checksum \
  --host=master_host \
  --user=root \
  --password=password \
  --databases=mydb

解决方案：

# 使用pt-table-sync同步
pt-table-sync \
  --execute \
  --sync-to-master \
  h=slave_host,D=mydb,t=users

---

33.2.3 误删除数据

问题现象：

DELETE语句误删除了数据

解决方案：

# 1. 立即停止应用，防止继续写入

# 2. 使用binlog恢复
# 找到误删除之前的binlog位置
mysqlbinlog --start-datetime="2024-11-10 09:00:00" \
  --stop-datetime="2024-11-10 09:59:59" \
  /var/lib/mysql/mysql-bin.000010 > recover.sql

# 3. 过滤掉DELETE语句
grep -v "DELETE FROM users" recover.sql > recover_filtered.sql

# 4. 恢复数据
mysql -u root -p mydb < recover_filtered.sql

# 5. 验证数据
SELECT COUNT(*) FROM users;

---

33.3 连接问题

33.3.1 连接数耗尽

问题现象：

ERROR 1040 (HY000): Too many connections

排查：

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看连接来源
SELECT USER, HOST, COUNT(*) 
FROM information_schema.PROCESSLIST 
GROUP BY USER, HOST;

解决方案：

-- 1. 临时增加最大连接数
SET GLOBAL max_connections = 1000;

-- 2. 杀死空闲连接
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.PROCESSLIST 
WHERE COMMAND = 'Sleep' AND TIME > 600;

-- 3. 设置超时时间
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

-- 4. 应用层使用连接池

---

33.3.2 连接超时

问题现象：

ERROR 2013 (HY000): Lost connection to MySQL server during query

原因：

1. 网络问题
2. 查询时间过长
3. max_allowed_packet太小

解决方案：

-- 1. 增加超时时间
SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

-- 2. 增加max_allowed_packet
SET GLOBAL max_allowed_packet = 64M;

-- 3. 优化慢查询

---

33.4 复制问题

33.4.1 主从延迟

问题现象：

Seconds_Behind_Master > 10

排查：

-- 查看主从状态
SHOW SLAVE STATUS\G

-- 关注字段：
-- Seconds_Behind_Master：延迟秒数
-- Slave_SQL_Running_State：SQL线程状态

解决方案：

-- 1. 开启并行复制（MySQL 5.7+）
STOP SLAVE;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;
START SLAVE;

-- 2. 升级从库硬件

-- 3. 拆分大事务

-- 4. 优化慢查询

---

33.4.2 复制中断

问题现象：

Slave_IO_Running: No 或 Slave_SQL_Running: No

排查：

SHOW SLAVE STATUS\G

-- 查看错误信息：
-- Last_IO_Error
-- Last_SQL_Error

解决方案：

-- 1. 跳过错误（谨慎使用）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 2. 重新搭建主从
# 主库备份
mysqldump -u root -p --single-transaction --master-data=2 --all-databases > backup.sql

# 从库导入
mysql -u root -p < backup.sql

# 配置复制
CHANGE MASTER TO ...;
START SLAVE;

---

33.5 磁盘问题

33.5.1 磁盘空间满

问题现象：

ERROR 3 (HY000): Error writing file (Errcode: 28 - No space left on device)

排查：

# 查看磁盘空间
df -h

# 查看MySQL数据目录大小
du -sh /var/lib/mysql/*

解决方案：

-- 1. 清理binlog
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 2. 清理慢查询日志
> /var/lib/mysql/slow.log

-- 3. 删除旧分区
ALTER TABLE orders DROP PARTITION p2021;

-- 4. 优化表
OPTIMIZE TABLE users;

---

33.5.2 I/O性能差

问题现象：

iostat显示I/O使用率100%

排查：

# 查看I/O
iostat -x 1

# 查看哪个进程占用I/O
iotop

解决方案：

-- 1. 优化慢查询

-- 2. 增加Buffer Pool
SET GLOBAL innodb_buffer_pool_size = 8G;

-- 3. 使用SSD

-- 4. 调整刷盘策略
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;

---

33.6 字符集问题

33.6.1 中文乱码

问题现象：

中文显示为???或乱码

排查：

-- 查看字符集
SHOW VARIABLES LIKE 'character%';

解决方案：

-- 1. 设置字符集
SET NAMES utf8mb4;

-- 2. 修改表字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 3. 修改配置文件
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

---

33.6.2 emoji无法存储

问题现象：

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F...'

原因：

使用utf8而不是utf8mb4

解决方案：

-- 转换为utf8mb4
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

---

33.7 安全问题

33.7.1 SQL注入

问题现象：

恶意SQL：SELECT * FROM users WHERE id = 1 OR 1=1

解决方案：

-- ✅ 使用预处理语句
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;

-- 应用程序中使用参数化查询
-- Java: PreparedStatement
-- Python: cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

---

33.7.2 暴力破解

问题现象：

大量登录失败

解决方案：

-- 1. 限制登录失败次数（MySQL 8.0.19+）
ALTER USER 'app_user'@'localhost' 
FAILED_LOGIN_ATTEMPTS 3 
PASSWORD_LOCK_TIME 1;

-- 2. 配置防火墙

-- 3. 使用fail2ban

---

33.8 本章总结

本章学习内容：

• ✅ 性能问题（查询慢、Buffer Pool、临时表）⭐⭐⭐⭐⭐
• ✅ 数据问题（主键冲突、数据不一致、误删除）⭐⭐⭐⭐⭐
• ✅ 连接问题（连接数耗尽、连接超时）⭐⭐⭐⭐⭐
• ✅ 复制问题（主从延迟、复制中断）⭐⭐⭐⭐⭐
• ✅ 磁盘问题（空间满、I/O差）⭐⭐⭐⭐⭐
• ✅ 字符集问题（乱码、emoji）⭐⭐⭐⭐⭐
• ✅ 安全问题（SQL注入、暴力破解）⭐⭐⭐⭐⭐

常见问题速查：

1. 查询慢 → EXPLAIN分析 → 添加索引
2. 连接数满 → 增加max_connections → 使用连接池
3. 主从延迟 → 并行复制 → 优化慢查询
4. 磁盘满 → 清理binlog → 删除旧分区
5. 中文乱码 → SET NAMES utf8mb4
6. emoji无法存储 → 转换为utf8mb4
7. SQL注入 → 使用预处理语句

问题排查流程：

1. 确认问题现象
2. 查看日志和监控
3. 分析原因
4. 制定解决方案
5. 验证效果
6. 总结预防措施

预防措施：

• 定期备份
• 监控告警
• 性能测试
• 代码审查
• 安全加固

---

🎉 恭喜完成MySQL学习！

您已经掌握：

• ✅ MySQL基础知识
• ✅ SQL语句编写
• ✅ 索引原理与优化
• ✅ 事务与锁机制
• ✅ 性能优化
• ✅ 高可用架构
• ✅ 监控与诊断
• ✅ 安全管理
• ✅ 实战案例

下一步建议：

1. 实践项目：将所学应用到实际项目
2. 深入源码：阅读MySQL源码
3. 持续学习：关注MySQL新特性
4. 分享交流：写博客、参与社区

推荐资源：

• 官方文档：https://dev.mysql.com/doc/
• MySQL技术内幕：InnoDB存储引擎
• 高性能MySQL
• MySQL实战45讲

---

练习题

1. 查询突然变慢如何排查？
2. 如何解决连接数耗尽？
3. 如何处理主从延迟？
4. 如何恢复误删除的数据？
5. 如何解决中文乱码？
6. 如何防止SQL注入？
7. 磁盘空间满如何处理？
8. 如何优化I/O性能？
9. 如何处理主键冲突？
10. 实战：排查并解决一个性能问题

学习完成！ 🎊

上一篇：第32章：MySQL面试题精选

专题

Java 与 JVM Spring Boot 与后端框架 MySQL 与数据架构 Linux 运维与部署 Python 爬虫与自动化 中间件与分布式 工具、效率与博客建设

标签

Python Java CentOS MySQL Python 爬虫 JVM Linux Scrapy 垃圾回收算法 Spring Boot 大表优化 调优 高并发 冷热分离 千万级表优化 线程 Nginx Spring Batch 待分类 内存溢出

教程中心

MySQL 35 篇教程 Spring Boot 4 22 篇教程 Netty 16 篇教程 查看全部教程 »

GitHub

104 文章
3 友链

© 2026 jackssybin 的个人博客
Powered by Bolo ♥ Theme bolo-9IPHP ^[ref] by Vanessa