圖解MySQL資料庫的儲存過程及儲存引擎

來源：酷知科普網 1.68W

我們經常會對資料表進行插入、刪除、更新及查詢的工作，即我們常說的CURD。其實，當我們輸入命令時，MySQL引擎會按照下圖進行操作。如果我們省略了分析和編譯的環節，那麼執行效率將大大提高。
本篇將會和大家介紹MySQL資料庫的儲存過程及儲存引擎的相關知識，希望對大家的工作和學習有所幫助！

儲存過程

（01）儲存過程是SQL語句和控制語句的預編譯集合，以一個名稱儲存並作為一個單元處理。儲存過程儲存在資料庫內，可以由應用程式呼叫執行，允許使用者宣告明變數以及進行流程控制。儲存過程可以接收引數（輸入型別引數、輸出型別引數），可以存在多個返回值。所以，儲存過程的執行效率高於單一SQL命令的執行效率。

（02）1、增強SQL語句的功能和靈活性。2、實現較快的執行速度。客戶端第一次呼叫儲存過程時，MySQL引擎會對其進行語法分析、編譯等操作，然後將編譯結果儲存到記憶體中，所以第一次和之前的效率一樣，然而以後會直接呼叫記憶體中的編譯結果，效率提高。3、減少網路流量。單條SQL語句字元量較大，而通過呼叫儲存過程則只需要傳儲存過程的名稱及相關引數即可，提交給伺服器的資料量相對較少。

（03）CREATE[DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name([proc_parameter[,...]]) [characteristic ...] routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeIN表示該引數的值必須在呼叫儲存過程時指定，不能返回。OUT表示該引數的值可以被儲存過程改變，並且可以返回。INOUT表示該引數在呼叫時指定，並且可以被改變和返回。

（04）COMMENT 'string'|{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY {DEFINER | INVOKER}COMMENT:註釋CONTAINS SQL:包含SQL語句，但不包含讀或寫資料的語句。NO SQL:不包含SQL語句。READS SQL DATA:包含讀資料的語句。MODIFIES SQL DATA:包含寫資料的語句。SQL SECURITY {DEFINER | INVOKER}:指明誰有許可權來執行。

（05）1.過程體由合法的SQL語句構成；2.過程體可以是“任意”SQL語句（這裡的任意主要是指對記錄的增刪改查，多表連線）；3.過程體如果為複合結構，則使用語句；4.複合結構可以包含宣告，迴圈，控制結構。

（06）CREATE PROCEDURE sp1() SELECT VERSION();

（07）方式一：CALL sp_name([parameter[,...]]) 如果儲存過程包含引數，則必須有小括號。方式二：CALL sp_name[()] 如果儲存過程不包含引數，則小括號可有可無。

（08）ALTER PROCEDURE sp_name [characteristic ...]COMMENT 'string'|{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }只能修改儲存過程中的註釋、當前內容的型別，並不能修改過程體。要修改過程體的話，需要先刪除儲存過程，然後重建。

（09）DROP PROCEDURE [IF EXISTS] sp_name

（10）DELIMITER //CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;END//DELIMITER;

（11）下面來呼叫儲存過程，如下圖所示：

（12）DELIMITER //CREATE PROCEDURE removeAndReturnUsersNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;SELECT count(id) FROM users INTO userNums;END//DELIMITER ;

（13）1.使用者變數：以"@"開始，形式為"@變數名"使用者變數跟mysql客戶端是繫結的，設定的變數，只對當前使用者使用的客戶端生效SET @i = 7;2.全域性變數：定義時，以如下兩種形式出現，set GLOBAL 變數名或者set @@global.變數名對所有客戶端生效。只有具有super許可權才可以設定全域性變數3.會話變數：只對連線的客戶端有效4.區域性變數：作用範圍在begin到end語句塊之間。在該語句塊裡設定的變數declare語句專門用於定義區域性變數。set語句是設定不同型別的變數，包括會話變數和全域性變數下面來呼叫儲存過程CALL removeAndReturnUsersNums(1,@nums);

（14）DELIMITER //CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SAMLLINT UNSIGNED)BEGINDELETE FROM users WHERE age = p_age;SELECT ROW_COUNT() INTO deleteUsers;SELECT COUNT(id) FROM users INTO userCounts;END//DELIMITER ;[注意]ROW_COUNT()函式用來得到插入、刪除以及更新的被影響的記錄總數。

（15）下面來呼叫儲存過程CALL removeUserByAgeAndReturnInfos(20,@a,@b);[注意]@a表示刪除的記錄數，@b表示剩餘的記錄數SELECT @a,@b;

（16）1.儲存過程實現的功能要複雜一些；而函式的針對性更強。2.儲存過程可以返回多個值；函式只能有一個返回值。3.儲存過程一般獨立的來執行；而函式主要作為其他SQL語句的組成部分來出現。

儲存引擎

（01）MySQL可以將資料以不同的技術儲存在檔案（記憶體）中，這種技術就稱為儲存引擎。每一種儲存引擎使用不同的儲存機制、索引技巧、鎖定水平，最終提供廣泛且不同的功能在關係型資料庫中，資料的儲存是以表的形式來實現的。所以，儲存引擎也可以稱為表型別。所以，實際上，儲存引擎就是一種儲存資料、查詢資料的技術MySQL支援的儲存引擎包括MyISAM、InnoDB、Memory、CSV、Archive。

（02）併發控制是指當多個連線對記錄進行修改時保證資料的一致性和完整性。例如：兩個使用者同時登入並操作資料庫，其中一個使用者刪除某條記錄，而另一個使用者讀取該條記錄，這就需要併發控制，否則會報錯或返回無效資訊。在處理併發'讀'或'寫'操作時，MySQL通過鎖系統實現併發控制，包括共享鎖和排他鎖。-共享鎖(讀鎖)：在同一時間段內，多個使用者可以讀取同一個資源，讀取過程中資料不會發生任何變化。-排他鎖(寫鎖)：在任何時候只能有一個使用者寫入資源，當進行寫鎖時會阻塞其他的讀鎖或者寫鎖操作。鎖顆粒(也稱為鎖力度)是指鎖定時的單位。只需要對修改的資料精確加鎖就可以，而無需對所有資源都加鎖。加鎖會增加系統開銷，所以需要通過鎖策略，在鎖開銷和系統安全之間尋找平衡。mysql鎖策略包括表鎖和行鎖兩種策略。- 表鎖，是一種開銷最小的鎖策略。- 行鎖，是一種開銷最大的鎖策略。

（03）事務是資料庫區別於檔案系統的重要特徵之一，事務主要用於保證資料庫的完整性。事務特性包括：原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)、永續性(Durability)，簡寫為ACID。

（04）索引是對資料表中一列或多列的值進行排序的一種結構，使用索引可以快速訪問資料表的特定資訊。索引是記錄快速定位的一種方法，類似於書的目錄。索引包括普通索引、唯一索引、全文索引、btree之索引、hash索引等。

（05）除了上面這幾種儲存引擎之外，還有下面幾種不太常見的引擎。CSV儲存引擎不支援索引，逗號分隔值(Comma-Separated Values，CSV，有時也稱為字元分隔值，因為分隔字元也可以不是逗號)，其檔案以純文字形式儲存表格資料（數字和文字）。純文字意味著該檔案是一個字元序列，不含必須像二進位制數字那樣被解讀的資料。CSV檔案由任意數目的記錄組成，記錄間以某種換行符分隔。BlackHole也叫黑洞引擎，寫入的資料都會消失，一般用於做資料複製的中繼。MyISAM引擎適合於事物處理不多的情況。