MySQL 存儲過程的優(yōu)缺點分析

MySQL 5.0 版本開始支持存儲過程。存儲過程（Stored Procedure）是數(shù)據(jù)庫中存儲的復(fù)雜程序，以便外部應(yīng)用調(diào)用的一種數(shù)據(jù)庫對象。存儲過程是為了完成特定功能的SQL語句集，經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫中，用戶可通過指定存儲過程的名字并給定參數(shù)（可選）來調(diào)用執(zhí)行。

存儲過程可以有效提高 SQL 語句的復(fù)用率，并且可以將相關(guān)的一組 SQL 放入到存儲過程中，從而避免了應(yīng)用程序的多次查詢帶來的與 MySQL 服務(wù)器的連接延遲和占用的網(wǎng)絡(luò)資源。下面是一個存儲過程的示例，用于傳入一個 id 來刪除指定 id的學生，并同時刪除擴展表中的學生信息。通過這種方式就可以處理相關(guān)聯(lián)的數(shù)據(jù)，而不需要應(yīng)用程序分兩次 SQL 操作。

DROP PROCEDURE IF EXISTS delete_student_by_id;

delimiter $$

CREATE PROCEDURE delete_student_by_id(IN p_id INT)
BEGIN
	DELETE FROM t_students
  WHERE id = p_id;
      
  DELETE FROM t_students_info
  WHERE student_id = p_id;
END
$$
    
delimiter ;

總的來說，存儲過程有如下的優(yōu)點：

• 直接在數(shù)據(jù)庫層運行，從而減少網(wǎng)絡(luò)帶寬的占用和減少查詢?nèi)蝿?wù)執(zhí)行的延遲。
• 提高了代碼的復(fù)用性和可維護性，可以聚合業(yè)務(wù)規(guī)則，加強一致性并提高安全性。
• 可以帶來安全性優(yōu)勢以及優(yōu)雅的權(quán)限控制手段。一個典型的例子就是銀行中的轉(zhuǎn)賬存儲過程。存儲過程在一個事務(wù)里完成轉(zhuǎn)賬及記錄用于后續(xù)審核的完整操作日志?？梢酝ㄟ^存儲過程完成訪問而無需對涉及到的表進行提權(quán)。
• 服務(wù)端會緩存存儲過程的執(zhí)行，這樣可以減少重復(fù)執(zhí)行的負荷。
• 存儲過程存儲在服務(wù)端，因此對于服務(wù)單的部署、備份和維護而言，存儲過程更好維護。
• 可以將應(yīng)用開發(fā)者與數(shù)據(jù)庫開發(fā)者的工作分離，因此可以讓數(shù)據(jù)庫牛人來寫存儲過程，而避免某些應(yīng)用開發(fā)者編寫 SQL水平不高的問題。
  

當然，有利必有弊，存儲過程也會存在一些缺陷：

• MySQL沒有提供好的開發(fā)和調(diào)試工具，因此存儲過程的調(diào)試相對來說不那么容易。
• SQL 語言本身的效率沒有應(yīng)用程序的編程語言效率高，且相對更初級。因此，難以處理復(fù)雜的業(yè)務(wù)。
• 存儲過程也可能增加應(yīng)用部署的復(fù)雜度，不單單需要部署應(yīng)用代碼和數(shù)據(jù)庫表，還需要部署存儲過程。
• 每個連接的存儲過程的執(zhí)行計劃緩存是各自獨立的。如果有很多連接調(diào)用同一個存儲過程，反復(fù)地緩存會造成資源的浪費。
• 存儲過程將運行符合轉(zhuǎn)移到了數(shù)據(jù)庫服務(wù)器，這會導(dǎo)致數(shù)據(jù)庫服務(wù)器的擴容更難，且比應(yīng)用服務(wù)器的擴容代價更高。
• 存儲過程占用的資源難以控制，如果發(fā)生一個 bug 可能導(dǎo)致服務(wù)器宕機。
• 存儲過程的代碼很難解讀，如果單純地調(diào)用 CALL XYZ('A')這種形式調(diào)用存儲過程的話，很難分析慢查詢?nèi)罩尽Ｒ驗?，這需要找到存儲過程的代碼并且檢查里面的語句。
• 對于語句級的 binlog或復(fù)制，使用存儲過程可能會有很多陷阱導(dǎo)致無法使用存儲過程——除非嚴格檢查排除潛在的問題。
  

因此，通常，需要保持存儲過程小巧簡潔，以避免上述的缺陷。當然，在某些操作時，存儲過程會運行得更快，尤其是在存儲過程中使用循環(huán)完成多個小查詢。如果查詢足夠小，解析 SQL 語句和網(wǎng)絡(luò)通信則變成了工作負荷過高的重要因素。這個時候存儲過程的優(yōu)勢就會被突顯出來。以下面的存儲過程代碼為例：

DROP PROCEDURE IF EXISTS insert_many_rows;

delemiter //

CREATE PROCEDURE insert_many_rows(IN loops INT)
BEGIN
	DECLARE v1 INT;
  SET v1=loops;
  WHILE v1 > 0 DO
  	INSERT INTO test_table values(NULL, 0,
                                 'aaaaaaaaaaaabbbbbbbbbb',
                                 'aaaaaaaaaaaabbbbbbbbbb');
    SET v1=v1-1;
  END WHILE;
END
//

delemiter ;
	

可以通過與應(yīng)用程序?qū)崿F(xiàn)同樣的功能進行比較，發(fā)現(xiàn)使用存儲過程的性能提高了2倍以上，而如果與使用 MySQL 代理相比，性能會提高到3倍。

結(jié)語：存儲過程目前用得其實不多，但是對于一些穩(wěn)定的業(yè)務(wù)，如果是因為與數(shù)據(jù)庫服務(wù)器之間的網(wǎng)絡(luò)請求過多或占用了大量的網(wǎng)絡(luò)帶寬，則可以考慮使用存儲過程來優(yōu)化性能，提高響應(yīng)速度。但是，存儲過程務(wù)必反復(fù)驗證，避免出現(xiàn)意向不到的錯誤導(dǎo)致耗費過多的時間排查問題。

以上就是MySQL 存儲過程的優(yōu)缺點分析的詳細內(nèi)容，更多關(guān)于MySQL 存儲過程的優(yōu)缺點的資料請關(guān)注腳本之家其它相關(guān)文章！