MySQL事务

MySQL-事务-介绍与操作

:::info

• 场景：学工部整个部门解散了，该部门及部门下的员工都需要删除了？
• 问题：如果删除部门成功了，而删除该部门的员工时失败了，此时就造成了数据的不一致。
• 解决：需要用到事务来解决
  :::

-- 删除学工部
delete from dept where id = 1;  -- 删除成功

-- 删除学工部的员工
delete from emp where dept_id = 1; -- 删除失败（操作过程中出现错误：造成删除没有成功）

• 一个业务要发送多条SQL语句给数据库执行，需要将多次访问数据库的操作视为一个整体来执行。
• 事务：是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
• 事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

SQL语句	描述
start transaction;  /  begin ;	开启手动控制事务
commit;	提交事务
rollback;	回滚事务

MYSQL中有两种方式进行事务的操作：

• 自动提交事务：即执行一条sql语句提交一次事务。（默认MySQL的事务是自动提交）
• 手动提交事务：先开启，再提交

:::warning
手动提交事务使用步骤：

• 第1种情况：开启事务  =>  执行SQL语句   =>  成功  =>  提交事务
• 第2种情况：开启事务  =>  执行SQL语句   =>  失败  =>  回滚事务
  :::

-- 开启事务
start transaction ;

-- 删除学工部
delete from tb_dept where id = 1;

-- 删除学工部的员工
delete from tb_emp where dept_id = 1;

• 上述的这组SQL语句，如果如果执行成功，则提交事务

-- 提交事务 (成功时执行)
commit ;

• 上述的这组SQL语句，如果如果执行失败，则回滚事务

-- 回滚事务 (出错时执行)
rollback ;

MySQL-事务-四大特性【重点】

• 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

:::info

• 原子性（Atomicity） ：原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的操作要么全部成功，要么全部失败。
• 一致性（Consistency）：一个事务完成之后数据都必须处于一致性状态。
• 如果事务成功的完成，那么数据库的所有变化将生效。
• 如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。
• 隔离性（Isolation）：多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互隔离。
• 一个事务的成功或者失败对于其他的事务是没有影响。
• 持久性（Durability）：一个事务一旦被提交或回滚，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。
  :::

MySQL-索引-介绍

• 索引（index）：是帮助数据库高效获取数据的数据结构。

:::info

• 没有索引的查询流程：目标值 45，从头开始扫描，找到目标值后继续向下扫描完，第二次查找 45 ，依然时全盘扫描，效率慢。
• 有索引的查询流程：比如用二叉树（打个比方），左边的叶小于右边的叶子，这样就能快速判断位置，提高查询效率。（第二次查找仍然要从头遍历，只是快了）
  :::
  

MySQL-索引-结构

:::info

• MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的B+Tree结构组织的索引。
  :::
  
  :::info

• 存在问题：数据越大，层级越深，查询的速度会越慢。
  :::

B+Tree

:::info
数据库中的索引结构B+树。

• 1、一个节点可以存储多个key值（n个key和n个指针）。
• 2、所有数据都储存在叶子节点，非叶子节点仅存索引数据。
• 3、叶子节点形成双向链表，方便数据排序和区间范围查找。
  :::

注意点！！：根据范围选择方向，如果相邻，可以范围搜索，因为时双向链表。

:::info
拓展：
非叶子节点都是由key+指针域组成的，一个key占8字节，一个指针占6字节，而一个节点总共容量是16KB，那么可以计算出一个节点可以存储的元素个数：16*1024字节 / (8+6)=1170个元素。

• 查看mysql索引节点大小：show global status like ‘innodb_page_size’;    – 节点大小：16384

当根节点中可以存储1170个元素，那么根据每个元素的地址值又会找到下面的子节点，每个子节点也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：11701170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。
对于第二层每个元素有指针，那么会找到第三层，第三层由key+数据组成，假设key+数据总大小是1KB，而每个节点一共能存储16KB，所以一个第三层一个节点大概可以存储16个元素(即16条记录)。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W16结果就是2000W+的元素个数。
结合上述分析B+Tree有如下优点：

• 千万条数据，B+Tree可以控制在小于等于3的高度
• 所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找
  :::

B-Tree

:::info

:::

MySQL-索引-操作语法

创建索引

create  [ unique ]  index 索引名 on  表名 (字段名,... ) ;

create index idx_emp_name on tb_emp(name);

查看索引

show  index  from  表名;

show  index  from  tb_emp;

删除索引

drop  index  索引名  on  表名;

drop index idx_emp_name on tb_emp;

:::info
注意事项：

• **主键字段，在建表时，会自动创建主键索引 **
• 添加唯一约束时，**数据库实际上会添加唯一索引 **
  :::