数据库概论之 MySQL 的索引事务

  • 一 . 索引
    • 1.1 索引的引入
    • 1.2 索引的使用
      • 1.2.1 查看索引
      • 1.2.2 创建索引
      • 1.2.3 删除索引
    • 1.3 索引使用的具体数据结构
      • 1.3.1 B 树
      • 1.3.2 B+ 树
  • 二 . 事务的基本特性
    • 2.1 最核心的特性 : 原子性
    • 2.2 一致性
    • 2.3 持久性
    • 2.4 隔离性
      • 隔离问题
        • 脏读
        • 不可重复读
        • 幻读
      • 隔离级别
  • 三 . 事务的操作

大家好，这篇文章就会将 MySQL 最后的一点基础知识进行收尾 , 后续大家也要关注一些数据库的其他方面知识 , 不能仅局限于我的博客里面的这些知识 . 我分享的内容在数据库当中都是一些非常基础、非常小白的操作 , 大家下来还是要深入学习的 .

一 . 索引

1.1 索引的引入

我们之前也介绍过 , 使用外键的时候 , 通过索引来查询 , 速度就会比较快

索引实际上就是目录 , 目录的意义就是加快查找的意义

索引本质上是通过一些复杂的数据结构把所有待查询的记录给组织起来了 , 从而加快查找的速度
我们之前也学习过一些查找的数据结构 :

  1. 顺序表 : O(N)
  2. 链表 : O(N)

谈谈 ArrayList 和 LinkedList 的区别

  1. ArrayList 是顺序表 , LinkedList 是链表
  2. ArrayList 支持按下标随机访问 , LinkedList 不支持

ArrayList 查找速度较快这个说法是错误的 , 查找指的是 indexOf 而不是 get

LinkedList 增加删除元素较快这个说法也是错误的 , 链表往中间位置插入元素 , 时间复杂度是 O(1) , 但是 LinkedList 不是 , 标准库在设计 LinkedList 的时候有些问题 , 就没发挥出链表的优势 . 比如
LinkedList list = new LinkedList();
list.add(4 , “hello”);
这个操作是从头遍历到 4 号元素再插入 , 虽然插入操作是 O(1) , 但是遍历操作的时间复杂度是 O(N)

  1. 栈 : 不是用来查找的
  2. 队列 : 不是用来查找的
  3. 二叉树 : O(N)
  4. 二叉搜素树 : O(N)~O(logN) , 极端情况下就变成了单枝树 , 就相当于遍历链表
  5. 堆(优先级队列) : 不是用来查找的
  6. 哈希表 : O(1)

目前来看 , 二叉搜索树和哈希表看起来还是比较快的 , 虽然普通的二叉搜索树查找的时间复杂度是 O(N) , 但是有 plus 版本 – AVL 树 / 红黑树 (他们俩的时间复杂度都是 O(logN))
但是数据库索引使用的数据结构还不是他们俩 , 因为 :
红黑树作为一个二叉树 , 如果元素数量多了 , 树的高度就会比较高 , 树的高度一旦高了 , 比较次数就会增加 , 由于数据库每次访问数据都要访问磁盘 , 比较次数过多就会造成查询速度变慢
哈希表虽然查的确实快 , 不过他只能查询相等的情况 , 不能进行 / 范围查询 / 模糊匹配的操作

索引实际上就是引入额外的数据结构 , 来加快查询速率的

  1. 消耗额外空间
  2. 虽然引入了索引加快了查询速度 , 但是拖慢了增删改的速度

假如索引就相当于书中添加了目录 , 如果再添加新的内容 , 目录也是需要更改的

索引也不是那么好用的 , 主要还是看场景是否合适 (多读少写的情况)

  1. 对于空间不紧张的情况下 (空间换时间)
  2. 查询频繁 , 增删改不频繁

1.2 索引的使用

1.2.1 查看索引

查看索引 : show index from 表名;

我们可以看到 , 我们没添加过索引 , 但是他自己就带了索引
一个表的主键会自动带上索引

像主键这种 , 需要保证记录不重复 . 每次插入新记录 , 都需要查询一下已有记录 , 看看新纪录是否存在 , 频繁的查询必然要搭配约束

unique 和 外键约束的列 , 也会自动带上索引

1.2.2 创建索引

创建索引 : create index 索引名 on 表名(列名);

1.2.3 删除索引

删除索引 : drop index 索引名 on 表名;

注意 : 创建索引和删除索引也是一个危险操作 ! 尤其是针对一个已经包含很大数据量的表进行操作的时候
如果是针对一个数据量非常大的表 , 创建索引就会导致大规模磁盘 IO , 直接就把主机的磁盘 IO 吃满 , 主机就有可能卡了 , 无法对线上服务进行相应

1.3 索引使用的具体数据结构

我们之前说不能使用红黑树的原因是因为树的高度每增加一层 , 就要多出许多磁盘 IO 操作 , 那么我们让树的高度矮一点呢 ” />
但是在实现数据库的时候 , 一般也不会去使用 B 树 , 而是使用 B 树的改进版本 -> B+ 树

有些教材上将 B 树叫做 B-树 , 这个 – 是连接符

1.3.2 B+ 树


B+ 树可以说是为了实现数据库索引量身定做的数据结构

  1. 查询速度快 : 类似于二叉搜索树的查询
  2. 单个节点存储更多的数据 , 因此树的高度比较低 , 比较次数就少了
  3. 所有的叶子结点使用链表首尾相连 , 非常适合范围查找

  1. 每一个数据行只需要保存在叶子节点上就够了 , 非叶子节点不必存储真实的数据行 , 只需要存储用来做索引的 id 即可

