關於sql語句的優化

最近在做mysql的資料庫優化以及對sql語句優化的指導，寫了一點文件，這個大家共勉一下！

資料庫引數進行優化所獲得的效能提升全部加起來只佔資料庫應用系統效能提升的40%左右，其餘60%的系統效能提升全部來自對應用程式的優化。許多優化專家甚至認為對應用程式的優化可以得到80%的系統效能提升。因此可以肯定，通過優化應用程式來對資料庫系統進行優化能獲得更大的收益。

通常可分為兩個方面: SQL語句的優化和資料庫效能調優。應用程式對資料庫的操作最終要表現為SQL語句對資料庫的操作。而資料庫效能調優是結合硬體，軟體，資料量等的一個綜合解決方案，這個需要測試人員進行效能測試，和開發人員配合進行效能調優。

SQL語句優化

3.1關鍵詞優化

所有關鍵詞都大寫。如：SELECT,FORM,WHERE,AND,CREATE,TABLE等等，例如：使用mysql管理工具匯出sql檔案，我們可以看到大部分關鍵詞都是大寫。如下圖：

解釋：這是因為，ORACLE的sql的處理底層，預設就將所有的sql語句，進行大寫轉換。Mysql和oracle是同一家公司，不排除哪一天mysql和oracle都做的一樣了。

3.2 sql語句中不能存在*

在所有的查詢sql語句中，不能存在*符號。即，SELECT *FORM 。舉例我們的部門表的查詢。錯誤寫法：SELECT * FROM tdepartment 正確寫法：SELECT idepartmentid,scompanycode,sdepartmentname,iparentdepartmentid,sdeptposttype,sifdeleted,sleafnode,sdesc FROM tdepartment。原因：*號會檢索全部欄位，

用*號效率低，就相當於for迴圈和foreach一樣。用*號，sql語句查詢底層會預設去字      

典庫裡查詢公有多少個欄位，然後在一個一個的取。如果不使用*，就不是去先查字典庫。

3.3 COUNT(*)使用

專案中不能使用COUNT(*)的sql語句。COUNT(*)全部替換成COUNT(1)。這在資料量比較小的情況下，不明顯，但是在表中資料較多的情況下，效果非常明顯。

3.4多用匹配查詢，少用like查詢

      原因，like查詢會直接放棄索引。

3.5主鍵索引使用

      所有表的主鍵全是索引。應儘量使用主鍵查詢。如：SELECT * FROM tusers ORDER BY dregistertime DESC效率低於SELECT * FROM tusers ORDER BY iuserid DESC。這是因為所有的主鍵都預設是索引。而註冊時間不是索引欄位。 

3.6第1第2索引排列使用

假設我們的使用者表中的scompanycode,dregistertime兩個欄位都建立了索引。而scompanycode是第一索引。dregistertime是第二索引。那麼查詢時：SELECT * FROM tusers ORDER BY scompanycode,dregistertime DESC的效率高於SELECT * FROM tusers ORDER BY dregistertime,scompanycode DESC。這是因為第一索引將首先被檢索。

3.7建表不要給欄位設定預設值

如：`sifaudited` varchar(2) default '0' COMMENT '0:未稽核；1:已稽核'。預設值會在插入資料時，增加資料庫底層判斷是否有值情況，進行賦預設值。

3.8欄位不要留null值

這是因為null值佔用的資料大小比較大。Null和空一般佔4到8個位元組。如：`scompanycode` varchar(16) default NULL COMMENT '公司編號(唯一識別）'，對於這樣的，我們一般把空值改為0，你們應該懂的。

3.9多用子查詢

      子查詢效能高於連線查詢。子查詢效能高於左聯接、右連線、全連線查詢。

3.10連線查詢效能高於迴圈查詢

對於部門查詢，我們一般是查詢根目錄，然後迴圈查詢子部門，一直迴圈到查詢結束。效能較低。我們應該採用，連線查詢。或者寫函式，儲存過程進行查詢。

4.設計優化

4.1 日誌模組，新增佇列，當日志達到100條或者200、500條的時候，我們採用批量插入n條，減少磁碟的io次數。這樣可以延長磁碟的壽命，同時對資料的插入也有了明顯的提高。

