Java筆記（一）–Eclipse 操作MySQL資料庫的效率問題

說明：這是最近學習java過程中遇到的一些問題，順便記錄下來，僅供參考。

去前幾天剛簡單的測試了一下ADO.NET操作SQL Server的效率，這兩天又來試試MySQL，用Eclipse來測試。

硬體環境同前。
軟體環境如下：
Eclipse Version : 3.4.0
MySQL Version : 5.0.51b-communicty-nt
JDBC Version: mysql-connector-java-5.1.6-bin.jar
Java Version: 1.6.0_07

測試代碼：
  就是簡單的往資料庫裡迴圈插入100萬條記錄，再全部讀出，最後刪除，看看花費多少時間。
   

Code
static Connection conn =null; 
  static Statement stmt=null;
  static ResultSet rs=null;
   
  // 測試連接MySQL數據庫，並讀取內容進行顯示
  private static boolean Conn_MySQL(){
  try{
  conn=DriverManager.getConnection("jdbc:mysql://localhost/test?" + 
"user=root&password=chu");
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  try{
  stmt=conn.createStatement();
  return true;
  }
  catch(SQLException s_e){
  System.out.println("s_E"+s_e.getMessage().toString());
  return false;
  }
  }
  catch(SQLException s_e){
  System.out.println("串連錯誤！SQLException"+s_e.toString());
  return false;
  }
  catch(ClassNotFoundException c_e){
  System.out.println("串連錯誤！ClassNotFoundException"+c_e.toString());
  return false;
  }
  catch(InstantiationException in_e){
  System.out.println("串連錯誤！InstantiationException"+in_e.toString());
  return false;
  }
  catch(IllegalAccessException il_e){
  System.out.println("串連錯誤！IllegalAccessException"+il_e.toString());
  return false;
  }
  }
   
  // 執行SQL語句,要求資料庫已經串連好，本函數不負責檢測
  // 參數flag表示sql語句類型
  // 1:select 等要求返回結果的查詢語句
  // 2:insert，update,delete 等不要求返回查詢結果的更新語句
  //
  private static void ExeSQL(String sql,int flag){
  try{
  long begin_SQL_Ticks=System.currentTimeMillis();
  boolean sql_exe=false;
  //for(int i=0;i<1000000;i++){
  sql_exe=stmt.execute(sql);
  //}
  long end_SQL_Ticks=System.currentTimeMillis();
  if(sql_exe){
  //如果要求返回結果
  if(flag==1){
  rs=stmt.getResultSet();
  rs.first();
  while(!rs.isLast()){
  System.out.print(rs.getString(1)+" ");
  System.out.print(rs.getInt(2)+" ");
  System.out.print(rs.getInt(3)+"\n");
  rs.next();
  }
  rs.last();
  System.out.print(rs.getString(1)+" ");
  System.out.print(rs.getInt(2)+" ");
  System.out.print(rs.getInt(3)+"\n");
  long show_Ticks=System.currentTimeMillis();
  System.out.println("顯示共耗時："+(show_Ticks-end_SQL_Ticks)+"毫秒");
  }
  }
  System.out.println("執行SQL語句共耗時："+(end_SQL_Ticks-begin_SQL_Ticks)+"毫秒");
  }
  catch(SQLException s_e){
  System.out.println(s_e.getMessage());
  }
  }
   
  public static void main(String[] args) {
  if(Conn_MySQL()){  
  ExeSQL("insert into test_1 values('nalan',435,65786888)",2);
  ExeSQL("select * from test_1",1);
  ExeSQL("delete from test_1",2);

但奇怪的是，這次執行過程極為緩慢：大概20分鐘才插入5萬多條記錄！這在前面測試SQL Server中是不可想象的！前面的測試插入100萬條花費11分33秒，而且資料記錄要大很多，

下面是MySQL 5.1參考手冊裡給的一些說法：

INSERT語句的速度
插入一個記錄需要的時間由下列因素組成，其中的數字表示大約比例：
串連：(3)
發送查詢給伺服器：(2)
分析查詢：(2)
插入記錄：（1x記錄大小）
插入索引：（1x索引）
關閉：(1)

這不考慮開啟表的初始開銷，每個並發啟動並執行查詢開啟。
表的大小以logN (B樹)的速度減慢索引的插入。

加快插入的一些方法：
• 如果同時從同一個用戶端插入很多行，使用含多個VALUE的INSERT語句同時插入幾行。這比使用單行INSERT語句快(在某些情況下快幾倍)。如果你正向一個非空表添加資料，可以調節bulk_insert_buffer_size變數，使資料插入更快。參見5.3.3節，“伺服器系統變數”。
• 如果你從不同的用戶端插入很多行，能通過INSERT DELAYED語句加快速度。參見13.2.4節，“INSERT文法”。
• 用MyISAM，如果在表中沒有刪除的行，能在SELECT語句正在啟動並執行同時插入行。
• 當從一個文字檔裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍。參見13.2.5節，“LOAD DATA INFILE文法”。
• 當表有很多索引時，有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列過程：
有選擇地用CREATE TABLE建立表。
執行FLUSH TABLES語句或命令mysqladmin flush-tables。
使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。這將從表中取消所有索引的使用。
用LOAD DATA INFILE把資料插入到表中，因為不更新任何索引，因此很快。
如果只想在以後讀取表，使用myisampack壓縮它。參見15.1.3.3節，“壓縮表特性”。
用myisamchk -r -q /path/to/db/tbl_name重新建立索引。這將在寫入磁碟前在記憶體中建立索引樹，並且它更快，因為避免了大量磁碟搜尋。結果索引樹也被完美地平衡。
執行FLUSH TABLES語句或mysqladmin flush-tables命令。
請注意如果插入一個空MyISAM表，LOAD DATA INFILE也可以執行前面的最佳化；主要不同處是可以讓myisamchk為建立索引分配更多的臨時記憶體，比執行LOAD DATA INFILE語句時為伺服器重新建立索引分配得要多。
也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk --keys-used=0 -rq /path/to/db/tbl_name，使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name。使用這種方式，還可以跳過FLUSH TABLES。

• 鎖定表可以加速用多個語句執行的INSERT操作：
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
這樣效能會提高，因為索引緩衝區僅在所有INSERT陳述式完成後重新整理到磁碟上一次。一般有多少INSERT語句即有多少索引緩衝區重新整理。如果能用一個語句插入所有的行，就不需要鎖定。
對於事務表，應使用BEGIN和COMMIT代替LOCK TABLES來加快插入。

鎖定也將降低多串連測試的整體時間，儘管因為它們等候鎖定最大等待時間將上升。例如：
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
如果不使用鎖定，2、3和4將在1和5前完成。如果使用鎖定，2、3和4將可能不在1或5前完成，但是整體時間應該快大約40%。

INSERT、UPDATE和DELETE操作在MySQL中是很快的，通過為在一行中多於大約5次連續不斷地插入或更新的操作加鎖，可以獲得更好的整體效能。如果在一行中進行多次插入，可以執行LOCK TABLES，隨後立即執行UNLOCK TABLES(大約每1000行)以允許其它的線程訪問表。這也會獲得好的效能。
INSERT裝載資料比LOAD DATA INFILE要慢得多，即使是使用上述的策略。
• 為了對LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度，通過增加key_buffer_size系統變數來擴大 鍵高速緩衝區。參見7.5.2節，“調節伺服器參數”。

createStatement
Statement createStatement()
  throws SQLException
建立一個 Statement 對象來將 SQL 陳述式發送到資料庫。沒有參數的 SQL 陳述式通常使用 Statement 對象執行。如果多次執行相同的 SQL 陳述式，使用 PreparedStatement 對象可能更有效。 

使用返回的 Statement 對象建立的結果集在預設情況下類型為 TYPE_FORWARD_ONLY，並帶有 CONCUR_READ_ONLY 並發層級。 

返回：
一個新的預設 Statement 對象 
拋出： 
SQLException - 如果發生資料庫訪問錯誤

prepareStatement
PreparedStatement prepareStatement(String sql)
  throws SQLException
建立一個 PreparedStatement 對象來將參數化的 SQL 陳述式發送到資料庫。 

帶有 IN 參數或不帶有 IN 參數的 SQL 陳述式都可以被先行編譯並儲存在 PreparedStatement 對象中。然後可以有效地使用此對象來多次執行該語句。 

註：為了處理受益於先行編譯的帶參數 SQL 陳述式，此方法進行了最佳化。如果驅動程式支援先行編譯，則 prepareStatement 方法會將該語句發送給資料庫進行先行編譯。一些驅動程式可能不支援先行編譯。在這種情況下，執行 PreparedStatement 對象之前無法將語句發送給資料庫。這對使用者沒有直接的影響；但它的確會影響拋出某些 SQLException 對象的方法。 

使用返回的 PreparedStatement 對象建立的結果集在預設情況下類型為 TYPE_FORWARD_ONLY，並帶有 CONCUR_READ_ONLY 並發層級。 

參數：
sql - 可能包含一個或多個 '?' IN 參數預留位置的 SQL 陳述式 
返回：
包含先行編譯的 SQL 陳述式的新的預設 PreparedStatement 對象 
拋出： 
SQLException - 如果發生資料庫訪問錯誤