面试之数据库篇

最新推荐文章于 2024-07-05 14:22:24 发布

yana_balabala

最新推荐文章于 2024-07-05 14:22:24 发布

阅读量466

点赞数

分类专栏：面试数据库文章标签： DataBase 数据库面试

本文链接：https://blog.csdn.net/yana_balabala/article/details/99748096

版权

数据库同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

面试

0 篇文章 0 订阅

订阅专栏

本篇是我总结的面试中常见的数据库的知识点，可能会有疏漏或者我自己理解不到位的地方，欢迎留言指正。

存储过程：在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果存储过程带有参数）来执行它。

存储过程的特点：有输入输出参数，可以声明变量，可使用if/else,case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；模块化，封装，代码复用，速度快，只有首次执行需要经过编译和优化步骤，后续从调用可以直接执行。

存储过程的优点：

1，增强SQL语言的功能和灵活性；

2，标准组件式编程；

3，较快的执行速度；

4，可作为一种安全机制。

缺点：不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；

不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务

事务：事务是指由一系列数据库操作组成的一个完整的逻辑过程，这个过程中的所有操作要么都成功，要么都不成功。

事务的特性：ACID是事务的四个特性。

原子性(atomicity): 指所有在事务中的操作要么都成功，要么都不成功，所有的操作都不可分割，没有中间状态。一旦某一步执行失败，就会全部回滚到初始状态。
一致性(consistency): 指的是逻辑上的一致性，即事务完成前后，数据在业务意义上都是正确的。
隔离性(isolation): 即不同事务之间的相互影响和隔离的程度。比如，不同的隔离级别，事务的并发程度也不同，最强的隔离状态是所有的事务都是串行化的（serializable）（即一个事务完成之后才能进行下一个事务），这样并发性也会降到最低，在保证了强一致性的情况下，性能也会受很大影响，所以在实际工程当中，往往会折中一下。
持久性(durability): 可以简单地理解为事务执行完毕后数据不可逆并持久化存储于存储系统当中

事务的隔离级别：

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　 ② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　 ③ Read committed (读已提交)：可避免脏读的发生。

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

脏读：指一个事务读到了另一个事务中未提交的数据。

不可重复读：针对一条记录的，同一条记录前后不一样

虚读（幻读）：针对一张表，前后读到的记录条数不一样。

索引：是存储引擎用于快速找到记录的一种数据结构，所有索引相当于目录表，本质都是通过不断缩小查找数据的范围来快速获取数据。

索引的两大类型：hash和BTree

hash类型的索引：查询单条快，范围查询慢

BTree类型的索引：查询和树的层数有关，层数增加，数据量指数级增长

不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

磁盘IO与预读：当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也读取到内存缓冲区内。因为计算机访问一个地址的数据的时候，通常马上会访问到相邻位置的数据。一次IO读取的数据称为一页。

索引的数据结构的要求：每次查找数据时把磁盘IO次数控制在一个很小的数量级。

索引的分类：

单值索引：一个索引值包含单个列，一个表可以包含多个单值索引。

复合索引：一个索引包含多个列。

1，普通索引index：加速查找

2，唯一索引：

主键索引primary key：加速查找+约束（不为空且唯一）

唯一索引unique：加速查找+约束（唯一，允许有空值）

3，联合索引

primary key(col1,col2)：联合主键索引

unique(col1,col2)：联合唯一索引

index(col1,col2)：联合普通索引

4，全文索引fulltext：fulltext索引仅可用于MyISAM表，用于搜索很长的文档时，效果最好。

索引的缺点：索引会占用磁盘空间，如果在一个大表上建立了多种复合索引，索引文件会膨胀的很快。过多的使用索引，降低更新表数据的速度，如对表进行insert，update，delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

优点：

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

使用B树存储索引的原因：

B树存储的优点：由于搜索树的效率和树的深度有关，B+树是一种平衡的多路搜索树，B树的各种操作能使B+树保持较低的高度，从而达到有效避免磁盘过于频繁的查找存取操作，从而有效地提高查找效率。

锁是网络数据库中的一个非常重要的概念，当多个用户同时对数据库并发操作时，会带来数据不一致的问题，所以，锁主要用于多用户环境下保证数据库完整性和一致性。

锁分类：从数据库系统角度分为三种：排他锁、共享锁、更新锁。

共享锁：（Share Lock）S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。

性质：

1. 多个事务可封锁同一个共享页；

2. 任何事务都不能修改该页；

3. 通常是该页被读取完毕，S锁立即被释放。

排他锁（Exclusive Lock）X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。

性质：

1. 仅允许一个事务封锁此页；

2. 其他任何事务必须等到X锁被释放才能对该页进行访问；

3. X锁一直到事务结束才能被释放。

更新锁：U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。

性质：

1. 用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；

2. 当被读取的页要被更新时，则升级为X锁；

3. U锁一直到事务结束时才能被释放。

锁的粒度就是指锁的生效范围，就是说是行锁，还是页锁，还是整表锁. 锁的粒度同样既可以由数据库自动管理，也可以通过手工指定hint来管理。

行锁：锁的作用范围是行级别。

表锁：锁的作用范围是整张表。

数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。

死锁：T1封锁了数据R1，正请求对R2封锁，而T2封住了R2,正请求封锁R1，这样就会导致死锁，死锁这种没有完全解决的方法，只能尽量预防。解决：调整程序的逻辑，对数据库的多表进行操作时，尽量按照相同的顺序。

避免死锁：

1）以固定的顺序访问表和行。即按顺序申请锁，这样就不会造成互相等待的场面。

2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5）为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

