新手福利：大学生毕业面试宝典——数据库技术篇

概述

数据库相关的面试题分两块：关系型数据库与非关系型数据库

1.关系型数据库

SQLserver、Oracle、MySQL

这里我以MySQL为例，常规的面试如下
1.数据库权限相关的问题
2.数据库表设计的三大范式
3.数据库操作delete/drop/trucate的区别
4.数据库的优化：查询sql优化，架构优化


端口号：3306
数据库权限：db、user、tables_priv、columns_priv
	生产环境：
		项目经理：Create、drop、grant、select、insert、Update、delete...
		项目组长：select、insert、Update、delete
		普通研发：select	
	
	测试环境：测试账号拥有所有权限

表设计以及创建
设计工具：powerdesign
设计理念：三大范式
	第一范式：列不可再分	拥抱变化
		例子：湖南省长沙市岳麓区天顶街道浪琴湾清水22栋
		违反：address
		遵循：Provence、city、area、address
	第二范式：主键约束	保障当前表的数据一致性
		例子：订单项存储	名称 单价 数量 小计
		违反：名称 单价 数量 小计
		遵循：名称 单价 数量
	第三范式：外键约束 	保障关联表的数据一致性
		例子：看病流程表 doctorid，personid，dockerName,personName
		需求：在页面上看到医生的姓名以及患者的姓名
		实现：医生表、看病流程表、患者档案表
		违反：doctorid，personid，dockerName,personName
		遵循：doctorid，personid
		
	反范式设计

表数据的增删改 查
delete/drop/trucate
查询优化
	1.涉及的表是否有构建索引
	2.排查索引失效的情况
		like/in/or/<>...
	3.通过explain执行计划，排查各个索引的执行情况
	
