sqlロット実行効率比較

環境:
　　MySQL 5.1
　　RedHat Linux AS 5
　　JavaSE 1.5
DbConnectionBrokerマイクロデータベース接続プール
テストのシナリオ:
10万回のInsert文を実行し、異なる方法を使用します.
Aグループ:静的SQL、自動コミット、トランザクション制御なし(MyISAMエンジン)
1、条ごとに10万回実行する
2、バッチ実行は10万をmバッチに分け、各バッチn本、複数のバッチスキームに分けて実行する.
Bグループ:プリコンパイルモードSQL、自動コミット、トランザクション制御なし(MyISAMエンジン)
1、条ごとに10万回実行する
2、バッチ実行は10万をmバッチに分け、各バッチn本、複数のバッチスキームに分けて実行する.
　　-------------------------------------------------------------------------------------------
Cグループ:静的SQL、自動的にコミットしない、トランザクション制御(InnoDBエンジン)
1、条ごとに10万回実行する
2、バッチ実行は10万をmバッチに分け、各バッチn本、複数のバッチスキームに分けて実行する.
Dグループ:プリコンパイルモードSQL、自動コミットせず、トランザクション制御(InnoDBエンジン)
1、条ごとに10万回実行する
2、バッチ実行は10万をmバッチに分け、各バッチn本、複数のバッチスキームに分けて実行する.
今回は主にC、Dグループをテストし、テスト結果を出します.

DROP TABLE IF EXISTS tuser; 
　　CREATE TABLE tuser ( 
　　id bigint(20) NOT NULL AUTO_INCREMENT, 
　　name varchar(12) DEFAULT NULL, 
　　remark varchar(24) DEFAULT NULL, 
　　createtime datetime DEFAULT NULL, 
　　updatetime datetime DEFAULT NULL, 
　　PRIMARY KEY (id) 
　　) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 

 

