常见数据库面试题详解

1. 触发器

是一种特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整信和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联机运算(某表上的触发器上包含对另一个表的数据操作,而该操作又会导致该表触发器被触发)。

2.存储过程?调用?优缺点?与函数的区别?与触发器的区别?

是一个预编译的SQL语句,优点是允许模块化的设计,即只需创建一次,之后在该程序中就可以调用多次。对于需执行多次SQL的某次操作,执行效果更快。

调用1)可以用一个命令对象来调用存储过程

      2)可以供外部程序调用

优点1)存储过程是预编译过的,执行效率高

      2)存储过程的代码直接存放在数据库中,通过存储过程直接调用,减少网络通讯

      3)安全性高,执行存储过程需要有一定权限的用户

4)存储过程可以重复使用,可减少数据库开发人员的工作量

缺点:移植性差    

存储过程

函数

用于在数据库中完成特定的操作或者任务(如插入、删除等)

用于特定的数据(如选择)

 

程序头部声明用procedure 程序头部声明用function

 

程序头部声明时不需描述返回类型 程序头部声明时要描述返回类型,而且PL/SQL块中至少要包括一个有效的return语句

可以使用in/out/in out 三种模式的参数

可以使用in/out/in out 三种模式的参数

可作为一个独立的PL/SQL语句来执行

不能独立执行,必须作为表达式的一部分调用

可以通过out/in out 返回零个或多个值

通过return语句返回一个值,且改值要与声明部分一致,也可以是通过out类型的参数带出的变量

SQL语句(DML 或SELECT)中不可调用存储过程

SQL语句(DML 或SELECT)中可以调用函数

触发器与存储过程非常相似,触发器也是SQL语句集,两者唯一的区别是触发器不能用EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发(激活)执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器,所以可以用它来强制实施复杂的业务规则,以确保数据的完整性。触发器不同于存储过程,触发器主要是通过事件执行触发而被执行的,而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时,SQLSERVER就会自动执行触发器所定义的SQL语句,从而确保对数据的处理必须符合这些SQL语句所定义的规则。

3.索引作用?优缺点?什么字段适合索引?原理?种类?

索引是对数据库表中一或多个列的值进行排序的结构,是帮助MySQL高效获取数据的数据结构。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

优点:

① 建立索引的列可以保证行的唯一性,生成唯一的rowId

② 建立索引可以有效缩短数据的检索时间

③ 建立索引可以加快表与表之间的连接

④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

缺点

① 创建索引和维护索引需要时间成本,这个成本随着数据量的增加而加大

② 创建索引和维护索引需要空间成本,每一条索引都要占据数据库的物理存储空间,数据量越大,占用空间也越大(数据表占据的是数据库的数据空间)

③ 会降低表的增删改的效率,因为每次增删改索引需要进行动态维护,导致时间变长

什么样的表跟列要建立索引

① 总的来说就是数据量大的,经常进行查询操作的表要建立索引

 

② 表中字段建立索引应该遵循几个原则:

1) 越小的数据类型通常更好:越小的数据类型通常在磁盘、内存中都需要更少的空间,处理起来更快。

2) 简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂,处理起来也更耗时。

3) 尽量避免NULL:应该指定列为NOT NULL。含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

4) 对非唯一的字段,例如“性别”这种大量重复值的字段,增加索引也没有什么意义,所以索引的建立应当更多的选取唯一性更高的字段。

③ 表与表连接用于多表联合查询的约束条件的字段应当建立索引

④ 用于排序的字段可以添加索引,用于分组的字段应当视情况看是否需要添加索引。

⑤ 添加多列索引的时候,对应的多条件查询可以触发该索引的同时,索引最左侧的列的单条件查询也可以触发。

⑥ 如果有些表注定只会进行查询所有,也就没必要添加索引,因为查询全部只能进行全量搜索即扫描全表。

原理

索引的原理大致概括为以空间换时间,数据库在未添加索引的时候进行查询默认的是进行全量搜索,也就是进行全局扫描,有多少条数据就要进行多少次查询,然后找到相匹配的数据就把他放到结果集中,直到全表扫描完。而建立索引之后,会将建立索引的KEY值放在一个n叉树上(BTree)。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表,每次以索引进行条件查询时,会去树上根据key值直接进行搜索,次数约为log总条数,底数为页面存储数,例如一个100万数据的表,页面存储数为100,那么有索引的查询次数为3次log1000000100,但是全量搜索为100万次搜索,这种方式类似于二分法,但是这个是n分法。