乐观锁和悲观锁：

悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

关系型数据库和非关系型数据库：

关系型数据库：指采用了关系模型来组织数据的数据库。

关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

非关系型数据库：指非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。

非关系型数据库结构

非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

数据库	关系型数据库（SQLite,Oracle,mysql）	非关系型数据库（mongoDB,redis,HBbase）
特性	1、关系型数据库，是指采用了关系模型来组织数据的数据库； 2、关系型数据库的最大特点就是事务的一致性； 3、简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。	1、使用键值对存储数据； 2、分布式； 3、一般不支持ACID特性； 4、非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。
优点	1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解； 2、使用方便：通用的SQL语言使得操作关系型数据库非常方便； 3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率； 4、支持SQL，可用于复杂的查询。	1、无需经过sql层的解析，读写性能很高； 2、基于键值对，数据没有耦合性，容易扩展； 3、存储数据的格式：nosql的存储格式是key,value形式、文档形式、图片形式等等，而关系型数据库则只支持基础类型。
缺点	1、为了维护一致性所付出的巨大代价就是其读写性能比较差； 2、固定的表结构，灵活度稍欠； 3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。	1、不提供sql支持，学习和使用成本较高； 2、无事务处理； 3，只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库显的更为合适。 4，不适合持久存储海量数据

CAP理论：一个分布式系统不可能同时满足C(一致性)、A(可用性)、P(分区容错性)三个基本需求，并且最多只能满足其中的两项。对于一个分布式系统来说，分区容错是基本需求，否则不能称之为分布式系统，因此需要在C和A之间寻求平衡

C(Consistency)一致性

一致性是指更新操作成功并返回客户端完成后，所有节点在同一时间的数据完全一致。与ACID的C完全不同

A(Availability)可用性

可用性是指服务一直可用，而且是正常响应时间。

P(Partition tolerance)分区容错性

分区容错性是指分布式系统在遇到某节点或网络分区故障的时候，仍然能够对外提供满足一致性和可用性的服务。

完整性约束：

数据完整性：存储在数据库中的所有数据值均正确的状态。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据完整性分为四类：

1. 实体完整性（实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人.。）

2. 域完整性（域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.。）

3. 参照完整性（参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考照整性）

4. 用户定义的完整性。

约束是表级的强制规定，有以下五中：not null（非空约束），unique（唯一约束），primary key（主键约束），foreign key（外键约束），check （检查约束）。

触发器的作用？

触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据库引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎可以获得不同的功能，可以根据不同的应用选择需要的存储引擎。

MySQL支持的存储引擎包括：MyISAM，InnoDB，Memory，CSV，Archive，blackHole。

它们各自的特点包括：

特点	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	有	无
事务安全（ACID）	-	支持	-	-
支持索引	支持	支持	支持	-
锁颗粒	表锁	行锁	表锁	行锁
数据压缩	支持	-	-	支持
支持外键	-	支持	-	-

InnoDB和MyISAM的区别：

一、InnoDB支持事务，MyISAM不支持。
二、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。
三、InnoDB支持外键，MyISAM不支持
四、MySQL5.1前MyISAM是默认引擎，5.1版本后默认InnoDB
五、InnoDB不支持FULLTEXT类型的索引
六、InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。
七、对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
八、清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
九、InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'）

InooDB存储引擎的主要优点：

1，遵循ACID的模型，具有提交，回滚和保护用户数据的崩溃恢复功能；

2，提供行锁，实现MVCC（多版本并发控制），可提高多用户并发性和性能；

3，InnoDB表将数据排列在磁盘上以优化基于主键的查询；每个InnoDB表都有一个叫做聚集索引的主键索，能尽可能的减少数据查询的次数；

4，InnoDB支持外键约束，可以维护数据的完整性和一致性。

索引的建立规则：

1，选择唯一性索引，可以快速定位数据

2，常作为查询条件的字段

3，限制索引的数目

4，使用数据量少的字段，char(10）比char（100）检索效率高

5，最左匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

不推荐使用索引的情况;

1，数据唯一性差的字段

2，频繁更新的字段不要使用索引

3，在where子句中不常出现的字段不建议添加索引

索引失效的情况：

1，使用like模糊查询，尤其是以%开头；

2，where语句索引列有运算

3，where语句索引列使用了函数

4，条件语句使用or连接词，如果有不是索引的列，索引失效

5，需要类型转换

6，索引列存在空值

7，not条件：当索引条件是not的时候，执行计划会认为扫描全表效率更改。<>、NOT、in、not exists

一条SQL的执行过程：

1，首先接受到查询sql之后，数据库会立即分配一个线程对其进行处理；

2，第一步查询处理器会对SQL查询进行优化，优化后会生成执行计划；

3，然后交由计划执行器来执行。

4，计划执行器需要访问更底层的事务管理器，存储管理器来操作数据；

5，最终通过调用物理层的文件获取到查询结构信息，将最终结果响应给应用层。

执行计划提供了各种查询类型与级别，方面我们进行查看以及为作为性能分析的依据。

mysql的执行计划：使用explain关键字来客观地查看一条SQL的执行计划。

执行计划查询的字段及含义：

id	查询中各个子查询的执行顺序，id越大优先级越高越先被执行
select_type	每个子查询的查询类型。simple，primary，union，subquery
table	该语句查询的表
partitions	表分区、表创建时可以指定通过那个列进行表分区
type	访问类型，后面细说
possible_keys	查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。可能使用的索引，注意不一定会使用。
key	显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
key_length	索引长度：(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)
rows	扫描行数
ref	表的连接匹配条件
extra	信息很丰富