《Mysql》数据库索引与事务

《Mysql》数据库索引与事务

前言:

本文主要介绍

1. 数据库索引的基本概念以及索引所使用数据结构所带来的优势.

   • 理解数据库为什么使用B+树作为索引的数据结构,而不是使用红黑树/哈希表

   • 理解B+树的结构特点以及优势

2. 数据库事务的基本概念以及事务所解决的问题,数据库事务的四个隔离等级.

数据库索引

理解索引以及其应用场景:

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。

也就是说 如果数据库的内容相当于一本书,那么索引就相当于书的目录,我们可以根据目录快速地找到书的内容,即我们也可以通过索引快速查询数据库的数据.

索引存在的目的就是希望可以加快查询数据的速度.

那么索引可以加快查询的速度,但是,索引的创建也导致了数据库增删改的速度会变慢,我们将索引简单理解为目录,如果我们需要对书的内容进行增删改,那么目录也要随之改变,这样就会降低了增删改的速度了…

并且创建索引需要实现复杂的数据结构,也会耗费一定的空间.

因此索引适用于以下场景:

• 项目对空间不紧张,但是对时间要求高.

• 项目需要频繁的查询但是不需要频繁地增删改.

索引的基本语法:

1. 查询索引:

   show index from 表名
   show index from student;
   

2. 创建索引

   create index 索引名 on 表名(value)
   create index student_name_index on student(name);
   

3. 删除索引

   drop index 索引名 on 表名(value)
   drop index student_name_index on student(name);
   

创建索引和删除索引都是十分危险的操作,

如果针对的是一张数据量巨大的表创建索引,那么会导致创建索引的过程中产生大量的磁盘IO,此时主机可能直接就卡了.

实现索引的数据结构:

常见数据结构对于实现索引的弊端

1. 顺序表与链表:容易导致长度过长,导致磁盘IO操作过多.

2. 栈和队列: 不符合创建索引的特征.(一般栈和队列只用于存储临时数据)

3. 二叉树: 由于二叉树只有左右孩子,在数据量较大时,依然会导致树的高度过高,也容易导致磁盘IO操作过多. 同理,虽然二叉搜索树,红黑树,AVL树都对二叉树进行了改进,使得二叉树得以更加地平衡,不会导致二叉树的高度太高,导致依然不太适用于索引.

4. 哈希表: 虽然查询的时间复杂度为O(1),但是不支持范围查找和模糊匹配.也不适用于索引.

为了降低高度 ,我们适用N叉树来实现索引

B树与B+树

B树:

尽管B树已经适合于索引,但是由于B+树比B树更适合实现索引.

B+树:

B+树实现规则:

1. 每一个结点都有一棵子树.

2. 父节点的元素都会存在于子树中,是子树中的最大值或者最小值.

3. 最下方的叶子结点适用链表串连起来.

为什么使用B+树作为数据库索引.

1. B+树的查询速度快,类似于二叉搜索树.

2. B+树的单个结点存储更多的数据,使得树的高度降低,减少了磁盘IO.

3. B+树的叶子结点通过链表串连,非常适合于范围查询.

4. B+树的叶子结点包含了父结点的数据,因此实现索引的时候,可以只给父节点存储来来作为索引的id,这样就大大减少了存储空间的同时,同时由于父节点的大小十分地小,有了缓存到内存的可能性,这样就可以大大地减少磁盘IO了.

数据库事务

什么是事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。

我们在操作数据库时,我们经常会希望一连串的sql命令可以是一个整体,当执行过程中出现问题,我们可以直接倒退恢复回整个整体的执行之前.也就是说一旦出现问题,我们希望问题的影响是最小的.

所以数据库事务就是将一系列sql操作组合成一个整体.

sql事务语法

start transcation
-- 多条sql语句 (事务)
rollback/commit

事务的基本特征:

1. 原子性:

使用原子来代指不可分割的内容,对于数据库事务而言,要么执行完事务中的所有sql命令,要么就都不执行(并非真的不执行,而是出现了问题通过回滚的方式来恢复).

   数据库如何进行回滚:
   数据库的每一个操作,数据库都会在内部进行记录,特别是事务的操作,如果出现问题,就可以根据之前的sql记录进行回滚.

2. 一致性:

   数据库中的数据是一致正确,没有纰漏

3. 持久性:

   数据库事务的操作是在磁盘上进行操作的,一旦执行成功,就会永久修改磁盘的数据内容,就算主机故障,数据改变依然存在

4. 隔离性:

   通过引入隔离性来解决多个事务并发执行所可能出现的问题.

理解数据库事务并发执行的问题以及四个隔离等级.

数据库不对多个事务进行隔离限制,达到最高的并行效率,隔离性最弱的隔离等级为:read uncommitted

脏读问题以及解决方案

当A事务与B事务并发执行时,A事务执行了修改数据库数据的sql语句,而在A事务执行完成之前,B事务可能读取了数据库的数据,这时的数据库数据还未完成修改,故而B事务读取到的是为数据库的临时结果,后续可能被A事务重新修改了.

此时B事务的读取行为就称之为"脏读"

为了解决脏读问题:

规定,在A事务执行完提交之前,B事务不能读取数据.

此时数据库的隔离级别为:read committed. (并发效率进一步下降,隔离性进一步加强)

不可重复读问题以及解决方案

在A事务执行完毕提交结果之后,B事务开始读取数据库数据,但是在B事务读取数据的过程中,A事务又一次启动,导致了数据库的数据发生了变化,导致B事务两次读取到的结果不一致,

此时B事务的读取行为就称之为"不可重复读"

为了解决不可重复读问题:

规定在B事务读取的过程中,A事务不能再一次启动去修改数据.

此时数据库的隔离级别为:repeattable read.(并发效率进一步下降,隔离性进一步加强)

幻读问题以及解决方案

在A事务执行完毕提交结果之后,B事务开始读取数据库数据,但是在B事务读取数据的过程中,A事务再一次启动,但是没有修改了B事务在读的数据库数据,而是修改了数据库的另一个结果集,

例如本来只有student表,但是没有teacher表
A事务为数据库增加了teacher表,虽然 没有去修改student表的数据了.但是多增加了一个teacher表,
这就导致了B事务可能执行了一条语句:select * from mydata.
就会出现了另一个表teacher.

为了解决幻读问题:

规定了 在B事务读取的过程中,A事务不允许去干别的事情,做到严格的读写串行.

此时数据库的隔离级别为:serializable,(并发效率最低,隔离险最强)

加油!