MYSQL开发性能研究批量插入的优化措施（mysql批量加载）

　　本篇文章为你整理了MYSQL开发性能研究——批量插入的优化措施（mysql批量加载）的详细内容，包含有mysql开发工具介绍 mysql批量加载 mysql source 批量 mysql软件怎么运行代码 MYSQL开发性能研究——批量插入的优化措施，希望能帮助你了解 MYSQL开发性能研究——批量插入的优化措施。

　　一、我们遇到了什么问题在标准SQL里面，我们通常会写下如下的SQL insert语句。

INSERT INTO TBL_TEST (id) VALUES(1);

　　很显然,在MYSQL中，这样的方式也是可行的。但是当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。

　　正是由于性能的瓶颈问题，MYSQL官方文档也就提到了使用批量化插入的方式，也就是在一句INSERT语句里面插入多个值。即，

INSERT INTO TBL_TEST (id) VALUES (1), (2), (3)

　　这样的做法确实也可以起到加速批量插入的功效，原因也不难理解，由于提交到服务器的INSERT语句少了，网络负载少了，最主要的是解析和优化的时间看似增多，但是实际上作用的数据行却实打实地多了。所以整体性能得以提高。根据网上的一些说法，这种方法可以提高几十倍。

　　然而，我在网上也看到过另外的几种方法，比如说预处理SQL，比如说批量提交。那么这些方法的性能到底如何？本文就会对这些方法做一个比较。

　　二、比较环境和方法

　　我的环境比较苦逼，基本上就是一个落后的虚拟机。只有2核，内存为6G。操作系统是SUSI Linux，MYSQL版本是5.6.15。

　　可以想见，这个机子的性能导致了我的TPS一定非常低，所以下面的所有数据都是没有意义的，但是趋势却不同，它可以看出整个插入的性能走向。

　　由于业务特点，我们所使用的表非常大，共有195个字段，且写满（每个字段全部填满，包括varchar）大致会有略小于4KB的大小，而通常来说，一条记录的大小也有3KB。

　　由于根据我们的实际经验，我们很肯定的是，通过在一个事务中提交大量INSERT语句可以大幅度提高性能。所以下面的所有测试都是建立在每插入5000条记录提交一次的做法之上。

　　最后需要说明的是，下面所有的测试都是通过使用MYSQL C API进行的，并且使用的是INNODB存储引擎。

　　三、比较方法

　　理想型测试（一）——方法比较

　　目的：找出理想情况下最合适的插入机制

　　关键方法：

　　1. 每个进/线程按主键顺序插入

　　2. 比较不同的插入方法

　　3. 比较不同进/线程数量对插入的影响

　　*“普通方法”指的是一句INSERT只插入一个VALUE的情况。

　　*“预处理SQL”指的是使用预处理MYSQL C API的情况。

