數(shù)據(jù)庫SQL查詢處理及其優(yōu)化方法的研究

1、數(shù)據(jù)庫數(shù)據(jù)庫 sql 查詢處理及其優(yōu)化方法的研究查詢處理及其優(yōu)化方法的研究 摘要摘要：隨著計算機(jī)技術(shù)的發(fā)展和各種應(yīng)用軟件的廣泛使用，各種應(yīng)用數(shù)據(jù)也隨著日常工作而迅速增長， 數(shù)據(jù)管理的重要性也日益顯著，各種基于數(shù)據(jù)庫的聯(lián)機(jī)事務(wù)處理以及聯(lián)機(jī)分析處理已經(jīng)成為數(shù)據(jù)管理中 最重要的部分。大量的數(shù)據(jù)管理應(yīng)用軟件中，針對數(shù)據(jù)庫的各種操作，查詢操作所占的比重最大，如果 在查詢過程中使用高效的查詢策略，往往可以減少查詢代價，縮短查詢時間，提高查詢效率。論文基于 sql 語言和對數(shù)據(jù)各種操作分析的基礎(chǔ)上，探討了數(shù)據(jù)庫 sql 查詢處理的內(nèi)容和過程，詳細(xì)的研究了基 于 sql 查詢優(yōu)化的一些策略，其中主要包括索引查

2、詢、sql 語句優(yōu)化其他一些常用優(yōu)化方法。 關(guān)鍵詞：關(guān)鍵詞：數(shù)據(jù)庫 查詢優(yōu)化 索引 sql 語句 the research of database sql query processing and optimized method abstract: with the development of computer technology and various application software widely used, various application data also grow rapidly, the importance of data management increa

3、ses markedly too, various processes based on database online transaction processing and on-line analytical processing have become the most important parts of data management. among a lot of data management application software, pointing at various operations in the database, the proportion of the qu

4、ery operation is the biggest, if using efficient query execution, it can reduce cost, decrease query time and improve the query efficiency. based on the sql language and the analysis of various operations in database, this paper probes into the database sql query processing and details some strategi

5、es which based on the content and process of sql query optimization , these strategies mainly include index inquiry, sql optimization and other common optimization methods. key words：database；query optimization； index ； sql statements 目目 錄錄 1 緒論緒論.1 2 關(guān)系數(shù)據(jù)庫查詢處理關(guān)系數(shù)據(jù)庫查詢處理.2 2.1 查詢處理步驟.2 2.2 實現(xiàn)查詢操作的算法示

6、例.4 2.2.1 選擇操作的實現(xiàn).4 2.2.2 連接操作的實現(xiàn).5 3 sql 查詢處理優(yōu)化方法查詢處理優(yōu)化方法.6 3.1 基于索引的優(yōu)化.6 3.2 sql 語句優(yōu)化.9 3.2.1 where字句優(yōu)化.9 3.2.2 避免相關(guān)子查詢 .11 3.2.3 優(yōu)化表的連接條件.11 3.2.4 其他 sql 語句優(yōu)化 .12 3.3 其他優(yōu)化方法.12 3.3.1 避免或簡化排序 .13 3.3.2 使用臨時表.13 3.3.3 優(yōu)化表中數(shù)據(jù)類型.14 3.3.4 用排序來取代非順序存取 .14 3.3.5 數(shù)據(jù)服務(wù)器存儲 .14 4 實例分析實例分析.14 4.1 實驗環(huán)境.14 4.2

7、 案例數(shù)據(jù)庫.15 4.3 具體實例.16 結(jié)束語結(jié)束語.20 致謝致謝.21 參考文獻(xiàn)參考文獻(xiàn).22 1 緒論緒論 到如今，幾乎所有應(yīng)用系統(tǒng)的開發(fā)都離不開數(shù)據(jù)庫，通過查詢數(shù)據(jù)庫就可以有效的得到 想要的數(shù)據(jù)。但是，現(xiàn)實中許多數(shù)據(jù)庫開發(fā)人員在利用一些前端數(shù)據(jù)庫開發(fā)工具開發(fā)數(shù)據(jù)庫 應(yīng)用程序時只注重用戶界面的華麗，并不注重查詢效率，導(dǎo)致所開發(fā)出來的應(yīng)用系統(tǒng)中查詢 時間長，響應(yīng)速度慢，甚至查詢結(jié)果不夠準(zhǔn)確等，系統(tǒng)工作效率低下，資源浪費(fèi)嚴(yán)重。究其 原因，一是硬件設(shè)備（如 cpu、磁盤）的存取速度跟不上，內(nèi)存容量不夠大；另一方面是 數(shù)據(jù)查詢方法不適當(dāng)，抑或是沒有進(jìn)行數(shù)據(jù)查詢優(yōu)化。 許多數(shù)據(jù)庫開發(fā)人員認(rèn)為查

