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() {@Overridepublic 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.267s32.638s这次测试的结果并不出乎我的意料，就像物理学上经典的阿斯派克特实验一样，众多物理学家对最终的实验结果并不感到意外。

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

 上表下表时间17.395s20.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() {@Overridepublic 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.673s5.785s

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

 上表下表时间23.575s21.008s

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