常规技术面试题（数据库）

31. 存储过程和触发器的区别?

触发器与存储过程的主要区别在于触发器的运行方式。存储过程必须有用户、应用程序或者触发器来显示的调用并执行，而触发器是当特定时间出现的时候，自动执行或者激活的，与连接用数据库中的用户、或者应用程序无关

32. 存储过程和函数的区别?

存储过程是用户定义的一系列SQL语句的集合,而函数通常是数据库已定义的方法,具体区别如下:

1.对于存储过程来说可以返回参数,而函数只能返回值或者表对象.

2.函数必须有返回值,存储过程可有可无

3.存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句的一部分来调用.

33. 聚集索引和非聚集索引区别

聚集索引，数据按索引顺序存储，中子结点存储真实的物理数据

非聚集索引，存储指向真正数据行的指针

34. 索引的优缺点，什么时候使用索引，什么时候不能使用索引？

索引最大的好处是提高查询速度。

缺点是更新数据时效率低，因为要同时更新索引 ，对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引。

35. 数据库的优化

1.创建适当的索引

2.对sql语句优化

使用exists或not exists代替in或not in

使用存储过程

用union替换or（适用于索引列）

where代替having,having 检索完所有记录，才进行过滤

使用select top或set rowcount来限制操作的行

避免嵌套查询

对多个字段进行等值查询时，联合索引

36. 数据库的主从复制

默认异步复制，容易造成主库数据和从库不一致

一个数据库为Master,一个数据库为slave,通过Binlog日志来实现

slave两个线程，一个线程去读master binlog日志，写到自己的中继日志

一个线程解析日志，执行sql

master启动一个线程，给slave传递binlog日志

半同步复制

只有把master发送的binlog日志写到slave的中继日志，这时主库才返回操作完成的反馈，性能有一定降低

并行复制

slave 多个线程去请求binlog日志

37. long_query怎么解决

设置参数，开启慢日志功能，得到耗时超过一定时间的sql

(1)slow_query_log 这句是开启记录慢查询功能，slow_query_log=0关闭；slow_query_log=1开启（这个1可以不写）

(2)long_query_time = 1 这句是记录超过1秒的SQL执行语句

38. varchar和char的使用场景

用来存储字符

varchar适用字符长度经常变的

char适用字符长度固定的

39. 数据库连接池的作用

维护一定数量的连接，减少创建连接的时间

更快的响应时间

统一的管理

40. 分库分表，主从复制，读写分离

读写分离，读从库，写主库

spring配置两个数据库，通过AOP（面向切面编程），在写或读方法前面进行判断得到动态切换数据源。

41. 数据库三范式

1NF 属性不可分

2NF 非主键属性，完全依赖于主键属性

3NF 非主键属性无传递依赖

42. 数据库中join的inner join, outer join, cross join

以A，B两张表为例 A left join B

选出A的所有记录，B表中没有的以null 代替

right join 同理

inner join

A,B的所有记录都选出，没有的记录以null代替

cross join (笛卡尔积)

A中的每一条记录和B中的每一条记录生成一条记录

例如A中有4条，B中有4条，cross join 就有16条记录

43. 有哪些锁,select时怎么加排它锁

乐观锁，悲观锁，排它锁，共享锁，更新锁，表锁，行级锁。

乐观锁：乐观锁不会锁住任何东西，也就是说，它不依赖数据库的事务机制，乐观锁完全是应用系统层面的东西。

悲观锁：悲观锁是指假设并发更新冲突会发生，所以不管冲突是否真的发生，都会使用锁机制

排它锁: 可以防止并发事务对资源进行访问。

共享锁: 允许并发事务在封闭式并发控制下读取资源。

更新锁:是共享锁和排他锁的结合。

行级锁：单独的一行记录加锁

表锁：锁住整个表，可以同时读，写不行

在Select语句中加for update是给相应的行增加排他锁。Select出来的数据别的事务不能读取，不能修改、不能删除。

44. 死锁怎么解决

找到进程号，kill 进程

产生死锁的原因：

一是系统提供的资源数量有限，不能满足每个进程的使用；二是多道程序运行时，进程推进顺序不合理。

产生死锁的必要条件是：

1、互斥条件；

2、不可剥夺条件（不可抢占）；

3、部分分配；

4、循环等待。

根据产生死锁的四个必要条件，只要使其中之一不能成立，死锁就不会出现。为此，可以采取下列三种预防措施：

1、采用资源静态分配策略，破坏"部分分配"条件；

2、允许进程剥夺使用其他进程占有的资源，从而破坏"不可剥夺"条件；

3、采用资源有序分配法，破坏"环路"条件。

解除死锁常常采用下面两种方法：1、资源剥夺法；2、撤消进程法

45. 最左匹配原则

最左匹配原则是针对索引的

举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的，这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，查询条件where name=’xxx’ and age=xx 这时的话，就利用到索引了。因为创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的age字段进行排序。其实就相当于实现了类似 order by name age这样一种排序规则。所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个age字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的为什么要强调最左匹配原则的原因。

PS：上面的比较繁琐，可以看看下面这个：

顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

具体链接：https://www.cnblogs.com/u013533289/p/11695122.html

46. SqlServer是一种大型数据库，他的存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。

它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQLServer的存储容量是可以扩大的.
SQL Server 数据库有三种类型的文件：

主要数据文件
主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。

次要数据文件
次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。

日志文件
日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。

47. sqlserver数据库中常用的聚合函数有哪些？

Max(),Avg(),Count(),Min(),Sum()。

中文：最大值，平均值，数据条数，最小值，总和。

48. 数据库主键、外键、约束、索引的作用是什么？有几种连表查询方式？

主键、外键及约束的作用：保证数据的完整性

索引的作用：索引是一个数据结构，用来快速访问数据库表格或者视图里的数据，加快数据库的搜索引擎对数据的检索效率

方式：左连接、右连接、内连接、自连接

49. 除了sqlserver存储过程实现分页，还有什么实现方法？

利用select top 和select not in进行分页

利用select top 和 select max(列)

利用Row_number()给数据行加上索引

利用临时表及Row_number

小剧场：还在不在。Still not.