8、詢優(yōu)化是 dbms（數(shù)據(jù)庫管理系統(tǒng)）的任務(wù)，與程序員所編 寫的 sql 語句關(guān)系不大，這是不對的，一個好的查詢方法往往可以使程序性能提高數(shù)十倍。 在實際的數(shù)據(jù)庫產(chǎn)品（如 oracle、sybase、sql server 2000 等）的高版本中都是采用基于代 價的優(yōu)化方法，這種優(yōu)化能根據(jù)從系統(tǒng)字典表中所得到的信息來估計不同的查詢方法代價， 然后選擇一個較優(yōu)的規(guī)則。雖然現(xiàn)在的數(shù)據(jù)庫產(chǎn)品在數(shù)據(jù)查詢優(yōu)化方面已經(jīng)做得越來越好， 但由于用戶提交的 sql 語句是查詢優(yōu)化的基礎(chǔ)，因此用戶所寫語句的優(yōu)劣至關(guān)重要。 2 關(guān)系數(shù)據(jù)庫查詢處理關(guān)系數(shù)據(jù)庫查詢處理 要研究查詢優(yōu)化就必須知道數(shù)據(jù)庫查詢處理過程，本節(jié)闡述

9、了關(guān)系數(shù)據(jù)庫（rdbms） 的查詢處理步驟，并介紹了查詢處理的任務(wù)是把用戶提交給 rdbms 的查詢語句轉(zhuǎn)換為高效 的執(zhí)行計劃。 2.1 查詢處理步驟查詢處理步驟 rdbms 查詢處理過程可以分為四個階段：查詢分析、查詢檢查、查詢優(yōu)化和查詢執(zhí)行， 如圖 2-1 所示。 (1) 查詢分析 查詢分析是查詢處理的第一個階段，主要任務(wù)是對查詢語句進(jìn)行掃描、詞法分析和語法 分析。從查詢語句中識別出語言符號，sql 關(guān)鍵字、屬性名和關(guān)系名等，并且進(jìn)行語法檢查 和語法分析，即判斷查詢語句是否符合 sql 語法規(guī)則。 (2) 查詢檢查 查詢檢查是根據(jù)數(shù)據(jù)字典對合法的 sql 查詢語句進(jìn)行語義檢查，即檢查語句中

10、的數(shù)據(jù) 庫對象，如屬性名、關(guān)系名，是否存在和是否有效等。還要根據(jù)數(shù)據(jù)字典中的用戶權(quán)限和完 整性約束對用戶的存取權(quán)限進(jìn)行檢查。如果該用戶沒有相應(yīng)的訪問權(quán)限或違反了完整性約束， 就拒絕執(zhí)行該查詢操作。檢查通過后便把 sql 查詢語句轉(zhuǎn)換成等價的關(guān)系代數(shù)表達(dá)式。 rdbms 一般都用查詢樹（query tree）,也稱為語法分析樹，來表示擴(kuò)展的關(guān)系代數(shù)表達(dá)式。 這個過程中要把數(shù)據(jù)庫對象的外部名稱轉(zhuǎn)換為內(nèi)部表示。 詞法分析 語法分析 語義分析 符號名轉(zhuǎn)換 安全性檢查 完整性檢查 查詢樹（query tree） 代數(shù)優(yōu)化 物理優(yōu)化等 執(zhí)行策略描述 代碼生成 查詢計劃的執(zhí)行代碼 數(shù)據(jù)庫 數(shù)據(jù)字典 查詢語句

11、 查詢分析 查詢檢查 查詢優(yōu)化 查詢執(zhí)行 圖 2-1 查詢處理步驟 (3) 查詢優(yōu)化 每個查詢語句都會有很多可供選擇的執(zhí)行策略和操作算法，查詢優(yōu)化（query optimization）就是選擇一個高效的查詢處理策略。查詢優(yōu)化有許多種方法。按照優(yōu)化的層次 一般可以分為代數(shù)優(yōu)化和物理優(yōu)化。代數(shù)優(yōu)化是指關(guān)系代數(shù)表達(dá)式的優(yōu)化，即按照一定的規(guī) 則，改變代數(shù)表達(dá)式中操作的次序和組合，使查詢執(zhí)行更高效；物理優(yōu)化則是指存取路徑和 底層操作算法的選擇。選擇的依據(jù)可以是基于規(guī)則的，也可以基于代價的，還可以基于語義 的。 實際 rdbms 中的查詢優(yōu)化器都綜合了運(yùn)用了這些優(yōu)化技術(shù)，以獲得最好的查詢優(yōu)化效 果。 (

12、4) 查詢執(zhí)行 查詢執(zhí)行就是依據(jù)優(yōu)化器得到的執(zhí)行策略生成查詢計劃，由代碼生成器（code generator）生成執(zhí)行這個查詢計劃的代碼。 2.2 實現(xiàn)查詢操作的算法示例實現(xiàn)查詢操作的算法示例 選擇操作和連接操作是查詢操作的兩個典型操作，每一種操作有多種執(zhí)行這個操作的算 法，下面探討實現(xiàn)這兩種操作的幾個主要算法。 2.2.1 選擇操作的實現(xiàn)選擇操作的實現(xiàn) 眾所周知 select 語句功能十分強(qiáng)大，有許多選項，因此實現(xiàn)的算法和優(yōu)化策略也很 復(fù)雜。下面以簡單的選擇操作為例講述典型的實現(xiàn)方法。 例 1 select * from student where； 考慮的幾種情況： c1：無條件； c2：