　　* “多表值SQL(10条）”是使用一句INSERT语句插入10条记录的情况。为什么是10条？后面的验证告诉了我们这样做性能最高。

　　结论，很显然，从三种方法的趋势上来看，多表值SQL(10条）的方式最为高效。

　　理想型测试（二）——多表值SQL条数比较

　　很显然，在数据量提高的情况下，每条INSERT语句插入10条记录的做法最为高效。

　　理想型测试（三）——连接数比较

　　结论：在2倍与CPU核数的连接和操作的时候，性能最高

　　一般性测试—— 根据我们的业务量进行测试

　　目的：最佳插入机制适合普通交易情况？

　　关键方法：

　　1. 模拟生产数据(每条记录约3KB)

　　2. 每个线程主键乱序插入

　　很显然，如果是根据主键乱序插入的话，性能会有直线下降的情况。这一点其实和INNODB的内部实现原理所展现出来的现象一致。但是仍然可以肯定的是，多表值SQL(10条）的情况是最佳的。

　　目的：最佳插入机制适合极端交易情况？

　　关键方法：

　　1. 将数据行的每一个字段填满（每条记录约为4KB）

　　2. 每个线程主键乱序插入

　　结果和我们之前的规律类似，性能出现了极端下降。并且这里验证了随着记录的增大（可能已经超过了一个page的大小，毕竟还有slot和page head信息占据空间），会有page split等现象，性能会下降。

　　根据上面的测试，以及我们对INNODB的了解，我们可以得到如下的结论。

　　•采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）

　　•采用多值表（10条）插入方式最为合适

　　•将进程/线程数控制在2倍CPU数目相对合适

　　我发现网上很少有完整的针对MYSQL 预处理SQL语句的例子。这里给出一个简单的例子。

--建表语句

　　CREATE TABLE tbl_test

　　 pri_key varchar(30),

　　 nor_char char(30),

　　 max_num DECIMAL(8,0),

　　 long_num DECIMAL(12, 0),

　　 rec_upd_ts TIMESTAMP

　　);

　　/*====================================================*/

#include string.h

　　#include iostream

　　#include mysql.h

　　#include sys/time.h

　　#include sstream

　　#include vector

　　using namespace std;

　　#define STRING_LEN 30

　　char pri_key [STRING_LEN]= "123456";

　　char nor_char [STRING_LEN]= "abcabc";

　　char rec_upd_ts [STRING_LEN]= "NOW()";

　　bool SubTimeval(timeval result, timeval begin, timeval end)

　　 if ( begin.tv_sec end.tv_sec ) return false;

　　 if ( (begin.tv_sec == end.tv_sec) (begin.tv_usec end.tv_usec) )

　　 return false;

　　 result.tv_sec = ( end.tv_sec - begin.tv_sec );

　　 result.tv_usec = ( end.tv_usec - begin.tv_usec );

　　 if (result.tv_usec 0) {

　　 result.tv_sec--;

　　 result.tv_usec+=1000000;}

　　 return true;

　　int main(int argc, char ** argv)

　　 INT32 ret = 0;

　　 char errmsg[200] = {0};

　　 int sqlCode = 0;

　　 timeval tBegin, tEnd, tDiff;

　　 const char* precompile_statment2 = "INSERT INTO `tbl_test`( pri_key, nor_char, max_num, long_num, rec_upd_ts) VALUES(?, ?, ?, ?, ?)";

　　 MYSQL conn;

　　 mysql_init( conn);

　　 if (mysql_real_connect( conn, "127.0.0.1", "dba", "abcdefg", "TESTDB", 3306, NULL, 0) == NULL)

　　 fprintf(stderr, " mysql_real_connect, 2 failed\n");

　　 exit(0);

　　 MYSQL_STMT *stmt = mysql_stmt_init( conn);

　　 if (!stmt)

　　 fprintf(stderr, " mysql_stmt_init, 2 failed\n");

　　 fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

　　 exit(0);

　　 if (mysql_stmt_prepare(stmt, precompile_statment2, strlen(precompile_statment2)))

　　 fprintf(stderr, " mysql_stmt_prepare, 2 failed\n");

　　 fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

　　 exit(0);

　　 int i = 0;

　　 int max_num = 3;

　　 const int FIELD_NUM = 5;

　　 while (i max_num)

　　 //MYSQL_BIND bind[196] = {0};

　　 MYSQL_BIND bind[FIELD_NUM];

　　 memset(bind, 0, FIELD_NUM * sizeof(MYSQL_BIND));

　　 unsigned long str_length = strlen(pri_key);

　　 bind[0].buffer_type = MYSQL_TYPE_STRING;

　　 bind[0].buffer = (char *)pri_key;

　　 bind[0].buffer_length = STRING_LEN;

　　 bind[0].is_null = 0;

　　 bind[0].length = str_length;

　　 unsigned long str_length_nor = strlen(nor_char);

　　 bind[1].buffer_type = MYSQL_TYPE_STRING;

　　 bind[1].buffer = (char *)nor_char;

　　 bind[1].buffer_length = STRING_LEN;

　　 bind[1].is_null = 0;

　　 bind[1].length = str_length_nor;

　　 bind[2].buffer_type = MYSQL_TYPE_LONG;

　　 bind[2].buffer = (char*) max_num;

　　 bind[2].is_null = 0;

　　 bind[2].length = 0;

　　 bind[3].buffer_type = MYSQL_TYPE_LONG;

　　 bind[3].buffer = (char*) max_num;

　　 bind[3].is_null = 0;

　　 bind[3].length = 0;

　　 MYSQL_TIME ts;

　　 ts.year= 2002;

　　 ts.month= 02;

　　 ts.day= 03;

　　 ts.hour= 10;

　　 ts.minute= 45;

　　 ts.second= 20;

　　 unsigned long str_length_time = strlen(rec_upd_ts);

　　 bind[4].buffer_type = MYSQL_TYPE_TIMESTAMP;

　　 bind[4].buffer = (char *)

　　 bind[4].is_null = 0;

　　 bind[4].length = 0;

　　 if (mysql_stmt_bind_param(stmt, bind))

　　 fprintf(stderr, " mysql_stmt_bind_param, 2 failed\n");

　　 fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

　　 exit(0);

　　 cout "before execute\n";

　　 if (mysql_stmt_execute(stmt))

　　 fprintf(stderr, " mysql_stmt_execute, 2 failed\n");

　　 fprintf(stderr, " %s\n", mysql_stmt_error(stmt));

　　 exit(0);

　　 cout "after execute\n";

　　 i++;

　　 mysql_commit( conn);

　　 mysql_stmt_close(stmt);

　　 return 0;

　　}

　　
------------------------------------------------------------------------

　　email : aicrosoft1104@126.com

　　吃遍天下。

　　以上就是MYSQL开发性能研究——批量插入的优化措施（mysql批量加载）的详细内容，想要了解更多 MYSQL开发性能研究——批量插入的优化措施的内容，请持续关注盛行IT软件开发工作室。

郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。

相关文章阅读