前言

忘记MySQL root密码是常见问题，不用慌，本文教你一步步重置。

重置步骤

第一步：停止MySQL服务

systemctl stop mysqld

第二步：以跳过权限模式启动

mysqld_safe --skip-grant-tables &

或者用这个方式：

systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
systemctl start mysqld

第三步：无密码登录MySQL

mysql -u root

现在不需要密码就能直接进了。

第四步：修改密码

-- 刷新权限
FLUSH PRIVILEGES;

-- 修改密码（MySQL 5.7）
ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';

-- 如果上面不行，用这个
UPDATE mysql.user SET authentication_string=PASSWORD('你的新密码') WHERE User='root' AND Host='localhost';

-- 刷新权限
FLUSH PRIVILEGES;

第五步：退出，正常重启MySQL

exit
systemctl stop mysqld
systemctl start mysqld

第六步：用新密码登录测试

mysql -u root -p

输入新密码，能进去就成功了。

常见坑

坑1：MySQL 8.0和5.7语法不一样

MySQL 8.0 不能用 PASSWORD() 函数了。

解决： 用 ALTER USER 语句。

坑2：skip-grant-tables没生效

启动后还是要密码。

原因： 可能MySQL已经在跑了，新启动的没生效。

解决： 先彻底停掉MySQL：

systemctl stop mysqld
pkill -9 mysqld

再重新启动。

坑3：改完密码忘记FLUSH PRIVILEGES

改完密码直接退出，结果还是不能用新密码登录。

解决： 改完密码一定要执行 FLUSH PRIVILEGES;

坑4：远程登录还是不行

本地改完密码，远程连接还是连不上。

原因： root用户只允许本地登录。

解决： 需要单独授权远程登录：

CREATE USER 'root'@'%' IDENTIFIED BY '密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

总结

六步重置root密码：

1. 停MySQL
2. 跳过权限启动
3. 无密码登录
4. 改密码
5. 正常重启
6. 测试登录

记住： 改完密码一定要 FLUSH PRIVILEGES;

遇到问题加QQ23979811 协助处理

MySQL 事务隔离级别

事务隔离是数据库的重要概念，搞懂4种级别很有必要。

1. 事务的ACID

• 原子性（Atomicity）： 要么全成功，要么全失败
• 一致性（Consistency）： 事务前后数据一致
• 隔离性（Isolation）： 多个事务互不影响
• 持久性（Durability）： 提交后永久保存

2. 4种隔离级别

1）读未提交（Read Uncommitted）

• 能读到未提交的数据
• 有脏读、不可重复读、幻读问题
• 性能最好

2）读提交（Read Committed）

• 只能读到已提交的数据
• 解决脏读
• 有不可重复读、幻读问题
• Oracle默认级别

3）可重复读（Repeatable Read）

• 同一个事务里读多次结果一样
• 解决脏读、不可重复读
• 有幻读问题
• MySQL默认级别

4）串行化（Serializable）

• 事务排队执行
• 解决所有问题
• 性能最差

3. 三种问题

脏读：

• 读到另一个事务未提交的数据
• 对方回滚了，你读的就是脏数据

不可重复读：

• 同一个事务里，两次读同一行结果不一样
• 中间被其他事务修改了

幻读：

• 同一个事务里，两次查询行数不一样
• 中间被其他事务插入了

4. 查看和设置

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

5. 实际使用

• 一般用默认的可重复读就够了
• 对一致性要求高的用串行化
• 不要随便改隔离级别

总结

MySQL事务隔离级别要点：

• 读未提交：性能最好，问题最多
• 读提交：解决脏读
• 可重复读：MySQL默认，解决不可重复读
• 串行化：最安全，性能最差
• 理解脏读、不可重复读、幻读

做数据库开发，事务隔离一定要懂。

MySQL 慢查询优化实战

数据库慢查询是性能瓶颈的常见原因，学会优化很重要。

1. 开启慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询
SET GLOBAL slow_query_log = ON;

-- 设置慢查询时间（秒）
SET GLOBAL long_query_time = 1;

2. 分析慢查询

用 EXPLAIN 分析：

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看：

• type：访问类型，ALL 是全表扫描，要优化
• key：用了哪个索引
• rows：扫描行数

3. 常见优化方法

1）加索引：

-- 加索引
ALTER TABLE users ADD INDEX idx_name(name);

*2）避免 SELECT ：**

-- 不好
SELECT * FROM users;

-- 好
SELECT id, name FROM users;

3）避免在索引字段上做运算：

-- 不好
SELECT * FROM users WHERE YEAR(create_time) = 2026;

-- 好
SELECT * FROM users WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';

4）避免 LIKE 左模糊：

-- 不好（用不到索引）
SELECT * FROM users WHERE name LIKE '%张%';

-- 好
SELECT * FROM users WHERE name LIKE '张%';

4. 分页优化

-- 不好（大偏移量慢）
SELECT * FROM users LIMIT 100000, 10;

-- 好（用子查询）
SELECT * FROM users WHERE id >= (
    SELECT id FROM users LIMIT 100000, 1
) LIMIT 10;

5. 常见慢查询原因

• 没加索引
• 索引失效
• 关联查询太多
• 数据量太大
• 大表分页

总结

MySQL 慢查询优化要点：

• 开启慢查询日志
• 用 EXPLAIN 分析
• 合理加索引
• 避免全表扫描
• 优化分页查询

数据库优化是个细活，多分析多测试。

MySQL 备份恢复实战

数据库备份是运维的基本功，数据丢了就找不回来了。

1. 备份方式

1）物理备份：

• 直接拷贝数据文件
• 速度快，适合大数据库

2）逻辑备份：

• 导出 SQL 语句
• 体积小，跨平台

2. mysqldump 备份

备份整个数据库：

mysqldump -u root -p 数据库名 > backup.sql

备份单个表：

mysqldump -u root -p 数据库名 表名 > table.sql

备份所有数据库：

mysqldump -u root -p --all-databases > all.sql

3. 恢复数据

mysql -u root -p 数据库名 < backup.sql

注意： 恢复前要先创建数据库。

4. 压缩备份

# 备份并压缩
mysqldump -u root -p 数据库名 | gzip > backup.sql.gz

# 恢复压缩文件
gunzip < backup.sql.gz | mysql -u root -p 数据库名

5. 定时备份脚本

#!/bin/bash
# 数据库备份脚本

DB_USER="root"
DB_PASS="123456"
DB_NAME="test"
BACKUP_DIR="/data/backup"
DATE=$(date +%Y%m%d)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 备份
mysqldump -u$DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/$DB_NAME_$DATE.sql.gz

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

加 crontab 定时执行：

# 每天凌晨3点备份
0 3 * * * /bin/bash /root/backup.sh

6. 常见问题

1）备份太大：

• 用 --quick 参数
• 压缩备份

2）备份报错：

• 检查用户名密码
• 检查权限

3）恢复失败：

• 检查 SQL 文件是否完整
• 检查数据库字符集

7. 最佳实践

• 每天定时备份
• 备份文件存到异地
• 定期测试恢复
• 重要数据做双备份

总结

MySQL 备份恢复要点：

• mysqldump 导出 SQL
• gzip 压缩减小体积
• crontab 定时执行
• 定期测试恢复

做运维，数据备份永远是第一位的。

明明建了索引，查询还是很慢？十有八九是索引失效了。MySQL 索引失效是开发中最常见的坑，下面整理了最容易导致索引失效的场景，写SQL的时候一定要避开。

1. 对索引字段做函数操作

-- 索引失效
SELECT * FROM user WHERE DATE(create_time) = '2026-10-01';

-- 正确写法，走索引
SELECT * FROM user WHERE create_time >= '2026-10-01' AND create_time < '2026-10-02';

对索引字段用了 DATE()、YEAR()、SUBSTRING() 等函数，索引就失效了。

2. 隐式类型转换

-- 假设 phone 是 varchar 类型
-- 索引失效（数字和字符串比较，MySQL自动转换）
SELECT * FROM user WHERE phone = 13800138000;

-- 正确写法，走索引
SELECT * FROM user WHERE phone = '13800138000';

字符串类型的字段，查询条件一定要加引号。

3. 联合索引不满足最左匹配原则

-- 联合索引：(a, b, c)
-- 走索引
SELECT * FROM t WHERE a = 1 AND b = 2;

-- 不走索引（跳过了a）
SELECT * FROM t WHERE b = 2 AND c = 3;

-- 走部分索引（a走，b和c不走）
SELECT * FROM t WHERE a = 1 AND c = 3;

联合索引必须从最左边的列开始用，不能跳过。

4. 使用了 != 或 <>

-- 索引大概率失效
SELECT * FROM user WHERE status != 1;

-- 可以改成范围查询
SELECT * FROM user WHERE status < 1 OR status > 1;

不等于操作通常会导致全表扫描，因为要返回的结果太多了。

5. LIKE 以 % 开头

-- 索引失效
SELECT * FROM user WHERE name LIKE '%张';

-- 走索引
SELECT * FROM user WHERE name LIKE '张%';

模糊查询左边带 %，索引就用不上了。

6. OR 连接的条件有一个没索引

-- name 有索引，age 没索引 → 整个SQL不走索引
SELECT * FROM user WHERE name = '张三' OR age = 20;

OR 两边只要有一个字段没索引，整个查询就不走索引。

7. 用 explain 验证索引是否生效

EXPLAIN SELECT * FROM user WHERE name = '张三';

看 key 列：

• 显示索引名 → 走索引了
• 显示 NULL → 没走索引

看 type 列：

• ref、eq_ref → 正常
• ALL → 全表扫描，索引失效了

常见坑总结

1. 不要在索引字段上做运算：WHERE age + 1 = 20 → 改成 WHERE age = 19
2. 不要用 NOT IN / NOT EXISTS：大数据量下很慢
3. **不要用 SELECT ***：只查需要的字段，覆盖索引更快
4. 数据量小的表不要建索引：全表扫描可能比索引还快

遇到问题加QQ23979811 协助处理

前言

网站越来越慢，不知道哪条 SQL 拖后腿。MySQL 自带慢查询日志功能，能把执行慢的 SQL 记录下来，方便优化。

什么是慢查询

就是执行时间超过某个阈值的 SQL 语句。默认阈值是 10 秒，太久了，一般改成 1 秒或者 2 秒。

临时开启（重启不丢）

登录 MySQL：

mysql -u root -p

开启慢查询：

-- 开启慢查询
SET GLOBAL slow_query_log = ON;

-- 设置超过1秒就记录
SET GLOBAL long_query_time = 1;

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

永久开启（改配置文件）

改 my.cnf：

vi /etc/my.cnf

加上：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

重启 MySQL：

systemctl restart mysqld

慢查询日志长什么样

# Time: 2026-10-07T07:00:00.000000Z
# User@Host: root[root] @ localhost []  Id:  1234
# Query_time: 5.234567  Lock_time: 0.000123 Rows_sent: 1000  Rows_examined: 1000000
SET timestamp=1728270000;
SELECT * FROM users WHERE name LIKE '%张%';

重点看：

• Query_time：执行时间
• Rows_examined：扫描了多少行
• 最后一行就是慢 SQL 语句

分析慢查询日志

用 mysqldumpslow 工具：

# 找出最慢的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 找出返回数据最多的10条
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

参数说明：

常见坑

坑1：没开启慢查询

不知道怎么开，或者开了忘了。

解决： 用 SHOW VARIABLES LIKE 'slow_query_log'; 确认一下。

坑2：阈值设太大

默认是 10 秒，10 秒以下的慢 SQL 都没记录到。

解决： 改成 1 秒，够用了。

坑3：日志太大占磁盘

慢查询日志一直写，磁盘满了。

解决： 配 logrotate 定期切割。

坑4：没记录没走索引的SQL

有些 SQL 虽然快，但没走索引，数据量一大就慢了。

解决： 加上这个参数，没走索引的也记录：

log_queries_not_using_indexes = 1

总结

三步开启慢查询：

1. 开 slow_query_log
2. 设 long_query_time
3. 用 mysqldumpslow 分析

记住： 慢查询日志是优化数据库的第一步，先开了再说。

遇到问题加QQ23979811 协助处理

MySQL 性能优化实战

数据库慢是网站性能的大问题，这篇讲实战优化方法，从几个方面下手。

1. 慢查询排查

先找到慢SQL，再优化。

开启慢查询日志：

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log%';

-- 开启慢查询
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;

用 explain 分析：

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看：

• type：有没有用到索引（ref/const 好，ALL 全表扫描坏）
• key：实际用的索引
• rows：扫描行数

2. 索引优化

建索引原则：

• where 条件经常用的字段建索引
• 联合索引最左前缀原则
• 避免建太多索引，影响写入

查看索引：

SHOW INDEX FROM users;

建索引：

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

3. SQL 优化

**不要 SELECT ***

-- 坏
SELECT * FROM users WHERE id = 1;

-- 好
SELECT id, name, email FROM users WHERE id = 1;

避免 LIKE 前导模糊：

