本文共 10337 字，大约阅读时间需要 34 分钟。

2020年秋招期间自己归纳关于数据库的考点。通过秋招面试情况来看，大部分问题确实有被问到，对自己也有非常大的帮助。不建议直接看我写的回答，而是带着问题去总结自己的回答。相信坚持就会有好结果。

31个问题，共计九千多字。

1、 数据库的三大设计范式

数据库范式就是一张数据表的表结构所符合的某种设计标准的集合。 第一范式：列不可分，符合1NF的关系中的每个属性都不可再分。 第二范式：若某关系R属于第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则关系R属于第二范式。理解： 第二范式是指每个表必须有一个（有且仅有一个）数据项作为关键字或主键（primary key），其他数据项与关键字或者主键一一对应，即其他数据项完全依赖于关键字或主键。 第三范式：非主属性既不传递依赖于码，也不部分依赖于码。理解： 第三范式要求在满足第二范式的基础上，任何非主属性不依赖于其他非主属性，即在第二范式的基础上，消除了传递依赖。 https://4m.cn/9DgrX

2、 什么是数据库索引，有什么作用

是对表中一列或多列的值进行排序的一种数据结构，是为了加快查询速度而对表字段附加的一种标识。对表进行搜索默认的方式是全表扫描，如果为该字段添加索引，就可以直接定位到特定的行数，大大减少匹配行数，增加速度。

3、 数据库索引的底层数据结构实现是啥? B树和B+树的区别

Mysql数据库（InnoDB引擎）的索引是由B+树实现的 B树即N阶B树，指一个节点上最多有N个子树，它在平衡二叉树的基础上又极大的减小了树的深度，因此在进行搜索的时候会有更快的速度。缺点在于范围查询时，需要多次的回溯，当数据量较大时，极大的限制了查询速度。B+树将数据只存放在叶子节点上，非叶子节点用作索引。叶子节点用连续的双向链表连接，因此在不牺牲纵向查找速度的情况下，又能在横向叶子节点上快速确定查找范围。兼顾了查询的速度和范围查找的速度，所以许多数据库底层使用了B+树来实现。

4、 B+树相对于B树的优点

B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了； B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询路径长度相同，导致每一个数据的查询效率相当； 数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

5、B树和B+树在节点存储结构上的差异

B树中每个节点存储的除了关键码的范围值，还包含了范围内多个关键码的有序表和指向含有这些关键码记录的指针（顺序表的大小一般为一个页面大小，方便从磁盘到内存的调页过程中只要一次I/O操作就可以完成）。当查找到该节点时，会先在顺序表中查找，若找到，则查找成功；否则，到按照对应的指针信息指向的子树中去查找，当到达叶子结点时，则说明树中没有对应的关键码。 B+树非叶节点只保存关键码的范围值，所有的关键码列表都存放在叶子节点中。因此，B+树中对数据的查找过程不管成果与否都会走完一条从根节点到叶子节点的完整路径。搜寻的效率是稳定的。

5、 数据库索引的优缺点

优点：通过创建唯一性索引可以保证数据库每一行数据的唯一性；可以大大加快数据查询分组和排序的时间；可以加快表之间的连接； 缺点：索引表的维护需要消耗时间和物理内存，且表越大，这种消耗越明显；此外对表进行增删改时，索引需要进行动态的维护，降低了数据的维护速度。

6、 什么样的表应该建立索引

数据量大，查询操作频繁。遵循的原则如下： 频繁作为查询条件的字段应该创建索引(where 后面的语句)； 查询中与其它表关联的字段，外键关系建立索引； 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度； 查询中统计或者分组字段；

7、 哪些情况下不用建索引

表加粗样式记录太少； 经常增删改的表； Where条件里用不到的字段不创建索引； 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。 特殊的格式例如文本或者图像不建立索引。

8、 创建索引时需要注意什么问题

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值； 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高； 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

9、 数据库索引失效的场景

列类型是字符串，查询条件未加引号。（例如：身份证号） 未使用该列作为查询条件 使用like时通配符在前 查询条件中使用OR 索引列进行函数运算 mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描 is not null 也无法使用索引,但是is null是可以使用索引的 组合索引ABC问题，Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部分，但只能是最左侧部分。最左匹配原则。

10、 索引的分类

