2026-08-29 · MySQL 学习笔记 06

MySQL 索引、事务与备份恢复

1. 索引（让查询变快）

是什么

索引相当于书的目录：不建索引查数据是逐行翻（全表扫描），建了索引直接定位。表小（几千行）感觉不到，数据量大了才会看出差别。

怎么建

最常用的：

-- 单列索引：经常按 name 查，就给 name 建
CREATE INDEX idx_goods_name ON goods(name);

-- 唯一索引：列值不能重复，同时加速查询（建表时用 UNIQUE 等价）
CREATE UNIQUE INDEX idx_member_phone ON member(phone);

查看表上所有索引：

SHOW INDEX FROM goods;

什么时候知道要建

情况	做法
经常出现在 WHERE 里的列	建索引
经常用于 JOIN 关联的列（外键列）	建索引
只有几万行的小表	先不建，够快
频繁 UPDATE/DELETE 的列	少建（写数据和维护索引有代价）

判断有没有用上索引、查询慢在哪，用 EXPLAIN SELECT ...; 看 type 列——ALL 是全表扫描（慢），ref/range 是走索引（好）。看懂这一列就够了，其余细节不要深挖。

EXPLAIN SELECT * FROM goods WHERE name = '可乐';

2. 事务（一组操作要么全成要么全没）

为什么需要

转账：A 扣 10 块、B 加 10 块，两个动作之间断电/报错了怎么办？事务保证：要么两步都成功（提交），要么一步都不算数（回滚）。

用法

START TRANSACTION;                              -- 开启
UPDATE member SET balance = balance - 10 WHERE id = 1;   -- A 扣钱
UPDATE member SET balance = balance + 10 WHERE id = 2;   -- B 加钱
COMMIT;                                          -- 确认，落盘生效

中途发现问题，不 COMMIT，直接回滚：

START TRANSACTION;
UPDATE member SET balance = balance - 999 WHERE id = 1;
ROLLBACK;                                        -- 撤销，余额没变

自动提交与手动事务

其实 MySQL 每条 SQL 默认自动提交（autocommit=1），单条语句本身就是事务。需要"多条一起成/一起败"才手动开事务。Spring Boot 里事务用 @Transactional 注解，SQL 层面理解了即可。

3. 备份与恢复

备份（导出）

mysqldump -uroot -proot -h127.0.0.1 --default-character-set=utf8mb4 --set-gtid-purged=OFF 库名 > 备份文件.sql

# 例：备份 mysql_study 库
mysqldump -uroot -proot -h127.0.0.1 --default-character-set=utf8mb4 --set-gtid-purged=OFF mysql_study > backup.sql

• --default-character-set=utf8mb4：中文不乱码
• --set-gtid-purged=OFF：MySQL 8 必须加，否则恢复时报 GTID_PURGED cannot be changed

恢复（导入）

# 先建好空库，再导进去
mysql -uroot -proot -h127.0.0.1 -e "CREATE DATABASE IF NOT EXISTS 库名 DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -proot -h127.0.0.1 --default-character-set=utf8mb4 库名 < backup.sql

一次完整流程

# 1. 灌点数据 → 2. 备份 → 3. 删库 → 4. 重建空库 → 5. 恢复
mysqldump -uroot -proot -h127.0.0.1 --set-gtid-purged=OFF mysql_study > backup.sql
mysql -uroot -proot -h127.0.0.1 -e "DROP DATABASE mysql_study;"
mysql -uroot -proot -h127.0.0.1 -e "CREATE DATABASE mysql_study DEFAULT CHARACTER SET utf8mb4;"
mysql -uroot -proot -h127.0.0.1 mysql_study < backup.sql
mysql -uroot -proot -h127.0.0.1 -e "SELECT COUNT(*) FROM mysql_study.goods;"   # 数据回来了

学习阶段定期导一遍练习库，出问题 10 秒还原。

4. 常见问题

现象	原因	解法
恢复报 GTID_PURGED 错	备份时没加 --set-gtid-purged=OFF	重导一份，或编辑备份文件删掉 SET @@GLOBAL.GTID_PURGED 那行
恢复后中文乱码	备份/导入字符集不一致	两边都加 --default-character-set=utf8mb4
ALTER USER 想起不起密码	忘了自己密码	停服务 → --skip-grant-tables 启动 → 改密码（工作里让 DBA 干，学习碰一次即可）