-- 坏（用不到索引）
SELECT * FROM users WHERE name LIKE '%张%';

-- 好（能用索引）
SELECT * FROM users WHERE name LIKE '张%';

大表分页优化：

-- 坏（深度分页慢）
SELECT * FROM users LIMIT 100000, 10;

-- 好（子查询先定位）
SELECT * FROM users 
WHERE id >= (SELECT id FROM users LIMIT 100000, 1) 
LIMIT 10;

4. 表结构优化

• 用合适的数据类型（能用 int 就别用 bigint）
• 字段尽量 NOT NULL
• 大表分表分库

5. 配置优化

my.cnf 常用配置：

# 缓冲池大小（内存的 70%）
innodb_buffer_pool_size = 1G

# 连接数
max_connections = 500

# 慢查询日志
slow_query_log = 1
long_query_time = 1

6. 总结

MySQL 优化三步走：

1. 找慢SQL： 慢查询日志 + explain
2. 加索引： where 条件字段
3. 优化SQL： 不写 SELECT *，避免全表扫描

数据库优化不是一蹴而就，要持续监控和调整。

前言

做SQL优化的同学肯定听说过覆盖索引，很多文章里都说用覆盖索引能提升查询性能。但是很多新手同学搞不懂覆盖索引到底是什么，为什么能提升性能。

这篇文章就把覆盖索引的知识点从头到尾讲清楚：什么是覆盖索引？为什么能提升性能？怎么判断是不是用了覆盖索引？怎么利用覆盖索引优化查询？看完之后你就彻底搞懂了。

一、什么是覆盖索引

先说说回表是什么。

MySQL的InnoDB引擎，主键索引是聚簇索引，叶子节点直接存整行数据。普通索引的叶子节点存的是主键值。

如果你用普通索引查询，查到主键值之后，还要再去主键索引里查一遍，拿到完整的行数据。这个过程就叫回表。

那什么是覆盖索引呢？就是：你要查询的字段，在索引里就都有了，不用再回表去主键索引里查了。这就叫覆盖索引，或者叫索引覆盖。

简单来说：查询需要的所有数据，索引里都有，不用回表，就是覆盖索引。

二、为什么覆盖索引能提升性能

为什么覆盖索引能提升性能？因为少了回表这一步。

我们对比一下：

没有覆盖索引的情况

1. 先在普通索引里查，找到主键值
2. 再拿着主键值去主键索引里查，拿到完整的行数据

这样要查两棵B+树，查了两次，当然慢。

有覆盖索引的情况

1. 直接在索引里查到所有需要的数据，就完事了

这样只查了一棵B+树，少了回表这一步，当然快。

尤其是表很大的时候，回表要做随机IO，性能很差。如果不用回表，直接在索引里就能拿到数据，那性能提升就非常明显了。

三、怎么判断是不是覆盖索引

用explain看执行计划的时候，如果Extra列里显示 Using index，就说明用了覆盖索引，不用回表。

比如：

+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ref   | name          | name | 767     | const|  10  | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

看到Extra里的Using index了吗？这就说明用了覆盖索引。

四、怎么利用覆盖索引优化查询

知道了覆盖索引的好处，那怎么利用它来优化查询呢？

1. 查询的时候只查需要的字段，不要SELECT *

很多同学写SQL的时候喜欢写SELECT *，把所有字段都查出来。这样你要查的字段太多了，索引里不可能全都有，就必须回表。

如果你只查需要的几个字段，那就有可能把这几个字段都加到索引里，这样就能用到覆盖索引了。

比如：

-- 不好的写法
SELECT * FROM users WHERE name = '张三';

-- 好的写法
SELECT id, name, age FROM users WHERE name = '张三';

2. 建联合索引，把经常查询的字段都加进去

如果你经常根据name查询，并且经常查name和age，那就建一个(name, age)的联合索引。

这样当你查SELECT name, age FROM users WHERE name = '张三'的时候，索引里就有name和age了，不用回表，直接就能拿到数据。

3. 把常用的查询字段加到索引里

除了WHERE条件的字段，SELECT里的字段也可以加到索引里，这样就能实现覆盖索引。

比如你经常查SELECT id, name, age FROM users WHERE name = ?，那就建一个(name, age)的联合索引，因为id是主键，InnoDB的普通索引叶子节点本来就存主键值，所以索引里就有id、name、age三个字段了，正好覆盖你的查询。

五、覆盖索引的注意点

1. 不是所有查询都能用覆盖索引

只有你要查询的字段都在索引里的时候，才能用覆盖索引。如果有字段不在索引里，那就必须回表。

2. 覆盖索引也不是万能的

不要为了覆盖索引，把所有字段都加到索引里。索引太多了，会影响插入和更新的性能。要权衡一下，哪些查询是高频的，值得为它建覆盖索引。

3. 主键索引天然就是覆盖索引

如果你是用主键查询，那本来就是在主键索引里查，直接就能拿到所有数据，天然就是覆盖索引。

常见坑

坑1：以为只要用了索引就是覆盖索引

很多同学以为只要查询用到了索引，就是覆盖索引。其实不是，用到索引只是说用索引找到了主键值，如果你还要查其他字段，还是要回表。只有Extra里显示Using index，才是真的覆盖索引。

坑2：写SELECT *，用不了覆盖索引

很多同学写SQL的时候喜欢SELECT *，结果要查的字段太多了，索引里根本放不下，就用不了覆盖索引。记住：只查需要的字段，别查所有字段。

坑3：为了覆盖索引，索引建得太多

很多同学为了让某个查询用上覆盖索引，把那个查询的所有字段都加到索引里。结果一个表建了好几十个索引，插入更新的时候要维护这么多索引，性能很差。覆盖索引是好，但是不要滥用，要权衡。

坑4：不知道Using index就是覆盖索引

很多同学看explain的时候，看到Extra里的Using index不知道是什么意思。记住：Using index就是覆盖索引，不用回表，性能很好。

坑5：联合索引的顺序不对

很多同学建联合索引的时候，顺序建反了，结果用不了覆盖索引。记住：联合索引要满足最左前缀原则，WHERE条件里的字段要放在前面，SELECT的字段放在后面。

总结

MySQL覆盖索引的核心知识点：

1. 什么是覆盖索引：查询需要的所有数据，索引里都有，不用回表
2. 为什么能提升性能：少了回表这一步，不用查两棵B+树，减少IO
3. 怎么判断：explain的Extra列里显示Using index，就是覆盖索引
4. 怎么利用：
   • 不要写SELECT *，只查需要的字段
   • 建联合索引，把WHERE和SELECT的字段都加进去
5. 注意点：不要为了覆盖索引建太多索引，要权衡性能

记住：覆盖索引是SQL优化里很重要的一个手段，很多时候只要把SELECT *改成只查需要的字段，再建个合适的联合索引，性能就能提升好几倍。

遇到问题加QQ23979811 协助处理

前言

做后端开发的同学，建表的时候都喜欢用自增主键，觉得简单方便。但是很多同学不知道，自增主键是有最大值的，用完了就不能再插入数据了。

很多新手同学建表的时候，主键直接用INT，也不知道INT的最大值是多少。结果项目跑了几年，数据量涨上来了，自增主键快用完了，才发现要出问题了。

这篇文章就把MySQL自增主键的知识点讲清楚：不同整型的最大值是多少？用完了会怎么样？怎么避免？已经用完了怎么办？看完之后你就彻底搞懂了。

一、不同整型的最大值

MySQL的整型类型有好几种，每种的最大值都不一样。我们常用的有这几个：

重点看INT和BIGINT

我们建表的时候，主键一般用INT或者BIGINT。

INT的最大值

INT有符号的最大值是 21亿多（2147483647），无符号的是 42亿多（4294967295）。

很多同学觉得21亿很多，肯定用不完。但是对于一些业务量大的网站，比如电商、社交，用户量很大，一张表可能就有几亿条数据，INT真的不够用。

BIGINT的最大值

BIGINT有符号的最大值是 922亿亿（9223372036854775807），这个数太大了，基本上一辈子都用不完。

所以建议：主键直接用BIGINT，不要用INT，省得以后不够用了还要改。

二、自增主键用完了会怎么样

如果自增主键用到了最大值，再插入数据会怎么样？

MySQL会报错：

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

或者：

ERROR: Duplicate entry '2147483647' for key 'PRIMARY'

意思就是：自增主键已经到最大值了，不能再插入新数据了。

这时候业务就受影响了，不能插入新数据了，必须赶紧处理。

三、怎么避免自增主键用完

最好的方法就是：建表的时候主键直接用BIGINT，不要用INT。

BIGINT的最大值太大了，基本上不可能用完。这样就不用以后担心主键不够用的问题了。

很多同学觉得BIGINT占空间大，其实BIGINT是8字节，INT是4字节，一张表也就多了几个字节，根本不算什么。但是换来的是永远不用担心主键不够用，这太值了。

怎么查看当前自增值到多少了

想知道当前自增主键已经用到多少了，可以用这个命令：

SHOW TABLE STATUS LIKE '表名';

执行完之后，看Auto_increment那列，就是当前自增值。

然后你对比一下这个类型的最大值，看看还有多少余量。如果已经用了一半了，那就得注意了。

四、已经用完了怎么办

如果你的主键已经快用完了，或者已经用完了，怎么办？

方法1：把INT改成BIGINT

这是最直接的方法。把主键的类型从INT改成BIGINT，这样最大值就变成922亿亿了，够用很久了。

执行的SQL：

ALTER TABLE 表名 MODIFY id BIGINT AUTO_INCREMENT;

注意：这个操作会重建表，如果表很大的话，会花很长时间，而且会锁表。最好在业务低峰期执行，或者用pt-online-schema-change这种工具在线改。

方法2：分库分表

如果你的表已经很大了，改主键类型也不能根本解决问题，那就分库分表，把数据分散到多个表或者多个数据库里。

不过分库分表成本很高，一般都是到万不得已才做。

五、自增主键的其他坑

除了用完的问题，自增主键还有一些其他的坑：

坑1：删除数据之后自增值不回收

很多同学以为删除了数据，自增值会重新开始。其实不是，自增值只会涨，不会降。就算你把表清空了，自增值还是接着之前的继续涨。

比如你插入了100条数据，id到了100，然后你把这100条都删了，再插入一条新数据，id还是101，不是1。

坑2：自增值不连续

因为有事务回滚的情况，自增值可能会不连续。比如一个事务插入了一条数据，id是100，然后事务回滚了，那100这个id就浪费了，下一条数据的id是101。

所以不要指望自增主键是连续的，它只是唯一的，不是连续的。

坑3：用了INT UNSIGNED，觉得够大了

很多同学觉得用了INT UNSIGNED，最大值是42亿，肯定够了。其实对于一些大业务，42亿也不够。还是用BIGINT最保险。

常见坑

坑1：建表的时候用INT主键，后来不够用了

很多同学建表的时候图简单，直接用INT主键，结果项目跑了几年，数据量涨上来了，INT快用完了，才慌慌张张要改成BIGINT。这时候表已经很大了，改起来很麻烦。一开始就用BIGINT，省得以后麻烦。

坑2：不知道自增主键的最大值

很多新手同学根本不知道INT的最大值是多少，觉得反正自增，肯定用不完。其实21亿听起来很多，但是对于大业务来说，真的可能用完。

坑3：改主键类型的时候锁表了

很多同学直接在线上执行ALTER TABLE改主键类型，结果表很大，改了几个小时，一直锁表，业务直接卡住了。改之前一定要评估表大小，最好在业务低峰期执行，或者用在线改表工具。

坑4：以为删除数据之后自增值会降

很多同学删了很多数据，以为自增值会降下来，就不用改类型了。其实不是，自增值只会涨，不会降。删了数据之后，自增值还是原来的，不会变。

坑5：用UUID做主键

很多同学觉得自增主键不够用，就想用UUID做主键。其实UUID做主键问题更多：占空间大、无序导致索引分裂、查询慢。真的不够用的话，改BIGINT就行，没必要用UUID。

总结

MySQL自增主键的核心知识点：

1. 不同整型的最大值：INT是21亿（有符号）/42亿（无符号），BIGINT是922亿亿
2. 主键直接用BIGINT：不要用INT，省得以后不够用了还要改
3. 自增主键用完会报错：不能再插入新数据了
4. 怎么查看当前自增值：SHOW TABLE STATUS，看Auto_increment列
5. 用完了怎么办：把INT改成BIGINT，或者分库分表
6. 自增主键的坑：删除数据自增值不回收、自增值不连续

记住：建表的时候主键直接用BIGINT，这是最稳妥的做法。不要一开始图省事用INT，以后改起来更麻烦。

遇到问题加QQ23979811 协助处理

前言

很多新手同学刚学MySQL的时候，根本不知道存储引擎是什么，建表的时候直接用默认的。其实存储引擎很重要，不同的存储引擎有不同的特点，适合不同的场景。

