测试insert、replace和update语句执行效率

 

测试程序

#include <stdio.h>

#include <stdlib.h>

#include <string>

#include <iostream>

#include <time.h>

#include "/usr/local/include/mysql/mysql.h"

 

using namespace std;

 

MYSQL mysql;

 

void exiterr(int exitcode)

{

         printf("%s\n", mysql_error(&mysql));

         exit( exitcode );

}

 

// create ip, from 10.0.0.0~10.0.255.255

void init_ip()

{

         string insert_sql;

        

         string ip;

         char vbuf1[5];

         char vbuf2[5];

 

         for(int i=0; i<=255; i++){

                   for(int j=0; j<=255; j++){

                            ip = "10.0.";

                            memset(vbuf1,0,sizeof(vbuf1));

                            sprintf(vbuf1,"%d.",i);

                            ip.append(vbuf1);

 

                            memset(vbuf2,0,sizeof(vbuf2));

                            sprintf(vbuf2,"%d",j);

                            ip.append(vbuf2);

                           

                            // 以下是测试语句

                            //insert_sql = "replace into test_data(ip,mobilenum) values('";

                            //insert_sql += ip + "','13200000001')" ;

                            insert_sql = "update test_data set mobilenum='13200000002' where ip='" + ip + "'";

                            if (mysql_query(&mysql,insert_sql.c_str())) exiterr(3);

                   }

         }

}

 

int main()

{

         int i = 0;

         time_t  timeVal;

 

         printf("************* begin mysql operation *****************\n");

         mysql_init(&mysql);

        

         if(!mysql_real_connect(&mysql,"192.168.0.100","user","pass","testDB",0,NULL,0)){

                   printf("connect to db error,%s\n", mysql_error(&mysql));

                   return 1;

         }

 

         time(&timeVal);

         cout << "begin time: " << timeVal << endl;

 

         init_ip();

 

         time(&timeVal);

         cout << "end time: " << timeVal << endl;

 

         mysql_close(&mysql);

         printf("************* end mysql operation *****************\n");

 

         return 0;

 

}

 

表结构：

CREATE TABLE gtpDB.test_data(

  ip varchar(15) NOT NULL,

  mobilenum varchar(11) NOT NULL,

  gtptime int(10) UNSIGNED NOT NULL,

  PRIMARY KEY (Ip),

  INDEX (Ip)

) TYPE=MyISAM;

 

操作数据表数据条数：65536

1、insert测试

         insert_sql = "insert into test_data(ip,mobilenum) values('";

         insert_sql += ip + "','13200000001')" ;

 

         执行时间：10s

 

2、replace测试

使用REPLACE插入一条记录时，如果不重复，REPLACE就和INSERT的功能一样，如果有重复记录，REPLACE就使用新记录的值来替换原来的记录值。使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。在使用REPLACE时，表中必须有唯一有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。

 

2.1、表空时的测试

         清除表中数据，此时相当于insert操作

         insert_sql = "replace into test_data(ip,mobilenum) values('";

         insert_sql += ip + "','13200000001')" ;

 

         执行时间10s

 

2.2、表不空时的操作，即此时有65536条数据

         紧接着2.1测试

         insert_sql = "replace into test_data(ip,mobilenum) values('";

         insert_sql += ip + "','13200000002')" ;

 

         此时相当于要修改原有的mobilenum数据

 

         执行时间：10S

 

 

3、update测试

         紧接着2.2，表不空时，更新数据

insert_sql = "update test_data set mobilenum='13200000003' where ip='" + ip + "'";

 

执行时间：15s

 

 

 

【结论】：replace效率不错，且概括了insert和delte/insert功能。