约束、索引与事务：同时守住正确性和查询效率

问题与目标

能写 SQL 不等于数据可靠。重复名称、孤立任务、半完成写入和慢查询，都需要数据库层面的约束、索引与事务来处理。

完成标准：能区分约束与索引，读取基础 EXPLAIN 结果，用事务完成原子修改，并理解隔离级别是在一致性和并发之间取舍。

核心概念

主键、唯一、非空和外键是数据规则；索引是帮助数据库定位记录的数据结构。唯一约束通常依赖唯一索引实现，但“保证规则”和“提升访问路径”仍是不同目的。

联合索引 (project_id, status, created_at) 通常适合从最左列开始的查询条件。索引不是越多越好：每个索引占空间，也增加写入维护成本。

事务满足 ACID：原子性、一致性、隔离性、持久性。常见并发现象包括脏读、不可重复读和幻读。隔离越强通常并发代价越高；MySQL InnoDB 默认隔离级别应在实际环境中查询确认。

可运行实现

为常用查询增加联合索引：

USE engineering_notes;

CREATE INDEX idx_tasks_project_status_created
ON tasks (project_id, status, created_at);

EXPLAIN
SELECT id, title, created_at
FROM tasks
WHERE project_id = 2 AND status = 'todo'
ORDER BY created_at DESC;

关注 type、key、rows 和 Extra，并比较加索引前后。小表仍可能全表扫描，因为优化器判断扫描更便宜，这不代表索引失效。

同一个联合索引可以用三组查询观察最左列影响：

EXPLAIN SELECT * FROM tasks
WHERE project_id = 2;

EXPLAIN SELECT * FROM tasks
WHERE project_id = 2 AND status = 'todo';

EXPLAIN SELECT * FROM tasks
WHERE status = 'todo';

第三条跳过了 project_id，通常无法像前两条那样有效利用该联合索引。判断时以实际 key、扫描行估算和执行统计为准，不能只背“最左前缀”四个字。

用事务同时创建项目和首个任务：

START TRANSACTION;

INSERT INTO projects (name) VALUES ('transaction-demo');
SET @project_id = LAST_INSERT_ID();
INSERT INTO tasks (project_id, title)
VALUES (@project_id, 'verify atomic write');

COMMIT;

若第二条写入失败，执行：

ROLLBACK;

查看当前事务隔离级别：

SELECT @@transaction_isolation;

索引实验的输入是查询条件，输出是 EXPLAIN 选择的访问路径；事务实验的输入是一组关联写入，输出是全部提交或全部回滚后的数据库状态。两者都应通过查询结果验证，不能只以“SQL 没报错”为完成标准。

隔离行为需要两个会话才能观察。先准备一个任务，然后按顺序执行：

-- 会话 A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT status FROM tasks WHERE id = 1;

-- 会话 B
UPDATE tasks SET status = 'doing' WHERE id = 1;
COMMIT;

-- 回到会话 A
SELECT status FROM tasks WHERE id = 1;
COMMIT;

在 READ COMMITTED 下，会话 A 的两次读取可能不同，这就是不可重复读。改用其他隔离级别重复实验前，应恢复测试数据并确认存储引擎；不要在生产表上做并发实验。

常见问题与排查

• 给每列都建索引：写入变慢且占空间；先从真实查询和 EXPLAIN 出发。
• 联合索引跳过最左列：可能无法有效利用该索引，需要根据查询模式重排或新增索引。
• 把 EXPLAIN rows 当精确行数：它是优化器估算，应结合实际执行统计和数据分布。
• 开启事务后忘记提交：连接持有锁和未提交数据；控制事务应短小明确。
• 捕获错误却继续 COMMIT：任何一步失败都应回滚整个业务单元。
• 外键级联删除范围过大：建模时明确生命周期，重要数据可选限制删除或软删除。
• 事务中等待很久：可能在等待其他事务持有的锁；先查看活跃事务和锁等待，再定位未提交的会话，不要直接重启数据库。

小结

约束负责拒绝无效数据，索引负责提供合适访问路径，事务负责让一组操作共同成功或失败。三者都应从具体业务不变量和查询模式出发。