MySQL最常用的两个存储引擎就是InnoDB和MyISAM。很多同学搞不清楚它们有什么区别，也不知道应该选哪个。这篇文章就把这两个引擎的区别讲清楚，看完之后你就知道怎么选了。

一、什么是存储引擎

存储引擎就是MySQL里，真正存储数据、管理数据的东西。MySQL是把数据和引擎分开的，不同的表可以用不同的存储引擎。

就像你有一个仓库，你可以选择用货架放东西，也可以用箱子放东西，不同的存放方式有不同的特点。存储引擎就是这个"存放方式"。

二、InnoDB和MyISAM的核心区别

我们先看一个对比表，一目了然：

三、InnoDB的特点

InnoDB是MySQL默认的存储引擎，从MySQL 5.5开始，默认就是InnoDB了。

1. 支持事务

这是InnoDB最大的特点。支持ACID事务，也就是原子性、一致性、隔离性、持久性。如果你需要事务，那就必须用InnoDB。

2. 支持行锁

InnoDB的锁粒度是行锁。也就是说，一个事务锁了某一行，其他事务还可以操作表里的其他行。这样并发性能就很好。

3. 支持外键

InnoDB支持外键约束，可以保证数据的一致性。

4. 支持崩溃恢复

InnoDB有redo log，MySQL崩溃之后重启，能够自动恢复数据，不容易丢数据。

5. 聚簇索引

InnoDB的主键索引是聚簇索引，叶子节点直接存数据。这样按主键查询速度很快。

InnoDB适合什么场景

• 需要事务的场景
• 读写都比较频繁的场景
• 对数据一致性要求高的场景
• 大部分互联网业务场景

四、MyISAM的特点

MyISAM是MySQL早期默认的存储引擎，现在用的越来越少了。

1. 不支持事务

MyISAM不支持事务，执行SQL就是直接执行，没有事务的概念。

2. 表锁

MyISAM的锁粒度是表锁。也就是说，一个操作锁了整张表，其他操作都要等。这样并发性能就很差。

3. 读性能好

MyISAM的读性能很好，因为它的结构简单，查询的时候直接读文件就行。

4. 不支持崩溃恢复

MyISAM没有redo log，MySQL崩溃之后，很可能会损坏数据，需要手动修复。

5. 非聚簇索引

MyISAM的索引是非聚簇索引，叶子节点存的是数据的地址，按主键查询要回表。

MyISAM适合什么场景

• 读多写少的场景
• 不需要事务的场景
• 一些日志表、统计报表表
• 对数据一致性要求不高的场景

五、怎么选InnoDB和MyISAM

很多同学问：到底应该选哪个？

其实现在大部分场景，直接选InnoDB就对了。因为InnoDB是默认的，功能也全，支持事务、行锁、崩溃恢复，大部分业务都适用。

MyISAM现在基本上只有一些特殊场景才会用，比如：

• 纯读的表，比如一些静态配置表
• 一些历史遗留的老项目，原来是用MyISAM的

记住：新项目直接用InnoDB，不用考虑MyISAM。

六、怎么查看表用的是什么引擎

查看某张表的引擎

SHOW TABLE STATUS LIKE '表名';

执行完之后，看Engine那列，就是这张表用的存储引擎。

查看数据库里所有表用的引擎

SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '数据库名';

七、怎么修改表的引擎

如果一张表原来是用MyISAM，想改成InnoDB，可以执行：

ALTER TABLE 表名 ENGINE = InnoDB;

注意：这个操作会把整张表重建一遍，如果表很大的话，会花很长时间，而且会锁表。最好在业务低峰期执行。

常见坑

坑1：以为MyISAM性能更好

很多同学觉得MyISAM读性能好，就想用MyISAM。其实现在InnoDB的性能已经很好了，尤其是读多写少的场景，InnoDB也不差。而且MyISAM不支持事务、表锁，并发高了性能很差。现在新项目直接用InnoDB就对了。

坑2：不知道MySQL默认引擎变了

很多老教程说MySQL默认是MyISAM，其实从MySQL 5.5开始，默认就是InnoDB了。不要被老教程误导。

坑3：用MyISAM做业务表

很多同学不知道MyISAM不支持事务，结果把业务表用MyISAM，出了问题才发现。只要是业务表，需要事务的，一定要用InnoDB。

坑4：修改引擎的时候没注意锁表

很多同学直接在线上执行ALTER TABLE改引擎，结果锁表了，业务直接卡住了。改引擎之前一定要评估表大小，最好在业务低峰期执行，或者用pt-online-schema-change这种工具在线改。

坑5：以为InnoDB一定比MyISAM慢

很多同学觉得InnoDB因为支持事务、行锁，所以性能比MyISAM差。其实不是，现在InnoDB的性能已经非常好了，尤其是读写都有并发的场景，InnoDB的行锁比MyISAM的表锁性能好很多。

总结

InnoDB和MyISAM的核心区别：

1. InnoDB支持事务，MyISAM不支持
2. InnoDB是行锁，MyISAM是表锁，InnoDB并发性能好
3. InnoDB支持崩溃恢复，MyISAM不支持
4. InnoDB支持外键，MyISAM不支持
5. InnoDB是聚簇索引，按主键查询快
6. 现在MySQL默认就是InnoDB，新项目直接用InnoDB
7. MyISAM只有一些特殊场景才用，比如纯读的表

记住：现在不用纠结选哪个，新项目直接用InnoDB就对了，不用考虑MyISAM。

遇到问题加QQ23979811 协助处理

前言

做后端开发的同学肯定都遇到过并发修改数据的问题：两个人同时改同一条数据，结果后提交的把先提交的覆盖了，数据就不对了。这时候怎么办？就需要用到并发控制。

常见的并发控制方案有两种：乐观锁和悲观锁。很多同学搞不懂这两个有什么区别，也不知道业务里应该选哪个。这篇文章就把乐观锁和悲观锁的概念、实现方法、怎么选讲清楚，看完之后你就知道什么时候用哪个了。

一、什么是悲观锁

悲观锁，顾名思义，就是很悲观。它认为并发冲突的概率很高，所以在操作数据之前，一定要先把数据锁住，然后再操作。

就像你要拿一个贵重物品，你怕别人也拿，所以先把它锁起来，你操作完了再放开。

悲观锁的特点

• 先锁再操作：操作之前先加锁
• 并发度低：同一时间只能有一个事务操作
• 适合冲突多的场景：如果并发冲突很频繁，悲观锁比较合适

二、什么是乐观锁

乐观锁，顾名思义，就是很乐观。它认为并发冲突的概率很低，所以操作数据的时候不加锁，等更新的时候再检查一下，看看有没有人在这期间改过数据。

就像你要拿一个东西，你觉得没人会跟你抢，所以直接去拿，拿的时候再看一眼，如果发现已经被别人拿了，那就重新来。

乐观锁的特点

• 操作的时候不加锁：先查出来，更新的时候再检查
• 并发度高：不用提前加锁，多个事务可以同时操作
• 适合冲突少的场景：如果并发冲突不频繁，乐观锁性能更好

三、悲观锁怎么实现

悲观锁一般用数据库的行锁来实现。MySQL里有两种方式：

1. SELECT ... FOR UPDATE

这个语句会给查询到的行加排他锁，其他事务就不能再改这些行了。

举个例子：

-- 开启事务
BEGIN;

-- 查询商品库存，并且加锁
SELECT stock FROM goods WHERE id = 1 FOR UPDATE;

-- 如果库存够的话，扣减库存
UPDATE goods SET stock = stock - 1 WHERE id = 1;

-- 提交事务
COMMIT;

这个流程就是：先把这行锁住，然后查库存，然后扣减库存，提交事务的时候释放锁。在这个过程中，其他事务想要修改这行数据，就必须等你释放锁。

2. 用UPDATE语句自带的锁

其实UPDATE语句本身就会加排他锁。所以你也可以不用SELECT FOR UPDATE，直接用UPDATE：

UPDATE goods SET stock = stock - 1 WHERE id = 1 AND stock > 0;

这样的话，如果库存够，就更新成功；如果库存不够，就更新0行。这其实也是一种悲观锁的实现。

四、乐观锁怎么实现

乐观锁一般用版本号或者时间戳来实现。

1. 版本号方式

在表里加一个version字段，每次更新的时候，把version加1。更新的时候检查一下version是不是跟之前查出来的一样，如果一样就更新，不一样就说明已经被别人改过了。

举个例子：

-- 先查出来数据，得到version
SELECT stock, version FROM goods WHERE id = 1;

-- 假设查出来version是1，stock是10

-- 更新的时候，带上version条件
UPDATE goods 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 1;

如果这个UPDATE语句执行之后，影响行数是1，说明更新成功了；如果影响行数是0，说明这期间已经有人改过数据了，更新失败，这时候就需要重试。

2. 时间戳方式

跟版本号方式差不多，只不过用时间戳代替版本号。每次更新的时候，把更新时间改成当前时间，更新的时候检查一下时间是不是跟之前查出来的一样。

五、乐观锁和悲观锁怎么选

很多同学问：业务里到底应该选乐观锁还是悲观锁？

其实没有绝对的答案，要看具体场景：

选悲观锁的场景

1. 并发冲突很频繁：如果很多人同时操作同一条数据，冲突很频繁，用悲观锁比较好。不然乐观锁每次都更新失败，要不断重试，反而更麻烦
2. 事务很长：如果事务里要做很多事情，持锁时间长，用悲观锁可以避免别人在你操作的期间改数据
3. 不能重试的场景：如果业务上不能重试，那就用悲观锁，提前锁住，保证能成功

选乐观锁的场景

1. 并发冲突很少：如果并发冲突的概率很低，用乐观锁性能更好，不用提前加锁，并发度高
2. 事务很短：如果事务里只做一点点事情，那用乐观锁就够了
3. 读多写少：如果大部分是读，只有很少的写，用乐观锁比较合适

总结一下： 冲突多、事务长 → 悲观锁；冲突少、读多写少 → 乐观锁。

六、乐观锁的ABA问题

乐观锁有一个经典的问题，就是ABA问题。

什么是ABA问题？比如：

• 事务A查出来数据是version=1，值是100
• 这时候事务B把数据改成了200，又改回了100，version变成了3
• 然后事务A更新的时候，它以为值还是100，所以更新成功了

但是中间数据其实已经被改过了，只是最后又改回来了。这就是ABA问题。

怎么解决ABA问题

一般用版本号就能解决ABA问题，因为每次修改版本号都会加1，就算最后改回来了，版本号也不一样了。

如果用时间戳的话，也能解决ABA问题。

常见坑

坑1：以为乐观锁是不加锁

很多同学以为乐观锁就是不加锁，其实不是。乐观锁是更新的时候再加锁，只是操作之前不加锁。它本质上还是要靠数据库的行锁来保证更新的原子性。

坑2：乐观锁更新失败了不知道重试

很多同学用乐观锁，更新失败了就直接报错给用户了。其实更新失败是正常的，这时候应该自动重试几次，重试成功了就没事了。如果重试几次都失败，再报错。

坑3：悲观锁加的锁太大

很多同学用SELECT FOR UPDATE，结果WHERE条件没有用到索引，导致锁了整张表，而不是某一行。这样并发度就太低了，性能很差。一定要确保WHERE条件用到了索引，这样才会加行锁。

坑4：事务太长，持锁时间久

用悲观锁的时候，如果事务很长，持锁时间久，其他事务都在等锁，并发性能就很差。所以一定要把事务尽量缩短，不要在事务里做耗时操作。

坑5：版本号用的不对

很多同学用版本号，但是更新的时候忘了把version加1，或者更新的时候没有带上version条件，这样乐观锁就失效了。一定要确保更新的时候，version加了1，并且WHERE条件里带上了原来的version。

总结

乐观锁和悲观锁的核心知识点：

1. 悲观锁：认为冲突多，先加锁再操作，并发度低，适合冲突多的场景
2. 乐观锁：认为冲突少，操作的时候不加锁，更新的时候再检查，并发度高，适合冲突少的场景
3. 悲观锁实现：SELECT ... FOR UPDATE，或者直接用UPDATE语句自带的锁
4. 乐观锁实现：版本号方式，或者时间戳方式
5. 怎么选：冲突多、事务长用悲观锁；冲突少、读多写少用乐观锁
6. ABA问题：乐观锁的经典问题，用版本号可以解决
7. 乐观锁更新失败要自动重试：不要直接报错给用户，重试几次就好了

记住：没有绝对的好和坏，看具体场景选。大部分互联网场景，读多写少，用乐观锁比较多，性能更好。

遇到问题加QQ23979811 协助处理

前言

做后端开发的同学肯定都遇到过死锁：两个事务互相等对方释放锁，结果都卡住了，数据库报错"Deadlock found when trying to get lock"。这时候整个业务都卡住了，用户都在骂娘，你急得满头大汗不知道怎么办。

很多新手同学遇到死锁就慌了，不知道怎么排查，也不知道怎么解决。其实死锁不可怕，只要掌握了正确的排查方法，很快就能定位到问题。这篇文章就把MySQL死锁的知识点从头到尾讲清楚：什么是死锁？怎么查看死锁？怎么排查？怎么避免？看完之后你就彻底搞懂了。

