数据库相关

一. 索引

1. 什么是索引

索引是存储引擎用于快速找到记录的一种数据结构。
B+树叶子节点指针指向被索引的数据

1. Cardinality

1. Cardinality：表示索引中不重复记录数量的预估值，在实际应用中，Cardinality应该接近于表中的行数。
2. InnoDB对Cardinality的更新策略是：
   • 表中的1/16的数据已经发生变化
   • 表中数据发生了20亿次变化
3. Cardinality的计算
   • 是用采样的方法计算的。随机抽取8个叶子节点，计算每个页中不同记录的数目得到8个数，对这8个数取平均再乘上所有的叶子数。

2. 索引使用的基本原则

• 最经常作为查询条件的列上建立索引能够提高查询效率
• 经常更新的列不宜建立索引
• 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
  https://tech.meituan.com/2014/06/30/mysql-index.html 建索引的几大原则

3. 索引的优点和缺点

优点：

1. 减少了服务器需要扫描的数据行数
2. 帮助服务器避免排序和分组，避免创建临时表。
3. 能够将相关的数据保存在一起，减少IO次数
   缺点：
4. 创建索引需要时间和空间
5. 修改数据的时候还要顺带修改索引，减慢速度

2. 索引的分类

从数据结构的角度分类

B+树索引

1. B+树索引的一个特点就是高扇出性，树高为2-4层。
2. B+树索引分为聚集索引(也称为主索引)和辅助索引(也称为非辅助索引)。
3. B+树节点的大小刚好等于操作系统一页的大小(Linux默认是4K)
4. B+树的叶子节点是用链表连在一起的。
5. 由于B+树的有序性，除了用于查找，还可以用于排序和分组
6. B+树索引不能找到一个给定键值对应的行，只能找到给定键值对应的行所在的页，然后数据库把页读进内存，再在内存中查找。

为什么说B+树比B树更适合数据库索引？

1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向具体数据的指针，但是B树有，因此B+树节点能容纳更多的键值，能查找更大的范围，树高和查询的次数降低，相对IO读写次数就降低了。

2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3、由于B+树的数据都存储在叶子结点中，叶子使用指针相连，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash索引

哈希索引基于哈希表实现。对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，不同的索引列计算出来的哈希码是不同的。哈希索引把哈希码存储在哈希表的索引列中，哈希表中另一列保存着指向每个数据行的指针。

1. InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表上生成哈希索引。
2. 缺点
   无法用于排序与分组；
   只支持精确查找，无法用于部分查找和范围查找

从物理存储的角度

聚集索引

1. 聚集索引的定义
   • 该索引中键值的逻辑顺序跟表中对应数据行的物理存储顺序相同。索引相邻，对应的数据也在磁盘上相邻存储。InnoDB通常根据主键建立索引，如果没有定义索引列，则在唯一非空索引列或者自动建立隐藏主键列完成聚集。
   • 在B+树中，聚集索引表现为叶子节点存储的是对应的数据行。
2. 聚集索引的优点：
   * 可以把相关的数据保存在一起，例如实现电子邮箱的时候，可以根据用户的ID来聚集数据，这样只需要从磁盘中读取少数的数据页就能获取某个用户的全部邮件，如果没有使用聚簇索引，每一封邮件都可能导致一次磁盘IO
   *. 数据访问很快。聚簇索引将索引和数据保存在同一棵树中，从聚簇索引获取数据通常比在非聚簇索引中查找要快。
3. 聚集索引的缺点
   1. 更新聚簇索引的代价很高。因为会强制InnoDB将每个被更新的行移动到新的位置。
   2. 在插入新行的时候，可能面临“页分裂”的问题。当一个行插入某一个已满的页中时，存储引擎会将该页分裂成两个页面，这样会导致表占用更多的磁盘空间。

非聚簇索引(辅助索引)

1. 非聚簇索引的索引逻辑顺序不决定数据行的物理顺序，并且其B+树的叶子节点存的不是数据行，而是是数据行的主键，每张表可以有多个辅助索引。
2. 如果用辅助索引查找数据，会首先根据索引值找到主键，然后根据主键在聚集索引中查找对应的完整行数据。这就是回表。

索引的其他分类

1.倒排索引