普通索引：仅加速查询 唯一索引：加速查询 + 列值唯一（可以有null） 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并 全文索引：对文本的内容进行分词，进行搜索。只能用于MYISAM类型的数据表；只能用于CHAR ,VARCHAR,TEXT数据列类型。 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。所以说覆盖索引属于组合索引。所以覆盖索引不属于基本索引类型。

11、 什么是聚簇索引（聚集索引）和非聚簇索引

InnoDB中，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。 Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。 我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据； 在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

12、 聚簇索引的优缺点

优点：数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快； 聚簇索引对于主键的排序查找和范围查找速度非常快。 缺点：插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键； 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新； 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

13、 为什么用自增列作为主键

如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、 如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引； 如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引 数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点） 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

14、 什么是数据库事务

定义：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。可以简化错误恢复并使应用程序更加可靠。一个执行单元要想成为事务，就必须有以下四个特性：ACID（原子性，一致性，隔离性和持久性）。 原子性：一个事务是最小的执行单元不可再分； 一致性：事务的执行会使数据从一个一致状态切换到另一个一致状态； 隔离性：一个事务的执行不会受其他事务的干扰； 持久性： 一个事务一旦提交就会永久的改变数据库的数据；

15、 事务的并发会产生那些问题

脏读：一个事务读取了其他事物还未提交的数据，读到的是其他“更新”的数据； 不可重复读：在一个事务中多次读取，数据不一样； 幻读：一个事务读取了其他事务还未提交的数据，读到的是其他事务插入的数据；

16、 数据库四种事务的隔离级别

read uncommitted：读未提交，它允许一个事务读取另一个事务还没commit的数据，这样可能会提高性能，但是会导致脏读问题； read committed：读已提交，在一个事务中只允许对其它事务已经commit的记录可见，该隔离级别不能避免不可重复读问题； repeatable read：可重复读，在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务commit或rollback。但是，其他事务的insert/delete操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复select的结果一样，除非本事务中update数据库。 serializable：串行化，只允许事务串行执行。 mysql中默认第三个隔离级别 oracle中默认第二个隔离级别

16、MySQL存储引擎中的MyISAM和InnoDB区别详解

存储结构：每个MyISAM在磁盘上存储成三个文件：.frm文件存储表定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。 存储空间：MyISAM可被压缩，占据的存储空间较小，支持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 事务支持：MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。 表锁差异：MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。 全文索引：MyISAM支持 FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。 表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。 外键：MyISAM不支持外键，而InnoDB支持外键。

17、 什么是存储过程？有什么特点？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。存储过程具有以下特点： 存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率； 当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码； 减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小； 通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

18、 简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别： Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器； Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小； Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。 因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

19、 数据库触发器的概念及作用

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。 特点：触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行； 作用：保证数据的完整性，起到约束的作用； 使用的一个实例：建立数据库增删改的触发器，当执行增删改的时候触发各自的触发器并将原始数据记录在备份表中。当需要执行回滚操作时，可以恢复。 https://www.cnblogs.com/zwj-199306231519/p/10766887.html

20、 什么叫视图？游标是什么？

视图：是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。 游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。 区别：游标和视图的本质是不同：一个是作为指针操作，一个是作为数据库对象来展现给用户。占用的资源不用：游标占用的资源很大，而视图占用的资源很小。工作方式不同：游标对需要的数据操作是针对行进行操作的，而视图是针对基表的一个整体的查询。 在操作mysql的时候，我们知道MySQL检索操作返回一组称为结果集的行。这组返回的行都是与 SQL语句相匹配的行（零行或多行）。使用简单的 SELECT语句，例如，没有办法得到第一行、下一行或前 10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

21、 讲讲数据库里的锁机制。

锁是计算机协调多个进程或线程并发访问某一资源的机制。 从对数据的操作类型分为：读锁(共享锁)（针对同一份数据，多个读操作可以同时进行而不会互相影响。）和写锁（排它锁）（当前写操作没有完成前，它会阻断其他写锁和读锁。） 从对数据操作的粒度分为：表锁（偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低）和行锁（偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高）；

对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

InnoDB使用行锁需要注意的点：用“where”对行进行加锁时，扫描过的所有行都会被上锁。因此当“where”中的条件没有用索引或者出现索引失效的情况下，就会全表扫描，那样行锁就会变成了表锁；