一、什么是死锁

死锁就是两个或者多个事务，互相持有对方需要的锁，又互相等对方释放锁，结果谁也动不了，就卡住了。

举个最简单的例子：

• 事务A锁了id=1的行，想要锁id=2的行
• 事务B锁了id=2的行，想要锁id=1的行
• 这时候A等B释放id=2的锁，B等A释放id=1的锁
• 两个事务互相等，就死锁了

MySQL检测到死锁之后，会自动回滚其中一个事务，让另一个事务继续执行。所以死锁不会一直卡住，但是被回滚的那个事务就失败了，业务上会报错。

二、死锁产生的原因

死锁一般都是因为这几种情况：

1. 多个事务以不同的顺序访问同一批资源

这是最常见的死锁原因。比如两个事务，一个先操作表A再操作表B，另一个先操作表B再操作表A，就容易死锁。

2. 事务太长，持锁时间久

如果一个事务里做了很多事情，持锁时间很长，其他事务都在等锁，时间一长就容易死锁。

3. 没有用到索引，锁了太多的行

如果WHERE条件没有用到索引，InnoDB就会扫描全表，把所有行都锁了，这样锁的范围很大，就容易和其他事务的锁冲突，产生死锁。

4. 间隙锁导致的死锁

间隙锁虽然是为了解决幻读，但是也会增加死锁的概率。两个事务都往同一个范围里插数据，间隙锁互相冲突，就容易死锁。

三、怎么查看死锁

MySQL默认会自动检测死锁，并且把死锁的信息记录下来。我们可以用下面的命令查看最近一次死锁的信息：

SHOW ENGINE INNODB STATUS;

执行完之后，会输出一大段信息，找到里面的 LATEST DETECTED DEADLOCK 部分，就是最近一次死锁的详细信息。

这个部分会告诉你：

• 哪个事务持有了什么锁
• 哪个事务在等什么锁
• 最后MySQL回滚了哪个事务

开启死锁日志

如果想要记录所有的死锁信息，不仅是最近一次，可以开启死锁日志：

# my.cnf里配置
innodb_print_all_deadlocks = 1

开启之后，所有的死锁信息都会写到MySQL的错误日志里。

四、怎么排查死锁

拿到死锁日志之后，怎么分析呢？一般按这个步骤来：

步骤1：看两个事务分别执行了什么SQL

死锁日志里会显示两个事务的SQL语句。你要看看这两个SQL分别操作了哪些表、哪些行。

步骤2：看锁的信息

日志里会显示每个事务持有了什么锁，在等什么锁。你要搞清楚：

• 事务A持有了哪些行的锁
• 事务A想要拿哪些行的锁
• 事务B持有了哪些行的锁
• 事务B想要拿哪些行的锁

这样就能搞清楚为什么会死锁了。

步骤3：分析业务逻辑

看完SQL和锁的信息之后，再结合业务逻辑，看看这两个事务为什么会以不同的顺序操作同一批数据。

比如：

• 事务A是扣库存的逻辑
• 事务B是修改订单的逻辑
• 它们都操作了商品表和订单表，但是顺序不一样，就死锁了

五、怎么避免死锁

排查完死锁之后，最重要的是怎么避免下次再出现。常见的避免死锁的方法：

1. 按相同的顺序访问资源

这是最有效的方法。所有的事务都按相同的顺序访问表和行，比如所有事务都先操作商品表，再操作订单表，就不会出现两个事务互相等的情况了。

2. 大事务拆成小事务

事务越小，持锁时间越短，死锁的概率就越低。把大事务拆成多个小事务，每个事务只做一点点事情。

比如不要在一个事务里调用RPC接口、查Redis、做复杂计算，这些耗时操作都放到事务外面。

3. 尽量用索引访问数据

如果WHERE条件没有用到索引，就会扫描全表，把所有行都锁了，这样锁的范围很大，死锁概率就高。一定要确保查询都用到了索引。

4. 降低隔离级别

如果对幻读不是很敏感，可以把隔离级别从可重复读（RR）降到读提交（RC）。这样间隙锁就没了，死锁的概率也会低很多。

很多互联网公司都是用RC隔离级别，就是为了减少锁冲突和死锁。

5. 设置锁等待超时时间

如果真的出现死锁了，不要让事务一直等。可以设置锁等待超时时间：

SET GLOBAL innodb_lock_wait_timeout = 5;

这样如果一个事务等锁超过5秒，就自动报错，不会一直卡住。

六、死锁和锁等待的区别

很多同学分不清死锁和锁等待：

锁等待

一个事务等另一个事务释放锁，但是不存在循环等待，等一会儿就好了。

比如事务A锁了id=1的行，事务B也想锁id=1的行，那B就要等A释放锁，这就是锁等待。

死锁

两个事务互相等对方释放锁，形成了循环等待，谁也动不了。

MySQL会自动检测到死锁，然后回滚其中一个事务。

常见坑

坑1：遇到死锁就重启MySQL

很多同学一遇到死锁就慌了，直接重启MySQL。这是最笨的方法，重启解决不了根本问题，下次还会出现。一定要先排查死锁的原因，从根本上解决。

坑2：以为死锁是MySQL的bug

很多同学觉得死锁是MySQL的问题，其实不是。死锁一般都是业务代码写的有问题，多个事务访问资源的顺序不一致导致的。MySQL只是帮你检测到了死锁，罪魁祸首是你的业务代码。

坑3：死锁日志里的信息看不懂

很多同学拿到死锁日志，里面全是锁的信息，看不懂。其实不用看那么细，重点看两个事务分别执行了什么SQL，操作了哪些表和行，搞清楚它们为什么会互相等就行。

坑4：只靠MySQL自动检测死锁

很多同学觉得MySQL会自动检测死锁、自动回滚，就不用管了。其实不是，死锁虽然不会一直卡住，但是被回滚的那个事务业务上会报错，影响用户体验。最好还是从源头上避免死锁。

坑5：间隙锁导致的死锁

很多同学在可重复读隔离级别下，两个事务往同一个范围插数据，结果死锁了。这就是间隙锁的问题。如果对幻读不是很敏感，降到读提交隔离级别就好了。

总结

MySQL死锁的核心知识点：

1. 死锁是什么：两个事务互相持有对方需要的锁，又互相等对方释放，形成循环等待
2. 死锁产生的原因：访问资源顺序不一致、事务太长、没用索引锁太多行、间隙锁
3. 怎么查看死锁：SHOW ENGINE INNODB STATUS，看LATEST DETECTED DEADLOCK部分
4. 怎么排查死锁：看两个事务的SQL、看锁的信息、结合业务逻辑分析
5. 怎么避免死锁：
   • 按相同的顺序访问资源
   • 大事务拆成小事务
   • 尽量用索引访问数据
   • 降低隔离级别到读提交
   • 设置锁等待超时时间
6. 死锁和锁等待的区别：锁等待是单向的，等一会儿就好；死锁是循环的，MySQL会自动回滚一个

记住：死锁不可怕，可怕的是不知道为什么会死锁。遇到死锁不要慌，先看死锁日志，找到原因，然后从业务代码上避免。

遇到问题加QQ23979811 协助处理

前言

做网站开发或者运维的同学肯定都遇到过这种情况：网站越来越慢，数据库CPU占用越来越高，用户体验越来越差。这时候怎么办？很多同学一上来就说：加机器！加内存！其实这是最笨的方法。

MySQL性能优化是一个系统工程，要从多个方面下手：SQL、索引、表结构、配置参数、架构。很多时候，不用加机器，只要优化一下SQL和索引，性能就能提升好几倍。

这篇文章就把MySQL性能优化的完整思路整理出来，从定位问题到SQL优化、表结构优化、参数优化、架构优化，一步一步讲清楚。看完之后你就知道遇到性能问题应该从哪里下手了。

一、优化之前先定位问题

很多同学一上来就瞎优化，优化了半天也不知道有没有效果。正确的做法是：先定位问题，再针对性优化。

怎么定位慢SQL

1. 开启慢查询日志

MySQL的慢查询日志可以记录所有执行慢的SQL。先把慢查询日志开了，看看哪些SQL慢。

# my.cnf里配置
slow_query_log = 1
long_query_time = 1   # 超过1秒的SQL记录下来
slow_query_log_file = /var/log/mysql/slow.log

2. 用explain分析慢SQL

找到慢SQL之后，用explain看看它的执行计划，看看有没有走索引，是不是全表扫描。

3. 看数据库的状态

用下面的命令看看数据库当前的状态：

SHOW GLOBAL STATUS;

重点关注这几个指标：

• Slow_queries：慢查询的数量
• Threads_connected：当前连接数
• Innodb_row_reads：读了多少行数据
• Innodb_data_reads：读了多少数据

二、SQL和索引优化

这是最常用、也是性价比最高的优化方式。很多时候，加个索引，SQL性能就能提升几十倍。

1. 加合适的索引

哪些字段要加索引

• WHERE条件里经常用到的字段
• 表连接的字段
• ORDER BY、GROUP BY的字段
• 区分度高的字段

索引的注意点

• 不要加太多索引，索引也占空间，而且插入更新的时候要维护索引
• 联合索引要注意最左前缀原则
• 尽量不要在索引字段上做函数操作，不然索引会失效

2. 避免索引失效的情况

常见的索引失效场景：

• WHERE条件里对索引字段做函数操作
• WHERE条件里用了!=、<>、NOT IN
• LIKE以%开头
• 联合索引不满足最左前缀
• 字段类型不匹配（比如字符串字段用数字去查）

3. 优化SQL写法

不要用SELECT *

只查需要的字段，不要查所有字段。这样不仅减少数据传输，还能用到覆盖索引。

避免大分页

比如LIMIT 100000, 10，这种越往后越慢。优化方法：用上次的最大id来查。

避免在WHERE里做计算

比如WHERE age + 1 = 18，这样索引会失效。应该写成WHERE age = 17。

小表驱动大表

联表查询的时候，小表在前，大表在后，性能更好。

三、表结构优化

1. 选择合适的数据类型

• 能用数字类型就不要用字符串
• 能用短的就不要用长的，比如用TINYINT不要用INT
• 时间类型用DATETIME或者TIMESTAMP，不要用字符串存时间

2. 避免太多字段

一张表不要有太多字段，字段太多的话，数据页能放的行就少，IO次数就多。不常用的字段可以拆到另一张表里。

3. 适当加冗余字段

有些字段虽然可以联表查出来，但是如果经常用到，可以考虑加冗余字段，减少联表查询。

4. 大表做归档

如果表里有很多历史数据，但是查询的时候很少用到，可以把历史数据归档到另一张表里，主表只保留最近的数据。这样主表数据量小了，查询就快了。

四、配置参数优化

MySQL的默认配置很多都不是最优的，需要根据自己的服务器配置调一下。

1. innodb_buffer_pool_size

这个是InnoDB的缓冲池大小，是最重要的参数。一般设置成服务器内存的50%~70%。如果你的服务器内存是16G，那这个参数就设成8G~10G。

这个参数设大了，很多数据和索引都能在内存里，就不用读磁盘了，性能会好很多。

2. innodb_log_file_size

这个是redo log的大小，一般设成256M~1G。这个参数影响写性能，太小的话会频繁刷盘。

3. max_connections

最大连接数，根据你的业务量调整，一般设成500~1000就够了。不要设太大，不然每个连接都占内存，反而会拖慢性能。

4. query_cache

MySQL 8.0已经把查询缓存去掉了，因为查询缓存的命中率不高，而且维护成本很高。如果是老版本的MySQL，建议把查询缓存关了。

五、架构层面优化

如果SQL和索引都优化过了，还是不行，那就要从架构层面下手了。

1. 读写分离

主库写，从库读，把读的压力分散到多个从库上。大部分网站都是读多写少，读写分离效果很明显。

2. 加缓存

把热点数据放到Redis里，直接从Redis读，不用查数据库。这是提升性能最明显的方式。

3. 分库分表

单表数据量太大了，到了千万级甚至亿级，那就分库分表，把数据分散到多个表或者多个数据库里。

4. 用更好的硬件

如果前面的优化都做了，还是不够，那只能加硬件了。比如用SSD代替机械硬盘，数据库的性能瓶颈很多时候都是磁盘IO。SSD的IOPS比机械硬盘高几个数量级，提升非常明显。

常见坑

坑1：上来就加机器，不优化SQL

很多同学一遇到性能问题就说：加机器！加内存！其实很多时候都是SQL写的烂，没加索引，优化一下SQL性能就能提升好几倍。加机器是最后手段，不是第一选择。

坑2：索引加了很多，但是都没用到

很多同学觉得索引加的越多越好，结果加了一堆索引，真正查询的时候一个都没用到。加索引之前要先explain一下，看看是不是真的能用到。

坑3：以为加了索引就一定快

