sql 面试必备知识点

1drop、truncate和delete的区别
delete 是dml 语言  删去要commit才能生效 没用commit之前 可以rollback 
delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录
delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变
DELETE操作不会减少表或索引所占用的空间

runcate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
truncate会删除表中所有记录，并且将重新设置高水线和所有的索引
3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
4、truncatetable不能用于参与了索引视图的表。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。

drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

在速度上，一般来说，drop> truncate > delete。

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

   如果想删除表，当然用drop； 

   如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

   如果和事务有关，或者想触发trigger，还是用delete；

   如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

 1）Truncate 是DDL 语句，DELETE 是DML语句。
2）Truncate 的速度远快于DELETE；
原因是：当执行DELETE操作时所有表数据先被COPY到回滚表空间，数据量不同花费时间长短不一。而TRUNCATE 是直接删除数据不进回滚表空间。
3）delete 数据可以运行Rollback 进行数据回滚。而Truncate 则是永久删除不能回滚。
4) Truncate 操作不会触发表上的delete触发器，而delete 会正常触发。
5) Truncate 语句不能带where 条件意味着只能全部数据删除，而DELETE可带where 条件进行删除数据。
6）Truncate 操作会重置表的高水位线（High Water Mark）,而delete 不会。
7）DELETE可以操作视图，TRUNCATE不能操作视图

集合操作符
Union操作符返回两个查询的结果集的并集，不包含重复值
Union ALL操作符返回两个查询的结果集的并集，包括所有重复⾏。
Minus是从第⼀个查询结果减去第二个查询结果,如果有相交部分就减去相交部分, 不包含重复值。
INTERSECT操作符只返回两个查询的公共行。是交集，不包含重复值。

第一范式：原子性，要求每一列的值不能再拆分了。
2）第二范式：一张表只描述一个实体（若列中有冗余数据，则不满足）防止了插入异常，更新异常 删除异常。
3）第三范式：所有列与主键值直接相关。

在数据库中事务是工作的逻辑单元，一个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。
原子性（Atomicity）
事务必须是原子工作单元，对其进行的数据修改，要么全都执行，要么全都不执行。
以网上银行转账为例，要在A账户上增加1000元，同时要在B账户上减少1000元。要么同时执行，要么都不执行更改，以确保整个事务是一个原子工作单元。
一致性（Consistency）
事务在完成时，必须使所有的数据都保持一致状态，即所有的数据都要发生更改，以保证数据的完整性。在银行转账时，A账户和B账户的数据都要发生更改，以保证数据的完整性。
隔离性（Isolation）
两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时、运行中间某一时刻的数据。比如银行转账操作时，如果有其他的会话也在进行转账，那么当前事务内不能看到其他事务在运行时或运行中间某一时刻的数据。
持久性（Durability）
一旦事务被提交之后，数据库的变化就会被永远保留下来，即使运行数据库软件的机器后来崩溃也是如此。银行转账一旦操作完成，数据就会被永久地保留下来了，即使数据库系统关闭也不会丢失数据。

2.2、事务隔离的级别
为此我们需要通过提供不同类型的“锁”机制针对数据库事务进行不同程度的并发访问控制，由此产生了不同的事务隔离级别：隔离级别（低->高）。SQL、SQL2标准定义了四种隔离级别：

●读未提交（Read Uncommitted）

含义解释：只限制同一数据写事务禁止其他写事务。解决”更新丢失”。（一事务写时禁止其他事务写）

名称解释：可读取未提交数据

所需的锁：排他写锁

●读提交（Read Committed）

含义解释：只限制同一数据写事务禁止其它读写事务。解决”脏读”，以及”更新丢失”。（一事务写时禁止其他事务读写）

名称解释：必须提交以后的数据才能被读取

所需的锁：排他写锁、瞬间共享读锁

●可重复读（Repeatable Read）

含义解释：限制同一数据写事务禁止其他读写事务，读事务禁止其它写事务(允许读)。解决”不可重复读”，以及”更新丢失”和”脏读”。（一事务写时禁止其他事务读写、一事务读时禁止其他事务写）

