关系型数据库的考点思维图
目录
3-10 锁模块 -- MyISAM与InnoDB关于锁方面的区别
3-12 锁模块 --- 事务并发访问产生的问题以及事务隔离机制
3-14 锁模块--RR如何避免幻读()需要看行锁+gap锁
3-1 数据库架构
如何设计一个关系型数据库?
划分为两个部分
- 存储部分:相当于文件系统,将数据持久化到存储设备上
- 程序实例:用逻辑结构映射物理结构,在程序中提供获取以及管理数据的方式,以及必要的问题追踪机制
- 存储管理 -- 将数据的逻辑关系转化为物理存储关系。(尽可能一次性读取多行,减少IO以提高效率。
- 缓存机制 -- 把取出来的数据块放到缓存里以便下次使用时直接从内存返回。优化执行效率。(缓存不宜过大,且应有淘汰机制)
- SQL解析 -- 解析SQL语句。转换成机器可执行的指令。可以将SQL缓存
- 日志管理 -- 记录操作
- 权限划分 -- 进行多用户管理
- 容灾机制 -- 灾难恢复
- 索引管理 -- 优化数据查询效率
- 锁管理 -- 使数据库支持高并发
3-2 索引模块
常见问题
- 为什么要使用索引
- 什么样的信息能成为索引
- 索引的数据结构
- 密集索引和稀疏索引的区别
为什么要使用索引
先使用最简单的方式来进行数据查询,那就是全表扫描,即将整张表的数据全部或者分批次加载到内存当中。
存储的最小单位是块或者页,是由多行数据组成的。(整个表就是多个块或页)我们把这些块或者页加载进来。然后每个块或者页去轮询,找到目标并返回(适用与数据比较小的)。
很多情况下我们都要避免全表扫描的情况发生,因此数据库要引用一种更高效的机制,那就是索引
即索引可以快速查询数据
索引灵感来自字典,查字典可以通过部首,拼音,查数据可以通过主键、唯一键以及普通键(我们常说的关键字)。
什么样的信息能成为索引
- 主键、唯一键、普通键可以作为索引
索引的数据结构
除此之外,我们还要将这些将这些关键字以一种好的形式组织起来。即索引的数据结构。
- 建立二叉查找树进行二分查找。
- 建立B-Tree结构,
- 建立B+Tree结构(mysql),
- 建立Hash结构进行查找
3-2 优化索引-运用二叉查找树
二叉查找树是每个节点,最多只有两个字数的树结构,左子树或者右子树,左<中<右
一般为平衡二叉树
- 关键字和指向子树的指针
- 每个节点最多只有两个子数,左子树或者右子树,左<中<右
- 左子树和右子树的高度差不超过1
- 二叉查找树用的二分查找,对半搜索,时间复杂度为O(logn)
- 缺点1---删除后,可能会变成线性二叉树,时间复杂度为O(n),效率大大降低
- 缺点2---检索深度每增加一次就增加了一次IO,数据块会非常多,树的深度要很高,这样效率要慢很多
方法---降低时间复杂度又降低读取IO次数,让树变矮一些,存储的数据多一些。
这个时候就有了BTree
3-3 优化索引-运用B树
B树又叫平衡多路查找树,如果每个节点最多有m个孩子,那么这个树就叫做M阶B树
- 每个节点包含了:关键字和指向孩子的指针
- 每个节点最多能有几个孩子 ,取决于每个存储块ed容量以及数据库的相关配置
B tree的特征定义
- 根节点至少包括两个孩子
- 树中每个节点最多含有m阶个孩子(m>=2)
- 除了根节点和叶子节点外,其它每个节点至少有ceil(m/2)个孩子
- 所有叶子节点都位于同一层
目的只有一个---让每个索引块尽可能存储更多的信息,让树的高度尽可能减少IO次数
最后一个约束:假设每个非终端节点中包含有n个关键字信息;目的是用来限定B树节点的关键字数量以及大小。
- ki(i=1...n)为关键字,关键字升序排序
- 关键字的个数n满足:ceil(m/2)-1<=n<=m-1
- 非叶子节点的指针:p[1],p[2]....p[M];其中p[1]指向关键字小于K[1]的子树;p[M]指向关键字大于K[m-1]的子树,p[i]指向关键字属于K[i-1],k[i]的子树.
怎么查数据,怎样维护数据,查找效率O(logn)
如何查询数据,从根节点开始查询,如果该节点记录中没有要查询的数据,则满足约束规划,通过二分法定位到对应的指针,然后通过指针找到对应的节点,重复上面的步骤,直到最后找到要查询的数据
优缺点
- 优点-- 使用二分法,logn的查找效率
- 缺点-- 每个节点都存储了该记录的所有数据,如果记录数很多的时候,会显得很笨重,浪费了很多空间。对查找和维护都不方便。
3-4 优化索引 - 运用B+树
B+树是B树的变体,其定义基本与B树相同,除了:
- 非叶子节点的子树指针与关键字个数相同
- 非叶子节点的子树指针P[i],指向关键字值【K[i],K[i+1])左边开区间的子树
- 非叶子节点仅用来索引,数据都保存在叶子节点中:叶子节点才存储我们需要的数据--有可能是指向数据的指针,有的是主键值,或者有的就直接将数据存储上去
- 所有叶子节点均有一个链指针指向下一个叶子结点,按照大小顺序排列的,链接起来能够方便我们在叶子节点做范围统计
B+树所有的检索都是从根部开始,检索到叶子节点才能结束
B+更适合用来做存储索引
- B+树的磁盘读写代价更低---内部结构没有指向关键字的指针,只存放索引,因此节点比B树更小,存放的关键字更多,一次IO读取到的关键字更多,这样可以减少IO的次数,代价降低
- B+树的查询效率更加稳定---数据是存放在叶子节点上,意味着每次查询都要从根节点到叶子节点的查询路径,时间复杂度为O(logn),比较稳定
- B+树 更有利于数据库的扫描---因为数据只存放在叶子节点上,并且是有序的,可以更好的确定查询数据的范围。
3-5 优化索引-运用Hash以及BitMap
Hash结构只需要根据Hash函数的计算,只需要经过一次定位,就可以查找到 所需数据所在的bucket;查询效率比B+树高
不像B+树要经过根节点->非叶子节点->叶子节点,这样要经过多次IO访问
缺点:
- 仅仅能满足"="."IN",不能使用范围查询---Hash索引比较的是进行运算之后的哈希值,所以只能用于等值的过滤,不能用于基于范围的查询;因为经过Hash算法运算过后的值并不能保证和运算之前的值一样。
- 无法被用来避免数据的排序操作
- 不能利用部分索引进行键值查询---Hash索引在计算时是组合键,所以通过组合索引前面的单个索引查询的时候无法利用hash索引,而B+树是支持利用组合索引中的部分索引的
- 不能避免表扫描---Hash索引是将索引键通过Hash运算之后,将运算结果的Hash值和所对应的行指针信息存放在一个bucket中,由于不同的索引键存在相同的hash值,所以即使取出满足hash键值的数据,也无法从hash索引中直接完成查询,还是要访问bucket中相应的数据,进行比较,所以这是不能避免表扫描的原因
- 遇到大量的Hash值相等的情况后性能并不一定就会比B-Tree索引高---对于选则性比较低的索引键,将会存在大量指针存在于一个bucket的情况,从而造成整体性能非常低下。
bitmap索引只适用于一个字段只有固定的有限的值时
缺陷:锁力度非常大,新增修改的时候同一个页面的位图都会被锁住。不适合高并发的联机实物处理系统,适合并发较少,统计较多的系统
3-7 密集索引和稀疏索引的区别
密集索引和稀疏索引的区别
- 密集索引文件中的每个搜索码值都对应一个索引值
- 密集索引决定了物理表的排列顺序,一个表只能有一个排列顺序,所以一个表只能有一个密集索引项
- 叶子节点包含关键字+行数据(主键索引)
- 叶子节点含关键字+主键(辅助键索引)
- 稀疏索引文件只为索引码的某些值建立索引项
- 叶子节点含关键字+行地址(主键/辅助索引)
InnoDB和MyslSam
- InnoDB采用密集索引+稀疏索引,主键索引可以直接找到叶子节点中的数据,辅助索引需要先找到主键再通过主键B+树找到数据,即InnoDB数据和索引是存放在一个文件里的
- MyslSam全部采用稀疏索引,根据主键和辅助键的索引都只能找到一个地址信息,要再根据这个地址信息去另外一个文件中寻找数据,即MyslSam的索引和数据是分开放的
3-8 索引的额外问题--如何调优Sql
如何定位并优化慢查询Sql
- 根据慢日志定位慢查询Sql
- 利用explain工具分析sql
- Type:找到数据的方式 inedx->all,这两个表明本次走的是全表扫描,如果explain结果是这两个,那么语句是需要优化的
- extra
- Using filesort 文件排序,对结果使用一个外部索引排序,在内部或者磁盘上进行排序
- Using temporary 对查询结果排序时使用临时表
- 修改sql或尽量让sql走索引
3-9 索引--最左匹配原则
联合索引---由多列组成的索引
最左匹配原则
- Mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配,比如a=3 and b=4 and c>4 and d=6 如果建立(a,b,c,d)的索引,d是用不到索引的;如果建立(a,b,d,c)的索引a,b,d的顺序可以任意调整。
- =和in可以乱序,如果a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会优化成可以识别的形式
联合索引的最左匹配原则的成因
- Mysql先对第一个索引字段进行排序,在第一个字段排序的基础上,再对第二个字段排序,也就是说,第二个字段是无序的,所以直接用第二个索引字段作条件判断是用不到索引的。
- 例子:索引(area,title),根据 area=1 and title=" "、根据area查询则索引可以用到,如果只用title=“ ”则索引失效
3-9 索引--索引是建立越多越好吗
- 数据量小的表不需要建立索引,建立只会增加额外的开销。(比如就只有几页的宣传手册,还要弄目录??不值得)
- 数据变更需要维护索引,因此更多的索引意味着更多的维护成本。
- 更多的索引意味着也需要更多的空间(一百页的书,意味着有50页的目录)
索引小结;主键,唯一键的约束,还要看看Mysql里二点InnoDB和MyslSam中的索引
3-10 锁模块 -- MyISAM与InnoDB关于锁方面的区别
常见问题
- MyISAM与InnoDB关于锁方面的区别
- 数据事物的四大特性
- 事物隔离级别以及各级别下的并发访问问题
- InnoDB可重复读隔离级别下如何避免幻读
- RC,RR级别下的InnoDB的非阻塞如何实现
MyISAM与InnoDB关于锁方面的区别
- MyISAM默认使用的是表级锁 ,不支持行级锁
- InnoDB默认的是行级锁,也支持表级锁
MyISAM表锁
- 对表进行查询的时候,MyISAM会自动给表上了一个表锁,会锁住这整张表,并且会阻塞其他的语句对表进行更新。
- MyISAM数据进行select的时候会自动为数据加上一个表级的读锁。进行增删改的时候会为表加上表级的写锁;当读锁未被释放的时候,另一个session想对表加上写锁就会阻塞。
- 释放了所有的读锁后,所有的写锁就能够正常执行(同时只能有一个写锁)
显示加上读锁/释放读锁
- lock tables person_info_mysiam read;
- unlock tables;
读锁(select load in share mode)为共享锁---同时多个读锁不会发生阻塞,但是不能上其他的写锁
写锁(增删改 for update)为排他锁---上了写锁后,必须要等写锁释放后,才能上其他锁,否则会发生阻塞。同时只能有一个写锁。
InnoDB行级锁
- InnoDB也支持读锁和写锁,支持事务。
- InnoDB默认是行级锁,用的是二段锁,加锁和解锁是分两步进行的,在书事务的异步操作进行加锁,在提交commit的时候进行解锁。
证明InnoDB是行级锁,MyISAM是表级锁
- 当对myisam的数据进行select row1的时候,再去对row1或者row2数据进行update,发现无法update,需要等select执行完成。
- 而innoDB的select row1同时可以对row2update,但是无法对row1update,所以证明是行级锁。但是innoDB不会默认加行级锁。
用到表级锁的时候,只要操作表里面的数据都会上表锁,因此表级锁和索引无关;
行级锁是否和索引有关?
- InnoDB在Sql没有用到索引的时候,用的是表级锁;Sql用到索引的时候,用的是行级锁以及gap锁
InnoDB除了支持行级锁之外,还支持表级的意向锁。
- 意向锁分为共享读锁IS和排他写锁IX;为的是进行表级别的操作的时候,不用去轮询每一行去看看有没有上行锁。
行级锁一定比表级锁好吗?不是,粒度越细的锁,执行代价越大。
MyISAM的使用场景
- 频繁执行全表count语句 --- 对于InnoDB来讲它是不保存表的具体行数的,执行select count(*)from table 时需要重新统计。而MyISAM用一个变量保存了整个表的行数,执行上述语句只需要读出该变量即可。
- 对数据的增删改的频率不高,查询非常频繁 --- 因为增删改会涉及锁表操作,虽然插入操作可以通过配置从表的尾部插入数据,但是依然会产生很多碎片,所以比较影响性能,但是纯查询效率是非常高的。
- 没有事务
InnoDB使用场景
- 数据的增删改查都相当频繁::增删改只是某些行被锁,在大多数情况下避免了阻塞,而不是MyISAM每次增删改都回去锁住整张表。
- 可靠性要求比较高,要求支持事务。
数据库锁的分类
- 按锁的粒度划分,可分为表级锁、行级锁、页级锁(DBD引擎)。
- 按锁级别划分,可分为共享锁、排他锁。
- 按加锁方式划分,可分为自动锁(意象锁、MyISAM的标锁,以及增删改加上的锁,因为这是MySQL自动为我们上的)、显式锁。
- 按操作划分,可分为DML(对数据进行操作上的,包括对数据的增删改查)、DDL锁(对表结构进行变更的如alter table语句加上的锁)。
- 按使用方式划分,乐观锁 、悲观锁。
乐观锁---认为数据不会造成冲突,提交时进行检测,一般通过记录版本实现,版本号或者时间戳,提交更新的时候检查版本,当前版本号与第一次取出作对比,不一致的时候则不更新。在修改数据的时候把事务锁起来,通过version的方式来进行锁定
悲观锁 -- 对本系统和外界的锁保持态度,通过数据库中的锁机制,先取锁再访问,有额外开销,增加死锁机会,只读没有必要加锁。
3-11 锁模块---数据库事务的四大特性
ACID
- A原子性:事务包含的所有操作要不全部执行要不全回滚
- C一致性:事务应确保数据库状态完整性,一致性
- I隔离性:一个事务的执行应不影响其他事务的执行
- D持久性:永久保存在数据库中,当系统或介质发生故障时,数据不应丢失,innodb就保存再文件中
3-12 锁模块 --- 事务并发访问产生的问题以及事务隔离机制
Mysql会利用锁机制创造不同的事物隔离级别,按照事物隔离级别从低到高的顺序进行讲解
第三个问题:事务并发访问引起的问题以及如何避免
- 更新丢失 —— 一个事务的更新覆盖了另一个事务的更新;Mysql的所有事务隔离级别均可避免
- 脏读 —— 一个事务读到另一个事务未提交的更新数据;READ - COMMITTED事务隔离级别以上可避免
- 不可重复读 —— 事务A多次读取同一数据,而存在事务B对该数据做出修改并提交。那么事务A读取同一个数据得到的结构就不一致。如果用原来的结果进行操作,则会造成数据紊乱(很严重的事情)。REPEATABLE-READ(InnoDB默认的隔离级别)
- 幻读 —— 事务A读取若干行数据,数据B以插入增或删除行的方式来修改事务A的结果集——SERIALIZABLE事务隔离级别可以避免。
3-13 锁模块--当前读和快照读
当前读就是加了锁的增删改查语句,读取的是记录的最新版本,读取之后还要保证其他并发事务不能修改当前记录,对当前的记录加锁。·
当前读:select...lock in share mode , selec...for update, update , delete, insert
快照读:
- 不加锁的非阻塞读(在以事务隔离级别不是serializable的情况下),select
- 基于提升并发性能的考虑,快照读的实现是基于多版本并发控制,即MVCC,在很多情况下避免了加锁操作,因此开销更低。
- 因为是多版本,快照读有可能读到的并不是最新版本,可能是之前的历史版本
如何实现快照读(伪MVCC)。RC、RR级别下的InnoDB的非阻塞读如何实现
- 数据行里的DB-TRX-ID、DB-ROLL-PTR、DB-ROW-ID字段
- DB_TRX_ID :标识最后一次对本行数据做修改事务id
- DB_ROLL_PTR:回滚指针,指向undo日志
- DB_ROW_ID:行id
- undo日志——当我们对记录进行修改时,就会产生undo记录,undo记录中存储的是一个老版数据,当一个旧的事务需要读取数据时,为了能够读到老版本的数据,需要顺着undo链找到满足其可见性的记录。
- 主要分为两种;insert undolog 和 update undolog
- insert undolog表示事务对insert新纪录产生的undolog,只在事务回滚时需要,并在事务提交的时候即时丢弃
- update undolog 事务对记录进行select或者update操作时产生的undolog,事务回滚时需要,快照读也需要,所以不会随意删除
- read view——可见性判断,当我们执行快照读的select的时候,会针对我们查询的数据,创建一个read view,来决定当前事务能看到的是哪个版本的数据。可见性算法。
3-14 锁模块--RR如何避免幻读()需要看行锁+gap锁
日志
- 重做日志文件(redo log)指事务中的操作的任何数据,将最新的数据备份到一个地方。为了事务的持久性而出现的产物
- 回滚日志(undo log)用来回滚行记录到某个版本。事务提交之前,undo保存了未提交之前的版本数据。undolog中的数据可作为数据旧版本快照供其他并发事务进行快照读。保证了事务的原子性
- binlog 记录所有数据库表结构变更以及表数据更改的二进制文件
3-15 关键语法讲解 ???需要看
GROUP BY
HAVING