MYSQL批量插入資料如何最佳化的方法介紹

在網路上也看到另外的幾種方法，比如說​​預處理SQL，比如說批量提交。那麼這些方法的性能到底如何呢？本文就會對這些方法做一個比較

一、我們遇到了什麼問題

#在標準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
);

c代码

#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 *)&ts;
    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;  
}

以上是MYSQL批量插入資料如何最佳化的方法介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！