数据库MySQL之事务、索引

目录

1.概述

2.事务

3.索引

3.1索引结构

3.2操作语法

---

1.概述

场景：假如我们需要解散教学部，那么该部门下的所有员工都需要删除。如果教学部成功删除了，但员工出于某些原因(比如SQL语句写错了等)并没有删除，此时就会出现数据不一致的问题。这时我们可以通过数据库中的事务来解决。

事务是一组操作的集合,它是一一个不可分割的工作单位。事务会把所有的操作作为一个整体向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。

场景：假如数据库中有一张表，表中包含几百万甚至几千万条数据，此时我们用查询操作就会很慢，因为查询会扫描整张表，此时我们可以通过构建索引去快速查找数据。

索引是数据库表中的一种数据结构，用于提高数据检索的效率。它们可以被看作是数据库查询的"快速通道"，允许数据库管理系统（DBMS）快速定位到需要的数据，而不必扫描整个表。

2.事务

开启事务后，只有当commit前的语句全部成功执行，我们才进行commit提交到数据库，否则进行rollback回滚操作，将数据重新恢复。

实际上，事务开启后，sql语句进行的操作并不在真的数据库上，而是操作了一份副本的数据库，只有commit后，才会将真的数据库进行修改。这就是事务的隔离性。

--开启事务
start transaction;
--删除部门
delete from dept where id = 3;
删除部门下的负工
delete from emp where dept_id = 3;
--提交事务
commit;
--回滚事务
rollback;

数据库事务确保了数据的完整性和一致性。事务具有以下四大特性，被称为ACID属性：

1. 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个点。这保证了数据的完整性，不会出现部分更新的情况。

2. 一致性（Consistency）：事务必须保证数据库从一个一致的状态转移到另一个一致的状态。事务执行的结果必须符合所有预定义的规则，包括外键约束、触发器等。

3. 隔离性（Isolation）：并发执行的事务之间不会互相影响。每个事务都独立运行，不知道其他事务的存在。

4. 持久性（Durability）：一旦事务提交，它对数据库的改变就是永久性的，即使系统发生故障也不会丢失。

3.索引

索引如同书本中的目录，没有目录，我们去查找某一章节的内容时就得一页一页地翻。

举例：下图中左边没有索引的情况下就是全表扫描。右边建立了二叉搜索树的索引，年纪小的放左边，年纪大的放右边。这样我们很快就能找到年龄为45的数据。

优点：

1. 提高查询速度：索引可以显著提高数据检索的速度，尤其是在大型数据表中。
2. 减少查询中的I/O操作：索引帮助数据库管理系统快速定位数据，减少了磁盘I/O操作。

缺点：

1. 增加存储空间：索引需要额外的存储空间，尤其是在大型数据库中。
2. 增加写操作的开销：插入、删除和更新操作会变慢，因为索引本身也需要更新。

缺点我们一般忽略不计，因为如今磁盘空间都以T计算，索引占不了多少空间。其次，在业务中，查询的操作比增删改三者加起来都要更加频繁，因此建立索引是必要的。

3.1索引结构

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

如果数据库中的表是按照大小来排序的，那么二叉树就会变成中间图的单向列表，此时搜索性能就会大打折扣。这时可以采用平衡二叉树或者红黑树解决。

但在实际应用中，当数据量很庞大，树的深度就会很深，此时只要是二叉树，它的检索速度都会变慢。B+树可以规避这类问题。

B+树（多路平衡搜索树）是一种自平衡的树形数据结构，并且广泛用于数据库和文件系统中的索引结构。以下是B+树的索引结构图：

非叶子节点只起到查找数据的作用，并不存储具体的数据，具体的数据保存在叶子节点上。所有的key都会出现在叶子节点上，比如根节点的6，在第一个叶子节点上也有6这个key，以此类推。另外，它的叶子节点元素是按照大小进行排序的，并形成双向链表。

查找过程：假如我们要查找53。先通过二分查找的方式定位53在38和67之间，因此走p2指针，再通过二分查找定位到47和55之间，再走p2指针，就找到了53和53对应的数据。

以下是B+树的一些关键特性：

1. 节点结构：B+树的内部节点可以有多个子节点，并且每个内部节点可以存储多个key。键值key用于分割子节点，每个键值对应一个子节点。

2. 有序性：B+树中的键值是有序的，这使得它可以高效地执行范围查询。

3. 叶子节点：所有的数据记录都存储在叶子节点中，叶子节点本身按照键值的顺序链接在一起，形成一个链表，这便于顺序访问。

4. 高度：B+树的高度比二叉树低，形成的树是矮胖样式的。

B+树的设计使得它非常适合作为数据库索引，因为它可以提供高效的数据检索，尤其是在需要处理大量数据和频繁进行范围查询的场景中。

3.2操作语法

--创建索引
create [unique] index 索引名 on 表名(字段名...);
--查看索引
show index from 表名;
--删除索引
drop index 索引名 on 表名;

数据库会自动给主键id创建一个主键索引，它的性能最高。username添加了唯一约束，那么也会自动创建一个唯一索引，实质上这个唯一约束就是唯一索引。name是我们自己创建的索引。