oracle 结果集已耗尽解决方法_数据库面试题

sql优化有哪些?如何创建索引?创建索引的原则?

索引的优缺点?

sql的优化有:

尽量避免使用 select * ，返回无用的字段会降低效率。优化方式：只能使用具体的字段代替 select 具体字段，只返回使用到的字段。

尽量避免使用in和not in，会导致数据库引擎放弃索引进行全表扫描。优化方式：如果是连续数值，可以用betwween代替，如果是子查询，可以用exists代替。

尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。优化方式:尽量在字段后面使用模糊查询。

尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。优化方式:可以给字段添加默认值0，对0值进行判断。

如何创建索引：

数据库索引，是数据库管理系统中一个排序的数据结构，索引的实现通常使用B树及其变种B+树。

创建索引有两种:

直接创建索引，例如使用CREATE INDEX语句或者使用创建索引向导。

间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索引。

索引的作用:

协助快速查询、更新数据库表中数据。

为表设置索引要付出代价的：

一是增加了数据库的存储空间；二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

创建索引可以大大提高系统的性能(优点):

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。增加索引也有许多不利的方面(缺点):

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

创建索引的原则：

最左前缀匹配原则(一直向右匹配直到遇到范围查询就停止匹配)；

=和in可以乱序(建立索引是可以任意顺序的，mysql的查询优化器会帮你优化成索引可以识别的形式)；

尽量选择区分度高的列作为索引(区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，)；

索引列不能参与计算(保持列“干净”，)；

尽量的扩展索引，不要新建索引(如a->(a，b)只需要修改原来的索引)；

选择唯一性索引(唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。)；

为经常需要排序、分组和联合操作的字段建立索引；

为常作为查询条件的字段建立索引；

限制索引的数目；

尽量使用数据量少的索引；

尽量使用前缀来索引；

删除不再使用或者很少使用的索引。

sql语句关键词的执行顺序？

FROM 子句, 组装来自不同数据源的数据；

WHERE 子句, 基于指定的条件对记录进行筛选

GROUP BY 子句, 将数据划分为多个分组

使用聚合函数进行计算

使用 HAVING 子句筛选分组

计算所有的表达式

使用 ORDER BY 对结果集进行排序

即：from—>where—>group by—>having—>计算所有的表达式—>order by—>select输出

sql如何去重？

总的思路就是先找出表中重复数据中的一条数据，插入临时表中，删除所有的重复数据，然后再将临时表中的数据插入表中。

实现：

重复数据完全一样，使用distinct；

id列不同,id类型为int,自增字段,使用聚合函数max或其他；

id列不同,id类型为uniqueidentifier；使用row_number() over()和partition by给每一组添加行号；将行号=1的数据插入临时表中。

内连接和外连接的区别

自然连接：是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。

sql语句：Select …… from 表1 natural join 表2

内连接：基本与自然连接相同，不同之处在于自然连接要求是同名属性列的比较，而内连接则不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。

sql语句：Select …… from 表1 inner join 表 2 on 表1.A=表2.E

左外连接：是在两表进行自然连接，左边表数据行全部保留，右边表保留符合连接条件的行。

sql语句：Select …… from 表1 left outer join 表2 on 表1.C=表2.C

右外连接：是在两表进行自然连接，右边表数据行全部保留，左边表保留符合连接条件的行。

Select …… from 表1 rignt outer join 表2 on 表1.C=表2.C

Java中如何使用redis？redis支持的数据类型及各种数据类型的使用场景？redis如何解决数据过期？

如何使用redis：

redis的安装(windows)；启动redis，默认端口6379；连接redis输入redis-cli.exe -h 127.0.0.1 -p 6379

java中利用jedis连接redis

spring集成redis：

引入spring-data-redis.jar包；

redis.properties 配置文件；

spring-redis配置文件(此处包含了数据库和mybatis的配置)；

redis工具类(此处try catch为防止redis宕机等问题时 方法可以继续执行)；

将spring-redis.xml包含到web.xml中；

在需要redis的业务类中注入redisUtil。

redis支持的数据类型及各种数据类型的使用场景：

redis如何解决数据过期：

Redis 提供了两种的方式，用于删除过期的数据：

定期删除：Redis 默认 100ms 随即抽取部分设置过期时间的 key，过期了就删除。优点是避免长时间的在扫描过期 key，缺点是有些过期 key 无法被删除。