https://www.cnblogs.com/zlslch/p/6440114.htm
在搜索引擎中有一种叫正向索引的结构：
“文档1”的ID > 单词1：出现次数，出现位置列表；单词2：出现次数，出现位置列表；
给定一个文档的ID，能得到文档中单词出现的次数和位置。
倒排索引则是反过来，将文件ID对应到关键词的映射转换为关键词到文件ID的映射。

2. 联合索引

使用多个列作为条件查询的时候，使用联合索引的性能比多个单列索引要好。对表上的多个列进行索引。联合索引也对键值进行了排序。

3.前缀索引

对于BLOB，text和varchar这样的字符列，应该使用前缀索引，只索引开始的部分字符，这样可以大大节约索引空间。
前缀长度的选取需要根据索引选择性来确定。
缺点： 无法用前缀索引做order by和group by,也无法用前缀索引做覆盖索引。

4.覆盖索引

如果一个索引包含了所有需要查询字段的值，就叫做覆盖索引。
优点：

1. 只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO操作，提高了效率

3.索引使用的条件

1. 小型的表没必要，全表扫描效率更高
2. 中到大型的表，索引非常有效
3. 特大型的表，建立和维护索引的代价很高，需要其他的技术，如分区技术。

4. 索引失效的情况

假如在(name,age,pos)这3列上建立了索引，

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

1. 必须满足左前缀匹配法则。从索引的左边的列开始，不跳过索引中的列，被跳过的列后面不能使用索引。如果中间的列使用了范围查询，这个列后面的列也不能使用索引。
2. 索引列不能参与计算，保持列“干净”，对索引列进行运算导致索引失效,此处对索引列进行运算包括(+，-，*，/，! 等)
   错误的例子：select * from test where id-1=9;
   正确的例子：select * from test where id=10;
3. 使用不等于时，无论是哪一列使用了不等于，整个索引都失效

select * from staffs where name = "july" and age != 25  //name也不能使用索引

5. 使用is null 或者is not null失效。

select * from staffs where name1 = 'july' and age is not null
//这时候第一列的索引时可以用的，带有not null的列和这个列后面的索引是失效的

7. 查询条件like以通配符开头会导致全表扫描，不使用索引。
   现在重新建了一个表，新建的索引是(name,pos,age)

select * from staffs where name1 ="xiaoming" and pos like "%dev" and age = 23
//只能使用name1上的索引，pos和age的索引都是无效的

9. 使用or的时候，整个索引会失效。

select * from staff where name="liang" or age=23 //不走索引。

二. 查询性能优化

1. 慢查询的优化流程

MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值是10，意思是运行10s以上的语句。默认情况下，MySQL数据库并不启动该参数，因为开启慢查询日志会带来一定的性能影响。慢查询日志支持将文件记录写入文件，也支持将日志写入数据库表。
https://blog.51cto.com/ljianbing/1616932

1. mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总，找到需要优化的SQL语句。
2. 使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈
3. 如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。

2. 使用 Explain 进行分析

1. 作用

Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句

2. 语法

例如：explain select * from tableName;
简单的说就是explain关键字后面跟你要检查的SQL语句

3. 比较重要的字段

select_type : 查询类型，有简单查询、联合查询、子查询等
key : 使用的索引
rows : 扫描的行数

3. 优化数据访问

1. 减少请求的数据量

1. 只返回必要的列，避免使用select * 语句
2. 只返回必要的行，使用limit语句来限制返回的数据
3. 缓存重复查询的数据，这样可以避免在数据库中进行查询。

2. 重构查询的方式

2.1 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。所以我们需要将大查询切分成小查询，每个查询的功能完全一样，每次返回一小部分的查询结果，将一次性的压力分散到一个很长的时间段中。

2.2 分解大的连接查询

这个方法的意思是把单个多表连接查询改成多个单表查询，然后在程序中合并数据
如 ： select a.,b. from A a join B b on a.id = b.id
可以替换为:
select a.* from A;
select b.* from B;
然后通过程序把数据合并

分解大的连接查询优点

1. 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
2. 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
3. 减少锁竞争；查询会锁住单个表较短时间，而不是把所有的表长时间锁着
4. 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
   查询本身效率也可能会有所提升。

三. 存储引擎

InnoDB