注意没有解决幻读，解决幻读的方法是增加范围锁（range lock）或者表锁。

名称解释：能够重复读取

所需的锁：排他写锁、共享读锁

●串行化（Serializable）

含义解释：限制所有读写事务都必须串行化实行。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。（一事务写时禁止其他事务读写、一事务读时禁止其他事务读写）

所须的锁：范围锁或表锁

下表是各隔离级别对各种异常的控制能力。
 

　　以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，数据完整性越好
但执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，
所以平时选用何种隔离级别应该根据实际情况。

Mysql数据库与Oracle 数据库有什么区别？
1，应用方面，Mysql是中小型应用的数据库。一般用于个人和中小型企业。Oracle 属于大型数据库，一般用于具有相当规模的企业应用。
2，自动增长的数据类型方面：MySQL有自动增长的数据类型。Oracle没有自动增长的数据类型。需要用触发器建立一个自增序列。
3，group by 用法：Mysql中group by 在SELECT语句中可以随意使用，但在ORACLE中如果查询语句中有组函数，那么其他列必须是组函数处理过的或者是group by子句中的列，否则会报错。
4,引导方面：MySQL中可以用单引号、双引号包起字符串，Oracle中只可以用单引号包起字符串。
 mysql 分页用limit  oracle中则不太一样，它使用rownum定位记录，一般需要使用嵌套子查询
mysql开源免费的，而oracle则是收费的，并且价格非常高
mysql的管理工具较少，在Linux下的管理工具的安装有时需要安装额外的包（phpmyadmin，etc），有一定复杂性。
oracle有多重成熟命令行、图形界面、web管理工具，还有很多第三方的管理工具，管理极其方便高效。oracle支持大并发，大访问量，是OLTP最好的工具
对事务提交
mysql默认是自动提交，可以修改为手动提交
oracle默认不自动提交，需要手动提交，需要在写commit指令或点击commit按钮。 
mysql：默认用户是root，用户下可以创建好多数据库，每个数据库下还有好多表，一般情况下都是使用默认用户，不会创建多个用户；
oracle：创建一个数据库，数据库下有好多用户：sys、system、scott等，不同用户下有好多表，一般情况下只创建一个数据库用。
mysql：int、float、double等数值型，varchar、char字符型，date、datetime、time、year、timestamp等日期型。
oracle：number（数值型），varchar2、varchar、char（字符型），date（日期型）等…

Oracle跟SQL Server 2005的区别？
宏观上：
1). 最大的区别在于平台，oracle可以运行在不同的平台上，sql server只能运行在windows平台上，由于windows平台的稳定性和安全性影响了sql server的稳定性和安全性。
2). oracle使用的脚本语言为PL-SQL，而sql server使用的脚本为T-SQL
3).在SQL Server中，每一个DML语句都具有一个隐式的事务，语句执行结束时事务被自动提交到服务器端，而在Oracle中，当第一条SQL语句开始执行时，一个新的事务自动开始，除非显式地使用COMMIT进行提交，或者是使用ROLLBACK进行回滚，或者是退出某个Oracle工具时，这些事务才结束，否则SQL语句的操作仅在会话级别进行，并没有保存到数据库中。
（4）sql server 是中小型企业用的，oracle是中大型企业用的；oracle跨平台能在linux上运行，sqlserver只能在windows运行，而linux平台比windows平台稳定，所以安全性高；sqlserver操作简便，oracle比较复杂；oracle能把数据存在不同的硬盘上sqlserver不能；oracle能够回滚表空间查询sqlserver不能；oracle支持label security，sqlserver不能；oracle扩展性比较强。

8.Oracle中function和procedure的区别？
可以理解函数是存储过程的一种
存储过程：
（1）一般用于在数据库中完成特定的业务或任务
（2）可以定义返回类型，也可以不定义返回类型
（3）可返回多个参数
函数：
（1）一般用于特定的数据查询或数据转转换处理
（2）申请时必须要定义返回类型，且程序体中必须定义 return 语句
（3）最多返回一个值
（4）不能独立执行，必须作为表达式的一部分调用
注意：sql数据操纵语句中只能调用函数而不能调用存储过程
.Oracle的导入导出有几种方式，有何区别？
1). 使用oracle工具 exp/imp
2). 使用plsql相关工具
方法：1.导入/导出的是二进制的数据
2.plsql导入/导出的是sql语句的文本文件

二、Delete 与Truncate区别

1）Truncate 是DDL 语句，Delete 是DML语句。
2） Truncate 的速度远快于 Delete ；
原因是： 当执行DELETE操作时所有表数据先被COPY到回滚表空间，数据量不同花费时间长短不一。而TRUNCATE 是直接删除数据不进回滚表空间。
3） delete 数据可以运行Rollback 进行数据回滚。而Truncate 则是永久删除不能回滚。
4) Truncate 操作不会触发表上的delete触发器，而delete 会正常触发。
5） Truncate 语句不能带where 条件意味着只能全部数据删除，而DELETE可带where 条件进行删除数据。
6） Truncate 操作会重置表的高水位线（High Water Mark）,而delete 不会。

数据库的三大范式是什么
1） 第一范式：原子件，要求每一列的值不能再拆分了。
2） 第二范式： 一张表只描述一个实体（若列中有冗余数据，则不满足）
3）第三范式： 所有列与主键值直接相关

事务的特性（ACID）是指什么

1）原子性（Atomic）： 事务中的各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败。
2) 一致性（Consistent）： 事务结束后系统状态是一样的。
3）隔离性（Isolated）: 并发执行的事务彼此无法看到对方的中间状态。
4） 持久性（Durable）:事务完成后，即使发生灾难性故障，通过日志和同步备份可以在故障发生后重建数据。

Oracle中function和procedure的区别？

1. 函数可以理解是存储过程的一种
2. 函数可以没有参数,但是一定需要一个返回值；存储过程可以没有参数,不需要返回值
3. 两者都可以通过out参数返回值, 如果需要返回多个参数则建议使用存储过程
4. 在sql数据操纵语句中只能调用函数而不能调用存储过程

 Oracle的导入导出有几种方式，有何区别？
1. 使用oracle工具 exp/imp
2. 使用plsql相关工具
方法1. 导入/导出的是二进制的数据， 2.plsql导入/导出的是sql语句的文本文件

Oracle中有哪几种文件？
数据文件（一般后缀为.dbf或者.ora），日志文件(后缀名.log)，控制文件（后缀名为.ctl）

数据库怎样实现每隔30分钟备份一次？
通过操作系统的定时任务调用脚本导出数据库

解释data block , extent 和segment的区别？
data block 数据块，是oracle最小的逻辑单位，通常oracle从磁盘读写的就是块
extent 区，是由若干个相邻的block组成
segment段，是有一组区组成
tablespace表空间，数据库中数据逻辑存储的地方，一个tablespace可以包含多个数据文件

十五、 解释什么是死锁，如何解决Oracle中的死锁？
简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等在状态，如果是行级锁则不能操作锁定行
解决办法：
1. 查找出被锁的表
2. 杀进程中的会话

0. 怎样创建一个存储过程, 游标在存储过程怎么使用, 有什么好处? 
附：存储过程的一般格式，游标使用参考问题 
1 .使用游标可以执行多个不相关的操作.如果希望当产生了结果集后,对结果集中的数据进行多种不相关的数据操作 
2. 使用游标可以提供脚本的可读性 
3. 使用游标可以建立命令字符串,使用游标可以传送表名,或者把变量传送到参数中,以便建立可以执行的命令字符串. 
但是个人认为游标操作效率不太高，并且使用时要特别小心，使用完后要及时关闭 
存储过程优缺点： 
优点： 
1. 存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。 
2. 可保证数据的安全性和完整性。 
3． 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。 
      通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。 
