三范式
1NF:原子性,字段不可分;
2NF:唯一性,有主键,非主键字段依赖主键;
3NF:每列都与主键有直接关系,不存在传递依赖,非主键字段不能相互依赖;
例子:
1NF:(关系数据库中create不出这样的表)
2NF:无主键,存在问题:插入异常。
3NF:
学生表:学号, 姓名, 所在学院, 学院电话,关键字"学号";
存在依赖传递: (学号) → (所在学院) → (学院地点, 学院电话)
存在问题:数据冗余
修正:
学生表:学号, 姓名, 所在学院;
学院表:学院, 电话。
注:NoSQL中与关系型数据库其中最大的不同点是不支持1NF,不固定属性,可以为数组。
性能调优
(2)减少不必要的排序
(4)In 和 Exist的优化(如下Q&A)
(5)NOT In替代,A left join B where B.key is null 替代 A where NOT in select key from B
A - 1,2,3
B - 1,2
left join where null / NOT in - 3
Q:In 和 Exist区分
A:in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。
select name from student where name in ('zhang','wang','li','zhao');与
select name from student where name='zhang' or name='li' or
name='wang' or name='zhao'
的结果是相同的。
如果两个表中一个较小,一个是大表,则性能优化是:子查询表大的用exists(减少外循环数),子查询表小的用in(减少hash连接数)。
Q:DML语句一般需要commit,但如果不commit,却在最后加上DDL语句,那DML会commit吗?
A:答案是会的。以MySQL为例:
show variables like 'autocommit'; (or select @@autocommit;)
set autocommit=0; (if 'on', run this)
开session 1:
insert into qqm.test01 values('d','1',1);
insert into qqm.test02 values('a');
select * from qqm.test01;
+-----------+---------------------+-------------+
| PERSON | IDENT | NUM_ACCOUNT |
+-----------+---------------------+-------------+
| c | 1 | 1 |
| d | 1 | 1 |
+-----------+---------------------+-------------+
开session 2:
select * from qqm.test01;
+-----------+---------------------+-------------+
| PERSON | IDENT | NUM_ACCOUNT |
+-----------+---------------------+-------------+
| c | 1 | 1 |
+-----------+---------------------+-------------+
session 1:
truncate qqm.test02;
session 2:
select * from qqm.test01;
+-----------+---------------------+-------------+
| PERSON | IDENT | NUM_ACCOUNT |
+-----------+---------------------+-------------+
| c | 1 | 1 |
| d | 1 | 1 |
+-----------+---------------------+-------------+
Q:大家知道union可以去重(子查询1、2共有的重),但如果子查询1或2内部有重,union又能否去内部重呢?(如同distinct)
A:答案是可以的。例:
select person from test01;
+--------+
| person |
+--------+
| a |
| b |
| b |
| c |
+--------+
select name from test02;
Empty set
select person name from test01 union (select name from test02);
+--------+
| name |
+--------+
| a |
| b |
| c |
+--------+
Q:Oracle中分区表global index和local index异同
A:
Local局部索引:
分区机制和表的分区机制一样。前缀局部索引以分区键开头,反之非前缀索引(不包含partitionkey)。
单分区查询时local性能更好。
注:局部索引只支持分区内的唯一性,无法支持表上的唯一性,因此如果要用局部索引去给表做唯一性约束,则约束中必须要包括分区键列。
Global全局索引:
分区机制和表的分区机制可能不一样。全局索引必须是前缀索引。
全局查询时Global性能更好,但分区维护时不如local方便。
注:表唯一索引一般用global。
1NF:原子性,字段不可分;
2NF:唯一性,有主键,非主键字段依赖主键;
3NF:每列都与主键有直接关系,不存在传递依赖,非主键字段不能相互依赖;
例子:
1NF:(关系数据库中create不出这样的表)
2NF:无主键,存在问题:插入异常。
3NF:
学生表:学号, 姓名, 所在学院, 学院电话,关键字"学号";
存在依赖传递: (学号) → (所在学院) → (学院地点, 学院电话)
存在问题:数据冗余
修正:
学生表:学号, 姓名, 所在学院;
学院表:学院, 电话。
注:NoSQL中与关系型数据库其中最大的不同点是不支持1NF,不固定属性,可以为数组。
性能调优
1、服务器角度,方法:观察CPU占用等排除非SQL的问题。
2、定位哪条SQL问题,方法:AWR(Oracle才有)。
3、SQL结果是否正确,方法:有历史纪录看历史,无则观察测试环境。
4、从业务角度分析SQL性能。
5、从SQL角度分析SQL性能,方法:explain plan,具体调优如下:
(2)减少不必要的排序
(4)In 和 Exist的优化(如下Q&A)
(5)NOT In替代,A left join B where B.key is null 替代 A where NOT in select key from B
A - 1,2,3
B - 1,2
left join where null / NOT in - 3
Q:In 和 Exist区分
A:in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。
select name from student where name in ('zhang','wang','li','zhao');与
select name from student where name='zhang' or name='li' or
name='wang' or name='zhao'
的结果是相同的。
如果两个表中一个较小,一个是大表,则性能优化是:子查询表大的用exists(减少外循环数),子查询表小的用in(减少hash连接数)。
Q:DML语句一般需要commit,但如果不commit,却在最后加上DDL语句,那DML会commit吗?
A:答案是会的。以MySQL为例:
show variables like 'autocommit'; (or select @@autocommit;)
set autocommit=0; (if 'on', run this)
开session 1:
insert into qqm.test01 values('d','1',1);
insert into qqm.test02 values('a');
select * from qqm.test01;
+-----------+---------------------+-------------+
| PERSON | IDENT | NUM_ACCOUNT |
+-----------+---------------------+-------------+
| c | 1 | 1 |
| d | 1 | 1 |
+-----------+---------------------+-------------+
开session 2:
select * from qqm.test01;
+-----------+---------------------+-------------+
| PERSON | IDENT | NUM_ACCOUNT |
+-----------+---------------------+-------------+
| c | 1 | 1 |
+-----------+---------------------+-------------+
session 1:
truncate qqm.test02;
session 2:
select * from qqm.test01;
+-----------+---------------------+-------------+
| PERSON | IDENT | NUM_ACCOUNT |
+-----------+---------------------+-------------+
| c | 1 | 1 |
| d | 1 | 1 |
+-----------+---------------------+-------------+
Q:大家知道union可以去重(子查询1、2共有的重),但如果子查询1或2内部有重,union又能否去内部重呢?(如同distinct)
A:答案是可以的。例:
select person from test01;
+--------+
| person |
+--------+
| a |
| b |
| b |
| c |
+--------+
select name from test02;
Empty set
select person name from test01 union (select name from test02);
+--------+
| name |
+--------+
| a |
| b |
| c |
+--------+
Q:Oracle中分区表global index和local index异同
A:
Local局部索引:
分区机制和表的分区机制一样。前缀局部索引以分区键开头,反之非前缀索引(不包含partitionkey)。
单分区查询时local性能更好。
注:局部索引只支持分区内的唯一性,无法支持表上的唯一性,因此如果要用局部索引去给表做唯一性约束,则约束中必须要包括分区键列。
Global全局索引:
分区机制和表的分区机制可能不一样。全局索引必须是前缀索引。
全局查询时Global性能更好,但分区维护时不如local方便。
注:表唯一索引一般用global。