1. MySQL默认的引擎，是事务型的引擎，当需要有它不支持的特性时，才考虑用其他的存储引擎。
2. 支持真正的在线热备份，其他存储引擎不支持在线热备份。
3. 有四个标准的隔离级别，分别是

隔离级别	Read Uncommitted	Read Committed	Repeatable Read	Serializable
未解决的问题	脏读	不可重复读	幻读	解决所有问题

其中InnoDB的默认级别是可重复读。
读脏数据和不可重复读的例子

读脏数据解决：对脏数据设置为加锁访问，如果访问结束后释放锁的话，会导致不可重复读。
不可重复读解决：对数据设置为加锁访问，事务结束后释放锁。
幻读解决：在两行记录的空隙加上锁，阻止新纪录的插入；这个锁称为间隙锁。

MyISAM

1. 对于只读数据，或者表比较小，可以容忍修复操作，可以使用它。
2. 提供了大量的特性，包括用前缀压缩技术让索引更小，通过数据的物理位置索引被索引的行，InnoDB根据主键索引被索引的行。
3. 不支持行锁，只有表锁。但是表在读取的时候可以往表中插入新的记录，叫做并发插入。
4. 可以自动执行检查和修复的工作，但是修复操作很慢，可能会导致数据的丢失。
5. 写锁也比读锁有更高的优先级， 因此一个写锁请求可以会被插入到读锁队列的前面。Myisam 的读写锁调度是写优先， 这也是 myisam 不适合做写为主表的引擎。
6. 用户 A 给表 A 加了读锁之后， 只能读表 A， 不能写表 A（报错） ， 也不能读写其他表（报错） 。
   此时用户 B 可以读表 A， 可以读写其他表， 但是写表 A 时会出现阻塞（未报错） ， 直至用户
   A 释放表 A 的锁之后才解除阻塞， 执行命令

比较

• 事务：InnoDB是事务型的，可以使用Commit和Rollback语句
• 锁的级别：InnoDB支持行级锁，MyISAM只支持表级锁
• 外键：InnoDB支持外键，MyISAM不支持
• 备份：InnoDB支持热备份，MyISAM不支持
• 崩溃回复：MyISAM崩溃后发生损坏的概率比InnoDB要大，恢复的速度慢
• 其他特性：MyISAM支持前缀压缩和根据数据的物理位置索引被索引的列。

四. 切分

http://blog.itpub.net/15498/viewspace-2135342/
https://zhuanlan.zhihu.com/p/50561846

1. 什么是分库分表

分库分表有垂直切分和水平切分两种。

1.1 垂直切分

1.1.1 垂直分表

就是“大表拆分成小表”，基于列字段进行的。一般是表中的字段太多，将不常用的，数据量较大的拆分到“扩展表”。这样可以避免查询时，数据量较大造成的跨页问题。

1.1.2 垂直分库

数据库有很多表，每一张表都对应着不同的业务，根据业务的不同，把每一张表拆分到相应的数据库上。比如用户表User放到User库中，商品表放到Product库中。而且拆分之后，每一个库需要放到不同的服务器上。
这是因为

1. 没拆分数据库之前，请求都是落在一个库上，使得单个数据库的处理能力成为瓶颈。
2. 没把不同的数据库放到不同服务器上时，请求都是落在一个服务器上的，使得单个服务器的处理能力成为瓶颈。

1.2 水平切分

1.2.1 水平分表

对于数据量巨大的单张表，按照某种规则(HASH取模)，切分到多张表里面去，但是这些表还是在同一个库中，单个数据库的处理能力是瓶颈。不建议使用。

1.2.2 水平分库分表

将表水平切分后，放到不同的数据库上，不同的数据库放在不同的服务器上，这样能够缓解单库和单机的性能瓶颈。

1.2.2.1 水平分库分表的规则

1. RANGE
   从0到9999一个表，10000到19999一个表
2. HASH
   取表的主键，然后hash取模，分配到不同的数据库上
3. 地理区域
   按照华东，华南，华北这样来划分区域。
4. 时间
   把半年前或者一年前的数据切分到另一张表，因为一年前的数据访问量肯定比现在的要低，做到冷热数据分离。

2. 分库分表后的问题

1. 事务支持。

在执行分库分表之后，事务就变成分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

