面试题-数据库基础篇

一: 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)不是最优的字符长度,最优的需要按照具体情况来,但是这个长度可以保证你能少出错的一个不错的默认值。

 

 

二: 面试题目

 

一: 什么是乐观锁和悲观锁,可以举例说说?

  悲观锁:


     总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。

     传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。就像for update，再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。


  乐观锁:


     顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

     乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。

 

二: 简单介绍下什么是MVCC

   全称是:多版本并发控制(Multi-Version Concurrency Control) ，是现代数据库（包括 MySQL 、 Oracle 、 PostgreSQL 等）引擎实现中常用的处理读写冲突的手段， 目的在于提高数据库高并发场景下的吞吐性能。

   如此一来不同的事务在并发过程中， SELECT 操作可以不加锁而是通过 MVCC 机制读取指定的版本历史记录，并通过一些手段保证保证读取的记录值符合事务所处的隔离级别，从而解决并发场景下的读写冲突。

 

三: 常见的varchar(50),其中的50是什么含义

   50表达的含义是:最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

   因为varchar是变长,所以需要1-2个字节去标识这一列的长度，char, varchar类型的值，会有一个长度标识位来存值长度。

   当定义varchar长度小于等于255时，长度标识位需要一个字节；当大于255时，长度标识位需要两个字节。

 

四: int(20)中20的又有什么含义

  1、是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

  2、不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示

  3、对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样

 

五: Drop、Truncate、Delete的有什么区别

  1、Drop: 删除数据内容和定义,并释放空间

  2、Truncate: 删除数据内容、释放空间,但不删除定义,并且重新设置索引ID从1开始，不触发Delete触发器,不能Rollback,且不能携带条件删除。

  3、Delete: 删除数据内容、不释放空间、不删除定义、可以携带条件,可以进行Rollback,效率低于Truncate，因为它相当于一行行的删除。

 

六: 什么是触发器、有什么作用

  它是一种特殊的存储过程,主要是用来通过事件如插入、删除来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以联级运算。

 

七: 什么是存储过程,它的优缺点

  是一个预编译的SQL语句,优点是模块化设计即只需创建一次,以后便可以在程序中多次使用。

  缺点是: 调试麻烦，移植性差、难以维护。

 

八: 有没有使用过视图，知道视图是什么、知道视图的使用场景是什么、使用视图的时候应该注意什么

  一、定义:

     视图是一个虚拟的表(对若干个基本表的引用),具有和物理表相同的功能,可以对视图进行增、改、查操作。


  二、作用:

     方便操作、增强可读性、更加的安全、数据库授权命令不能够限制到具体的行和具体的列,但是可以通过合理的创建视图,将权限限制到具体的行和列中。


  三、使用场合:

     1、权限控制: 不希望用户访问表中某些敏感信息如:薪资

     2、简化操作、提高效率: 关键信息来源多个表时,创建视图提取需要的信息,简化操作。


  四、注意事项

     1、不能在一张或者多张关联表链接而成的视图上做同时修改两张表的操作

     2、如果视图和表是一对一关系,如果没有其他约束(如:在基本表必填的字段视图没有),是可以进行增、删、改操作。

     3、视图和表是一对多关系,如只修改一张表的数据,且没有其他约束,是可以进行”改”操作,但是删除和插入操作不行。

 

九: id作为主键,但可能不是连续增长的列,如何查询表A(id、name)中第31至40条记录

   (一):思路

      因为id可能不是连续增长的,无法直接通过排序然后limit获取,所以需要先找到记录的起始位置的ID。


   (二):针对SQL Server数据库

      Select top 10 from A where id > (select max(ID) from (select top 30,id from A order by id) T) order by A


   (三):针对如MySql这种没有top关键字的数据库,需要分步处理

   1、先查询到前三十条id:
      select id from A order by id limit 30

   2、查询到前30条数据最大的id：
      Select max(id) from (select id from A order by id limit 30)

   3、从大于前30条数据的ID中查找10条
      Select * from A where id > (Select max(id) from (select id from A order by id limit 30)) order by id limit 10

 

十: 如何通过SQL语句完成表结构和数据的复制

   1、create table 新的表名 select * from 旧表名(可以复制结构和数据,但是像主键这种属性无法复制)

   2、create tale 新表名 like 旧表名(只复制表结构,不复制数据)

   3、insert into 新表(可以选择复制的字段) select * from 旧表(复制字段-可选)

   4、select * into 新表 from 旧表 where 1=2(复制旧表结构到新表中,1=2的目的就是为了不复制表中的数据)

   5、select * into 新表 from 旧表(复制旧表内容到新表中)

 

四: 35家公司面试题目的大纲

__EOF__

本文作者：夜雨闻铃
本文链接：https://www.cnblogs.com/sugeek/articles/16614015.html
关于博主：编程菜鸟一只，希望每个今天胜过昨天，一步步走向技术的高峰！
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！