3. 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 
4. 可以降低网络的通信量, 不需要通过网络来传送很多sql语句到数据库服务器了 
5. 使体现企业规则的运算程序放入数据库服务器中，以便集中控制 
       当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。如果把体现企业规则的 运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化。 
缺点： 
1. 可移植性差 
2. 占用服务器端多的资源，对服务器造成很大的压力 
3. 可读性和可维护性不好 

索引的优缺点： 
优点： 
1. 创建唯一性索引，保证数据库表中每一行数据的唯一性 
2. 大大加快数据的检索速度，这也是创建索引的最主要的原因 
3. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
缺点： 
1. 索引创建在表上，不能创建在视图上 
2. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加 
3. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大 
4. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度 

索引使用原则：
1.索引字段建议建立NOT NULL约束
2.经常与其他表进行连接的表，在连接字段上应该建立索引；
3.经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；
4.如果有两个或者以上的索引，其中有一个唯一性索引，而其它是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
5.至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
6.小表不要建立索引
7.对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引
8.列中有很多空值，但经常查询该列上非空记录时应该建立索引
9.经常进行连接查询的列应该创建索引
10.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）。

索引使用原则：
1.索引字段建议建立NOT NULL约束
2.经常与其他表进行连接的表，在连接字段上应该建立索引；
3.经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；
4.如果有两个或者以上的索引，其中有一个唯一性索引，而其它是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
5.至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
6.小表不要建立索引
7.对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引
8.列中有很多空值，但经常查询该列上非空记录时应该建立索引
9.经常进行连接查询的列应该创建索引
10.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）。
索引的优缺点：

左右连接、内连接、全连接的区别
left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

什么是分区？
就是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
什么时候使用分区表?
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加到新的分区中。
表分区有以下优点：
1.改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4.均衡I/O：可 以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
缺点：已经存在的表没有方法可以直接转化为分区表。
分区表的分类？
分区表：范围分区(range)，列表分区(list)，散列分区(hash),复合分区,交换分区
范围分区
将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用
的，并且分区键经常采用日期。

什么是临时表
临时表就是用来暂时保存临时数据（亦或叫中间数据）的一个数据库对象。
ORACLE临时表是会话或事务级别的，只对当前会话或事务可见。每个会话只能查看和修改自己的数据。
在oracle中，临时表分为会话级别(session)和事务级别(transaction)两种。
会话级的临时表在整个会话期间都存在，直到会话结束；
事务级别的临时表数据在transaction结束后消失，即commit/rollback或结束会话时，会清除临时表数据。
1、事务级临时表on commit delete rows；当COMMIT的时候删除数据(默认情况)。
2、会话级临时表on commit preserve rows；当COMMIT的时候保留数据，当会话结束删除数据。

拉链表适合于数量比较大的表 然后更新数据又比较小  业务又需要保留更新记录 以及查看某个某个历史时间快照信息的场景 设计带来很多便利但设计难度比较大
就是应对维度变化的解决方案  拉链表有开链 和闭链 开链是一条数据生成的时候 还没有对他进行update 毕链代表对生成数据进行修改后的样子 毕链代表该数据中的
信息已经失效

数仓分层的目的  1减少数据重复计算 2模块之间能够复用 3 数据功能的解耦 4合理的分层能够快速的相应业务的需求  5 提高开发效率6 减少业务报表的生成时间

数据血缘 数据地图数据dna 描述的都是同一个意思  
各数据之间的关联关系

HIVE 是基于Hadoop的数据仓库，
能够把结构化的数据映射成一张表 并提供类sql查询
将sql语句转化为MapReduce程序
hive数据存储在hdfs中 分析数据的底层实现是在MapReduce上 执行程序运行在yarn上面

hive 优点 
1操作接口采用类sql语法提供快速开发的能力
2避免了去写mapreduce程序节省了开发人员的学习成本
3hive 的执行延迟比较高 所以hive常用于数据分析 和实时性要求不高的场景
4hive 的执行延迟度比较高，适合于处理大数据 ，对小数据没有优势。
5hive 支持用户自定义函数 用户可以根据自己的需求来实现自己的函数。

