《面试季》经典面试题-数据库篇(一)

前言

• 大家好,这里是IT学习日记,相信大家对今年IT的行情应该也有所了解了，从大厂到小厂，各种裁员消息。公司裁员我们无法决定，我们能做的就是不断提升自己，提前准备。
• 本系列文章主要分享了之前博主真实面试中遇到的一些问题，希望能够帮助准备就业或者跳槽的朋友。

面试题目

一: Mysql的存储引擎分类

InnoDB: 支持事务,行锁及无锁读提高了并发的效率,为了数据的完整性,支持外键

MyISAM: 不支持事务和外键,表级别锁,优势在于访问速度快,一般用于只读或者以读为主的数据场景。

Memory: 在内存中存储所有数据,应用于对非关键数据的快速查询,默认使用HASH索引,但是服务关闭,数据会消失。

CSV: 它的表是以逗号分隔的文本文件,可以允许以CSV格式导入导出,以相同的格式与脚本和应用进行交互,所有列必须不能为null,不支持索引,可以对数据文件直接编辑，保存文本文件内容

NDB: 又叫NDBCLUSTER -- 这种集群数据引擎适用于需要最高程度的正常运行和可用性的应用,但是MySql5.6暂不支持

设置存储引擎方式: create table 表名()engine=innodb/myisam

二: InnoDB和MyISAM的区别

(一) InnoDB的特点:

  1、支持事务处理、ACID事务特性；

  2、实现了SQL标准的四种隔离级别；

  3、支持行级锁和外键约束；

  4、可以利用事务日志进行数据恢复。

  5、锁级别为行锁，行锁优点是适用于高并发的频繁表修改，高并发是性能优于 MyISAM。缺点是系统消耗较大。

  6、索引不仅缓存自身，也缓存数据，相比 MyISAM 需要更大的内存。

(二) MyISAM的特点:

  1、锁级别为表锁，表锁优点是开销小，加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务。

  2、此引擎不支持事务，也不支持外键。

  3、INSERT和UPDATE操作需要锁定整个表；

  3、它存储表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。

