MySQL的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功能。

上一篇: 好玩的gif动画
下一篇: MagickWand For PHP安装

点击获得Trackback地址

发表评论

内 容: