資料庫查詢優化

考計算機三級 記錄下來學習的知識點

1.合理使用索引

索引的物理設計決策涉及以下幾類問題

① 是否為一個屬性建立索引

該屬性必須是一個碼，或者必須存在某個查詢，其選擇條件或連線條件使用了該屬性。之所以會建立多個索引。其因素之一有些查詢只需掃描索引即可處理，而無需檢索任何資料。

② 在那些屬性上建立索引

可以在一個或多個屬性上建立索引。如果一個關係的多個屬性共同出現在若干個查詢中，則一般會使用多屬性索引。多屬性索引中的屬性順序可以考慮按照其區分度進行排序。

③是否建立聚簇索引

是否建立聚簇索引。一個表中最多隻能有一個主索引或聚簇索引,因為該表必須按照這個屬性物理排序。

在大多數關聯式資料庫中,都用關鍵詞CLUSTER來指定聚簇索引(如果是碼屬性便建立主索引,如果不是碼屬性則建立聚簇索引)。

如果一個表中需要多個索引,那麼將哪個索引作為聚簇索引則取決於是否需要按照該屬性排序。

聚簇索引最適合用於範圍查詢。

如果有多個屬性需要範圍查詢,那麼選擇哪個索引作為聚簇索引則取決於是否要在該屬性上對資料進行排序。

如果一次查詢只進行一次索引排序(沒有資料記錄存取過程),則相應的索引不應該被聚簇,因為聚簇的最大優點體現在資料記錄存取過程中。當由複合鍵檢索的範圍用於報表建立時,聚簇索引就可能建立為多屬性索引。

④ 使用雜湊索引還是樹索引

關聯式資料庫管理系統中使用B+樹作為索引，某些系統也提供雜湊索引**。B+樹索引支援作為搜尋碼的屬性上的等值查詢和範圍查詢。**

雜湊索引應用於等值查詢，尤其是在連線操作中查詢匹配記錄時。

索引的使用要恰到好處，其使用原則一般如下

1. 經常在查詢中作為被使用的列，應為其建立索引

2. 頻繁進行排序或分組的列，應為其建立索引

3. 一個列的值域很大時，應為其建立索引

4. 如果待排序的列很多個，應為其建立索引

5. 可以使用系統工具來檢查索引的完整性，必要時進行修復。當資料庫表更新大量資料後，刪除並重建索引也可以提高查詢速度。

   ---

2.避免或簡化排序

在執行Order by 和group by的SQL語句時，會涉及排序的操作，**應當簡化或避免對大型表進行重複的排序，因為磁碟的開銷是很大的。**與記憶體排序相比較，磁碟排序操作很慢。從而會花費很長時間，降低資料庫效能，而且磁碟排序會消耗臨時表空間中的資源。當能夠利用索引自動以適當的次序產生輸出時,優化器就可以避免不必要的排序步驟。以下是一些影響因素:
①由於現有索引的不足,導致排序時索引中不包括一個或幾個待排序的列。
②group by或 order by子句中列的次序與索引的次序不一樣。
③排序的列來自不同的表。
為了避免不必要的排序,就要正確地增建索引,合理地合併資料庫表(儘管有時可能影響表的規範化,但相對於效率的提高是值得的)。如果排序不可避免,那麼應當試圖簡化它,如縮小排序列的範圍等。

3.消除對大型表資料的順序存取

**在巢狀查詢中,對錶的順序存取對查詢效率可能產生致命的影響。避免這種情況的主要方法就是對連線的列進行索引。**還可以使用並集來避免順序存取。儘管在所有的檢查列上都有索引,但某些形式的where子句強迫優化器使用順序存取.

4.避免複雜的正規表示式

在使用正規表示式進行條件查詢時可能會消耗較多的CPU資源進行字串匹配工作,因此應儘量避免。

5.使用臨時表加速查詢

把表的一個子集進行排序並建立臨時表,有時能加速查詢。它有助於避免多重排序操作,而且在其他方面還能簡化優化器的工作。臨時表中的行比主表中的行要少,而且順序就是所要求的順序,減少了磁碟的IO操作,所以查詢工作量可以得到大幅減少。

6.用排序來取代非順序磁碟存取

非順序磁碟存取是最慢的操作。但是在寫SQL語句時往往忽略了這一點,使得在寫應用程式時很容易寫出要求存取大量非順序頁的查詢,導致效率降低。有些時候,可以使用以資料庫排序功能為基礎的SQL來替代非順序的存取,以改進查詢效率。

7.不充分的連線條件

儘量不要使用左右連線，左右連線消耗的資源多，包含了NULL資料匹配的資料。儘可能使用內連線。

8.儲存過程

平時每次向資料庫傳送的SQL指令碼，都需要先編譯後執行。對於相同的sql語句效率很低。儲存過程不需要編譯就可以直接執行，因此速度很快。

**頻繁使用的sql語句建議使用儲存過程。**另外：注意在儲存過程中儘量使用SQL自帶的返回引數，而非自定義的返回引數，減少不必要的引數，避免資料冗餘。

9.不要隨意使用遊標

遊標會佔用較多的資源，尤其在大規模併發情況下，很容易使得系統因資源耗盡而崩潰。
遊標使用後應及時關閉和銷燬，以釋放資源。

10.事務處理

​ 多個處理放入事物中，系統的處理速度會有所下降，因此在保證資料庫一致性的前提下，將頻繁操作的多個可分割的處理過程放入到多個儲存過程中，這樣大大提高系統的響應速度。