軟體系統效能最佳化策略–SQL最佳化

  非常感謝大家對《大白話系列之C#委託與事件講解》的支援，這次我給大家帶來的是《軟體系統效能最佳化策略》的講解，這個講解分別圍繞SQL最佳化、IIS最佳化、代碼最佳化[BS架構]、資料庫訪問最佳化、緩衝最佳化。

  而這一篇中，我們就圍繞SQL最佳化來開始這次講解，為什麼第一講要說SQL最佳化？因為我認為這是程式員的基本功，而且也是我們必須要去掌握的，雖然你寫的
SQL語句能完成相應的功能，但是你是否考慮過這些語句碰到海量資料或者暴力訪問時會不會帶來效率的大幅度的減慢？也許很多程式員和我一樣在碰到系統回應時間過慢的時候，就會說：“怎麼回事啦！伺服器太破了，這麼慢！”或者“網路怎麼這麼差的啦！”卻很少抱怨自己編寫的代碼。其實這些細節也是我這次講解的目的，希望大家在編寫SQL語句時候，能看的更“遠”，考慮的更“深”。

 

一、建立合適的索引
  這裡我不是想老生常談“索引技術”，而是希望大家能重視“索引”，更希望大家能充分的理解“索引”，而不是僅僅停留在“主鍵就是索引”這樣膚淺的認識上面。

首先我們大致瞭解什麼是“索引”，在百度中打入“資料庫索引是什麼”，然後我們在百度百科中我們就能看到這樣的解釋

  資料庫索引好比是一本書前面的目錄，能加快資料庫的查詢速度。
  例如這樣一個查詢：select * from table1  where  id=44。如果沒有索引，必須遍曆整個表，直到ID等於44的這一行被找到為止；有了索引之後(必須是在ID這一列上建立的索引)，直接在索引裡面找44（也就是在ID這一列找），就可以得知這一行的位置，也就是找到了這一行。可見，索引是用來定位的。
  索引分為叢集索引和非叢集索引兩種，叢集索引 是按照資料存放的物理位置為順序的，而非叢集索引就不一樣了；聚簇索引能提高多行檢索的速度，而非聚簇索引對於單行的檢索很快。
    
  看完百度的解釋，應該有了初步的瞭解，至於叢集索引和非叢集索引，大家可能還雲裡霧裡的，其實你去問很多老程式員叢集索引和非叢集索引的區別時，他們的回答都是“去百度自己查去”,其實大家也不用去深究，可以理解成

1、叢集索引--字典裡拼音尋找，聯想一下字典，當我們差找“金”時，我們會直接翻到"J"字母著去找，因為大家這本字典是按A-Z的排序去排列的，這個原理和資料庫裡的叢集索引很想，如果將一個索引設為叢集索引，那就說明這個欄位是一定順序排列的，而資料庫表中也只能有一個叢集索引，多用在欄位資料量大，但是相同值多的地方，打個比方像證劵公司，銀行等系統的時間欄位，因為這些系統一天裡可能成交成千上萬的資料，所以如果你在交易時間上設定叢集索引，那就說明當你查詢“2010-3-16”的交易記錄時，資料庫將馬上定位到這些記錄上，不會去曆遍這個資料表。
叢集索引經常使用的欄位，分別為
  a.使用運算子（如 BETWEEN、>、>=、 < 和 <=）返回一系列值
  b.返回大型結果集。
  c.使用 JOIN 子句；一般情況下，使用該子句的是外鍵列。
  d.使用 ORDER BY 或 GROUP BY 子句。 

 

2、非叢集索引--字典裡筆畫或偏旁尋找，聯想一下字典，當我們不知道一個字的讀音“搳”，首先我們找偏幫，然後找到這個字的頁碼，最後找到這個字。在資料庫欄位中設定非叢集索引，那就說明，在這些欄位中的記錄中都有他們的索引地址，在查詢記錄時，就直接定位到該條記錄。

 

