MySQL直接执行和预处理执行效率比较示例

     “对于多次执行的语句，预处理执行比直接执行快，主要原因在于，仅对查询执行一次解析操作。在直接执行的情况下，每次执行语句时，均将进行查询。此外，由于每次执行预处理语句时仅需发送参数的数据，从而减少了网络通信量。

     “预处理语句的另一个优点是，它采用了二进制协议，从而使得客户端和服务器之间的数据传输更有效率。”——MySQL 5.1 中文手册

 

      到底是否真的会快，会快多少呢，故做了此实验。

代码：

/**mysqlTest.cpp*author: Peter_ZHA*at: Aug 5th, 2010*for: 比较MySQL API中普通函数和预处理语句函数执行效率*/#include <mysql.h>#include <stdio.h>#include <time.h>#include <string.h>#define BUFFER_SIZE128#defineMAX_TIMES1000000000/* 普通函数使用的语句 */const char* insertStmt1 = "insert into Record( id, record) values (%lu, /"%s/")";/*预处理语句函数使用的语句 */const char* insertStmt2 = "insert into Record( id, record) values (? , ?)";MYSQLmysql;MYSQL_STMT*pStmt;/*普通函数 */int insert1(unsigned long id, char* sentence){charbuff[BUFFER_SIZE];sprintf(buff, insertStmt1, id, sentence);mysql_query(&mysql, buff);return 0;}/*预处理语句函数 */int insert2(unsigned long id, char* sentence){MYSQL_BINDbind[2];unsigned long sentenceL = strlen(sentence);bind[0].buffer_type = MYSQL_TYPE_LONG;bind[0].buffer = (char*)&id;bind[0].buffer_length = sizeof(unsigned long);bind[0].is_null = NULL;bind[1].buffer_type = MYSQL_TYPE_VAR_STRING;bind[1].buffer = sentence;bind[1].buffer_length = 36;bind[1].length = &sentenceL;bind[1].is_null = NULL;if(mysql_stmt_bind_param(pStmt, bind) != 0){fprintf(stderr, "Mysql bind parameter fail: %s/n", mysql_stmt_error(pStmt));return -1;}if(mysql_stmt_execute(pStmt) != 0){fprintf(stderr, "Mysql execute fail: %s/n", mysql_stmt_error(pStmt));return -1;}return 0;}void deleteAll(){mysql_query(&mysql, "delete from Record");}int main(){unsigned long times = 1000;time_tformerTime;time_tnowTime;longrunTime[2];/*欲插入的数据 */charsentence[] = "hi Mr. Mysql i'm comming...";if(mysql_library_init(0, NULL, NULL))return -1;if((mysql_init(&mysql) == NULL))return -1;if(mysql_real_connect(&mysql, "localhost", "root", NULL, "mysqltest", 0, "/usr/local/mysql/data/mysql.sock", 0) == NULL){fprintf(stderr, "Failed to connect to database: %s/n", mysql_error(&mysql));return -1;}printf("times/t/tCommon Func/t/tPrepare func/n");while(times <= MAX_TIMES){/*普通函数运行 */time(&formerTime);for(unsigned long i = 0; i < times; i++){insert1(i, sentence);}time(&nowTime);runTime[0] = nowTime - formerTime;deleteAll();/*清空数据，避免已有数据对语句执行速度的影响 *//*预处理函数运行 */time(&formerTime);pStmt = mysql_stmt_init(&mysql);mysql_stmt_prepare(pStmt, insertStmt2, strlen(insertStmt2));for(unsigned long i = 0; i < times; i++){insert2(i, sentence);}mysql_stmt_close(pStmt);time(&nowTime);runTime[1] = nowTime - formerTime;deleteAll();/*输出结果 */printf("%lu/t/t%lu/t/t/t%lu/n", times, runTime[0], runTime[1]);times = times * 5;}mysql_close(&mysql);mysql_library_end();return 0;}

 结果：

 

    结果表明：预处理执行比直接执行快10%~20%，速度提升效果很明显。

    我同时也做了个使用预处理执行函数，但是在每次执行前都创建语句（mysql_stmt_init、mysql_stmt_prepare），执行结束后都销毁语句（mysql_stmt_close），结果是其执行时间是直接执行的2倍，结果很悲剧。