discuz论坛用sql语句批量插入测试数据（百万级别）

于 2010-09-15 10:07:00 发布
阅读量8.2k 收藏 3
点赞数
分类专栏： database 文章标签： sql 测试 string insert 数据库 loadrunner

转载于：http://www.8100.cc/space-2065-do-blog-id-7441.html

 

 

 

discuz论坛用sql语句批量插入测试数据，公司要测试discuz论坛的性能，要求插入100万条以上数据进行测试，开始通过loadrunner模拟用户插入，只能用一个用户插入，一个多小时才插入四百条，平均16秒才插入一条记录，比手动发帖还慢！后来想到以前用数据库恢复可以直接恢复数据，那么直接改sql文件里面的sql语句就可以插入数据了，但是弄了半天很不幸，数据是插入到数据库表了，但论坛前台没有显示。后来一看才知道还有几个关联表没有修改数据。而且sql文件达到1.85mb的时候包文件超过长度的异常（官方备份以1.95mb分割文件的），所以即使知道里面的sql语句怎么修改也无法预知什么时候改分割文件（而且100万条数据分割起来起码也得上百个2mb左右的文件了－－很累啊！）。

断了这条路后有通过搜索，终于搜索到了一个比较好的批量添加帖子是插件，安装，到正常使用花了一个多小时，最后还得根据情况写个生成数据文件的java类，通过测试一次插入两千条数据没有问题，但到2500条就不行了，最终测试效果是2350条为峰值。由于这个插件是需要到后台点击几次进去再填写表单－－提交才能插入的，如果每次手动插入的话按2000条一次，100万条记录也得500次（同时得生成对应的500个资源文件）－－累啊！

后来想通过Ajax来自动完成表单的提交动作，但是很遗憾自己对php不熟悉，捣鼓了半天都没有成功，重新写了action也不行，后来看到插件里面提交动作其实只是执行几个表的关联操作，在网上又搜索了很久没有搜索到相关资料，只好硬着头皮自己搞了，先一个一个的分析里面是sql语句，后来分别插入还是不行，最后卸载掉discuz再重新安装，安装后先备份数据库，然后插入一条简单的记录，再次备份数据库，最后把两个文件通过版本比较工具对比这两个备份文件的异同，最后发现只要两行不同－－插入了两个表的记录，跟重装前分析的一样（updata的sql语句不会显示在备份文件里面的－－但是这个正是决定前台显示的关键）。找到原因后就好办了，再次通过sql语句插入一条记录，再更新一下，奇迹发生了：前台跟手动添加一样显示了，看来sql语句是成功了，那么接下来就是根据情况写循环语句了。但mysql不支持for语句，写存储过程也比较复杂（整了半个小时没有搞定），后来就直接通过jdbc连接数据库插入操作了－－更接近真实的插入数据。最终通过测试：一次插入20万条记录耗时3分钟左右（1g内存的windows xp系统，同时安装了一整套discuz系统和启动了eclipse、tomcat（数据库连接用到））。如果是放在服务器上应该快上几倍，不过这个是单条记录的插入，一个完整的插入过程应该是三步的，也就是说插入20万条记录可能耗时十分钟。有了数据页面点击效果就可以用loadrunner模拟分析详细的性能数据了。

最后分享下代码：(经过实际应用已经改进了新的代码测试了，放在后面)

package com.test.utc;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.test.utc.DBManager;

