mysql預編譯機制（mysql預編譯原理）

本文目錄一覽：

• 1、mysql 存儲過程 是什麼意思
• 2、mysql 預編譯 為什麼默認不開啟
• 3、Mysql怎麼確定儲存過程是預編譯的比普通sql執行的快
• 4、mysql-connector-java-5.1.6支持預編譯嗎
• 5、mysql怎麼實現預編譯

mysql 存儲過程 是什麼意思

存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後存儲在資料庫中，用戶通過指定存儲過程的名字並給定參數（如果該存儲過程帶有參數）來調用執行它。

一個存儲過程是一個可編程的函數，它在資料庫中創建並保存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程序或平台上執行相同的函數，或者封裝特定功能時，存儲過程是非常有用的。資料庫中的存儲過程可以看做是對編程中面向對象方法的模擬。它允許控制數據的訪問方式。

存儲過程通常有以下優點：

存儲過程增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。

存儲過程允許標準組件是編程。存儲過程被創建後，可以在程序中被多次調用，而不必重新編寫該存儲過程的SQL語句。而且資料庫專業人員可以隨時對存儲過程進行修改，對應用程序源代碼毫無影響。

存儲過程能實現較快的執行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那麼存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的。在首次運行一個存儲過程時查詢，優化器對其進行分析優化，並且給出最終被存儲在系統表中的執行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優化，速度相對要慢一些。

存儲過程能過減少網路流量。針對同一個資料庫對象的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織程存儲過程，那麼當在客戶計算機上調用該存儲過程時，網路中傳送的只是該調用語句，從而大大增加了網路流量並降低了網路負載。

存儲過程可被作為一種安全機制來充分利用。系統管理員通過執行某一存儲過程的許可權進行限制，能夠實現對相應的數據的訪問許可權的限制，避免了非授權用戶對數據的訪問，保證了數據的安全。

MySQL存儲過程創建的格式如下：

CREATE PROCEDURE 過程名 ([過程參數[,…]])

[特性 …] 過程體

舉例代碼如下：

CREATE PROCEDURE proc1(OUT s int)  

BEGIN 

    SELECT COUNT(*) INTO s FROM user;  

END

存儲過程根據需要可能會有輸入、輸出、輸入輸出參數，這裡有一個輸出參數s，類型是int型，如果有多個參數用”,”分割開。

過程體的開始與結束使用BEGIN與END進行標識。

注意：MySQL在5.0以前並不支持存儲過程

mysql 預編譯 為什麼默認不開啟

mysql 預編譯 為什麼默認不開啟

當然如果開啟預編譯功能的話，還要開啟客戶端緩存功能，否則有害無利。但是即使開了預編譯功能也不會對程序帶來多大改進，因為mysql的服務端預編譯是session級別的，也就是說除非你的單個connection 進行了大量的重複某個SQL這個預編譯功能才有意義。

Mysql怎麼確定儲存過程是預編譯的比普通sql執行的快

SET @update_stmt=” /*在單引號中間填入要執行的sql語句*/

PREPARE stmt FROM @update_stmt;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

mysql-connector-java-5.1.6支持預編譯嗎

預編譯和版本沒關係，sql語句中使用PrepareStatement創建的sql就會進行預編譯，普通statement不會進行預編譯，所以取決於你的調用方式。

在JDBC編程中，常用Statement、PreparedStatement 和 CallableStatement三種方式來執行查詢語句，其中 Statement 用於通用查詢， PreparedStatement 用於執行參數化查詢，而 CallableStatement則是用於存儲過程。

如果想深入可以一個個看一下。

mysql怎麼實現預編譯

玩Oracle的都比較關注shared pool，特別是library cache，在使用了綁定變數（預編譯sql）之後確實能得到很大的性能提升。現在在轉Mysql之後特別是innodb很多東西都還能和Oracle對得上號的，就像innodb_buffer_pool_size類似於Oracle的database buffer cache，innodb_log_buffer_size類似於redo log buffer，但是innodb_additional_mem_pool_size僅僅類似於shared pool的Data dictionary cache，似乎還缺少和library cache相對應的東西。那就有一個問題了，在Mysql裡面使用預編譯的sql還會有性能提升嗎？

這裡我用Java的jdbc做了一下測試，分別用Statement和PreparedStatement執行1000個sql，並運行10次

1.使用Statement做硬解析：

