MySQL教程 / 第 330 节

第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注入、暴力破解）⭐⭐⭐⭐⭐

常见问题速查：

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

问题排查流程：

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

预防措施：

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

🎉 恭喜完成MySQL学习！

您已经掌握：

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

下一步建议：

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

推荐资源：

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

练习题

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

学习完成！ 🎊