數(shù)據(jù)庫(kù)設(shè)計(jì)及優(yōu)化

數(shù)據(jù)庫(kù)設(shè)計(jì) ，可以使數(shù)據(jù)庫(kù)通過(guò)健壯的數(shù)據(jù)庫(kù)結(jié)構(gòu)高效并且健康的進(jìn)行工作。 數(shù)據(jù)庫(kù)設(shè)計(jì)原則： 1、熟悉需求。2、開(kāi)發(fā)符合規(guī)范的數(shù)據(jù)庫(kù)。3、審核數(shù)據(jù)庫(kù)設(shè)計(jì)。 數(shù)據(jù)庫(kù)規(guī)范 1、命名規(guī)范： 1、表名的單數(shù)和復(fù)數(shù)形式要統(tǒng)一。2、對(duì)于字段，如果主鍵是數(shù)字類(lèi)型的，可

數(shù)據(jù)庫(kù)設(shè)計(jì)，可以使數(shù)據(jù)庫(kù)通過(guò)健壯的數(shù)據(jù)庫(kù)結(jié)構(gòu)高效并且健康的進(jìn)行工作。

數(shù)據(jù)庫(kù)設(shè)計(jì)原則：1、熟悉需求。2、開(kāi)發(fā)符合規(guī)范的數(shù)據(jù)庫(kù)。3、審核數(shù)據(jù)庫(kù)設(shè)計(jì)。

數(shù)據(jù)庫(kù)規(guī)范

1、命名規(guī)范：1、表名的單數(shù)和復(fù)數(shù)形式要統(tǒng)一。2、對(duì)于字段，如果主鍵是數(shù)字類(lèi)型的，可以考慮使用_N結(jié)尾，例如USERID_N；如果是字符類(lèi)型_C結(jié)尾USERNAME_C;3、如果一個(gè)單詞經(jīng)常出現(xiàn)，比如USER，則可以考慮以_U結(jié)尾，例如，USERID可以表示成ID_U，USERNAME表示為NAME_U。4、如果某一列是日期類(lèi)型，則使用D_開(kāi)頭，起到強(qiáng)調(diào)的作用，例如：D_CREATEDATETIME。

2、明確現(xiàn)實(shí)中實(shí)體與數(shù)據(jù)表的關(guān)系。3、一張數(shù)據(jù)表不能既沒(méi)有主鍵也沒(méi)有外鍵。4、表要符合基本表的特征。

5、必須滿足第一第二范式，盡量滿足第三范式。6、比較簡(jiǎn)潔的E-R圖。7、符合完整性約束。

基本表的特征：1、原子性，就是基本表中的字段是不可在分解的。2、原始性，基本表中的記錄是袁術(shù)數(shù)據(jù)的記錄。3、演繹性，由基本表和關(guān)系表中的數(shù)據(jù)可以派生出任何想要的數(shù)據(jù)。4、穩(wěn)定性、基本表中的結(jié)構(gòu)是相對(duì)穩(wěn)定的，表中的記錄需要長(zhǎng)期保存。

第一范式：是對(duì)屬性的原子性約束，要求屬性具有原子性，不可再分。第二范式:記錄的唯一性約束，要求記錄有唯一標(biāo)識(shí)，即實(shí)體的唯一性。第三范式：對(duì)字段冗余的約束，即任何字段不能由其他字段派生出來(lái)，要求字段沒(méi)有冗余，一個(gè)表中的字段除了和主鍵有關(guān)外，這些字段他們之間不能有關(guān)系，也就是說(shuō)這個(gè)表中的字段只能和主鍵相關(guān)，他們之間沒(méi)有關(guān)系。

數(shù)據(jù)庫(kù)設(shè)計(jì)技巧：1、在符合系統(tǒng)需求的前提下，表的個(gè)數(shù)越少越好，一張表中組合主鍵字段數(shù)越少越好，一個(gè)表中的字段越少越好。2、檢查各種變化字段。3、避免使用保留字。4、數(shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候多使用刪除標(biāo)記字段。5、避免使用觸發(fā)器。6、如果發(fā)現(xiàn)在重復(fù)輸入數(shù)據(jù)，就需要?jiǎng)?chuàng)建新的表和新的關(guān)系。7、熟練使用數(shù)據(jù)庫(kù)設(shè)計(jì)工具。8、創(chuàng)建E-R圖和數(shù)據(jù)字典。9、每個(gè)表都應(yīng)該有三個(gè)有用的字段：修改記錄時(shí)間、修改人、修改后的版本。10、對(duì)于地址和電話采用多個(gè)字段。

