事务

ACID 特性

事务是数据库的逻辑工作单元，包含一组操作，确保数据一致性和完整性：

• 原子性（Atomicity）：事务中所有操作要么全部成功，要么全部失败。
• 一致性（Consistency）：事务执行后，数据库从一个合法状态转换到另一个合法状态。
• 隔离性（Isolation）：并发执行的事务相互隔离，互不干扰。
• 持久性（Durability）：事务提交后，对数据库的更改是永久性的。

事务使用步骤

1. 关闭自动提交：

   SET autocommit = 0;  -- 关闭自动提交

2. 开启事务（可选）：

   START TRANSACTION;

3. 执行 SQL 操作：增删改查。
4. 结束事务：提交或回滚。

查看自动提交状态

SHOW VARIABLES LIKE 'autocommit';

事务操作

• 提交事务：永久保存更改。

  COMMIT;

• 回滚事务：撤销未提交的操作。

  ROLLBACK;

• 事务保存点：设置还原点。

  SAVEPOINT savepoint_name;  -- 例如：SAVEPOINT one_change;

  • 回滚到保存点：

    ROLLBACK TO savepoint_name;

    注意：回滚后，后续保存点将被销毁。

事务并发问题

问题	描述
脏读	事务 T1 读取了 T2 未提交的数据，若 T2 回滚，T1 读取的数据无效。
不可重复读	事务 T1 多次读取同一字段，期间 T2 更新并提交了该字段，导致 T1 读取结果不一致。
幻读	事务 T1 多次读取同一表，期间 T2 插入新记录，导致 T1 读取到额外记录。

MySQL 事务隔离级别

隔离级别	描述	解决并发问题
READ UNCOMMITTED	读未提交数据	无
READ COMMITTED	读已提交数据（默认）	脏读
REPEATABLE READ	可重复读	脏读、不可重复读
SERIALIZABLE	串行化	脏读、不可重复读、幻读

设置隔离级别

-- 会话级设置  
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

查看当前隔离级别

SELECT @@transaction_isolation;

---

索引

索引用于加速数据查询（类似书籍目录）：

• 优点：加快查询速度，减少全表扫描，提升系统性能。
• 缺点：过多索引会增加插入、更新、删除的开销。

索引类型

类型	关键字	描述	适用场景
全文索引	FULLTEXT	对文本字段分词索引，支持关键词搜索。	文章内容、评论等大文本搜索
空间索引	SPATIAL	优化地理空间数据查询（点、线、多边形）。	地理信息系统（GIS）、地图应用
唯一索引	UNIQUE	确保字段值唯一。	主键、用户 ID 等唯一性字段
普通索引	无关键字	加速查询，允许字段值重复。	频繁作为查询条件的非唯一字段
复合索引	多字段组合	基于多个字段创建索引。	查询条件涉及多个字段的组合

创建索引

1. 建表时创建

CREATE TABLE 表名 (  
    字段1 类型,  
    字段2 类型,  
    -- 唯一索引  
    UNIQUE INDEX 索引名 (字段),  
    -- 复合索引  
    INDEX 索引名 (字段1, 字段2)  
);

示例：

CREATE TABLE students (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    name VARCHAR(255),  
    UNIQUE INDEX unique_name (name)  -- 为 name 字段创建唯一索引  
);

2. 建表后添加

-- 方式1  
ALTER TABLE 表名 ADD INDEX 索引名 (字段);  
-- 方式2  
CREATE INDEX 索引名 ON 表名 (字段);

示例：

ALTER TABLE students ADD INDEX english_index (english);  
CREATE INDEX english_index ON students (english);

查看索引

SHOW INDEX FROM 表名;

删除索引

-- 方式1  
DROP INDEX 索引名 ON 表名;  
-- 方式2  
ALTER TABLE 表名 DROP INDEX 索引名;

---

数据迁移与备份

备份数据库

mysqldump -u 用户名 -p 数据库名 > 备份文件名.sql

示例：

mysqldump -u root -p mydb > mydb_backup.sql

迁移数据库

mysql -u 用户名 -p 数据库名 < 备份文件名.sql

示例：

mysql -u root -p newdb < mydb_backup.sql

关键提示：

• 事务确保数据操作的原子性和一致性。
• 索引提升查询效率，但需平衡读写性能。
• 定期备份防止数据丢失。