MYSQL 事务 与 索引

MySQL事务

事务是一组SQL语句的执行，被视为一个单独的工作单元

事务必须满足以下4个条件：ACID

• 原子性（Atomicity）不可分割性，事务是数据库中最小的工作单位，作为一个整体执行，若事务执行的过程中发生错误，进行回滚（Rollback）到事务开始前的状态，就像事务没有被执行过
• 一致性（Consistency）：事务必须使得数据库从一个一致性状态变为另一个一致性状态。
• 隔离性（Isolation）独立性，数据库系统提供一定的隔离级别，使事务在不受外部并发操作影响的“隔离”环境下运行，以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
• 持久性（Durability）：旦事务被提交，它对数据库的修改就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

MySQL的自动提交模式

SET AUTOCOMMIT=0 # 禁止自动提交
SET AUTOCOMMIT=1 # 开启自动提交

使用事务

使用BEGIN和START TRANSACTION 开启一个事务，接下来的数据库操作都在这个事务内执行

START TRANSACTION;
# 或者 BEGIN;
INSERT INTO users (username, password, birthday, is_activate)  
VALUES ('newuser123', 'hashedpassword', '1990-01-01', FALSE);
UPDATE users SET is_activate = FALSE WHERE id=15;

# 操作顺利
COMMIT;

# 发生数据错误 回滚
ROLLBACK;

【提交/回滚】

• 如果数据库脚本执行顺利，提交事务，完成数据库操作
• 如果在执行数据库脚本的过程中发生了错误，使用ROLLBACK操作，回滚这次事务，使得整个事务的操作不受影响

提交/回滚之后，数据库事务结束，再次使用事务的时候需要BEGIN开启一个事务，执行结束后进行提交或者回滚，结束事务；从而完成一个事务的完整生命周期

1 隔离级别

MySQL支持四种事务隔离级别，不同的隔离级别可以允许不同级别的事务并发，可能导致不同程度的数据脏读、不可重复读和幻读问题。

• READ UNCOMMITTED（读未提交）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读或幻读。
• READ COMMITTED（读已提交）：允许读取已经提交的数据，可以阻止脏读，但是不可重复读和幻读仍有可能发生。
• REPEATABLE READ（可重复读）：MySQL的默认事务隔离级别。它确保在同一事务内多次读取同样记录的结果是一致的，但是无法解决幻读问题。
• SERIALIZABLE（可串行化）：最高的隔离级别，它通过强制事务串行执行，来避免脏读、不可重复读以及幻读。

通过调整事务的隔离级别，可以在一定程度上平衡系统的并发处理能力和数据的一致性。

1 脏读

脏读（Dirty Read）是数据库事务并发控制中的一个问题，它发生在一个事务读取了另一个事务未提交的数据时。这种情况下的数据被称为“脏数据”，因为它可能会因为原始事务的回滚（rollback）而被撤销，从而导致读取该数据的事务得到了一个无效或不一致的数据视图。

数据不一致性：脏读违反了数据库事务的隔离性原则，因为它允许一个事务看到另一个事务未完成的更改。

潜在风险：如果原始事务因为某种原因（如违反约束、权限问题等）而回滚，那么基于脏数据所做的任何操作都可能是无效的，甚至可能导致数据错误或应用程序崩溃。

2 幻读

幻读是指在一个事务中，多次执行同一查询，但在两次查询之间，另一个事务插入了新的数据行，导致第一次事务的两次查询结果集大小不一致。这种现象就像是在查询过程中出现了“幻觉”，原本不存在的数据行在后续查询中突然出现了。

特点与影响：

• 关注点：幻读关注的是整个数据集的变化，即数据行的增加或减少
• 操作类型：通常由INSERT操作引起，但也可能由DELETE操作（如果删除了大量数据，使得原本不在查询范围内的数据进入查询范围）引起
• 隔离级别：在MySQL中，幻读在可重复读（Repeatable Read）隔离级别及以上可以被避免，但在读已提交（Read Committed）隔离级别下仍可能发生

【影响】幻读可能导致事务在执行过程中得到不一致的数据视图，特别是在需要精确控制数据集大小和内容的情况下。

3 不可重复读

不可重复读是指在一个事务中，多次读取同一数据，但在两次读取之间，另一个事务修改了该数据，导致第一次事务的两次读取结果不一致。

特点与影响：

• 关注点：不可重复读关注的是单个数据行的变化
• 操作类型：通常由UPDATE或DELETE操作引起
• 隔离级别：在MySQL中，不可重复读在读已提交（Read Committed）隔离级别及以上可以被避免，但在读未提交（Read Uncommitted）隔离级别下仍可能发生

【影响】不可重复读可能导致事务在执行过程中得到不一致的数据视图，影响数据的准确性和一致性。

---

3 MySQL索引

数据库索引是一种数据结构（常常是树结构），用于加快数据库的查询提升数据库查询性能；

MySQL索引的建立对于MySQL高效运行很重要

1 创建索引

直接在表上创建

CREATE INDEX user_id_name ON users (id, username);

修改表的方式创建

ALTER TABLE users ADD INDEX user_id_name (id, username);

2 删除索引

直接删除表的索引

DROP INDEX user_id_name ON users;

修改表格的方式删除

ALTER TABLE users DROP INDEX user_id_name;

---

参考

MYSQL基础https://www.bilibili.com/video/BV15m421T7RQ