惰性删除：如果查询了某个过期 key，但定期删除没有删除掉，那就将其删除了。key 没过期就正常返回。

数据库表的设计注意事项有哪些？三大范式的了解？

数据库设计的注意事项：

字段的原子性：保证每列的原子性，不可分解，能用一个字段表达清楚的绝不使用第二个字段。

主键设计：主键不要与业务逻辑有所关联，最好是毫无意义的一串独立不重复的数字。

字段使用次数：对于频繁修改的字段(一般是指状态类字段)最好用独立的数字或者单个字母去表示，不用使用汉字或长字符的英文。

字段长度：建表的时候，字段长度尽量要比实际业务的字段大3-5个字段左右，最好是2的n次方幂值。

关于外键：尽量不要建立外键，保证每个表的独立性。

动静分离：最好做好静态表和动态表的分离。

关于code的值：使用数字码或者字母去代替实际的名字，也就是尽量把name转换为code。

关于null的值：尽量不要有null值，有null值的话，数据库在进行索引的时候查询的时间更久，从而浪费更多的时间！可以在建表的时候设置一个默认值！

关于引擎的选择：myisam的实际查询速度要比innodb快，因为它不扫面全表，但是myisam不支持事务，没办法保证数据的Acid。

资源存储：数据库不要存储任何资源文件。

与主键相关：根据数据库设计三大范式，尽量保证列数据和主键直接相关而不是间接相关

关系映射：多对一或者一对多的关系,关联一张表最好通过id去建立关系。

预留字段：在设计一张表的时候应该预制一个空白字段，用于以后的扩展。

留下单一字段确定是否可用：通过一个单一字段去控制表是否可用。

删除字段：数据库是禁止使用delete命令的,一般都不会真正删除数据，都是采用改状态的方式，设置state字段，通过修改状态赋予它是否有效的逻辑含义！

三大范式的了解：

第一范式(1NF):确保每一列的原子性

数据表中的每一列都是最小的不可分割的单元

第二范式(2NF)：表中的记录是唯一的

表中的数据是可以通过主键来区分的

第三范式(3NF)：表中数据不要有冗余

在一个表中不要出现其他表中除了关键字段(主键)的其他字段，用外键进行关联

存储过程的了解和使用？

存储过程：就是作为可执行对象存放在数据库中的一个或多个SQL命令。通俗来讲：存储过程其实就是能完成一定操作的一组SQL语句。

优点：

存储过程可封装，并隐藏复杂的商业逻辑。

存储过程可以回传值，并可以接受参数。

存储过程无法使用select指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

存储过程可以用在数据检验，强制实行商业逻辑等。

缺点：

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

存储过程的性能调校和撰写，受限于各种数据库系统。

使用：

创建存储过程保存在数据库的数据字典中。

查询所有存储过程状态

查看对应数据库下所有存储过程状态

mysql存储过程用call和过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数调用。

数据库如何实现分页？

SQL Server

在分页查询上，我感觉SQL Server比较费劲，没有一个专门的分页的语句，靠的是一种巧妙的方法实现分页查询。

MySQL

MySQL有个专门针对查询出一段数据的语句limit，使用起来非常的方便。

Oracle

Oracle中有个rownum，其含义更加明显，就是第几行的意思，这样我们就可以通过where条件来进行分段查询了。

百万级量的数据分页查询如何优化？

--方法1: 直接使用数据库提供的SQL语句

--语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N

--适应场景: 适用于数据量较少的情况(元组百/千级)

--原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

--方法2: 建立主键或唯一索引, 利用索引(假设每页10条)

--语句样式: MySQL中,可用如下方法: SELECT _FROM 表名称 WHERE id_pk > (pageNum_10) LIMIT M

--适应场景: 适用于数据量多的情况(元组数上万)

--原因: 索引扫描,速度会很快. 有朋友提出: 因为数据查询出来并不是按照pk_id排序的，所以会有漏掉数据的情况，只能方法3

--方法3: 基于索引再排序

--语句样式: MySQL中,可用如下方法: SELECT _FROM 表名称 WHERE id_pk > (pageNum_10) ORDER BY id_pk ASC LIMIT M