索引对增删改的影响实际数据修改测试:

一个表有字段A、B、C,同时进行插入10000行记录测试

在没有建索引时平均完成时间是2.9秒

在对A字段建索引后平均完成时间是6.7秒

在对A字段和B字段建索引后平均完成时间是10.3秒

在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒

从以上测试结果可以明显看出索引对数据修改产生的影响

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

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

索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。

这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:

基于一个范围的检索,一般查询返回结果集小于表中记录数的30%

基于非唯一性索引的检索

根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引。

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。 主键索引 数据库表经常有一列或列组合,其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。 聚集索引 在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。

如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。

局部性原理与磁盘预读

由于存储介质的特性,磁盘本身存取就比主存慢很多,再加上机械运动耗费,磁盘的存取速度往往是主存的几百分分之一,因此为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率。

预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块,每个存储块称为一页(在许多操作系统中,页得大小通常为4k),主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行。

B-/+Tree索引的性能分析

到这里终于可以分析B-/+Tree索引的性能了。

上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析,根据B-Tree的定义,可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的,在实际实现B-Tree还需要使用如下技巧:

每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O(根节点常驻内存),渐进复杂度为O(h)=O(logdN)。一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小(通常不超过3)。

而红黑树这种结构,h明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远,无法利用局部性,所以红黑树的I/O渐进复杂度也为O(h),效率明显比B-Tree差很多。

综上所述,用B-Tree作为索引结构效率是非常高的。

4. 事务?模式?特性?

是被绑定在一起作为一个逻辑工作单元的SQL语句分组,如果任何一个语句操作失败那么整个操作就失败,以后操作就会回滚到操作前状态,或上一个节点。可在需确保是否执行时使用。要讲语句作为事务考虑,需要通过ACID测试,即原子性,一致性,隔离性和持久性。

事务通常是以BEGIN TRANSACTION开始,以COMMIT或ROLLBACK结束。

COMMIT表示提交,即提交事务的所有操作。具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去,事务正常结束。

ROLLBACK表示回滚,即在事务运行的过程中发生了某种故障,事务不能继续进行,系统将事务中对数据库的所有以完成的操作全部撤消,滚回到事务开始的状态。

事务运行的三种模式:

A:自动提交事务

每条单独的语句都是一个事务。每个语句后都隐含一个COMMIT。

B:显式事务

BEGIN TRANSACTION显式开始,以COMMIT或ROLLBACK显式结束。

C:隐性事务

在前一个事务完成时,新事务隐式启动,但每个事务仍以COMMIT或ROLLBACK显式结束。

事务的特性(ACID特性)

A:原子性(Atomicity)事务是数据库的逻辑工作单位,事务中包括的诸操作要么全做,要么全不做。

B:一致性(Consistency)事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

C:隔离性(Isolation)一个事务的执行不能被其他事务干扰。

D:持续性/永久性(Durability)一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。

:事务是恢复和并发控制的基本单位。

5.锁?游标?

是实现事务的关键,可以保证事务的完整性和并发性。与现实生活中锁一样,它可以使某些数据的拥有者,在某段时间内不能使用某些数据或数据结构。

游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,需要逐条处理数据的时候使用。

6.视图及其优缺点?应用场景?

视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作,隐藏具体的细节,保护数据;视图创建后,可以使用与表相同的方式利用它们。

优点1)对数据库的访问,视图可以有选择性的选取数据里的一部分

      2)用户通过简单的查询可以从复杂查询中得到结果

3)维护数据的独立性,视图可从多个表检索数据

4)相对相同的数据可产生不同的视图

缺点:查询视图时,必须把视图的查询转化成对基本表的查询,如果这个视图是由一个复杂的多表查询所定义就无法更改数据

如下两种场景一般会使用到视图:

(1)不希望访问者获取整个表的信息,只暴露部分字段给访问者,所以就建一个虚表,就是视图。

(2)查询的数据来源于不同的表,而查询者希望以统一的方式查询,这样也可以建立一个视图,把多个表查询结果联合起来,查询者只需要直接从视图中获取数据,不必考虑数据来源于不同表所带来的差异。