13、sno=200215121； c3：sage20； c4: sdept=cs and sage20； （1）簡單的全表掃描方法 對查詢的基本表順序掃描，逐一檢查每個元組是否滿足選擇條件，把滿足條件的元組作 為結(jié)果輸出。對于小表，這種方法簡單有效。對于大表順序掃描十分費(fèi)時，效率很低。 （2）索引（或散列）掃描方法 如果選擇條件中的屬性上有索引（例如 b+樹索引或 hash 索引） ，可以用索引掃描方法。 通過索引先找到滿足條件的元組主碼或元組指針，再通過元組指針直接在要查詢的基本表中 找到元組。 例 1-c2 以 c2 為例，sno=200215121，并且 sno 上有索引，則可以通過使用索引

14、得 到 sno 為200215121元組的指針，然后通過元組指針在 student 表中檢索等到該學(xué)生。 例 1-c3 以 c3 為例，sage20，并且 sage 上有 b+樹索引，則可以使用 b+樹索引找到 sage=20 的索引項，以此為入口在 b+樹的順序集上得到 sage20 的所有元組指針，然后通過 這些元組指針到 student 表中檢索所有年齡大于 20 的學(xué)生。 例 1-c4 以 c4 為例，sdept=csand sage20,如果 sdept 和 sage 上都有索引，一種算 法是：分別用上面的兩種方法分別找到 sdept=cs的一組元組指針和 sage20 的另一組元組

15、 指針，求這兩組指針的交集，再到 student 表中檢索，就得到計算機(jī)系年齡大于 20 的學(xué)生。 另一種算法是：找到 sdept=cs一組元組指針，通過這些元組指針到 student 表中檢索， 并對得到的元組檢查另一些選擇條件是否滿足，把滿足條件的元組作為結(jié)果輸出。 2.2.2 連接操作的實現(xiàn)連接操作的實現(xiàn) 連接操作是查詢處理中最耗時的操作之一。不失一般性，本文只討論等值連接最常用的 實現(xiàn)算法。 例 2 select * from student，sc where student.sno=sc.sno; （1）嵌套循環(huán)方法 這是最簡單可行的算法。對外層循環(huán)（student）的每一個元組（s

16、）,檢索內(nèi)層循環(huán) （sc）中的每一個元組（sc）,并檢查這兩個元組在連接屬性（sno）上是否相等。如果滿足 連接條件，則串接后作為結(jié)果輸出，直到外層循環(huán)表中的元組處理完為止。 （2）排序-合并方法 這也是最常用的算法，尤其適合連接的諸表已經(jīng)排好序的情況。 用排序-合并連接方法的步驟是： 如果連接的表沒有排好序，首先對 student 表和 sc 表按連接屬性 sno 排序； 取 student 中的第一個 sno，依次掃描 sc 表中具有相同的 sno 的元組；把它們連接起 來； 當(dāng)掃描到 sno 不相同的第一個 sc 元組時，返回 student 表掃描它的下一個元組；再 掃描 sc 表中具

17、有相同的 sno 的元組，把它們連接起來。 重復(fù)上述步驟直到 student 表掃描完。 這樣 student 表和 sc 表都只要掃描一遍。當(dāng)然，如果 2 個表原來無序，執(zhí)行時間要加上 對兩個表的排序時間。即使這樣，對于 2 個大表，先排序后使用 sort-merge join 方法執(zhí)行連 接，總的時間一般仍會大大減少。 （3）索引連接方法 用索引連接方法的步驟是： 在 sc 表上建立屬性 sno 的索引，如果原來沒有的話； 對 student 中的每一個元組，由 sno 值通過 sc 的索引查找相應(yīng)的 sc 元組； 把這些 sc 元組和 student 表中的元組連接起來。 循環(huán)執(zhí)行，直到

18、 student 表中的元組處理完為止。 （4）hash join 方法 屬性作為 hash 碼，用同一個 hash 函數(shù)把 r 和 s 中的元組散列到同一個 hash 文件中。第 一步，劃分階段，對包含較少元組的表進(jìn)行一遍處理，把它的元組按 hash 函數(shù)分散到 hash 表的桶中；第二步，試探階段，也稱為連接階段，對另一表（s）進(jìn)行一遍處理，把 s 的元 組散列到適當(dāng)?shù)?hash 桶中，并把元組與桶中所有來自 r 并與之相匹配的元組連接起來。 3 sql 查詢處理優(yōu)化方法查詢處理優(yōu)化方法 查詢優(yōu)化在關(guān)系數(shù)據(jù)庫系統(tǒng)中有著非常重要的地位，關(guān)系數(shù)據(jù)庫系統(tǒng)和非過程化的 sql 之所以能取得巨大的成