--适应场景: 适用于数据量多的情况(元组数上万). 最好ORDER BY后的列对象是主键或唯一所以,使得ORDERBY操作能利用索引被消除但结果集是稳定的(稳定的含义,参见方法1)

--原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有ASC没有DESC(DESC是假的,未来会做真正的DESC,期待...).

--方法4: 基于索引使用prepare(第一个问号表示pageNum，第二个？表示每页元组数)

--语句样式: MySQL中,可用如下方法: PREPARE stmt_name FROM SELECT FROM 表名称 WHERE id_pk > (？ ？) ORDER BY id_pk ASC LIMIT M

--适应场景: 大数据量

--原因: 索引扫描,速度会很快. prepare语句又比一般的查询语句快一点。

--方法5: 利用MySQL支持ORDER操作可以利用索引快速定位部分元组,避免全表扫描

比如: 读第1000到1019行元组(pk是主键/唯一键).

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

--方法6: 利用"子查询/连接+索引"快速定位元组的位置,然后再读取元组. 道理同方法5

如(id是主键/唯一键,蓝色字体时变量):

数据库的乐观锁和悲观锁的理解和使用？

悲观锁，就是对数据的冲突采取一种悲观的态度，也就是说假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住。(数据锁定：数据将暂时不会得到修改)

乐观锁，认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息。让用户决定如何去做。

使用：

悲观锁通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select … for update来实现悲观锁。

乐观锁不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。

数据库中字符串和日期的相互转换？

Oracle

时间转字符串 to_char(date,format)

select to_char(sysdata,'YYYY"年"MM"月"DD"日"') 时间转字符串 from dual

字符串转时间 to_date(str,format)

select to_date('2019-10-25 17:15:20','yyyy-MM-dd HH24:mi:ss')

字符串转时间 from dual

select to_date('2019-10-25 17:15:20','yyyy-MM-dd HH24:mi:ss') 字符串转时间 from dual

MySQL

MySQL内置函数，在MySQL里面利用str_to_date() 把字符串转换为日期

示例：分隔符一致，年月日要一致

字符串转日期

select str_to_date('2019-10-25 15:43:28','%Y-%m-%d %H:%i:%s');

日期转字符串

select DATE_FORMAT(SYSDATE(),'%Y年%m月%d日') MySQL日期转字符串 from DUAL；

union和unionAll区别？

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；---在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；---如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

MySQL的存储引擎有哪些？

InnoDB,MyISAM,Memory,Merge,Archive,Federate,CSV,BLACKHOLE

事务隔离级别有哪些？mysql和oracle默认的隔离级别是什么？

事务的四大特性：

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。

一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

脏读？幻读？不可重复读？

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

SQL如何行转列和列转行？

行转列：

case when 以及 sum(max也可以)

列转行：

使用union 和 case when 以及concat来完成

如何查看sql的执行计划？

set statistics profile on

explain

oracle中的分析函数有哪些？

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ；

first_value()与last_value()：求最值对应的其他属性

rank(),dense_rank()与row_number()：求排序

lag()与lead()：求之前或之后的第N行

rollup()与cube()：排列组合分组

max(),min(),sun()与avg()：求移动的最值总和与平均值

数据库中除了聚合函数之外还有哪些常用的函数？oracle数据库的merge()函数的作用和使用？

oracle数据库merge()函数的作用和使用：

通常我们对数据库数据进行插入的时候，会判断数据是否已经存在，如果存在就修改，不存在就插入，一般我们会写两个sql，一个insert一个update,那么其实oracle中存在merge函数，可以一次性解决这个问题

SQL中drop、truncate、delete的区别？

MySQL中如何忽略表名的大小写？

mysql在windows系统下安装好后，默认是对表名大小写不敏感的。

在linux下，一些系统需要手动设置。 用root登录，打开并修改 /etc/my.cnf；在[mysqld]节点下，加入一行： lower_case_table_names=1。重启mysql服务systemctl restart mysqld

having和where的区别？

Where过滤子句是在查询过程中对表中数据的过滤条件，不允许使用聚合函数作为过滤条件，原因在于时机不对。聚合函数是对表中数据查询后的结果集进行统计处理的，两者的执行时机不一致。

Having过滤子句是对查询结果集进行过滤的，可以在该子句后使用聚合函数，因为时机相同，聚合函数是处理结果集的，having子句又是过滤结果集的，可以在一起使用，另外having不能单独使用，只能跟在group by分组子句后面使用。