很多同学以为只要加了索引，查询就一定快。其实不是，如果索引字段的区分度很低，比如性别只有男和女，那加了索引也没用，优化器可能还是会选择全表扫描。

坑4：调参数瞎调

很多同学看了网上的优化文章，把一堆参数往自己服务器上套，结果把数据库搞出问题了。调参数一定要一点点调，调完观察效果，不要一下子改一堆参数。

坑5：不做监控

很多同学数据库出问题了才知道性能差，平时根本没监控。一定要做监控，比如慢查询数量、CPU使用率、连接数这些指标，提前发现问题。

总结

MySQL性能优化的思路和步骤：

1. 先定位问题：开慢查询日志，找到慢SQL
2. SQL和索引优化：加合适的索引，优化SQL写法，这是性价比最高的
3. 表结构优化：选合适的数据类型，大表归档，适当加冗余字段
4. 配置参数优化：重点调innodb_buffer_pool_size
5. 架构层面优化：读写分离、加缓存、分库分表
6. 最后才是加硬件：比如用SSD、加内存

记住：性能优化是一个系统工程，要从多个方面下手。不要指望一招就能解决所有问题，先从最简单、最容易见效的地方下手，比如加索引、优化SQL，然后再一步步深入。

遇到问题加QQ23979811 协助处理

前言

做网站的同学肯定都遇到过这种情况：网站用户越来越多，数据库里的表数据量越来越大，查询越来越慢，插入也越来越慢。这时候怎么办？很多同学第一反应就是：分库分表！

但是分库分表不是万能药，也不是说只要数据量大了就一定要分。很多同学上来就分库分表，结果把自己搞的更累了，问题反而更多。

这篇文章就把分库分表的知识点从头到尾讲清楚：什么时候需要分库分表？怎么分？分完之后有什么问题？看完之后你就知道什么时候该分，什么时候不该分了。

一、什么时候需要分库分表

很多同学一上来就问：分库分表好不好？其实这是个伪命题。分库分表是有代价的，不是什么场景都适合。

一般来说，出现下面这些情况的时候，才考虑分库分表：

1. 单表数据量太大

一般来说，MySQL单表数据量到了千万级之后，性能就开始下降了。如果到了亿级，那基本上就必须分了。

但是也不是说千万级就一定要分，要看你的表结构和查询。如果表结构简单，查询也都走索引，几千万条数据性能还是能接受的。

2. 单库压力太大

一个数据库实例能承受的连接数、IOPS、CPU都是有限的。如果单库的QPS太高，扛不住了，这时候就需要分库，把压力分散到多个数据库实例上。

3. 业务发展到一定阶段

很多互联网公司的业务发展到一定阶段，单库单表确实扛不住了，这时候才需要分。创业初期的小项目，根本没必要分库分表，纯属给自己找麻烦。

总结一下： 分库分表是最后的手段，不是第一选择。在分之前，先看看能不能通过优化SQL、加索引、读写分离、加缓存这些手段解决问题。如果这些手段都用了还是不行，再考虑分库分表。

二、分库分表的两种方式

分库分表有两种方式：垂直拆分和水平拆分。

1. 垂直拆分

垂直拆分又分两种：

垂直分库

按照业务把不同的表分到不同的数据库里。比如把用户相关的表放到user库，订单相关的表放到order库，商品相关的表放到product库。

优点：

• 业务解耦，不同业务之间互不影响
• 可以针对不同的业务做不同的优化

缺点：

• 跨库联表查询比较麻烦
• 分布式事务问题

垂直分表

把一张大表的字段拆到两张表里。比如把不常用的字段拆到另一张表里，常用的字段留在原表里。

比如一张用户表，有基本信息和详细信息。把基本信息（id、昵称、头像）放到user表，详细信息（简介、地址、备注）放到user_detail表。

优点：

• 减少单表的数据量，查询更快
• 把热点字段和冷门字段分开

缺点：

• 查询的时候可能要联表
• 代码复杂度变高

2. 水平拆分

水平拆分就是把同一张表的数据，按照某个规则分到多张表里。每张表的结构是一样的，只是数据不一样。

比如一张user表有1000万条数据，我们把它分成4张表：user_0、user_1、user_2、user_3。按照用户id取模，0的放user_0，1的放user_1，以此类推。

优点：

• 单表数据量小了，性能上去了
• 可以分散到多个数据库实例上

缺点：

• 跨片查询很麻烦
• 分布式事务问题
• 扩容麻烦

三、怎么选择分片键

水平拆分的时候，最关键的就是选分片键。分片键选不好，后面会很痛苦。

好的分片键有什么特点

1. 数据分布均匀：不要有的分片数据特别多，有的特别少
2. 查询都能带上分片键：大部分查询都能根据分片键定位到具体的分片，不用扫全部分片
3. 尽量不要跨片：尽量避免需要跨片查询的场景

常见的分片键选择

1. 用user_id做分片键

如果你的业务大部分都是跟用户相关的，比如用户的订单、用户的评论，那用user_id做分片键就很合适。同一个用户的数据都在同一个分片上，查询的时候不用跨片。

2. 用order_id做分片键

如果是订单系统，大部分查询都是按订单id查的，那就用order_id做分片键。

3. 按时间做分片

如果是日志类的数据，按时间查询比较多，那就按时间做分片。比如一个月一张表，或者一个季度一张表。

分片算法选什么

常见的分片算法有：

1. 取模

比如：user_id % 4，这样数据会比较均匀。

优点： 数据分布均匀
缺点： 扩容的时候很麻烦，要重新对所有数据做取模，大部分数据都要迁移

2. 范围分片

比如：id在1-1000万的放第一个分片，1000万-2000万的放第二个分片。

优点： 扩容简单，直接加新的分片就行
缺点： 数据分布不均匀，新的数据都写到新的分片上，容易出现热点

3. 一致性哈希

一致性哈希可以解决扩容的时候数据迁移太多的问题。

四、分库分表带来的问题

分库分表不是银弹，分完之后会带来很多新的问题。

1. 分布式事务

原来一个事务里操作同一张表，现在跨了多个分片，原来的本地事务就不管用了，需要用分布式事务。分布式事务的复杂度和性能损耗都是很大的。

2. 跨片查询

原来一条SQL就能查出来的数据，现在要从多个分片查出来再合并。比如要统计所有用户的总数，就得查所有分片然后加起来。如果要分页就更麻烦了。

3. 跨片联表

原来两张表联查很简单，现在两张表在不同的分片上，联查就很麻烦。

4. 全局唯一ID

原来用自增主键就行了，现在分了多个表，自增主键就重复了。需要用全局唯一ID，比如雪花算法。

5. 扩容麻烦

一开始分了4个表，后来数据量太大了，要扩成8个表。这时候数据要重新分布，迁移量很大。

五、常用的分库分表中间件

自己写分库分表的逻辑太麻烦了，一般都是用现成的中间件：

1. ShardingSphere

Apache的开源项目，国内用的比较多。支持分库分表、读写分离、分布式事务这些功能。

2. MyCat

也是一个开源的分库分表中间件，用的也比较多。

3. 官方方案

MySQL本身也有一些分区的功能，比如Range分区、List分区。但是这个是在单库内的分区，不是真正的分库分表。

常见坑

坑1：一上来就分库分表

很多同学刚做项目，就想着分库分表，搞的很复杂。其实小项目根本没必要，单库单表就能扛住。分库分表是等到数据量真的大了再做的事情，不要提前过度设计。

坑2：分片键选的不好

很多同学分片键随便选一个，结果大部分查询都带不上分片键，每次都要扫全部分片，性能反而更差了。分片键一定要选那些查询最常用的字段。

坑3：用取模算法，扩容的时候崩溃了

很多同学一开始用取模算法分了4个表，后来要扩到8个表，结果发现数据全都要重新分布，迁移量巨大，欲哭无泪。所以一开始就要考虑好扩容的问题。

坑4：分完之后跨片查询太多

很多同学分完库分完表之后，发现原来的很多查询都要跨片，性能反而比原来更差了。这就是分片键没选好，或者业务设计的问题。

坑5：以为分库分表是万能的

很多同学觉得分库分表能解决所有性能问题，其实不是。分库分表只是把单表的数据量变小了，但是复杂SQL、慢SQL这些问题还是存在的。优化SQL、加索引这些基础工作还是要做。

总结

MySQL分库分表的核心知识点：

1. 分库分表是最后的手段，不是第一选择，先试试优化SQL、加索引、读写分离、加缓存
2. 什么时候需要分：单表千万级以上、单库压力太大、业务真的到那个规模了
3. 垂直拆分：按业务拆库、按字段拆表
4. 水平拆分：同一张表按某个规则拆成多张表
5. 分片键很重要：要数据分布均匀、查询都能带上、尽量不要跨片
6. 分片算法：取模（均匀但扩容麻烦）、范围（扩容简单但容易有热点）、一致性哈希
7. 分完之后的问题：分布式事务、跨片查询、跨片联表、全局唯一ID、扩容麻烦
8. 常用中间件：ShardingSphere、MyCat

记住：分库分表是有代价的，能不分就不分。真的要分的时候，一定要想清楚分片键和分片算法，不然后面会很痛苦。

遇到问题加QQ23979811 协助处理

前言

做后端开发的同学肯定都遇到过这种情况：写了一条SQL，查询特别慢，但是不知道为什么慢。是没走索引？还是全表扫描？还是数据量太大？

这时候就需要用MySQL的explain命令了。explain可以告诉你MySQL是怎么执行这条SQL的，走了哪个索引，扫了多少行，是不是全表扫描。搞懂了explain，你就能快速定位SQL慢的原因，然后针对性优化。

很多新手同学不知道explain怎么用，也不知道每一列是什么意思。这篇文章就把explain的知识点从头到尾讲清楚，看完之后你就能看懂执行计划了。

一、explain是什么

explain就是MySQL提供的一个分析工具。你在SELECT语句前面加上EXPLAIN，MySQL就不会真的执行这条SQL，而是告诉你它准备怎么执行这条SQL。

explain能告诉你什么

• 这条SQL有没有用到索引
• 扫了多少行数据
• 有没有全表扫描
• 用的是哪个索引
• 表连接的顺序是什么

二、怎么用explain

用法很简单，就在你的SELECT语句前面加上EXPLAIN就行：

EXPLAIN SELECT * FROM users WHERE name = '张三';

执行完之后，MySQL会返回一行结果，每一列都代表不同的信息。

三、explain的各列详解

explain返回的结果有很多列，我们重点讲几个常用的。

1. id

这一列表示SELECT的序号。如果有多个SELECT（比如子查询），id越大的越先执行。

2. select_type

这一列表示查询的类型，常见的有：

• SIMPLE：简单查询，没有子查询或者UNION
• PRIMARY：最外层的查询
• SUBQUERY：子查询里的第一个SELECT
• DERIVED：派生表（FROM子句里的子查询）
• UNION：UNION里的第二个及以后的SELECT

3. table

这一列表示当前这一行是在查哪张表。

4. type

这一列非常重要，表示MySQL是怎么找到数据的，也就是访问类型。从好到差依次是：

• system：表里只有一行数据，最好的情况
• const：通过索引一次就找到了，比如WHERE id=1，id是主键
• eq_ref：联表查询的时候，用主键或者唯一索引关联，最多匹配一行
• ref：用普通索引关联，可能匹配多行
• range：索引范围扫描，比如WHERE id BETWEEN 1 AND 10
• index：扫描整个索引树，比全表扫描好一点
• ALL：全表扫描，最差的情况，一定要优化

重点关注： 如果type是ALL或者index，说明这条SQL性能很差，要赶紧优化。

5. possible_keys

这一列表示理论上可能用到的索引。如果是NULL，说明没有用到索引。

6. key

这一列表示实际用到的索引。如果是NULL，说明真的没用到索引。

重点关注： 如果possible_keys有值，但是key是NULL，说明MySQL没有选择用索引，可能是数据量太小，或者索引失效了。

7. key_len

这一列表示用了索引的长度。可以用来判断用了联合索引的前几个字段。

8. ref

这一列表示用了哪个常量或者列来和索引做比较。

9. rows

这一列表示MySQL估计要扫多少行才能找到需要的数据。这个值越大，说明要扫的数据越多，性能越差。

10. Extra

这一列非常重要，包含了很多额外的信息，常见的有：

• Using where：服务器在存储引擎返回数据之后，又做了一层WHERE过滤
• Using index：用了覆盖索引，不需要回表，性能很好
• Using temporary：用了临时表，一般是GROUP BY或者ORDER BY的时候没有用到索引
• Using filesort：用了文件排序，ORDER BY没有用到索引，性能差
• Using join buffer：联表的时候用了缓冲，说明联表的字段没有索引
• Impossible WHERE：WHERE条件永远是false，根本查不到数据

重点关注： 如果Extra里出现了Using temporary或者Using filesort，说明SQL性能有问题，要优化。

四、重点看哪几列

很多同学看explain结果的时候，这么多列不知道重点看什么。其实重点看这几个：