19、功，關(guān)鍵得益于查詢優(yōu)化技術(shù)的發(fā)展。關(guān)系查詢優(yōu)化是影響 rdbms 性能的關(guān)鍵因素。 查詢優(yōu)化既是 rdbms 實現(xiàn)的關(guān)鍵又是關(guān)系數(shù)據(jù)庫的優(yōu)點(diǎn)所在。它減輕了用戶選擇存取 路徑的負(fù)擔(dān)。用戶只要提出“干什么” ，不必指出“怎么干” 。對比一下非關(guān)系系統(tǒng)中的情況： 用戶使用過程化的語言表達(dá)查詢要求，執(zhí)行何種記錄級的操作，以及操作的序列是由用戶而 不是由系統(tǒng)來決定的。因此用戶必須了解存取路徑，系統(tǒng)要提供用戶選擇存取路徑的手段， 查詢效率由用戶的存取策略決定。如果用戶做了不當(dāng)?shù)倪x擇，系統(tǒng)是無法對此加以改進(jìn)的。 這就要求用戶有較高的數(shù)據(jù)庫技術(shù)和程序設(shè)計水平。下面介紹幾種常用的查詢優(yōu)化方法。 3.13.1 基

20、于索引的優(yōu)化基于索引的優(yōu)化 （1）索引定義 索引是一個單獨(dú)的、物理的數(shù)據(jù)庫結(jié)構(gòu)。它是根據(jù)表中一列或若干列，按照一定順序建 立的列值與記錄行之間的對應(yīng)關(guān)系表。 索引是依賴于表建立的，它包含索引鍵值及指向數(shù)據(jù)所在頁面和行的指針。一個表的存 儲是由兩部分組成的，一部分用來存放表的數(shù)據(jù)頁面，另一部分存放索引頁面，索引就存放 在索引頁面上。通常，索引頁面相對于數(shù)據(jù)頁面來說要小得多。當(dāng)進(jìn)行數(shù)據(jù)檢索時，系統(tǒng)先 搜索索引頁面，從中找到所需數(shù)據(jù)的指針，然后再直接通過指針從數(shù)據(jù)頁面中讀取數(shù)據(jù)。 索引可以提供對一個表中的數(shù)據(jù)的有效訪問，它可以用于加速數(shù)據(jù)的檢索和強(qiáng)制唯一性 限制。但是，不應(yīng)該在每一個列上都建立索引，

21、因為構(gòu)造索引需要占用一定的系統(tǒng)資源，降 低更新的速度。而且，插入、刪除或更新一個索引列中的數(shù)據(jù)比非索引列中的數(shù)據(jù)要花費(fèi)更 長的時間。 （2）索引的作用 索引是加快數(shù)據(jù)檢索的一種數(shù)據(jù)庫結(jié)構(gòu)，使得數(shù)據(jù)查詢時不必掃描整個數(shù)據(jù)庫就能迅速 查到想要的數(shù)據(jù)。具體如下 5 個方面： 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。 可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。 可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。 在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時，同樣可以減少查詢中分組和排序的時間。 通過使用索引，可以在查詢的過程中，使用優(yōu)化器隱藏，提高系統(tǒng)的性能

22、。 （3）索引的類型 如果一個表沒有創(chuàng)建索引，則數(shù)據(jù)行不按任何特定的順序存儲，這種結(jié)構(gòu)稱為堆集。在 sql server 2000 的數(shù)據(jù)庫中，按存儲結(jié)構(gòu)的不同將索引分為兩類：簇索引（clustered index）和非簇索引(nonclustered index)。 1. 簇索引 簇索引對表的數(shù)據(jù)行的鍵值進(jìn)行排序，然后再存儲有用的數(shù)據(jù)記錄。由于簇索引對表中 的數(shù)據(jù)一一進(jìn)行了排序，因此用簇索引查找數(shù)據(jù)很快。但由于簇索引將表中的所有數(shù)據(jù)完全 重新排列了，它所需要的空間也就特別大，大約相當(dāng)于表中數(shù)據(jù)所占空間的 120%。表的數(shù) 據(jù)行只能以一種排序方式存儲在磁盤上，所以一個表只能有一個簇索引。 2.

23、 非簇索引 非簇索引具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)，使用非簇索引不用對表的數(shù)據(jù)行的鍵值進(jìn)行排 序。非簇索引的 b-樹葉節(jié)點(diǎn)存儲了組成非簇索引的鍵值和行定位器（從索引行指向數(shù)據(jù)行 的指針稱為行定位器） ，行定位器的結(jié)構(gòu)和存儲內(nèi)容取決于數(shù)據(jù)的存儲方式，如果數(shù)據(jù)是以 索引方式存儲的則行定位器中存儲的是簇索引的索引鍵；如果不是以索引方式存儲的，這種 方式稱為堆存儲方式（heap structure）,則行定位器中存儲的是指向數(shù)據(jù)行的指針。非簇索 引將行定位器的鍵值用一定的方式排序，這個順序與表的行在數(shù)據(jù)頁中的排序是不匹配的。 由于非簇索引使用索引頁存儲，因此簇索引需要更多的空間，且檢索效率較低。但一個 表