2. 跨库跨表的join问题。

表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
https://www.jianshu.com/p/e20169951da1
案例：日志数据库有pay_log，里面有一个userid。用户的详细信息放主库main_db中。
粗略的解决办法有：
1. 字段冗余。在pay_log表里面放userid，还要放一个user_name字段。这种方法只能放少量的字段。
2. 表的复制。把join操作中，那一个较小的表格复制到较大的表所在的数据库中，然后就能在同一个库进行join操作了。但是这样比较浪费空间。
3. 链接表。什么是链接表呢？简单来说，就是在log_db里有一个user_info表，但这个表并没有存储数据，而是直接链接到了 main_db里的user_info表。这样的话，我们可以既无需定期同步，又可以像在同一个库里使用JOIN等操作

3. 额外的数据管理负担和数据运算压力。

例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表之前，只需一个order by语句就可以搞定，但是在进行分表之后，将需要n个order by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。

五. 复制

1. 主从复制

1. 主服务器把数据更改记录记录到二进制日志(binlog)中
2. 从服务器把主服务器的二进制日志复制到自己的中继日志中。
3. 从服务器重做中继日志的日志，把更改应用到自己的数据库上，达到数据的最终一致性。
   

2. 读写分离

从图中可以看到，SQL语句并不直接进入到master数据库或者slave数据库，而是进入到
proxy，然后proxy判断这条语句是有关写的语句（包括insert、update、delete）还
是读语句（select），当是写语句的时候，那么proxy将向master所在的服务器发出请
求，同理，如果是读语句的时候，proxy将向slave所在的服务器发出请求。
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。
主从服务器之间采用主从复制的方法来同步数据

六. mysql的sql语句执行流程

参考链接 http://blog.itpub.net/30126024/viewspace-2222570/

这个更好https://blog.youkuaiyun.com/weter_drop/article/details/93386581
1、client和server建立连接，client发送sql至server（对应连接器这一过程）

2、server如果在查询缓存中发现了该sql，则直接使用查询缓存的结果返回给client，如果查询缓存中没有，则进入下面的步骤（对应查询缓存这一过程，8.0这一过程已经不存在了，8.0完全抛弃了这个功能）

3、server对sql进行语法分析，识别出sql里面的字符串是否符合标准，比如select关键字不能写错（对应分析器这一过程）

4、server对sql进行语义分析，识别出sql里面的字符串的具体意思是什么，比如T1是表名，C1是列名（对应分析器这一过程。3、4步其实解析的过程，这个解析的过程是分析器的工作不是优化器的工作）

5、server确定sql的执行路径，比如走索引还是全表，多表连接时哪张表先走哪张表后走，当你的where条件的可读性和效率有冲突时mysql内部还会自动进行优化，也就是大家理解的重写where子句（对应优化器这一过程）

6、server对执行sql的用户进行权限检查，比如对表是否有权限执行(对应执行器这一过程)

7、server执行该sql语句，发送结果给client(对应执行器这一过程)

连接器–>查询缓存–>分析器–>优化器–>执行器

如果表 T1 中没有字段 C1，而执行select * from T1 where C1=1会报错不存在C1这个列，这个过程对应上面第4个过程，对应分析器这一过程

如果用户对T1表没有权限，而执行select * from T1 where C1=1会报错对表T1没有权限，这个过程对应上面第6个过程，对应执行器这一过程

七.事务

1. 事务相关

1. 什么是事务
   事务： 是用户定义的一个数据库操作序列， 这些操作要么全做， 要么全不做， 是一个不可分割的工作单位。

2. 事务的四个特性

   1. 原子性（atomicity）： 一个事务是一个不可分割的工作单位， 事务中包括的诸操作要么都做， 要么都不做；
   2. 一致性（consistency）： 事务必须使数据库从一个一致性状态变成另一个一致性状态；比如A有500块，B有400块，两者相互转钱，两者相加的钱还是900.与现实保持一致。
   3. 隔离性（isolation）： 一个事务的执行不能被其他事务干扰；系统保证，任何一对事务A和B，在A看来，B要么在A执行之前已经执行完成，要么在A执行完后才开始执行。
   4. 持续性（durability）： 也称永久性， 指一个事务一旦提交， 它对数据库中数据的改变就应该是永久性的。

