数据库常见面试题

一.什么是存储过程？有哪些优缺点？

什么是存储过程
一个记录集，由一些T_SQL语句组成的代码块，代码块像一个实现了一些功能的方法(对单表或多表的增删改查)。给代码块去个名字，需要用到这些功能的时候调用就行了。

好处：
数据库执行时先编译后执行，而代码块已经编译过，执行的时候不用再编译。

存储过程在程序在网络中交互时，可以替代大堆的T_SQL语句，降低网络通信，提高通信速率

通过存储过程能够使没有权限的用户在控制之下间接地存取数据路，确保数据的安全。

1.只返回单一记录集的存储过程

结果：相当于运行 select * from UserAccount 这行代码，结果为整个表的数据。

二.索引是什么？有什么作用以及优缺点

有索引的列会被排序，所以查找索引会很快，而如果查不是索引的字段就会扫描整个表。

MySQL数据库几个基本索引类型：普通索引，唯一索引，主键索引，全文索引

索引加快数据库的检索速度
索引降低了插入，删除，修改等维护任务的速度。(因为还要改变索引树)
唯一索引可以确保每行数据的唯一性
索引需要占领物理和数据空间

三、什么数据库事务？

是一个操作序列，这些序列要么全部执行，要么全都不执行。
什么数据库事务
数据路管理系统中的并发控制的任务是确保多个事务同时存取数据库中的同一数据时，满足当初定下来的隔离级别(读未提交，读已提交，可重复读，串行化)和统一性以及数据库的统一性。

2.事务的语句
　开始事物：BEGIN TRANSACTION
　提交事物：COMMIT TRANSACTION
　回滚事务：ROLLBACK TRANSACTION

3.事务的4个属性 ACID
原子性：事务内的语句时一个整体，要么全部执行，要么全都不执行。
一致性：事务前后，数据总额一致
隔离性：一个事务在全部执行完之前，其他事务不能干扰
持久性：一旦事务提交，就会被更新在数据库中。

一个事务（transaction）在等待其他事务的过程中，进行回滚（roll back）到
某个保存点（savepoint）的操作不会释放行级锁（row lock）。为了避免事务
因为不能获得锁而被挂起，应在执行 UPDATE 或 DELETE 操作前使用 FOR
UPDATE … NOWAIT 语句。（以上内容讲述的是回滚保存点之前所获得的
锁。而在保存点之后获得的行级锁是会被释放的，同时保存点之后执行的
SQL 语句也会被完全回滚）。

（四）数据库的乐观锁和悲观锁是什么？

深入理解乐观锁与悲观锁
悲观锁：
悲观锁主要用于数据争用激烈的地方，以及发生并发冲突时使用锁保护数据的成本低于回滚事务的成本的环境中。

悲观锁在整个数据处理过程中，把数据锁住。往往利用数据库的锁机制，(数据库的行锁)

MySQL InnoDB中使用悲观锁
要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，执行一个更新操作后，MySQL会立刻将结果进行提交。

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

select…for update，通过开启排他锁的方式实现了悲观锁。行级锁都是基于索引的，若SQL语句用不到索引，使用表级锁。注意。

悲观并发控制实际上是“先取锁再访问”。

乐观锁：
乐观锁认为自己在执行代码的时候，不会有线程也过来操作共享变量，实际上就是没加锁，每当提交数据更新前，每个事务会检查是不是和原来的一样，如果不一样说明别的事务更改过，会对事务进行回滚。

一般的实现乐观锁的方式就是记录数据版本。读取数据时，一起读取版本号，更新数据时，也一起更新版本号。当提交更新时，判断此刻的版本号是不是和上一次的版本号一致，一致才更新，否则认为是过期数据。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。？

（五） 使用索引查询一定能提高查询的性能吗？为什么？

通常,通过索引查询数据比全表扫描要快。

代价：
索引需要空间来存储,也需要定期维护，每当有记录在表中增减或索引列被修改时,索引本身也会被修改.。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O.索引需要额外的存储空间和处理，不必要的索引反而会使查询反应时间变慢。

基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索.(?)

（六）简单说一说drop、delete与truncate的区别

drop、truncate和delete的区别

DELETE: 只删除数据，删除一行,删除操作作为事务记录在日志中保存以便进行进行回滚操作。delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

truncate：只删除数据，删除表中所有记录。不会记录日志，执行速度很快，不能通过rollback撤消操作。表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，

drop：隐式提交，不能回滚，不会触发触发器。删除表结构及所有数据，并将表所占用的空间全部释放。

delete和truncate只删除表的数据不删除表的结构速度,
一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

（七）drop、delete与truncate分别在什么场景之下使用？

不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

（八） 超键、候选键、主键、外键分别是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键：唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：另一个表的主键称此表的外键。

（九）什么是视图？以及视图的使用场景有哪些？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

只暴露部分字段给访问者，所以就建一个虚表，就是视图。
查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异。

（十）说一说三个范式。

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如
果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段
x → 非关键字段y

作者：路人甲
链接：https://zhuanlan.zhihu.com/p/23713529
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

---

1.事务四大特性

原子性：事务内的语句时一个整体，要么全部执行，要么全都不执行。
一致性：事务前后，数据总额一致
隔离性：一个事务在全部执行完之前，其他事务不能干扰
持久性：一旦事务提交，就会被更新在数据库中。

2.数据库隔离级别

读未提交
读已提交
可重复读
串行化