24、只能建一個簇索引，當(dāng)用戶需要建立多個索引時，就需要使用非簇索引了。從理論上講， 一個表最多可以建 248 個非簇索引。 對于何時使用簇索引、何時使用非簇索引如表 3-1 所示 表 3-1 使用簇索引或非簇索引的時機(jī) 動作描述使用簇索引使用非簇索引 列經(jīng)常被分組排序應(yīng)應(yīng) 返回某范圍內(nèi)的數(shù)據(jù)應(yīng)不應(yīng) 一個或極少不同值不應(yīng)不應(yīng) 小數(shù)目的不同值應(yīng)不應(yīng) 大數(shù)目的不同值不應(yīng)應(yīng) 頻繁更新的列不應(yīng)應(yīng) 外鍵列應(yīng)應(yīng) 主鍵列應(yīng)應(yīng) 頻繁修改索引列不應(yīng)應(yīng) （4）索引的建立與刪除 一般來說，建立與刪除索引由數(shù)據(jù)庫管理員 dba 或表的屬主（owner） ，即建表的人負(fù) 責(zé)完成。系統(tǒng)在存取數(shù)據(jù)時會自動選擇合適的索引作為存取路

25、徑，用戶不必也不能顯式地選 擇索引。 1.建立索引 在 sql 語言中，建立索引使用 create index 語句，其一般格式為： create unique cluster index on （，） 其中，是要建索引的基本表的名字。索引可以建立在該表的一列或多列上，各列 名之間用逗號分隔。每個后面還可以用指定索引值的排列次序，可選 asc（升序）或 desc（降序） ，缺省值為 asc。 unique 表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄。 cluster 表示要建立的索引是聚簇索引。 例 1 create cluster index stusname on student(sna

26、me); 這條語句是在 student 表的 sname（姓名）列上建立一個聚簇索引，而且 student 表中的 記錄將會按照 sname 值的升序存放。 例 2 create unique index stusno on student(sno); create unique index coucno on student(cno); create unique index scno on student(sno asc,cno desc); 這三條語句是為學(xué)生-課程數(shù)據(jù)庫中的 student，course，sc 3 個表建立索引。其中 student 表按學(xué)號升序建唯一索引，course

27、 表按課程號升序建唯一索引，sc 表按學(xué)號升序和 課程號降序建唯一索引。 2.刪除索引 索引一經(jīng)建立，就由系統(tǒng)使用和維護(hù)它，不需用戶干預(yù)。建立索引是為了減少查詢操作 的時間，但如果數(shù)據(jù)增刪改頻繁，系統(tǒng)會花費(fèi)很多時間來維護(hù)索引，從而降低了查詢效率。 這時可以刪除一些不必要的索引。 在 sql 中，刪除索引使用 drop index 語句，其一般格式為 drop index ； 例 3 刪除 student 表的 stusname 索引。 drop index stusname; 刪除索引時，系統(tǒng)會同時從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。 3.2 sql 語句語句優(yōu)化優(yōu)化 使用索引可以有效的提高查詢

28、速度，但是 sql 語句是對數(shù)據(jù)庫操作的唯一途徑，程序 的執(zhí)行最終都?xì)w結(jié)為 sql 語句的執(zhí)行，所以 sql 語句的執(zhí)行效率對數(shù)據(jù)庫系統(tǒng)的性能起了 決定性的作用。所以我們不但要會寫 sql 語句，還要寫出性能優(yōu)良的 sql 語句。 對于優(yōu)化 sql 語句，本論文主要就避免相關(guān)子查詢、where 字句的優(yōu)化以及幾個表的連 接條件這幾個方面進(jìn)行闡述。 3.2.1 where 字句優(yōu)化字句優(yōu)化 在 where 子句中優(yōu)化 sql 語句是 sql 語句優(yōu)化的重要部分，它包括很多內(nèi)容，這里只 介紹幾種常用的優(yōu)化原則。 1.應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使

29、用索引 而進(jìn)行全表掃描，如： select id from t where num is null 可以在 num 上設(shè)置默認(rèn)值 0，確保表中 num 列沒有 null 值，然后這樣查詢： select id from t where num=0 2.應(yīng)盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進(jìn)行全 表掃描。優(yōu)化器將無法通過索引來確定將要命中的行數(shù),因此需要搜索該表的所有行。 3.應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而 進(jìn)行全表掃描，如： select id from t where num=10 or num=20