SQL查詢優(yōu)化

劣質(zhì)SQL判斷條件：1、運(yùn)行時(shí)間超長(zhǎng)。2、引發(fā)嚴(yán)重的等待事件。3、不能滿足壓力測(cè)試。4、消耗大量系統(tǒng)資源。

索引是對(duì)數(shù)據(jù)庫(kù)表中一列或者多列的值進(jìn)行排序的一種結(jié)構(gòu)。使用索引可以快速訪問(wèn)數(shù)據(jù)庫(kù)表中的特定信息。

索引存在的問(wèn)題：1、索引占用表空間，創(chuàng)建太多索引可能會(huì)造成索引冗余。2、索引影響DML性能。

索引使用的條件：1、一個(gè)字段里包含大量的值。2、一個(gè)字段包含多個(gè)空值。3、多個(gè)字段經(jīng)常出現(xiàn)在where查詢中。4、表非常大并且查詢返回?cái)?shù)據(jù)量低于總數(shù)據(jù)的20%。

創(chuàng)建索引的語(yǔ)法：

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 --使用索引查詢。

索引類(lèi)型，按列值是否唯一分為：非唯一索引和唯一索引。按索引列的個(gè)數(shù)分為單列索引和復(fù)合索引。按照索引的物理組織方式分為：B樹(shù)索引、反向鍵索引、基于函數(shù)的索引、位圖索引。

創(chuàng)建復(fù)合索引時(shí)，表指定的第一個(gè)列為主導(dǎo)列。

select *from depositor t where IDENTITY_L>100000 AND ACT>=500 --未使用索引
create index OP_IDACT_INDEX on DEPOSITOR(IDENTITY_L,ACT) tablespace DEMO --使用復(fù)合索引

使用復(fù)合索引的情況：1、當(dāng)SQL語(yǔ)句的WHERE自居中用到復(fù)合索引的主導(dǎo)列時(shí)。2、當(dāng)某幾個(gè)列在SQL語(yǔ)句的WHERE子句中經(jīng)常通過(guò)and操作符聯(lián)合在一起使用，并且這些列合在一起是選擇性比各自單個(gè)列的選擇性要好時(shí)。

3、當(dāng)有幾個(gè)查詢語(yǔ)句都是查詢相同的幾個(gè)列值時(shí)。

反向鍵索引就是將當(dāng)前列中的值反轉(zhuǎn)后生成的索引。

創(chuàng)建反向鍵索引的語(yǔ)法：create index索引名 on 表名（列名） reverse tablespace表空間;

基于函數(shù)的索引，基于一個(gè)或者多個(gè)列上的函數(shù)或表達(dá)式創(chuàng)建的索引。

注意：表達(dá)式中不能包含聚合函數(shù)（SUM、COUNT、AVG、MIN、MAX）。

基于函數(shù)索引的語(yǔ)法：CREATE INDEX索引名 ON 表名（函數(shù)（列名）） TABLESPACE表空間;

select lower(firstname) from depositor t; --沒(méi)有使用基于函數(shù)索引的情況。
create index firstname_lower on depositor(lower(firstname)) tablespace prd;

使用基于函數(shù)的索引的情況：1、一個(gè)表中的字段經(jīng)常被函數(shù)所調(diào)用，那么這個(gè)字段就可以使用基于函數(shù)的索引。

位圖索引：

整個(gè)表就是針對(duì)一個(gè)列建立出來(lái)的位圖索引，這個(gè)表中的列（1，2，3，4）表示的是對(duì)應(yīng)的索引列的第幾行。行代表的是當(dāng)前行，當(dāng)前被索引列的值。比如圖中第一列1，張三第一列的值是1，表示在當(dāng)前被索引的第一行的位置中張三這個(gè)值時(shí)存在的，并且是1。

