java程序员一些初中级面试题（数据库部分）

说出一些数据库优化方面的经验？

1.从JDBC编程的角度讲，用PreparedStatement一般来说比Statement性能高，因为在使用时，SQL语句被预编译并存储在PreparedStatement对象中，然后可以使用PreparedStatement对象多次高效地执行该语句。

2.有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就可以去掉外键。（程序控制代替数据库控制）

3.表中允许适当冗余，比如订单表中存账号名，可以方便直接看订单是哪个账号创建的，而不用关联账号表去查询账号名。要分析业务的特点，对数据库中表做优化，选择合适的解决方案。也就是说，在有些情况下，设计数据库可以适当地违反三个范式地设计原则，以获得更好的程序性能。

4.SQL语句全部使用大写，特别是列名和表名。Oracle数据库中的语句执行机制是这样的：编写SQL语句->发给Oracle服务端->语法检查和编译成为内部指令->缓存和执行指令。在语法检查和编译阶段，Oracle会自动将SQL语句转换为大写。并且根据SQL缓存的特点，不要拼凑条件，而是用占位符参数【?】和PreparedStatement来执行参数化的SQL语句。

5.建立索引改进查询性能。当经常查询索引列中的数据时，需要在表上创建索引。索引的缺点是占用磁盘空间，并且降低添加、删除和更新行的速度。但是在大多数情况下，索引用于数据检索的速度优势大大超过它的不足之处。但是，如果应用程序非常频繁地更新数据，或磁盘空间有限，则可能需要限制索引的数量。

你有优化SQL查询数据的经验吗？如有能说一下你一般用哪些什么方式进行优化？

1.适当建立索引，用索引提高查询效率、

2.选择最有效率的表名顺序。Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句写在最后的表（基础表，Driving Table）将最先被处理。

3.WHERE子句中的连接顺序：Oracle采用自上至下的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，哪些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

4.SELECT子句中避免使用通配符【*】，需要什么字段，就写什么字段的列名。

什么是事务？事务的四个特性是什么？有哪些隔离级别？

事务（Transaction）是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作：这些操作作为一个整体一起向系统提交，要么全都执行，要么全都不执行。事务是一组不可再分的操作集合（工作逻辑单元）。

事务有四个特性，通常称为ACID特性

1.原子性（Atomicity）：事务是一个完整的操作，事务的各步操作是不可分的，要么都执行，要么都不执行。

2.一致性（Consistency）：当事务完成时，数据必须处于一致状态。

3.隔离性（Isolation）：对数据进行修改的并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。

4.持续性（Durability）：事务完成后，它对数据库的修改将被永久保存，事务日志能保持事务的永久性。

隔离级别有四种

1.Read Uncommitted（读取未提交）

2.Read Committed（读提交）

3.Repeatable Read（可以重复读）

4.Serializable（序列化）

数据库中，数据类型char和varchar2有什么区别？性能方面各有什么优势？

char表示的是固定长度，所占存储空间是固定的，定义长度是多少就是多少。数据量较大的时候，以char字段为查询条件时查询得快一点。

varchar2表示的是实际长度的数据类型，所占存储空间为实际大小，前提是不超过定义的长度。如果存储的字段不是定长的，用varchar2好一点，节省存储空间。

你怎么知道查询SQL的性能高还是低？

1.简单的可以通过查看执行SQL的运行时间。

2.可以通过查看数据库提供的语句执行计划来分析相应的数据信息，例如查看执行计划中对应的COST（成本耗费）值，反映了运行这段SQL的总体估计成本，通常COST低的执行计划要好一点。

在开发过程中遇到过数据库锁吗？怎样解锁？ 

如果Oracle中有事务未提交，又打开一个窗口去提交一个新的事务则可能会产生数据库锁。

解锁的两种方式

1.结束当前的Session（会话，这里值和数据库的通讯）就会自动解锁。

例如可以退出当前数据库连接工具或应用程序。

2.利用DBA权限强行关闭执行Lock操作的Session。

可以通过查询出SID和SERIAL#（流水号）去杀掉（关闭）会话。

-- 查询出对应会话操作的SID和SERIAL
SELECT ID, SERIAL#, USERNAME, OSUSER FROM V$SESSION;

-- 根据指定的SID和SERIAL#来杀掉（关闭）对应的会话
ALTER SYSTEM KILL SESSION 'SID, SERIAL'

怎样处理并发数据？

就数据库操作来说，是通过加锁的概念来确保并发数据操作下事务的完整性和数据库的一致性。

数据库主要有共享锁和排他锁。

排他锁：当你再修改数据的时候，别人也可以查看，但是他不能修改数据并提交，只能优先你的操作，等到你向数据库提交了数据之后他才能执行增删改的操作，这就是排他锁。

共享锁：共享锁就是你和另外一个用户可以同时共享数据（查看），但是你和他都不能修改。