问题：
脏读：事务B读取事务A还没有提交的数据(隔离级别：读未提交)
不可重复读：两次事务读的数据不一致(读已提交:读已提交)
幻读(事务A修改了数据,事务B刚好插入了那行数据)

3.MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景

MYISAM：不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描
INNODB：支持外键，行锁，查表总行数时，全表扫描

4.索引有B+索引和hash索引

Hash hash索引，等值查询效率高，不能排序,不能进行范围查询
B+ 数据有序,范围查询

5.聚集索引和非聚集索引

聚集索引 数据按索引顺序存储，中子结点存储真实的物理数据
非聚集索引 存储指向真正数据行的指针

6.索引的优缺点，什么时候使用索引，什么时候不能使用索引

索引最大的好处是提高查询速度，
缺点是更新数据时效率低，因为要同时更新索引
对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引。

7.InnoDB索引和MyISAM索引的区别

一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键

8.索引的底层实现（B+树，为何不采用红黑树，B树）重点

红黑树 增加，删除，红黑树会进行频繁的调整，来保证红黑树的性质，浪费时间
B树也就是B-树 B树，查询性能不稳定，查询结果高度不致，每个结点保存指向真实数据的指针，相比B+树每一层每屋存储的元素更多，显得更高一点。
B+树 B+树相比较于另外两种树,显得更矮更宽，查询层次更浅

9.B+树的实现

一个m阶的B+树具有如下几个特征：
1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素

10.为什么使用B+Tree

索引查找过程中就要产生磁盘I/O消耗,主要看IO次数，和磁盘存取原理有关。
根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，
将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入
局部性原理与磁盘预读

11.Sql的优化

1.sql尽量使用索引,而且查询要走索引

2.对sql语句优化
子查询变成left join
limit 分布优化，先利用ID定位，再分页
or条件优化，多个or条件可以用union all对结果进行合并（union all结果可能重复）
不必要的排序
where代替having,having 检索完所有记录，才进行过滤
避免嵌套查询
对多个字段进行等值查询时，联合索引

12.索引最左前缀问题

如果对三个字段建立联合索引，如果第二个字段没有使用索引，第三个字段也使用不到索引了

13.索引分类，索引失效条件

普通索引 最基本的索引，没有任何限制
唯一索引 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引 它是一种特殊的唯一索引，不允许有空值。
全文索引 针对较大的数据，生成全文索引很耗时好空间。
组合索引 为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则
失效条件
条件是or,如果还想让or条件生效，给or每个字段加个索引
like查询，以%开发
内部函数
对索引列进行计算
is null不会用，is not null 会用

14.数据库的主从复制

15.long_query怎么解决

设置参数，开启慢日志功能，得到耗时超过一定时间的sql

16.varchar和char的使用场景

varchar 字符长度经常变的
char 用字符长度固定的

17.数据库连接池的作用

维护一定数量的连接，减少创建连接的时间
更快的响应时间
统一的管理

19.分库分表，主从复制，读写分离

读写分离，读从库，写主库
spring配置两个数据库，通过AOP（面向切面编程），在写或读方法前面进行判断得到动态切换数据源。

20.数据库三范式

21.关系型数据库和非关系型数据库区别
关系型数据库

优点

1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；
3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
4、支持SQL，可用于复杂的查询。
5.支持事务

缺点
1、为了维护一致性所付出的巨大代价就是其读写性能比较差；
2、固定的表结构；
3、不支持高并发读写需求；
4、不支持海量数据的高效率读写

非关系型数据库

```sql
1、使用键值对存储数据；
2、分布式；
优点
无需经过sql层的解析，读写性能很高
基于键值对，数据没有耦合性，容易扩展
存储数据的格式：nosql的存储格式是key,value形式
缺点
不提供sql支持

22.数据库中join的inner join, outer join, cross join

1.以A，B两张表为例
A left join B
选出A的所有记录，B表中没有的以null 代替
right join 同理

2.inner join
A,B的所有记录都选出，没有的记录以null代替

3.cross join (笛卡尔积)
A中的每一条记录和B中的每一条记录生成一条记录
例如A中有4条，B中有4条，cross join 就有16条记录

23.有哪些锁,select时怎么加排它锁

24.死锁怎么解决

找到进程号，kill 进程

25.最左匹配原则

最左匹配原则是针对索引的
举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的，
这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，
查询条件where name=‘xxx’ and age=xx 这时的话，就利用到索引了，再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗，

1.事务四大特性
2.数据库隔离级别
3.MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景
4.索引有B+索引和hash索引
5.聚集索引和非聚集索引
6.索引的优缺点，什么时候使用索引，什么时候不能使用索引
7.InnoDB索引和MyISAM索引的区别
8.索引的底层实现（B+树，为何不采用红黑树，B树）重点
9.B+树的实现
10.为什么使用B+Tree
11.Sql的优化
12.索引最左前缀问题
13.索引分类，索引失效条件
14.数据库的主从复制
15.long_query怎么解决
16.varchar和char的使用场景
17.数据库连接池的作用
19.分库分表，主从复制，读写分离
20.数据库三范式
21.关系型数据库和非关系型数据库区别
22.数据库中join的inner join, outer join, cross join
23.有哪些锁,select时怎么加排它锁
24.死锁怎么解决
25.最左匹配原则

————————————————
版权声明：本文为CSDN博主「csdn侠」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_22222499/article/details/79060495