游标的作用和使用？

SQL的游标是一种临时的数据库对象，即可以用来存放在数据库表中的数据行副本，也可以指向存储在数据库中的数据行的指针。游标提供了在逐行的基础上操作表中数据的方法。

游标的一个常见用途就是保存查询结果，以便以后使用。

游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

使用：

%FOUND--判断游标中是否还有数据，若有，返回true，否则，返回false。

%NOTFOUND--与%FOUND 相反

%ISOPEN--判断游标是否为打开状态

%ROWCOUNT--记录已从游标中取出的记录数

如何使用数据库中的定时器？触发器？定时任务？

Oracle中如何实现递归查询？

start with 条件一 connect by prior 条件二 where 条件三

第一种:start with 子节点ID=’...’connect by prior 子节点ID=父节点ID---按照条件对条件(包括自己)及其子节点进行递归查询,查询结果自己所有后代节点(包括自己)

第二种:start with 子节点ID=’...’connect by 子节点ID = prior 父节点ID----按照条件对于条件(包括自己)及其父节点进行递归查询,查询结果自己所有的前代节点(包括自己)

第三种: start with 父节点ID=’...’connect by prior 子节点ID=父节点ID---按照条对条件(不包括自己)子节点进行递归查询,查询结果自己所有的后代节点,(不包括自己)

第四种: start with 父节点ID=‘...‘ connect by 子节点ID = prior 父节点ID---按照条件对条件(包括自己)的第一代孩子们及其父节点进行递归查询,查询结果是自己的第一代后节点,和所有的前代节点(包括自己)

如果有where条件,(4)执行顺序为先执行start with connect by prior,然后再按照where条件进过滤

高并发下如何保证修改数据安全

使用Synchronized解决，给生成ID的代码加上同步代码块，成功解决问题；

作用是：同一时刻，只有一个线程可以执行该代码块

使用Lock锁解决问题：给生成ID的代码加上Lock锁，成功解决问题；

悲观锁---在修改数据的时候,采用锁定状态,排斥外部请求的修改,遇到加锁的状态,就必须等待

弊端: 在高并发下,每个请求都需要等待’锁’,某些线程可能永远都没有机会抢到这个锁,请求就会死在那里,这种请求会很多,瞬间增系统的平均响应时间,结果时可用链接数被耗尽,系统陷入异常

FIFO队列----采用FIFO(First Input First Output，先进先出),这样就不会导致某些请求永远获取不到锁

弊端:请求很多,很有可能一瞬间将队列内存”撑爆”,系统陷入到异常状态,或者设计一个极大的内存队列,但是系统处理完一个队列,内请求的速度根本无法和疯狂涌入队列中的数目相比,也就是说,队列内的请求会越积累越多,最终WEB系统平均响应时候还是会大幅下降，系统还是陷入异常。

乐观锁---相对于”悲观锁”采用更为宽松的加锁机制,大都是采用带版本号更新,实现就是,这个数据所有请求都有资格去修改,但会获得一个该数据的版本号,只有版本号符合才能更新成功,其他的返回抢购失败,这样就不用了考虑队列的问题,会增大CPU的计算开销,

Oracle中如何实现主键自增？

Oracle没有这个”auto_increment”属性，所以它没法像MySQL般在表内定义自增主键。但是，Oracle里的序列(SEQUENCE)，可间接实现自增主键的作用。也可以创建触发器实现自增。

序列(Sequence)，又叫序列生成器，用于提供一系列的数字，开发人员使用序列生成唯一键。每次访问序列，序列按照一定的规律增加或者减少。

序列的定义存储在SYSTEM表空间中，序列不像表，它不会占用磁盘空间。

序列独立于事务，每次事务的提交和回滚都不会影响序列。

Delete误删数据没有备份怎么恢复？

flashback query闪回查询

--尝试使用Oracle 10g 以后的flashback Query特性 闪回查询可以查询若干时间之前的数据

logmnr 日志挖掘

--使用logminer 日志挖掘 把delete的redo挖出来看有没有 对应的undo回滚SQL可用

使用ORACLE PRM-DUL 工具

--Oracle PRM-DUL 工具可以恢复 数据库中表上 被删除的记录。

Oracle死锁如何处理？