1 package exmysql; 2  3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 import java.util.Date; 9 10 public class adddata {11     12     private static long worker(){13         Date begin = new Date();

14         15         String driver=”com.mysql.jdbc.Driver”;16 17         String url=”jdbc:mysql://172.16.2.7:3306/testdb”;18         19         Connection conn=null;20         Statement stmt=null;21         ResultSet rs=null;22         23         try{24             Class.forName(driver);25             conn=DriverManager.getConnection(url,”dbaadmin”,”123456″);26             stmt=conn.createStatement();27             String sql;28             for (int i=1;i=5000;i++){29                 sql=”select * from test1 where id=”+i;30                 rs=stmt.executeQuery(sql);31             }32         }33         catch(SQLException | ClassNotFoundException e){34             e.printStackTrace();35         }36         37         if(stmt!=null){38             try{39                 stmt.close();40             }41             catch(SQLException e){42                 e.printStackTrace();43             }44         }45         46         if(conn!=null){47             try{48                 conn.close();49             }50             catch(SQLException e){51                 e.printStackTrace();52             }53         }54         55         Date end = new Date();

56         return end.getTime()-begin.getTime();57     }58 59     public static void main(String[] args) {60         // TODO Auto-generated method stub61         62         long elapsed,average;63         average=0;64         for (int i=1;i=10;i++){65             elapsed=worker();66             System.out.println(“elapsed time(ms):”+elapsed);67             average=average+elapsed;68         }69         System.out.println(“average time(ms):”+average/10);70     }71 72 }

結果如下：

elapsed time(ms):24652

elapsed time(ms):13380

elapsed time(ms):13250

elapsed time(ms):13877

elapsed time(ms):13275

elapsed time(ms):13193

elapsed time(ms):19022

elapsed time(ms):13558

elapsed time(ms):14138

elapsed time(ms):13364

average time(ms):15170

2.同樣的sql用PreparedStatement預編譯執行

1 package exmysql; 2  3 import java.sql.*; 4 import java.util.Date; 5  6 public class insert_data { 7  8     private static long worker(){ 9         Date begin = new Date();

10         11         String driver=”com.mysql.jdbc.Driver”;12 13         String url=”jdbc:mysql://172.16.2.7:3306/testdb”;14         15         Connection conn=null;16         PreparedStatement pstm=null;17         ResultSet rs=null;18         19         try{20             Class.forName(driver);21             conn=DriverManager.getConnection(url,”dbaadmin”,”123456″);22             String sql=”select * from test1 where id=?”;24             pstm=conn.prepareStatement(sql);25             for(int i=1;i=5000;i++){26                 pstm.setInt(1, i);27                 rs=pstm.executeQuery();28             }29             31         }32         catch(SQLException | ClassNotFoundException e){33             e.printStackTrace();34         }35         36         if(pstm!=null){37             try{38                 pstm.close();39             }40             catch(SQLException e){41                 e.printStackTrace();42             }43         }44         45         if(conn!=null){46             try{47                 conn.close();48             }49             catch(SQLException e){50                 e.printStackTrace();51             }52         }53         54         Date end = new Date();

55         return end.getTime()-begin.getTime();56     }57     58     public static void main(String[] args) {59         // TODO Auto-generated method stub60         61         long elapsed,average;62         average=0;63         for (int i=1;i=10;i++){64             elapsed=worker();65             System.out.println(“elapsed time(ms):”+elapsed);66             average=average+elapsed;67         }68         System.out.println(“average time(ms):”+average/10);69     }70 71 }

結果如下：

elapsed time(ms):14773

elapsed time(ms):16352

elapsed time(ms):14797

elapsed time(ms):15800

elapsed time(ms):12069

elapsed time(ms):14953

elapsed time(ms):13238

elapsed time(ms):12366

elapsed time(ms):15263

elapsed time(ms):13089

average time(ms):14270

可以看出兩種方式執行的結果幾乎相同，不像Oracle差距那麼大。而且就算是用PreparedStatement的方式，在Mysql資料庫端抓出來的sql語句也不是以變數id=?的形式出現的，而是實際的數值。後來在網上看到在連接字元串上加上useServerPrepStmts=true可以實現真正的預編譯

String url=”jdbc:mysql://172.16.2.7:3306/testdb”;        url=url+”?useServerPrepStmts=true”;

加上這段後可以在資料庫端可以看到明確的結果：

mysql show global status like ‘Com_stmt_prepare’;

+——————+——-+

| Variable_name | Value |

+——————+——-+

| Com_stmt_prepare | 11 |

+——————+——-+

1 row in set (0.00 sec)

但是實際的運行結果和上面幾乎一樣，性能上也沒有任何的提升。由此可以推斷出Mysql由於缺少類似於Oracle的library cache的部件，因此採用預編譯方式執行sql是沒有性能上的提升的。