前言

新装MySQL，要创建数据库、创建用户、授权，一套操作下来经常懵。本文整理完整命令，照着抄就行。

第一步：登录MySQL

mysql -u root -p

输入root密码登录。

第二步：创建数据库

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

说明：

• mydb 是数据库名，自己改
• utf8mb4 支持emoji，推荐用
• utf8mb4_general_ci 是排序规则

第三步：创建用户

MySQL 5.7及以前

CREATE USER 'myuser'@'localhost' IDENTIFIED BY '你的密码';

MySQL 8.0

CREATE USER 'myuser'@'localhost' IDENTIFIED BY '你的密码';

允许远程登录

CREATE USER 'myuser'@'%' IDENTIFIED BY '你的密码';

% 表示所有IP都能连，生产环境建议指定具体IP。

第四步：授权

授权所有权限（常用）

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

说明：

• mydb.* 表示mydb库下所有表
• 要授权所有库就写 *.*

只授权查询权限

GRANT SELECT ON mydb.* TO 'myuser'@'localhost';

授权指定权限

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myuser'@'localhost';

授权远程用户

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%';

第五步：刷新权限

FLUSH PRIVILEGES;

这步必须做！ 不然授权不生效。

查看和撤销权限

查看用户权限

SHOW GRANTS FOR 'myuser'@'localhost';

撤销权限

REVOKE ALL PRIVILEGES ON mydb.* FROM 'myuser'@'localhost';

删除用户

DROP USER 'myuser'@'localhost';

常见坑

坑1：创建了用户但没授权

用户登录上了，但什么都做不了。

原因： 只创建了用户，没授权。

解决： 创建用户后一定要 GRANT。

坑2：改了密码不生效

改完密码还是旧密码能登。

原因： 没 FLUSH PRIVILEGES。

解决： 执行 FLUSH PRIVILEGES;

坑3：远程连接不上

本地能登，远程连不上。

原因： 用户只允许localhost登录。

解决： 创建 '用户'@'%' 的远程用户，或者授权。

坑4：密码太简单报错

创建用户时报密码不符合策略。

原因： MySQL 5.6以后有密码强度校验。

解决： 用复杂点的密码，或者关掉密码校验。

坑5：MySQL 8.0密码加密方式变了

老客户端连不上MySQL 8.0。

原因： MySQL 8.0默认用caching_sha2_password，老客户端不支持。

解决： 创建用户时指定老的加密方式：

CREATE USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

完整一键脚本

-- 登录MySQL后执行
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY '你的密码';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

总结

三步走：

1. 建库 CREATE DATABASE
2. 建用户 CREATE USER
3. 授权 GRANT + FLUSH PRIVILEGES

记住： 授权完一定要刷新！

遇到问题加QQ23979811 协助处理

前言

大表分页查询，第一页很快，第十页就慢，第一百页直接卡死。这是MySQL分页的经典问题，本文讲怎么优化。

为什么越往后越慢

普通分页：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

MySQL 会先扫描前100010条，然后丢掉前100000条，只返回最后10条。

越往后翻，扫描的行越多，当然越慢。

优化方案一：子查询优化

先查出起始位置的id，再用id做条件：

SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
LIMIT 10;

原理： 子查询走索引，只查id列，很快。然后外层用id直接定位，不用扫那么多行。

优化方案二：游标分页（推荐）

记住上一页最后一条的id，下次从这个id之后查：

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;

-- 第二页（假设上一页最后id是100）
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

-- 第三页（假设上一页最后id是110）
SELECT * FROM orders WHERE id > 110 ORDER BY id LIMIT 10;

优点： 不管翻到第几页，都是直接定位，速度一样快。

缺点： 不能跳页，只能上一页下一页。

优化方案三：覆盖索引

只查需要的列，不要 SELECT *：

SELECT id, title, create_time FROM orders ORDER BY id LIMIT 100000, 10;

如果查询的列都在索引里，MySQL直接从索引返回数据，不用回表，快很多。

优化方案四：业务上限制深分页

根本解决： 不让用户翻到那么深。

1. 搜索结果只显示前100页
2. 用条件过滤，不要全表翻
3. 用搜索引擎（ES）代替深分页

常见坑

坑1：用了offset就一定慢

其实offset小的时候（比如前10页），差别不大。

结论： 数据量不大的时候，普通分页就行，不用过度优化。

坑2：游标分页不能用排序

