MySQL学习笔记(第七节)(超详细解析索引和事务)

索引

1. 什么是索引?
2. 索引有什么作用?
3. 索引的使用场景
4. 使用索引
5. 索引有哪些不足?
6. 索引的底层数据结构
  6.1 B树
  6.2 B+树

事务

1. 什么是事务?
2. 为什么要使用事务?
3. 使用事务
4. 事务的基本特性
5. 事务的隔离性和并发性分析

---

索引

1. 什么是索引?

  索引是一种特殊的文件, 包含着对数据表里所有记录的引用指针, 可以对数据表中的一列或多个列创建索引, 并制定索引, 不同类型的索引由不同的数据结构实现.

  (通俗地讲, 索引就好比一本书的目录, 我们可以根据目录找到特定章节的位置)

2. 索引有什么作用?

  上面已经提到了, 索引就像书的目录一样, 那么, 目录的作用是什么?就是方便我们快速定位到某个特定的章节, 那么, 索引的功能也是也是一样的, 它可以帮我们快速定位数据, 检索数据, 这对数据库的性能有非常大的帮助.

3. 索引的使用场景

  因为只是在学习阶段, 我们的数据库里可能只存在少量的数据, 这时使用索引的效果并不明显. 但是, 当数据库中存在成千上万的数据时, 使用索引就是非常有必要的.

  当我们考虑是否需要对数据表创建索引时, 需要考虑以下几点:

1. 数据量较为庞大, 并且经常需要查询这些数据.
2. 我们并不需要对数据表频繁地进行插入和修改操作(这条下面会讲到)
3. 索引需要占用额外的磁盘空间.

4. 使用索引

  当我们主动创建主键约束(primary key), 唯一性约束(unique), 外键约束(foreign key)时, 会自动创建对应列的索引.

查看索引:

show index from 表名;

对于没有进行上述约束的普通字段, 我们可以对其创建普通索引:

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

删除索引:

drop index 索引名 on 表名;

5. 索引有哪些不足?

1. 索引本身是需要消耗一定的存储空间的, 而且数据量越大, 索引消耗的额外空间也就越多
2. 如果书的目录确定了, 之后再去修改书的内容的时候, 就需要重新调整目录, 索引也是一样的, 当我们对数据表进行增, 删, 改操作时, 往往也需要同步地调整索引的结构
3. 索引虽然能提高查找数据的数据, 但它也会拖慢增删改的速度.

  虽然索引有这么多的不足, 但它仍然是一个常用的东西, 在实际开发中, 我们对数据表进行查找的频率要远远大于增删改的频率.

6. 索引底层的数据结构

  数据库的种类非常多, 每种数据库底层又支持多种存储引擎, 每种存储引擎存储数据的结构可能都不一样, 背后的索引数据结构也可能都不同, 而数据库索引中最常见的数据结构,就是----B+树.

B+树是一颗N叉搜索树, 要了解B+树, 首先, 我们需要了解一下B+树的前身, B树.

B树

  B树也是一颗N叉搜索树, B树的每个节点上, 都会存储N个key值, N个key值就划分出了N+1个区间:

例如, 根节点共有三个值, 这三个值划分出了四个空间, 根节点的左孩子共有两个值, 两个值划分出了三个空间.

在B树中查找元素, 其实和二叉搜索树十分类似, 现在根节点中判断要找的元素所在的区间, 并依次向下查找即可.

那么, B树和二叉搜索树有什么区别呢?

  二叉搜索树比较的次数和它的高度相关, 高度越高,比较次数也就越多, 而B树的高度相对较低, 但是每个节点需要比较多次, 对于数据库来说, 数据库是以树的节点进行磁盘IO的, 也就是说, 相比于比较次数, 我们更应该关心节点的个数, 而B树的节点个数是明显少于二叉搜索树的.

