mysql数据库系统学习笔记（2）

mysql事物

事物是一组完整操作的集合，事物是一个不可分割的操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一个一致性状态变到另一个一致性状态。事物是逻辑上的一组操作要么都执行要么都不执行。

在一组操作中任何一个环节出现问题都无法完成一个事物操作，例如转账操作，A给B转账，A没发出或者B没收到都不能构成一个事物

在 MySQL 中只有使用了Innodb数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理insert、update、delete语句

事务的使用场景：任何需要保证数据完整性的操作都需要使用事务。

提交事物

在mysql中有两种方式进行事物的操作

自动提交事物
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行COMMIT 操作。因此要显式地开启一个事务务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

理解为自动挡模式，自动帮你踩离合，其中的条件出现问题时候会自动回滚到事物条件前

AS:

-- 就像开自动挡汽车，MySQL帮你踩离合
UPDATE 钱包表 SET 余额=余额-648 WHERE 用户='氪金大佬';  -- 首充
UPDATE 装备表 SET 屠龙刀=1 WHERE 用户='氪金大佬';      -- 送神器
-- 如果第二条SQL报错？系统自动帮你回滚到充钱前！

小心别在AUTOCOMMIT=1时把每条SQL都变成独立事务

手动提交事务

理解为手动挡模式，每一个操作都是可以手动完成

START TRANSACTION;  -- 游戏开始！
INSERT INTO 抽奖记录 VALUES('欧皇', 'SSR');
UPDATE 概率表 SET 剩余=剩余-1 WHERE 奖品='SSR';
-- 突然发现概率表被锁了？
ROLLBACK;  -- 大喊一声「时间回溯！」（抽奖记录消失）,回滚操作
-- 或者成功时：
COMMIT;    -- 全网广播我的欧气！

-- 带案例的大总结
-- 事务
-- 事务是数据库中一个不可分割的逻辑单元
-- 用于保证一个业务中的多条sql的语句同时成功或同时失败

-- 举例：银行转账
-- A账户扣钱和B账户加钱的操作必须同时成功，或者同时失败