1. type：有没有全表扫描（ALL）
2. key：有没有用到索引
3. rows：扫了多少行
4. Extra：有没有Using filesort或者Using temporary

只要这几个都没问题，这条SQL的性能一般就不会太差。

五、举个例子

比如我们执行这条SQL：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;

执行完之后看到：

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | ref  | user_id       | user_id | 4       | const |  100 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

我们来分析一下：

• type是ref：说明用了普通索引，还不错
• possible_keys是user_id：理论上可以用user_id索引
• key是user_id：实际用了user_id索引
• rows是100：估计要扫100行
• Extra是Using where：说明返回之后又做了一层过滤

从这个结果来看，这条SQL用了索引，性能还可以。但是status字段没有用到索引，如果status过滤性好的话，可以考虑建一个(user_id, status)的联合索引。

六、explain的扩展用法

除了基本的explain，还有几个扩展的用法：

EXPLAIN PARTITIONS

看这条SQL会不会命中分区表：

EXPLAIN PARTITIONS SELECT * FROM orders WHERE ...;

EXPLAIN FORMAT=JSON

输出JSON格式的执行计划，信息更详细：

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE ...;

常见坑

坑1：看到type是const就以为没问题

很多同学看到type是const就以为这条SQL性能很好。但是如果rows很大，或者Extra里有Using filesort，性能还是可能有问题。不能只看type，要综合看。

坑2：以为possible_keys有值就一定会用索引

很多同学看到possible_keys有值，就以为这条SQL一定会用到索引。其实不是，possible_keys只是说理论上可能用到，实际用不用要看key列。如果key是NULL，说明MySQL最后没有用这个索引。

坑3：rows是精确值

很多同学以为rows列是精确的行数，其实不是。rows是MySQL估计出来的行数，不是精确值。但是大致能反映出扫描的数据量大小。

坑4：以为Using filesort就是用了文件排序

很多同学看到Extra里有Using filesort就以为是真的用磁盘文件排序。其实不是，Using filesort只是说明MySQL做了额外的排序操作，不一定是用磁盘，数据量小的时候也可能在内存里排。

坑5：只看单表的explain，不看联表的

很多同学优化SQL的时候，只看单表的执行计划，不看联表的。其实联表的时候，表的连接顺序对性能影响很大。explain会按表的连接顺序一行一行列出来，要按顺序看。

总结

MySQL explain执行计划的核心知识点：

1. explain的作用：看MySQL是怎么执行SQL的，不用真的执行
2. type列最重要：从好到差是system > const > eq_ref > ref > range > index > ALL，出现ALL就是全表扫描
3. key列：实际用到的索引，NULL就是没用到索引
4. rows列：估计要扫多少行，越大越慢
5. Extra列：重点关注Using filesort和Using temporary，出现这两个就要优化
6. 重点看type、key、rows、Extra这四列，其他的了解就行
7. rows是估计值，不是精确的，但是能反映大致的数据量

explain是分析SQL性能的必备工具，不管是面试还是实际开发，都是必须掌握的。以后遇到SQL慢，先explain一下，看看是哪里出了问题。

遇到问题加QQ23979811 协助处理

前言

做运维或者DBA的同学肯定都遇到过这种情况：不小心执行了一个DROP TABLE语句，或者DELETE忘加WHERE条件，把数据删了。这时候怎么办？别急，MySQL的binlog日志可以帮你把数据找回来。

很多新手同学不知道binlog是什么，也不知道怎么用它来恢复数据。这篇文章就把binlog的知识点从头到尾讲清楚，包括binlog的三种格式、怎么开启、怎么查看、怎么用它恢复数据，看完之后你就彻底搞懂了。

一、binlog是什么

binlog的全称是二进制日志（Binary Log）。它记录了MySQL数据库里所有的写操作（INSERT、UPDATE、DELETE、CREATE TABLE这些），但是不记录SELECT查询。

binlog有什么用

1. 数据恢复：不小心删了数据，可以用binlog恢复
2. 主从复制：主库把binlog传给从库，从库重放binlog，实现主从同步
3. 数据审计：可以通过binlog追溯谁在什么时候做了什么操作

二、binlog的三种格式

binlog有三种格式，不同的格式记录的内容不一样。

1. STATEMENT（语句模式）

这种格式记录的是SQL语句本身。比如你执行了一条INSERT语句，binlog里就记录这条INSERT语句。

优点：

• 日志量小，占用空间少
• 同步的时候从库重放SQL就行

缺点：

• 有些函数（比如NOW()、UUID()）在主库和从库执行结果可能不一样，导致主从不一致
• 某些复杂的SQL可能在从库上执行结果不一样

2. ROW（行模式）

这种格式记录的是每一行数据的修改。比如你更新了100行数据，binlog里就记录这100行每一行改前是什么样，改后是什么样。

优点：

• 记录的是真实的数据修改，不会出现主从不一致的问题
• 能精确知道哪一行被改了

缺点：

• 日志量大，因为每一行修改都要记录
• 批量更新的时候，日志会特别大

3. MIXED（混合模式）

这种模式是前两种的混合。MySQL会自动判断：一般的SQL用STATEMENT模式，遇到那些可能出问题的SQL（比如用了NOW()、UUID()这些函数的），就自动切换成ROW模式。

总结： 现在一般推荐用ROW模式，虽然日志量大一点，但是数据一致性有保证，不会出主从不一致的问题。

三、怎么开启binlog

默认情况下，MySQL的binlog可能是没开的。我们先看看开没开：

SHOW VARIABLES LIKE 'log_bin';

如果Value是ON，说明开了；如果是OFF，说明没开。

开启binlog

编辑MySQL的配置文件 /etc/my.cnf，在[mysqld]下面添加：

[mysqld]
# 开启binlog
log_bin = mysql-bin

# binlog的格式，推荐用ROW
binlog_format = ROW

# 保存多少天的binlog，过期自动删除
expire_logs_days = 7

# 单个binlog文件最大多大
max_binlog_size = 100M

修改完之后重启MySQL服务：

systemctl restart mysqld

重启完之后再查一下，应该就开了。

四、怎么查看binlog

查看有哪些binlog文件

SHOW BINARY LOGS;

执行完之后会看到类似这样的结果：

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     15264 |
| mysql-bin.000002 |       126 |
| mysql-bin.000003 |       126 |
+------------------+-----------+

binlog文件是按编号来的，mysql-bin.000001满了就生成mysql-bin.000002，以此类推。

查看binlog里的内容

binlog是二进制文件，不能直接用cat看，要用mysqlbinlog工具。

比如要看mysql-bin.000001这个文件：

mysqlbinlog /var/lib/mysql/mysql-bin.000001

如果是ROW格式的binlog，直接看是看不懂的，要加-vv参数：

mysqlbinlog -vv /var/lib/mysql/mysql-bin.000001

这样就能看到具体改了哪一行，改前是什么，改后是什么。

五、怎么用binlog恢复数据

这是大家最关心的：不小心删了数据，怎么用binlog恢复？

恢复的原理

binlog记录了所有的写操作。如果你不小心删了某段时间的数据，只要把这段时间之前的binlog重新执行一遍，数据就回来了。

举个例子

假设你在2026-10-05 10:00的时候，不小心执行了一条DELETE语句，把testdb库的users表全删了。现在要恢复数据。

步骤1：找到要恢复的binlog文件

先看看有哪些binlog文件，找到那个时间点对应的文件：

SHOW BINARY LOGS;

步骤2：找到删除操作的位置

用mysqlbinlog工具查看binlog，找到DELETE语句的位置：

mysqlbinlog --start-datetime="2026-10-05 09:00:00" --stop-datetime="2026-10-05 11:00:00" /var/lib/mysql/mysql-bin.000003

然后找到那条DELETE语句在binlog里的位置（position）。

步骤3：恢复数据

把DELETE语句之前的binlog重新执行一遍，数据就回来了：

mysqlbinlog --stop-position=刚才找到的位置 /var/lib/mysql/mysql-bin.000003 | mysql -u root -p

这样就把DELETE之前的数据恢复了。

注意： 恢复完之后，DELETE之后的操作也没了，所以还要把DELETE之后的操作也重新执行一遍，直到最新的位置。

六、binlog的其他常用操作

手动生成新的binlog文件

FLUSH LOGS;

这个命令会关闭当前的binlog文件，生成一个新的。一般备份完数据库之后执行这个，这样备份之后的操作都记录到新的binlog里，恢复的时候方便。

删除旧的binlog文件

-- 删除指定文件之前的所有binlog
PURGE BINARY LOGS TO 'mysql-bin.000003';

-- 删除指定日期之前的所有binlog
PURGE BINARY LOGS BEFORE '2026-10-01 00:00:00';

查看当前正在写的binlog

SHOW MASTER STATUS;

这个命令会显示当前正在写的binlog文件名和位置，主从复制的时候经常用到。

常见坑

坑1：binlog没开，删了数据找不回来

很多同学的MySQL默认没开binlog，结果不小心删了数据，才发现根本没有日志可以恢复，欲哭无泪。生产环境一定要开binlog！

坑2：binlog格式用了STATEMENT，主从不一致

很多同学用默认的STATEMENT格式，结果主从数据不一致。推荐用ROW格式，虽然日志大一点，但是数据一致性有保证。

坑3：binlog占满磁盘

很多同学开了binlog但是没设置过期时间，结果binlog文件越来越多，把磁盘占满了，MySQL直接挂了。一定要设置expire_logs_days，让旧的binlog自动删除。

坑4：恢复数据的时候把DELETE也执行了

很多同学恢复的时候，把DELETE语句也一起执行了，结果恢复完数据又被删了。一定要找到DELETE之前的位置，只恢复到那个位置之前。

坑5：恢复完没验证

很多同学恢复完数据就以为完事了，结果一查发现数据不对。恢复完一定要仔细核对数据，确保没问题了再上线。

总结

MySQL binlog的核心知识点：

1. binlog记录所有写操作，不记录查询
2. binlog的三个作用：数据恢复、主从复制、数据审计
3. 三种格式：
   • STATEMENT：记录SQL语句，日志小，但是可能主从不一致
   • ROW：记录每行数据修改，日志大，但是数据一致，推荐用
   • MIXED：混合模式，自动选择
4. 开启binlog：在my.cnf里配置log_bin和binlog_format
5. 查看binlog：用mysqlbinlog工具，ROW格式要加-vv
6. 恢复数据：找到误操作之前的位置，重新执行之前的binlog
7. 注意设置过期时间：不然binlog会把磁盘占满

binlog是MySQL最重要的日志之一，生产环境必须开。学会了用binlog恢复数据，以后不小心删数据的时候就不用慌了。

遇到问题加QQ23979811 协助处理

前言

做后端开发的同学肯定都听说过事务隔离级别，但是很多同学搞不清楚读未提交、读提交、可重复读、串行化这四个级别到底有什么区别，也不知道自己项目应该用哪个级别。

很多面试的时候也经常被问到：MySQL的默认隔离级别是什么？能解决什么问题？很多同学答不上来。

这篇文章就把MySQL的事务隔离级别从头到尾讲清楚，看完之后你就彻底搞懂了。

一、先复习一下事务的ACID特性

讲隔离级别之前，先复习一下事务的四个特性，也就是ACID：

1. 原子性（Atomicity）：事务里的操作要么全部成功，要么全部失败回滚
2. 一致性（Consistency）：事务执行前后，数据库的完整性约束没有被破坏
3. 隔离性（Isolation）：多个事务之间互相隔离，不能互相干扰
4. 持久性（Durability）：事务提交之后，修改是永久的

我们今天讲的隔离级别，就是跟第三个特性"隔离性"有关的。隔离性的程度不同，就有了不同的隔离级别。

二、并发事务会带来什么问题

为什么需要隔离级别？因为多个事务同时操作数据库的时候，会出现各种问题。

常见的并发问题有三个：

1. 脏读

脏读就是：一个事务读到了另一个事务还没提交的数据。

比如：

• 事务A把某条数据从1改成了2，但是还没提交
• 事务B读了这条数据，读到的是2
• 然后事务A回滚了，数据又变回了1
• 事务B读的那个2就是脏数据，这就是脏读

2. 不可重复读

不可重复读就是：一个事务里，两次读同一条数据，结果不一样。

比如：

• 事务A第一次读某条数据，值是1
• 事务B把这条数据改成了2，并且提交了
• 事务A第二次再读这条数据，值变成了2
• 同一个事务里，两次读同一条数据结果不一样，这就是不可重复读

3. 幻读

幻读就是：一个事务里，两次查询的结果集不一样。

比如：

• 事务A查询id > 5的记录，查到了5、6两条
• 事务B插入了一条id=7的记录，并且提交了
• 事务A再查一次，发现多了一条id=7的
• 这就是幻读

注意：不可重复读和幻读的区别：

• 不可重复读：同一条数据，两次读结果不一样（修改导致的）
• 幻读：查询的结果集变多或者变少了（插入/删除导致的）

三、四个事务隔离级别

