互联网数据库面试持续更新。。。。

知识整理持续更新

基础知识

简述数据库三大范式

第一范式：最基本的范式，列表字段不可分

第二范式：关系模式必须满足第一范式，有主键且非主键依赖主键

第三范式：关系模式满足第二范式 所有非主键字段不能相互依赖

存储引擎

innoDB 和 myisam 的区别

• innodb支持事务、外键，myisam不支持事务、外键。
• InnoDB支持行锁，myisam支持表锁，每次更新增加删除都会锁住表。
  InnoDB只缓存索引，不缓存真实数据，myisam支不仅缓存索引也缓存真实数据，对内存要求性高，而且内存大小对性能有决定性的影响。
• innoDB是聚集索引，myisam是非聚集索引，索引和数据是分开的，需要一个回表操作。

如何选择搜索引擎

MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

索引

什么是索引

索引是排好序的快速查找的数据结构，是为了解决SQL数据过于庞大引起效率下降的优化方法，可以帮助我们快速的进行数据的查找。

常见的索引的数据结构

https://www.jianshu.com/p/8b653423c586
漫画什么是B树：
漫画什么是B+树
https://zhuanlan.zhihu.com/p/54102723

索引的分类

索引类型主要包括：普通索引，唯一索引，主键索引和组合索引。
普通索引：之间创建简单的索引
唯一索引：与普通索引类似，不同的是Mysq的索引列值必须唯一，但是允许有空值
主键索引：是一种特殊的唯一索引，不允许有空值，一般是在创建表的时候直接指定
组合索引：多个字段上创建的索引

索引采取的是哪种数据额结构

常见的mysql主要有两种结构 Hash索引和B+Tree索引，我们使用的InnoDB引擎，默认的是B+树

为什么采用B+ 树吗？这和Hash索引比较起来有什么优缺点吗？

• 因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。（hash表仅仅支持“=”、“IN”，不支持范围查找。）
• B+Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。

https://article.itxueyuan.com/eoJEMj

什么是聚集索引

数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个个表中只能拥有一个聚集索引

聚集索引的特点：

• 叶子节点上包含着该行的所有信息。当您找到该叶子节点的时候，不需要再回表，直接可以取出该行数据的所有信息。
• 每个表只能有一个聚集索引。

非聚集索引
非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

非聚集索引的特点：

• 一张表的聚集索引个数可能有多个，最多可以创建249个非聚集索引。
• 先建聚集索引才能创建非聚集索引。
• 非聚集索引数据与索引不同序。
• 非聚集索引在叶节点上有一个“指针”直接指向要查询的数据区域

我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针

非聚集索引一定回表查询吗

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.

？

在建立索引的时候,都有哪些需要考虑的因素呢

建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合.如果需要建立联合索引的话,还需要考虑联合索引中的顺序.此外也要考虑其他方面,比如防止过多的所有对表造成太大的压力.这些都和实际的表结构以及查询方式有关.

创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

使用Explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句。分析你的查询语句或是表结构的性能瓶颈。

什么是覆盖索引和回表

覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。
而要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可。

事务

什么是事务

事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；
这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；
事务是一组不可再分割的操作集合（工作逻辑单元）；

事务的特性

• A=Atomicity原子性 要么全部成功 要么全部失败 不可能只执行一部分操作
• C=Consistency 一致性
  从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。
• -I=Isolation 隔离性

一个事务的修改在最终提交前，对其他事务是不可见的。

• D=Durability
  一旦事务提交，所做的修改就会永久保存到数据库中

ACID靠什么保证的呢？

• A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
• C一致性一般由代码层面来保证
• I隔离性由MVCC来保证
• D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redolog刷盘，宕机的时候可以从redo log恢复

同时有多个事务在进行会怎么样呢?

• 脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据（更新）
• 不可重复读：一个事务多次读取，结果不一样（打了500没有提交 再一次看50）
• 幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是其他事务“插入”的数据（插入、删除）

怎么解决这些问题呢?MySQL的事务隔离级别了解吗?

• read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。
• read commit 读已提交，两次读取结果不一致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务。
  用户开启事务读取id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。
• repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读。
• serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。
  

Innodb使用的是哪种隔离级别呢?

InnoDB默认使用的是可重复读隔离级别.

MVCC和间隙锁可以解决幻读的问题

MVCC实现原理

https://www.jianshu.com/p/8845ddca3b23
https://www.bilibili.com/read/cv7519001

幻读

锁的类型有哪些呢

mysql锁分为共享锁和排他锁，也叫做读锁和写锁。
共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个。（多个事务加，都能访问数据，只能读不能改）
排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥（只能一个事务加）. 可以分为表锁和行锁

表锁和行锁：行锁 锁的是一行，表锁 锁的是一个表。 行锁的并发效率比表锁的高，但是锁定粒度，加锁效率、冲突概率都比表锁小

行锁又可以分为乐观锁和悲观锁
悲观锁：对数据的修改持有悲观态度的并发控制方式，总是假设最坏的情况，每次读取数据的时候都默认其他线程会更改数据，因此需要进行加锁操作，当其他线程想要访问数据时，都需要阻塞挂起。（适合写多读少）

乐观锁：乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。乐观锁适用于读操作多的场景，这样可以提高程序的吞吐量。

对死锁和解决死锁的理解

• 死锁的第一种情况

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
解决办法：

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

• 死锁的第二种情况
  用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项 目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操 作，很容易就出现这种死锁的情况。
  

• 死锁的第三种情况
  如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

bin log redo log和undo log

分库分表

https://www.cnblogs.com/butterfly100/p/9034281.html

其他

MySQL中的varchar和char有什么区别.

• char是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用10个字符,
  varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间
• 在检索效率上来讲,char >varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.

varchar(10)和int(10)代表什么含义?

• varchar的10代表了申请的空间长度,也是可以存储的数据的最大
• 而int的10只是代表了展示的长度,不足10位以0填充.也就是说,
• int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.

参考博客https://zhuanlan.zhihu.com/p/40211594
https://www.bilibili.com/read/cv7519001