四、索引优化：查询加速的核心
4.1 索引的本质
索引就像书的目录，让你不需要翻遍整本书就能找到目标。
没有索引：全表扫描，查询 100 万行需要扫描 100 万次
有索引：B+ 树查找，100 万行只需要约 20 次 IO 操作
4.2 B+ 树索引原理
B+ 树结构：
[50]
/ \
[30] [70]
/ \ / \
[20] [40] [60] [80]
↓ ↓ ↓ ↓
数据 数据 数据 数据
(叶子节点之间用链表连接，方便范围查询)
4.3 索引的最佳实践
哪些列应该建索引？
复合索引的最左前缀原则
-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);
-- ✅ 能使用索引的查询
WHERE name = '张三' -- 使用索引第1列
WHERE name = '张三' AND age = 25 -- 使用全部索引
WHERE name LIKE '张%' -- 范围查找，可使用第1列
-- ❌ 不能使用索引的查询
WHERE age = 25 -- 跳过第1列
WHERE name = '张三' OR age = 25 -- OR 导致索引失效（部分情况）
索引失效的常见场景
-- 1. 对列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024; -- ❌
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'; -- ✅
-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000; -- ❌ phone 是 VARCHAR，类型不匹配
SELECT * FROM users WHERE phone = '13800138000'; -- ✅
-- 3. 前导模糊匹配
SELECT * FROM users WHERE name LIKE '%三'; -- ❌
SELECT * FROM users WHERE name LIKE '张%'; -- ✅
-- 4. OR 条件
SELECT * FROM users WHERE name = '张三' OR age = 25; -- ⚠️ 可能失效
-- 5. NOT 条件
SELECT * FROM users WHERE name NOT IN ('张三', '李四'); -- ⚠️ 效率低
4.4 执行计划分析
使用 EXPLAIN 分析查询语句，是优化 SQL 的必备技能。
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- 输出关键字段解读：
-- type: 访问类型（性能从好到差）
-- system > const > eq_ref > ref > range > index > ALL
-- ALL 表示全表扫描，需要优化！
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- rows: 预估扫描的行数
-- Extra: 额外信息（Using filesort 表示需要额外排序，需要优化）
-- 实际优化案例
-- 慢查询
EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- type: ALL, rows: 1000000 (全表扫描)
-- 优化后
ALTER TABLE orders ADD INDEX idx_order_date (order_date);
EXPLAIN SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
-- type: range, rows: 36500 (扫描1年的数据，约3.6万行)
五、事务：要么全做，要么全不做
事务是保证数据一致性的重要机制，尤其在金融、电商等场景。
5.1 什么是事务？
事务是一组 SQL 操作，要么全部成功，要么全部失败。
经典案例：银行转账
-- 张三给李四转账100元
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user = '张三';
UPDATE accounts SET balance = balance + 100 WHERE user = '李四';
-- 如果中间出现断电或错误，可以回滚
ROLLBACK;
-- 全部成功后才提交
COMMIT;
5.2 ACID 特性
5.3 事务的隔离级别
事务隔离级别解决并发事务的问题，但隔离级别越高，性能越差。
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
三种问题解释：
-- 1. 脏读：读到未提交的数据
-- 事务A：UPDATE balance SET amount = amount - 100 WHERE id=1; -- 未提交
-- 事务B：SELECT amount FROM balance WHERE id=1; -- 读到-100（脏数据）
-- 事务A：ROLLBACK; -- 事务B读到了不存在的数
-- 2. 不可重复读：同一事务内，同一条记录两次读取结果不同
-- 事务A：SELECT amount FROM balance WHERE id=1; -- 100
-- 事务B：UPDATE balance SET amount = 200 WHERE id=1; COMMIT;
-- 事务A：SELECT amount FROM balance WHERE id=1; -- 200（前后不一致）
-- 3. 幻读：同一事务内，两次查询返回的记录数不同
-- 事务A：SELECT COUNT(*) FROM users WHERE age > 18; -- 10条
-- 事务B：INSERT INTO users(name,age) VALUES('新用户',20); COMMIT;
-- 事务A：SELECT COUNT(*) FROM users WHERE age > 18; -- 11条（多了1条）
5.4 锁机制
-- 1. 行级锁（InnoDB 默认）
-- 只锁住需要修改的行，并发性能好
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 锁定id=1的行
-- 2. 表级锁（MyISAM 默认）
-- 锁住整张表，并发性能差
LOCK TABLES users WRITE; -- 写锁
LOCK TABLES users READ; -- 读锁
-- 3. 乐观锁（通过版本号实现）
-- 适用于读多写少的场景
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5; -- 只有版本号匹配才更新
5.5 事务实战
-- 电商秒杀场景：防止超卖
START TRANSACTION;
-- 1. 查询库存（使用行锁）
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- 2. 检查库存
IF stock > 0 THEN
-- 3. 更新库存
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- 4. 创建订单
INSERT INTO orders (product_id, user_id, quantity) VALUES (1, 100, 1);
COMMIT;
ELSE
ROLLBACK;
END IF;
