第11章：事务与索引（实战必备）

事务和索引是 MySQL 中两个非常重要的概念，它们分别用于保证数据的一致性和提高查询性能。本章将详细介绍这两个核心知识点。

11.1 事务（Transaction）

事务是一组 SQL 操作的集合，这些操作要么全部成功，要么全部失败，是保证数据一致性的重要机制。

11.1.1 事务的核心特性（ACID）

特性	含义	说明
A (Atomicity)	原子性	事务是一个不可分割的整体，要么全部执行，要么全部不执行
C (Consistency)	一致性	事务执行前后，数据库状态保持一致
I (Isolation)	隔离性	多个事务之间相互隔离，互不影响
D (Durability)	持久性	事务一旦提交，结果永久保存

通俗理解：

原子性：要么全做，要么全不做
一致性：操作前后数据状态一致
隔离性：多个事务互不干扰
持久性：结果永久保存

11.1.2 事务的使用

基本语法：

sql

-- 开始事务
START TRANSACTION;  -- 或 BEGIN;

-- 执行SQL操作
-- 操作1
-- 操作2
-- 操作3

-- 提交事务（成功）
COMMIT;

-- 回滚事务（失败）
ROLLBACK;

示例：转账操作

sql

-- 创建账户表
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2)
);

-- 插入数据
INSERT INTO accounts VALUES 
(1, '张三', 1000.00),
(2, '李四', 1000.00);

-- 转账操作（张三给李四转500元）
START TRANSACTION;

-- 1. 张三账户减少500
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- 2. 李四账户增加500
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- 检查操作是否正确
SELECT * FROM accounts;

-- 如果正确，提交事务
COMMIT;
-- 如果出错，回滚事务
-- ROLLBACK;

11.1.3 事务的应用场景

转账操作：确保扣款和入账同时成功
订单提交：确保订单创建和库存减少同时成功
用户注册：确保用户信息和关联数据同时创建
数据迁移：确保数据的一致性

11.2 索引（Index）

索引是一种数据结构，用于提高数据库查询效率，类似于书籍的目录。

11.2.1 索引的作用

加快查询速度：通过索引直接定位数据，避免全表扫描
加速排序：索引本身是有序的，排序操作更高效
加速分组：分组操作也需要排序，索引可以提供帮助

11.2.2 索引的创建

基本语法：

sql

-- 创建普通索引
CREATE INDEX 索引名 ON 表名(字段);

-- 创建唯一索引（值不能重复）
CREATE UNIQUE INDEX 索引名 ON 表名(字段);

-- 创建复合索引（多个字段）
CREATE INDEX 索引名 ON 表名(字段1, 字段2);

-- 删除索引
DROP INDEX 索引名 ON 表名;

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

示例：

sql

-- 为学生表的姓名字段创建索引
CREATE INDEX idx_student_name ON students(name);

-- 为订单表的订单日期字段创建索引
CREATE INDEX idx_order_date ON orders(order_date);

-- 为用户表的用户名创建唯一索引
CREATE UNIQUE INDEX idx_user_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_student_class_score ON students(class_id, score);

11.2.3 常用索引类型

索引类型	特点	适用场景
主键索引	唯一且非空，自动创建	唯一标识记录
唯一索引	值唯一，允许NULL	确保字段唯一性
普通索引	无特殊限制	加速查询
复合索引	多个字段组合	多字段查询
全文索引	支持全文搜索	文本搜索

11.2.4 索引使用注意事项

避免过度索引：
- 索引会占用磁盘空间
- 插入、更新、删除操作会维护索引，影响性能
合理选择索引字段：
- 频繁查询的字段
- 经常用于 WHERE、ORDER BY、GROUP BY 的字段
- 基数高的字段（值的多样性高）
复合索引的最左前缀原则：
- 复合索引 (a, b, c) 会自动包含 (a)、(a,b) 索引
- 查询条件必须从最左边开始使用
避免索引失效：
- 不要在索引字段上使用函数
- 不要对索引字段进行计算
- 避免使用 !=、<> 操作符
- 避免使用 IS NULL、IS NOT NULL
- 避免使用 LIKE '%xxx'（前缀模糊查询）

11.3 实操案例

案例1：事务实现转账功能

完整流程：

sql

-- 1. 开始事务
START TRANSACTION;

-- 2. 检查转账人余额
SELECT balance INTO @balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 3. 检查余额是否足够
IF @balance >= 500 THEN
    -- 4. 执行转账
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
    
    -- 5. 提交事务
    COMMIT;
    SELECT '转账成功' AS message;
