MySQL 中預存程序的使用

標籤：

    關於 MySQL 的預存程序，我所瞭解的一些有規模的公司，對於使用預存程序實現商務邏輯都有嚴格的限制。我這裡搜羅了一些資料，結合自身的經驗，總結一些自己關於 MySQL 中預存程序使用的認識。由於水平有限，如有紕漏錯誤，還請多拍磚。

 

    預存程序的優點主要包括以下幾點：

 

1. 效能提高。相對於不使用預存程序來說的，因為預存程序在建立的時候，資料庫已經對其進行了一次解析和最佳化，而後每次調用都不會再次編譯，這相對於傳統的SQL語句中每次調用都需要編譯的情況來說，效能有所提高，預存程序經過編譯之後會比單獨一條一條執行要快。而預存程序一旦執行，在記憶體中就會保留一份，這樣下次再執行同樣的預存程序時，可以從記憶體中直接中讀取。

2. 重用性強。預存程序使用名字即可執行，也就是傳說中的“一次編寫，隨便調用”。並且是透明的，因為儲存在資料庫裡所以對任何應用來說都可以使用。新的應用只需要調用相應的預存程序就可以得到相應的資料服務。這樣不僅提高了重用性，還減少了出錯的幾率，也會加快開發速度。同時不依賴某種宿主語言，如果用多種語言開發，某些通用代碼不用重複。

3. 減少網路流量。這一點對於小資料量的時候體現的並不明顯。預存程序直接就在資料庫伺服器上跑，所有的資料訪問都在伺服器內部進行，不需要傳輸資料到其它終端，因此減少了應用伺服器同資料庫伺服器的通訊頻率。調用一個行數不多的預存程序與直接調用SQL語句的網路通訊量可能不會有很大的差別，可是如果預存程序包含上百行SQL語句，那麼其效能就會比一條一條的調用SQL語句有較大的提升。

4. 安全性提高。預存程序是通過向使用者授予許可權(而不是基於表)，它們可以提供對特定資料的訪問，而且參數化的預存程序可以防止SQL注入攻擊，在一定程度上保證了安全性。DBA可以對那些沒有許可權訪問資料庫中的表的應用，賦予預存程序的許可權來獲得資料服務，這個時候這些預存程序好像我們編程裡面的“介面”。對於安全性要求很高的系統，例如銀行，基本上常用的操作都是通過預存程序或者函數來進行的，這樣完全對應用”隱藏“了表。

5. 靈活性增強。由於預存程序可以使用流程式控制制語句來編寫，使得它有著很強的靈活性，可以根據實際情況來執行不同的SQL語句，而不是只能單純的簡單的執行命令。而且預存程序還可以修改其邏輯而其他部分不用改變，也就是說，我們的表的結構改變了，我們可能只需要修改相應的預存程序即可，我們的Java或者PHP等程式不需要改變。

6. 減少工作量。當業務複雜的時候，如果我們不使用預存程序，那麼就會需要先從資料庫中取出來資料，然後經過計算，再放入到資料庫中。這些都是有開銷的，其中包括我們的Java或者PHP等程式串連資料庫擷取結果集等若干操作。如果我們使用了預存程序，那麼直接在MySQL中就能完成修改。並且可以分布式工作，應用程式和資料庫的編碼工作可以分別獨立進行，而不會相互壓制。

7. 可維護性高。更新預存程序通常比更改、測試以及重新部署程式集需要較少的時間和精力。在生產環境下，可以通過直接修改預存程序的方式修改商務邏輯（或bug），而不用重啟伺服器。但這一點便利很多時候被濫用了。比如：直接在正式伺服器上修改預存程序，而沒有經過完整的測試，後果可能會非常嚴重。

 

    好，上面說了預存程序的優點，再來看看預存程序的缺點：

 

1. 編寫和調試麻煩。MySQL本身並沒有很像樣的IDE來開發儲存體過程，我們很多時候還是需要一行一行的老老實實手寫，這樣就會比較麻煩。而預存程序的調試也是一個問題，沒有很像樣的調試工具，很多時候是用print來調試，而且在使用slowlog對MySQL進行效能分析時，只能記錄整個預存程序的執行情況，卻無法記錄預存程序內具體語句的執行情況，對於調試長達數百行SQL的預存程序簡直是蛋疼。

2. 效能優勢不明顯。在運行速度上，對於大多數的SQL語句來說，編譯SQL的時間開銷並不是很大，但是執行預存程序還需要檢查許可權等一些其他開銷，所以，對於很簡單的SQL，預存程序並沒有很大的效能優勢。而且，資料庫畢竟主要用來做資料存取的，並不適合進行複雜的商務邏輯操作，承擔業務壓力會佔用大量的系統資源(cpu、memory)。

3. 贅餘功能。通常情況下，資料庫伺服器只向內網中的應用伺服器提供服務，而且串連資料庫的使用者往往是同一個。對於除金融領域外的其他大多數程式來說，安全性方面的需求往往小於效能、功能等其他方面。所以，對於安全性方面的情況看上去很好，實際上優點有些多餘。

4. 可移植性差。當我們的程式要更換資料庫的時候，它的移植性相對於不適用預存程序要更複雜。而且MySQL的預存程序功能比起Oracle、SQLserver、乃至PostgreSQL 都要較弱一些。

5. 本末倒置。SQL本身是一種結構化查詢語言 (SQL)，加上了一些控制（賦值、迴圈和異常處理等），但不是OO的，本質上還是過程化的，大量採用預存程序進行商務邏輯的開發時，由於不支援物件導向的設計，無法採用物件導向的方式將商務邏輯進行封裝，從而無法形成通用的可支援複用的商務邏輯架構，面對複雜的商務邏輯，過程化的處理會很吃力。

6. 拓展性差。像MySQL這樣的關係型資料庫，出現CPU和IO瓶頸時，極難擴充，但是應用伺服器出現CPU和IO瓶頸，特別是採用SNA架構的情況下，理論上可以獲得無限的水平擴充能力，只需要加伺服器就行了。這個方法比任何方法都見效快，而且往往也是成本最低的。

              

總結：

    對於MySQL來說，通常情況下，除了某些非常依賴資料處理的操作，其他的商務邏輯不應該使用預存程序來實現，而應該由應用程式層實現。

 

    PS：題外話，並不是每個應用的瓶頸都在資料庫端。即便瓶頸在資料庫端，具體問題具體分析，也要功能、效能、安全、成本、效率等多方面權衡，才能做出最合適的選擇。

 

MySQL 中預存程序的使用