DELETE FROM TABLE和TRUNCATE TABLE的区别？DROP TABLE呢？

1.TRUNCATE是DDL（数据定义语言），所做的修改是不能回滚的，也就是不可恢复的操作。用了这个命令后就收回了表空间。DELETE是DML（数据操纵语言），执行删除是会把数据写入系统回滚段（ROLLBACK SEGEMENT)中以便恢复时使用。

2.两者都是删除表中的数据而不会影响到表的结构和定义。

3.使用DROP的话就会删除表的结构。

UNION和UNION ALL有什么不同？

UNION在进行表连接后会筛选掉重复的记录，所以在表连接后会对所产生的结果集进行排序运算，删除重复的记录再返回排序后的结果。

UNION ALL只是简单地将两个结果集合并后就返回，如果两个结果集中有重复的数据，最后返回的结果集也会包含重复的数据，且不会进行排序。

从效率上来说，UNION ALL要比UNION快很多，如果可以确认合并的两个结果集中不包含重复数据的话，建议使用UNION ALL。

JDBC访问数据库的步骤？ 

1.加载JDBC驱动。

2.与数据库建立连接。

3.发送SQL语句，并得到返回结果。

4.处理返回结果。

5.关闭资源。

大数据量下的分页解决方法

最好的方法是利用SQL语句进行分页，这样每次查询出的结果集中就只包含某页的数据内容。在SQL语句无法实现分页的情况下，可以考虑对大的结果集通过游标定位方式来获取某页的数据。

不同的数据库下的分页方案各不一样，下面是主流的三种数据库的分页SQL：

SQL Server

String sql = "SELECT TOP " + pageSize + " * FROM STUDENTS WHERE ID NOT IN " + "(SELECT TOP " + pageSize * (pageNumber - 1) + " ID FROM STUDENTS ORDER BY ID)" + " ORDRE BY ID"; 

MySQL

String sql = "SELECT * FROM STUDENTS ORDER BY ID LIMIT " + pageSize * (pageNumber - 1) + "," + pageSize;

Oracle

String sql = "SELECT * FROM " + "(SELECT *, ROWNUM RID FROM (SELECT * FROM STUDENTS ORDER BY POSTIME DESC) WHERE RID <= " + pageSize * pageNumber + ") AS T" + " WHERE T > " + pageSize * (pageNumber - 1);

其实就是和它们SQL语句的不同语法有关。

简述建立索引的作用和索引的分类？

索引的作用：通过使用索引，大大地提高数据库的检索速度，改善数据库性能。

索引的分类

1.唯一索引：唯一索引不允许两行具有相同的索引值。

2.主键索引：在数据库关系图中为表定义一个主键自动创建主键索引，主键索引是唯一索引的特殊类型。

3.聚集索引：在聚集索引中表中各行的事物顺序与键值的逻辑（索引）顺序相同且唯一

4.非聚集索引：非聚集索引建立在索引页上，在查询数据时可以总索引中找到记录存放的位置。

5.复合索引：在创建索引时，并不是只能对其中一列创建索引，与创建主键一样，可以将多个列组合作为索引，这种索引称为复合索引。

6.全文索引：全文索引是一种特殊类型的、基于标记的功能性索引，由SQL Server中全文引擎服务创建和维护。

什么是存储过程，有什么优缺点？

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，是由流程控制和SQL语句书写的命名语句块，经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可，可以通过特定存储过程的名字并给出参数（如果该存储过程带有参数）来调用。 

使用存储过程有以下的优点

1.具有良好的安全性：可保证数据的安全性和完整性。通过存储过程可以使没有权限直接访问响应数据库对象的用户受控制得间接地执行数据库相关操作，从而保证数据的安全。

2.执行速度快，效率高：在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。

3.减少网络流量：可以降低网络的通信量。

4.模块化程序设计：存储过程可以封装业务逻辑，并存入数据服务器中，当业务逻辑发生变化时，无需修改调用存储过程的应用程序，只需要修改存储过程即可。

使用存储过程的缺点是

1.相对而言，调试比较麻烦。

2.移植问题，数据库端代码当然是与具体数据库相关的。

3.代码可读性差。

存储过程与SQL的区别？

存储过程是一组预编译的SQL语句，经编译后存储在数据库中，可包含一个或多个SQL语句，能够完成一定功能的子程序，需要的时候用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程可以包含数据操纵语言，逻辑和调用函数等；它又可分为系统存储过程和用户自定义的存储过程。

SQL指的是结构化查询语言，是一种ANSI的标准计算机语言。

如何创建视图？ 

创建视图主要是通过以下语句来创建。

CREATE VIEW 视图名 AS <SELECT 语句>

Oracle数据库如何提高访问性能？

避免在索引列上使用IS NULL和IS NOT NULL 操作