-- transaction
CREATE TABLE account (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,  
  username VARCHAR(20) NOT NULL UNIQUE,
  balance DECIMAL(10,2) NOT NULL,
  create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO account(username,balance)VALUES
('张三',5000),
('李四',5000),
('王五',5000);

SELECT * FROM account;


-- 事务的开始和结束
-- begin 开始一个事务(将自动提交改为手动提交)
-- 在没有事务的状态下，INSERT | UPDATE | DELETE 语句运行后数据库会自动运行commit

BEGIN:# 改手动提交

UPDATE account SET balance = balance - 500 WHERE id = 1;
UPDATE account SET balance = balance + 500 WHERE id = 2;


commit; -- 提交事务(注意：一旦事务被提交不可被回滚)
rollback; -- 回滚事务，执行rollbackk是手动回滚
          -- 系统故障，会检查所有有终端的事务并回滚

事务保存点

在一个事务中可以设置多个保存点，每个保存点都有一个唯一的名称。当事务执行到某个保存点时，可以回滚到该保存点，撤销该保存点之后的所有操作。

-- 设置事务保存点

BEGIN;

UPDATE account SET balance = balance - 100 WHERE id = 1;
SAVEPOINT s1;
UPDATE account SET balance = balance - 100 WHERE id = 1;
SAVEPOINT s2;
UPDATE account SET balance = balance - 100 WHERE id = 1;
SAVEPOINT s3;
UPDATE account SET balance = balance - 100 WHERE id = 1;
SAVEPOINT s4;

-- 上述保存了s1 - s4 四个回滚点

COMMIT;-- 全部提交，不可再回滚
ROLLBACK to SAVEPOINT s1;# s1 后面的事物会被回滚，可以再回滚后再提交
ROLLBACK to SAVEPOINT s2;# s2 后面的事物会被回滚，可以再回滚后再提交
ROLLBACK to SAVEPOINT s3;
ROLLBACK;-- 全部回滚

-- 简单来说，回滚到某个点事务并没有结束，可以继续回滚，保存点名称自定义，但是最后一个保存点后不可再回滚，一开始全部提交后也不可回滚

上述的操作可以发现，我们得先将模式转为手动提交，才能使用保存点，其实我们可以修改其默认模式为手动提交

#查看自动提交情况
show VARIABLES LIKE 'autocommit';
#查看当前自动提交模式
SELECT @@autocommit;
# 手动提交模式
SET AUTOCOMMIT=0;

但是这个方式只会作用域当前会话，其他会话还是会自动提交，如果你想一劳永逸，你可以在mysql配置文件中添加如下配置

# linux配置路径：/etc/my.cnf
# windows配置路径：C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
autocommit=0

# 重启数据库
systemctl restart mysqld

这样操作后会影响到后续的所有会话情况，没有特殊需求的话，不建议修改默认模式
如果你是需要频繁开启事务的数据库，建议修改默认模式为手动提交

事务的四大特性（ACID）

Atomicity, Consistency, Isolation, Durability

原子性：事务是最小的执行单位，事务中的所有操作不允许分割，事务的原子性确保动作要么全部完成，要么完全不起作用
一致性：执行事务前后，数据保持一致，对个实物对同一个数据的读取结果是相同的
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间的数据库是独立的
持久性：一个事务被提交后，它对数据库中的数据改变是永久的，哪怕数据库发生故障也不应该对其造成影响

事务的隔离级别

所有的事务之间保持隔离，互不影响，因为并发操作，多个用户同时访问同一个数据可能引发并发访问问题

事务隔离级别的标准定义

事务隔离级别用于控制多个并发事务之间的相互影响，主要解决以下问题：

脏读（Dirty Read）：事务A读取了事务B未提交的数据，如果事务B回滚，事务A读到的就是无效数据。
不可重复读（Non-repeatable Read）：事务A多次读取同一数据，期间事务B修改并提交了该数据，导致事务A前后读取结果不一致。（同一个事务两次读取的数据内容不一致）
幻读（Phantom Read）：事务A按条件查询数据，期间事务B插入或删除了符合条件的数据，导致事务A再次查询时结果集发生变化。（同一个事物两次读取的数据行数不一样）

数据库提供四种隔离级别，按严格程度排序：

# 查看隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
#查询默认隔离级别
SELECT @@tx_isolation;

隔离级别	脏读	不可重复读	幻读
读未提交 (Read Uncommitted)	❌可能	❌可能	❌可能
读已提交 (Read Committed)	✅避免	❌可能	❌可能
可重复读 (Repeatable Read)	✅避免	✅避免	✅避免
串行化 (Serializable)	✅避免	✅避免	✅避免

以下是我的抽象理解表示
1. 读未提交（Read Uncommitted）

场景：你偷看同桌的考试答案，但他可能正在涂改，答案可能是错的。
数据库行为：事务能读到其他事务未提交的数据（脏数据）。

2. 读已提交（Read Committed）

场景：你等同桌交卷后，才抄他的答案（确保答案已确认）。
数据库行为：事务只能读到已提交的数据，但同一事务内多次查询可能结果不同。

3. 可重复读（Repeatable Read）

场景：考试时你抄下答案后，老师不允许任何人修改答案（但可能有人偷偷塞新答案进来）。
数据库行为：事务内多次读取同一数据结果一致，但可能有新数据插入（幻读）。

4. 串行化（Serializable）

场景：考场一次只允许一个人答题，其他人排队等。
数据库行为：事务完全串行执行，杜绝所有并发问题，但性能最差。

关键注意点

隔离级别越高，安全性越强，性能越低。
MySQL默认是可重复读（但通过间隙锁避免了幻读）。
实际开发中常用读已提交或可重复读，串行化仅在极端场景使用。

更改级别

-- 以读未提交为例
# 仅修改当前会话的事物隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

# 修改全局会话窗口的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

可以在虚拟机中同时访问同一个数据库，来进行不同隔离级别的测试
以下是完整sql笔记：

SHOW VARIABLES LIKE 'transaction_isolation';

-- 修改事务的隔离级别
# 仅修改当前会话的事物隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 修改全局会话窗口的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 修改全局的隔离级别为RU
-- 在该级别下会发生脏读
-- 一个事务会读取另一个事务中未提交的数据

-- 读已提交可以避免脏读的发生
-- 它只会读取已提交的版本
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 但是会造成不可重复读和幻读的问题
-- 不可重复读： 同一个事务两次读取的数据内容不一致
-- 幻读：同一个事物两次读取的数据行数不一样
-- 有另一个事务插入或者删除数据导致的

-- 提升到RR级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 在RR级别下，会读取自己创建的快照版本
-- 快照读下会在事务第一次读时创建，事务结束后自动删除，下一次事务会创建新的快照版本
-- 他可以解决脏读，幻读，不可重复读的问题

-- 提升至S
-- 这是最高级别，串行化
-- 可以避免所有隔离问题，但是严重影响数据库性能
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

ER图数据库设计

主要组成元素

实体：现实世界中可区分的对象（如：学生、课程）
属性：实体的特征（如：学号、姓名）
关系：实体间的联系（如：选课、授课）
常用符号表示
实体：矩形 □
属性：椭圆 ○
关系：菱形 ◇
主键：下划线 _
外键：虚线或特殊标记
~~建议对照一个er图来明确对应关系~~
这里只是给出每个形状所代指的关系部分，建议去找个图对照练习，不想录入图床了，懒

索引

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。类似字典的目录，可以快速的定位到数据的位置，而不需要对整个表进行扫描

MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。
打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

索引的数据结构
数据结构的详情也可以在本站找到
hash索引，b_tree索引，full_text全文索引

索引的分类

在mysql数据库中索引可以分为以下几类
主键索引，唯一索引，常规索引，全文索引

主键索引：唯一标识每行数据，不允许NULL值，每表只能有一个（如身份证号）
表中的列设定为主键后，数据库会自动建立主键索引，索引的列的值必须是唯一的，并且不允许为空值。
唯一索引：确保列值唯一但允许NULL值，可多个（如用户邮箱）
表中的列创建了唯一约束时，数据库会自动建立唯一索引，索引的列的值必须是唯一的，但是允许为空值。
单列索引：仅针对单个字段加速查询（如按姓名找人）
非主键的列创建的索引，一个索引只能包含单个列，一个表可以有多个单列索引。
组合索引：多字段联合索引，遵循最左匹配原则（如按省+市+区定位）
一个索引包含多个列，我们在查询的时候要查询多个字段，就可以为查询的多个字段创建一个复合索引，这样就可以加快查询速度。
1.按功能特性分类

1. 主键索引（PRIMARY KEY）

特点：
- 每张表只能有一个主键索引
- 列值必须唯一且不能为NULL
- 物理存储按照主键顺序组织（聚集索引）

创建方式：

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50),
  PRIMARY KEY (id)  -- 主键索引
);