探討問題：
  當我們在SQL2005中，給一個欄位設定主鍵的時候，它會將這個主鍵預設設定成叢集索引，這樣做有好處，就是可以讓您的資料在資料庫中按照ID進行物理排序，但是我覺的這並不是很合理，是對叢集索引的一種浪費。顯而易見，叢集索引的優勢是很明顯的，而每個表中只能有一個叢集索引的規則，這使得叢集索引變得更加珍貴。從我們前面談到的叢集索引的定義我們可以看出，使用叢集索引的最大好處就是能夠根據查詢要求，迅速縮小查詢範圍，避免全表掃描。在實際應用中，因為主鍵ID號是自動產生的，我們並不知道每條記錄的ID號，所以我們很難在實踐中用ID號來進行查詢。這就使讓ID號這個主鍵作為叢集索引成為一種資源浪費。

  所以我覺得我們在對海量資料表的索引定義時要謹慎！大家也可以對這個問題發表自己的意見。

 

  最後我們回到出發點，我們為什麼要設定索引？就是為了查詢更迅速，快速呈現查詢結果。有人會說在我們做的系統中，沒有建索引速度也很快哦！那是你查詢的資料量沒有達到一個層級，一旦資料量達到百萬級，你每寫的一個不合理的SQL將會給你帶來意想不到的麻煩。

  記的我第一次認識到索引的重要性的時候，是在可口可樂公司工作的時候，當時我被分配到STM（冷櫃快速投放系統）中，當時該系統的PHASE ONE 已經在全國7個廠房的生產環境使用，隨著資料量一天天的累計，最後發現在一張KPI資料報表中，速度特別慢，而KPI報表都是可樂公司高層需要查看的，用來評估員工的工作效率，高層們當時就對我們的系統印象非常差，要知道在大公司一旦惹惱了高層，將在以後的很多活動中受限，當時我第一時間看了他們寫的預存程序並且查看了資料庫的資料量，報表I/O輸出只有3000條左右，資料庫也只有10W條左右的層級，這些都不可能讓一個月的資料報表查詢達到6分多鐘的，然後我查看了這個KPI的預存程序，發現他們的在SELECT嵌套的時候，JOIN的表之間的主外鍵沒有設定索引，導致他們的報表SQL嵌套是個乘積級數，也就是在嵌套語句中是一個全曆遍過程。在我們剛查看這個預存程序的時候，我們都沒有關注到只是索引的關係，還想了一大堆的方案，但是這些方案對原有的版本改動量太大，最後都夭折了。最後我們通過研究SQL的查詢執行計畫，發現巢狀查詢中消耗的資源特別大，如：

 

  最後我們才發現原來是關聯表的關聯索引沒建，當建完索引之後，從之前的6分鐘減少到了6秒鐘，這遠遠超過我們預期的期望，可想而知，沒建索引之前，資料庫做了多少的全曆遍操作。

  既然我們知道了索引的重要性，我們就要在日常的SQL編寫的時候，盡量的不要破快索引，接下來我就列出幾種導致索引失效的SQL寫法
1、在索引欄位中使用OR或者IN
例：Select * from table1 where id in (2,3)　　或者　　Select * from table1 where id=2 or tid=3
這些寫法都會讓id索引失效而引起全表曆遍，當然當資料沒有達到海量的時候，你愛這麼寫都可以，只要實現功能，一旦達到海量便一個細節決定成敗
解決方案一：
Select * from table1 where id = 2 UNION Select * from table1 where id = 3
當然大家也可以用別的寫法來實現功能。

注【上面的結論 是扯蛋的】

 

2、萬用字元%在字串的開通使得索引無法使用
例：Select * from table1 where name like ‘%張’
如果在name欄位設定索引，那這樣的寫法將導致索引失效
註：Select * from table1 where name like ‘張%’不會導致索引失效

 

3、非操作符將會引起索引無法使用
例：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等都會引起索引失效

 

二、限制物理I/O操作
事實上，在查詢和提取超大容量的資料集時，影響資料庫回應時間的最大因素並不是資料尋找，而是物理的I/0操作。這時候我們在SQL語句編寫中要注意2個問題
1、避免“SELECT *”操作
當我們寫SQL語句時候，經常為了方便，我們用"SELECT * FROM TABLE" 。其實這是一個非常不好的習慣，當達到百萬級的資料量之後，多一個欄位，那將多N秒鐘。

2、使用“TOP”進行資料提取
這是當我們查詢海量資料的時候，假如查詢結果是10W行，這時候我們就需要用真分頁控制項來進行分頁，而我們用的原理就是TOP來實現的。

註：這些結論大家最好建張百萬級的資料表來驗證一下，畢竟動手操作過，將會有一個更清晰的概念。

 

在我們做一些統計或者報表的時候，80%的效率問題都是可以通過建立合理的索引和SQL來最佳化來解決，而且效果也是非常的明顯。這次關於SQL的最佳化就講到這裡，其實還是很多很多其它方面需要注意的問題沒有列舉，這都需要我們在日常的編程中去細細的體會。

 

 

推薦文章

大白話系列之C#委託與事件講解（序言）

大白話系列之C#委託與事件講解(一)  

大白話系列之C#委託與事件講解(二)

大白話系列之C#委託與事件講解(三)

大白話系列之C#委託與事件講解大結局

 

 

推薦2篇文章，是關於人生的

 為什麼我們做不到【領導篇】

 為什麼我們做不到【員工篇】