public class InsertIntoSQL {
public static void main (String[] agrs)
{
   Date dt=new Date();//如果不需要格式,可直接用dt,dt就是当前系统时间
        DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//设置显示格式
        String nowTime="";
    nowTime= df.format(dt);//用DateFormat的format()方法在dt中获取并以yyyy/MM/dd HH:mm:ss格式显示
  
    DBManager dbm = new DBManager("localhost:6033", "discuz", "discuz", "discuzexp");
    System.out.println(nowTime);
   String sql = "";
  
   long i = 1;
   for(i=300000; i < 500000; i++)
   {
    //sql = "INSERT INTO cdb_posts VALUES ('" + i + "','2','" + i + "','1',0x61646d696e,'1',0x74657374,'1221120302',0x74657374,0x3139322e3136382e302e3335,'0','0','0','0','-1','-1','0','0','0','0','0')";
    sql = "INSERT INTO cdb_threads VALUES ('" + i + "','2','0','0','0','0',0x61646d696e,'1',0x74657374,'1221120302','1221120302',0x61646d696e,'1','0','0','0','0','0','0','0','0','0','0','0','0','0','0')";
   
   
    //sql = "INSERT INTO cdb_posts VALUES ('" + i + "','4','" + i + "','1',0x61646d696e,'1',0x74657374,'1221120302',0x74657374,0x3139322e3136382e302e3335,'0','0','0','0','-1','-1','0','0','0','0','0')";
    //sql = "INSERT INTO cdb_threads VALUES ('" + i + "','4','0','0','0','0',0x61646d696e,'1',0x74657374,'1221120302','1221120302',0x61646d696e,'1','0','0','0','0','0','0','0','0','0','0','0','0','0','0')";
    dbm.executeUpdate(sql);
   }
  
   //更新论坛显示的相关数据，添加完成后执行一次就可以了
   /*
   sql = "UPDATE cdb_forums SET lastpost='0xd7eebcd1b2d9c5ccb9a4bedf2d31313732', threads=499999, posts=499999, todayposts=499999 WHERE fid=2";
   dbm.executeUpdate(sql);
   */
  
   Date dt2 = new Date();
        DateFormat df2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String nowTime2 ="";
    nowTime2 = df2.format(dt2);
   System.out.println(nowTime2);
  
}

}

=====================================

DBManager.java

=====================================

package com.test.utc;

import java.sql.*;


public class DBManager {

//用户名

private String user = "";

//密码

private String password = "";

//主机

private String host = "";

//数据库名字

private String database = "";


/*

     private String url="jdbc:mysql://"+host+"/"+"useUnicode=true&characterEncoding=GB2312";

   */

private String url ="";

private Connection con = null;


Statement stmt;

/**

   * 根据主机、数据库名称、数据库用户名、数据库用户密码取得连接。

   * @param host String

   * @param database String

   * @param user String

   * @param password String

   */

public DBManager(String host, String database, String user, String password) {


    this.host = host;

    this.database = database;

    this.user = user;

this.password = password;

//显示中文

    this.url = "jdbc:mysql://" + host + "/" + database +

        "?useUnicode=true&characterEncoding=GB2312";


    try {

      Class.forName("org.gjt.mm.mysql.Driver");

    }

    catch (ClassNotFoundException e) {

      System.err.println("class not found:" + e.getMessage());

    }


    try {

      con = DriverManager.getConnection(this.url, this.user, this.password);

//连接类型为ResultSet.TYPE_SCROLL_INSENSITIVE,                                 ResultSet.CONCUR_READ_ONLY

      stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

                                 ResultSet.CONCUR_READ_ONLY);

    }

    catch (SQLException a) {

      System.err.println("sql exception:" + a.getMessage());

    }

}

/**

* 返回取得的连接

*/

public Connection getCon() {

    return con;

}

/**

* 执行一条简单的查询语句

* 返回取得的结果集

*/

public ResultSet executeQuery(String sql) {

    ResultSet rs = null;

    try {

      rs = stmt.executeQuery(sql);

    }

    catch (SQLException e) {

      e.printStackTrace();

    }

    return rs;

}

/**

* 执行一条简单的更新语句

* 执行成功则返回true

*/

public boolean executeUpdate(String sql) {

    boolean v = false;

    try {

      v = stmt.executeUpdate(sql) > 0 ? true : false;

    }

    catch (SQLException e) {

      e.printStackTrace();

    }

    finally {
      return v;

    }

}

}

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

新的测试代码：一次同时插入两个表，for循环后更新数据。

为了方便同时加入了纳米级别的时间差，并且把详细信息输出到指定文件中。

转换16进制的单独测试没有问题，但转换后作为sql语句插入总是提示过大或者是中文的转换后不能识别，比较郁闷。不过这个不是很重要的问题。DBManager.java 跟上面的一样。

插入的文件如下：

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

package com.test.utc;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.test.utc.DBManager;