游标分页只能按主键或唯一索引排序。

解决： 如果按其他字段排序，先查出对应的id列表，再用id定位。

坑3：子查询效率也不高

子查询在MySQL 5.6以前有优化器问题。

解决： MySQL 5.6以后子查询优化得很好了，放心用。

坑4：没加where条件

深分页没加where，全表扫描。

解决： 尽量加上时间范围、状态等过滤条件。

性能对比

总结

大表分页优化三板斧：

1. 小数据量：普通分页就行
2. 中等数据量：子查询优化
3. 大数据量：游标分页 + 搜索引擎

记住： 深分页不是MySQL擅长的事，数据量大了就上ES。

遇到问题加QQ23979811 协助处理

前言

导入SQL文件到MySQL，经常报各种错。本文总结最常见的导入报错和解决方法。

常用导入命令

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

或者登录MySQL后：

source /路径/xxx.sql;

常见报错及解决

报错1：Unknown database

ERROR 1049 (42000): Unknown database 'xxx'

原因： 数据库不存在。

解决： 先创建数据库：

CREATE DATABASE xxx DEFAULT CHARSET utf8mb4;

然后再导入。

报错2：Access denied

ERROR 1044 (42000): Access denied for user

原因： 用户没权限。

解决： 用root用户导入，或者给用户授权。

报错3：File not found

ERROR 1045 (28000): Access denied

或者文件找不到。

原因： 文件路径不对，或者文件权限不对。

解决：

1. 确认文件存在：ls -l /路径/xxx.sql
2. 确认文件可读：chmod 644 /路径/xxx.sql
3. 用绝对路径，别用相对路径

报错4：Table already exists

ERROR 1050 (42S01): Table 'xxx' already exists

原因： 表已经存在了。

解决： 两种方法：

1. 先删表再导入：DROP TABLE xxx;
2. SQL文件里加 DROP TABLE IF EXISTS xxx;

报错5：Duplicate entry

ERROR 1062 (23000): Duplicate entry 'xxx' for key

原因： 主键重复。

解决：

1. 先清空表再导入
2. 或者用 INSERT IGNORE 跳过重复

报错6：字符集乱码

导入后中文变成乱码。

原因： 字符集不对。

解决： 导入前设置字符集：

SET NAMES utf8mb4;
source /路径/xxx.sql;

或者命令行指定：

mysql -u root -p --default-character-set=utf8mb4 数据库名 < xxx.sql

报错7：max_allowed_packet太小

ERROR 2006 (HY000): MySQL server has gone away

原因： SQL文件太大，MySQL接收不了。

解决： 修改my.cnf，加大max_allowed_packet：

[mysqld]
max_allowed_packet = 64M

重启MySQL生效。

导入大文件技巧

方法1：分卷压缩

把大SQL文件拆成小的：

split -b 10M xxx.sql small_

然后逐个导入。

方法2：跳过外键检查

导入时临时关闭外键检查，更快：

SET FOREIGN_KEY_CHECKS = 0;
source /路径/xxx.sql;
SET FOREIGN_KEY_CHECKS = 1;

常见坑

坑1：用错用户名密码

导入时一直提示密码错误。

解决： 确认用户名密码，或者用 mysql -u root -p 交互式输入密码。

坑2：SQL文件不是UTF-8编码

导入后中文乱码。

解决： 用 file 命令查看文件编码：

file -i xxx.sql

坑3：没选对数据库

导入后数据不知道导到哪去了。

解决： 命令行一定要指定数据库名，或者先 USE 数据库名;

总结

导入SQL文件三步走：

1. 先确认数据库存在
2. 用正确的字符集导入
3. 大文件就调大max_allowed_packet

记住： 报错信息一定要看清楚，别瞎试。

遇到问题加QQ23979811 协助处理

前言

忘记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. 索引类型

• 主键索引： 主键自带，唯一且非空
• 普通索引： 最基本的索引
• 唯一索引： 字段值不能重复
• 联合索引： 多个字段组成的索引
• 全文索引： 全文搜索用

2. 创建索引

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

-- 唯一索引
CREATE UNIQUE INDEX idx_phone ON users(phone);

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

3. 索引失效情况

1）不要在索引字段上做运算：

-- 失效
SELECT * FROM users WHERE age + 1 = 20;

-- 生效
SELECT * FROM users WHERE age = 19;

2）like 左模糊：

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

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