1.1 事务的隔离级别和实现

读未提交	读提交	可重复读	可序列化
无锁	MVCC	MVCC，MVCC的最大作用是实现了非阻塞的读操作，写操作只是锁定了必要的行	读加共享锁，写加排他锁，读写互斥，使用悲观锁的理论

脏读：是读取了另一个事务未提交的修改
不可重复读：是读取了另一个事务提交之后的修改
幻读：两次读取得到的结果集不一样

1.2 MVCC

1. 为什么要使用MVCC
   数据库使用加锁的方式来实现事务的隔离性，但是这样的话，读取数据的时候没法修改，修改数据的时候没办法读取，极大地降低了数据库的性能。
   MVCC可以在读取数据的时候进行修改，修改数据的同时可以读取。

2. InnoDB使用MVCC来实现可重复读，避免脏读，但是没有解决幻读的问题。InnoDB的默认隔离级别就是可重复读。

1.2.1 MVCC的实现原理

1. 参考的博客是https://juejin.im/post/6886056051590823949#heading-28
   https://zhuanlan.zhihu.com/p/52977862
   MVCC是通过undo log和read view来实现的

首先表格有隐藏列 DB_TRX_ID和 DB_ROLL_PTR。对于每一行数据，DB_TRX_ID用来记录修改该事务的事务id，DB_ROLL_PTR用来记录这条数据的上一个版本在undo log的位置。
写事务修改这一条数据的时候，先把这条数据复制到undo log里面，然后修改这条数据，并把这条数据的DB_TRX_ID设置为写事务的事务ID，然后将DB_ROLL_PTR指向undo log的位置。
每一个SQL语句在执行的时候都会得到一个read view，read vie有着4个属性。
trx_ids: 当前系统活跃(未提交)事务版本号集合。
low_limit_id: 创建当前read view 时“当前系统最大事务版本号+1”。
up_limit_id: 创建当前read view 时“系统正处于活跃事务最小版本号”
creator_trx_id: 创建当前read view的事务版本号；

Read view 匹配条件
（1）数据事务ID <up_limit_id 则显示
如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务启之前就已经存在了的,所以可以显示。

（2）数据事务ID>=low_limit_id 则不显示
如果数据事务ID大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不予显示。

（3） up_limit_id <数据事务ID<low_limit_id 则与活跃事务集合trx_ids里匹配
如果数据的事务ID大于最小的活跃事务ID,同时又小于等于系统最大的事务ID，这种情况就说明这个数据有可能是在当前事务开始的时候还没有提交的。

所以这时候我们需要把数据的事务ID与当前read view 中的活跃事务集合trx_ids 匹配:

1. 情况1: 如果事务ID不存在于trx_ids 集合（则说明read view产生的时候事务已经commit了），这种情况数据则可以显示。

2. 情况2： 如果事务ID存在trx_ids则说明read view产生的时候数据还没有提交，但是如果数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。

3. 情况3： 如果事务ID既存在trx_ids而且又不等于creator_trx_id那就说明read view产生的时候数据还没有提交，又不是自己生成的，所以这种情况下此数据不能显示。

（4）不满足read view条件时候，从undo log里面获取数据
当数据的事务ID不满足read view条件时候，从undo log里面获取数据的历史版本，然后数据历史版本事务号回头再来和read view 条件匹配 ，直到找到一条满足条件的历史数据，或者找不到则返回空结果；

1.3 锁相关

https://www.aneasystone.com/archives/2017/10/solving-dead-locks-one.html

1.3.1 行锁

记录锁

• 记录锁锁住的是索引，并非是行记录。如果没有定义索引，这时候锁住的就是聚集索引。如果where条件中指定的列是一个二级索引，那么记录锁不仅会加载二级索引上，还会加在二级索引对应的聚簇索引上。
• 当一条sql语句执行的时候没有使用任何索引，那么就走主索引实现全表扫描，对这个表上的主索引索引的每条都加一个记录锁。如果记录无法通过主索引快速筛选，那么就返回给Server层，让Server层来过滤。在实际使用过程中，发现不满足条件的行记录时，会把这些行记录的锁释放掉，满足条件的行记录不释放。

间隙锁

间隙锁和间隙锁之间是互不冲突的。