public class InsertIntoSQL
{
public final static String path = "D:/ComsenzEXP/wwwroot/discuz/attachments/testLog.txt";

public static void main (String[] agrs)
{
   //System.out.println(toHexString("测试"));
   //System.out.println(toStringHex("0x74657374"));
   insertData(300000,4,100000,"test","test");
  
}


/*
* @deprecated 插入数据
* @param startNumber
* @param endNumber
* @param fourmid
* @param postsNumber
* @param todayposts
*/
public static void insertData(long insertNumber,int fourmid,long todayposts,String title,String content)
{
   String message = "插入：" + insertNumber + "条记录";
   System.out.println(message);
   appendMethod(path,message);
  
   long startTime = System.nanoTime();
   message = "insertData startTime:" + startTime;
   System.out.println(message);
   appendMethod(path,message);
   
   
    DBManager dbm = new DBManager("localhost:6033", "discuz", "discuz", "discuzexp");
   String sql = "";
   String pid = getLastRowByColumnValue(dbm,"cdb_posts","pid");
   long startNumber = Long.parseLong(pid) + 1;
   long endNumber = startNumber + insertNumber;
   long i = 1;
   //content = toHexString(content);
   //0x6d4b8bd551855bb9
   //0x6d4b8bd5
   //String insertTitle = title;
   for(i = startNumber; i < endNumber; i++)
   {
    //insertTitle = insertTitle + i;
    //insertTitle = toHexString(insertTitle + i);
   
    sql = "INSERT INTO cdb_posts VALUES ('" + i + "','" + fourmid + "','" + i + "','1',0x61646d696e,'1',0x74657374,'1221120302',0x74657374,0x3139322e3136382e302e3335,'0','0','0','0','-1','-1','0','0','0','0','0')";
    dbm.executeUpdate(sql);
    sql = "INSERT INTO cdb_threads VALUES ('" + i + "','" + fourmid + "','0','0','0','0',0x61646d696e,'1',0x74657374,'1221120302','1221120302',0x61646d696e,'1','0','0','0','0','0','0','0','0','0','0','0','0','0','0')";   
    dbm.executeUpdate(sql);
   
    /*
    sql = "INSERT INTO cdb_posts VALUES ('" + i + "','" + fourmid + "','" + i + "','1',0x61646d696e,'1','" + insertTitle + "','1221120302','" + content + "',0x3139322e3136382e302e3335,'0','0','0','0','-1','-1','0','0','0','0','0')";
    dbm.executeUpdate(sql);
    sql = "INSERT INTO cdb_threads VALUES ('" + i + "','" + fourmid + "','0','0','0','0',0x61646d696e,'1','" + insertTitle + "','1221120302','1221120302',0x61646d696e,'1','0','0','0','0','0','0','0','0','0','0','0','0','0','0')";   
    dbm.executeUpdate(sql);
    */
   }
   todayposts = endNumber - 499999;
   sql = "UPDATE cdb_forums SET lastpost='0x74657374', threads=" + endNumber + ", posts=" + endNumber + ", todayposts=" + todayposts + " WHERE fid=" + fourmid;
   dbm.executeUpdate(sql);
  
   message = "insertData endTime:" + System.nanoTime();
   System.out.println(message);
   appendMethod(path,message);
  
   long estimatedTime = System.nanoTime() - startTime;
   message = "相差(用时):" + estimatedTime + "纳秒," + estimatedTime/1000000000 + "秒/r/n/r/n";//1秒=10亿纳秒
   System.out.println(message);
   appendMethod(path,message);
}

/*
* @deprecated 获取最后一行某列的值
* @param table
* @param column
*/
public static String getLastRowByColumnValue(DBManager dbm,String table,String column)
{
   String sql = "";
   sql = "select " + column + " from " + table + " order by " + column + " desc limit 1";
   ResultSet rs = dbm.executeQuery(sql);
   String str = "";
   try {
    while( rs.next())
    {
     str = rs.getString(column);
    }
   } catch (SQLException e) {
  
    e.printStackTrace();
   }
   return str;
}

//转化字符串为十六进制编码
public static String toHexString(String s)  
{  
   String str="";  
   for (int i=0;i<s.length();i++)  
   {  
    int ch = (int)s.charAt(i);  
    String s4 = Integer.toHexString(ch);  
    str = str + s4;
   }  
   return "0x" + str;  
}

//转化十六进制编码为字符串
public static String toStringHex(String s)
{
   if("0x".equals(s.substring(0, 2)))
   {
    s =s.substring(2);
   }
   byte[] baKeyword = new byte[s.length()/2];
   for(int i = 0; i < baKeyword.length; i++)
   {
      try
      {
       baKeyword[i] = (byte)(0xff & Integer.parseInt(s.substring(i*2, i*2+2),16));
      }
      catch(Exception e)
      {
       e.printStackTrace();
      }
   }
   
   try
   {
    s = new String(baKeyword, "utf-8");//UTF-16le:Not
   }
   catch (Exception e1)
   {
    e1.printStackTrace();
   }
   return s;
}

/**
    * B方法追加文件：使用FileWriter
    * @param fileName
    * @param content
    */
public static void appendMethod(String fileName, String content){
    try {
     //打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件
     FileWriter writer = new FileWriter(fileName, true);
     writer.write(content + "/r/n");
     writer.close();
    } catch (IOException e) {
     e.printStackTrace();
    }
}


}

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