3）联合索引不满足最左前缀：

-- 索引：(name, age)
-- 生效（从name开始）
SELECT * FROM users WHERE name = '张三' AND age = 20;

-- 失效（跳过name直接查age）
SELECT * FROM users WHERE age = 20;

4）用 or 连接：

• or 两边有一个没索引就全表扫描

4. 优化原则

• 频繁查询的字段加索引
• 区分度高的字段适合做索引
• 不要加太多索引（影响写入性能）
• 联合索引注意最左前缀
• 尽量扩展索引，不要新建索引

5. 查看索引

-- 查看表的索引
SHOW INDEX FROM users;

6. 常见问题

• 索引加了还是慢？检查是不是失效了
• 查询还是慢？用 EXPLAIN 分析
• 写入慢？索引太多了

总结

MySQL 索引优化要点：

• 常用查询字段加索引
• 注意索引失效情况
• 联合索引最左前缀
• 不要加太多索引
• EXPLAIN 分析执行计划

数据库性能优化，索引是第一步。

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 root密码忘了进不去数据库？别慌，按下面步骤操作，5分钟就能重置。

1. 先停止MySQL服务

systemctl stop mysqld

CentOS 7 默认是这个命令，如果是 mariadb 就改成 systemctl stop mariadb。

2. 跳过权限验证启动MySQL

mysqld_safe --skip-grant-tables &

这一步会跳过密码验证，直接进入MySQL，不需要输密码。

3. 无密码登录MySQL

mysql -u root

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

4. 重置root密码

USE mysql;

-- MySQL 5.7 及以上版本
UPDATE user SET authentication_string = PASSWORD('你的新密码') WHERE User = 'root';

-- 如果是 MySQL 5.6 及以下版本
UPDATE user SET password = PASSWORD('你的新密码') WHERE User = 'root';

FLUSH PRIVILEGES;

EXIT;

把 你的新密码 换成你想设置的新密码，注意密码强度。

5. 重启MySQL服务

systemctl start mysqld

6. 用新密码登录测试

mysql -u root -p

输入刚才设置的新密码，能登录就成功了。

常见坑

坑1：mysqld_safe 找不到
有些系统用的是 mysqld，试试：

mysqld --skip-grant-tables &

坑2：改完密码登录还是不行
一定要执行 FLUSH PRIVILEGES;，不然不生效。

坑3：密码太简单报错
MySQL 有密码强度要求，新密码最好是字母+数字+符号，8位以上。

坑4：MySQL 8.0 版本
MySQL 8.0 改密码的语法不一样：

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

遇到问题加QQ23979811 协助处理

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 协助处理

MySQL 索引优化技巧

索引是数据库性能的关键，建对了快10倍，建错了反而更慢。这篇讲实战技巧。

1. 索引类型

普通索引： 最基本的索引，没有限制
唯一索引： 列值不能重复
主键索引： 特殊的唯一索引，一张表只能一个
联合索引： 多个字段组成的索引

2. 建索引原则

1）where 条件经常用的字段建索引

-- 常用查询
SELECT * FROM users WHERE email = 'xxx@qq.com';

-- 给 email 建索引
CREATE INDEX idx_email ON users(email);

2）联合索引最左前缀原则

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

-- 能用索引
WHERE name = '张三'
WHERE name = '张三' AND age = 25

-- 不能用索引（跳过了name）
WHERE age = 25

3）避免建太多索引

• 索引不是越多越好
• 每个索引都占空间，影响写入速度
• 一张表索引不要超过5个

3. 索引失效场景

1）不要在索引字段上做运算

-- 坏（索引失效）
SELECT * FROM users WHERE age + 1 = 26;

-- 好
SELECT * FROM users WHERE age = 25;

2）不要用函数

-- 坏
SELECT * FROM users WHERE DATE(create_time) = '2026-10-01';

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

3）like 前导模糊

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

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

**4）避免 select ***

-- 坏（查所有字段，可能回表）
SELECT * FROM users WHERE name = '张三';

-- 好（覆盖索引）
SELECT id, name FROM users WHERE name = '张三';

4. 查看索引使用

-- 查看表索引
SHOW INDEX FROM users;

-- 用 explain 分析
EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看：

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

5. 总结

索引优化记住：

• where 字段建索引
• 联合索引最左前缀
• 别在索引字段上做运算
• 避免前导模糊查询
• 用 explain 检查

