• 售前

  • 售后

热门帖子
入门百科

MYSQL开发性能研究之批量插入数据的优化方法

[复制链接]
名人堂熊猫虞kk 显示全部楼层 发表于 2021-10-25 19:53:22 |阅读模式 打印 上一主题 下一主题
一、我们遇到了什么问题
在尺度SQL里面,我们通常会写下如下的SQL insert语句。
  1. INSERT INTO TBL_TEST (id) VALUES(1);
复制代码
很显然,在MYSQL中,这样的方式也是可行的。但是当我们必要批量插入数据的时间,这样的语句却会出现性能问题。比方说,假如有必要插入100000条数据,那么就必要有100000条insert语句,每一句都必要提交到关系引擎那边去解析,优化,然后才华够到达存储引擎做真的插入工作。
正是由于性能的瓶颈问题,MYSQL官方文档也就提到了使用批量化插入的方式,也就是在一句INSERT语句里面插入多个值。即,
  1. 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语句的例子。这里给出一个简朴的例子。
  1. --建表语句
  2. CREATE TABLE tbl_test
  3. (
  4.   pri_key varchar(30),
  5.   nor_char char(30),
  6.   max_num DECIMAL(8,0),
  7.   long_num DECIMAL(12, 0),
  8.   rec_upd_ts TIMESTAMP
  9. );
复制代码
c代码
  1. #include <string.h>
  2. #include <iostream>
  3. #include <mysql.h>
  4. #include <sys/time.h>
  5. #include <sstream>
  6. #include <vector>
  7. using namespace std;
  8. #define STRING_LEN 30
  9.   
  10. char    pri_key            [STRING_LEN]= "123456";
  11. char    nor_char           [STRING_LEN]= "abcabc";
  12. char    rec_upd_ts          [STRING_LEN]= "NOW()";
  13. bool SubTimeval(timeval &result, timeval &begin, timeval &end)
  14. {
  15.   if ( begin.tv_sec>end.tv_sec ) return false;
  16.   if ( (begin.tv_sec == end.tv_sec) && (begin.tv_usec > end.tv_usec) )  
  17.     return  false;
  18.   result.tv_sec = ( end.tv_sec - begin.tv_sec );  
  19.   result.tv_usec = ( end.tv_usec - begin.tv_usec );  
  20.   if (result.tv_usec<0) {
  21.     result.tv_sec--;
  22.     result.tv_usec+=1000000;}
  23.   return true;
  24. }
  25. int main(int argc, char ** argv)
  26. {
  27.   INT32 ret = 0;
  28.   char errmsg[200] = {0};
  29.   int sqlCode = 0;
  30.   timeval tBegin, tEnd, tDiff;
  31.    
  32.   const char* precompile_statment2 = "INSERT INTO `tbl_test`( pri_key, nor_char, max_num, long_num, rec_upd_ts) VALUES(?, ?, ?, ?, ?)";
  33.    
  34.   MYSQL conn;
  35.   mysql_init(&conn);
  36.    
  37.   if (mysql_real_connect(&conn, "127.0.0.1", "dba", "abcdefg", "TESTDB", 3306, NULL, 0) == NULL)
  38.   {
  39.     fprintf(stderr, " mysql_real_connect, 2 failed\n");
  40.     exit(0);
  41.   }
  42.    
  43.   MYSQL_STMT  *stmt = mysql_stmt_init(&conn);
  44.   if (!stmt)
  45.   {
  46.    fprintf(stderr, " mysql_stmt_init, 2 failed\n");
  47.    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  48.    exit(0);
  49.   }
  50.    
  51.   if (mysql_stmt_prepare(stmt, precompile_statment2, strlen(precompile_statment2)))
  52.   {
  53.    fprintf(stderr, " mysql_stmt_prepare, 2 failed\n");
  54.    fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  55.    exit(0);
  56.   }
  57.    
  58.   int i = 0;
  59.   int max_num = 3;
  60.   const int FIELD_NUM = 5;
  61.   while (i < max_num)
  62.   {
  63.     //MYSQL_BIND  bind[196] = {0};
  64.     MYSQL_BIND  bind[FIELD_NUM];
  65.     memset(bind, 0, FIELD_NUM * sizeof(MYSQL_BIND));
  66.    
  67.     unsigned long str_length = strlen(pri_key);
  68.     bind[0].buffer_type  = MYSQL_TYPE_STRING;
  69.     bind[0].buffer    = (char *)pri_key;
  70.     bind[0].buffer_length = STRING_LEN;
  71.     bind[0].is_null    = 0;
  72.     bind[0].length    = &str_length;
  73.      
  74.     unsigned long str_length_nor = strlen(nor_char);
  75.     bind[1].buffer_type  = MYSQL_TYPE_STRING;
  76.     bind[1].buffer    = (char *)nor_char;
  77.     bind[1].buffer_length = STRING_LEN;
  78.     bind[1].is_null    = 0;
  79.     bind[1].length    = &str_length_nor;
  80.      
  81.     bind[2].buffer_type  = MYSQL_TYPE_LONG;
  82.     bind[2].buffer    = (char*)&max_num;
  83.     bind[2].is_null    = 0;
  84.     bind[2].length    = 0;
  85.      
  86.     bind[3].buffer_type  = MYSQL_TYPE_LONG;
  87.     bind[3].buffer    = (char*)&max_num;
  88.     bind[3].is_null    = 0;
  89.     bind[3].length    = 0;
  90.      
  91.     MYSQL_TIME ts;
  92.     ts.year= 2002;
  93.     ts.month= 02;
  94.     ts.day= 03;
  95.     ts.hour= 10;
  96.     ts.minute= 45;
  97.     ts.second= 20;
  98.      
  99.     unsigned long str_length_time = strlen(rec_upd_ts);
  100.     bind[4].buffer_type  = MYSQL_TYPE_TIMESTAMP;
  101.     bind[4].buffer    = (char *)&ts;
  102.     bind[4].is_null    = 0;
  103.     bind[4].length    = 0;
  104.      
  105.     if (mysql_stmt_bind_param(stmt, bind))
  106.     {
  107.       fprintf(stderr, " mysql_stmt_bind_param, 2 failed\n");
  108.       fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  109.       exit(0);
  110.     }
  111.      
  112.     cout << "before execute\n";
  113.     if (mysql_stmt_execute(stmt))
  114.     {
  115.      fprintf(stderr, " mysql_stmt_execute, 2 failed\n");
  116.      fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  117.      exit(0);
  118.     }
  119.     cout << "after execute\n";
  120.      
  121.     i++;
  122.   }
  123.    
  124.   mysql_commit(&conn);
  125.    
  126.   mysql_stmt_close(stmt);
  127.   return 0;  
  128. }
复制代码
以上就是mysql批量插入数据的优化方法,建议各人也可以多看下草根技术分享从前的文章。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作