为了解决上面说的这三个问题，SQL标准定义了四个隔离级别，级别越高，越能解决问题，但是并发性能越差。

1. 读未提交（Read Uncommitted）

最低的隔离级别。一个事务可以读到另一个事务还没提交的数据。

能解决什么问题： 啥也解决不了
会出现什么问题： 脏读、不可重复读、幻读都可能出现

这个级别基本上没人用，所以就不多说了。

2. 读提交（Read Committed，RC）

一个事务只能读到另一个事务已经提交的数据。

能解决什么问题： 解决了脏读
还会出现什么问题： 不可重复读、幻读

Oracle默认就是这个隔离级别。

3. 可重复读（Repeatable Read，RR）

一个事务里，多次读同一条数据，结果是一样的。

能解决什么问题： 解决了脏读、不可重复读
还会出现什么问题： 幻读

注意：MySQL的InnoDB引擎在RR级别下，通过间隙锁解决了幻读问题，所以MySQL的RR级别实际上可以完全解决幻读。

MySQL默认就是这个隔离级别。

4. 串行化（Serializable）

最高的隔离级别。所有事务一个一个排队执行，完全串行。

能解决什么问题： 脏读、不可重复读、幻读全都解决了
缺点： 性能太差了，并发度最低，基本上没人用

四、四个隔离级别对比表

给大家整理了一个表格，一目了然：

五、MySQL默认的隔离级别

很多同学面试的时候被问到"MySQL默认的隔离级别是什么"，答不上来。

记住：MySQL InnoDB默认的隔离级别是可重复读（RR）。

注意和Oracle区分一下：Oracle默认的是读提交（RC）。

六、怎么查看当前的隔离级别

登录MySQL之后，执行下面的命令：

-- 查看全局的隔离级别
SELECT @@global.tx_isolation;

-- 查看当前会话的隔离级别
SELECT @@session.tx_isolation;

执行完之后会看到类似这样的结果：

+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这就说明当前是可重复读级别。

七、怎么修改隔离级别

临时修改（当前会话）

-- 改成读提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 改成可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

这种修改只对当前连接有效，断开连接之后就恢复了。

全局临时修改

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

这种修改对新连接有效，已经存在的连接不受影响，MySQL重启之后就恢复了。

永久修改

要永久修改的话，需要改配置文件。编辑my.cnf，在[mysqld]下面添加：

[mysqld]
transaction-isolation = READ-COMMITTED

修改完之后重启MySQL服务，就永久生效了。

八、实际项目中应该用哪个级别

很多同学问：实际项目中应该用哪个隔离级别？

一般来说：

• 如果对并发性能要求高：用读提交（RC）就够了，很多互联网公司都是用RC
• 如果对数据一致性要求高：用可重复读（RR），MySQL默认就是这个
• 串行化基本上没人用，性能太差了

其实大部分情况下，用MySQL默认的RR就够了，不用特意改。如果并发特别高，可以考虑改成RC，这样间隙锁就没了，并发性能会好很多。

常见坑

坑1：不知道MySQL默认的隔离级别是RR

很多同学以为MySQL默认是读提交，跟Oracle一样，结果面试答错了。记住：MySQL默认是可重复读（RR）。

坑2：把不可重复读和幻读搞混了

很多同学分不清不可重复读和幻读。记住：

• 不可重复读：同一条数据被修改了，两次读结果不一样
• 幻读：结果集的行数变多或者变少了，是插入/删除导致的

坑3：以为RR级别解决不了幻读

很多同学以为RR级别解决不了幻读，其实MySQL的InnoDB引擎在RR级别下，通过间隙锁是可以解决幻读的。这一点跟标准SQL不一样，MySQL做了增强。

坑4：修改了隔离级别但是没生效

很多同学用SET GLOBAL改了隔离级别，然后用当前连接查，发现还是原来的级别。这是因为SET GLOBAL只对新连接有效，当前连接还是原来的。要当前连接生效的话，用SET SESSION。

总结

MySQL事务隔离级别的核心知识点：

1. 并发事务的三个问题：脏读、不可重复读、幻读
2. 四个隔离级别：读未提交、读提交、可重复读、串行化，级别越高越安全，性能越差
3. 脏读：读到了别的事务还没提交的数据
4. 不可重复读：同一个事务里，两次读同一条数据结果不一样（修改导致的）
5. 幻读：同一个事务里，两次查询的结果集不一样（插入/删除导致的）
6. MySQL InnoDB默认是可重复读（RR），并且通过间隙锁解决了幻读
7. 查看隔离级别：SELECT @@tx_isolation
8. 修改隔离级别：SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL ...
9. 实际项目用RR或者RC就够了，串行化性能太差没人用

事务隔离级别是数据库的基础知识点，不管是面试还是实际开发，都是必须掌握的。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定都遇到过这种情况：两个人同时修改同一条数据，结果后提交的把先提交的覆盖了，数据就不对了。或者两个事务互相等对方释放锁，结果都卡住了，这就是死锁。

这些问题其实都是跟MySQL的锁机制有关的。很多新手同学对锁的概念很模糊，不知道什么是表锁、行锁、间隙锁，也不知道什么时候会加什么锁。这篇文章就把MySQL的锁机制从头到尾讲清楚，看完之后你就对锁有一个完整的认识了。

一、为什么需要锁

为什么数据库需要锁？很简单，因为要保证数据的一致性。

如果没有锁，两个事务同时修改同一条数据，就会出现问题。比如：

• A事务读了一条数据，值是100
• B事务也读了同一条数据，值也是100
• A事务给它加1，变成101，写回去
• B事务也给它加1，变成101，写回去

结果两个操作加起来，应该变成102，结果实际变成了101。这就出问题了。

锁的作用就是：当一个事务在操作数据的时候，其他事务不能操作，这样就保证了数据的一致性。

二、表锁

表锁就是锁整张表。当一个事务给表加了表锁，其他事务就不能操作这张表了。

表锁的特点

• 开销小，加锁快：因为不用锁每一行，直接锁整张表就行
• 不会出现死锁：因为一次就锁整张表，不存在互相等的情况
• 锁粒度大，并发度低：一张表同一时间只能有一个事务写，并发高了性能就差

什么时候用表锁

• 基本上我们用的InnoDB引擎很少用表锁，MyISAM引擎默认就是表锁
• InnoDB引擎在某些特殊情况下也会用表锁，比如：
  • 没有用到索引，导致行锁失效，升级成表锁
  • 要修改很多行数据，优化器觉得用表锁更划算

怎么手动加表锁

-- 加读锁（共享锁）
LOCK TABLES test READ;

-- 加写锁（排他锁）
LOCK TABLES test WRITE;

-- 释放锁
UNLOCK TABLES;

三、行锁

行锁就是锁某一行数据。当一个事务给某一行加了行锁，其他事务就不能操作这一行了，但是可以操作表里的其他行。

行锁的特点

• 开销大，加锁慢：要找到具体的行，然后加锁
• 可能会出现死锁：两个事务分别锁了不同的行，又互相等对方释放
• 锁粒度小，并发度高：不同的行可以同时被不同的事务操作，并发性能好

InnoDB的行锁是怎么实现的

很多同学以为InnoDB的行锁是真的锁数据行，其实不是。InnoDB的行锁是锁索引实现的。

也就是说，你WHERE条件用到了哪个索引，InnoDB就会给那个索引上加锁。如果你的WHERE条件没有用到索引，那InnoDB就没办法给行加锁了，这时候就会扫描全表，把所有行都锁一遍，相当于变成了表锁。

这就是为什么索引失效会导致性能差：不仅查询慢，还会把所有行都锁了，并发性能直接崩了。

行锁的两种模式

1. 共享锁（S锁）：读锁，加了S锁之后，其他事务也能加S锁，但是不能加X锁

   • 比如：SELECT ... LOCK IN SHARE MODE

2. 排他锁（X锁）：写锁，加了X锁之后，其他事务既不能加S锁，也不能加X锁

   • 比如：SELECT ... FOR UPDATE
   • UPDATE、DELETE语句默认都会加X锁

四、间隙锁

间隙锁是InnoDB里比较特殊的一种锁，很多同学都搞不懂。

简单来说，间隙锁就是锁两个值之间的间隙。比如你的表有1、3、5三个值，那间隙锁可以锁 (1,3)、(3,5) 这些区间，让你不能往这个区间里插数据。

间隙锁是干嘛的

间隙锁是为了防止幻读。

什么是幻读？比如：

• 事务A查询id > 5的记录，查到了5、6两条
• 事务B插入了一条id=7的记录
• 事务A再查一次，发现多了一条id=7的，这就是幻读

间隙锁就是为了解决这个问题：当你查询某个范围的数据时，InnoDB会给这个范围的间隙也加上锁，这样其他事务就不能往这个范围里插数据了，就不会出现幻读了。

间隙锁的注意点

1. 间隙锁只会在可重复读（RR）隔离级别下才有
2. 间隙锁和间隙锁之间不冲突，但是间隙锁和插入操作是冲突的
3. 间隙锁会导致加锁的范围变大，本来你只想锁一条记录，结果把周围的间隙也锁了，并发性能就差了

五、临键锁（Next-Key Lock）

临键锁就是行锁 + 间隙锁的组合。InnoDB在可重复读隔离级别下，默认用的就是临键锁。

比如你有一个索引，值是1、3、5、7。如果你查询id=3，那InnoDB加的临键锁就是 (1, 3]，也就是锁了1到3这个区间，包括3本身。

临键锁的作用就是：既锁了这条记录，又锁了它前面的间隙，这样就防止了幻读。

六、死锁

死锁就是两个或者多个事务互相等对方释放锁，结果谁也动不了，就卡住了。

比如：

• 事务A锁了id=1的行，想要锁id=2的行
• 事务B锁了id=2的行，想要锁id=1的行
• 这时候两个事务互相等，就死锁了

怎么解决死锁

MySQL有自动检测死锁的机制，发现死锁之后，会自动回滚其中一个事务，让另一个事务继续执行。

怎么避免死锁

1. 按相同的顺序访问表和行：比如所有事务都先锁id=1，再锁id=2，就不会死锁了
2. 大事务拆成小事务：事务越小，持锁时间越短，死锁概率越低
3. 尽量用索引访问数据：不用索引的话会锁更多的行，更容易死锁
4. 降低隔离级别：比如从可重复读降到读提交，间隙锁就没了，死锁概率也会低一点

七、怎么查看当前有哪些锁

MySQL8.0可以用下面的命令查看当前的锁：

SELECT * FROM performance_schema.data_locks;

或者看一下当前的事务：

SELECT * FROM information_schema.innodb_trx;

常见坑

坑1：没有用索引，行锁变成表锁

很多同学以为自己加的是行锁，结果发现整张表都被锁了。这是因为你的WHERE条件没有用到索引，InnoDB没办法定位到具体的行，只能扫描全表，把所有行都锁了，相当于表锁。

解决方法： 确保你的查询语句用到了索引，这样才会加行锁而不是表锁。

坑2：间隙锁导致并发性能差

很多同学发现自己的并发不高，但是锁冲突很严重。这就是因为间隙锁把范围锁大了。本来你只想锁一条记录，结果把周围的间隙也锁了，其他事务插数据就会被挡住。

解决方法： 如果不需要解决幻读，可以把隔离级别降到读提交（RC），这样就没有间隙锁了，并发性能会好很多。

坑3：事务太长，持锁时间久

很多同学写代码的时候，事务里包含了RPC调用、查Redis、做复杂计算这些耗时操作。结果这个事务持锁的时间特别长，其他事务都在等锁，性能就差了。

解决方法： 事务里只放数据库操作，其他耗时操作都放到事务外面。

坑4：死锁了不知道怎么排查

很多同学遇到死锁就慌了，不知道怎么查。其实MySQL默认会把死锁的信息记录到错误日志里。可以看看MySQL的错误日志，里面有详细的死锁信息。

也可以用这个命令查看最近的死锁：

SHOW ENGINE INNODB STATUS;

里面有个LATEST DETECTED DEADLOCK部分，就是最近一次死锁的信息。

总结

MySQL锁机制的核心知识点：

1. 表锁：锁整张表，开销小，并发低，MyISAM默认用
2. 行锁：锁某一行，开销大，并发高，InnoDB默认用
3. 行锁是锁索引实现的，没有用到索引的话会变成表锁
4. 共享锁（S锁）：读锁，多个事务可以同时加
5. 排他锁（X锁）：写锁，只能有一个事务加
6. 间隙锁：锁两个值之间的间隙，用来防止幻读，只有可重复读隔离级别才有
7. 临键锁：行锁 + 间隙锁，InnoDB默认的加锁方式
8. 死锁：两个事务互相等对方释放锁，MySQL会自动回滚其中一个
9. 避免死锁的方法：按相同顺序访问、小事务、用索引

锁是数据库里比较复杂的一个知识点，但是也是必须掌握的。搞懂了锁，你才能写出高并发的数据库代码。

遇到问题加QQ23979811 协助处理