(三) 适用场景

  MyISAM适合: (1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

  InnoDB适合: (1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

三: 如何选择Mysql的存储引擎

根据系统的业务要求选择,首先要了解索引的特点

InnoDB: 如果对数据的完整性要求比较高,且除了插入和查询外,还存在着许多更新和删除操作的,适用于选择InnoDB,InnoDB也是Mysql现在默认的存储引擎。

MyISAM: 以只读或者插入操作为主,很少的更新和删除操作的,并且对数据完整性要求不高的可以选择。

四: 数据库语句的执行顺序

(一): 执行顺序

  from -> on -> join -> where -> group by -> having -> count(聚合函数) -> select -> distinct -> order by -> limit

(二): 执行步骤解释:

  (1)、from: 表示数据的来源

  (2)、on: 表示数据的关联表,执行完后生成一个临时表t1,提供给下一步的操作使用

  (3)、join: 将join表的数据补充到on执行完成的临时表t1中,如: left join则将坐标剩余的数据添加到临时表t1中,如果join超过3个,则重复on...join之间的步骤。

  (4)、where: 根据携带的条件,从临时表中筛选出符合条件的数据,并生成临时表t2。

  (5)、groub by: 根据携带的条件,将临时表t2进行相应的数据分组,并形成临时表t3,如果语句包含了group by则它后面的字段必须出现在select中或者出现在聚合函数中,否则会报SQL语法错误。

  (6)、having: 筛选分组后临时表t3的数据,得到临时表t4。

  (7)、count等聚合函数: 对临时表进行指定字段的聚合函数操作,形成临时表t5。

  (8)、select: 从临时表筛选出需要返回的数据,形成临时表t6。

  (9)、distinct: 对临时表t6进行指定的去重筛选,形成临时表t7。

  (10)、order by: 对临时表t7排序,形成临时表t8。

  (11)、limit: 筛选返回的数据条数   想要了解更多的执行过程的问题,可以查看之前专门解析执行过程的文章: 你真的懂使用Group by?

五: Mysql和PostGreSQL有什么区别?

回答思路:

  面试官询问这个问题,原因可能是你在自己的简历中有描述使用到两种不同的数据,主要考察两个方面。

一个是考察你在工作中是否善于思考,一般数据库的选型都是公司的架构师或者组长选择,你可能只是一名组员,只需要负责使用即可,但是,如果你能够主动去思考为什么会选择使用这个数据库而不是使用其他数据库,了解两者的一些差别,这个会很给面试官添加印象分,证明你在平常的工作中是善于去思考的。

  第二个考察的方面,是看你是否能够结合项目或者公司现在有的业务去讲解使用当前数据库的一些利弊,这同样也是一个加分项,毕竟技术的选型最后还是要考虑业务的支撑,因此,这个问题主要从这两方面回答会有很不错的效果。

第一方面:

    1、Mysql中text类型有不同的限制(既:small text middle text...)，但是Pg没有这种限制。

    2、MySQL 里需要 utf8mb4 才能显示 emoji 的坑, Pg 就没这个坑。

    3、MySQL 不支持 OVER 子句, 而 Pg 支持. OVER 子句能简单的解决 "每组取 top 5" 的这类问题。

    4、pg支持更多的数据类型如:jsonb array等,对地理信息处理扩展更好的支持,有更多的数据源。

    5、在高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySQL 明显出现一个波峰后下滑

第二方面:

  可以结合项目的一些业务场景来回答体现使用这种数据库的优势。如使用PostgreSQL,回答如下。

  因为这个项目的技术选型是由我们公司架构师进行选择的,但是,我也通过项目和公司的业务了解到一些选择PG数据库的好处,我们的公司主要项目是公安的相关系统,系统中涉及到很多地理位置信息数据的处理,PG数据库对地理信息的存储和拓展都有很好的支持,这也是我们项目中选择PG数据库的一个原因等等。

六: 事务的隔离级别和存在的问题

(一): Read Uncommited(读未提交)

    1、定义: 可以读取到其他没有提交的事务的内容。

    2、并发情况下存在的问题: 脏读,不可重复读，幻读

(二): Read Committed(读已提交)

    1、定义: 可以读取到其他提交的事务的内容。

    2、并发情况下存在的问题: 不可重复读，幻读

(三): Repeatbale Read(可重复读)

    1、定义: 同一个事务下可以重复读取,数据都一样。

    2、并发情况下存在的问题: 幻读(采用多版本并发控制（MVCC）机制解决幻读问题。)

(四): serialized(串行化)

    1、可读，不可写。像java中的锁，写数据必须等待另一个事务结束。

    2、不存在问题

七: 事务并发情况下出现的问题和解决方案

(一): 出现的问题:

1、更新丢失: 并发事务时,可能出现多个事务同时更新同一条记录,导致前一个事务更新的被后面事务的更新覆盖。

2、脏读: 一个事务读取到另一个事务没有提交的数据

3、不可重复读: 在同一个事务中,前后读取的相同的条件下的数据不一样(在并发情况下另外一个事务对数据进行了修改)

4、幻读: 同一个事务下,前后读取的数据不一样(在并发情况下,另外的事务对数据进行了删除或者增加的操作)

(二): 解决方案:

     1、更新丢失更新问题可以通过应用层来解决,如加锁。

     2、脏读、不可重复读、幻读通过数据库提供的隔离机制进行处理,实现隔离机制的方法如下: 加读写锁,一致性快照读即MVCC。

八: 数据库范式的理解

1、第一范式: 每个列都不能再拆分

2、第二范式: 在第一范式的基础上,非主键列完全依赖于主键,而不能依赖于主键的一部分。

举例:      如关系模型（职工号，姓名，职称，项目号，项目名称）中，职工号->(依赖)姓名，职工号->职称，而项目号->项目名称(项目名称依赖于项目号,但是项目号并不是这个关系模型中的主键)。显然依赖关系不满足第二范式，常用的解决办法是拆分表格，比如拆分为职工信息表和项目信息表。

3、第三范式: 在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键(不存在传递依赖)

举例:    如:Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）这样一个表结构，就存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话)。我们应该拆开来，如下： （学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

总结:

     第一范式：具有原子性

     第二范式：主键列与非主键列遵循完全函数依赖关系

     第三范式：非主键列之间没有传递函数依赖关系

九: 数据库中的约束种类

   1、NOT NULL 非空约束

   2、UNIQUE: 空间内容不能重复、一个表可以存在多个

   3、PRIMARY KEY: 一个表只能存在一个,且不能重复,不能为空

   4、FOREIGN KEY: 用于关联表链接得字段,防止非法数据插入外键列

   5、CHECK: 用于控制字段得值范围

十: SQL中得五种关联查询

   1、交叉查询(笛卡尔积 cross join)

   2、内连接(Inner join)

   3、外连接(left join/right join)

   4、联合查询(union/union all)

   5、全连接(full join) - MYSQL不支持

十一: mysql中得exist和in的区别

(一): 含义

     mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。

(二): 特点

     1、如果查询的两个表大小相当，那么用in和exists差别不大。

     2、如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

     3、not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

十二: 为什么varchar默认长度是255

1、mysql要求varchar一个行的定义长度不能超过65535bytes,这个大小包括了字段占用的空间在内,text和blob等大字段除外(注: 单行最大限制指的就是一张表中所有字段的所设置的长度总和不得超过65535字节)

2、InnoDB存储引擎的表索引的前缀长度最长是: 767字节,如果需要创建索引,不能超过这个长度。而utf-8编码时: 255 * 3(一个字符占3个字节) = 765字节,恰恰是能够建立索引的最大值。单列索引的长度的限制(5.6里面默认不能超过767bytes，5.7不超过3072bytes)

3、总结: varchar(255)不是最优的字符长度,最优的需要按照具体情况来,但是这个长度可以保证你能少出错的一个不错的默认值。

小结

   不积跬步，无以至千里；不积小流，无以成江海。今天播种努力的种子,总会有一天发芽!