22、 什么是间隙锁，其作用及危害

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”， InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。 由于MySql默认的隔离级别是RR（可重复读），所以没有办法解决幻读的问题。但是间隙锁的存在，有可能会避免幻读现象； 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。有可能会降低并发性。

23、 数据库的优化有哪几个方面

数据库的优化主要有四个方面：SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化。

24、 SQL语句及索引的优化方法

怎么发现有问题的SQL：1）MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。2）使用 EXPLAIN 关键字分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay，则往往表示SQL需要优化了。 优化方法： 1） 使用索引加快查询分组和排序速度（但是注意索引失效的情况）； 2） 关联查询优化：保证被驱动表的join字段已经被索引；left join 时，选择小表作为驱动表，大表作为被驱动表；子查询尽量不要放在被驱动表，有可能使用不到索引；（关联查询中驱动表会被全表扫描，所以需要越小越好） 3） 子查询优化：有索引的情况下 用 inner join 是最好的 其次是 in ，exists最糟糕。无索引的情况下用，小表驱动大表 因为join 方式需要distinct ，没有索引distinct消耗性能较大，所以exists性能最佳in其次join性能最差。 4） order by关键字优化 5） GROUP BY关键字优化 6） 去重优化，尽量不要使用distinct。

25、 数据库表结构的优化

数据库表结构的优化包括选择合适数据类型、表的范式的优化、表的垂直拆分和表的水平拆分等手段。

26、 什么是数据库连接池

数据库连接池是负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个。 数据库连接池的解决方案是在应用程序启动时建立足够的数据库连接，并将这些连接组成一个连接池。由应用程序动态地对池中的连接进行申请、使用和释放。对于多于连接池中连接数的并发请求，应该在请求队列中排队等待。并且应用程序可以根据池中连接的使用率，动态增加或减少池中的连接数。

27、 传统的连接机制与数据库连接池运行机制区别

传统的连接机制：TCP三次握手建立连接；mysql认证；sql查询；mysql关闭；tcp四次挥手断开连接。在sql查询阶段只会执行一条sql语句。 数据库连接池机制：过程与传统的机制相同，不同点在于在sql查询阶段可以进行多次查询，也就是会复用之前创建的连接，减少网络开销，提升速度。

28、 数据库链接池的工作原理

连接池的工作原理主要由三部分组成，分别为：连接池的建立；连接池中连接的使用管理；连接池的关闭。 连接池的建立：一般在系统初始化时，连接池会根据系统配置建立，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销。 连接池的管理：当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没达到就重新创建一个连接给请求的客户；如果达到就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过就从连接池中删除该连接，否则保留为其他客户服务。该策略保证了数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销。 连接池的关闭：当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，该过程正好与创建相反。

29、 什么是超键，候选键，主键和外键

超键：在关系中能唯一标识元组属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。 候选键：是最小超键，即没有冗余元素的超键。 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。主键是从候选键中选择的一个。 外键：在一个表中存在的另一个表的主键称此表的外键。

30、 数据库中char和varchar的区别

定长与变长：char是定长，默认为1，对于指定的长度，如达不到改长度就用null填充，varchar变长，根据实际长度分配空间大小。（c++中的数组与容器） 存储速度：char定长，有利于数据的存放和查找。 存储容量：char只能存放255个字符，和编码无关，varchar是65532个字节；

31、 数据库多表连接查询的实现方式

交叉连接：即笛卡尔乘积，是指关系中所有元素的任意组合。（cross join） 内连接：是一种任意条件的查询，如果两个表的字段满足连接条件，就从两表中提取数据组合成新的记录。（inner join … on…）,根据条件不同可以分为等值连接（有重复列），不等连接和自然连接（无重复列）。 自连接：再一次连接查询中，涉及到的两个表都是同一个表，这种查询就称为自连接查询。 外连接：内连接的查询结果都是满足连接条件的元组。但有时我们也希望输出那些不满足连接条件的元组信息。包含有：左外连接（LEFT OUTER JOIN…ON）左外连接查询中左端表中的所有元组的信息都得到了保留。右外连接（RIGHT OUTER JOIN…ON）右外连接查询中右端表中的所有元组的信息都得到了保留。全外连接（FULL OUTER JOIN）全外连接查询中所有表中的元组信息都得到了保留。三种方式没找到的元素都用NULL代替。