【MySQL 数据库】MySQL 的索引和事务(扫盲必备)

x33g5p2x  于2022-01-11 转载在 Mysql  
字(2.6k)|赞(0)|评价(0)|浏览(313)

1. 索引

1.1 概念

  • 索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的,它是由数据页面以外的索引页面组成的,每个索引页面中的行都会含有逻辑指针,以便加速检索物理数据。
  • 在数据库关系图中,可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表,或保存该表所在的关系图时,索引将保存在数据库中。

通俗的讲,索引与数据库中的表和数据的关系就类似于书架上的图书(表)、书籍内容(数据)和书籍目录(索引)的关系

1.2 作用

在数据库系统中建立索引主要有以下作用:

  • 快速取数据
  • 保证数据记录的唯一性
  • 实现表与表之间的参照完整性
  • 在使用 order by、group by 子句进行数据检索时,利用索引可以减少排序和分组的时间

1.3 索引的原理

1.3.1 减少磁盘的访问次数是构建索引的核心思想

  • 索引的目的是方便进行查询
  • MySQL 的查询主要是 select,select 基本执行的过程包括遍历表、依次取出每个记录、根据 where 字句的条件进行筛选
  • 由于 MySQL 是把数据储存在硬盘上,因此进行查询时,每次取出记录都意味着要访问硬盘
  • 而 IO 设备对硬盘的访问效率远低于对内存的访问,因此减少磁盘的访问次数就可以提高查询的效率,也就是构建索引的核心思路

1.3.2 B+ 树适用实现索引的底层

减少对数据的访问次数就是实现索引时的重要思想,接下来将会对几种数据结构进行分析,以找到更适合的实现索引的数据结构

二叉搜索树
由于二叉搜索树可能是一个单支树,时间复杂度为 O(N)

AVL 树

  • AVL 树本质是一个二叉平衡搜索树,是对二叉搜索树的一个改进,它会保证了左右子树的高度差不超过1,即不会存在单支树的结构,查找时间复杂度为 O(logN)
  • 因为需要满足左右子树高度差不超过1的条件,所以插入或者删除操作会破坏 AVL 树的结构。因此需要随时对树进行调整。虽然查询效率得到了满足,但是降低了插入和删除操作的效率,插入删除时间复杂度为 O(logN)

红黑树

  • 红黑树本质是一个放松了规则的 AVL 树,即不要强制左右子树高度差不超过1,会降低要求,以此来保证插入和删除操作的效率。
  • 整体和 AVL 树差异不大,查询插入删除的时间复杂度为 O(logN)

哈希表

  • 哈希表可以做到查询、插入、删除的时间复杂度都是 O(1)
  • 但是哈希表的一个关键是必须要比较相等,但是形如大于、小于之类的条件无法做到,这就和实际查询的情况不符合了

到这里为止,好像只有 AVL 树或者红黑树更加适合用作 MySQL 的索引的实现,而这两个数据结构的查找效率直接是由树的高度决定的,因此数据增多的话,树的高度也会增加。

为了进一步优化,就可以使用 N 叉搜索树来降低树的高度,即减少磁盘 IO,以提高查找效率

B 树

  • B 树是 N 叉搜索树的一种

  • B 树示例结构:

  • 用在索引中,每个结点都表示一个记录

  • B 树的特点:

  • 每个结点可能包含 N 个子树

  • 每个结点上都可能存在多个值

  • 左子树的值都小于根节点的对应值,右子树的值都大于根节点的对应值

B+ 树

  • B+ 树是一种特殊的 N 叉搜索树,是 B 树的改进版
  • B+ 树示例结构:

  • B+ 树相对于 B 树的改进:

  • 叶子节点存储每行记录,非叶子节点只要存储每行的索引值即可

  • 非叶子节点的值存在重复,使得叶子结点这一层是完整的数据集合

  • 可以通过类似于链表的方式,把所有的叶子节点连接起来

  • B+ 树的优势:

  • 善于进行范围查找

  • 由于所有的查询都是落在叶子结点上,故查询的速度是比较稳定的

  • 由于叶子结点是数据的全集,因此就可以把叶子结点存到硬盘上,非叶子结点直接存到内存中,大大降低了读取硬盘的次数

1.4 适用场景

  • 查找的次数比较多,插入删除的次数较少适合用索引
  • 由于索引本身也占据一定空间,如果磁盘紧张就不太适合用索引
  • 索引是指定某个列来建立的,当某列的区分度比较大的时候,适合用索引,例如自增主键

1.5 使用语句

补充:
创建主键约束(primary key)、唯一约束(unique)、外键约束(foreign key)时,会自动创建对应列的索引

1.5.1 查看索引

语法:

show index from 表名;

示例:

1.5.2 创建索引

语法:

create index 索引名 on 表名(字段名);

示例:

1.5.3 删除索引

语法:

drop index 索引名 on 表名;

示例:

注意:
主索引不能删除,删除会报错

2. 事务

2.1 概念

事物:是属于计算机中一个很广泛的概念,一般是指要做的或所做的事情。在关系数据库中,一个事务可以是一条 SQL 语句或者一组 SQL 语句或整个程序。

通俗的讲,比如银行转账的操作,A 转给 B 500元,那么这个操作其实包含了 A 账户余额减少500元和 B 账户余额增加500元两个操作。

事物就相当于将这一连串的动作给打包,使其成为一个整体,要么全都不做,要么全都做完

2.2 为什么使用事务

用上述银行转账的例子为例,假设 A 账户减少500元的操作成功了,但 B 账户增加500元的账户没有成功,那么这个转账的操作是失败的。

事物的核心特点就是: 把一系列操作给打包到一起,构成一个整体,要么全都做完,要么一个都不做。

全都不做是指: 如果某个操作失败了,那么就会将此时的中间状态偷偷还原回去

因此使用事物的话就可以保证,某一系列的操作,不会只完成其中一部分,它要么完全完成,要么都没有完成

2.3 四大属性

事务是恢复和并发控制的基本单位,它具有四个属性:原子性、一致性、持久性、隔离性

事物的核心是原子性

2.3.1 原子性

概念:
一个事务是一个不可分割的工作单位,事务中包括的操作要么都做,要么都不做。

事物的核心是原子性,原子性的核心是回退为中间状态,回退为中间状态核心就是回滚,回滚的核心就是记住每步的操作

2.3.2 一致性

概念:
事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

执行事物之前和执行事物完成后,当前表里的数据都是合理的状态

2.3.3 持久性

概念:
持久性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事物操作的数据都是直接操作硬盘,而硬盘的数据都是持久化的

2.3.4 隔离性

概念:
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。

2.4 使用方法

  1. 开启事物
start transaction;
  1. 执行多条 SQL 语句
  2. 回滚或提交
-- 回滚:表示上述 SQL 语句全部失败
rollback;

-- 提交:表示上述 SQL 语句全部成功
commit;

相关文章