　//　C、D     ： 
　　package testbatch; 
　　import java.io.IOException; 
　　import java.sql.*; 
　　/** 
　　* JDBC  Insert  ( ) 
　　* 
　　* @author leizhimin 2009-7-29 10:03:10 
　　*/ 
　　public class TestBatch { 
　　public static DbConnectionBroker myBroker = null; 
　　static { 
　　try { 
　　myBroker = new DbConnectionBroker("com.mysql.jdbc.Driver", 
　　"jdbc:mysql://192.168.104.163:3306/testdb", 
　　"vcom", "vcom", 2, 4, 
　　"c:\\testdb.log", 0.01); 
　　} catch (IOException e) { 
　　e.printStackTrace(); 
　　} 
　　} 
　　/** 
　　*         
　　* 
　　* @throws SQLException       
　　*/ 
　　public static void init() throws SQLException { 
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　Statement stmt = conn.createStatement(); 
　　stmt.addBatch("DROP TABLE IF EXISTS tuser"); 
　　stmt.addBatch("CREATE TABLE tuser (
" + 
　　" id bigint(20) NOT NULL AUTO_INCREMENT,
" + 
　　" name varchar(12) DEFAULT NULL,
" + 
　　" remark varchar(24) DEFAULT NULL,
" + 
　　" createtime datetime DEFAULT NULL,
" + 
　　" updatetime datetime DEFAULT NULL,
" + 
　　" PRIMARY KEY (id)
" + 
　　") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8"); 
　　stmt.executeBatch(); 
　　conn.commit(); 
　　myBroker.freeConnection(conn); 
　　} 
　　/** 
　　* 100000   SQL   
　　* 
　　* @throws Exception       
　　*/ 
　　public static void testInsert() throws Exception { 
　　init(); //      
　　Long start = System.currentTimeMillis(); 
　　for (int i = 0; i < 100000; i++) { 
　　String sql = "
" + 
　　"insert into testdb.tuser 
" + 
　　"\t(name, 
" + 
　　"\tremark, 
" + 
　　"\tcreatetime, 
" + 
　　"\tupdatetime
" + 
　　"\t)
" + 
　　"\tvalues
" + 
　　"\t('" + RandomToolkit.generateString(12) + "', 
" + 
　　"\t'" + RandomToolkit.generateString(24) + "', 
" + 
　　"\tnow(), 
" + 
　　"\tnow()
" + 
　　")"; 
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　Statement stmt = conn.createStatement(); 
　　stmt.execute(sql); 
　　conn.commit(); 
　　myBroker.freeConnection(conn); 
　　} 
　　Long end = System.currentTimeMillis(); 
　　System.out.println("    100000 Insert  ，   ：" + (end - start) / 1000f + " !"); 
　　} 
　　/** 
　　*        SQL   
　　* 
　　* @param m    
　　* @param n      
　　* @throws Exception       
　　*/ 
　　public static void testInsertBatch(int m, int n) throws Exception { 
　　init(); //      
　　Long start = System.currentTimeMillis(); 
　　for (int i = 0; i < m; i++) { 
　　//        
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　Statement stmt = conn.createStatement(); 
　　for (int k = 0; k < n; k++) { 
　　String sql = "
" + 
　　"insert into testdb.tuser 
" + 
　　"\t(name, 
" + 
　　"\tremark, 
" + 
　　"\tcreatetime, 
" + 
　　"\tupdatetime
" + 
　　"\t)
" + 
　　"\tvalues
" + 
　　"\t('" + RandomToolkit.generateString(12) + "', 
" + 
　　"\t'" + RandomToolkit.generateString(24) + "', 
" + 
　　"\tnow(), 
" + 
　　"\tnow()
" + 
　　")"; 
　　//      
　　stmt.addBatch(sql); 
　　} 
　　stmt.executeBatch(); //      
　　conn.commit(); 
　　// stmt.clearBatch(); //      
　　stmt.close(); 
　　myBroker.freeConnection(conn); //     
　　} 
　　Long end = System.currentTimeMillis(); 
　　System.out.println("    " + m + "*" + n + "=" + m * n + " Insert  ，   ：" + (end - start) / 1000f + " !"); 
　　} 
　　/** 
　　* 100000    SQL   
　　* 
　　* @throws Exception       
　　*/ 
　　public static void testInsert2() throws Exception { //    100000 Insert  ，   ：40.422 ! 
　　init(); //      
　　Long start = System.currentTimeMillis(); 
　　String sql = "" + 
　　"insert into testdb.tuser
" + 
　　" (name, remark, createtime, updatetime)
" + 
　　"values
" + 
　　" (?, ?, ?, ?)"; 
　　for (int i = 0; i < 100000; i++) { 
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　PreparedStatement pstmt = conn.prepareStatement(sql); 
　　pstmt.setString(1, RandomToolkit.generateString(12)); 
　　pstmt.setString(2, RandomToolkit.generateString(24)); 
　　pstmt.setDate(3, new Date(System.currentTimeMillis())); 
　　pstmt.setDate(4, new Date(System.currentTimeMillis())); 
　　pstmt.executeUpdate(); 
　　conn.commit(); 
　　pstmt.close(); 
　　myBroker.freeConnection(conn); 
　　} 
　　Long end = System.currentTimeMillis(); 
　　System.out.println("    100000 Insert  ，   ：" + (end - start) / 1000f + " !"); 
　　} 
　　/** 
　　*         SQL   
　　* 
　　* @param m    
　　* @param n      
　　* @throws Exception       
　　*/ 
　　public static void testInsertBatch2(int m, int n) throws Exception { 
　　init(); //      
　　Long start = System.currentTimeMillis(); 
　　String sql = "" + 
　　"insert into testdb.tuser
" + 
　　" (name, remark, createtime, updatetime)
" + 
　　"values
" + 
　　" (?, ?, ?, ?)"; 
　　for (int i = 0; i < m; i++) { 
　　//        
　　Connection conn = myBroker.getConnection(); 
　　conn.setAutoCommit(false); 
　　PreparedStatement pstmt = conn.prepareStatement(sql); 
　　for (int k = 0; k < n; k++) { 
　　pstmt.setString(1, RandomToolkit.generateString(12)); 
　　pstmt.setString(2, RandomToolkit.generateString(24)); 
　　pstmt.setDate(3, new Date(System.currentTimeMillis())); 
　　pstmt.setDate(4, new Date(System.currentTimeMillis())); 
　　//      
　　pstmt.addBatch(); 
　　} 
　　pstmt.executeBatch(); //      
　　conn.commit(); 
　　// pstmt.clearBatch(); //      
　　pstmt.close(); 
　　myBroker.freeConnection(conn); //     
　　} 
　　Long end = System.currentTimeMillis(); 
　　System.out.println("    " + m + "*" + n + "=" + m * n + " Insert  ，   ：" + (end - start) / 1000f + " !"); 
　　} 
　　public static void main(String[] args) throws Exception { 
　　init(); 
　　Long start = System.currentTimeMillis(); 
　　System.out.println("--------C   ----------"); 
　　testInsert(); 
　　testInsertBatch(100, 1000); 
　　testInsertBatch(250, 400); 
　　testInsertBatch(400, 250); 
　　testInsertBatch(500, 200); 
　　testInsertBatch(1000, 100); 
　　testInsertBatch(2000, 50); 
　　testInsertBatch(2500, 40); 
　　testInsertBatch(5000, 20); 
　　Long end1 = System.currentTimeMillis(); 
　　System.out.println("C       ，      ：" + (end1 - start) / 1000f + " !"); 
　　System.out.println("--------D   ----------"); 
　　testInsert2(); 
　　testInsertBatch2(100, 1000); 
　　testInsertBatch2(250, 400); 
　　testInsertBatch2(400, 250); 
　　testInsertBatch2(500, 200); 
　　testInsertBatch2(1000, 100); 
　　testInsertBatch2(2000, 50); 
　　testInsertBatch2(2500, 40); 
　　testInsertBatch2(5000, 20); 
　　Long end2 = System.currentTimeMillis(); 
　　System.out.println("D       ，      ：" + (end2 - end1) / 1000f + " !"); 
　　} 
　　}

 　　
 