避免在索引中使用任何可以为空的列，Oracle蒋无法使用该索引，对于单列索引，如果列包含空值，索引中将不存在此记录；对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。

例如：

如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A，B值为(123, null)，Oracle将不接受下一条具有相同A，B值(123, null)的记录（插入）。然而如果所有的索引列都为空，Oracle将认为整个键值为空而不等于空，因此你可以插入1000条具有相同简直的记录，当然它们都是空！因为空值不存在与索引列中，所以WHERE子句中对索引列进行空值比较将使Oracle停用该索引。

用UNION替换OR（适用于索引列）

通常情况下，用UNION替换WHERE子句中的OR将会引起较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果有column没有被索引，查询效率可能会因为你没有选择OR而降低。

分离表和索引

总是将你的表和索引建立在不同的表空间内（TABLESPACES），绝不要将不属于Oracle内部系统的对象存放到System表空间里。同时，确保数据表空间和索引表空间置于不同的硬盘上（通过指定表空间的方式可以实现这个要求）。

共享SQL语句

为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中。这块处于系统全局区域SGA（System Global Area）的共享池（Shared Buffer Pool）中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句（有时被称为一个游标）时，如果它之前和之前的执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行路径。Oracle的这个功能大大提高了SQL的执行性能，并节省了内存的使用。

当你向Oracle提交一个SQL语句，Oracle会首先在这块内存中查找相同的语句。

这里需要说明的是，Oracle对两者采取的是一种严格匹配，要达到共享，SQL语句必须完全相同（包括空格，换行等）。

WHERE子句中的连接顺序

Oracle采用自上而下的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

SELECT子句中避免使用通配符【*】 

当你想在SELECT子句中列出所有的COLUMN时，使用动态列引用【*】是一个方便的方法，不幸的是，这是一个非常低效的方法。实际上，Oracle在解析的过程中，会将【*】依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

使用DECODE函数来减少处理时间 

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

SELECT 
　　COUNT(*), 
　　SUM(SAL)

FROM EMP

WEHRE DEPT_NO = '0020'
    AND ENANME LIKE 'SMITH%';

SELECT 
　　COUNT(*), 
　　SUM(SAL)

FROM EMP

WEHRE DEPT_NO = '0030'
    AND ENAME LIKE 'SMITH%'

你可以使用DECODE函数高效地得到相同结果。

SELECT
    COUNT(DECODE(DEPT_NO, '0020', 'X', NULL)) D0020_COUNT,
    COUNT(DECODE(DEPT_NO, '0030', 'X', NULL)) D0030_COUNT,
    SUM(DECODE(DEPT_NO, '0020', SAL, NULL)) D0020_SAL,
    SUM(DECODE(DEPT_NO, '0030', SAL, NULL)) D0030_DAL

FROM EMP

WHERE E_NAME LIKE 'SMITH%'

类似的，DECODE函数也可以运用于GROUP BY和ORDER BY子句中。

用TRUNCATE替代DELETE

当删除表中的记录时，在通常情况下，回滚段（ROLLBACK SEGMENTS）用来存放可以被恢复的信息。如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）。而当运用TRUNCATE时，回滚段不再存放任何可以被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。

不过要注意的是，TRUNCATE只在删除全表适用，TRUNCATE是DDL，而不是DML。

尽量多使用COMMIT 

只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。

COMMIT所释放的资源：

1.回滚段上用于恢复数据的信息。

2.被程序语句获得的锁。

3.REDO LOG BUFFER中的空间。

4.Oracle为管理上述3种资源的内部花费。

在使用COMMIT时必须要注意到事务的完整性，现实中效率和事务完整性往往是不可兼得。

用WHERE子句替代HAVING子句

避免使用HAVING子句，因为HAVING子句只会在检索出所有的记录之后才对结果集进行过滤。这个处理需要排序，总计等操作，如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接，在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。

用NOT EXISTS替代NOT IN

在子查询中，NOT IN 子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我么可以把它改写成外连接（Outer Joins）或NOT EXISTS（最高效）。

聚合函数有哪些？

AVG()返回某列的平均值。

COUNT()返回某列的行数。

MAX()返回某列的最大值。

MIN()返回某列的最小值。

SUM()返回某列之和。

悲观锁和乐观锁的区别？

悲观锁（Pessimistic Lock）：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里面就用到了很多这种锁机制，比如行锁，表锁，读锁，写锁等，都是在做操作之前先上锁。

乐观锁（Optimistic Lock）：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁。但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于WRITE_CONDITION机制的其实都是提供的乐观锁。

两种锁各有优缺点，不可认为一种好于另一种。像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但是如果经常产生冲突，上层应用会不断地进行retry，这样反而降低了性能，所以这种情况下用悲观锁就比较合适。

 

"让我来成为你一天的开始与结束。"