使用位圖索引的情況：主要是處理數(shù)據(jù)的聚合關(guān)系的，用于一個(gè)重復(fù)數(shù)據(jù)很多的情況。

位圖索引的語(yǔ)法：

CREATE BITMAP INDEX 索引名 ON 表名（列名） TABLESPACE 表空間; select count(*) from depositor t where firstname=’Luke’;
create bitmap index fn_bitmap on depositor(firstname) tablespace prd;

表分區(qū)：有利于管理非常大的表和索引。當(dāng)表里面的內(nèi)容或者整個(gè)表的結(jié)構(gòu)非常龐大的時(shí)候，就考慮表分區(qū)。

表分區(qū)的優(yōu)點(diǎn)：1、提高數(shù)據(jù)的可用性，當(dāng)某個(gè)分區(qū)損壞不會(huì)影響整個(gè)表結(jié)構(gòu)。2、減少管理負(fù)擔(dān)。3、改善語(yǔ)句性能。

表分區(qū)的分區(qū)方式：1、區(qū)間分區(qū)。2、散列分區(qū)。3、列表分區(qū)。4、組合分區(qū)。

區(qū)間分區(qū)：往往根據(jù)一個(gè)列值的范圍來(lái)劃分，開(kāi)發(fā)中常常使用日期字段來(lái)劃分。

語(yǔ)法：

PARTITIONBY RANGE(列名) --RANGE表示分區(qū)的方式
（
 PARTITION 分區(qū)表名 VALUES LESS THAN (值)
 tablespace 表空間
）

示例：PART_1這個(gè)分區(qū)保存早于（不包括）2008年12月31日的數(shù)據(jù)

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); 顯示結(jié)果：2009-1-1；2009-6-6；2010-12-31；

散列分區(qū)：會(huì)對(duì)分區(qū)鍵應(yīng)用一個(gè)散列函數(shù)，以此確定數(shù)據(jù)應(yīng)當(dāng)放在N個(gè)分區(qū)中的哪個(gè)分區(qū)中。直到散列算法是將數(shù)據(jù)隨機(jī)均勻的分布。為了使數(shù)據(jù)分布的更均勻，分區(qū)表的數(shù)目建議為2的冪，也就是1、4、8、16……

散列分區(qū)語(yǔ)法：

PARTITION BY HASH(列)（PARTITION 分區(qū)表名 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,
)

對(duì)其隨機(jī)插入500條測(cè)試語(yǔ)句

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)

輸出結(jié)果基本均勻分布，分別為：123、116、121、140。

列表分區(qū)：可以將數(shù)據(jù)按照列的值分類(lèi)。列表分區(qū)語(yǔ)法如下：

PARTITIONBY LIST(列)
（
 PARTITION 分區(qū)表名values(指定值1，指定值2……)
）

示例：根據(jù)用戶所在地區(qū)將用戶存儲(chǔ)在不同分區(qū)中

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)

查詢結(jié)果：在part_1分區(qū)中數(shù)據(jù)為102200、102202；part_2分區(qū)中數(shù)據(jù)位164300、164303；

組合分區(qū)：就是區(qū)間分區(qū)與散列分區(qū)或者區(qū)間分區(qū)與列表分區(qū)的組合。

區(qū)間-散列組合分區(qū)表語(yǔ)法：首先按照列1的值進(jìn)行區(qū)間分區(qū)，然后按照列2散列分區(qū)。

PARTITION BY RANGE(列1) SUBPARTITION BY HASH(列2)（
 PARTITION分區(qū)名 VALUES LESS THAN (值)
 TABLESPACE表空間,
)

區(qū)間-列表組合分區(qū)表語(yǔ)法：先按照列1的值進(jìn)行區(qū)間分區(qū)，然后按照列2指定值進(jìn)行列表分區(qū)

PARTITION BY RANGE(列1) SUBPARTITION BY LIST(列2)（
 PARTITION 分區(qū)名 VALUES LESS THAN (值) TABLESPACE 表空間
 （
 SUBPARTITION 子分區(qū)名 VALUES(列表指定值1……) TABLESPACE 表空間
 ）
）

組合分區(qū)示例：

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);