30、 可以這樣查詢： select id from t where num=10 union all select id from t where num=20 4.in 和 not in 也要慎用，因為 in 會使系統(tǒng)無法使用索引,而只能直接搜索表中的數(shù)據(jù)。 如： select id from t where num in(1,2,3) 對于連續(xù)的數(shù)值，能用 between 就不要用 in 了： select id from t where num between 1 and 3 5.應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而 進(jìn)行全表掃描。如： selec

31、t * from t1 where f1/2=100 應(yīng)改為: select * from t1 where f1=100*2 select * from record where substring(card_no,1,4)=5378 應(yīng)改為: select * from record where card_no like 5378% select member_number, first_name, last_name from members where datediff(yy,datofbirth,getdate() 21 應(yīng)改為: select member_number, firs

32、t_name, last_name from members where dateofbirth =2005-11-30 and createdate 10 group by orderid 可改為： select distinct orderid from details where unitprice 10 2.能用 union all 就不要用 union union all 不執(zhí)行 select distinct 函數(shù)，這樣就會減少很多不必要的資源 3.盡量不要用 select into 語句。 select inot 語句會導(dǎo)致表鎖定，阻止其他用戶訪問該表。 4.in、or 子句常會

33、使用工作表，使索引失效。如果不產(chǎn)生大量重復(fù)值，可以考慮把子 句拆開。拆開的子句中應(yīng)該包含索引。 5.set show plan_all on 查看執(zhí)行方案。dbcc 檢查數(shù)據(jù)庫數(shù)據(jù)完整性。 dbcc(database consistency checker)是一組用于驗證 sql server 數(shù)據(jù)庫完整性的程序。 6.慎用游標(biāo) 在某些必須使用游標(biāo)的場合，可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時表中，再對臨時表定 義游標(biāo)并進(jìn)行操作，這樣可使性能得到明顯提高。 上面我們講述的是一些基本的提高查詢速度的方法,但是在更多的情況下,往往需要反復(fù) 試驗比較不同的語句以得到最佳方案。最好的方法當(dāng)然是測試，看實現(xiàn)相

34、同功能的 sql 語句 哪個執(zhí)行時間最少，但是如果數(shù)據(jù)庫中數(shù)據(jù)量很少，是比較不出來的，這時可以用查看執(zhí)行 計劃，即：把實現(xiàn)相同功能的多條 sql 語句拷到查詢分析器，按 ctrl+l 查看所利用的索引 以及表掃描次數(shù)（這兩個對性能影響最大） ，總體上看成本百分比即可。 3.3 其他優(yōu)化方法其他優(yōu)化方法 數(shù)據(jù)庫的查詢優(yōu)化方法除了索引和優(yōu)化 sql 語句還有其他的方法，其他方法的合理使用 同樣也能很好的對數(shù)據(jù)庫查詢起到優(yōu)化作用。我們就來列舉幾種簡單實用的方法。 3.3.1 避免或簡化排序避免或簡化排序 應(yīng)當(dāng)簡化或避免對大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動以適當(dāng)?shù)拇涡虍a(chǎn)生輸出 時，優(yōu)化器就避免了

35、排序的步驟。以下是一些影響因素: 索引中不包括一個或幾個待排序的列; group by 或 order by 子句中列的次序與索引的次序不一樣; 排序的列來自不同的表。 為了避免不必要的排序，就要正確地增減索引，合理地合并數(shù)據(jù)庫表(盡管有時可能影 響表的規(guī)范化，但相對于效率的提高是值得的)。如果排序不可避免，那么應(yīng)當(dāng)試圖簡化它， 如縮小排序的列的范圍等。 3.3.2 使用臨時表使用臨時表 臨時表中的行比主表中的行要少，而且物理順序就是所要求的順序，減少磁盤的 i/o 操 作，查詢工作量可以大幅減少。 在表的一個子集進(jìn)行排序并創(chuàng)建臨時表，也能實現(xiàn)加速查詢。在一些情況下這樣可以避 免多重排序操作。

36、但所創(chuàng)建的臨時表的行要比主表的行少，其物理順序就是所要求的順序， 這樣就減少了輸入和輸出，降低了查詢的工作量，提高了效率，而且臨時表的創(chuàng)建并不會反 映主表的修改。 但是對臨時表的使用也要有一些規(guī)則，主要有： 1.盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只 有主鍵索引） 。 2.避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗。 3.臨時表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行?，例如，?dāng)需要重復(fù) 引用大型表或常用表中的某個數(shù)據(jù)集時。但是，對于一次性事件，最好使用導(dǎo)出表。 4.在新建臨時表時，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into

37、 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先 create table，然后 insert。 5.如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定。 3.3.3 優(yōu)化表中數(shù)據(jù)類型優(yōu)化表中數(shù)據(jù)類型 對表中數(shù)據(jù)最好使用兼容的數(shù)據(jù)類型，因為數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一 些本來可以進(jìn)行的優(yōu)化操作。例如 float 和 int、char 和 varchar、binary 和 varbinary 是不兼容 的。下面用