hive的缺点  hql表达能力有限
1迭代式算法无法表达
2数据挖掘方面不擅长由于MapReduce数据处理流程的限制，效率更高的算法却无法实现
hive的效率比较低
1hive自动生成的MapReduce作业通常情况下不够智能化
2 hive调优比较困难粒度较粗。

Hive QL语句是怎么执行的？

整个过程的执行步骤如下：

 (1) 解释器完成词法、语法和语义的分析以及中间代码生成，最终转换成抽象语法树；

 (2) 编译器将语法树编译为逻辑执行计划；

 (3) 逻辑层优化器对逻辑执行计划进行优化，由于Hive最终生成的MapReduce任务中，Map阶段和Reduce阶段均由OperatorTree组成，所以大部分逻辑层优化器通过变换OperatorTree，合并操作符，达到减少MapReduce Job和减少shuffle数据量的目的；

 (4) 物理层优化器进行MapReduce任务的变换，生成最终的物理执行计划；

 (5) 执行器调用底层的运行框架执行最终的物理执行计划。
内部表和外部表的区别吗？

     创建表时：创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表用exteral，仅记录数据所在的路径， 不对数据的位置做任何改变。
   内部表数据由Hive自身管理，外部表数据由HDFS管理；删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除。
Hive的构成包括哪些部分？

用户接口层：常用的三个分别是CLI，JDBC/ODBC 和 WEBUI。其中最常用的是CLI，CLI启动的时候，会同时启动一个Hive副本。JDBC/ODBC是Hive的客户端，用户通过客户端连接至Hive Server。在启动客户端模式的时候，需要指出Hive Server所在节点，并且在该节点启动Hive Server。WEBUI是通过浏览器访问Hive。
元数据存储：Hive将元数据存储在RDBMS中，有三种模式可以连接到数据库，分别是内嵌式元存储服务器、本地元存储服务器、远程元存储服务器。
Driver(Compiler/Optimizer/Executor)

Driver完成HQL查询语句的词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS上，并由MapReduce调用执行。

 分区表和分桶表的区别？

分区表，Hive 数据表可以根据某些字段进行分区操作，细化数据管理，让部分查询更快，不同分区对应不同的目录；

分桶表：表和分区也可以进一步被划分为桶，分桶是相对分区进行更细粒度的划分。分桶将整个数据内容按照某列属性值的hash值进行区分，不同的桶对应不同的文件。
Hive的作用有哪些

  可以将结构化的数据文件映射成一张表，并提供类SQL查询功能，方便非java开发人员对hdfs上的数据做 MapReduce 操作；
可以对数据提取转化加载(ETL)

构建数据仓

5）Hive的使用场景

 即席查询：利用CLI或者类似Hue之类的工具，可以对Hive中的数据做即席查询，如果底层的引擎使用的是MapReduce耗时会很久，可以替换成Tez或者Spark；
离线的数据分析：通过执行定时调度或者脚本去执行HQL语句，并将结果保存；
构建数仓时用于组织管理数据库和表。
数据倾斜
    数据倾斜不仅在Hive面试中会被问到，其他只要涉及到大规模程序开发的组件都会问到数据倾斜方面的问题，因为这是在实际工作中经常会出现的问题，如何去避免和解决出现的数据倾斜问题是衡量你代码水平高低的尺子。

1）什么是数据倾斜？

    数据倾斜就是数据的分布不平衡，某些地方特别多，某些地方又特别少，导致在处理数据的时候，有些很快就处理完了，而有些又迟迟未能处理完，导致整体任务最终迟迟无法完成，这种现象就是数据倾斜。

2）你知道发生数据倾斜的原因吗？

发生数据倾斜的原因有很多，大致可以归为：

1）key分布不均匀；

2）数据本身的特性，原本按照日期进行分区，如果在特定日期数据量剧增，就有可能造成倾斜；

3）建表时考虑不周，分区设置不合理或者过少；

4）某些 HQL 语句本身就容易产生数据倾斜，如 join。

、HIVE优化
1）谈谈如何对join操作进行优化？