B树只是B+树的前身`, B+树对B树又进行了改进.

B+树

  B+树也是一颗二叉搜索树, B+树的每个节点上都包含N个key值, 而与B树不同的是, B+树每个节点的N个值划分出了N个区间, 而不是N+1个.

B+树的特点:

1. 父节点的值都会在子节点中存在
2. 非叶子节点的每个值都会在叶子结点中存在
3. 父节点的值会作为子节点中的最大值(也可以作为最小值)
4. 所有的叶子结点会以链表的方式进行连接(图上的红色箭头)

使用B+树作为索引背后的数据结构的好处:

1. 使用B+树进行查找时, 整体的IO次数也非常少
2. 所有的查询最终都会落到叶子节点上, 每次查询的IO次数相差不大, 查询速率稳定
3. 叶子结点通过链表连接后, 非常适合进行范围查找
4. 所有的数据存储(载荷)都是放在叶子结点上的, 非叶子结点中只保存key值即可, 因此非叶子结点整体占用的空间较小, 甚至可以缓存到内存中.

---

事务

1. 什么是事务?

  事务指的是逻辑上的一组操作, 组成这个操作的各个单元, 要么全部成功, 要么全部失败, 不能存在部分操作执行成功, 部分操作执行失败的情况.

2. 为什么要使用事务?

  例如, 在进行转账操作时, 我们将钱转出后, 数据库突然出现了故障, 这时收款方无法正确地收到钱款, 这时候该怎么办呢?

  解决方法就是, 使用事务来控制转账操作的一致性, 要么转出和收款同时成功, 要么同时失败.

3. 使用事务

1. 开启事务:

start transaction;

2. 执行SQL语句
3. 回滚或提交

rollback / commit;

回滚(rollback)即代表全部执行失败, 提交(commit)即代表全部成功.

4. 事务的基本特性

1. 原子性:事物中的SQL语句要么全执行完, 要么全都不执行, 任务不可以再细分
2. 一致性:在事务执行前后, 数据库中的数据都是合理合法的.
3. 持久性:事务提交之后, 数据就被写入硬盘持久化地存储起来了
4. 隔离性:当多个事务并发执行的时候, 如果事务和事务之间没有任何的隔离, 就可能会出现脏读问题, 加上一些约束限制, 就可能避免这种问题的产生

5. 事务的隔离性和并发性分析

脏读问题:事务A在对某个数据进行修改时, 事务B去读取了这个数据, 但这个被读取到的数据可能是一个"脏数据"(脏数据指这个数据只是一个临时的数据, 而并非最终结果)

处理脏读问题:给读操作和写操作加锁, 在修改数据的过程中, 其他事务无法读取该数据, 在读取数据的过程中, 其他事务也无法修改数据, 这样, 事物之间的隔离性高了, 并发性也就降低了.

注意:事务虽然在提交隔离性的时候需要进行一系列的加锁, 但这个锁并非将数据库锁定, 而是可以继续修改其他的数据, 例如其他的表, 甚至是这个加锁的表的其他行.

幻读问题:一个事务执行过程中进行了多次查询, 多次查询的结果不一样, 这个操作算是一种特殊的不可重复读.

处理幻读问题:彻底串行化运行, 在进行读操作时无法执行任何写操作, 在写操作的时候也无法进行任何读操作, 这种方式隔离性最高, 并发程度最低, 数据最可靠, 但效率也最低.

因此, 并发性和隔离性是不可兼得的, 并发性意味着更高的效率, 但数据的准确性会降低, 而隔离性会提高数据的准确性, 也会降低运行效率.

在实际开发中, 我们会根据实际需要来调整数据库的隔离级别, 通过不同的隔离级别, 也就控制了事务之间的隔离性和并发程度.

MySQL中提供了以下几种隔离级别:

read uncommitted:允许读取未提交的数据, 并发程度最高, 隔离程度最低, 会引入脏读, 不可重复读, 幻读等问题.
read committed:只允许读取提交后的数据, 只为写操作加锁, 降低了并发程度, 提高隔离程度, 解决了脏读问题, 仍会存在不可重复读和幻读问题.
repeatable:为读操作和写操作加锁, 继续降低并发程度, 提高隔离程度, 解决了脏读问题和不可重复读问题, 仍存在幻读问题.
serializable:串行化, 并发程度最低(串行执行), 同时解决了脏读问题, 不可重复读问题和幻读问题, 但运行速度最慢.

我们可以通过修改my.ini配置文件来修改隔离级别.(my.ini文件位于MySQL的安装目录下)