最近闲来无事，想看看mysql预处理执行效率和直接执行的效率差别有多大。先去网上查了查，两种声音都有，没办法，只好自己来验证验证。
我打算测试插入的效率，下来建一个表
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` varchar(64) NOT NULL DEFAULT '',
`col2` char(60) DEFAULT NULL,
`col3` blob,
`col4` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
接下来贴上两段分别用预处理插入的语句和直接插入的语句
预处理代码
bool insert()
{
if (mysql_stmt_prepare(stmt, "INSERT test(col1,col2,col3,col4) values(?,?,?,?)", strlen("INSERT test(col1,col2,col3,col4) values(?,?,?,?)")))
{
fprintf(stderr, " mysql_stmt_prepare(), insert failed\r\n");
fprintf(stderr, " %s\r\n", mysql_stmt_error(stmt));
return false;
}
//测试mysql_stmt_field_count()作用
//printf("mysql_stmt_field_count return value[%d]\r\n",mysql_stmt_field_count(stmt));
MYSQL_BIND bind[4];
unsigned long length[4];
my_bool is_null[4];
memset(bind,0,sizeof(bind));
memset(length,0,sizeof(length));
memset(is_null,0,sizeof(is_null));
char varchar_var[STRING_SIZE] = "hello yg";
char char_var[STRING_SIZE] = "hello dl";
struct st_stud st;
st.id = 1;
strncpy(st.name,"shine.yang",16);
st.score = 10.0f;
time_t cur_t = time(NULL);
struct tm cur_tm;
localtime_r(&cur_t,&cur_tm);
MYSQL_TIME ts;
ts.year = cur_tm.tm_year+1900, ts.month = cur_tm.tm_mon+1, ts.day = cur_tm.tm_mday,ts.hour = cur_tm.tm_hour, ts.minute = cur_tm.tm_min, ts.second = cur_tm.tm_sec;
bind[0].buffer_type= MYSQL_TYPE_STRING;
bind[0].buffer= varchar_var;
bind[0].buffer_length = STRING_SIZE;
length[0] = strlen(varchar_var);
bind[0].length = &length[0];
bind[0].is_null= &is_null[0];
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= char_var;
bind[1].buffer_length = STRING_SIZE;
length[1] = strlen(char_var);
bind[1].length = &length[1];
bind[1].is_null= &is_null[1];
bind[2].buffer_type= MYSQL_TYPE_BLOB;
bind[2].buffer= (char *)&st;
bind[2].buffer_length = sizeof(st);
length[2] = sizeof(st);
bind[2].length = &length[2];
bind[2].is_null= &is_null[2];
bind[3].buffer_type = MYSQL_TYPE_TIMESTAMP;
bind[3].buffer= (char *)&ts;
bind[3].buffer_length = sizeof(ts);
length[3] = sizeof(ts);
bind[3].length = &length[3];
bind[3].is_null= &is_null[3];
dword time1 = get_time();
for ( int i = 0; i < INSERT_ITEM_CNT;++i )
{
if ( mysql_stmt_bind_param(stmt,bind) )
{
fprintf(stderr,"mysql_stmt_bind_param failed,err = %s\r\n",mysql_stmt_error(stmt));
return false;
}
if ( mysql_stmt_execute(stmt) )
{
fprintf(stderr,"mysql_stmt_excute failed,err = %s\r\n",mysql_stmt_error(stmt));
return false;
}
//printf("Total effect rows [%d]\r\n",(int)mysql_stmt_affected_rows(stmt));
}
dword time2 = get_time();
fprintf(stdout,"stmt insert %d items,during time[%d]\r\n",INSERT_ITEM_CNT,time2-time1);
return true;
}
直接插入的代码
bool insert()
{
char varchar_var[STRING_SIZE] = "hello yg";
char char_var[STRING_SIZE] = "hello dl";
struct st_stud st;
st.id = 1;
strncpy(st.name,"shine.yang",16);
st.score = 10.0f;
char blob_var[128];
mysql_real_escape_string(sql, blob_var, (char *)&st,sizeof(st));
char query[1024] = {0};
int len = snprintf(query,1024,"insert test(col1,col2,col3,col4) values('%s','%s','%s',NOW())",varchar_var,char_var,blob_var);
dword time1 = get_time();
for (int i = 0;i < INSERT_ITEM_CNT;++i )
{
mysql_real_query(sql,query,len);
}
dword time2 = get_time();
fprintf(stdout,"direct insert %d items,during time[%d]\r\n",INSERT_ITEM_CNT,time2-time1);
return true;
}
下面是测试结果
预处理耗时（单位微秒） 直接操作耗时（单位微秒）
插入10000条 660763 746571
插入100000条 8597130 8784918
插入1000000条 95070524 100157193
可以看出，插入方面，预处理效率要高些
posted on 2012-01-29 16:28
梨树阳光 阅读(707)
评论(0) 编辑 收藏 引用