前言

做网站的同学肯定都遇到过这种情况：网站用户越来越多，数据库压力越来越大，单台数据库扛不住了。这时候怎么办？很常见的方案就是做主从复制，主库写数据，从库读数据，把读写压力分开。

很多新手同学觉得主从复制很难，不敢尝试。其实只要按照步骤来，一步步配置，很快就能搭起来。这篇文章就把一主一从的主从复制搭建完整教程整理出来，从原理到实操，一步一步讲清楚。

一、主从复制是干嘛的

简单来说，主从复制就是：主数据库（Master）负责写数据，从数据库（Slave）负责读数据。主库的数据会自动同步到从库上。

主从复制的作用

1. 读写分离：写操作走主库，读操作走从库，减轻主库压力
2. 数据备份：从库相当于实时备份，主库挂了从库还能顶上
3. 做数据分析：可以在从库上跑复杂的统计查询，不影响主库的业务

主从复制的原理

主从复制的原理其实很简单，三步：

1. 主库把数据变更记录到二进制日志（binlog）里
2. 从库把主库的binlog拉过来，写到自己的中继日志（relay log）里
3. 从库重放中继日志里的SQL，这样数据就和主库一致了

二、准备工作

搭建主从复制之前，先准备好环境：

服务器准备

需要两台服务器：

• 主库服务器：IP假设是 192.168.1.10
• 从库服务器：IP假设是 192.168.1.11

软件要求

• 两台服务器都要安装MySQL，版本最好一样
• 主库和从库的server-id不能一样
• 两台服务器的MySQL端口要能互相通（一般是3306）

三、配置主库

首先配置主库，编辑主库的my.cnf配置文件：

vi /etc/my.cnf

在 [mysqld] 下面添加这些配置：

[mysqld]
# 唯一的服务器ID，主库和从库不能一样
server-id = 1

# 开启二进制日志
log-bin = mysql-bin

# 需要同步的数据库，多个用逗号隔开
binlog-do-db = testdb

# 不同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

修改完之后重启主库的MySQL：

systemctl restart mysqld

给从库创建一个复制账号

主库上要创建一个专门用来复制的用户，从库要用这个账号来连主库拉数据。

登录主库MySQL：

mysql -u root -p

然后执行：

CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'repl123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';
FLUSH PRIVILEGES;

这个命令创建了一个叫repl的用户，密码是repl123456，只能从从库的IP（192.168.1.11）连接，并且有复制的权限。

查看主库的状态

接下来要记录主库的binlog文件名和位置，从库配置的时候要用：

SHOW MASTER STATUS;

执行完之后会看到类似这样的结果：

+------------------+----------+--------------+----------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+
| mysql-bin.000001 |      156 | testdb       | mysql,information_schema,...     |                   |
+------------------+----------+--------------+----------------------------------+-------------------+

记住File和Position的值，比如这里是 mysql-bin.000001 和 156。

注意：执行完这个命令之后，主库就不要再写数据了，不然Position会变，到时候从库同步就会出错。

四、配置从库

接下来配置从库。编辑从库的my.cnf配置文件：

vi /etc/my.cnf

在 [mysqld] 下面添加：

[mysqld]
# 唯一的服务器ID，和主库不一样就行
server-id = 2

# 开启中继日志
relay-log = relay-bin

# 只读模式，从库默认只读，防止误写
read_only = 1

修改完之后重启从库的MySQL：

systemctl restart mysqld

配置从库连接主库

登录从库的MySQL：

mysql -u root -p

然后执行下面的命令，告诉从库主库在哪里，用什么账号连接：

CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl123456',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=156;

这里的MASTER_LOG_FILE和MASTER_LOG_POS就是刚才在主库上用SHOW MASTER STATUS查出来的值。

启动从库复制

配置完之后，启动从库的复制线程：

START SLAVE;

查看从库状态

看看从库是不是正常运行：

SHOW SLAVE STATUS\G

执行完之后会看到很多信息，重点看这两个：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果这两个都是Yes，那就说明主从复制已经搭好了！

如果是No，那就说明哪里出错了，下面的Last_Error字段会显示错误信息，根据错误信息去排查就行。

五、测试主从复制

现在来测试一下主从复制是不是正常工作。

在主库上创建一个表，插入一些数据：

USE testdb;
CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

INSERT INTO test (name) VALUES ('张三'), ('李四'), ('王五');

然后去从库上查一下：

USE testdb;
SELECT * FROM test;

如果能看到刚才插入的那三条数据，那就说明主从复制是正常工作的！

六、主从复制的常见问题

主从延迟

主从复制不是实时的，有时候主库写了数据，从库要等一会儿才能读到。如果主库压力很大，或者从库性能差，延迟可能会很大。

可以用这个命令查看从库延迟了多少秒：

SHOW SLAVE STATUS\G

看Seconds_Behind_Master这个值，就是延迟的秒数。如果是0，说明没有延迟。

从库误写数据

很多同学不知道从库是只读的，不小心在从库上写了数据，导致主从数据不一致，复制出错。

解决方法：一定要开启read_only=1，并且不要用root账号在从库上操作。

常见坑

坑1：主库和从库的server-id一样

很多同学配置的时候，忘了改从库的server-id，结果主从复制启动不起来。记住：主库和从库的server-id必须不一样。

坑2：主库防火墙没开

主库的防火墙没开3306端口，导致从库连不上主库。一定要检查防火墙是不是放行了3306端口。

坑3：配置完CHANGE MASTER之后忘了START SLAVE

很多同学配置完主库和从库之后，就以为完事了，结果发现数据根本没同步。这是因为忘了执行START SLAVE启动复制线程。

坑4：SHOW MASTER STATUS之后主库还在写数据

很多同学在主库执行了SHOW MASTER STATUS之后，还在往主库写数据，导致Position变了，从库配置的时候用的是旧的Position，结果同步出错。记住：执行SHOW MASTER STATUS之后，主库就不要再写数据了。

坑5：主库和从库数据一开始就不一致

很多同学的主库已经有很多数据了，直接配置主从复制，结果从库同步的时候出错。正确的做法应该是：先把主库的数据导出，导入到从库，然后再配置主从复制。

总结

MySQL主从复制搭建的步骤：

1. 准备两台服务器，都安装好MySQL
2. 配置主库：设置server-id，开启binlog
3. 主库创建复制账号
4. 主库执行SHOW MASTER STATUS，记录File和Position
5. 配置从库：设置server-id，开启relay log
6. 从库执行CHANGE MASTER TO，指向主库
7. 从库执行START SLAVE启动复制
8. 查看从库状态，确认IO和SQL线程都是Yes
9. 在主库写数据，从库查数据，测试同步

主从复制是数据库架构的基础，学会了之后才能搞读写分离、分库分表这些更高级的东西。

遇到问题加QQ23979811 协助处理

前言

做网站开发的同学肯定遇到过这个错误：用户访问网站的时候，突然报错了，说"MySQL连接失败：Too many connections"。这时候网站就打不开了，用户都在骂娘，你急得满头大汗不知道怎么办。

这个错误其实很常见，就是MySQL的最大连接数不够用了。很多新手同学遇到这个错误就慌了，不知道怎么处理。这篇文章就把这个问题的原因和解决方法讲清楚，以后再遇到就不用慌了。

一、为什么会出现Too many connections错误

MySQL默认的最大连接数是151，这个数在小项目里够用，但是如果项目用户多了，或者连接池设置得太大，就很容易把连接数占满。

当所有连接都被占满的时候，新的请求就进不来了，这时候MySQL就会报"Too many connections"的错误。

常见原因

1. 应用程序连接池设置太大：比如你的应用配置了500个连接，但是MySQL最大连接数才151，那肯定不够用
2. 慢SQL太多：很多SQL执行得很慢，连接迟迟不释放，占着茅坑不拉屎
3. 连接没有及时释放：应用程序写完代码忘了关闭连接，导致连接泄漏
4. 突发流量：突然来一波大流量，把连接数占满了

二、查看当前连接数和最大连接数

先看看你的MySQL现在最大连接数是多少：

SHOW VARIABLES LIKE 'max_connections';

执行完之后会看到类似这样的结果：

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

再看看当前有多少个连接：

SHOW STATUS LIKE 'Threads_connected';

结果类似：

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 120   |
+-------------------+-------+

你看，当前已经用了120个连接，最大才151，再涨一点就满了。

三、临时调整最大连接数

如果现在已经出现Too many connections错误了，可以先临时把最大连接数调大一点，救个急：

SET GLOBAL max_connections = 500;

这个命令执行完之后立刻生效，不用重启MySQL。但是注意：这个修改是临时的，MySQL重启之后就会恢复成原来的值。

四、永久修改最大连接数

要永久修改的话，需要改配置文件。找到MySQL的配置文件 /etc/my.cnf，在 [mysqld] 下面添加：

[mysqld]
max_connections = 1000

修改完之后重启MySQL服务：

systemctl restart mysqld

这样以后MySQL重启之后最大连接数还是1000，不会变回去。

注意：不要设得太大

很多同学觉得既然不够用，那就设大点，直接设个10000。这是不对的。

每个连接MySQL都要分配内存，如果连接数设得太大，每个连接占的内存加起来就很多了，可能会把服务器内存吃满。

一般来说，最大连接数设到500~1000就够了，要看你的服务器内存有多大。内存大就设大点，内存小就设小点。

五、查看连接是从哪来的

如果连接数突然涨得很猛，你得看看这些连接都是从哪来的，是不是有哪个程序疯了一直在创建连接。

SELECT 
    host, 
    COUNT(*) AS count
FROM information_schema.processlist
GROUP BY host
ORDER BY count DESC;

这个命令会把每个IP的连接数统计出来，你看看是不是某个IP连接特别多，如果是的话，就去查那个程序是不是有问题。

六、查看正在执行的SQL

如果很多连接都在执行SQL，那可能是慢SQL太多导致的。看看现在都在执行什么SQL：

SHOW PROCESSLIST;

执行完之后会列出所有正在运行的连接和SQL语句。你看看有没有执行了很久还没跑完的SQL，如果有的话，那就是罪魁祸首。

七、杀掉空闲连接

如果有很多连接都是空闲的（Sleep状态），占着连接不干活，那可以把它们杀掉，释放连接。

先看看有多少个Sleep状态的连接：

SELECT COUNT(*) FROM information_schema.processlist WHERE command = 'Sleep';

如果Sleep状态的连接很多，可以把它们杀掉。不过手动一个个杀太麻烦了，可以设置一个自动断开空闲连接的参数：

SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;

这两个参数的意思是：连接如果空闲超过60秒，MySQL就自动把它断开，释放连接。

同样，这个修改是临时的，要永久生效的话需要写到配置文件里：

[mysqld]
wait_timeout = 60
interactive_timeout = 60

八、从根本上解决问题

光调大最大连接数只是治标不治本，要从根本上解决问题，还得从应用层面下手：

1. 检查应用程序是不是有连接泄漏：用完连接记得释放，不要一直占着
2. 优化慢SQL：把执行慢的SQL优化一下，减少连接占用时间
3. 合理设置连接池大小：连接池不要设得太大，比最大连接数小一点就行
4. 使用连接池中间件：比如MyCat、ProxySQL这些数据库中间件，可以统一管理连接

常见坑

坑1：改完max_connections之后重启又变回去了

很多同学用SET GLOBAL改完最大连接数之后，以为就完事了，结果MySQL一重启又变回原来的值。这是因为SET GLOBAL是临时修改，要永久生效必须改配置文件。

坑2：max_connections设得太大

很多同学一遇到连接数不够，就直接把max_connections改成10000，结果MySQL直接起不来了，或者服务器内存被吃满了。每个连接都要占内存，连接数越大，内存消耗越多，要根据服务器内存来设，不要盲目设大。

坑3：只改了max_connections，没改表连接数

其实MySQL还有一个参数叫max_user_connections，是限制单个用户的最大连接数的。如果你给某个用户限制了最大连接数，就算全局max_connections很大，那个用户的连接数到上限了还是会报错。

可以用这个命令查看：

SHOW VARIABLES LIKE 'max_user_connections';

坑4：杀掉了正在执行重要SQL的连接

很多同学一看到连接数满了，就不管三七二十一把所有连接都杀掉。结果把正在执行重要业务SQL的连接也杀了，导致业务出问题。杀连接之前一定要看清楚，不要乱杀。

总结

MySQL Too many connections错误的解决方法：

1. 先查看当前最大连接数和已用连接数
2. 临时救急：用SET GLOBAL调大max_connections
3. 永久解决：改my.cnf配置文件，设置max_connections
4. 看看连接都是从哪来的，有没有异常
5. 优化慢SQL，减少连接占用时间
6. 设置wait_timeout，自动断开空闲连接
7. 从应用层面优化，减少连接泄漏

记住：调大最大连接数只是治标，优化SQL和代码才是治本。不要一遇到连接数不够就盲目调大max_connections，先找找根本原因。

遇到问题加QQ23979811 协助处理