DB2數(shù)據(jù)庫SQL語法參考手冊(cè)

1、DB2數(shù)據(jù)庫SQL語法參考手冊(cè)DB2提供了關(guān)連式資料庫的查詢語言SQL (Structured Query Language)，是一種非?？谡Z化、既易學(xué)又易懂的語法。此一語言幾乎是每個(gè)資料庫系統(tǒng)都必須提供的，用以表示關(guān)連式的操作，包含了資料的定義(DDL)以及資料的處理(DML)。SQL原來拼成SEQUEL，這語言的原型以"系統(tǒng)R"的名字在IBM圣 荷西實(shí)驗(yàn)室完成，經(jīng)過IBM內(nèi)部及其他的許多使用性及效率測(cè)試，其結(jié)果相當(dāng)令人滿意，并決定在系統(tǒng)R的技術(shù)基礎(chǔ)發(fā)展出來IBM的產(chǎn)品。而且美國國家標(biāo)準(zhǔn)學(xué)會(huì) (ANSI)及國際標(biāo)準(zhǔn)化組織(ISO)在1987遵循一個(gè)幾乎是以IBM SQL為

2、基礎(chǔ)的標(biāo)準(zhǔn)關(guān)連式資料語言定義。一、資料定義 DDL(Data Definition Language)資料定義語言是指對(duì)資料的格式和形態(tài)下定義的語言，他是每個(gè)資料庫要建立時(shí)候時(shí)首先要面對(duì)的，舉凡資料分哪些表格關(guān)系、表格內(nèi)的有什麼欄位主鍵、表格和表格之間互相參考的關(guān)系等等，都是在開始 的時(shí)候所必須規(guī)劃好的。1. 建表格：CREATE TABLE table_name(column1 DATATYPE NOT NULL NOT NULL PRIMARY KEY,column2 DATATYPE NOT NULL,)說明：DATATYPE是資料的格式，詳見表。NUT NULL可不可以允許資料有空的(

3、尚未有資料填入)。PRIMARY KEY是本表的主鍵。2. 更改表格ALTER TABLE table_nameADD COLUMN column_name DATATYPE說明:增加一個(gè)欄位(沒有刪除某個(gè)欄位的語法。ALTER TABLE table_nameADD PRIMARY KEY (column_name)說明:更改表得的定義把某個(gè)欄位設(shè)為主鍵。ALTER TABLE table_nameDROP PRIMARY KEY (column_name)說明 :把主鍵的定義刪除。3. 建立索引CREATE INDEX index_name ON table_name (column_na

4、me)說明 :對(duì)某個(gè)表格的欄位建立索引以增加查詢時(shí)的速度。4. 刪除DROP table_nameDROP index_name二、的資料形態(tài) DATATYPEssmallint16 位元的整數(shù)。interger32 位元的整數(shù)。decimal(p,s)p 精確值和 s 大小的十進(jìn)位整數(shù)， 精確值 p 是指全部有幾個(gè)數(shù) (digits) 大小值 ,s 是指小數(shù)點(diǎn)後有幾位數(shù)。 如果沒有特別指定，則系統(tǒng)會(huì)設(shè)為 p=5; s=0 。float32 位元的實(shí)數(shù)。double64 位元的實(shí)數(shù)。char(n)n 長度的字串， n 不能超過 254 。varchar(n)長度不固定且其最大長度為 n 的字串

5、， n 不能超過 4000 。graphic(n) 和 char(n) 一樣，不過其單位是兩個(gè)字元 double-bytes ， n 不能超過 127 。這個(gè)形態(tài)是為了支援兩個(gè)字元長度的字體，例如中文字。vargraphic(n) 可變長度且其最大長度為 n 的雙字元字串， n 不能超過 2000 。date 包含了 年份、月份、日期。 time 包含了 小時(shí)、分鐘、秒。timestamp 包含了 年、月、日、時(shí)、分、秒、千分之一秒。三、資料操作 DML (Data Manipulation Language) 資料定義好之後接下來的就是資料的操作。資料的操作不外乎增加資料(insert) 、

6、查詢資料 (query) 、更改資料 (update) 、刪除資料 (delete) 四種模式，以下分 別介紹他們的語法 :1. 增加資料 :INSERT INTO table_name (column1,column2,.)valueS ( value1,value2, .)說明:(1) 若沒有指定 column 系統(tǒng)則會(huì)按表格內(nèi)的欄位順序填入資料。(2) 欄位的資料形態(tài)和所填入的資料必須吻合。(3) table_name 也可以是景觀 view_name 。INSERT INTO table_name (column1,column2,.)SELECT columnx,columny,.

7、FROM another_table說明 :也可以經(jīng)過一個(gè)子查詢 (subquery) 把別的表格的資料填入。2. 查詢資料 :基本查詢SELECT column1,columns2,.FROM table_name說明 :把 table_name 的特定欄位資料全部列出來SELECT *FROM table_nameWHERE column1 = * AND column2 > yyy OR column3 <> zzz說明:(1) '*'表示全部的欄位都列出來(2) WHERE 之後是接條件式，把符合條件的資料列出來。SELECT column1,colu

8、mn2FROM table_nameORDER BY column2 DESC說明:ORDER BY 是指定以某個(gè)欄位做排序，DESC是指從大到小排列，若沒有指明，則是從小到大排列 ,組合,查詢組合查詢是指所查詢得資料來源并不只有單一的表格，而是聯(lián)合一個(gè)以上的表格才能夠得到結(jié)果的。SELECT *FROM table1,table2WHERE table1.colum1=table2.column1說明 :(1) 查詢兩個(gè)表格中其中 column1 值相同的資料。(2) 當(dāng)然兩個(gè)表格相互比較的欄位，其資料形態(tài)必須相同。(3) 一個(gè)復(fù)雜的查詢其動(dòng)用到的表格可能會(huì)很多個(gè)。整合性的查詢 :SELEC

9、T COUNT (*) FROM table_nameWHERE column_name = xxx說明 :查詢符合條件的資料共有幾筆。SELECT SUM(column1)FROM table_name說明 :(1) 計(jì)算出總和，所選的欄位必須是可數(shù)的數(shù)字形態(tài)。(2) 除此以外還有 AVG()是計(jì)算平均、MAX()、MIN()計(jì)算最大最小值的整合性查詢。SELECT column1,AVG(column2)FROM table_nameGROUP BY column1HAVING AVG(column2) >xxx說明 :(1)GROUP BY: 以 column1 為一組計(jì)算 col

10、umn2 的平均值必須和 AVG、 SUM 等整合性查詢的關(guān) 鍵字一起使用。(2)HAVING : 必須和 GROUP BY 一起使用作為整合性的限制復(fù)合性的查詢SELECT *FROM table_name1WHERE EXISTS (SELECT *FROM table_name2WHERE conditions )說明:(1)WHERE 的 conditions 可以是另外一個(gè)的 query 。(2)EXISTS 在此是指存在與否。SELECT *FROM table_name1WHERE column1 IN (SELECT column1 FROM table_name2WHERE

11、conditions )說明:(1)IN 後面接的是一個(gè)集合，表示 column1 存在集合里面。(2)SELECT 出來的資料形態(tài)必須符合 column1 。其他查詢SELECT *FROM table_name1WHERE column1 LIKE 'x%'說明：LIKE必須和後面的x%'相呼應(yīng)表示以 x為開頭的字串。SELECT *FROM table_name1WHERE column1 IN ('xxx','yyy',.)說明 ：IN 後面接的是一個(gè)集合，表示 column1 存在集合里面。SELECT *FROM table_

12、name1WHERE column1 BETWEEN xx AND yy說明 ：BETWEEN 表示 column1 的值介於 xx 和 yy 之間。3. 更改資料 ：UPDATE table_nameSET column1='xxx'WHERE conditoins說明 ：(1) 更改某個(gè)欄位設(shè)定其值為 'xxx' 。(2) conditions 是所要符合的條件、若沒有 WHERE 則整個(gè) table 的那個(gè)欄位都會(huì)全部被更改。4. 刪除資料 ：DELETE FROM table_nameWHERE conditions說明 :刪除符合條件的資料說明 :關(guān)于

13、 WHERE 條件后面如果包含有日期的比較，不同數(shù)據(jù)庫有不同的表達(dá)式。具體如下(1) 如果是 ACCESS 數(shù)據(jù)庫，則為 :WHERE mydate>#2000-01-01#(2) 如果是 ORACLE 數(shù)據(jù)庫，則為 :WHERE mydate>cast('2000-01-01' as date)WHERE mydate>to_date('2000-01-01','yyyy-mm-dd')在 Delphi 中寫成 * from abc where mydate>cast('+''''

14、+thedate+''''+' as date)');如果比較日期時(shí)間型，則為 :WHERE mydatetime>to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss')DB2基礎(chǔ)：編碼DB2 SQL以獲得最佳性能簡(jiǎn)介當(dāng)要保證用 IBM DB2? Universal Database?(DB2 UDB)和 Borland? 工具 (如 Delphi? 、C+Builder? 或 Kylix?) 構(gòu)建的企業(yè)應(yīng)用程序擁有最優(yōu)性能時(shí)， 程序員可以利用 DB2 優(yōu)

15、化器的能力來處 理即使是難以處理的” SQL語句并給出有效的存取路徑。盡管如此，拙劣編碼的SQL和應(yīng)用程序代碼仍可能給您帶來性能問題，通過學(xué)習(xí)幾條基本準(zhǔn)則可以輕易地避免這些問題。我將向您演示DB2 優(yōu)化器的工作方式，并提供編寫能發(fā)揮優(yōu)化器最大效率的 SQL 的準(zhǔn)則。但即使擁有了 DB2 的優(yōu)化能力，編寫有 效的 SQL 語句仍可能是一件復(fù)雜的事情。如果程序員和開發(fā)人員還不熟悉關(guān)系數(shù)據(jù)庫環(huán)境，這件事就尤 其顯得棘手。 因此，在我們深入研究編碼 SQL 以獲得最佳性能的細(xì)節(jié)之前， 先花一些時(shí)間來回顧 SQL 基 礎(chǔ)知識(shí)。基礎(chǔ)知識(shí)由于 SQL 與過程化語言不同，它提供了更高的抽象級(jí)別，因此它可以讓程

16、序員把精力集中到他們需要什么樣的數(shù)據(jù)，而不是如何檢索數(shù)據(jù)。您不必使用嵌入式數(shù)據(jù)導(dǎo)航指令來編碼SQL oDB2會(huì)分析SQL，并“在幕后 ”制定數(shù)據(jù)導(dǎo)航指令。這些數(shù)據(jù)導(dǎo)航指令叫作 存取路徑。讓 DBMS 確定到數(shù)據(jù)的最優(yōu)存取路徑 解除了程序員肩上沉重的負(fù)擔(dān)。此外，數(shù)據(jù)庫可以更好地理解它存儲(chǔ)的數(shù)據(jù)的狀態(tài)，從而可以生成到數(shù)據(jù) 的更有效和動(dòng)態(tài)的存取路徑。其結(jié)果就是適當(dāng)使用的SQL可以用于更快的應(yīng)用程序開發(fā)。另一個(gè)SQL特性是它不僅僅是一種查詢語言。您還可以使用它來定義數(shù)據(jù)結(jié)構(gòu)；控制對(duì)數(shù)據(jù)的訪問；以及插入、修改和刪除數(shù)據(jù)的發(fā)生。通過提供一種公共語言，SQL簡(jiǎn)化了 DBA、系統(tǒng)程序員、應(yīng)用程序員、系統(tǒng)分析員

17、和最終用戶之間的通信。當(dāng)項(xiàng)目的所有參與者都使用同一種語言時(shí)，他們之間所建立起來 的協(xié)作就可以減少整體系統(tǒng)開發(fā)時(shí)間。歷史證明，保證 SQL成功的最重要的一個(gè)特性就是它使用類似英語的語法輕松地檢索數(shù)據(jù)的能力。 理解這種語言比理解數(shù)據(jù)頁面的結(jié)構(gòu)和程序源代碼要容易得多：SELECT LASTNAMEFROM EMPWHERE EMPNO = '000010'想想看：當(dāng)訪問文件中的數(shù)據(jù)時(shí)，程序員必須編碼指令來打開文件、開始一個(gè)循環(huán)、讀取記錄、檢查EMPNO 字段是否等于適當(dāng)?shù)闹?、檢查文件結(jié)尾、回到循環(huán)的開頭等。SQL 本來就是非常靈活的。 它使用自由格式的結(jié)構(gòu)， 該結(jié)構(gòu)可以讓用戶開發(fā) S

18、QL 語句來適合他們的 需要。 DBMS 在執(zhí)行之前會(huì)分析每個(gè) SQL 請(qǐng)求，以檢查語法是否正確和優(yōu)化該請(qǐng)求。 SQL 語句不需要 從任何給定的列中開始， 您可以將它們串在一行中， 或者把它們拆成幾行。 例如， 以下這條單行的 SQL 語 句與我前面使用的三行示例等價(jià)：SELECT LASTNAME FROM EMP WHERE EMPNO = '000010'SQL 的另一個(gè)靈活特性是您可以用許多形式不同但功能等價(jià)的方法來制定一個(gè)請(qǐng)求。例如： SQL 可 以連接表或嵌套查詢。您始終可以將嵌套查詢轉(zhuǎn)換成等價(jià)的連接。您可以在大量的函數(shù)和謂詞中看到這一 靈活性的其它示例。具有等價(jià)功

19、能的特性的示例包括：BETWEEN vs <= / >=IN vs 一系列和 OR 配合的謂詞INNER JOIN vs FROM 子句中串在一起并用逗號(hào)分隔的表OUTER JOIN vs 帶有 UNION 的簡(jiǎn)單 SELECT 和相關(guān)的子查詢CASE 表達(dá)式 vs 復(fù)雜的 UNION ALL 語句SQL 展示的這一靈活性并不總是稱心的， 因?yàn)樾问讲煌δ艿葍r(jià)的 SQL 公式可以提供非常不同的 性能。我將在本文的以后部分討論該靈活性所造成的結(jié)果，并提供開發(fā)有效的 SQL 的準(zhǔn)則。如我所說的， SQL 指定了要檢索或操作什么數(shù)據(jù)， 但沒有指定數(shù)據(jù)庫如何完成這些任務(wù)。 這就使 SQL

20、 本身變得很簡(jiǎn)單。如果您能夠記得關(guān)系數(shù)據(jù)庫的一次處理一個(gè)集合 (set-at-a-time) 的特點(diǎn)，您就開始掌握 SQL 的本質(zhì)和性質(zhì)了。 一條 SQL 語句可以作用于多行。 作用于一組數(shù)據(jù)而不需要建立如何檢索和操作數(shù) 據(jù)的能力將 SQL 定義成非過程化語言因?yàn)?SQL 是一種非過程化語言，所以一條語句可以代替一系列過程。同樣，由于 SQL 使用集合級(jí) 別的處理以及 DB2 優(yōu)化查詢來確定數(shù)據(jù)導(dǎo)航邏輯，所以這是可能的。有時(shí)，如果不使用 SQL 語句，一 條或兩條 SQL 語句可以完成的任務(wù)就需要完整的過程化程序來完成。優(yōu)化器優(yōu)化器是 DB2 的心臟和靈魂。 它分析 SQL 語句并確定可以滿足

21、每條語句的最有效的存取路徑( 請(qǐng)參閱圖 1) 。 DB2 UDB 通過解析 SQL 語句來確定必須訪問哪些表和列，從而完成該操作。 DB2 優(yōu)化器然 后查詢存儲(chǔ)在 DB2 系統(tǒng)目錄中的系統(tǒng)信息和統(tǒng)計(jì)信息，以確定完成滿足 SQL 請(qǐng)求所必需的任務(wù)的最佳 方法。圖 1. 運(yùn)行中的 DB2 優(yōu)化D日2 DBMSI z科I mEd削SOL Sl0tem»nt（s）Other Sysle-mInformal tonOB? OptlmirfifPlan TablesSytlem CallogOptimi2«dAccessPth優(yōu)化器在功能上等價(jià)于一個(gè)專家系統(tǒng)。專家系統(tǒng)是一個(gè)標(biāo)準(zhǔn)規(guī)則集合

22、，當(dāng)與情境數(shù)據(jù)組合時(shí)，它返回 一個(gè)專家”意見。例如，醫(yī)學(xué)專家系統(tǒng)采用一個(gè)規(guī)則集合，用來確定哪些藥可以用于哪些疾病，將規(guī)則集 與描述疾病癥狀的數(shù)據(jù)組合，并將知識(shí)庫應(yīng)用于輸入癥狀的列表。DB2優(yōu)化器會(huì)根據(jù)存儲(chǔ)在 DB2系統(tǒng)目錄中的情境數(shù)據(jù)和 SQL格式的查詢輸入來生成對(duì)數(shù)據(jù)檢索方法的專家意見。在DBMS中優(yōu)化數(shù)據(jù)訪問的概念是 DB2最強(qiáng)大的能力之一。請(qǐng)記住，您訪問 DB2數(shù)據(jù)時(shí)應(yīng)告訴DB2要檢索什么，而不是如何檢索。無論數(shù)據(jù)實(shí)際上是如何存儲(chǔ)和操作的，DB2和SQL都可以訪問該數(shù)據(jù)。從物理存儲(chǔ)特征中分離岀訪問標(biāo)準(zhǔn)叫作物理數(shù)據(jù)獨(dú)立性。DB2的優(yōu)化器是完成該物理數(shù)據(jù)獨(dú)立性的組件。如果您不要索引，DB2仍

23、然能夠訪問數(shù)據(jù)（盡管效率會(huì)降低）。如果將一列添加到正在被訪問的表中， DB2仍然可以在不更改程序代碼的情況下操作數(shù)據(jù)。因?yàn)榈紻B2數(shù)據(jù)的物理存取路徑并不是由程序員在應(yīng)用程序中編碼的，而是由 DB2生成的，所以這種情況是完全有可能發(fā)生的。這個(gè)特點(diǎn)與非 DBMS系統(tǒng)非常不同，在那種系統(tǒng)中，程序員必須知道數(shù)據(jù)的物理結(jié)構(gòu)。如果有索引，程序員就必須編寫適當(dāng)?shù)拇a來使用該索引。如果某人刪除了索引，程序就不能工作，除非程序員進(jìn)行更 改。而使用 DB2和SQL就不必如此。這一靈活性完全歸功于DB2自動(dòng)優(yōu)化數(shù)據(jù)操作請(qǐng)求的能力。優(yōu)化器根據(jù)許多信息執(zhí)行復(fù)雜的計(jì)算。要使優(yōu)化器的工作方式直觀化，可以將優(yōu)化器想象成執(zhí)行一

24、個(gè) 四步驟的過程：1、接收并驗(yàn)證 SQL語句的語法。2、分析環(huán)境并優(yōu)化滿足 SQL語句的方法。3、 創(chuàng)建計(jì)算機(jī)可讀指令來執(zhí)行優(yōu)化的SQL oSQL語句？4、執(zhí)行指令或存儲(chǔ)它們以便將來執(zhí)行。這個(gè)過程的第二步是最有趣的。優(yōu)化器怎樣決定如何以它的方式執(zhí)行您可以發(fā)送的大量?jī)?yōu)化器有許多類型的優(yōu)化SQL的策略。它如何選擇在優(yōu)化存取路徑中使用這些策略中的哪一個(gè)？IBM并沒有發(fā)布優(yōu)化器如何確定最佳存取路徑的真正和深入的詳細(xì)信息，但優(yōu)化器是一個(gè)基于成本的優(yōu)化器。這意味著優(yōu)化器將始終嘗試為每個(gè)查詢制定減少總體成本的存取路徑。要實(shí)現(xiàn)這個(gè)目標(biāo)，DB2優(yōu)化器會(huì)應(yīng)用查詢成本公式，該公式對(duì)每條可能的存取路徑的四個(gè)因素進(jìn)行評(píng)

25、估和權(quán)衡：CPU成本、I/O成本、DB2系統(tǒng)目錄中的統(tǒng)計(jì)信息和實(shí)際的SQL語句。在DB2中提高Insert性能的技巧INSERT 處理過程概述首先讓我們快速地看看insert 一行時(shí)的處理步驟。這些步驟中的每一步都有優(yōu)化的潛力，對(duì)此我們?cè)?后面會(huì)一一討論。1、 在客戶機(jī)準(zhǔn)備 語句。對(duì)于動(dòng)態(tài) SQL，在語句執(zhí)行前就要做這一步，此處的性能是很重要的；對(duì)于 靜態(tài)SQL，這一步的性能實(shí)際上關(guān)系不大，因?yàn)檎Z句的準(zhǔn)備是事先完成的。2、 在客戶機(jī)，將要插入的行的各個(gè)列值組裝起來，發(fā)送到 DB2服務(wù)器。3、DB2服務(wù)器確定將這一行插入到哪一頁中。4、 DB2在用于該頁的緩沖池中預(yù)留一個(gè)位置。如果DB2選定的是

26、一個(gè)已有的頁，那么就需要讀磁盤;如果使用一個(gè)新頁，則要在表空間（如果是SMS，也就是系統(tǒng)管理存儲(chǔ)的表空間）中為該頁物理地分配空間。插入了新行的每一頁最后都要從緩沖池寫入到磁盤。5、 在目標(biāo)頁中對(duì)該行進(jìn)行格式化，并獲得該行上的一個(gè)X（exclusive，獨(dú)占的）行鎖。6、將反映該insert的一條記錄寫入到日志緩沖區(qū)中。7、 最后提交包含該insert的事務(wù)，如果這時(shí)日志緩沖區(qū)中的記錄還沒有被寫入日志文件的話，則將 這些記錄寫到日志文件中。此外，還可能發(fā)生很多類型的附加處理，這取決于數(shù)據(jù)庫配置，例如，索引或觸發(fā)器的存在。這種額外的處理對(duì)于性能來說也是意義重大的，我們?cè)诤竺鏁?huì)討論到。insert的

27、替代方案在詳細(xì)討論insert的優(yōu)化之前，讓我們先考慮一下 insert的兩種替代方案：load和import。import 實(shí)用程序?qū)嶋H上是 SQL INSERT 的一個(gè)前端，但它的某些功能對(duì)于您來說也是有用的。load也有一些有用的額外功能，但是我們使用 load而不使用insert的主要原因是可以提高性能。load直接格式化數(shù)據(jù)頁，而避免了由于插入導(dǎo)致的對(duì)每一行進(jìn)行處理的大部分開銷（例如，日志記錄在這里實(shí)際上是消除了 ）。而且，load可以更好地利用多 處理器機(jī)器上的并行性。在V8 load中有兩個(gè)新功能，它們對(duì)于load成為insert的替代方案有著特別的功效，這兩個(gè)功能是：從游標(biāo)裝載

28、和從調(diào)用層接 口 （CLI）應(yīng)用程序裝載。從游標(biāo)裝載這種方法可用于應(yīng)用程序的程序代碼 （通過 db2Load API） ，或用于 DB2 腳本。下面是后一種情況的 一個(gè)例子：declare staffcursor cursor forselect * from staff;load from staffcursor of cursor insert into myschema.new_staff;這兩行可以用下面一行替代：insert into myschema.new_staff select * from staff同等效的 INSERT . SELECT 語句相比，從游標(biāo)裝載幾乎可以提高

29、 20% 的性能。從 CLI 裝載這種方法顯然只限于調(diào)用層接口 （CLI） 應(yīng)用程序，但是它非常快。這種技巧非常類似于數(shù)組插入，DB2附帶了這樣的示例，使用 load 時(shí)的速度是使用經(jīng)過完全優(yōu)化的數(shù)組插入時(shí)的兩倍，幾乎要比未經(jīng)優(yōu)化的 數(shù)組插入快 10 倍。所有 insert 可以改進(jìn)的地方讓我們看看插入處理的一些必要步驟，以及我們可以用來優(yōu)化這些步驟的技巧。1. 語句準(zhǔn)備作為一條 SQL 語句， INSERT 語句在執(zhí)行之前必須由 DB2 進(jìn)行編譯。 這一步驟可以自動(dòng)發(fā)生 （例如 在 CLP 中，或者在一次 CLI SQLExecDirect 調(diào)用中 ），也可以顯式地進(jìn)行 （ 例如，通過一條

30、SQL Prepare 、 CLI SQLPrepare 或 JDBC prepareStatement 語句 ） 。該編譯過程牽涉到授權(quán)檢查、優(yōu)化，以及將語句轉(zhuǎn) 化為可執(zhí)行格式時(shí)所需的其他一些活動(dòng)。在編譯語句時(shí)，語句的訪問計(jì)劃被存儲(chǔ)在包緩存中。如果重復(fù)地執(zhí)行相同的 INSERT 語句，則該語句的訪問計(jì)劃 （通常 ）會(huì)進(jìn)入到包緩存中，這樣就免除了 編譯的開銷。然而，如果 insert 語句對(duì)于每一行有不同的值，那么每一條語句都將被看成是惟一的，必須 單獨(dú)地進(jìn)行編譯。因此，將像下面這樣的重復(fù)語句：insert into mytable values （1, 'abc'）inser

31、t into mytable values （2, 'def'）等等，換成帶有參數(shù)標(biāo)記的語句，一次準(zhǔn)備，重復(fù)執(zhí)行，這樣做是十分可取的：insert into mytable values （?, ?）使用參數(shù)標(biāo)記可以讓一系列的 insert 的運(yùn)行速度提高數(shù)倍。 （在靜態(tài) SQL 程序中使用主機(jī)變量也可 以獲得類似的好處。 ）2. 發(fā)送列值到服務(wù)器可以歸為這一類的優(yōu)化技巧有好幾種。 最重要的一種技巧是在每條 insert 語句中包括多行， 這樣就可 以避免對(duì)于每一行都進(jìn)行客戶機(jī) -服務(wù)器通信，同時(shí)也減少了 DB2 開銷?？捎糜诙嘈胁迦氲募记捎校涸?VALUES 子句中包含多行的

32、內(nèi)容。 例如，下面的語句將插入三行： INSERT INTO mytable VALUES (1, 'abc'), (2, 'def'), (3, 'ghi')在 CLI 中使用數(shù)組插入 (array insert) 。這需要準(zhǔn)備一條帶參數(shù)標(biāo)記的 INSERT 語句，定義一個(gè)用于存儲(chǔ) 要插入的值的數(shù)組，將該數(shù)組綁定到參數(shù)標(biāo)記，以及對(duì)于每個(gè)數(shù)組中的一組內(nèi)容執(zhí)行一次 insert 。而且， 示例程序 sqllib/samples/cli/tbload.c 提供了數(shù)組插入的基本框架 (但是執(zhí)行的是 CLI LOAD) 。從不使用數(shù) 組改為使用包含 10

33、0 行的數(shù)組，可以將時(shí)間縮短大約 2.5 倍。所以應(yīng)該盡可能地使用包含至少 100 行 的數(shù)組。在 JDBC 中使用批處理操作。這跟 CLI 中的數(shù)組插入一樣，基于相同的概念，但是實(shí)現(xiàn)細(xì)節(jié)有所不同。 當(dāng)通過 prepareStatement 方法準(zhǔn)備了 insert 語句之后，剩下的步驟是針對(duì)每一列調(diào)用適當(dāng)?shù)?setXXXX 方法 (例如， setString 或 setInt) ，然后是 addBatch 。對(duì)于要插入的每一行，都要重復(fù)這些步驟，然后調(diào) 用 executeBatch 來執(zhí)行插入。要查看這方面的例子，請(qǐng)參閱 “參考資料 ”一節(jié)中的 JDBC Tutorial 。使用 load

34、將數(shù)據(jù)快速地裝入到一個(gè) staging 表中，然后使用 INSERT . SELECT 填充主表。 ( 通過這種 方法節(jié)省下來的代價(jià)源于 load 的速度非?？?，再加上 INSERT . SELECT 是在 DB2 內(nèi)(在服務(wù)器上 ) 傳輸數(shù)據(jù)的，從而消除了通信上的代價(jià)。一般情況下我們不會(huì)使用這種方法，除非在INSERT . SELECT中還要另外做 load 無法完成的處理。如果不可能在一條 insert 語句中傳遞多行，那么最好是將多條 insert 語句組成一組，將它們一起從 客戶機(jī)傳遞到服務(wù)器。 (不過，這意味著每條 insert 都包含不同的值，都需要準(zhǔn)備，因而其性能實(shí)際上要 比使用

35、參數(shù)標(biāo)記情況下的性能更差一些。)將多條語句組合成一條語句可以通過Compound SQL 來實(shí)現(xiàn)：在 SQL 中，復(fù)合語句是通過在 CLI 中，復(fù)合語句可以通過 另一種生成復(fù)合語句的方法是在SQL_ATTR_CHAINING_BEGINBEGIN ATOMIC 或 BEGIN COMPOUND 語句創(chuàng)建的SQLExecDirect 和 SQLExecute 調(diào)用來建立。對(duì)于 DB2 V8 FixPak 4 (對(duì)一條預(yù)處理語句 ) 發(fā)出多個(gè) SQLExecute 調(diào)用之前設(shè)置語句屬性，并在調(diào)用之后設(shè)置語句屬性 SQL_ATTR_CHAINING_END下面是關(guān)于該話題的其他一些建議： 如果可能的

36、話，讓客戶機(jī)與要存取的數(shù)據(jù)庫使用相同的代碼頁，以避免在服務(wù)器上的轉(zhuǎn)換代價(jià)。數(shù)據(jù)庫的 代碼頁可以通過運(yùn)行 “get db cfg for 來確”定。在某些情況下， CLI 會(huì)自動(dòng)執(zhí)行數(shù)據(jù)類型轉(zhuǎn)換， 但是這樣同時(shí)也會(huì)帶來看不見的 (小小的 )性能損耗。 因此 盡量使插入值直接處于與相應(yīng)列對(duì)應(yīng)的格式。CLI 中使用數(shù)組插入時(shí)，對(duì)于整個(gè)一組插入，應(yīng) 而不是對(duì)每一組數(shù)組內(nèi)容都執(zhí)行一次。對(duì)于個(gè)體( 如果使用了多維群集 (Multi-dimensional將應(yīng)用程序中與插入相關(guān)的設(shè)置開銷最小化。例如，當(dāng)在 該盡量保證對(duì)于每一列只執(zhí)行一次 SQLBindParameter 來說，這些調(diào)用的代價(jià)并不高，但是這些

37、代價(jià)是累積的。3. 找到存儲(chǔ)行的地方DB2 使用三種算法中的一種來確定將行插入到哪里。 Clustering ， MDC) ，則另當(dāng)別論，我們?cè)谶@里不予討論。缺省模式是， DB2 搜索散布在表的各頁上的自由空間控制記錄 (Free Space Control Records ，F(xiàn)SCR) ， 以找到有足夠自由空間存放新行的頁。顯然，如果每頁上的自由空間都比較少的話，就要浪費(fèi)很多的搜索 時(shí)間。為了應(yīng)付這一點(diǎn)， DB2 提供了 DB2MAXFSCRSEARCH 注冊(cè)表變量，以便允許將搜索范圍限制 為少于缺省的 5 頁。當(dāng)表是通過 ALTER TABLE 以 APPEND 模式放置時(shí)，就要使用第二種

38、算法。這樣就完全避免了FSCR 搜索，因?yàn)橹恍韬?jiǎn)單地將行直接放到表的末尾。當(dāng)表有群集索引 (clustering index) 時(shí)，就要用到最后一種算法。在這種情況下， DB2 試圖將每一行插 入到有相似鍵值的一頁中。如果那一頁沒有空間了， DB2 就會(huì)嘗試附近的頁，如果附近的頁也沒有空間， DB2 就進(jìn)行 FSCR 搜索。如果只考慮插入時(shí)間的優(yōu)化，那么使用 APPEND 模式對(duì)于批量插入是最快的一種方法，但是這種方 法的效果遠(yuǎn)不如我們這里討論的很多其他方法那么成效顯著。第二好的方法應(yīng)該是采用缺省算法，但是， 如果在最佳環(huán)境中，更改 DB2MAXFSCRSEARCH 的值影響很小，而在一個(gè) I

39、/O 約束較少的環(huán)境中，這 種更改所造成的影響就比較可觀了。如果有群集索引，則對(duì) insert 的性能會(huì)有很大的負(fù)面影響，這一點(diǎn)也不驚奇，因?yàn)槭褂萌杭饕哪?的就是通過在插入時(shí)做額外的工作來提高查詢(即 select) 性能的。如果的確需要群集索引，那么可以通過確保有足夠的自由空間來使其對(duì)插入的影響降至最?。菏褂?ALTER TABLE 增加 PCTFREE ，然后使用 REORG 預(yù)留自由空間。不過，如果允許太多自由空間的存在，則可能導(dǎo)致查詢時(shí)需要讀取額外的頁，這 反而大大違反了使用群集索引的本意。另一種選擇是，在批量插入之前先刪除群集索引，而后再重新創(chuàng)建 群集索引，也許這是最優(yōu)的方法 (

40、創(chuàng)建群集索引的開銷跟創(chuàng)建常規(guī)索引的開銷差不多，都不是很大，只是在 插入時(shí)有額外的開銷 ) 。4. 緩沖池、 I/O 和頁清除每一條 insert 在執(zhí)行時(shí)，都是先將新行存儲(chǔ)在一個(gè)頁中，并最終將那個(gè)頁寫到磁盤上。一旦像前面討 論的那樣指定了頁，那么在將行添加到該頁之前，該頁必須已經(jīng)在緩沖池中。對(duì)于批量插入，大部分頁都 是最新指派給表的，因此讓我們關(guān)注一下對(duì)新頁的處理。如果表在系統(tǒng)管理存儲(chǔ)的 (System Managed Storage ，SMS) 表空間中，當(dāng)需要新頁時(shí)，缺省情況下是 從文件系統(tǒng)中分別為每一頁分配空間。 但是， 如果對(duì)數(shù)據(jù)庫運(yùn)行了 db2empfa 命令，那么每個(gè) SMS 表空

41、 間就會(huì)為新頁一次性分配一個(gè)區(qū)段。我們建議運(yùn)行 db2empfa 命令，并使用 32 頁的區(qū)段。對(duì)于數(shù)據(jù)庫管理的存儲(chǔ) (Database Managed Storage ，DMS) 表空間，空間是在創(chuàng)建表空間時(shí)就預(yù)先分 配的，但是頁的區(qū)段則是在插入處理過程中指派給表的。與 SMS 相比， DMS 對(duì)空間的預(yù)分配可以提高 大約 20% 的性能 - 使用 DMS 時(shí)，更改區(qū)段大小并沒有明顯的效果。如果表上有索引，則對(duì)于每個(gè)插入的行，都要添加一個(gè)條目到每條索引。這要求在緩沖池中存在適當(dāng) 的索引頁。晚些時(shí)候我們將討論索引的維護(hù)，但是現(xiàn)在只需記住，插入時(shí)對(duì)緩沖池和 I/O 的考慮也類似地 適用于索引頁，

42、對(duì)于數(shù)據(jù)頁也是一樣。隨著插入的進(jìn)行，越來越多的頁中將填入被插入的行，但是， DB2 不要求在 insert 或 Commit 后將 任何新插入的或更新后的數(shù)據(jù)或索引寫入到磁盤。(這是由于 DB2 的 writeahead 日志記錄算法。但是有一個(gè)例外，這將在關(guān)于日志記錄的小節(jié)中論述到 能會(huì)在數(shù)據(jù)庫關(guān)閉時(shí)才會(huì)輪到。）然而，這些頁需要在某一時(shí)刻寫到磁盤上，這個(gè)時(shí)刻可一般來說，對(duì)于批量插入，您會(huì)希望積極地進(jìn)行 異步頁清除 （asynchronous page cleaning） ，這樣在 緩沖池中就總有可用于新頁的空余位置。頁清除率，或者說總?cè)表撀?，可能?dǎo)致計(jì)時(shí)上的很大不同，使得 性能比較容易產(chǎn)生誤

43、解。 例如， 如果使用 100,000 頁的緩沖池， 并且不存在頁清除，則批量插入在結(jié)束前 不會(huì)有任何新的或更改過的 （“臟的 ”頁）寫到磁盤上，但是隨后的操作 （例如選擇，甚至乎關(guān)閉數(shù)據(jù)庫 ）都將被 大大推遲， 因?yàn)檫@時(shí)有至多 100,000 個(gè)在插入時(shí)產(chǎn)生的臟頁要寫到磁盤上。 另一方面， 如果在同一情況下 進(jìn)行了積極的頁清除，則批量插入過程可能要花更長的時(shí)間，但是此后緩沖池中的臟頁要少一些，從而使 得隨后的任務(wù)執(zhí)行起來性能更佳。至于那些結(jié)果中到底哪個(gè)要更好些，我們并不是總能分得清，但是通常 來說，將所有臟頁都存儲(chǔ)在緩沖池中是不可能的， 所以為了取得最佳性能， 采取有效的頁清除是有必要的。為

44、了盡可能好地進(jìn)行頁清除：將 CHNGPGS_THRESH 數(shù)據(jù)庫配置參數(shù)的值從缺省的 60 減少到 5 這么低。 這個(gè)參數(shù)決定緩沖池中臟 頁的閾值百分比，當(dāng)臟頁達(dá)到這個(gè)百分比時(shí)，就會(huì)啟動(dòng)頁清除。嘗試啟用注冊(cè)表變量 DB2_USE_ALTERNATE_PAGE_CLEANING（ 在 DB2 V8 FixPak 4 中最新提供 ）。 通過將這個(gè)變量設(shè)置成 ON ，可以為頁清除提供一種比缺省方法 （基于 CHNGPGS_THRESH 和 LSN 間 隙觸發(fā)器 ）更積極的方法。我沒有評(píng)測(cè)過其效果。請(qǐng)參閱FixPak 4 Release Notes 以了解這方面的信息。確保 NUM_IOCLEANER

45、S 數(shù)據(jù)庫配置參數(shù)的值至少等于數(shù)據(jù)庫中物理存儲(chǔ)設(shè)備的數(shù)量。至于 I/O 本身，當(dāng)需要建立索引時(shí)，可以通過使用盡可能大的緩沖池來將 I/O 活動(dòng)減至最少。如果 不存在索引，則使用較大的緩沖池幫助不大，而只是推遲了 I/O 。也就是說，它允許所有新頁暫時(shí)安放在 緩沖池中，但是最終仍需要將這些頁寫到磁盤上。當(dāng)發(fā)生將頁寫到磁盤的 I/O 時(shí)，通過一些常規(guī)的 I/O 調(diào)優(yōu)步驟可以加快這一過程，例如：將表空間分布在多個(gè)容器 （這些容器映射到不同磁盤 ） 。 盡可能使用最快的 硬件 和存儲(chǔ)管理配置，這包括磁盤和通道速度、寫緩存以及并行寫等因素。 避免 RAID5（ 除非是與像 Shark 這樣有效的存儲(chǔ)設(shè)備

46、一起使用 ） 。5. 鎖缺省情況下， 每一個(gè)插入的行之上都有一個(gè) X 鎖，這個(gè)鎖是在該行創(chuàng)建時(shí)就開始有的， 一直到 insert 被提交。有兩個(gè)跟 insert 和鎖相關(guān)的性能問題：為獲得和釋放鎖而產(chǎn)生的 CPU 開銷。 可能由于鎖沖突而導(dǎo)致的并發(fā)問題。對(duì)于經(jīng)過良好優(yōu)化的批量插入，由獲得每一行之上的一個(gè) X 鎖以及后來釋放該鎖引起的 CPU 開銷 是比較可觀的。對(duì)于每個(gè)新行之上的鎖，惟一可以替代的是表鎖 （DB2 中沒有頁鎖 ）。當(dāng)使用表鎖時(shí)，耗時(shí) 減少了 3%。有 3 種情況可以導(dǎo)致表鎖的使用，在討論表鎖的缺點(diǎn)之前，我們先用一點(diǎn)時(shí)間看看這 3 種 情況：運(yùn)行 ALTER TABLE LOCK

47、SIZE TABLE 。這將導(dǎo)致 DB2 為隨后使用該表的所有 SQL 語句使用一個(gè)表 鎖，直到 locksize 參數(shù)改回到 ROW 。運(yùn)行 LOCK TABLE IN EXCLUSIVE MODE 。這將導(dǎo)致表上立即上了一個(gè) X 鎖。注意，在下一次提交 （ 或 回滾 ）的時(shí)候，這個(gè)表將被釋放，因此，如果您要運(yùn)行一個(gè)測(cè)試，測(cè)試中每N 行提交一次，那么就需要在每次提交之后重復(fù)執(zhí)行 LOCK TABLE 。使用缺省鎖，但是讓 LOCKLIST 和 MAXLOCKS 數(shù)據(jù)庫配置參數(shù)的值比較小。當(dāng)獲得少量的行鎖時(shí)，行 鎖就會(huì)自動(dòng)地逐漸升級(jí)為表鎖。當(dāng)然，所有這些的缺點(diǎn)就在于并發(fā)的影響：如果表上有一個(gè)

48、X 鎖，那么其他應(yīng)用程序除非使用了隔 離級(jí)別 UR（ 未提交的讀 ），否則都不能訪問該表。 如果知道獨(dú)占訪問不會(huì)導(dǎo)致問題， 那么就應(yīng)該盡量使用表 鎖。但是，即使您堅(jiān)持使用行鎖，也應(yīng)記住，在批量插入期間，表中可能存在數(shù)千個(gè)有 X 鎖的新行，所 以就可能與其他使用該表的應(yīng)用程序產(chǎn)生沖突。通過一些方法可以將這些沖突減至最少： 確保鎖的升級(jí)不會(huì)無故發(fā)生。您可能需要加大 LOCKLIST 和 /或 MAXLOCKS 的值，以允許插入應(yīng)用程 序有足夠的鎖。對(duì)于其他的應(yīng)用程序，使用隔離級(jí)別 UR對(duì)于 V8 FixPak 4 ，或許也可以通過 量設(shè)置為 YES ，那么在很多情況下，DB2_EVALUNCOMM

49、ITTED 注冊(cè)表變量來減少鎖沖突：如果將該變 只能獲得那些符合某個(gè)謂詞的行上的鎖， 而并不是獲得被檢查的所有 行上的鎖。發(fā)出一個(gè) COMMIT 命令以釋放鎖，因此如果更頻繁地提交的話就足以減輕鎖沖突的負(fù)擔(dān)。注意 在 V7 中，存在涉及 insert 和鍵鎖的并發(fā)問題，但是在 V8 中，由于提供了 type-2 索引，這些問題實(shí) 際上已經(jīng)不見了。 如果要遷移到 V8 中來，那么應(yīng)該確保使用帶 CONVERT 關(guān)鍵字的 REORG INDEXES 命令，以便將索引從 type-1 轉(zhuǎn)換為 type-2 。在 V7 中，插入過程中可能使用 W 或 NW 鎖，但是在 V8 中只有在使用了 type-

50、1 索引或者隔離級(jí)別 為 RR 的情況下才會(huì)出現(xiàn)這兩種鎖。因此，應(yīng)盡可能避免這兩種情況。一條 insert 所據(jù)有的鎖 （通常是一個(gè) X 鎖 ）通常不會(huì)受隔離級(jí)別的影響。例如，使用隔離級(jí)別UR 不會(huì)阻止從插入的行上獲得鎖。然而，如果使用了 INSERT . SELECT ，則隔離級(jí)別將影響從 SELECT 獲得的 鎖。6. 日志記錄缺省情況下，每條 insert 都會(huì)被記錄下來，以用于恢復(fù)。日志記錄首先被寫到 內(nèi)存 中的日志緩沖池， 然后再寫到日志文件，通常是在日志緩沖池已滿或者發(fā)生了一次提交時(shí)寫到日志文件的。對(duì)批量插入的日 志記錄的優(yōu)化實(shí)際上就是最小化日志記錄寫的次數(shù)，以及使寫的速度盡可能快

51、。這里首先考慮的是日志緩沖池的大小， 這由數(shù)據(jù)庫配置參數(shù) LOGBUFSZ 來控制。 該參數(shù)缺省值為 8 頁或 32 K ，這與大多數(shù)批量插入所需的理想日志緩沖池大小相比要小些。舉個(gè)例子，對(duì)于一個(gè)批量插入， 假設(shè)對(duì)于每一行的日志內(nèi)容有 200 字節(jié)，則在插入了 160 行之后，日志緩沖池就將被填滿。如果要插入 1000 行， 因?yàn)槿罩揪彌_池將被填滿幾次， 再加上提交， 所以大概有 6 次日志寫。 如果將 LOGBUFSZ 的 值增加到64頁（256K）或者更大，緩沖池就不會(huì)被填滿， 這樣的話對(duì)于該批量插入就只有一次日志寫（在提交時(shí)）。通過使用更大的 LOGBUFSZ 可以獲得大約 13% 的性能提升。較大日志緩沖池的不利之處是， 緊急事故恢復(fù)所花的時(shí)間可能要稍微長一點(diǎn)。ALTER TABLE ACTIVATE NOT減少日志寫的另一種可能性是對(duì)新行要插入到的那個(gè)表使用LOGGED INITIALLY” （NLI） 。如果這樣做了，那么在該