一个表里面有很多列 , 几十列都有可能 .
一行数据占用的空间比较大 , 只能放在磁盘上
做索引的常见情况是拿出其中的某个 id 做索引
因为单拎出来某个 id 会比整个行小很多


此时非叶子节点占用的空间小 , 就有了在内存中缓存非叶子节点的可能性
如果把非叶子节点都在内存中缓存了(哪怕一部分也好) , 也会大大的提升速率
而 B 树是把所有信息都存储在了每个节点上 , 导致速率降低

B+ 树的实现比较麻烦 , 最复杂的部分就是叶子的分裂和合并
我们只需要理解 : (高频面试题)

  1. 数据库的索引为什么使用 B+ 树而不是红黑树 / 哈希表
  2. 理解 B+ 树的结构特点
  3. 理解 B+ 树的优势

二 . 事务的基本特性

在我们的实际操作数据库的过程中 , 有些操作 , 希望是一个整体

比如 : 你谈了个对象 , 一般都是这个流程 : 给彩礼 -> 领证 -> 办酒席
那么我们希望这个流程是一个整体 , 一旦中间某个过程出现问题 , 就希望能够让这些操作的影响降低到最低
比如说 : 彩礼给完了 , 但是这个时候女生发现男生是个渣男 , 后悔了 , 不想结婚了 , 女方反悔 , 就想要回彩礼
那要是彩礼给了 , 证也领了 , 女方想反悔 , 这个时候就需要先把彩礼给退了 , 还需要去民政局办个离婚证

那在这样的场景下 , 我们就希望能够一气呵成 , 当做一个整体来进行 . 如果执行中间过程中 , 出现异常 , 就需要把前面已经进行的操作进行 回退 / 恢复 , 恢复成好像根本没操作过的样子 .
这三个步骤就可以视为一个事务 , 把多个操作打包成一个操作 , 其中任意一个步骤执行失败 , 都会进行回退 , 使这里的影响降到最低

数据库上也涉及到与事务很多的相关操作 , 最典型的就是转账
account(id,name,money)
1 张三 1000
2 李四 2000
张三给李四转 500 块钱
-> 给张三 -500 SQL1 执行减的操作
给李四 +500 SQL1 执行减的操作

那现在有一种情况 : 如果在执行完 SQL1 (账户 -500) 之后 , 恰好再执行 SQL2 (账户 +500) 的过程中 , 数据库出问题了 (比如断电了) , 这就变成了张三的账户变成了 500 , 李四的账户还是 2000 , 这 500 块钱就转丢了
我们的解决方案就是要不两个都执行完 , 要不就两个都不执行
这就涉及到了事物的最核心的特性 : 原子性

2.1 最核心的特性 : 原子性

以前的人们认为 : 原子是不可分割的最小的单位 , 后来就使用 “原子” 这个概念来指代这种不可分割的内容
原子性指的是 : 当前事务的操作要么同时成功，要么同时失败

我们上面没说的 “要不两个都执行完 , 要不就两个都不执行” , 难道 “两个都不执行” 是真的没执行吗 ” />
事务 A 提交了之后 , 事务 B 才开始读 (读是加锁了) , 然后在 B 的执行过程中 , A 又开启了一次 , 修改了数据 . 此时 B 执行中 , 两次读取操作可能结果就不一致 .
那么怎样解决呢 ?
我们重新约定 : 发到寝室群之前大家不要读 , 室友读的时候我也不要去修改代码
经过这个新的约定之后 , 就变成了这样

这样的话 , 隔离性又提高了 , 并发性又降低了 . 数据更准确了 , 但是效率更低了

幻读

我们之前约定了在室友读的时候我不能改代码
那我也不能闲着 , 不让改 Student , 那我就改 Teacher , 互不打扰不也挺好
但是这样就会存在问题 :
如果 Teacher 本身就存在 , 也就罢了 .
如果是在室友读的过程中 , 新增的 Teacher.java 或者删除了一个已有的内容 , 就会产生幻读问题

事务 B 读取过程中 , 事务 A 对其他数据进行了修改 , 但是没有直接修改 B 读取的数据 , 但是影响到了 B 读取的结果集
事务 B 两次读取到的结果集不一样 , 这个就是”幻读”
幻读相当于是不可重复读的特殊情况

不可重复度偏重于数据内容 , 幻读偏重于数据结果集

那如何解决幻读问题呢 ” /> 保证读和写都是严格串行执行的 (一个执行完才能执行另一个)
它的隔离性最高 , 并发程度最低 , 数据的准确性最好 , 但同时也是速度最慢的

为啥要实现并发 , 追求效率 ;
为啥要实现隔离 , 追求准确 ;
但是鱼和熊掌不能兼得

隔离级别

MySQL 中给我们提供了四个等级 , 让我们自由选择
read uncommitted : 没提交的数据也能读到 , 并发能力最强 , 隔离性最弱
read committed : 只能读取提交之后的数据 , 解决了脏读问题 , 并发能力下降 , 隔离性增加
repeatable read : 针对读和写都进行限制 , 解决了不可重复读问题 , 并发能力进一步下降 , 隔离性进一步增加
serializable : 串行化 , 严格的串行执行 , 解决了幻读问题 , 并发能力最低 , 隔离级别最高
可以根据实际需要 , 在配置文件中 (my.ini) 修改数据库的隔离级别

三 . 事务的操作

1 : 开启事务 : start transaction;
2 : 执行多条 SQL 语句
3 : 回滚或提交 : rollback(全部失败) / commit (全部成功)

在 1~3 的过程中 , 中间的 SQL 就是一个整体
实际开发的时候 , 很少通过 SQL 代码来写事务 , 更多的是借助其它编程语言来写事务