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;    -- 全网广播我的欧气！

事物的四大特性

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，多个事务并发时，一个事物不能影响其他事务的影响，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事物的隔离级别

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

事务隔离级别的标准定义

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

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

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

隔离级别	脏读	不可重复读	幻读
读未提交 (Read Uncommitted)	❌可能	❌可能	❌可能
读已提交 (Read Committed)	✅避免	❌可能	❌可能
可重复读 (Repeatable Read)	✅避免	✅避免	❌可能
串行化 (Serializable)	✅避免	✅避免	✅避免
以下是我的抽象理解表示
1. 读未提交（Read Uncommitted）

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

2. 读已提交（Read Committed）

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

3. 可重复读（Repeatable Read）

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

4. 串行化（Serializable）

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

关键注意点

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

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值
• 创建方式：

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

4. 全文索引（FULLTEXT）

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

  CREATE FULLTEXT INDEX idx_content ON articles(content);

2.按索引列数量分类

1. 单列索引

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

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

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

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

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

索引的使用场景

那些情况需要创建索引

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

那些情况不需要创建索引

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

索引的优缺点

优点：

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

缺点：

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

视图

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

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

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

锁

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

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

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

1. 全局锁（Global Lock）

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

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

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

2. 表级锁（Table Lock）

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

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

分为两类：

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

  公开课允许旁听，但不让改PPT

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

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

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

读锁

-- 加表锁
LOCK TABLES 表名 READ/WRITE;
-- 读锁
LOCK TABLES 表名 READ;

写锁

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

### 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;

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

• ​UPDATE/DELETE​：像自动感应门，碰到就锁
• ​INSERT​：像新人报到自动领座位牌

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

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

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

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

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