MySql索引和事务

一.索引

1. 索引的概念

索引是一种特殊的文件,包含着对数据表里所有记录的引用指针. 可以对表中的一列或者多列创建索引.
索引本质上类似于书的目录, 用于加快查找的速度, 但是同样的, 索引的引入也提高了增删改的开销!!!

2. 索引的使用场景

在考虑对数据库的某列或者对某几列创建索引,要考虑以下几点:

• 数据量较大, 并且经常对这些列进行查询操作
• 该数据库表的插入操作, 以及对这些列的修改操作频率较低.
• 索引会占用额外的磁盘空间.

当满足上述条件时, 可以考虑对这些字段创建索引, 以便提高查找效率.
反之, 则不建议创建索引.

3. 索引操作中需要注意的地方

• 创建索引

创建索引这个操作最好是在表的创建之初就将索引添加到位, 否则, 对于一个有很多数据记录的表创建索引是一个危险操作.

危险之处: 最后创建索引时, 会吃掉大量的磁盘 IO , 花费相当长的时间, 此时, 数据库将无法使用!!!

• 删除索引

删除索引与创建索引类似, 删除操作同样会吃掉大量的磁盘 IO 是危险操作,要谨慎操作.

二. 索引在MySQL 中的数据结构

在数据库中, 常常要进行的是对数据进行范围查找.
因此,数据库内部的数据结构为 N 叉搜索树, 即 , 每个节点上有多个值, 同时具有多个分叉存在.

例如,其中一个比较典型的例子----B树

在这种情况下, 比较次数虽然没有变化, 但是读写磁盘的次数已经减少.

B 树虽然比较适合作为数据库的索引, 但是还不够, 因此 B+ 树是对 B 树的改进.

B+ 树 就是为了 ‘索引’ 这个场景量身定做的如图:

• B+树的特点

1. B+ 树也是一个二叉搜索树 , 每个节点上可能包含 N个 key , N 个 key 划分出 N 个区间, 最后一个key 相当于最大值
2. 父节点中的 key 值会在子节点中重复出现, 并且会是以最大值的形态出现的!

这样的重复出现, 导致了叶子结点中包含了所有数据的全集, 非叶子结点的所有值都会在叶子中体现出来!

3. 最终, 叶子结点会以链表的方式进行首尾相连.

• 使用 B+ 树的优点

1. 作为一个 N 叉搜索树, 高度的降低, 使其在比较时 硬盘IO的次数减少, 更加适合与范围查询.
2. 所有的查询操作, 最终都要落到叶子结点上, B+ 树来讲, 无论查询那个元素,中间比较次数都差不多, 查询较为均衡.

对于 B 树来讲, 有的值查询速度较快, 有的值查询速度较慢, 可能出现时间不均衡的情况.

3. 由于所有的 key 都会在叶子结点上体现, 因此, 非叶子结点可以不必存储真实的记录, 只需要将所有的数据放置到叶子结点即可,非叶子结点只需存储索引列的值即可.

存储方式并不是连续的空间结构

由于非叶子结点置存放了简单 id 没有存储一整行元素表, 这样就意味着非叶子节点占用的空间大大降低, 降低了磁盘 IO.

三.事务

1. 事务的概念
   事务指逻辑上的一组操作, 组成这组操作的各个单元, 要么全部成功, 要么全部失败.

2. 事务的最核心的特性

• 原子性
  是事务的初心
• 一致性
  事务执行前后都得是数据合法的状态
• 持久性
  元素最终被存放在硬盘之中不能被修改
• 隔离性
  一个数据服务器同时执行多个事务, 事务之间的 “相互影响度”.

在上述的四个核心特性中隔离性是相对最重要的一点, 因此, 在下面我会对其进行更加详细的解释!

3. 隔离性的详细解释

在 MySQL 中 , 通过控制隔离性的高低 / 并发程度的高低 / 执行效率的高低 / 数据准确性高低, 来控制 隔离级别.

会出现隔离问题, 必然是因为事务之间的相互干扰造成的, 下面, 我会通过举例分别说明事务之间的问题, 以及解决办法.

• 例1

  假如某同学正在写一篇博客, 此时我正好经过看了一眼就走了, 看到他正好写了一段话, 但是, 在我离开后, 他修改了这一段话.

  这里的写 和 看 就是两个事务读写同一个数据.

此时, 这种问题就是脏读问题

解决办法: 可以通过降低并发性, 提高隔离性, 对写操作加锁. 即就是, 先不让我看他的博客写了什么东西, 等他完全完成后发布出来我才能看.

• 例2

  此时, 在加锁的条件下, 同学写完博客提交了, 我正在认真仔细的阅读, 但是, 写博客的同学此时发现自己的文章还有不足, 于是, 他将文章进行了修改后又提交了, 我看了一半, 突然发现前面刚读的全都变了…

此时, 这种问题就是不可重复读问题 : 一个事务中, 连续两次读到的数据, 结果不一致.

解决办法: 在我阅读的时候, 不能让同学修改博客即, 读加锁.

此时的并发程度较上面更低, 隔离性更高, 速度更慢, 数据的准确性进一步提高.

• 例3

  此时, 在读加锁和写加锁的条件下, 我正在读他的这篇文章, 但是, 这个同学是个肝帝, 一刻都不想闲下来, 还在不断地写新的博客, 我上一篇还没学习完, 他新的一篇又发布了. 此时, 虽然我读的读的文章内容没有改变, 但是文章的数量在发生变化.

这个问题成为 幻读 问题, 同一个事务中, 两次的结果集不同.

解决办法: 串行化, 彻底放弃掉并发性.

四. MySQL中的隔离级别

1. read uncommitted (读未提交)

不做任何的限制, 事务之间都是随意的, 并发的, 并发程度越高, 隔离性越低
有脏读 + 不可重复读 + 幻读问题.

2. read committed (读已提交)

对写操作加锁, 并发性降低, 隔离性提高.
解决了脏读问题, 仍有不可重复读 + 幻读问题.

3. repeatable read (可重复读)

对读写操作都加锁, 并发程度更加降低, 隔离性进一步提高.
解决了脏读 + 不可重复读问题, 仍存在幻读问题.

4. serialization (串行化)

严格串行化, 并发程度最低, 隔离程度最高, 解决了以上全部问题, 运行的速度最慢.

到此, 文章结束, 如有不足, 欢迎提出. 如有错误, 欢迎指正!