実行結果:
 
------Cグループテスト--------
単一のInsert操作を100000個実行し、合計時間:103.656秒!
100*1000=100000個のInsert操作を一括実行し、合計時間:31.328秒!
250*400=100000個のInsert操作を一括実行し、合計時間:31.46秒!
400*250=100000個のInsert操作を一括実行し、合計時間:31.75秒!
500*200=100000本のInsert操作を一括実行し、合計時間:31.438秒!
1000*100=100000件のInsert操作を一括実行し、合計時間:31.968秒!
2000*50=100000件のInsert操作を一括実行し、合計32.938秒かかります.
2500*40=100000個のInsert操作を一括実行し、合計時間:33.41秒!
5000*20=100000本のInsert操作を一括実行し、合計時間:35.265秒!
Cグループのテストプロセスが終わり、すべてのテストに時間がかかります:363.656秒!
------Dグループテスト--------
単一のInsert操作を100000個実行し、合計時間:107.61秒!
100*1000=100000件のInsert操作を一括で実行し、合計32.64秒かかります.
250*400=100000個のInsert操作を一括実行し、32.641秒の時間を費やします.
400*250=100000個のInsert操作を一括実行し、合計33.109秒かかります.
500*200=100000個のInsert操作を一括で実行し、合計32.859秒かかります.
1000*100=100000個のInsert操作を一括実行し、合計時間:33.547秒!
2000*50=100000個のInsert操作を一括で実行し、合計34.32秒かかります.
2500*40=100000個のInsert操作を一括実行し、合計34.672秒かかります.
5000*20=100000本のInsert操作を一括で実行し、合計36.672秒かかります.
Dグループのテストプロセスが終了し、すべてのテストに時間がかかります:378.922秒!
テストの結果は思いもよらなかったでしょう.最短時間で前編を超えました.テスト結果全体を観察すると,ストライプごとの実行効率が低すぎるため,総時間が長いことが分かった.
結論:
本試験条件の下で、結論を出す:
データベース接続プール制御下、自動コミットなし、トランザクション制御(InnoDBエンジン)
1、逐条実行の効率は非常に低く、できるだけ逐条実行を避ける.
2、取引制御の下で、静的SQLの効率は前処理SQLより高い.
3、バッチのサイズは効率に大きな影響を及ぼします.一般的に、トランザクション制御の下で、バッチのサイズは100-1000の間で適切です.
4、最適化方式については、上記のバッチ処理が優れた最適化戦略である.
大まとめ:
前回のトランザクションのないテスト結果と比較して、包括的な結論を出しました.
1、接続プールは最も基本的で最も重要な最適化戦略であり、常に性能を大幅に向上させることができる.
2、バッチ処理は効率的にいつも1つの処理より優位で、処理するデータの記録数が大きいほど、バッチ処理の優位性が明らかになり、バッチ処理にはデータベースへのリンク回数を減らし、データベースの圧力を軽減する利点がある.
3、バッチがSQLを実行する場合、バッチのバッチのサイズはデータベースのスループットとハードウェア構成と大きく関係しており、テストによって最適なバッチサイズを見つける必要があります.一般的に50-1000の間です.
4、前処理SQLは事務のない表で効率が高く、実物がある場合は静的SQLより少し及ばない.しかし、事前定義されたSQLには、消費されるメモリが少なく、静的SQL列が大量のメモリリソースを消費し、メモリオーバーフローの問題を引き起こしやすいというメリットがあります.したがって、一括実行時に事前定義されたSQLを優先的に選択できます.
5.バッチの実行時に、各バッチの実行が完了する後、pstmtを明示的に呼び出すことが望ましい.close()またはstmt.close()メソッドを使用して、実行したSQL文をできるだけ早く解放し、メモリの使用率を向上させます.
6、大量のSELECT操作がある場合、MyISAMはより良い選択である.INSERTとUPDATE操作が多数あるテーブルでは、InnoDBの方が効率的です.
7、テスト結果は特定の状況のいくつかの事実しか反映できないが、以上の最適化戦略は普遍的な戦略であり、最適な戦略を探す時間を明らかに短縮することができ、効率の要求が高いプログラムに対して、同時性などのテストを行うべきである.
8、テストは大変なことで、あなたの最適化が有効であることを証明するために多くの事実が必要です.経験だけではいけません.機械の環境が異なるので、使い方も違います.