為什麼你的MySQL效能差？函式、儲存過程和觸發器都確認無誤嗎？

MySQL儲存過程、函式和觸發器是開發人員常打交道的三大名詞，但是，這三個過程可能會對資料庫效能造成嚴重影響，尤其是函式中出現“死”程式碼時，其他兩個過程都會受到牽連，以下是作者進行的一些基本測試，通過對比更好得衡量三者對資料庫效能的影響。

觸發器和儲存過程對效能影響的重要原因：函式“死”程式碼

儘管所有開發人員都知道“死”程式碼（永遠不會被執行的程式碼）不應該出現在程式碼行中，但可能並不清楚一行“死”程式碼到底可以對效能造成多大影響。“死”程式碼可以顯著減緩函式執行、儲存過程和觸發器的響應時間，我們必須認真清理不需要的程式碼，以下是具體示例：

分析MySQL儲存過程函式

讓我們比較以下四個簡單的儲存函式（在MySQL 5.7中）：

函式1：

該函式只是宣告一個r變數並返回它的值，這是一個虛擬函式。

函式2：

該函式呼叫另一個函式levenshtein_limit_n（計算levenshtein距離）。但是，由於IF 1=2條件永遠不會成立，因此這段程式碼永遠不會被執行，這與函式1的返回結果類似。 

函式3:

 

函式3看起來很複雜，總共有四個條件，但因為這些條件永遠不會成立，因此同樣是“死”程式碼呼叫，結果與函式2和函式1相同。

函式4:

 

該函式與函式3情況相同，selectdoes_not_exit依舊不會被執行。

雖然所有函式最終返回的r值都為0，但這四段函式的效能有非常大的差異，為了衡量效能變化，我們使用benchmark功能對效能進行測試，在執行相同的1M次之後，四個函式的效能結果如下：

 

我們可以看到fun1只需要執行1.75 sec，具有一個死程式碼呼叫的fun 2需要執行2.45 sec，而func3（有四個永不執行的死程式碼呼叫）執行速度比func1（）慢近3倍;，func3_nope（）的響應時間與func3（）完全相同。

視覺化函式的所有系統呼叫

要弄清楚函式呼叫過程發生了什麼，我們可以使用performance_schema / sys模式建立一個帶有 ps_trace_thread() procedure的跟蹤

1、獲取MySQL連線的thread_id：

 

2、在另一個連線中通過thread_id = 49執行ps_trace_thread

 

3、此時切換到原始連線（thread_id = 49）並執行：

 

sys.ps_trace_thread收集資料（10秒鐘，在此期間我執行了select func1（）），然後它完成了集合並建立了點檔案：

 

我為上面的所有函式重複了這些步驟，然後建立了命令圖表。

結果如下：

Func1()

Func2()

Func3()

正如我們所看到的，每個“if”檢查都有一個sp / jump_if_not呼叫，後跟一個開啟表的語句。因此，解析“IF”條件就會對效能產生影響。

對於MySQL 8.0，我們還可以看到儲存過程的MySQL原始碼文件，這些文件記錄了它的實現方式，如下：

· 流量分析優化

· 生成程式碼後，優化低階sp_instr指令。優化主要集中在兩方面：

· 死程式碼刪除，

· 跳轉快捷方式。

· 這兩個優化是一起執行的，因為它們都涉及圖表中流量分析的問題，代表生成的程式碼。實現這些優化的程式碼是sp_head :: optimize（）。

但是，這並不能解釋為什麼會執行“opening tables”，因此，我向官網提交了一個bug（MySQL Bug #91585  https://bugs.mysql.com/bug.php?id=91585）。

“死”程式碼如何影響觸發器效能？

當然，如果我們執行的函式不太多，我們可能注意不到這些效能上的差異，但是它會在觸發器內部產生影響。假設我們在表上有一個觸發器，每次更新該表時，它都會執行觸發器來更新欄位。比如，假設我們有一個名為“form”的表，我們只需要更新它的建立日期：

 

現在我們建立一個觸發器，它將呼叫我們的虛擬函式func1（）：

 

請記住：重複更新不會更改新的結果，因為我們沒有在觸發器中做任何事情。

 

如果再新增一個虛擬觸發器就會增加兩倍的開銷，即便觸發器沒有執行任何函式，同樣會引起減速：

 

現在，讓我們使用func3（它具有“死”程式碼，但功能相當於func1）：

 

如果，我們從觸發器內的函式func3執行程式碼（而不是呼叫函式）將加快更新速度：

 

記憶體分配

即使程式碼永遠不會被執行，MySQL仍然需要分析每次執行解析儲存的過程或觸發器程式碼，並預先為其留出足夠的記憶體，這可能會導致記憶體洩漏，比如MySQL Bug #86821（https://bugs.mysql.com/bug.php?id=86821）所描述的。

結論

儲存過程和觸發器事件在執行時會被解析，即使是永不執行的“死”程式碼也會顯著影響批量操作的效能（例如，在觸發器內執行時）。這意味著通過設定“flag”來禁用觸發器（例如，if @trigger_disable =0 then ...）仍然會影響批量操作的效能。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31077337/viewspace-2158337/，如需轉載，請註明出處，否則將追究法律責任。