数据库面试总结之终结篇

一、数据库基础知识

0、数据库的种类

添加链接描述
早期较为时兴的数据库种类有三种，分别是层次式数据库、网络式数据库和关系型数据库。
而在如今的互联网中，最常见的数据库种类主要有2种，即关系型数据库和非关系型数据库。

关系型数据库

主要代表：SQL Server，Oracle, Mysql, PostgreSQL。

1概念：
简单来说，关系模式就是二维表格模型。关系型数据库也选用由列和行构成的二维表来管理数据，简单易懂。

2优点
（1）.容易理解，二维表的结构非常贴近现实世界，二维表格，容易理解。

（2）使用方便，通用的sql语句使得操作关系型数据库非常方便。

（3）易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率。

3.瓶颈

(1 )海量数据的读写效率。
对于网站的并发量高，往往达到每秒上万次的请求，对于传统关系型数据库来说，硬盘I/o是一个很大的挑战。

(2) 高扩展性和可用性。
在基于web的结构中，数据库是最难以横向拓展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没有办法像web Server那样简单的通过添加更多的硬件和服务节点来拓展性能和负载能力。

非关系型数据库

主要代表MongoDB，Redis、CouchDB。

  NoSQL非关系型数据库，主要指那些非关系型的、分布式的，且一般不保证ACID的数据存储系统。
  NoSQL提出了另一种理念，以键值来存储，且结构不稳定，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构，可以减少一些时间和空间的开销。使用这种方式，为了获取用户的不同信息，不需要像关系型数据库中，需要进行多表查询。仅仅需要根据key来取出对应的value值即可。

分类

   非关系数据库大部分是开源的，实现比较简单，大都是针对一些特性的应用需求出现的。根据结构化方法和应用场景的不同，分为以下几类。

（1）面向高性能并发读写的key-value数据库

主要特点是具有极高的并发读写性能，例如Redis、Tokyo Cabint等。

（2）面向海量数据访问的面向文档数据库

特点是，可以在海量的数据库快速的查询数据。例如MongoDB以及CouchDB.

（3）面向可拓展的分布式数据库

解决的主要问题是传统数据库的扩展性上的缺陷。

缺点

  但是由于Nosql约束少，所以也不能够像sql那样提供where字段属性的查询。因此适合存储较为简单的数据。有一些不能够持久化数据，所以需要和关系型数据库结合。

1、关系数据库语言的种类

关系数据库的语言分为三类，它们是：
（1）数据定义语言，也就是DDL（Data Defining Language）例如CREATE，DROP，ALTER;

（2）数据操纵语言DML(Data Manufacturing Language)，例如SELECT，INSERT，UPDATE，DELETE；

（3）以及数据控制语言DCL（Data Control language），例如GRANT，RMOVE，COMMIT，ROLLBACK。

而SQL（结构化查询语言，structrued query language）是集DDL、DML、DCL为一体的标准关系数据库语言。其中DDL数据定义语言用于定义和管理SQL数据库中的所有对象的语言；DML数据操纵语言，SQL中处理数据等操作统称为数据操纵语言。

2、SQL和T-SQL的联系与区别

添加链接描述
SQL（Structrued Query Language）：结构化查询语言。
T-SQL（Transact-SQL）：T-SQL是SQL语言的一种版本（准确来说是SQL语设计语言的增强版），且只能在SQL SERVER上使用。它是ANSI SQL的加强版语言、提供了标准的SQL命令。另外，T-SQL还对SQL做了许多补允，提供了数据库脚本语言，即类似C、Basic和Pascal的基本功能，如变量说明、流控制语言、功能函数等。

3、SQL, Mysql, SQL Server, Oracle 之间的区别与联系

添加链接描述
添加链接描述

SQL：结构化查询语言，本质是编程语言。
Mysql, SQL Server, Oracle则是关系型数据库。

（1）Oracle：商用关系型数据库。由于其诞生早、结构严谨、高可用、高性能等特点，使其在传统数据库应用中大杀四方，金融、通信、能源、运输、零售、制造等各个行业的大型公司基本都是用了Oracle，早些年的时候，世界500强几乎100%都是Oracle的用户。