测试结果

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

插入：1条记录
insertData startTime:996017275659
insertData endTime:996570971450
相差:555546585纳秒,0秒


插入：10条记录
insertData startTime:1005210041220
insertData endTime:1005774437812
相差:566248503纳秒,0秒


插入：100条记录
insertData startTime:1014529111102
insertData endTime:1015376139146
相差:848823244纳秒,0秒


插入：1000条记录
insertData startTime:1024027419406
insertData endTime:1027460295093
相差:3434544614纳秒,3秒

 


插入：10000条记录
insertData startTime:1098779316644
insertData endTime:1116864493392
相差:18086540050纳秒,18秒


插入：20000条记录
insertData startTime:1172427124676
insertData endTime:1207721237196
相差:35295511580纳秒,35秒


插入：30000条记录
insertData startTime:1221623540422
insertData endTime:1273396534755
相差:51774426358纳秒,51秒


插入：40000条记录
insertData startTime:1304022174326
insertData endTime:1373074817609
相差:69054081175纳秒,69秒


插入：50000条记录
insertData startTime:1387615577957
insertData endTime:1474072398866
相差:86458228630纳秒,86秒


插入：60000条记录
insertData startTime:1498220329120
insertData endTime:1602732788105
相差:104513895481纳秒,104秒


插入：70000条记录
insertData startTime:1713667130878
insertData endTime:1834998856279
相差:121333178658纳秒,121秒


插入：80000条记录
insertData startTime:1859249070482
insertData endTime:1998321805424
相差:139074161939纳秒,139秒


插入：90000条记录
insertData startTime:2025945141631
insertData endTime:2182073256517
相差:156129545235纳秒,156秒


插入：100000条记录
insertData startTime:2220057424616
insertData endTime:2395134985871
相差:175078943553纳秒,175秒


插入：120000条记录
insertData startTime:2658822511240
insertData endTime:2866959720299
相差:208138247459纳秒,208秒


插入：150000条记录
insertData startTime:2886769641392
insertData endTime:3146130152016
相差:259361930078纳秒,259秒


插入：180000条记录
insertData startTime:3482758554788
insertData endTime:3795354906559
相差:312598627479纳秒,312秒


插入：200000条记录
insertData startTime:4023631035788
insertData endTime:4370300796888
相差(用时):346671225253纳秒,346秒


插入：250000条记录
insertData startTime:4460471629494
insertData endTime:4894180641115
相差(用时):433710430516纳秒,433秒


插入：300000条记录
insertData startTime:4971718038186
insertData endTime:5489207947378
相差(用时):517491270818纳秒,517秒


＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

另外分享几条删除，更新的sql语句

DELETE FROM cdb_posts where pid > 1000000
DELETE FROM cdb_threads where tid > 1000000
已删除行数： 1262140 (查询花费 130.2156 秒)
UPDATE cdb_forums SET lastpost='0x74657374', threads=1000000, posts=1000000, todayposts=500001 WHERE fid=4;
select pid from cdb_posts order by pid desc limit 1;

zgmzyr
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值