数据库基础知识点
盘点了常考的数据库基础知识点
- 考点：
- 如何设计一个关系型数据库 √
- 索引模块
- 如何定位并优化慢查询Sql √
- 根据慢日志定位慢查询Sql
- 联合索引最左匹配原则 √
- 索引是越多越好么 √
- 锁模块
- 关键语法
- 理论范式
- 数据库连接
考点：
架构，索引，锁，语法，理论范式
如何设计一个关系型数据库 √
RDBMS：关系数据库管理系统
两大部分：
存储部分和程序部分
索引模块
为什么要使用索引 √
避免全表扫描去查找数据，提升检索效率
什么样的信息能成为索引 √
主键，唯一键等能让数据具备一定区分性的字段
索引的数据结构 √
二叉查找树，B-Tree，B+Tree（MySql），Hash结构
B-Tree -
即遍观整棵树，子节点最多的个数是m，那么这棵树就是m阶树。
定义
根节点至少包括两个孩子
树中每个节点最多含有m个孩子（m>=2）
除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子
所有叶子节点都位于同一层
假设每个非终端结点中包含n个关键字信息，其中
A） Ki（i=1…n）为关键字，且关键字按顺序升序排序K(i-1)<Ki
B） 关键字的个数n必须满足：[ceil(m/2)-1]<=n<=m-1
C） 非叶子结点的指针：P[1]，P[2]，…，P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于（K[i-1],K[i]）的子树
B+树是B树的变体，其定义与B树相同，除了
非叶子节点的子树指针与关键字个数相同
非叶子节点的子树指针P[i]，指向关键字值[K[i],K[i+1])的子树
非叶子节点仅用来索引，数据都保存在叶子节点中
所有叶子节点均有一个链指针指向下一个叶子节点
结论 √
B+Tree更适合用来做存储索引
B+树的查询效率更加稳定
B+树更有利于对数据库的扫描
拓展阅读
https://www.jianshu.√com/p/7a2017e830a0
Hash索引
缺点：
仅仅能满足“=”，“IN”，不能使用范围查询
无法被用来避免数据的排序操作
不能利用部分索引键查询
不能避免表扫描
遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
Bitmap √
位图索引是一种使用位图的特殊数据库索引。
主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等),
索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,
位置编码中的每一位表示键值对应的数据行的有无.一个块可能指向的是几十甚至成百上千行数据的位置.
适合：
这种方式存储数据,相对于B+Tree索引,占用的空间非常小,创建和使用非常快.
当根据键值查询时,可以根据起始Rowid和位图状态,快速定位数据.
当根据键值做and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,快速得出结果行数据.
不适合：
不适合键值较多的列（重复值较少的列）；
不适合update、insert、delete频繁的列，代价很高。
密集索引和稀疏索引 √
Ø 密集索引文件中的每个搜索码值都对应一个索引值
Ø 稀疏索引文件只为索引码的某些值建立索引项
密集索引：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引
稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息及其主键
InnoDB
Ø 若一个主键被定义，该主键则作为密集索引
Ø 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
Ø 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
Ø 非主键索引存储相关键位和其对应的主键值，包含两次查找
主键索引和非主键索引 -
主键索引和非主键索引的区别是：非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。
InnoDB使用的是密集索引
- 主键索引的B+Tree的叶子节点包含索引和数据
红色的是稀疏索引的条件筛选，需要两个步骤
第一步：在稀疏索引的B+Tree中索引该键第二步：使用主键在主键索引B+Tree中再执行一次检索操作
MyISAM使用的均为稀疏索引
节点结构完全一致，只是存储内容不一样。主键索引B+Tree存储主键，辅助键B+Tree存储了辅助键。索引和数据是分开存储的。通过辅助键检索无需访问主键的索引树。
聚簇索引和非聚簇索引
聚簇索引：将存储数据与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储与索引分开，索引结构的叶子节点指向了数据的对应行。
如何定位并优化慢查询Sql √
Ø 根据慢日志定位慢查询sql
Ø 使用explain等工具分析sql
Ø 修改sql或者尽量让sql走索引
根据慢日志定位慢查询Sql
显示慢查询配置信息
显示慢查询数量
本次回合的慢条数，重启客户端清零
打开慢日志
重启无效
设置慢查询时间阈值
需要重新连接数据库
可以对my.ini或者my.cnf去配置，永久保存
利用Explain分析sql
Type从最优到最差
红色的为全表扫描
Extra
Extra中出现以下2项意味着MySQL根本不能使用索引，效率会受到重大影响，应尽可能对此进行优化
| Etra 项 | 说明 |
|---|---|
| Using filesort | 表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容，可能在内存或者磁盘上进行排序，MySQL中无法利用索引完成的排序操作称为“文件排序” |
| Using temporary | 表示MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询group by |
修改sql尽量让sql走索引
走索引
Name没有走索引，而account有索引，因此我们可以将语句优化。
加索引
联合索引最左匹配原则 √
删除title后依然走联合索引
把area删除只留下title后就不走联合索引了
在联合索引中area在最左边
1． 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a=3 and b=4 and c>5 and d=6，如果建立（a,b,c,d）顺序的索引，d是用不到索引的（c可以用到索引），如果建立（a,b,d,c）的索引则都可以用到，a,b,d的顺序可以任意调整
2． =和in可以乱序，比如a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
成因：第一个字段是绝对有序的，第二个字段是无序的。
直接用第二个字段是没办法走索引的，因为它是无序的。
用column3建立b+tree，通过关键词查找alice然后排序。单靠column2无法走b+tree索引。
索引是越多越好么 √
Ø 数据量小的表不需要建立索引，建立会增加额外的索引开销
Ø 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
Ø 更多的索引意味着也需要更多的空间
锁模块
二段锁
两阶段的含义是指在同一个事务内，对涉及的所有数据项进行先加锁，然后才对所有的数据项解锁。
但第一阶段加共享锁后影响了其他事务的写操作、加排它锁后影响了其他事务的读操作，所以较大地影响了其他事务的运行。只有第二阶段释放了所有的数据项上的锁之后，才能运行其他要操作相同数据项的事务。
MyISAM与InnoDB关于锁方面的区别
Ø MyISAM默认用的是表级锁，不支持行级锁
Ø InnoDB默认用的是行级锁，也支持表级锁
MySql客户端中一个Tab（标签页）就是一个Session
两个表除了引擎都一样
使用MyISAM引擎的时候，对一个表进行查询等操作的时候会加上数据的读锁，其他Session进行增删改的时候会加一个表级别的写锁，当读锁未被释放的时候，另外一个Session想要给表加上写锁就会被阻塞。
同理上写锁的时候没办法再上读锁，上写锁后也不能再上写锁。
如果在执行读操作后加上for update，其他读的操作也会被block。
InnoDB是二段锁，且默认打开autocommit。
两种方法都可以。
Set autocommit只对当前Session有用。
上共享锁后不能修改数据但是同样可以读数据（同一行）
当不走索引的时候，InnoDB也会锁住整张表，此时用表级锁。
X排他锁：增删改以及Select for update的时候。
S共享锁：读操作
MyISAM适合的场景 √
频繁执行全表count语句
对数据进行增删改的频率不高，查询非常频繁
没有事务
InnoDB适用的场景 √
数据库增删改查都相当频繁
可靠性要求比较高，要求支持事务
数据库锁的分类：-
- 按锁的粒度划分，可分为表级锁、行级锁、页级锁
- 按锁级别划分，可分为共享锁、排它锁
- 按加锁方式划分，可分为自动锁、显式锁
- 按操作划分，可分为DML锁、DDL锁
- 按使用方式划分，可分为乐观锁、悲观锁
DML(Data Manipulation Language)数据操纵语言：
适用范围：对数据库中的数据进行一些简单操作，如insert,delete,update,select等.
DDL(Data Definition Language)数据定义语言：
适用范围：对数据库中的某些对象(例如，database,table)进行管理，如Create,Alter和Drop.truncate
多Session之间的互锁就是悲观锁的实现。
乐观锁不使用数据库提供的锁机制
- 一般实现的两种方式：版本号和时间戳
乐观锁加入了版本标识
程序1：
先执行
程序2：
数据库事务的四大特性
ACID：
原子性Atomic：事务中各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败，就像原子一样不可分割；
一致性Consistency：事务结束后系统状态是一致的；事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。
隔离性Isolation：并发执行的事务彼此无法看到对方的中间状态；
持久性Durability：事务完成后所做的改动都会被持久化，即使发生灾难性的失败。通过日志和同步备份可以在故障发生后重建数据。
事务隔离级别以及各级别下并发访问问题
1. 更新丢失
Mysql所有事务隔离级别在数据库层面上均可避免
2. 脏读（读取未提交数据）
A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读。
Read-committed事务隔离级别及以上可以避免
通过
可以查到事务的隔离级别
设置事务隔离级别
设置成最低的事务隔离级别
然后Session1操作
’ 
Session2操作
此时Session1还未提交
然后Session1进行回滚，然后再查询
金额变成1000.
但是Session2以900位底进行操作
导致金额变成了1100。
但是应该有1200的。
只需要把事务级别设置成read commited以上就可以了
然后Session1进行红框操作
然后此时Session1并未提交，然后
Session2进行查询操作
此时Session2读到的值是1100（还未修改的数据）
可以避免脏读
3. 不可重复读（一个事务范围内两个相同的查询却返回了不同数据）
事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。
Repeatable-read事务隔离级别以上可以避免
Session1：
Session2：
存入数据后发现已经为1600了
此时
Session1进行读取操作
由于设置了read-committed避免了脏读，因此还是1300
然后
Session2提交修改
此时，Session1再次读取
发现balance变成了1600
这就是所谓的不可重复读，即不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
解决方法：
将事务隔离级别改成：可重复读
然后此时：
Session1和Session2都开启事务
Session1读取账户
Session2：存入400
但是并未提交
此时
Session1读取
然后Session2进行提交操作
Commit
Session1再次进行查询操作
即使此时读取的是1600，但是再去做提交还是正确的
4. 幻读：（前后多次读取，数据总量不一致）
Serializable事务隔离级别可避免
Session1使用当前读读取整表数据
Session2进行插入操作
然后commit
紧接着Session1对所有余额进行更新
结果却更新了新插入的数据
事务在插入已经检查过不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测获取到的数据如同鬼影一般。
把隔离级别改成Serializable后，所有sql操作都会加上锁，即使不加上lock in share mode；
InnoDB可重复读隔离级别下如何避免幻读
Ø 表象：快照读（非阻塞读）–伪MVCC
Ø 内在：next-key锁（行锁+gap锁）
当前读
Select…lock in share mode（共享锁）, select…for update
Update, delete, insert
其余都是加上排他锁
读取的是记录的最新版本，并且读取后还需保证其他并发事务不能修改当前事务，对读取的事务加锁。
快照读
不加锁的非阻塞读，select（不在Serializable级别下才成立）
在Serializable下是串行读，所以快照读退化成当前读。
RC级别下
Session1：（RC）
Session2中
在Session1中用当前读:
快照读
快照读已经为600
在RC下，快照读和当前读的效果是一样的
在RR级别下
在Session3中先使用快照读
在Session4中将余额改成300
Session3当前读：
更新成300
快照读：
快照读还是600
有没有可能在快照读下读到最新的数据呢？ 可以！
在Session3中先不做查询，先在Session4中对balance更新
然后在Session3中使用当前读和快照读都是0
Gap锁
Gap是索引树中插入数据的空隙，gap lock锁定一个范围，但是不包括数据本身。
目的是为了防止同一事务的两次当前读出现幻读的情况。
在RR和Serializable下都默认支持Gap锁
1. 主键或者唯一键
在RR下无论删改查当前读若用到主键或者唯一键会用到gap锁吗？
Ø 如果where条件全部命中，则不会用Gap锁，只会加记录锁
全部命中即精确查询，全部条件都在数据库中存在。
由于全部命中，不会出现幻读情况。就不需要加Gap锁。（因为新增的数据肯定在where范围之外，（where里面是唯一键或者主键））
由于id是唯一键。因此delete会走id这列的索引进行where条件的过滤。
在找到id=9的记录会将id=9的数据加上行锁(record lock),会根据读到的name作为主键索引，即密集索引，然后将name=d对应的主键索引项也加上record lock（排他锁）。
为什么将主键索引也加锁呢
如果此时有其他事务更新name=d的id为90，由于没有锁，该事务无法感知此次更新，违背了同一记录上的更新或者删除需要串行执行的约束。
创建tb表
其中ID为4 、7 和 8的数据不存在
此时两个Session的事务隔离级别均为RR
全部命中
Session1：
怎么证明是走唯一索引呢？用explain
如果有gap锁的话9周围的间隙将被锁起来
如果在别的事务中加入id=10的数据是会被Blocked住
在Session1未提交时
Session2：
执行成功，因为不加gap锁。
Ø 如果where条件部分命中或者全不命中，则会加Gap锁
全不命中
我们只需要删除一个不存在的记录即可（我们在Session1删除7，Session2插入8试试看）
在Session1：
删除不存在的值
Session2：
插入不存在的值
然后被blocked
部分命中
Session1：
Session1未提交时
Session2进行插入操作
插入4成功了，小于5的数据并未加锁
（插入5到9的数据会被锁住）
插入7的时候被blocked住了
插入8也被锁住
插入10就没被锁住
如果全命中
Session1
此时Session2插入7和8均成功执行（不加Gap锁）
Gap范围有那么多，但是只会对(6,9] (9,11]的gap上锁
Gap锁锁住左开右闭的区间
Gap锁是用来防止插入的
如图会对(6-9] (9-11]gap区间上锁
但是还要考虑主键的值才能最终判断。
非唯一索引和不走索引的当前读下
非唯一索引
创建表
和图中的案例是一致的，gap锁锁住(6,9] (9,11]
Session1：
Session2：
需要等待Session1提交或者回滚
此时插入5 7 12的情况：
5成功
7失败
12成功
临界情况
插入6和11
我们可以看到此时表里id为6的行的name的值为c
根据字母表顺序b<c<d
插入成功
插入主键为dd，id为6的值失败
由于主键bb按字母顺序不在6-11内因此没有被blocked
相反 dd在6-11内因此被blokced
不走索引
所有gap均被锁住
创建表
c
Session1:
Session2:
RC、RR级别下的InnoDB的非阻塞读如何实现
Ø 数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段
第一个是事务ID，第二个是rollback指针，第三个是行号（可以作为隐藏主键（如果没有主键和唯一键）），此外还有其他标志，比如说删除一行数据不是真的将其删除，而是将删除的标志位更改。
Ø Undo日志
存储老版数据
Ø Read view
分为Insert Undo log和update undo log
Insert Undo log事务对insert产生的undo log，只在事务回滚的时候需要，并且在事务提交的时候丢弃。
Update undo log，事务delete和update产生的undo log，不仅在回滚的时候需要，快照读的时候也需要所以不能随便删除，只有在快照中不涉及该记录该日志才会被xx线程删除。
Read view主要做可见性判断的
当我们执行快照读Select的时候，针对我们查询的数据，创建出一个read view来决定当前事务能看到的是哪个版本的数据。Read view 遵循一个可见性算法，将数据的DB_TRX_ID数据取出来与系统其它活跃事务id做对比，如果大于或者等于这些id的话就通过db_roll_ptr指针去取出undo log上一层的DB_TRX_ID直到小于这些活跃事务id为止。这样就保证我们获取数据的版本是最稳定的版本。（越新开启的事务，它的ID是越大的）
在RR级别下Session在start transaction之后的第一条快照读后创建一个快照即read view会将当前系统中活跃的事务记录起来，此后调用快照读的话还是调用同一个read view。
在RC级别下，事务中每条Select语句每次都会创建一次新的快照
读取事务时候的非阻塞就是MVCC（多版本并发控制）
InnoDB的非阻塞读机制是伪MVCC（读写不冲突），并没有实现多版本共存，undo log内容只是串行化的结果，记录多个事务过程不属于多版本共存。
关键语法
GROUP BY
满足Select字句中的列名必须为分组列或列函数（只对一张表成立）
列函数对于group by字句定义的每个组各返回一个结果
查询所有同学的学号，课程数，总成绩
Where一定要在group by前面
HAVING
Ø 通常与GROUP BY子句一起使用
Ø WHERE过滤行，HAVING过滤组
Ø 出现在同一sql的顺序：WHERE->GROUP BY->HAVING
理论范式
1NF
是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域(**原子项)**。
1NF是关系模式应具备的最起码的条件，如果数据库设计不能满足第一范式，就不能称为关系型数据库。关系数据库设计研究的关系规范化是在1NF之上进行的。
2NF
第二范式要满足的条件：首先要满足第一范式，其次每一个非主属性要完全函数依赖于候选键，或者是主键。也就是说，每个非主属性是由整个主键函数决定的，而不能有主键的一部分来决定。
3NF
如果关系模式R是2NF，且关系模式R（U,F）中的所有非主属性对任何候选关键字都不存在传递依赖，则称关系R是属于第三范式。
第三范式（3NF）；符合2NF，并且，消除传递依赖。
其它范式
第四范式：要求把同一表内的多对多关系删除。
第五范式：从最终结构重新建立原始结构。
BC范式（BCNF）：符合3NF，并且，主属性不依赖于主属性。若关系模式R属于第一范式，且每个属性都不传递依赖于键码，则R属于BC范式。
优缺点
规范化的优点是明显的，它避免了大量的数据冗余，节省了存储空间，保持了数据的一致性。当一个库里的数据经常发生变化时，达到3NF的库可以使用户不必在超过两个以上的地方更改同一个值。那么是不是只要把所有的表都规范为3NF后，数据库的设计就是最优的呢?这可不一定。范式越高意味着表的划分更细，一个数据库中需要的表也就越多，用户不得不将原本相关联的数据分摊到多个表中。当用户同时需要这些数据时只能采用连接表的形式将数据重新合并在一起。同时把多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能
数据库连接