join优化是个复杂的问题，可以从以下几点进行优化：

1）小表前置

大小表在join的时候，应该将小表放在前面，Hive在解析带join的SQL语句时，会默认将最后一个表作为大表，将前面的表作为小表并试图将它们读进内存。如果表顺序写反，大表在前面，可能会引发OOM。

2）key值相同

多表join的时候尽量使用相同的key来关联，这样会将会将多个join合并为一个MR job来处理。

3）利用map join特性

map join特别适合大小表join的情况。Hive会将大表和小表在map端直接完成join过程，消灭reduce，效率很高。Hive 0.8版本之前，需要加上map join的暗示，以显式启用map join特性，具体做法是在select语句后面增加/*+mapjoin(需要广播的较小表)*/。

map join的配置项是hive.auto.convert.join，默认值true；还可以控制map join启用的条件，hive.mapjoin.smalltable.filesize，当较小表大小小于该值就会启用map join，默认值25MB。

2）对于空值或者无意义的值引发的数据倾斜，该怎么处理呢？

   这在写程序的时候要考虑清楚，这些异常值的过滤会不会影响计算结果，如果影响那就不能直接过滤掉，可以将这些异常的key用随机方式打散，例如将用户ID为null的记录随机改为负值。

3）如何调整mapper数？

mapper数量与输入文件的split数息息相关，可以通过设置相关参数来调整mapper数。

1）可以直接通过参数mapred.map.tasks（默认值2）来设定mapper数的期望值，但它不一定是最终mapper数；

2）输入文件的总大小为total_input_size。HDFS中，一个块的大小由参数dfs.block.size指定，默认值64MB或128MB。所以得出来的默认mapper数就是：

default_mapper_num = total_input_size / dfs.block.size，但是它也不一定是最终的mapper数；

3）设置参数mapred.min.split.size（默认值1B）和mapred.max.split.size（默认值64MB）分别用来指定split的最小和最大值。那么split大小和split数计算规则是：

split_size = MAX(mapred.min.split.size, MIN(mapred.max.split.size, dfs.block.size))；

split_num = total_input_size / split_size。

4)最终得出mapper数：

mapper_num = MIN(split_num, MAX(default_mapper_num, mapred.map.tasks))。

其中可变的参数有：mapred.map.tasks、dfs.block.size（不会为了一个程序去修改，但是也算是一个可变参数）、mapred.min.split.size、mapred.max.split.size，通过调整他们来实现，mapper数的变化。

4）如何调整reducer数？

利用参数mapred.reduce.tasks可以直接设定reducer数量，不像mapper一样是期望值。如果不设这个参数的话，Hive就会自行推测，逻辑如下：

1）参数hive.exec.reducers.bytes.per.reducer用来设定每个reducer能够处理的最大数据量。

2）参数hive.exec.reducers.max用来设定每个job的最大reducer数量。

3）reducer数：

reducer_num = MIN(total_input_size / reducers.bytes.per.reducer, reducers.max)。

reducer数量决定了输出文件的数量。如果reducer数太多，会产生大量小文件，对HDFS造成压力。如果reducer数太少，每个reducer要处理很多数据，容易拖慢执行时间也有可能造成OOM。

5）什么时候又需要合并文件？如何合并小文件？

当有很多小文件的时候没需要合并小文件，可以在输入阶段合并，也可以在输出阶段合并。

1）输入阶段合并

要想文件自动合并，需要更改Hive的输入文件格式，通过参数hive.input.format来更改，默认值是org.apache.hadoop.hive.ql.io.HiveInputFormat，需要改成org.apache.hadoop.hive.ql.io.CombineHiveInputFormat。还需要设置mapred.min.split.size.per.node和mapred.min.split.size.per.rack这两个参数，他们的含义是单节点和单机架上的最小split大小。设置完后，如果发现有split大小小于这两个值（默认都是100MB），则会进行合并。

2）输出阶段合并

设置hive.merge.mapfiles为true可以将map-only任务的输出合并；

