SQL語句及資料庫最佳化

標籤：sql最佳化

 1，統一SQL語句的寫法

對於以下兩句SQL語句，程式員認為是相同的，資料庫查詢最佳化工具認為是不同的。 所以封裝成複用方法，用標準模板來控制。

select*from dual 

select*From dual

其實就是大小寫不同，查詢分析器就認為是兩句不同的SQL語句，必須進行兩次解析。產生2個執行計畫

2，不要把SQL語句寫得太複雜

我經常看到，從資料庫中捕捉到的一條SQL語句列印出來有2張A4紙這麼長。一般來說這麼複雜的語句通常都是有問題的。我拿著這2頁長的SQL語句去請教原作者，結果他說時間太長，他一時也看不懂了。可想而知，連原作者都有可能看糊塗的SQL語句，資料庫也一樣會看糊塗。

 

比如 Select語句的結果作為子集

簡化SQL語句的重要方法就是採用暫存資料表暫存中間結果，但是，暫存資料表的好處遠遠不止這些，將臨時結果暫存在暫存資料表，後面的查詢就在tempdb中了，這可以避免程式中多次掃描主表 也大大減少了程式執行中“共用鎖定”阻塞“更新鎖定”，減少了阻塞，提高了並發效能。

 

3，必須採用綁定變數 

select*from orderheader where changetime >‘2010-10-20 00:00:01‘ 
select*from orderheader where changetime >‘2010-09-22 00:00:01‘

以上兩句語句，查詢最佳化工具認為是不同的SQL語句，需要解析兩次。如果採用綁定變數

select*from orderheader where changetime >@chgtime

 

4，使用like進行模糊查詢時應注意

有的時候會需要進行一些模糊查詢比如

select*from contact where username like ‘%yue%’

 關鍵詞%yue%，由於yue前面用到了“%”，因此該查詢必然走全表掃描，除非必要，否則不要在關鍵詞前加%，

 

5，聯表查詢

(1)    串連欄位盡量選擇叢集索引所在的欄位

(2)    仔細考慮where條件，盡量減小A、B表的結果集

 擷取  springmvc+mybatis+spring 整合 bootstrap html5

6，索引，

看sql 的效能，主要看執行計畫，還有cpu成本，io成本等。這裡就以一個簡單的表為例。

首先，建立一個簡單的表，一般會先建個主鍵，系統自動以主鍵建叢集索引。

判斷是否需要最佳化sql的一個簡單規則是：看執行計畫中的操作是seek(搜尋)還是scan（掃描）

是scan的話就要索引。

 

使用情境：

當一個系統查詢比較頻繁，而建立，修改等操作比較少時，可以建立覆蓋索引，將查詢欄位和where子句裡的欄位全部包含在內，這樣查詢的速度會比以前快很多，同時也帶來弊端，就是建立或修改等操作時，比沒有索引或沒有建立覆蓋索引時的要慢。讀寫資料庫分離也能解決問題

 

經常對Creator_Id欄位查詢，就做個索引。

對錶Article的Creator_Id欄位建索引

CREATE INDEX Ix_article_creatorid ON Article(Creator_Id)

set statistics io 和 set statistics，這是效能調優時查看相關cpu佔用時間，IO資源資料的兩個比較重要的命令

 

記得Order by 語句加索引

 

7，讀寫分離

 

當主要資料庫進行寫操作時，資料要同步到從的資料庫，這樣才能有效保證資料庫完整性

主從分離，對資料庫層面就是資料同步或者是資料複製；從應用程式層講就是請求的分離：增刪改請求主庫，查詢請求從庫

 

8，盡量不用select * from …..

 

,而要寫欄位名 select field1,field2,…這條沒什麼好說的，主要是按需查詢，不要返回不必要的列和行。

 

9 任何對列的操作都將導致表掃描，

它包括資料庫函數、計算運算式等，查詢時要儘可能將操作移至等號右邊

10 In 、or子句常會使索引失效

顯而易見的，IN,OR擴大的查詢範圍。

11通常情況下，串連比子查詢效率要高

必然的，需要子查詢時，也是用暫存資料表暫存中間結果

SQL語句及資料庫最佳化