索引是数据库优化的第一步，做好了性能提升很大。

前言

网站越来越慢，不知道哪条 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. 事务的 ACID 特性

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

2. 三个并发问题

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

不可重复读： 同一个事务内，两次读同一行数据结果不一样（被别的事务修改了）

幻读： 同一个事务内，两次查询返回的行数不一样（被别的事务插入/删除了）

3. 四个隔离级别

1）读未提交（Read Uncommitted）

• 最低级别，能读到未提交的数据
• 会出现脏读、不可重复读、幻读

2）读提交（Read Committed）

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

3）可重复读（Repeatable Read）

• 同一个事务内多次读同一行结果一致
• 解决脏读、不可重复读，但还有幻读
• MySQL InnoDB 默认级别

4）串行化（Serializable）

• 最高级别，事务串行执行
• 解决所有问题，但性能最差

4. 查看和设置

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

-- 设置隔离级别（会话级别）
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 设置全局隔离级别
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

5. InnoDB 的优化

MySQL InnoDB 在可重复读级别下，通过 MVCC（多版本并发控制）和间隙锁，也解决了幻读问题，比标准 SQL 定义的更强。

6. 实际开发怎么选

• 大多数场景用默认的可重复读就够了
• 对一致性要求极高的场景（金融），用串行化
• 报表分析等读多写少的场景，可以调低到读提交提高性能

总结

MySQL 默认可重复读，日常开发不用改，理解原理就行。

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 *，避免全表扫描

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

MySQL 备份与恢复实战

数据库备份是运维的基本功，万一数据丢了，没有备份就完蛋了。这篇整理常用的备份恢复方法。

1. mysqldump 逻辑备份

最常用的备份工具，生成 SQL 文件。

备份整个数据库：

mysqldump -u root -p123456 blog > blog_$(date +%Y%m%d).sql

备份单张表：

mysqldump -u root -p123456 blog users > users.sql

备份所有数据库：

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

常用参数：

• --databases：指定多个数据库
• --single-transaction：InnoDB 一致性备份，不锁表
• --routines：备份存储过程和函数
• --triggers：备份触发器

2. 恢复数据

# 恢复整个数据库
mysql -u root -p123456 blog < blog_20261007.sql

# 登录后恢复
mysql -u root -p123456
mysql> use blog;
mysql> source /root/blog_20261007.sql;

3. 压缩备份

数据大的时候，直接存 SQL 太占空间，压缩一下：

# 备份并压缩
mysqldump -u root -p123456 blog | gzip > blog_$(date +%Y%m%d).sql.gz

# 恢复压缩备份
gunzip < blog_20261007.sql.gz | mysql -u root -p123456 blog

4. 全量+增量备份

大数据量不能每天全量备份，用 binlog 做增量。

全量备份：

mysqldump -u root -p123456 --single-transaction --master-data=2 blog > full.sql

增量恢复：

# 先恢复全量
mysql -u root -p123456 blog < full.sql

# 再按时间点恢复 binlog
mysqlbinlog --start-datetime="2026-10-07 10:00:00" \
            --stop-datetime="2026-10-07 14:00:00" \
            /var/lib/mysql/binlog.000001 | mysql -u root -p123456

5. 物理备份（冷备）

直接拷贝数据文件，速度快，但需要停库。

# 停库
systemctl stop mysqld

# 拷贝数据目录
cp -r /var/lib/mysql /backup/mysql_$(date +%Y%m%d)

# 启动
systemctl start mysqld

6. 定时备份脚本

写个脚本每天自动备份：

#!/bin/bash
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d)
USER="root"
PASS="123456"

# 创建目录
mkdir -p $BACKUP_DIR

# 备份并压缩
mysqldump -u$USER -p$PASS --single-transaction blog | gzip > $BACKUP_DIR/blog_$DATE.sql.gz

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

加到 crontab：

0 3 * * * /bin/bash /root/mysql_backup.sh

7. 恢复注意事项

• 恢复前先测试备份文件能不能用，别等真出事了才发现备份坏了
• 大表恢复慢，别在线上高峰期操作
• 字符集要一致，恢复前先检查
• 恢复前先备份当前数据，防止恢复出错

总结

MySQL 备份三件事：

• 定期备份，别偷懒
• 备份后要测试能不能恢复
• 重要数据异地备份

数据无价，备份一定要做好。

前言

做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 协助处理