数据库设计及优化

数据库设计，可以使数据库通过健壮的数据库结构高效并且健康的进行工作。

数据库设计原则：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 between109000 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 LESSTHAN(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的执行效率。