1.索引
1.1 概念
- 一种特殊的文件,包含着对数据表里所有记录的引用指针.
- 举个例子:我们常用的新华字典,上面可以根据拼音和偏旁部首来快速查询,这就是两种索引. 当然,书籍的目录也用到了索引.
1.2 作用
提高查询效率
1.3 缺陷
- 会占用额外的磁盘空间
- 可能会拖慢增删改的速度(新增数据,除了要在表里写,同时还要修改索引,删除与修改,如果删除修改的条件正好与索引匹配还可以快点,但是如果涉及索引列的话,这时还需要维护索引)
所以在创建索引时,要考虑到这些条件
1.4 使用
1.4.1 创建索引
(以列的维度来创建的)(可以对表中的一列或多列创建)
- 自动创建:主键,外键,unique
注意:外键这里也涉及到自动查询:
- 给学生表中插入一条记录,就需要查询classId是否在class表中的Id存在,这个查询就用到了class表的Id(主键自动生成的索引)
- 给班级表中删除一条记录,就需要查询classId是否在student表中存在,如果存在,删除会失败(子表对父表的反向约束),这个查询,就用到了student表的classId这一列对应的索引(外键自动生成的)
- 手动创建:create index 索引名 on 表名(列名)
注:创建索引操作,可能会非常危险!!!
如果表是空的,或者说表里的数据不多,创建索引就没事,但是如果表中包含了非常多的数据,创建索引就会引起非常大规模的硬盘IO操作
1.4.2 查看索引
show index from 表名
1.4.3 删除索引
drop index 索引名 on 表名
删除索引,只能删除手动创建的索引,自动生成的索引,无法删除
注:针对一个很大的表进行创建或者删除索引,就可能会导致出现问题--数据库卡死
1.5 索引的数据结构(重点)(面试题)
问:
MySQL的索引的数据结构到底是什么??并非是定式!!!取决于MySQL使用哪个存储引擎
根据前面所学的数据结构:
hash可以精准查询,不能够进行范围查询,不能进行模糊匹配查询,不适合数据库的查询场景
红黑树能进行范围查询和模糊匹配查询,但是会引入较多的硬盘IO
所以引入B+树
索引的底层是一个B+树
那么什么是B+树呢?这需要先了解B树.
1.5.1 B树
B树的核心思路,和"二叉搜索树"差不多,B树本质上是一个N叉搜索树
一个节点上,可以保存多个key,N个key就能延伸出来N+1个分叉来,相当于N个key划分了N+1个区间
如上图,B树查询的流程为:
拿着要查询的元素,从根节点出发,判断这个元素在根节点上是否存在,如果不存在,判断元素在哪个区间,然后沿着区间向下一个节点寻找,最终到叶子节点,如果还不存在,那就是真的不存在了.
优点:
在B树中,每个节点可以保存多个元素,当元素的总个数固定时,相比于二叉搜索树,B树涉及到的节点的个数就大大降低了,并且树的高度也大大降低了,所以在查询时,硬盘IO的次数也相应减少了.
对于数据库来说,每个节点,都需要把数据从硬盘上读出来,才能进行比较,所以一个节点上有多个key和一个节点上有一个key,硬盘IO的开销是差不多的(这就相当于倒垃圾,一次拿一个垃圾袋,和一次拿五个垃圾袋到倒垃圾的时间开销是差不多的,当然如果垃圾袋很多,一次拿不完,这时效率就明显降低了),所以查找时的比较次数并没有减少,但是节点个数变少了,所以读写硬盘的次数减少了,也就提高了数据库的查找效率
在对B树进行元素插入和删除操作的时候,就涉及到了拆分和合并的操作
注:虽然前面提出一个节点可以存多个key,但是这个key也是有限制的,当key到达一定程度时,就需要把这个节点进行拆分,把节点的一部分key以树的子节点的形式进行重新组织
1.5.2 B+树
B+树在B树的基础上,又进一步做了改进
特点:
- B+树也是N叉搜索树,N个key分出了N个区间,其中节点上的最好一个key式最大值(或最小值)
- 此时父亲节点的的key会在字节的中重复出现(以最大值或最小值的身份) (看起来有很多重复元素,浪费了空间,但是能够让叶子节点这一层包含整个数据的全集!!!!)
- 把叶子节点,按照链表的方式,首尾相连(双向),此时就可以快速进行"上一个,下一个元素"的查找,进一步方便进行"范围查询"
例如: 查找5<=id<=12的元素,这时只需要把把这段子链表拎出来就是范围查询的结果.
优势:
特别擅长范围查询
所有的查询操作,最终都会落在叶子字节的上,比较次数是均衡的,查询时间是稳定的
(有时候,"稳定"比"快"更重要,比如高考,平时发挥的稳定才更棒!!!)
由于叶子节点上是完整的数据全集,所以表的每一行数据的其他列.都可以保存到叶子节点上,而非叶子节点,只存储构建索引的key即可
例如下表,只存id即可
其实,在物理层面,不需要"表格"这样的数据结构,直接使用B+树来存储这个表的数据,"表格"只是用户看来这像是个表格而已
此时,非叶子节点的存储空间,消耗是非常小的!!!.可以在内存中缓存一份.此时,进行数据查询的时候,就可以通过内存来直接比较,从而更快的找到叶子节点上的记录,这样就会进一步减少硬盘IO的次数
2.事务
2.1 使用情况
在许多工作中,我们都需要进行多组sql操作.
举个例子,当我们进行购物时,下单后:
- 我的账户余额减少
- 店铺的商品库存减少
- 订单要新增一项
事务的本质,就是为了把多个操作,打包成一个操作来完成
- 这多个操作,要么全部都能执行,要么一个都不执行,当然不是真的没执行,执行成不成功,要执行了才能知道
- 在真正执行之前,是不会知道哪一步操作会失败的,如果执行到中间出错了,就需要自动把前面已经执行的操作,进行还原,还原到最初没有执行的操作
- 本质上,这里的"一个都不执行",不是真的没执行,而是看起来就和没执行一样
那么回滚操作是怎么实现的呢?
只要把事务中执行的每个操作,都记录下来(通过特定的日志),如果需要回滚,就直接按照之前操作的"逆操作"执行就可
比如:如果上一个操作时插入,逆操作就是删除......
再比如:进行一个转账:
1)给1的账户 -500
2)给2的账号 +500
如果执行第二步的时候程序崩溃了,此时就需要对第一步进行回滚,数据库就会自动的把第一步操作的修改给还原回去
那如果数据库挂了呢?重启了呢?
因为是通过日志,来记录事务执行的中间过程的,日志中的数据是始终在硬盘上存在的,及时是数据库服务器重启,就会在重启之后,对之前没有回滚完的操作继续进行处理
2.2 基本操作
| 开启事务 | start transaction |
| 提交事务 | commit |
| 回滚事务 | rollback |
注:一个事务中必要以提交/回滚来结尾,如果没有这两个操作,接下来的各种sql操作都会被认为是事务的一部分.
2.3 基本特征(重点)(面试题)
| 性质 | 特征 |
|---|---|
| 原子性 | 保证多个操作被打包成一个整体,要么全部执行,要么一个都不执行 |
| 一致性 | 事务执行之前和执行之后,数据能对上,数据不能离谱(约束,回滚机制) |
| 持久性 | 事务这里执行的操作,都是持久生效的(最终写入到硬盘上的),一旦事务执行成功了,这里的所有操作产生的修改,都是写到硬盘上的 |
| 隔离性 | 并发执行事务的时候,隔离性会在执行效率与数据可靠之间做出权衡 "隔离"描述的是同时执行的事务之间相互的影响 隔离性越高,并发性就越低,数据越可靠,性能越低 |
这里补充一个概念.什么是并发呢?
- 简单理解就是同时执行
- 服务器可以同一时刻给多个客户端提供服务,这些客户端也能给服务器提交事务,如果提交的两个事务是针对不同的数据库/不同的表,相互之间没什么影响,但是如果是修改同一个表,这个时候就存在麻烦了
举个例子:
客户端1和客户端2都把事务提交给了mysql服务器,宏观上看起来,是同时提交的,但微观上执行的时候仍然可能是有先后顺序的(就如两把尺子一样,虽然都是20cm长,可精确到mm,um,ns呢)
如上述两种情况,如果根据查询到的余额进行条件判断:如果余额>2000,就......
那这里是1000还是3000呢?就会存在歧义
所以顺序的先后就会影响最终的执行结果
- 在并发执行事务的时候,可能会产生以下问题:
| 类型 | 举例说明 | 解决方法 | 作用 |
|---|---|---|---|
| 脏读 | 当我在写作业时,我的舍友偷偷的看了一眼我的答案,他走了之后,我发现自己写错了,就把答案改了,这时舍友拿到的数据就是一个临时的数据,是不准确的,不是最终的结果. "脏"在这里指的就是临时的,并非准确结果的数据 | 答:给写操作加锁,当一个事务A在写的时候,其他事务B就不能读了,直到A写完数据,提交事务,B才能进行读取数据 | 降低了两个事务之间的并发性,提高了隔离性,降低了效率,使数据更准确 |
| 不可重复读 | 当我写完代码提交之后,我的舍友来读了,但是在他们读的过程中,我又进行修改了,修改之后,我又提交了,这就导致舍友读着读着,代码突然变了.这就造成了一个事务中,两次读到的数据不一样 | 给写操作和读操作都加锁,在写的时候,别人不能读(除非是我写完提交),此时,当别人在读的时候,我还可以再开启一个事务来写,在第二个事务提交之前,其它读事务读到的就是旧版本数据,第二个事务提交之后,别人读到的就是新版本数据(给读操作加锁:别人读的时候,我不能写), | 并发程度进一步降低,隔离性提高,执行效率降低,数据可靠性更高 |
| 幻读 | 我在写代码的时候,进行写加锁操作和读加锁操作,提交后,舍友来看了代码,我此时不修改正在读的代码,我创建一个新的类来编写,这就导致代码没变,但突然多出来了其它类 这样一个事务在多次读的过程中,虽然独到的数据的值是一样的,但是结果集却不同,比如,第一次读是10条消息,第二次是15条消息,后者的其中10条和前者是一样的,但多出来了五条 | 串行化,彻底放弃并发执行事务,所有的事务都是一个挨一个的串行执行(执行完一个事务,再执行下一个) | 并发性最低,隔离性最高,效率最低,数据最可靠. |
- MySQL提供了四种事务的隔离级别:
| 种类 | 解释 | 作用 |
|---|---|---|
| read uncommitted(RU) | 允许读未提交的数据,存在脏读,不可重复读,幻读问题 | 隔离性最低,并发程度最高,数据可靠性最低,效率最高 |
| read committed(RC),数据可靠性最低,效率最高 | 允许读取已经提交的数据(给写加锁了),解决脏读,存在不可重复读和幻读 | 隔离性提高了,并发性降低了,数据可靠性提高了,效率降低了 |
| repeatable read(RR) | 可以重复读取数据(给写操作和读操作都加锁),解决了脏读和不可重复读的问题 | 并发性又降低了,数据可靠性又提高了,效率又降低了 |
| serializable | 事务彻底的串行执行,解决了脏读,不可重复读,幻读, | 隔离性最高,并发性最低(没有),数据最可靠,效率也最低 |
- 注:根据实际的需求场景,来决定使用哪个隔离级别,找到一个效率和可靠性都能接受的情况. 大部分情况下,使用默认的隔离级别(RR)就可以了.
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