设置hive.merge.mapredfiles为true可以将map-reduce任务的输出合并。另外，设置hive.merge.size.smallfiles.avgsize可以指定所有输出文件大小的均值阈值，一旦低于这个阈值，就会启动一个任务来进行合并

cut
cut的工作就是“剪”，具体的说就是在文件中负责剪切数据用的
Sed
sed是一种流编辑器，
awk
一个强大的文本分析工具，
sort
sort命令是在Linux里非常有用，它将文件进行排序，并将排序结果标准输出。

Hive排序
全局排序（Order by）
Order By：全局排序，一个Reducer
ASC: 升序（默认）
DESC: 降序
内部排序(Sort by)
每个 Reducer 内部进行排序，对全局结果集来说不是排序。
分区排序 （Distribute By）
类似 MR 中 partition ，进行分区，结合 sort by 使用。
注意： Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by的效果。
Cluster by
当distribute by 和sorts by 字段相同时，可以使用cluster by 方式。
cluster by 除了具有distribute by 的功能外还兼具sort by 的功能。但是排序只能是升序排序，不能指定排序规则为ASC 或者DESC。
以下两种写法等价:
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意：按照部门编号分区，不一定就是固定死的数值，可以是20 号和30 号部门分到一个分区里面去。

因为truncate table在功能上与不带WHERE子句的DELETE语句相同：
二者均删除表中的全部行。但TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。
TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

delete
delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
delete语句不影响表所占用的extent，高水线（high watermark）保持原位置不变。
drop
drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。
truncate
truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
4、truncatetable不能用于参与了索引视图的表。
适用场景
TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。
truncate与不带where的delete：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
在没有备份情况下，谨慎使用drop与truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
一般而言，执行速度：drop > truncate > delete
Hive中的操作(drop、truncate)
hive是数据仓库，存储格式是文本，不支持行级别的更新删除和插入。
所以在hive中，只有drop table、truncate操作，没有delete操作
hive 语句执行顺序
from … where … select … group by … having … order by … limit …

、key分布不均衡

二、业务问题后者业务数据本身的问题，某些数据比较集中

三、建表的时候考虑不周

四、某些sql语句本身就有数据倾斜，例如：

（1）大表join小表：其实小表的key集中，分发到某一个或者几个reduce上的数据远远高于平均值

（2）大表join大表：空值或无意义值：如果缺失的项很多，在做join时这些空值就会非常集中，拖累进度。

（3）group by： group by的时候维度过小，某值的数量过多，处理某值的reduce非常耗时间。

（4）Count distinct：某特殊值过多，处理此特殊值的reduce耗时。

3、Hive数据倾斜解决
【参数调节】 hive.map.aggr = true

Map端部分聚合。

有数据倾斜的时候进行负载均衡，当选项设定为true，生成的查询计划会有两个MRJob。第一个MRJob中，Map的输出结果集合会随机分不到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同于Group By Key 有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MRJob再根据预处理的数据结果按照Group By Key分不到Reduce中(这个过程可以保证相同的Group By Key被分布到同一个Reduce中)，最后完成最终的聚合操作。

【SQL调整】

1）如何join：关于驱动表的选取，选用join key分布最均匀的表作为驱动表，做好列裁剪和filter操作，以达到两表做join的时候，数据量相对变小的效果。

2）大小表join的时候：使用map join 让小的维度表先进内存，在map端完成reduce。效率很高。

3）大表join大表的时候：把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后不影响最终的结果。

4）count distinct 大量相同特殊值，将这些值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1即可。如果还有其他的计算，需要进行group by，可以先将那些值为空的记录单独处理，再和其他计算结果进行 union。

5）group by 维度过小的时候：采用sum() group by 的方法来替换count(distinct)完成计算。

6）单独处理倾斜key：一般来讲倾斜的key都很少，我们可以将它们抽样出来，对应的行单独存入临时表中，然后打上随机数前缀，最后再进行聚合。或者是先对key做一层hash，先将数据随机打散让它的并行度变大，再汇集。其实办法一样。