事务与索引：数据库核心机制详解
1. 事务与ACID特性
(1) 事务的基本概念
事务(Transaction)是数据库操作的最小工作单元，是用户定义的一个操作序列，这些操作要么全部执行，要么全部不执行。
-- 典型的事务示例：银行转账
BEGIN TRANSACTION;UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;UPDATE accounts SET balance = balance + 500 WHERE account_id = 1002;
COMMIT;(2) ACID特性详解
| 特性 | 描述 | 实现机制 |
|---|---|---|
| 原子性 (Atomicity) | 事务是不可分割的工作单位，事务中的操作要么全部成功，要么全部失败回滚 | 通过Undo Log实现回滚 |
| 一致性 (Consistency) | 事务执行前后，数据库从一个一致性状态变到另一个一致性状态 | 由应用层和数据库约束共同保证 |
| 隔离性 (Isolation) | 并发执行的事务之间互不干扰 | 通过锁机制和MVCC实现 |
| 持久性 (Durability) | 事务一旦提交，其对数据库的改变就是永久性的 | 通过Redo Log实现 |
2. 事务隔离级别
(1) 四种标准隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 | 典型应用 |
|---|---|---|---|---|---|
| 读未提交 (Read Uncommitted) | ✓ | ✓ | ✓ | 最高 | 极少使用 |
| 读已提交 (Read Committed) | × | ✓ | ✓ | 高 | Oracle默认 |
| 可重复读 (Repeatable Read) | × | × | ✓ | 中 | MySQL InnoDB默认 |
| 串行化 (Serializable) | × | × | × | 最低 | 金融交易 |
(2) 并发问题示例
脏读：事务A读取了事务B未提交的数据，事务B回滚导致数据不一致
-- 事务A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 读取到未提交的修改-- 事务B
BEGIN;
UPDATE accounts SET balance = 1000 WHERE id = 1; -- 未提交
ROLLBACK; -- 回滚后事务A读取的数据无效不可重复读：同一事务内多次读取同一数据返回不同结果
-- 事务A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 第一次读取
-- 事务B在此修改了id=1的balance并提交
SELECT balance FROM accounts WHERE id = 1; -- 第二次读取结果不同(3) MySQL设置隔离级别
-- 查看当前隔离级别
SELECT @@transaction_isolation;-- 设置会话级隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;3. 索引原理与实现
(1) B+树索引（主流关系型数据库采用）
结构特点：
- 多路平衡搜索树，所有数据存储在叶子节点
- 叶子节点通过指针连接形成链表，支持范围查询
- 树高度通常为3-4层（千万级数据只需3次IO）
B+树 vs B树：
| 特性 | B树 | B+树 |
|---|---|---|
| 数据存储位置 | 所有节点 | 仅叶子节点 |
| 叶子节点链接 | 无 | 有双向链表 |
| 范围查询效率 | 低 | 高 |
| 适用场景 | 文件系统 | 数据库索引 |
索引创建示例：
-- 创建普通索引
CREATE INDEX idx_name ON users(name);-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);-- 查看表索引
SHOW INDEX FROM users;(2) 哈希索引
特点：
- 基于哈希表实现，O(1)时间复杂度查找
- 仅支持等值查询，不支持范围查询和排序
- Memory/Heap存储引擎默认使用哈希索引
哈希索引适用场景：
- 键值查询为主的简单查询
- 数据仓库的维度表
- 内存数据库如Redis
(3) 索引优化实践
索引选择原则：
- 高选择性列优先建索引（如用户ID比性别更适合）
- 遵循最左前缀原则（复合索引(a,b,c)只能用于a、a,b或a,b,c查询）
- 避免过度索引（每个额外索引会增加写操作开销）
索引失效场景：
-- 1. 使用函数操作索引列
SELECT * FROM users WHERE UPPER(name) = 'JOHN';-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar类型-- 3. 前导模糊查询
SELECT * FROM users WHERE name LIKE '%john%';-- 4. 使用OR条件（除非所有列都有索引）
SELECT * FROM users WHERE name = 'john' OR age = 25;4. 事务与索引的协同工作
MVCC（多版本并发控制）实现：·
- InnoDB通过Undo Log维护数据的历史版本
- 每个事务启动时获取一个递增的事务ID
- 读操作访问符合当前事务可见性的数据版本
锁机制：
| 锁类型 | 描述 | 使用场景 |
|---|---|---|
| 共享锁(S锁) | 允许并发读，阻止写 | SELECT...LOCK IN SHARE MODE |
| 排他锁(X锁) | 阻止其他任何锁 | UPDATE/DELETE/INSERT语句 |
| 意向锁 | 表级锁，提高锁检查效率 | 自动由InnoDB管理 |
死锁处理：
-- 事务1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- 事务2（同时执行）
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- 可能发生死锁，数据库会自动检测并回滚其中一个事务5. 高级主题
(1) 覆盖索引
索引包含查询所需的所有字段，无需回表
-- 创建覆盖索引
CREATE INDEX idx_covering ON users(name, age, email);-- 查询可以使用覆盖索引
SELECT name, age FROM users WHERE name = 'John' AND age > 20;(2) 自适应哈希索引
InnoDB自动为频繁访问的索引页建立哈希索引，加速查询
(3) 索引下推(ICP)
MySQL 5.6+特性，将WHERE条件过滤下推到存储引擎层
-- 复合索引(name, age)
SELECT * FROM users WHERE name LIKE 'J%' AND age = 25;
-- ICP允许在索引中直接过滤age=25的记录通过深入理解事务和索引的工作原理，可以设计出高性能、高并发的数据库应用系统。