数据库-面试

1.事务的相关概念

定义:包含一个序列对数据库的读写操作.包含有以下两个目的:系统错误回复,并发数据库访问.

事务的具体执行过程:当事务被提交给了数据库管理系统,则DBMS需确保该事务的所以操作都完成且其结果被永久保存在数据中，如果事务中有的操作没有成功，则事务的所有操作都需要回滚（rollback），回到事务执行前的状态；同时，该事务对数据库或者其他事务的执行无影响，所有的事物都好像在独立的运行．

例子：某人在商店买了一百元的东西，当中至少包含两个操作：（１）该人账户会减少100元,(2)商店账户会增加100元支持事务的DBMS就是确保以上的两个操作(整个事务)都可以完成,或一起取消,否则就会出现100元凭空消失的情况.

 

2.事务的特性

A:atomacity,原子性 事务必须是原子工作单元;对于其数据修改,要么全部执行,要么全都不执行.

C:consistency. 一致性 事务将数据库从一种一致状态转变为另一种一致状态,就是说,事务在完成时,必须使得所有的数据都保持一致性.

I:isolation. 隔离性 一个事务的影响在该事务提交前对其他事务都没有影响.

D:durablity 持久性 事务完成后对于数据库的影响是永久性的.

原子性和一致性的区别?
一致性同时保证了数据的原子性;而数据的原子性只能保证数据的连续性.

 

3.并发控制(并发控制的主要方法就是封锁)

数据库是广大可所共享访问的,所以在数据库操作过程中很可能会出现以下一些问题.

丢失修改:两个事务T1,T2读入同一个数据并修改,T2提交的结果被T1破坏了,导致T1的修改丢失

不可重复度:事务T1读取数据后,事务T2执行更改操作,使得T1无法再次读取数据

读脏数据:事务T1修改某个数据恢复原来的值,T2读取的数据就与数据库中的数据不一致

幻读:数据在操作过程中进行了两次查询,第二次查询结果包含了第一次查询中未出现的数据,这是因为在两次查询数据之间有另外的事务插入数据造成的

 

4.封锁

定义:实现并发控制的一个非常重要的技术,就是事务T在对某个数据对象例如表,记录等操作之前,先向系统发出请求,对其加锁.数据库系统提供两种锁:

排他锁(写锁):若事务T对数据对象A加写锁,则只允许T读取和修改A,其他事务不能对A加任何类型的锁,直到T释放A的锁为止.

共享锁(读锁):若事务T对数据对象 A加读锁,则只允许T可以读取但不能修改A，其他事务只能再对A加读锁，而不能加写锁，直到事务T释放A上的读锁

区别：写锁可以对数据对象读写　　　读锁只能对数据对象读，但是可以施加其他的读锁．

 

５.事务的隔离级别

为了避免上面出现的几种情况，在标准的SQL规范定义了４给事务隔离级别，不同隔离对事务处理不同．

提交未读　提交读　　可重复读　　可序列化（有要求事务序列化的执行，事务只能一个接着一个地执行，不能并发执行）最高等级的隔离级别．

隔离地级别越高，越能保证数据地完整性，但是对并发性也有很大地影响．（完整性　一致性　并发性）

 

６封锁协议

使用读锁和写锁地时候，需要约定一定地规则．比如：何时申请，持续时间，何时释放．这些规则被称为封锁协议．针对不同的事务隔离级别，有不同地封锁协议．

一级封锁协议：事务在对数据对象进行读写操纵之前，必须对数据对象施加写锁，直到事务结束才释放．

二级封锁协议：在一级封锁协议地基础上增加事务T在读数据对象R之前必须加读锁，读完就可以释放．

三级封锁协议：在一级封锁协议地基础上增加事务T在读数据对象R之前必须加读锁，直到事务结束才释放

四级封锁协议：其实是对三级封锁协议地加强，其他事务不可以读写该数据对象中地任何数据．

 

7 并行调度

调度是指一个或多个事务按时间排序地一个序列。如果调度首先是一个事务地所以动作，其次另一事务地所有动作，以此类推，没有事务间动作地混合，我们称这一调度是串行的。

事务的正确性原则告诉我们，每个串行调度都将保持数据库的一致性。通常，不管数据库的初态是怎么样的，一个调度对数据库的影响都和某个串行调度相同，我们就称这个调度是可串行化的。

