记录遇到的问题
- Hive sql中left semi join用法
- in和exists用法和区别
- drop、truncate和delete的用法和区别
Hive sql中left semi join用法
left semi join叫做左半连接,是in/exists子查询的一种高效实现方式。
select * from tab1 where size in (select size from tab2)
等价于
select * from tab1 left semi join tab2 on tab1.size = tab2.size
特点:
- left semi join 是只传递表的 join key 给 map 阶段,因此left semi join 中最后 select 的结果只会出现左表中的记录。
- 由于连接的右表不会出现,因此不能通过where条件过滤右表记录,只能在on后面加上条件对右表进行过滤。
- 连接时,右表有重复的连接key值,left semi join后产生结果不重复。
in和exists用法和区别
- in关键字:先执行内表查询,再将内表结果与外表匹配,查询出匹配成功的记录。由于内表无论多大,查询都不可避免,所以一般来说in适用于内表较小的,外表较大的查询场景。
select * from A where id in (select id from B)
等价于
① 先执行select id from B
② 再执行select id from A where A.id = B.id
- exists关键字:先执行外表查询,将外表查询结果放入子查询中验证,为true则输出,为false则不输出。
select * from A where exists (select 1 from B where B.id = A.id)
等价于
① 先执行外层查询select id from A
② 再执行select id from B where B.id = A.id
一般来说exists适用外表小,内表大的查询场景。代码中的select 1 还是select *都一样,执行时会忽略select.
- 总结:
1、exists 会针对子查询的表使用索引,not exists 会对主子查询都会使用索引。in 与子查询一起使用的时候,只能针对主查询使用索引,not in 则不会使用任何索引。
2、如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
3、表中有null记录,in/exists查询不执行,in (select id from B)查询出的id不为null,exists (select 1 from B where B.id = A.id)判断null= null时不为true,因此也不会输出null记录。
drop、truncate和delete的用法和区别
- 语法
drop table 表名
truncate table 表名
delete from 表名 where 列名 = 值eg:drop table A、truncate table B、delete from C where id = 1
- 三者区别与联系
1、drop是删除表数据、表结构定义,同时删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。执行drop操作后,无法再执行插入数据操作,只有重新创建。
truncate只是清除表的所有数据,保留表结构,但其无法删除某行数据,只能全部清楚,执行truncate之后,可以再执行插入数据操作使表再次有数据。
delete结合where可以删除指定行记录,并且同时将该行的删除操作作为事务记录在日志中保存,以便进行回滚操作。不带where等同truncate,只删除表所有数据,但保留表结构。
2、对于外键约束引用的表,不能使用truncate,而使用delete不带where的语句,由于truncate table记录在日志中,所以不能激活触发器。
3、delete是数据库操作语言(dml),执行操作会记录在rollback segement上,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发。而truncate和drop是数据库定义语言(ddl),它们的操作立即生效,原数据不放到 rollback segment 中,不能回滚,操作不触发 trigger。
4、执行速度,一般来说: drop> truncate > delete。
5、在 InnoDB 引擎中,truncate 可以重置自增列,而 delete 不能重置自增列。
6、truncate只能对table,delete可以是table,view,synonym。