38、例子來闡述 select name from employee where salary 60000 在這條語句中,如 salary 字段是 money 型的,則優(yōu)化器很難對其進(jìn)行優(yōu)化,因為 60000 是 個整型數(shù)。我們應(yīng)當(dāng)在編程時將整型轉(zhuǎn)化成為錢幣型,而不要等到運(yùn)行時轉(zhuǎn)化。 3.3.4 用排序來取代非順序存取用排序來取代非順序存取 磁盤存取臂的來回移動使得非順序磁盤存取變成了最慢的操作。但是在 sql 語句中這 個現(xiàn)象被隱藏了，這樣就使得查詢中進(jìn)行了大量的非順序查詢，降低了查詢速度，對于這個 現(xiàn)象還沒有很好的解決方法，只能依賴于數(shù)據(jù)庫的排序能力來替代非順序的存取。有些時候， 用數(shù)據(jù)庫的排序

39、能力來替代非順序的存取能改進(jìn)查詢效率。 3.3.5 數(shù)據(jù)服務(wù)器存儲數(shù)據(jù)服務(wù)器存儲 盡量將數(shù)據(jù)的處理工作放在服務(wù)器上，減少網(wǎng)絡(luò)的開銷，如使用存儲過程。存儲過程是 編譯好、優(yōu)化過并且被組織到一個執(zhí)行規(guī)劃里、且存儲在數(shù)據(jù)庫中的 sql 語句，是控制流 語言的集合，速度當(dāng)然快。 4 實例分析實例分析 第三章研究了數(shù)據(jù)庫查詢處理過程以及一些 sql 優(yōu)化方法，本章將在第三章研究的基礎(chǔ) 上，利用案例數(shù)據(jù)庫進(jìn)行對上述三類 sql 查詢優(yōu)化的有效性進(jìn)行實驗驗證，并對實驗結(jié)果分 析。 4.1 實驗環(huán)境實驗環(huán)境 操作系統(tǒng)：windows xp 數(shù)據(jù)庫管理軟件：sql server 2000 內(nèi)存：2g cpu：i

40、ntel(r) pentium(r) dual cpu e2160 1.80ghz 4.2 案例數(shù)據(jù)庫案例數(shù)據(jù)庫 為了進(jìn)行實驗分析，本文建立了案例數(shù)據(jù)庫，包括 company，house、sale 三張表，如 表 4-1、4-2、4-3 所示，案例數(shù)據(jù)庫的數(shù)據(jù)概況如表 4-4 所示。 表 4-1 company 數(shù)據(jù)表定義 列名數(shù)據(jù)結(jié)構(gòu)長度說明 company_idint4編號，主鍵 company_namenvarchar255公司名稱 company_distrisct_idint4公司所在行政區(qū)域編號，外鍵 company_attribute_idint4公司所在性質(zhì)類別編號，外鍵 表

41、4-2 house 數(shù)據(jù)表定義 列名數(shù)據(jù)結(jié)構(gòu)長度說明 house _idint4編號，主鍵 house_ namenvarchar255樓盤名稱 occupy_areafloat8樓盤占地面積 afforest_areafloat8樓盤綠化面積 build_costmoney8樓盤建筑成本 sum_areafloat8樓盤建筑面積 sale_able_areafloat8樓盤可售面積 company_idint4樓盤公司編號，外鍵 section_idint4樓盤所在地理區(qū)域編號，外鍵 type_idint4樓盤類型編號，外鍵 distrisct_idint4樓盤所在行政區(qū)域編號，外鍵 sum

42、_ build_costmoney8樓盤總建筑成本 表 4-3 sale 數(shù)據(jù)表定義 列名數(shù)據(jù)結(jié)構(gòu)長度說明 sale _idint4編號，主鍵 house_ idint4樓盤編號，外鍵 time_idint4樓盤銷售時間編號，外鍵 sale_areafloat8樓盤銷售面積 sale_pricefloat8樓盤銷售價格 案例數(shù)據(jù)的數(shù)據(jù)該庫如表 4-4 所示 表 4-4 數(shù)據(jù)庫數(shù)據(jù)量概況 數(shù)據(jù)表名數(shù)據(jù)量 company91 house300 sale3590 4.3 具體實例具體實例 （1）基于索引優(yōu)化實驗 索引優(yōu)化是數(shù)據(jù)庫 sql 查詢優(yōu)化的重要方法，為了證明其有效性，本實驗在 company

43、 和 house 表的 company_id 列上建立了索引，以下面 sql 語句為例，比較索引建立前后執(zhí) 行時間的變化。 sql 語句：select getdate() select * from company,house where company.company_id=house.company_id select getdate() 此語句的功能是先獲取系統(tǒng)的當(dāng)前時間，然后查詢 company 和 house 表中公司編號 （company_id）相等的元組，并把兩表中這些元組的信息連接起來返回，最后再獲取系統(tǒng) 當(dāng)前時間，兩次時間相減就是 sql 語句執(zhí)行時間。 在 company_

