分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法

由于公司准备用discuz建立企业级的论坛,所以需要进行百万级数据的测试。
搜索了很久没有看到相关的批量插入数据的资料,后来找到一个批量插入帖子的插件,但导入的txt文件超过2350条记录就插入不了。最后只好硬着头皮分析插入帖子的相关sql语句--直接插入到cdb_posts表前台不会显示的。
最后简化为只有标题和内容的帖子需要在两个表插入记录,最后更新cdb_foums对应的一条数据即可。
由于是本人主要是开发java的对php不熟悉,所以测试代码是用java写的,但懂php的人根据我的思路用php也应该很容易搞定了。
最后分享下代码:(用jdbc连接数据库的,需要在java web项目下的lib目录下导入mysq的jar包)

=====================================
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语句插入总是提示过大或者是中文的转换后不能识别,比较郁闷。不过这个不是很重要的问题。
插入的文件InsertIntoSQL .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;  
}

/**
    * 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;

============================
最后通过loadrunner模拟用户点击页面普通页面,和在100万条数据的表下面查询20条数据的页面(通过后台js调用设置就可以了)。

希望官方和那位高手能整出更加高效,轻松实现的方法来。

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

后来发现每个版块最多只能1000分页--虽然上面显示了25000页,但点到1000页时后面就没有分页了,直接输入1001就直接跳转到1000也那里了。也就是说一个版块前台用户看到的最多文章主题只有20000条。无法做到真正的100万数据量(数据库里面确实是100万主题数)。

最后发现这些越来是可以在后台设置的。后台-->全局-->界面与显示-->主题列表页-->每页显示主题数:
主题列表中每页显示主题数目
主题列表最大页数:
主题列表中用户可以翻阅到的最大页数,建议设置为默认值 1000,或在不超过 2500 范围内取值,0 为不限制
==================================================================================
后记:没想到自己发的这篇文章受到这么多人的关注,顺便对回复的问题作个概要回答:
储存过程是我一开始考虑到的,但为了模拟真实插入数据的效率后来否定了。
Oracle SQL Loader 工具对这个应用没有用,因为这个是要用来跟其他系统作对比的,使用的都是mysql,批量提交的方法也测试过了,受到文件大小的限制一次最多只能插入两千条。
非常欢迎给更多的人分享,如果转载文章中能注明作者或网址就更好了。

posted on 2008-09-16 18:16 流风 阅读(10428) 评论(9)  编辑  收藏

评论

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法 2008-09-16 20:05 YZ

一个储存过程不就搞定了么?费这么大神?  回复  更多评论   

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法 2008-09-16 20:22 gfh

反复使用 insert into .... select ...语句来插入数据, 速度又快又好  回复  更多评论   

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法 2008-09-17 01:37 站长论坛

还不错.....  回复  更多评论   

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法 2008-09-17 10:32 skzr

怎么不用批量提交?
18W的时候需要3分钟了  回复  更多评论   

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法 2008-09-17 11:21 ∪∩BUG

收藏备用..  回复  更多评论   

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法 2008-09-17 14:17 Unmi

Oracle 有 SQL Loader 工具很快的。  回复  更多评论   

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法 2008-09-19 11:58 流风

储存过程是我一开始考虑到的,但为了模拟真实插入数据的效率后来否定了。
Oracle SQL Loader 工具对这个应用没有用,因为这个是要用来跟其他系统作对比的,使用的都是mysql,批量提交的方法也测试过了,受到文件大小的限制一次最多只能插入两千条。  回复  更多评论   

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法[未登录] 2008-09-20 11:08

你好 我是出版社的编辑,我看到你博客中的内容,感觉写的非常好,如果想把这些内容和更多的人分享,可以和我联系,把这些东西写成书。现在有一本Java数据库操作的书。
我的邮箱:books_522008@yahoo.com.cn  回复  更多评论   

# re: 分享下自己用sql语句批量插入测试数据(企业级 百万数据级别)的方法 2008-11-10 15:09 keyboardsun@163.com

效率真的太慢。太慢太慢了。用绑定变量,你的效率可以提高10倍,一般10万条数据要控制在3秒以为,才能说凑合。  回复  更多评论   


只有注册用户登录后才能发表评论。


网站导航:
 
<2008年11月>
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456

导航

统计

常用链接

留言簿(1)

随笔档案

相册

搜索

最新评论

阅读排行榜

评论排行榜