2. 唯一索引（UNIQUE KEY）

特点：
- 保证列值的唯一性
- 允许NULL值（但只能有一个NULL值）
- 一张表可以有多个唯一索引

创建方式：

CREATE TABLE users (
  email VARCHAR(100),
  UNIQUE KEY (email)  -- 唯一索引
);

3. 常规索引（普通索引/INDEX）

特点：
- 最基本的索引类型
- 仅加速查询，无约束功能
- 允许重复值和NULL值

创建方式：

1	CREATE INDEX idx_name ON users(name); -- 常规索引

4. 全文索引（FULLTEXT）

特点：
- 专门用于全文搜索
- 仅适用于MyISAM和InnoDB引擎
- 只能创建在CHAR、VARCHAR或TEXT类型列上

创建方式：

1	CREATE FULLTEXT INDEX idx_content ON articles(content);

2.按索引列数量分类

1. 单列索引

特点：
- 只包含一个列的索引
- 可以是上述任意类型（主键、唯一、普通等）

示例：

1	CREATE INDEX idx_age ON users(age); -- 单列索引

2. 组合索引（复合索引）

特点：
- 包含两个或更多列的索引
- 遵循”最左前缀原则”

示例：

1	CREATE INDEX idx_name_age ON users(name, age); -- 组合索引

索引的使用场景

那些情况需要创建索引

1.主键自动创建唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引

那些情况不需要创建索引

表的数据太少
经常增删改的表
where条件里用不到的字段不建立索引

索引的优缺点

优点：

加快数据查询速度，类似字典的目录索引，体改数据查询的效率，

缺点：

创建索引和维护索引需要耗费时间，且伴随表中的数据增加，所消耗的时间也随之增加
索引需要占用磁盘空间
索引提高了查询的效率，但是同时会降低表更新的速度

视图

视图是一个虚拟的表，它的内容是由查询定义的，而不是实际存储在数据库中的数据。它是基于一个或多个表的查询结果，并且可以像表一样进行查询和操作。内容是从其他表中选择，过滤，或者计算出来的。

视图的作用
将复杂的查询结果进行封装，简化查询操作，提高数据安全性，支持数据共享，减少重复查询，提高查询效率。