44、id 列上未建立索引時系統(tǒng)執(zhí)行情況如圖 4-1 所示 圖 4-1 未建立索引系統(tǒng)執(zhí)行情況圖 由圖 4-1 可知，sql 語句執(zhí)行前的系統(tǒng)時間為 19:29:36.013，sql 語句執(zhí)行后的系統(tǒng)時 間為 19:29:36.030，兩次時間相減就是 0.017s，故 sql 語句執(zhí)行時間為 0.017s。 在 company_id 列上建立索引時系統(tǒng)執(zhí)行情況如圖 4-4 所示 圖 4-2 建立索引系統(tǒng)執(zhí)行情況圖 由圖 4-2 可知，sql 語句執(zhí)行前的系統(tǒng)時間為 19:45:01.060，sql 語句執(zhí)行后的系統(tǒng)時 間為 19:45:01.060，兩次時間相減就是 0.000s. 由上實驗可以知

45、道，建立索引時，sql 語句執(zhí)行時間約為 0.000s，而未建立索引時系統(tǒng) 的查詢時間為 0.017s,很明顯建立索引可以顯著的提高系統(tǒng)執(zhí)行效率，但是要是建立了不適 當(dāng)?shù)乃饕?，不但會浪費(fèi)系統(tǒng)資源，還會降低查詢性能。 （2）嵌套優(yōu)化實驗 由第三章的理論研究可知，當(dāng)查詢語句中包括嵌套查詢時，會降低查詢效率。本實驗分 別用嵌套的 sql 語句和沒有嵌套的 sql 語句來實現(xiàn)相同的功能，比較這兩者的執(zhí)行時間。 用 in 的嵌套 sql 語句查詢 sql 語句為：select getdate() select * from company,house where company.company_id i

46、n （select house.company_id from house） select getdate() 此語句的功能是先獲取系統(tǒng)的當(dāng)前時間，然后查詢 company 和 house 表中公司編號 （company_id）相等的元組，并把兩表中這些元組的信息連接起來返回，最后再獲取系統(tǒng) 當(dāng)前時間，兩次時間相減就是 sql 語句執(zhí)行時間。 系統(tǒng)執(zhí)行情況如圖 4-3 所示 圖 4-3 嵌套 sql 查詢系統(tǒng)執(zhí)行情況圖 由圖 4-3 可知，sql 語句執(zhí)行前的系統(tǒng)時間為 19:54:40.513，sql 語句執(zhí)行后的系統(tǒng)時 間為 19:54:41.230，兩次時間相減就是 0.717s，故 s

47、ql 語句執(zhí)行時間為 0.717s。 沒有嵌套的 sql 語句查詢 sql 語句：select getdate() select * from company,house where company.company_id=house.company_id select getdate() 此語句的功能如，系統(tǒng)執(zhí)行情況如圖 4-4 所示 圖 4-4 嵌套 sql 查詢系統(tǒng)執(zhí)行情況圖 由圖 4-4 可知，sql 語句執(zhí)行前的系統(tǒng)時間為 19:55:56.043，sql 語句執(zhí)行后的系統(tǒng)時 間為 19:55:56.060，兩次時間相減就是 0.017s，故 sql 語句執(zhí)行時間為 0.017s。 由

48、上實驗可以知道，用 in 實現(xiàn)嵌套查詢時，sql 語句執(zhí)行時間約為 0.717s，而不采用 嵌套查詢時，系統(tǒng)的執(zhí)行時間為 0.017s, 由其可見，不使用 in 可以提高查詢性能，因為用 in 實現(xiàn)嵌套查詢時會使系統(tǒng)無法使用索引,只能對表中的數(shù)據(jù)全部搜索這將導(dǎo)致系統(tǒng)效率降低。 （3）表達(dá)式優(yōu)化實驗 由第三章可知在 where 子句中對字段進(jìn)行表達(dá)式操作會降低查詢性能，本實驗分別在 where 字句中進(jìn)行表達(dá)式操作和不進(jìn)行表達(dá)式操作，比較兩者的執(zhí)行時間。 在 where 字句中未進(jìn)行表達(dá)式操作 sql 語句：select getdate() select * from sale，house wh

49、ere sale.sale_area1500 and house.house_id=sale. house_id select getdate() 該語句的功能是在 house 和 sale 兩個表中找到 house_id 相等的元組，然后再在這些元 組中找出樓盤銷售面積大于 1500 的元組。 系統(tǒng)執(zhí)行情況如圖 4-5 所示 圖 4-5 未進(jìn)行表達(dá)式操作的系統(tǒng)執(zhí)行情況圖 由圖 4-5 可知，sql 語句執(zhí)行前的系統(tǒng)時間為 20:40:08.640，sql 語句執(zhí)行后的系統(tǒng)時 間為 20:40:08.780，兩次時間相減就是 0.140s，故 sql 語句執(zhí)行時間為 0.140s 在 where 字句中進(jìn)行表達(dá)式操作 sql 語句：select getdate() select * from sale