mysql有序插入研究part2

之前做了一次mysql有序插入研究，不是我所设想的结果，见http://blog.csdn.net/wj310298/article/details/46649627。

所以这次的测试目标是在并发情况下的性能。测试初始环境不变，变化的只是测试代码。

表为

create table test_insert_tbl(uid int primary key, age int, name char(20) not null default 'abb', sex char(6) not null default 'male');  
create table test_insert_tbl(id int auto_increment primary key, uid int, age int, name char(20) not null default 'abb', sex char(6) not null default 'male', unique key(uid));  

建立测试集的代码与上篇相同，不赘述，直接上测试代码，用20个线程，并发插入10000条数据。

import java.sql.*;
import java.util.concurrent.CountDownLatch;
public class Main1 {
	static final String DB_URL = "jdbc:mysql://172.30.204.36/test";
	static final String USER = "root";
	static final String PASS = "";
	static final int THREAD_NUM = 20;
	static final int INSERT_TIMES = 500;
	public static void main(String[] args) throws ClassNotFoundException, InterruptedException {
		Class.forName("com.mysql.jdbc.Driver");
		final CountDownLatch startGate = new CountDownLatch(THREAD_NUM);
		final CountDownLatch endGate = new CountDownLatch(THREAD_NUM);
		long t1 = System.currentTimeMillis();
		for (int i = 0; i < THREAD_NUM; ++i) {
			final int jj = 2000000 + i;
			Thread t = new Thread(new Runnable() {
				@Override
				public void run() {
					Connection conn = null;
					Statement stmt = null;
					try {
						System.out.println(Thread.currentThread() + ": Connecting to database...");
						conn = DriverManager.getConnection(DB_URL, USER, PASS);
						stmt = conn.createStatement();
						startGate.countDown();
						try {
							try {
								startGate.await();
							} catch (InterruptedException e) {
								e.printStackTrace();
							}
							for (int j = 0; j < INSERT_TIMES; ++j) {
								stmt.execute("insert into test_insert_tbl(uid, age) values(" + (j*THREAD_NUM + jj) + ", 20)");
							}
						} catch (SQLException e) {
							e.printStackTrace();
							System.out.println(Thread.currentThread() + ": Error.");
						}
						System.out.println(Thread.currentThread() + ": Finished.");
					} catch (SQLException e) {
						e.printStackTrace();
						startGate.countDown();
						System.out.println(Thread.currentThread() + ": Error.");
					} finally {
						if (stmt != null)
							try {
								stmt.close();
							} catch (SQLException e1) {
								e1.printStackTrace();
							}
						if (conn != null)
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						endGate.countDown();
					}
				}
			});
			t.start();
		}
		endGate.await();
		long t2 = System.currentTimeMillis();
		System.out.println("Time: " + (double)(t2 - t1)/1000 + "s");
	}
}

测试结果：

	上表	下表
时间	32.267s	32.638s

这次测试的结果并不出乎我的意料，就像物理学上经典的阿斯派克特实验一样，众多物理学家对最终的实验结果并不感到意外。

增加线程数，改为50个线程，并发插入10000条数据。测试结果：

	上表	下表
时间	17.395s	20.402s

接下来要测试update操作了，还是直接上代码。

import java.sql.*;
import java.util.concurrent.CountDownLatch;
public class Main1 {
	static final String DB_URL = "jdbc:mysql://172.30.204.36/test";
	static final String USER = "root";
	static final String PASS = "";
	static final int THREAD_NUM = 50;
	static final int INSERT_TIMES = 2000;
	public static void main(String[] args) throws ClassNotFoundException, InterruptedException {
		Class.forName("com.mysql.jdbc.Driver");
		final CountDownLatch startGate = new CountDownLatch(THREAD_NUM);
		final CountDownLatch endGate = new CountDownLatch(THREAD_NUM);
		long t1 = System.currentTimeMillis();
		for (int i = 0; i < THREAD_NUM; ++i) {
			final int jj = i;
			Thread t = new Thread(new Runnable() {
				@Override
				public void run() {
					Connection conn = null;
					Statement stmt = null;
					try {
						System.out.println(Thread.currentThread() + ": Connecting to database...");
						conn = DriverManager.getConnection(DB_URL, USER, PASS);
						stmt = conn.createStatement();
						startGate.countDown();
						try {
							try {
								startGate.await();
							} catch (InterruptedException e) {
								e.printStackTrace();
							}
							for (int j = 0; j < INSERT_TIMES; ++j) {
								int uid = j*THREAD_NUM + jj;
								stmt.execute("update test_insert_tbl set uid = " + uid + " where uid = " + (uid + 1000000));
							}
						} catch (SQLException e) {
							e.printStackTrace();
							System.out.println(Thread.currentThread() + ": Error.");
						}
						System.out.println(Thread.currentThread() + ": Finished.");
					} catch (SQLException e) {
						e.printStackTrace();
						startGate.countDown();
						System.out.println(Thread.currentThread() + ": Error.");
					} finally {
						if (stmt != null)
							try {
								stmt.close();
							} catch (SQLException e1) {
								e1.printStackTrace();
							}
						if (conn != null)
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
							}
						endGate.countDown();
					}
				}
			});
			t.start();
		}
		endGate.await();
		long t2 = System.currentTimeMillis();
		System.out.println("Time: " + (double)(t2 - t1)/1000 + "s");
	}
}

测试结果，update的性能还不错，达到1w+tps：

	上表	下表
时间	6.673s	5.785s

增加线程数，100个线程，共40w插入，表就保留uid和age两项，测试结果：

	上表	下表
时间	23.575s	21.008s

虽然我的测试还存在许多的不完善之处，但还是希望能有参考意义。其中没有多次重复取平均值是最大的不完善，要批评一下自己的懒。