5.資料庫引擎使用

5.1   ENGINE = innodb

    Innodb資料庫引擎是對外來鍵，事務進行過優化。我們對建立所有的表都使用innodb引擎。這是錯誤的，應該對每一個表的用途對應一個不同的資料庫引擎。

5.2   ENGINE = MyISAM

MyISAM型別不支援事務處理等高階處理。MyISAM型別的表強調的是效能，其執行數度比InnoDB型別更快，但是不提供事務支援。MyISAM型別的二進位制資料檔案可以在不同作業系統中遷移。也就是可以直接從Windows系統拷貝到linux系統中使用。這個是預設型別,它是基於傳統的ISAM型別,ISAM是Indexed Sequential Access Method (有索引的 順序訪問方法) 的縮寫,它是儲存記錄和檔案的標準方法.與其他儲存引擎比較,MyISAM具有檢查和修復表格的大多數工具. MyISAM表格可以被壓縮,而且它們支援全文搜尋.它們不是事務安全的,而且也不支援外來鍵。如果事物回滾將造成不完全回滾，不具有原子性。如果執行大量 的SELECT，MyISAM是更好的選擇。這個型別東海們專案使用的多。最常用的引擎之一。

5.3   ENGINE = BDB

BDB：可替代InnoDB的事務引擎，支援COMMIT、ROLLBACK和其他事務特性。

5.4   ENGINE = Memory

Memory：將所有資料儲存在RAM中，在需要快速查詢引用和其他類似資料的環境下，可提供極快的訪問。

5.5   ENGINE = Merge

Merge：允許MySQL DBA或開發人員將一系列等同的MyISAM表以邏輯方式組合在一起，並作為1個物件引用它們。對於諸如資料倉儲等VLDB環境十分適合。

5.6    ENGINE = Archive

Archive：為大量很少引用的歷史、歸檔、或安全審計資訊的儲存和檢索提供了完美的解決方案。

5.7    ENGINE = Federated

 Federated：能夠將多個分離的MySQL伺服器連結起來，從多個物理伺服器建立一個邏輯資料庫。十分適合於分散式 環境或資料集市環境。

5.8    ENGINE =Cluster/NDB

Cluster/NDB：MySQL的簇式資料庫引擎，尤其適合於具有高效能查詢要求的應用程式，這類查詢需求還要求具有最高的正常工作時間和可用性

5.9    Other：其他儲存引擎包括CSV（引用由逗號隔開的用作資料庫表的檔案），Blackhole（用於臨時禁止對資料庫的應用程式輸入），以及Example引擎（可為快速建立定製的外掛式儲存引擎提供幫助）。

6.表欄位設計

  6.1對於型別限制。是否刪除欄位，如：`sifdeleted` varchar(2) default '0' COMMENT '0:正常；1:已刪除',使用int(1)型別標識，不要使用varchar(2)多佔用空間。

     6.2 對於欄位長度限制，如手機號11位，我們就沒有必要設計更多位數。公司編號可以只設定8位。使用者名稱限制32位等等。

     6.3 少用外來鍵限制

         我們可以使用程式碼限制。如：級聯刪除，級聯新增，修改等等操作。最好不要設計外來鍵，外來鍵對新增資料不利。

     6.4  少用約束，如：唯一約束。

 6.5  少用自動增長

      在圓通主鍵沒有自動增長，而是使用uuid，java自動生成。考慮到我們資料表資料較少，少用。

 6.6  對於內容較少的表，沒有必要建立索引。因為索引浪費空間。

 6.7  表分割槽使用

      對於日誌表，我們可以使用表分割槽。表分割槽之後，對於查詢效率有很高的提升。預設有時間分割槽，大小分割槽，型別分割槽等等。

 6.8  對錶的內容進行限制，如：日誌表可以限制條數。再建立表時。我們使用MAX_ROWS進行限制。

7.其他請遵守建表規則

   如：三正規化等。

好吧就到這裡，歡迎大家關注我的個人部落格！

如有疑問，請加qq群：135430763 共同學習！

點選文件下載：