间隙锁的问题，在可能会锁住更大的范围。比如我执行这条语句

UPDATE accounts SET level = 100 WHERE id = 5;

可能会锁住这两个范围(a,5)和(5,b)，假设a=1,b=10，那么这时候插入一条id=3的数据就会阻塞，尽管这时候(用这条语句，插入id=3的数据)并不会导致幻读。

插入意向锁

这个锁表示插入的意向，只有insert的时候才会有这个锁。插入意向锁和插入意向锁之间是不冲突的。假如区间(1,5)没有间隙锁，事务1和事务2都能在这个区间加上插入意向锁，然后分别插入2和3。但是当区间(1,5)已经被间隙锁锁住，那么事务1和事务2获得插入意向锁的时候就会阻塞。

Next-Key Lock的作用

https://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html
Next-Key Lock，锁一条记录及其之前的间隙，这是 RR 隔离级别用的最多的锁。默认隔离级别REPEATABLE-READ下，InnoDB中默认隔离级别是RR，所以行锁默认使用算法Next-Key Lock，只有当查询走的索引是唯一索引时，InnoDB会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。
当查询走的索引为非唯一索引(只有辅助索引才有可能是非唯一的)时，InnoDB则会使用Next-Key Lock进行加锁。还会将该键值后面的间隙加上Gap LOCK。
当查询没有走索引时，只能走聚簇索引，对表中的记录进行扫描。需要给所有的聚簇索引的数据加上行锁，聚簇索引的之间加上间隙锁。

1.4 死锁

1.4.1 死锁案例一
死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的，这个死锁案例其实是最经典的死锁场景。
首先，事务 A 获取 id = 20 的锁（lock_mode X locks rec but not gap），事务 B 获取 id = 30 的锁；然后，事务 A 试图获取 id = 30 的锁，而该锁已经被事务 B 持有，所以事务 A 等待事务 B 释放该锁，然后事务 B 又试图获取 id = 20 的锁，这个锁被事务 A 占有，于是两个事务之间相互等待，导致死锁。
1.4.2 死锁案例二

首先事务 A 和事务 B 执行了两条 UPDATE 语句，但是由于 id = 25 和 id = 26 记录都不存在，事务 A 和 事务 B 并没有更新任何记录，但是由于数据库隔离级别为 RR，所以会在 (20, 30) 之间加上间隙锁（lock_mode X locks gap before rec）(唯一索引如果没有命中，那么就加间隙锁)，间隙锁和间隙锁并不冲突。之后事务 A 和事务 B 分别执行 INSERT 语句要插入记录 id = 25 和 id = 26，需要在 (20, 30) 之间加插入意向锁（lock_mode X locks gap before rec insert intention），插入意向锁和间隙锁冲突，所以两个事务互相等待，最后形成死锁。

1.5 意向锁

1.5.1 引入原因

对某个对象加锁，首先得看该节点是否加了互斥的锁，然后检查上级节点有没有加互斥锁，然后检查下级节点是否加了互斥锁。这样检查效率低，所以引入了意向锁。

1.5.2 定义

如果对一个节点加意向锁，说明下层节点正在加锁。对任意一个节点加锁的时候，必须对它的上层节点加意向锁。

锁	意向共享锁（IS锁）	意向排他锁（IX锁）	共享意向排他锁（SIX锁）
定义	如果对一个数据对象加 IS 锁， 表示它的子节点拟加 S 锁	如果对一个数据对象加 IX 锁， 表示它的子节点拟加 X 锁。	如果对一个数据对象加 SIX 锁， 表示对它加 S 锁， 再加 IX 锁， 即 SIX = S + IX
例子	事务 T1 要对 R1 中某个元组加 S 锁， 则要首先对关系 R1 和数据库加 IS 锁	事务 T1 要对 R1 中某个元组加 X 锁， 则要首先对关系 R1 和数据库加 IX 锁	对某个表加 SIX 锁， 则表示该事务要读整个表（所以要对该表加 S 锁），同时会更新个别元组（所以要对该表加 IX 锁）

数据库恢复技术

1. 数据转储

将整个数据库复制到其他存储介质保存起来的过程。

2.日志文件

1.事务故障的恢复

事务故障：事务运行到正常终止点前被终止
解决办法：反向扫描日志文件，查找该事务的更新操作，对这些更新操作做逆操作。直到读到事务的开始标志。