（2）SQL Server：商用关系型数据库。MS SQL Serve（Microsoft SQL Server）是一整套数据解决方案，主要面向中小企业。Sybase则专注于Linux/Unix方向的数据库开发。MS SQL Server主要面向中小企业。其最大的优势就是在于集成了MS公司的各类产品及资源，提供了强大的可视化界面、高度集成的管理开发工具。

（3）Mysql：开源数据库，简便易用。mysql是单纯的数据库存储。09年，Oracle收购了Sun和MySQL。

总结：
T-SQL：为SQL的扩展语言，提供标准SQL的DDL和DML功能外，还加入了程序语言中的if,while 等语法，同时可以使用函数等数据库对象。

4、数据库应用范畴

  事实上，数据库的应用不仅仅局限于数据存储，而是致力于提供一套完整的数据存储、管理以及应用方案。其一个典型的应用场景如：ERP。
  ERP即企业资源计划：是针对物资资源管理（物流）、人力资源管理（人流）、财务资源管理（财流）、信息资源管理（信息流）集成一体化的企业管理软件；更是一种管理思想。
SAP是德国的一家软件公司，也可以称其旗下的ERP产品为SAP
什么是ERP
ERP和SAP的区别

5、数据库的三大范式

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

第二范式（2NF）：满足第一范式的前提，数据库的所有非主属性都完全依赖于任何一个候选码（不存在部分函数依赖）。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。如果关系模式满足第三范式，那么它的所有非主属性既不传递函数依赖于码，也不部分函数依赖于码。

BC范式
在关系模式R中，若每一个决定属性组都包含候选码，则R满足BC范式。
BC范式比第三范式更进了一步，通常认为BC范式是修正的第三范式，有时也称为扩充的第三范式。

多值依赖

第四范式

SQL语言细节

关系数据库的数据类型

二、引擎

数据库引擎
数据库引擎的使用比较

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

  数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。
   使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

常用的存储引擎有以下：

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

• 插入缓冲（insert buffer)
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

三、索引

索引

什么是索引

索引就像书的目录， 通过书的目录就准确的定位到了书籍具体的内容（ 索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构。）

数据库中的索引类型

数据库中索引类型
数据库中的索引
参考链接二
（ MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引）
按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
按物理存储分类可分为：聚集索引、非聚集索引（辅助索引）。
按字段特性分类可分为：主键索引、唯一索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

B+tree索引\Hash索引:
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

B+tree 非叶子节点只存储键值信息， 数据记录都存放在叶子节点中。B+tree 所有叶子节点之间都采用单链表连接。

Hash 索引仅仅能满足 = , IN 和 <=>(表示NULL安全的等价) 查询，不能使用范围查询。

Hash 索引无法适用数据的排序操作。

Hash 索引不能利用部分索引键查询。

Hash 索引依然需要回表扫描。

Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

聚集索引\非聚集索引：
一个表只能有一个聚集索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据；而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据。

主键索引/唯一索引/普通索引/前缀索引:

1. 主键索引
   建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。

2. 唯一索引
   建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

3. 普通索引
   建立在普通字段上的索引被称为普通索引。

4. 前缀索引
   前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

单列索引/联合索引:

1. 单列索引
   建立在单个列上的索引被称为单列索引。

2. 联合索引（复合索引、组合索引）
   建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。

索引有哪些优缺点？

索引的优点
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。
索引使用场景（重点）

设计索引的原则

1适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2基数较小的类，索引效果较差，没有必要在此列建立索引
3使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

创建索引的原则

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

创建索引时需要注意什么？

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

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

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
基于非唯一性索引的检索

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2然后删除其中无用数据（此过程需要不到两分钟）
3删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

四、事务

事务是一个不可分割的数据库操作序列，事务有四个特性ACID。

1原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
3隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

脏读、幻读、不可重复读

• 脏读(Drity Read)：一个事务读取另一个事务未提交的问题
• 幻读(Phantom Read): 同一事务中，用同样的操作读取两次，得到的记录数不相同
• 不可重复读(Non-repeatable read): 在同一事务中，两次读取同一数据，得到内容不同

什么是事务的隔离级别？MySQL的默认隔离级别是什么？

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

SQL 标准定义了四个隔离级别：

• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
• 这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到**SERIALIZABLE(可串行化)**隔离级别。

锁

数据库锁

分页技术

视图

存储过程与函数

触发器

常用SQL语句

SQL优化

数据库优化

添加链接描述
主要参考博客