ELSE
    -- 6. 回滚事务
    ROLLBACK;
    SELECT '余额不足' AS message;
END IF;

案例2：创建索引优化查询

场景：查询学生表中特定班级的学生

未创建索引时：

sql

-- 执行时间：0.12秒
SELECT * FROM students WHERE class_id = 1 ORDER BY score DESC;

创建索引后：

sql

-- 创建复合索引
CREATE INDEX idx_class_score ON students(class_id, score);

-- 执行时间：0.01秒
SELECT * FROM students WHERE class_id = 1 ORDER BY score DESC;

使用 EXPLAIN 分析查询计划：

sql

EXPLAIN SELECT * FROM students WHERE class_id = 1 ORDER BY score DESC;

执行计划分析：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	students	range	idx_class_score	idx_class_score	4	const	20	Using index condition; Using filesort

11.4 新手易错点

❌ 错误1：事务使用不当

sql

-- 错误：没有明确的提交或回滚
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- 忘记 COMMIT 或 ROLLBACK

-- 正确：明确提交
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

❌ 错误2：索引滥用

sql

-- 错误：为所有字段创建索引
CREATE INDEX idx_student_id ON students(id);  -- 主键自动有索引
CREATE INDEX idx_student_name ON students(name);
CREATE INDEX idx_student_age ON students(age);
CREATE INDEX idx_student_score ON students(score);

-- 正确：只为常用查询字段创建索引
CREATE INDEX idx_student_class_score ON students(class_id, score);

❌ 错误3：复合索引使用不当

sql

-- 错误：违反最左前缀原则
CREATE INDEX idx_class_age ON students(class_id, age);
SELECT * FROM students WHERE age = 18;  -- 不会使用索引

-- 正确：遵循最左前缀原则
SELECT * FROM students WHERE class_id = 1 AND age = 18;  -- 使用索引

❌ 错误4：索引字段使用函数

sql

-- 错误：索引字段使用函数，导致索引失效
CREATE INDEX idx_student_name ON students(name);
SELECT * FROM students WHERE LOWER(name) = 'zhangsan';  -- 索引失效

-- 正确：直接使用字段
SELECT * FROM students WHERE name = 'ZhangSan';

11.5 本章小结

事务总结

操作	语法	作用
开始事务	`START TRANSACTION`	标记事务开始
提交事务	`COMMIT`	保存事务结果
回滚事务	`ROLLBACK`	撤销事务操作
保存点	`SAVEPOINT`	设置事务保存点

索引总结

类型	语法	特点
普通索引	`CREATE INDEX`	加速查询
唯一索引	`CREATE UNIQUE INDEX`	保证唯一性
复合索引	`CREATE INDEX (a,b)`	多字段查询
主键索引	自动创建	唯一标识

最佳实践：

事务：用于需要保证数据一致性的操作
索引：为频繁查询的字段创建，避免过度索引
性能：定期分析查询计划，优化索引使用

11.6 课后练习

使用事务实现一个完整的转账操作
为学生表的 name 字段创建索引
为订单表的 order_date 字段创建索引
创建一个复合索引 (class_id, score) 并测试其使用
分析一个查询语句的执行计划，查看是否使用了索引

参考答案：

sql

-- 练习1
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

-- 练习2
CREATE INDEX idx_student_name ON students(name);

-- 练习3
CREATE INDEX idx_order_date ON orders(order_date);

-- 练习4
CREATE INDEX idx_class_score ON students(class_id, score);
SELECT * FROM students WHERE class_id = 1 ORDER BY score DESC;

-- 练习5
EXPLAIN SELECT * FROM students WHERE class_id = 1 ORDER BY score DESC;

第11章：事务与索引（实战必备） ​

11.1 事务（Transaction） ​

11.1.1 事务的核心特性（ACID） ​

11.1.2 事务的使用 ​

11.1.3 事务的应用场景 ​

11.2 索引（Index） ​

11.2.1 索引的作用 ​

11.2.2 索引的创建 ​

11.2.3 常用索引类型 ​

11.2.4 索引使用注意事项 ​

11.3 实操案例 ​

案例1：事务实现转账功能 ​

案例2：创建索引优化查询 ​

11.4 新手易错点 ​

❌ 错误1：事务使用不当 ​

❌ 错误2：索引滥用 ​

❌ 错误3：复合索引使用不当 ​

❌ 错误4：索引字段使用函数 ​

11.5 本章小结 ​

事务总结 ​

索引总结 ​

11.6 课后练习 ​