2. 系统故障的恢复

1. 在日志文件中找到最后一个检查点的位置
2. 得到该检查点建立时刻所有正在执行的事务清单 Active-list。并且建立两个事务队列，Undo-List和Redo-List. 把Actvie-List的内容放到Undo-List中。
3. 正向扫描日志文件，直到日志文件结束
   • 如果有新开始的事务，那把这个事务放到Undo-List中
   • 如果有提交的事务，把这个事务从Undo-list中放到Redo-List中。
4. 对Undo-List中的事务执行Undo操作。
   对Redo-List中的事务进行Redo操作。

2. MySQL的持久化

https://www.imooc.com/article/299436
https://www.jianshu.com/p/4bcfffb27ed5
https://segmentfault.com/a/1190000023897572

1. redo log
   MySQL的持久化并不是直接持久化到磁盘的，而是使用了redo日志作为中间层，redo日志是InnoDB特有的功能。在一条语句执行的时候，InnoDB会把更新记录写到redo log日志中，然后更新内存里面的数据页。在空闲的时候或者按照更新策略将redo log中的内容刷新到磁盘中。这样当内存中的数据还没来得及持久化到磁盘就发生宕机，我们也可以通过磁盘上的redo log完成数据的恢复，避免数据的丢失。

2. bin log
   数据库的bin log记录了数据库系统的所有更新操作。在主从复制的时候，可以用bin log把主库的更新操作传递到从库中。而且数据库还可以用bin log进行数据的恢复。

3. 两阶段提交保证redo log和bin log的一致性
   执行更新语句的时候，redo log记录事务的更新操作，然后将更新后的数据行都设置为prepare，这是prepare阶段。然后将bin log写入内存中，再把bin log持久化到硬盘中，接着提交事务，然后将redo log里面这个事务相关的记录置为commit状态，这就是commit阶段。
   以第二阶段的bin log是否写入作为成功提交的标志。如果在bin log记录之前/记录过程中发生宕机，那么回滚操作。如果在bin log记录完成之后宕机，即使redo log还没有commit，也认为是成功提交。在这种情况，机器重启后，会把redo log中那些认为成功提交的事务的记录设置为commit状态。

4. 为什么要引入redo日志？直接持久化到硬盘不行吗？
   如果每一次的更新操作都需要写入磁盘，然后磁盘也要找到对应的记录，然后再更新，整个IO过程的成本和查找成本都很高。所以引入了redo日志作为缓存，先把redo log的内容写到磁盘，等系统空闲的时候再把修改过的内存页刷新到磁盘。redo日志占用的空间很小，并且redo日志是顺序写入磁盘的，所以写redo日志的成本很低。

范式

1. 非平凡的函数依赖
   X->Y, 但是Y∉X，称X->Y为非平凡的函数依赖

1NF 列不可分

2NF 消除了非主属性对候选码的部分函数依赖

3NF消除了非主属性对候选码的传递函数依赖

BCNF 消除了主属性对候选码的部分函数依赖和传递函数依赖

2. 避免数据冗余和操作异常
   假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：

   (学号, 课程名称) → (姓名, 年龄, 成绩, 学分)

   这个数据库表不满足第二范式，因为存在如下决定关系：

   (课程名称) → (学分)

   (学号) → (姓名, 年龄)

即存在组合关键字中的字段决定非关键字的情况。

由于不符合2NF，这个选课关系表会存在如下问题：

(1) 数据冗余：

同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。

(2) 更新异常：

若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。

(3) 插入异常：

假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。

反范式(减少链接，提高查询效率)

88	范式	反范式
目的	解决存储问题	解决性能问题和使用问题
动作	拆表	合表
效果	存储优化	性能优化

MySQL的使用

视图

视图是虚拟的表，并不是预先计算并存储，在使用的时候才通过执行查询并计算出来。
视图本身不包含数据，它返回的数据是从其他表检索出来的。

• 可以利用其他视图来构造一个视图，也就是说可以嵌套。
• 视图不能索引，也不能有触发器和默认值
• Order by可以在视图中使用
• 创建视图的语句。其中faculty是视图的名字。

create view faculty as 
select ID, name, dept_name
from instructor