数据库架构优化
	分库分表
		水平分割 t_order
			例如：1000*10*10*26*365=9亿...
			t_order_1
			t_order_2
			t_order_3
			t_order_4
			....
			t_log_1
			t_log_2
			t_log_3
			t_log_4
			....
			1.quartz 任务调度框架 实现每月凌晨00:00自动生成新表
			create table t_order_${month} as
			select * from t_order where 1=2
			2.insert into t_order values(#{no},#{time}..)
			insert into t_order_${month} values(#{no},#{time}..)
			
		垂直分割
			按照数据列段的使用频率，划分为热数据以及冷数据
			拆分成两张表为一对一的关系；
			t_user 40   15	25
			t_user_hot
			t_user_cool
			create view t_user as 
			select * from t_user_hot h,t_user_cool c 
			where h.id = c.id
	MySQL集群/读写分离

2.非关系型数据库

echcache、redis

数据库键值对的存储类型：
string、set、zset、list、hash

击穿穿透雪崩
击穿：大量请求访问redis，redis的这一个key正好过期，大量请求会到达MySQL
穿透：大量请求访问redis，访问的数据在数据库中都压根不存在，每一次请求都会访问MySQL
雪崩：大量请求访问redis，redis中大量的key同一时间过期，大量请求同一时间到达MySQL，那么MySQL宕机；
大的方向：限流	RabbitMQ流量削峰
穿透：redis中给不存在的数据设置默认值
雪崩：设置的缓存策略，即不同的过期时间

标准

1.关系型数据库

1.数据库的三范式是什么

第一范式：列不可再分
第二范式：行可以唯一区分，主键约束
第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束
且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

2.InnoDB与MyISAM的区别

1.InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，  这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3.InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但   是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，  因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的  是数据文件的指针。主键索引和辅助索引是独立的。
4.InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5.Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

3.数据库的事务

什么是事务？： 多条sql语句，要么全部成功，要么全部失败。
事务的特性：
数据库事务特性：原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性(Durabiliy)。     简称ACID。
原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个  事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。    一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A  转账100元给B，不管操作是否成功，A和B的账户总额是不变的。
隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰  持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

事务保证一组原子性的操作，要么全部成功，要么全部失败。一旦失败，回滚之前的所有操作。MySql采用自动提交，如果不是显式的开启一个事务，则每个查询都作为一个事务。

隔离级别控制了一个事务中的修改，哪些在事务内和事务间是可见的。四种常见的隔离级别：

「未提交读」（Read UnCommitted），事务中的修改，即使没提交对其他事务也是可见的。事务可能读取未提交的数据，造成脏读。

「提交读」（Read Committed），一个事务开始时，只能看见已提交的事务所做的修改。事务未提交之前，所做的修改对其他事务是不可见的。也叫不可重复读，同一个事务多次读取同样记录可能不同。

「可重复读」（RepeatTable Read），同一个事务中多次读取同样的记录结果时结果相同。

「可串行化」（Serializable），最高隔离级别，强制事务串行执行。

4.MySQL索引的注意事项

1、联合索引遵循前缀原则
KEY(a,b,c)
	WHERE a = 1 AND b = 2 AND c = 3
	WHERE a = 1 AND b = 2
	WHERE a = 1
	#以上SQL语句可以用到索引
	WHERE b = 2 AND c = 3
	WHERE a = 1 AND c = 3
	#以上SQL语句用不到索引

2、LIKE查询，%不能在前
WHERE name LIKE "%wang%"
	#以上语句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。

3、列值为空（NULL）时是可以使用索引的，但MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。
4、如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引，例如：
表中只有100条数据左右。对于SQL语句WHERE id > 1 AND id < 100，MySQL会优先考虑全表扫描。
5、如果关键词or前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。
6、列类型是字符串，查询时一定要给值加引号，否则索引失效，例如：
列name varchar(16)，存储了字符串"100"
WHERE name = 100;
以上SQL语句能搜到，但无法用到索引。

5.SQL优化

1、查询语句中不要使用select *
2、尽量减少子查询，使用关联查询（left  join,right  join,inner    join）替代
3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
4、or 的查询尽量用 union或者union all  代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)
5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null 值，然后这样查询： select id from t where num=0
7、like ‘%xx%’尽量改成‘xx%’，以%开头无法命中索引，如确实要用’%xx%’可使用外部全文索引技术，如：Lucene 、Elasticsearch
8、使用explain对查询性能进行分析

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

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

7.mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

2.redis相关面试题

8.非关系型数据库和关系型数据库区别，优势比较

非关系型数据库的优势：
性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
关系型数据库的优势：
复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持：使得对于安全性能很高的数据访问要求得以实现。

9.redis有哪些数据类型

①. String(字符串)：redis最基本的数据类型，一个key对应一个value，一个键最大能存储512MB
②. Hash(哈希)：是一个键值对集合，特别适合用于存储对象
③. List(列表)：存放多个字符串值，可以重复，按照插入顺序进行排序，也可以添加一个元素到列表的头部和尾部
④. Sets(集合)：存放多个值，不可以重复，没有顺序
⑤. ZSet(有序集合)：存放多个值，不可以重复，有顺序。不同的是每个元素都会关联一个double类型的分数，redis正是通过分数来为集合中的成员进行从小到大的排序

10.什么是缓存击穿、缓存穿透、缓存雪崩？

缓存击穿

缓存击穿的概念就是单个key并发访问过高，过期时导致所有请求直接打到db上，这个和热key的问题比较类似，只是说的点在于过期导致请求全部打到DB上而已。
解决方案：
（1）加锁更新，比如请求查询A，发现缓存中没有，对A这个key加锁，同时去数据库查询数据，写入缓存，再返回给用户，这样后面的请求就可以从缓存中拿到数据了。
（2）将过期时间组合写在value中，通过异步的方式不断的刷新过期时间，防止此类现象。

缓存穿透

缓存穿透是指查询不存在缓存中的数据，每次请求都会打到DB，就像缓存不存在一样。

针对这个问题，加一层布隆过滤器。布隆过滤器的原理是在你存入数据的时候，会通过散列函数将它映射为一个位数组中的K个点，同时把他们置为1。
这样当用户再次来查询A，而A在布隆过滤器值为0，直接返回，就不会产生击穿请求打到DB了。
显然，使用布隆过滤器之后会有一个问题就是误判，因为它本身是一个数组，可能会有多个值落到同一个位置，那么理论上来说只要我们的数组长度够长，误判的概率就会越低，这种问题就根据实际情况来就好了。

缓存雪崩

当某一时刻发生大规模的缓存失效的情况，比如你的缓存服务宕机了，会有大量的请求进来直接打到DB上，这样可能导致整个系统的崩溃，称为雪崩。雪崩和击穿、热key的问题不太一样的是，他是指大规模的缓存都过期失效了。

针对雪崩几个解决方案：
（1）针对不同key设置不同的过期时间，避免同时过期
（2）限流，如果redis宕机，可以限流，避免同时刻大量请求打崩DB
（3）二级缓存，同热key的方案。