简化复杂查询：可以将复杂的查询封装成视图，方便其他用户使用
提高数据安全性：可以通过视图限制用户对数据的访问权限

锁

在并发访问的情况下，多个用户同时对同一个数据进行操作，可能会导致数据不一致的情况，为了保证数据的一致性，数据库系统引入了锁机制。某些情况下，锁的应用必不可少。

目的
在mysql中，锁是数据库管理系统中用于控制并发访问的一种机制，用于确保在多个事务同时访问同一资源时，数据的一致性和完整性，mysql中的锁分为三类

全局锁（Global Lock）：
表级锁（Table Lock）：
行级锁（Row Lock）：

-- 查看等待锁超时的时间
SHOW VARIABLES LIKE '%lock_wait_timeout%';
select lock_wait_timeout;
-- 设置等待锁超时时间为10秒
SET lock_wait_timeout = 10;

1. 全局锁（Global Lock）

全局锁会让所有的表都加上一个读锁，阻止对这些表的写操作，包括增删改查，但是可以对表进行查询操作，这对于需要对整个数据库进行维护或者备份的场景非常有用。

全局锁就像封城，什么都不可用，主要用于数据库备份和维护，但是备份完记得解封，要不然长期锁着，其他事务就无法进行操作了。（业务停滞）

-- 加全局锁
FLUSH TABLES WITH READ LOCK;
-- 释放全局锁
UNLOCK TABLES;

2. 表级锁（Table Lock）

每次操作锁住整张表，其他事务不能对该表进行任何操作，包括增删改查，但是可以对表进行查询操作，这对于需要对整个表进行维护或者备份的场景非常有用。

表锁就像上课锁门，别人不能干扰，但是可以旁听，但是不能上讲台，只能看。（业务停滞）

分为两类：

读锁(共享锁)：
其他事务可以对表进行查询操作，但是不能对表进行增删改操作，常用于需要对表进行查询操作的场景，可以多个事务同时持有读锁，但是不能同时持有写锁。

公开课允许旁听，但不让改PPT
写锁（排他锁）：
其他事务不能对表进行任何操作，包括增删改查，但是可以对表进行查询操作，常用于需要对表进行维护或者备份的场景

老师改卷子时锁门，谁都不让进

读锁和写锁的区别在于，读锁可以同时被多个事务持有，而写锁只能被一个事务持有。

读锁

-- 加表锁
LOCK TABLES 表名 READ/WRITE;
-- 读锁
LOCK TABLES 表名 READ;
-- 解锁表
UNLOCK TABLES;
-- 查看当前表锁数量
SHOW OPEN TABLES WHERE In_use > 0;

写锁

-- 加表锁
LOCK TABLES 表名 READ/WRITE;
-- 写锁
LOCK TABLES 表名 WRITE;
-- 解锁表
UNLOCK TABLES;
```  
**特点：**
排他性：写锁是排他的，意味着同一时刻只有一个事务可以持有写锁，其他事务或者会话都不能同时对该资源进行操作

### 3. 行级锁（Row Lock）
每次操作锁住对应一行数据
>行锁就像图书馆抢座位，占座方式还有三大种

行锁又分为：
**行锁，间隙锁，临键锁**

**行锁：**
行锁是最基本的锁，锁定的是一行数据，其他事务不能对该行数据进行任何操作，在事务结束后会自行释放
>你占了3号座（WHERE id=3），别人不能坐但能坐4号座

**间隙锁：**
间隙锁是一种特殊的行锁，锁定的是一个区间，其他事务不能对该区间内的数据进行任何操作
>你占了3号到5号的座位，别人不能坐（这可以解决幻读问题）

**临键锁：**
临键锁是行锁和间隙锁的结合，锁定的是一个区间，其他事务不能对该区间内的数据进行任何操作
>我把着3号到5号的座位，还顺便把插座给占了（锁定记录+间隙）

显示隐式又分为两种类型：

**显式锁（主动宣称）**使用SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 这种通过在SQL语句后添加关键字的的加锁形式，称为显式锁。
```sql
-- 加行锁
SELECT * FROM 表名 WHERE 条件 FOR UPDATE;
-- 霸道总裁式宣言："这排座位我包了！"上了写锁
SELECT * FROM orders WHERE user_id=1 FOR UPDATE;

-- 温和派宣言："可以一起看，但不能改"上了读锁
SELECT * FROM products LOCK IN SHARE MODE;

-- 条件这个值很宽泛，可能会锁定多行数据
select * from orders where user_id in (1,2,3) for update; #范围锁

隐式锁（cos地雷，自动触发）使用UPDATE、DELETE、INSERT等操作时，Innodb会自动锁定涉及的记录常见的SQL语句，这种加锁方式称为隐式锁。

UPDATE/DELETE：像自动感应门，碰到就锁（自动会对更新的行加写锁）
INSERT：像新人报到自动领座位牌

以下是各种行锁的区别对照表，按照（SQL,行锁类型，备注）进行；

SQL语句	行锁类型	备注
SELECT … FOR UPDATE	排他锁	会锁定查询到的所有行，其他事务不能对这些行进行修改或删除
SELECT… LOCK IN SHARE MODE	共享锁	会锁定查询到的所有行，其他事务可以对这些行进行读取，但不能修改或删除
UPDATE	排他锁	会锁定查询到的所有行，其他事务不能对这些行进行修改或删除

悲观锁和乐观锁

悲观锁（Pessimistic Lock）：
悲观锁是一种保守的加锁策略，认为在并发环境中，数据很可能会被其他事务修改，因此在对数据进行操作前，会先对数据进行加锁，确保其他事务不能对该数据进行操作，直到当前事务完成操作并释放锁。

悲观者认为大概率被其他人干涉，尝试竞争冲突，所以用锁来保护数据不被其他事务干扰。

乐观锁（Optimistic Lock）：
乐观锁是一种假设在并发环境中，数据不会被其他事务修改的加锁策略，因此在对数据进行操作时，不会对数据进行加锁，而是在提交操作时检查数据是否被其他事务修改过，如果被修改过，则回滚操作，否则提交操作。

乐观者认为大概率不会产生竞争冲突，所以不用锁，但是会留个心眼，在提交数据后会检查数据是否被其他事务干涉，若被干涉就回滚操作。

死锁机制

死锁定义：
死锁是指两个或多个事务在执行过程中，由于竞争资源而造成的一种僵局，每个事务都在等待其他事务释放资源，而其他事务又在等待第一个事务释放资源，从而导致恶性循环，最终导致所有事务都无法继续执行。

就像两个人在争夺资源，一个人占了资源A，另一个人占了资源B，两个人都在等待对方释放资源，从而导致死锁。

死锁产生的条件：

互斥条件：一个资源每次只能被一个事务占用
请求与保持条件：一个事务在请求新的资源的同时，保持对已占有的资源的占用
不剥夺条件：事务已占有的资源，在未使用完之前，不能被其他事务强行剥夺
循环等待条件：若干事务之间形成一种头尾相接的循环等待资源关系

数据库会干涉造成死锁的条件，强制回滚，释放事务内的锁，以便让其他事务可以顺利获得锁继续运行

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

我们作为相关从业人员应该在源头避免死锁的产生

方案一；按相同的顺序来更新数据
方案二；一次性锁定事务类所需要更新的所有资源
方案三：使用乐观锁

想用好锁的注意事项：
1.锁的粒度越小越好（能用行锁不用表锁）
2.持有时间越短越好（快锁快放）
3.避免死锁就像避免厕所占坑打架：

按固定顺序访问表
事务不要太长

mvcc机制（多版本并发控制）

这是mysql数据库提供的一张数据多版本控制表，用于存储不同事务对同一数据的不同版本。

快照读（Snapshot Read）：
快照读是指在事务开始时，根据事务的开始时间点，读取数据库中某个时刻的快照数据。

当前读（Current Read）：
当前读是指在事务中，读取最新版本的数据。

基本就到这里，有什么需要额外补充的地方我会后续更新

mysql数据库系统学习笔记（2）

mysql事物

提交事物

事务保存点

事务的四大特性（ACID）

事务的隔离级别

事务隔离级别的标准定义

更改级别

ER图数据库设计

索引

索引的分类

1.按功能特性分类

1. 主键索引（PRIMARY KEY）

2. 唯一索引（UNIQUE KEY）

3. 常规索引（普通索引/INDEX）

4. 全文索引（FULLTEXT）

2.按索引列数量分类

1. 单列索引

2. 组合索引（复合索引）

索引的使用场景

索引的优缺点

视图

锁

1. 全局锁（Global Lock）

2. 表级锁（Table Lock）

悲观锁和乐观锁

死锁机制

mvcc机制（多版本并发控制）