可串行化是并行调度的正确性的唯一准则，两段锁（简称2PL）协议就是为了保证并行调度可串行化而提供的封锁协议。

两段锁的协议规定：在对任何一个数据读写操作之前，事务道要首先获得对于该数据的封锁，而且在释放一个封锁之前，事务不再获得任何资源。

所谓两段的含义就是：事务分为两个阶段，第一阶段是获得封锁，也称为拓展阶段；第二阶段是释放封锁，也成为收缩阶段。

8.使用事务

开启事务：start transaction 和begin 

提交事务：commit

放弃事务：rollback

MYSQL默认设置了事务的自动提交，即一条SQL语句就是一个事务。

 

9.事务总结

事务的ACID特性是由RDBMS（关系数据库管理系统）来实现的。

数据库管理系统用日志来保证A（原子性）C（一致性）D（持久性）。日志中包含了事务对数据库所做的更新，如果事务在执行过程中发生了错误，那么就可以根据日志来撤销事务对数据库做的更新，是数据库退回到执行事务之前的初始状态。

数据库管理系统用锁机制来实现事务的I（隔离性）。当多个事务同时更新数据库中的相同数据时，只允许带锁事务更新该事务，其他事务必须等待，直到一个事务释放了锁，其他事务采有机会更新该数据。

 

10.索引

用于提交数据库表数据访问速度的数据库对象。

一条索引记录中包含的基本信息包括：键值（及定义索引时指定的所有字段的值）+逻辑指针（指向数据页或者另一索引页）

当为一张空表索引时，数据库系统将会为你分配一个索引页，该索引页在你插入数据前一直都是空的。此时此页既是根节点也是叶子节点。每当你向表中插入一行数据时，数据库系统就向此根节点中插入一行索引记录。当根节点满的时候，我们通常采用以下步骤进行分裂：

创建两个儿子节点；

将原根节点中的数据近似的拆分为两半，分别写入新的两个儿子节点；

根节点中加上两个儿子节点的指针。

通常情况下，由于索引记录仅包含索引字段值（以及4-9字节的指针），索引实体比真实的数据行要小很多，索引页相较于数据页要密集很多。一个索引页可以储存数量很多的索引记录，这意味着在索引中查找时在I/O上占很大的优势。

 

11.索引的分类

聚集索引：表数据按索引的顺序来存储的。对于聚集索引，叶子节点即存储了真实的数据行，不在有另外单独的数据页。在聚集索引中，叶节点就是数据节点，所有数据行的存储顺序于索引的存储顺序一致。在一张表上，只能创建一个聚集索引，因为表中真实数据的物理排序只能有一种。如果一张表中没有聚集索引，那么他被称为“堆集（Heap）”。这样的表中的数据行没有特定的顺序，所有的新行将被添加的表的末尾位置。

非聚集索引：表数据存储数据与索引顺序无关。对于非聚集索引，叶节点包含索引字段值指向数据页数据行的逻辑指针，该层紧临数据页，其行数量与数据表一致。

聚集索引和非聚集索引的比较：

叶子节点不是数据节点；

叶子节点为每一个真正的数据行储存一个“键—指针”对；

叶子节点 还储存了一个指针偏移量，根据页指针及指针偏移量可以具体定位到具体的数据行；

类似的，在除了叶节点外的其他索引节点，储存的也是类似的内容，只不过他是指向下一级的索引页的。

我们用字典的例子来举例：其实偏旁查找法就是典型的非聚集索引的例子。

 

12.索引失效

索引并不是时时都会生效的，出现以下几种情况，会导致索引值失效：

如果条件中有or，即使其中条件带索引页不会使用。要想使用or，又想让索引生效，只能将Or条件中的每个列都加上索引；

对于多列索引，不是使用的第一部分，则不会使用索引；

like查询是以%开头；

如果列类型是字符串，那一定要在条件中数据使用引号引用起来，否则不使用索引。

如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

 

13.索引设计的原则

字段的离散度越高，该字段就越适合选作索引的关键字；

占用储存空间少的字段更适合选作索引的关键字；

存储空间固定的字段；

最左前缀原则；

尽量使用前缀索引。

 

14.索引总结

聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚集索引，则是密集索引，在数据页的上一级索引为它每一个数据行存储一条储存记录。

与非聚集索引相比，聚集索引有着更快检索速度，更快的字段排序。

在musql中按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。

 

15.SQL语句

SQL语句（查询语句）结构是：子句，表名，常量。

SQL语法的基础是子句，子句中会包含一些关键字。每条语句均由一个关键词开始，该关键字描述这条语句要产生的动作。

create table:创建一个数据库表

drop  table:删除一个数据库表

alter table:修改数据库表结构

create view:创建一个视图

drop view:从数据库中删除视图

create index:为数据库表创建一个索引

drop index:从数据库中删除索引

create procedure:创建一个储存过程

drop procedure:从数据库中删除储存过程

create trigger:创建一个触发器

drop trigger:从数据库中删除触发器

select:从数据库表中检索数据行或列

insert:向数据库表中添加新数据行

delete:从数据库表中删除数据行

update:更新数据库表中的数据

grant:授予用户访问权限

deny:拒绝用户访问

revoke:解除用户访问权限

commit:提交当前事务

rollback:回滚当前事务

set transaction:定义当前事务数据访问特征

declare:为查询设定游标

explain:为查询描述数据访问计划
open:检索查询结果打开一个游标

fetch:检索一行查询结果

close:关闭游标

prepare:为动态执行准备SQL

execute:动态执行SQL

describe:描述准备好的查询

%表示任意0个或多个字符。可以匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

—：表示任意单个字符。匹配单个任意字符，他常用来限制表达式的字符长度语句。

在数据库原理中，关系运算包含 选择，投影，连接 这三种运算。相应的SQL语句中也有表现，其中Where表示选择，Select表示投影，From表示连接。

连接运算是从两个关系的 笛卡尔积中选择属性满足一定条件的元组，在连接中最常见的是等值连接和自然连接。

等值连接：关系R，S取两者笛卡尔积属性相等的元组，不要求属性相同。

自然连接（内连接）：是一种特殊的的等值连接，他要求比较的属性列必须是相同的属性组，并且把结果中重复的属性去掉。（要求更高）

两个关系在做自然连接的时候，选择两个关系在公共属性上值相等的元组构成新的关系。此时关系R中某些元组有可能在S中不存在公共属性上相等的元组，从而造成R中这些元组在操作是被舍弃，同样，S中某些元组也可能被舍弃。这些舍弃的元组就被成为 悬浮元组。

如果把悬浮分组也保存在结果中，而在其他属性上置NULL，那么这种连接就成为外连接，如果只保留左边关系R中的悬浮元组就叫做左外连接（左连接），如果只保留右边关系S中的 悬浮元组就叫做右外连接（右连接）。

 

16.JOIN

JOIN用于多表中字段之间的联系，按照功能大致分为以下三类：

inner join（内连接，或等值连接）：取得两个表中存在连接匹配关系的记录。

left join（左连接）：取得左表完全记录，即使右表并无对应匹配记录。

right join（右连接）：取得右表完全记录，即使左表并无对应匹配记录。

如果想只从左表中产生一套记录，但不包含右表的记录，可以通过设置where语句来执行，如下：

select * from a left join B on A.name=B.name

where B.id is NULL

union :用于合并多个select语句的结果集，去掉重复的值。union all：作用和union类似，但是不会去掉重复值。

Cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积。

实际上，在MYSQL中Cross join与inner join的表现是一样的。都可以省略inner或cross关键字。

 

17.主键和外键

关系型数据库中一条记录中有若干属性，若其中某一个属性组 能唯一标识一条记录，该属性就可以成为一个主键，例如：学生表中的学号，课程表中的课程号，成绩表中的学号+成绩号。

定义主键和外键主要是为了维护关系数据库的完整性：

主键是能确定一条记录 的唯一标识，一条记录包含身份证号，姓名，年龄。身份证号就是唯一能确定你这个人的，其他的都可能重复，所以身份证号是主键。

外键是用于与另一张表相关联，是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是 B表的主键，那么他就可以是A表的外键。

主键：用来保证数据的完整性  只能有一个   不可重复，不可为空

外键：用于和其他表保持联系 可以有多个   可重复，可为空

索引：提高查询排序的速度  可以有多个   不可重复，可为空