注:这个视图是在数据库中创建的 而不是用代码创建的。

7.列举表的连接方式?区别?与视图的关系?

内连接 外连接(左右全) 交叉连接

内连接:还有两个元素表相匹配才能在结果集中显示

外连接:

    左外连接:左边为驱动表,驱动表的数据全部显示,匹配表不匹配的不显示

    右外连接:

    全外连接:连接的表中数据全部显示

交叉连接:笛卡尔效应,显示结果是连接表数的乘积

视图其实就是一条查询sql语句,用于显示一个或多个表或其他视图中的相关数据。表就是关系数据库中实际存储数据用的。

8.主键 超键 候选键 外键 主键和外键的区别?

键:数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。

键:在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键:是最小超键,即没有冗余元素的超键。

键:在一个表中存在的另一个表的主键称此表的外键。

主键在本表中是唯一的、不可为空的,外键可以重复可以为空;外键和另一张表的主键关联,不能创建对应表中不存在的外键。

9.数据库三范式?

第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。

第二范式(2NF):数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖(部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况),也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式(3NF):在第二范式的基础上,数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖,指的是如 果存在”A → B → C”的决定关系,则C传递函数依赖于A。因此,满足第三范式的数据库表应该不存在如下依赖关系: 关键字段 → 非关键字段 x → 非关键字段y

10.union与union all区别

union进行表连接后会删选重复记录 union all不会 后者效率更高

11.varchar和varchar2区别

char的长度是固定的,而varchar2长度可变。前者效率更高。目前varchar是varchar2的同义词,工业标准的varchar类型可以存储空字符串,但是oracle不能这样做,尽量它保留以后这样偶的权利。| oracle自己开发了一个数据类型varchar2,这个类型不是一个标准的varchar,它将数据库中varchar列可以存储空字符串的特性可以存储null值,如果想有向后兼容的能力,oracle建议使用varchar2而不是varchar

12.order by与group by的区别

order by排序查询 asc升序 desc降序

group by分组查询 子句的查询语句需要使用聚合函数。having只能用于group by子句,作用于组内,可以直接跟函数表达式

13.行转列、列转行怎么转

decode函数/case when语句

14.drop、delete与truncate分别在什么场景之下使用?区别?

不再需要一张表的时候,用drop

想删除部分数据行时候,用delete,并且带上where子句

保留表而删除所有数据的时候用truncate

drop直接删掉表 truncate删除表中数据,再插入时自增长id又从1开始 delete删除表中数据,可以加where字句。

1) DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

2) 表和索引所占空间。当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

3) 一般而言,drop > truncate > delete

4) 应用范围。TRUNCATE 只能对TABLE;DELETE可以是table和view

5) TRUNCATE 和DELETE只删除数据,而DROP则删除整个表(结构和数据)。

6) truncate与不带where的delete :只删除数据,而不删除表的结构(定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。

7) delete语句为DML(data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

8) truncate、drop是DLL(data define language),操作立即生效,原数据不放到 rollback segment中,不能回滚

9) 在没有备份情况下,谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除,如果于事务无关,用truncate即可实现。如果和事务有关,或老师想触发trigger,还是用delete。

10) Truncate table 表名 速度快,而且效率高,因为:

11) TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。

12) 对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。

15.如何优化数据库

1.SQL语句优化

1)应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

2)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

3)很多时候用 exists 代替 in 是一个好的选择

4)用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

2.索引优化

看上文索引

3.数据库结构优化

1)范式优化: 比如消除冗余(节省空间。。)

2)反范式优化:比如适当加冗余等(减少join)

3)拆分表: 分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O,一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

4)拆分其实又分垂直拆分和水平拆分:

案例: 简单购物系统暂设涉及如下表:

1. 产品表(数据量10w,稳定)

2. 2.订单表(数据量200w,且有增长趋势)

3. 3.用户表 (数据量100w,且有增长趋势) 以mysql为例讲述下水平拆分和垂直拆分,mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分:解决问题:表与表之间的io竞争 不解决问题:单表中数据量增长出现的压力 方案: 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分: 解决问题:单表中数据量增长出现的压力 不解决问题:表与表之间的io争夺

方案: 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上

 详解Mysql安全配置

 


阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页