結(jié)果為:DT_DATE 2009-1-1;CODE102200;

分區(qū)維護(hù)：增加分區(qū)、刪除分區(qū)、截?cái)喾謪^(qū)、合并分區(qū)。

增加分區(qū)的語(yǔ)法：

ALTER TABLE 表名 ADD PARTITION 分區(qū)表名 VALUES LESS THAN(值)

例如：

ALTERTABLE DRAWLIST ADD PARTITION PART_4 VALUES LESS
THAN(TO_DATE(‘1/1/2012’,’DD/MM/YYYY’)) TABLESPACE DEMO;

刪除分區(qū)語(yǔ)法：

ALTER TABLE 表名 DROP PARTITION 分區(qū)表名

例如：

ALTER TABLE DRAWLIST DROPPARTITION PART_3;

截?cái)喾謪^(qū)是刪除當(dāng)前分區(qū)中的數(shù)據(jù)，但不刪除當(dāng)前分區(qū)，也不影響其他分區(qū)。

語(yǔ)法：

ALTER TABLE 表名 TRUNCATE PARTITION 已存在的分區(qū)表

如：

ALTER TABLE DRAWLI ST TRUNCATEPARTITION PART_1;

合并分區(qū)是將兩個(gè)分區(qū)的數(shù)據(jù)合并成一個(gè)分區(qū)里，注意：高界限的分區(qū)不能合并到低界限的分區(qū)中。

合并分區(qū)的語(yǔ)法：

ALTER TABLE 表名 MERGE PARTITIONS 分區(qū)表1，分區(qū)表2 INTO PARTITION 分區(qū)表2

例如：

ALTER TABLE drawlist MERGE PARTITIONS part_2,part_4 INTO PARTITION part_4;

SQL優(yōu)化途徑：選擇合適的Oracle優(yōu)化器、選擇恰當(dāng)?shù)膾呙璺绞?、善于利用共享的SQL語(yǔ)句。

1、選擇合適的優(yōu)化器。CBO優(yōu)化器：基于成本的優(yōu)化器，這個(gè)成本是指CPU和內(nèi)存占用率。

在編寫(xiě)SQL時(shí)，可以使用CBO對(duì)SQL進(jìn)行優(yōu)化從而獲得更快的響應(yīng)速度。

優(yōu)化器使用的模式根據(jù)目標(biāo)而定，CBO的優(yōu)化模式有四種分別為：1、Rule：基于規(guī)則。2、Choose：默認(rèn)，表或者索引有統(tǒng)計(jì)信息，走CBO模式，否則，走RBO模式。3、FirstRow：表中有統(tǒng)計(jì)信息時(shí)，以最快方式返回查詢的前幾行，總體減少響應(yīng)時(shí)間，4、AllRows：表中有統(tǒng)計(jì)信息時(shí)，以最快的方式返回表的所有行，總體提高查詢吞吐量。

1、 選擇合適的掃描方式。Oracle中，查詢數(shù)據(jù)可以分為：全表掃描、使用ROWID、索引全掃描、快速索引掃描。

全表掃描：就是Oracle讀取表中所有的行，并檢查每一行是否滿足WHERE語(yǔ)句的限制。在數(shù)據(jù)量大的表中不建議使用全表掃描，效率低下。

使用ROWID：ROWID指出該行所在的數(shù)據(jù)文件、數(shù)據(jù)塊以及行在該塊的位置。是Oracle存取單行數(shù)據(jù)最快的方式。

索引全掃描：只在CBO模式下有效，這種方式查詢的數(shù)據(jù)必須可以從索引中直接得到。

快速索引掃描：它會(huì)掃描索引中所有的數(shù)據(jù)塊，與全索引掃描類(lèi)似，但這種方式不會(huì)對(duì)查詢出的數(shù)據(jù)進(jìn)行排序，這種方式會(huì)獲得最大吞吐量，并且縮短執(zhí)行時(shí)間。

3、學(xué)會(huì)利用共享的SQL語(yǔ)句。它存在Oracle的系統(tǒng)全局區(qū)中，也就是SGA，可以通過(guò)設(shè)置SGA的大小，來(lái)提高sql的執(zhí)行效率。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com