关系型数据库的考点思维图
目录
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
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
