SQL索引事务

SQL索引事务

索引

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

1.1 查看索引

show index from 表名

 

现在这个表中没有索引,那么我们现在将这几个表删除之后创建新表

我们现在建立一个班级表一个学生表,并且学生表与班级表存在外键约束

班级表

学生表

我们可以看到子表中有两个索引

1.2 创建索引

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

1.3 删除索引

drop index 索引名 on 表名

删除索引,只能针对手动创建的索引,自动生成的索引(如:因主键约束与外键约束所生成的索引),是不能被删除的.

删除索引这个操作也是比较危险!

如果我现在确实需要`给一个已经有很多数据的表 创建/删除 索引,并且这个数据库还是生产环境的数据库,怎么办?

冗余

数据库服务器往往也不是单台服务器,为了整个系统的可靠性,通常会搞多个mysql服务器节点,这些节点的数据都是一样的,能够提供相同的服务(其中某个挂了,也不影响大局)

准备好一个新的MySQL服务器,把表和索引都创建好,然后把数据都导入过来,再把要替换的MySQL服务器关闭,把新的MySQL服务器替换上去就行了

索引底层的数据结构(非常重要)

MySQL的索引的数据结构到底是啥样的数据结构?并非是定式!!取决于MySQL使用哪个存储引擎

答:MySQL这个程序,里面包含很多模块

有点负责解析SQL,有的负责网络通信,有的负责储存数据.

存储引擎,本质上就是代码中的一个模块(这里包含了若干个代码文件以及一大堆具体的代码)

具体如何存储数据,MySQL支持多种存储方案

innodb当下最主流的一种方式

然而数据库这块组织数据使用的数据结构,是在硬盘上的,内存上的数据结构,对于访问操作来说,是不敏感的(找数据的过程,花时间多,真正访问的时候时间不多)硬盘上的数据结构,对于访问操作来说,比较敏感!!读写一次硬盘,开销远远大于内存的

索引的数据结构

hash

不能够进行范围查询,不能够进行模糊查询

解释:还记得我们学hash的时候吗?哈希其实是运用hash函数先计算下标然后进行查找,那么就导致对于hash来说,找到的只可能是准确的数据,不可能进行模糊查询的这一类操作的

红黑树

能够进范围查询和模糊匹配,但是引入较多的硬盘IO

B+树,是为了数据库量身定做的数据结构(针对于MySQL的innodb引擎而言)其他因为存储引擎的不同也可能会用到hash,这不过这种引擎所制作的数据库只能进行精准查询,会损失一些功能.

而实际上 B 树和二叉搜索树差不多.B树本质上是一个N叉搜索树

一个节点上,可以保存多个key.N个key就能 衍伸出N+1个分叉来.

N个key就划分出了N+1个区间

上图就是B树的物理图

此时每个节点上,都可以保存多个元素了,当总元素的个数固定的时候,相比于二叉搜索树,涉及到的节点的总数就大大降低了,树的高度也就大大降低了,硬盘IO也就减少了.

对于数据库来说,每个节点,都需要把数据从硬盘上读出来才能进行比较,一个节点上有多个key,和一个节点上有一个key,硬盘IO的开销是差不多的.

然而 B+树 才是数据库索引的主角,在 B树 的基础上,又进一步做出了一些改进=>针对数据库的查询场景展开的.

1. B+树 也是N叉搜索树,但是N个key分出了N个区间,其中节点上最后一个key就是最大值了
2. 父节点的key会在子节点中重复出现(而且是以最大值的身份)

看起来是有很多重复元素,浪费了空间,实际上能够达成一个重要的效果:叶子结点这一层,包含了整个数据的全集. 

1. 将叶子节点,按照链表(双向)的方式首尾相连

2. (此时通过叶子节点之间的这个连接,快速找到"下一个""上一个"元素,进一步也方便进行范围查询)

   上面三个是B+树的特点,这些特点,产生的优势是什么呢?

3. 特别擅长范围查询
4. 所有的查询操作,最终都会落在叶子节点上,比较次数,是均衡的.查询时间是稳定的!!!
5. 由于叶子节点上是完整的数据全集,因此每一行数据的其他列,都可以保存到叶子节点上,而非叶子节点,只存储构建索引的key即可(只存id就行了)

其实在物理层面上不需要"表格"这样的数据结构,直接使用B+树来存储这个表的数据,"表格只是用户看起来像是个表格而已" 

用户看到的

实际存储的

此时,非叶子节点的存储空间,消耗是非常小的!!!,小到可以在内存中缓存一份!!!此时,进行数据查询的时候,就可以通过内存来直接进行比较从而更快速地找到叶子结点上的记录(进一步减少了硬盘IO的次数)

事务

事物的本质就是将多个操作打包成一个操作来完成的,也就是说这几个操作,要么都执行,要么都不执行,就相当于把他们几个操作捆绑起来,也被称作具有"原子性"

注意:"一个都不执行"不是真的没执行.执行成不成功要执行了才知道,比如我们在一个事物中有三个操作,在真正的执行之前我们是不知道1,2,3哪一步会失败的.如果执行到中间出错了,就需要字自动的把前面已经成功执行的操作,进行还原,还原回最初的模样(这种操作,叫做回滚(rollback)),这样就和看起来没有执行是一样的了,那么怎么进行回滚呢?

我们只需要将之前的操作都记录下来,需要回滚的时候,就对之前的操作进行"逆操作"就可以了(通过特定的日志,来记录数据库事务操作的中间过程),那么如果在回滚的过程中数据库挂了,数据库服务器重启了怎么办?

因为我们是通过日志来进行回滚的,日志上的数据始终在硬盘上存在的.即使是数据库服务器重启,就会在重启之后,针对之前没有回滚完的情况继续进行回滚

使用

1.开启事务: start transaction

2.执行多条SQL语句

3.commit提交事务: 把这些SQL按照原子的方式来进行执行(带有回滚机制)

rollback:手动触发回滚

注意:一个事物,务必要以 commit 或者 rollback 结尾

如果没有这两个操作,接下来的各种SQL操作都会被认为是事务的一部分.

事务的基本特性

1.原子性:保证多个操作被1打包成一个整体,要么能够全部执行正确,要么就一个都不执行.

2.一致性:事务执行之前,和事务执行之后,数据都能对上,数据不能离谱(用约束以及回滚机制来保证数据是靠谱的)

3.持久性(在计算机中,谈到持久,十有八九都是和硬盘有关的,硬盘上存储的数据,就是持久化存储):事务这里执行的各种操作,都是持久生效的,也就是说,最后会写入硬盘当中的,一旦事务执行成功了,那么这里所有操作产生的修改,都是写到硬盘里的.

4.隔离性:并发执行事务的时候,隔离性,会在执行效率和数据可靠之间做出权衡."隔离"描述的是同时执行事务之间,相互的影响,隔离性越高,并发性就越低,数据越可靠,性能就越低.

那么接下来说一下什么是并发?

并发可以简单理解成同时执行

数据库是一个客户端 服务器 结构的程序,既然是服务器,服务器就可以同一时刻,给多个客户端提供服务,这两个客户端,就能给服务器提交事务,如果提交的这两个事务,是修改不同的数据库/不同的表,相互之间是没啥影响的,如果这两个事务,修改的是同一个表,这个时候就可能存在麻烦

如何解决脏读问题?

给 写 操作加锁,一个事务A写的时候,其他事务B不能读了.知道A事务写完数据,提交事务,其他的事务B才能来读取数据.

引入了写加锁,降低了两个事务之间的并发性,提高了隔离性,降低了效率,使数据更准确了

在同一个读取数据的事务中,可能会涉及到多次读操作,多个操作读到的数据,不一样.

如何应对不可重复读?

给读操作也加锁

给写操作加锁的意思,就是我在写的时候,别人不能读(除非是我写完提交,别人才能读),此时别人读的过程中,我还可以再开启一个事务来写,第二个事务提交之前,其他读事务独到的都是旧版本的数据,第二个事务提交之后,别人再读读到的就是新版数据了.

给读操作加锁的意思是,别人读的时候,我不能写了.

此时并发程度进一步降低了,也就是执行效率降低了

隔离性进一步提高了,也就是数据可靠性更高了

一个事务在多次读的过程中,虽然读到的数据的值是一样的,但是结果集不同,比如第一次读是10条记录,第二次读是11条记录,11条记录中的10条,和之前的10条是一模一样的,但是多出来一个

可以视为是:不可重复读 的特殊情况.

解决幻读的办法:串行化(彻底放弃并发执行事务,所有的事务都是一个挨一个的串行执行(执行完一个事务,再执行下一个事务)并发性是最低的,隔离性是最高的,效率是最低的,数据是最可靠的)

小结

在并发执行事务的过程中,可能产生以下问题

1.脏读

读到了写事务提交之前的中间数据(脏数据)

解决方法:写加锁,提交之前,不能读

2.不可重复读

一个事务之内,多次读取同一个数据,发现数据不一样(在读的过程中,另一个事务修改了数据)

解决方法:给读加锁,读的时候,不能修改了

3.幻读

一个事务之内,多次读到的数据,值相同,但是结果集不同

解决方法:彻底串行化,完全放弃并发执行

MySQL提供了四种事务的隔离级别:

read uncommitted(RU):允许读未提交的数据(存在脏读,不可重复读)

此时隔离性最低,并发程度最高,数据可靠性最低,效率最高.

read committed(RC):允许读取已经提交的数据(给写加锁了),解决了脏读问题,但是存在不可重复读和幻读,此时隔离性提高了,并发性降低了,数据可靠性提高了,效率降低了

repeatable read(RR 默认的隔离级别):可以重复读取数据(给写操作和读操作都加锁)解决了脏读和不可重复读的问题,存在幻读问题,此时隔离性又提高了,并发性有降低了,数据可靠性又提高了,效率又降低了

serializable:事务彻底的串行执行.解决了脏读,不可重复读,幻读的问题,隔离性最高,并发性最低(没有),数据最可靠,效率也最低.