数据库设计及优化
数据库设计 ，可以使数据库通过健壮的数据库结构高效并且健康的进行工作。 数据库设计原则： 1、熟悉需求。2、开发符合规范的数据库。3、审核数据库设计。 数据库规范 1、命名规范： 1、表名的单数和复数形式要统一。2、对于字段，如果主键是数字类型的，可
数据库设计，可以使数据库通过健壮的数据库结构高效并且健康的进行工作。
数据库设计原则：1、熟悉需求。2、开发符合规范的数据库。3、审核数据库设计。
数据库规范
1、命名规范：1、表名的单数和复数形式要统一。2、对于字段，如果主键是数字类型的，可以考虑使用_N结尾，例如USERID_N；如果是字符类型_C结尾USERNAME_C;3、如果一个单词经常出现，比如USER，则可以考虑以_U结尾，例如，USERID可以表示成ID_U，USERNAME表示为NAME_U。4、如果某一列是日期类型，则使用D_开头，起到强调的作用，例如：D_CREATEDATETIME。
2、明确现实中实体与数据表的关系。3、一张数据表不能既没有主键也没有外键。4、表要符合基本表的特征。
5、必须满足第一第二范式，尽量满足第三范式。6、比较简洁的E-R图。7、符合完整性约束。
基本表的特征：1、原子性，就是基本表中的字段是不可在分解的。2、原始性，基本表中的记录是袁术数据的记录。3、演绎性，由基本表和关系表中的数据可以派生出任何想要的数据。4、稳定性、基本表中的结构是相对稳定的，表中的记录需要长期保存。
第一范式：是对属性的原子性约束，要求属性具有原子性，不可再分。第二范式:记录的唯一性约束，要求记录有唯一标识，即实体的唯一性。第三范式：对字段冗余的约束，即任何字段不能由其他字段派生出来，要求字段没有冗余，一个表中的字段除了和主键有关外，这些字段他们之间不能有关系，也就是说这个表中的字段只能和主键相关，他们之间没有关系。
数据库设计技巧：1、在符合系统需求的前提下，表的个数越少越好，一张表中组合主键字段数越少越好，一个表中的字段越少越好。2、检查各种变化字段。3、避免使用保留字。4、数据库设计的时候多使用删除标记字段。5、避免使用触发器。6、如果发现在重复输入数据，就需要创建新的表和新的关系。7、熟练使用数据库设计工具。8、创建E-R图和数据字典。9、每个表都应该有三个有用的字段：修改记录时间、修改人、修改后的版本。10、对于地址和电话采用多个字段。
SQL查询优化
劣质SQL判断条件：1、运行时间超长。2、引发严重的等待事件。3、不能满足压力测试。4、消耗大量系统资源。
索引是对数据库表中一列或者多列的值进行排序的一种结构。使用索引可以快速访问数据库表中的特定信息。
索引存在的问题：1、索引占用表空间，创建太多索引可能会造成索引冗余。2、索引影响DML性能。
索引使用的条件：1、一个字段里包含大量的值。2、一个字段包含多个空值。3、多个字段经常出现在where查询中。4、表非常大并且查询返回数据量低于总数据的20%。
创建索引的语法：
CREATE INDEX 索引名 ON 表名（列名） TABLESPACE 表空间名
例如：
Select identity_L from depositor where identity_L between 109000 and 110000 --未使用索引，查询值在109000到110000之间的值 CREATE INDEX DPTOR_INDEX ON DEPOSITOR(IDENTITY_L) TABLESPACE DEMO --使用索引查询。
索引类型，按列值是否唯一分为：非唯一索引和唯一索引。按索引列的个数分为单列索引和复合索引。按照索引的物理组织方式分为：B树索引、反向键索引、基于函数的索引、位图索引。
创建复合索引时，表指定的第一个列为主导列。
select *from depositor t where IDENTITY_L>100000 AND ACT>=500 --未使用索引 create index OP_IDACT_INDEX on DEPOSITOR(IDENTITY_L,ACT) tablespace DEMO --使用复合索引
使用复合索引的情况：1、当SQL语句的WHERE自居中用到复合索引的主导列时。2、当某几个列在SQL语句的WHERE子句中经常通过and操作符联合在一起使用，并且这些列合在一起是选择性比各自单个列的选择性要好时。
3、当有几个查询语句都是查询相同的几个列值时。
反向键索引就是将当前列中的值反转后生成的索引。
创建反向键索引的语法：create index索引名 on 表名（列名） reverse tablespace表空间;
基于函数的索引，基于一个或者多个列上的函数或表达式创建的索引。
注意：表达式中不能包含聚合函数（SUM、COUNT、AVG、MIN、MAX）。
基于函数索引的语法：CREATE INDEX索引名 ON 表名（函数（列名）） TABLESPACE表空间;
select lower(firstname) from depositor t; --没有使用基于函数索引的情况。 create index firstname_lower on depositor(lower(firstname)) tablespace prd;
使用基于函数的索引的情况：1、一个表中的字段经常被函数所调用，那么这个字段就可以使用基于函数的索引。
位图索引：
|
值\行 |
1 |
2 |
3 |
4 |
|
张三 |
1 |
0 |
0 |
1 |
|
李四 |
0 |
0 |
0 |
1 |
|
王五 |
0 |
1 |
0 |
1 |
|
小明 |
1 |
0 |
1 |
1 |
整个表就是针对一个列建立出来的位图索引，这个表中的列（1，2，3，4）表示的是对应的索引列的第几行。行代表的是当前行，当前被索引列的值。比如图中第一列1，张三第一列的值是1，表示在当前被索引的第一行的位置中张三这个值时存在的，并且是1。
使用位图索引的情况：主要是处理数据的聚合关系的，用于一个重复数据很多的情况。
位图索引的语法：
CREATE BITMAP INDEX 索引名 ON 表名（列名） TABLESPACE 表空间; select count(*) from depositor t where firstname=’Luke’; create bitmap index fn_bitmap on depositor(firstname) tablespace prd;
表分区：有利于管理非常大的表和索引。当表里面的内容或者整个表的结构非常庞大的时候，就考虑表分区。
表分区的优点：1、提高数据的可用性，当某个分区损坏不会影响整个表结构。2、减少管理负担。3、改善语句性能。
表分区的分区方式：1、区间分区。2、散列分区。3、列表分区。4、组合分区。
区间分区：往往根据一个列值的范围来划分，开发中常常使用日期字段来划分。
语法：
PARTITIONBY RANGE(列名) --RANGE表示分区的方式
（
PARTITION 分区表名 VALUES LESS THAN (值)
tablespace 表空间
）
示例：PART_1这个分区保存早于（不包括）2008年12月31日的数据
create table drawlist( dt_draw date not null)
PARTITIONBY RANGE(dt_draw)
(
PARTITION PART_1 VALUES LESSTHAN(to_date(‘1/1/2009’,’dd/mm/yyyy’))
tablespace DEMO,
PARTITION PART_1 VALUES LESSTHAN(to_date(‘1/1/2011’,’dd/mm/yyyy’))
tablespace DEMO,
PARTITION PART_1 VALUES LESSTHAN(to_date(MAXVALUE)
tablespace DEMO
)
insert into drawlist values(to_date(‘31/12/2008’,’dd/mm/yyyy’));
insert into drawlist values(to_date(‘01/01/2009’,’dd/mm/yyyy’));
insert into drawlist values(to_date(‘06/06/2009’,’dd/mm/yyyy’));
insert into drawlist values(to_date(‘31/12/2010’,’dd/mm/yyyy’));
insert into drawlist values(to_date(‘01/01/2011’,’dd/mm/yyyy’));
insert into drawlist values(to_date(‘04/05/2011’,’dd/mm/yyyy’));
select *from drawlist partition(part_2); 显示结果：2009-1-1；2009-6-6；2010-12-31；
散列分区：会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区中。直到散列算法是将数据随机均匀的分布。为了使数据分布的更均匀，分区表的数目建议为2的幂，也就是1、4、8、16……
散列分区语法：
PARTITION BY HASH(列)（PARTITION 分区表名 tablespace表空间）
createtable hash_table (hash_no INTEGER not null )
PARTITIONBY HASH(hash_no)
(
PARTITION PART_1 tablespace DEMO,
PARTITION PART_2 tablespace DEMO,
PARTITION PART_3 tablespace DEMO,
PARTITION PART_4 tablespace DEMO,
)
对其随机插入500条测试语句
selectcount(*) from hash_table partition(part_1) selectcount(*) from hash_table partition(part_2) selectcount(*) from hash_table partition(part_3) selectcount(*) from hash_table partition(part_4)
输出结果基本均匀分布，分别为：123、116、121、140。
列表分区：可以将数据按照列的值分类。列表分区语法如下：
PARTITIONBY LIST(列)
（
PARTITION 分区表名values(指定值1，指定值2……)
）
示例：根据用户所在地区将用户存储在不同分区中
create table area( code INTEGERnot null )
PARTITION BY LIST(code)
(
PARTITIONPART_1 values(102200,102202,102203),
PARTITIONPART_2 values(164300,164302,164303)
)
tablespace DEMO;
insert into area values(102200);
insert into area values(102203);
insert into area values(164300);
insert into area values(164303);
select * from area partition(part_1)
select * from area partition(part_2)
查询结果：在part_1分区中数据为102200、102202；part_2分区中数据位164300、164303；
组合分区：就是区间分区与散列分区或者区间分区与列表分区的组合。
区间-散列组合分区表语法：首先按照列1的值进行区间分区，然后按照列2散列分区。
PARTITION BY RANGE(列1) SUBPARTITION BY HASH(列2)（
PARTITION分区名 VALUES LESS THAN (值)
TABLESPACE表空间,
)
区间-列表组合分区表语法：先按照列1的值进行区间分区，然后按照列2指定值进行列表分区
PARTITION BY RANGE(列1) SUBPARTITION BY LIST(列2)（
PARTITION 分区名 VALUES LESS THAN (值) TABLESPACE 表空间
（
SUBPARTITION 子分区名 VALUES(列表指定值1……) TABLESPACE 表空间
）
）
组合分区示例：
select *from range_list partition(part_2);
返回值为：DT_DATE 2009-1-1,2010-12-31，2010-11-21;CODE 102200，164300，164300;
select *from range_list subpartition(part2_list);
结果为:DT_DATE 2009-1-1;CODE102200;
分区维护：增加分区、删除分区、截断分区、合并分区。
增加分区的语法：
ALTER TABLE 表名 ADD PARTITION 分区表名 VALUES LESS THAN(值)
例如：
ALTERTABLE DRAWLIST ADD PARTITION PART_4 VALUES LESS THAN(TO_DATE(‘1/1/2012’,’DD/MM/YYYY’)) TABLESPACE DEMO;
删除分区语法：
ALTER TABLE 表名 DROP PARTITION 分区表名
例如：
ALTER TABLE DRAWLIST DROPPARTITION PART_3;
截断分区是删除当前分区中的数据，但不删除当前分区，也不影响其他分区。
语法：
ALTER TABLE 表名 TRUNCATE PARTITION 已存在的分区表
如：
ALTER TABLE DRAWLI ST TRUNCATEPARTITION PART_1;
合并分区是将两个分区的数据合并成一个分区里，注意：高界限的分区不能合并到低界限的分区中。
合并分区的语法：
ALTER TABLE 表名 MERGE PARTITIONS 分区表1，分区表2 INTO PARTITION 分区表2
例如：
ALTER TABLE drawlist MERGE PARTITIONS part_2,part_4 INTO PARTITION part_4;
SQL优化途径：选择合适的Oracle优化器、选择恰当的扫描方式、善于利用共享的SQL语句。
1、选择合适的优化器。CBO优化器：基于成本的优化器，这个成本是指CPU和内存占用率。
在编写SQL时，可以使用CBO对SQL进行优化从而获得更快的响应速度。
优化器使用的模式根据目标而定，CBO的优化模式有四种分别为：1、Rule：基于规则。2、Choose：默认，表或者索引有统计信息，走CBO模式，否则，走RBO模式。3、FirstRow：表中有统计信息时，以最快方式返回查询的前几行，总体减少响应时间，4、AllRows：表中有统计信息时，以最快的方式返回表的所有行，总体提高查询吞吐量。
1、 选择合适的扫描方式。Oracle中，查询数据可以分为：全表扫描、使用ROWID、索引全扫描、快速索引扫描。
全表扫描：就是Oracle读取表中所有的行，并检查每一行是否满足WHERE语句的限制。在数据量大的表中不建议使用全表扫描，效率低下。
使用ROWID：ROWID指出该行所在的数据文件、数据块以及行在该块的位置。是Oracle存取单行数据最快的方式。
索引全扫描：只在CBO模式下有效，这种方式查询的数据必须可以从索引中直接得到。
快速索引扫描：它会扫描索引中所有的数据块，与全索引扫描类似，但这种方式不会对查询出的数据进行排序，这种方式会获得最大吞吐量，并且缩短执行时间。
3、学会利用共享的SQL语句。它存在Oracle的系统全局区中，也就是SGA，可以通过设置SGA的大小，来提高sql的执行效率。