一、我们遇到了什么问题

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. 比较不同进/线程数量对插入的影响

必发娱乐官方网站 1

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

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

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

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

理想型测试(二)——多表值SQL条数比较

必发娱乐官方网站 2

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

理想型测试(三)——连接数比较

必发娱乐官方网站 3

必发娱乐官方网站 4

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

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

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

关键方法:

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

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

必发娱乐官方网站 5

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

压力测试

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

关键方法:

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

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

必发娱乐官方网站 6

结果和我们之前的规律类似,性能出现了极端下降。并且这里验证了随着记录的增大(可能已经超过了一个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批量插入数据的优化方法,建议大家也可以多看下帮客之家以前的文章。

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

单位IM改版了
用户聊天内容要存放在数据库.

在标准SQL里面,我们通常会写下如下的SQL insert语句。

一般JAVA Insert MySQL有如下几种方式
1.自动提交Insert
2.事务提交Insert
3.批量提交
4.使用Load File接口

INSERT INTO TBL_TEST (id) VALUES(1);

模拟表结构如下

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

  1. create table chat_message(
  2.     id bigint primary key auto_increment,
  3.     src_userid bigint not null,
  4.     target_userid bigint not null,
  5.     message varchar(200),
  6.     ts timestamp not null default current_timestamp,
  7.     s1 int,
  8.     s2 int,
  9.     s3 int,
  10.     s4 int
  11. );

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

下面代码,分别使用四种方式,Insert 2w记录.记录执行时间.

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

依赖
commons-lang3-3.3.2.jar
mysql-connector-java-5.1.31-bin.jar(低版本驱动有性能影响)

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

  1. import java.io.ByteArrayInputStream;  
  2. import java.io.InputStream;  
  3. import java.io.UnsupportedEncodingException;  
  4. import java.sql.Connection;  
  5. import java.sql.DriverManager;  
  6. import java.sql.PreparedStatement;  
  7. import java.sql.SQLException;  
  8.   
  9. import org.apache.commons.lang3.RandomStringUtils;  
  10.   
  11. public class Main {  
  12.     private static String URL = “jdbc:mysql://127.0.0.1:3306/mvbox”;  
  13.     private static String USERNAME = “xx”;  
  14.     private static String PWD = “xx”;  
  15.     private static int MAX = 20000;  
  16.     private static String SQL = “insert into chat_message(src_userid,target_userid,message,s1,s2,s3,s4) values(?,?,?,?,?,?,?)”;  
  17.   
  18.     public static void main(String[] args) throws ClassNotFoundException, SQLException, UnsupportedEncodingException {  
  19.         long start = System.currentTimeMillis();  
  20.         testLoadFile(100);  
  21.         long end = System.currentTimeMillis();  
  22.         System.out.println((end – start));  
  23.         System.out.println(MAX / ((end – start) / 1000));  
  24.     }  
  25.   
  26.     private static Connection getConnection() throws SQLException, ClassNotFoundException {  
  27.         Class.forName(“com.mysql.jdbc.Driver”);  
  28.         Connection con = DriverManager.getConnection(URL, USERNAME, PWD);  
  29.         return con;  
  30.     }  
  31.   
  32.     private static void testInsert() throws ClassNotFoundException, SQLException {  
  33.         Connection con = getConnection();  
  34.         con.setAutoCommit(false);  
  35.         PreparedStatement pt = con.prepareStatement(SQL);  
  36.         int i = 0;  
  37.         while (i < MAX) {  
  38.             pt.setLong(1, 1 + (int) (Math.random() * 100000000));  
  39.             pt.setLong(2, 1 + (int) (Math.random() * 100000000));  
  40.             pt.setString(3, RandomStringUtils.randomAscii(200));  
  41.             pt.setInt(4, 1);  
  42.             pt.setInt(5, 1);  
  43.             pt.setInt(6, 1);  
  44.             pt.setInt(7, 1);  
  45.             pt.executeUpdate();  
  46.             con.commit();  
  47.             i++;  
  48.         }  
  49.         con.close();  
  50.     }  
  51.   
  52.     private static void testInsertAutoCommit() throws ClassNotFoundException, SQLException {  
  53.         Connection con = getConnection();  
  54.         con.setAutoCommit(true);  
  55.         PreparedStatement pt = con.prepareStatement(SQL);  
  56.         int i = 0;  
  57.         while (i < MAX) {  
  58.             pt.setLong(1, 1 + (int) (Math.random() * 100000000));  
  59.             pt.setLong(2, 1 + (int) (Math.random() * 100000000));  
  60.             pt.setString(3, RandomStringUtils.randomAscii(200));  
  61.             pt.setInt(4, 1);  
  62.             pt.setInt(5, 1);  
  63.             pt.setInt(6, 1);  
  64.             pt.setInt(7, 1);  
  65.             pt.executeUpdate();  
  66.             i++;  
  67.         }  
  68.         con.close();  
  69.     }  
  70.   
  71.     private static void testBatchInsert(int batchSize) throws ClassNotFoundException, SQLException {  
  72.         Connection con = getConnection();  
  73.         con.setAutoCommit(false);  
  74.         PreparedStatement pt = con.prepareStatement(SQL);  
  75.         int i = 0;  
  76.         while (i < MAX) {  
  77.             pt.setLong(1, 1 + (int) (Math.random() * 100000000));  
  78.             pt.setLong(2, 1 + (int) (Math.random() * 100000000));  
  79.             pt.setString(3, RandomStringUtils.randomAscii(200));  
  80.             pt.setInt(4, 1);  
  81.             pt.setInt(5, 1);  
  82.             pt.setInt(6, 1);  
  83.             pt.setInt(7, 1);  
  84.             pt.addBatch();  
  85.             if (i % batchSize == 1) {  
  86.                 pt.executeBatch();  
  87.                 con.commit();  
  88.             }  
  89.             i++;  
  90.         }  
  91.         pt.executeBatch();  
  92.         con.commit();  
  93.         con.close();  
  94.     }  
  95.   
  96.     private static void testLoadFile(int batchSize)  
  97.             throws ClassNotFoundException, SQLException, UnsupportedEncodingException {  
  98.         String fieldsterminated = “\t\t”;  
  99.         String linesterminated = “\t\r\n”;  
  100.         String loadDataSql = “LOAD DATA LOCAL INFILE ‘sql.csv’ INTO TABLE chat_message FIELDS TERMINATED BY ‘”  
  101.                 + fieldsterminated + “‘  LINES TERMINATED BY ‘” + linesterminated  
  102.                 + “‘ (src_userid,target_userid,message,s1,s2,s3,s4) “;  
  103.         Connection con = getConnection();  
  104.         con.setAutoCommit(false);  
  105.         PreparedStatement pt = con.prepareStatement(loadDataSql);  
  106.         com.mysql.jdbc.PreparedStatement mysqlStatement = null;  
  107.         if (pt.isWrapperFor(com.mysql.jdbc.Statement.class)) {  
  108.             mysqlStatement = pt.unwrap(com.mysql.jdbc.PreparedStatement.class);  
  109.         }  
  110.   
  111.         int i = 0;  
  112.         StringBuilder sb = new StringBuilder(10000);  
  113.         while (i < MAX) {  
  114.             sb.append(1 + (int) (Math.random() * 100000000));  
  115.             sb.append(fieldsterminated);  
  116.             sb.append(1 + (int) (Math.random() * 100000000));  
  117.             sb.append(fieldsterminated);  
  118.             sb.append(RandomStringUtils.randomAscii(200).replaceAll(“\\\\”, ” “));  
  119.             sb.append(fieldsterminated);  
  120.             sb.append(1);  
  121.             sb.append(fieldsterminated);  
  122.             sb.append(1);  
  123.             sb.append(fieldsterminated);  
  124.             sb.append(1);  
  125.             sb.append(fieldsterminated);  
  126.             sb.append(1);  
  127.             sb.append(linesterminated);  
  128.             if (i % batchSize == 1) {  
  129.                 byte[] bytes = sb.toString().getBytes();  
  130.                 InputStream in = new ByteArrayInputStream(bytes);  
  131.                 mysqlStatement.setLocalInfileInputStream(in);  
  132.                 mysqlStatement.executeUpdate();  
  133.                 con.commit();  
  134.                 sb = new StringBuilder(10000);  
  135.             }  
  136.   
  137.             i++;  
  138.         }  
  139.         byte[] bytes = sb.toString().getBytes();  
  140.         InputStream in = new ByteArrayInputStream(bytes);  
  141.         mysqlStatement.setLocalInfileInputStream(in);  
  142.         mysqlStatement.executeUpdate();  
  143.         con.commit();  
  144.   
  145.         con.close();  
  146.     }  
  147. }  

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

测试结果:

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

 

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

执行方式 执行时间(毫秒) 每秒Insert数量
自动提交 17437 1176
事务提交 22990 909
batchInsert 每10条提交 12646 1666
batchInsert 每50条提交 13758 1538
batchInsert 每100条提交 15870 1333
loadfile 每10条提交 6973 3333
loadfile 每50条提交 5037 4000
loadfile 每100条提交 4175 5000

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

 

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

 

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

三、比较方法

 

理想型测试(一)——方法比较

一、我们遇到了什么问题

在标准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. 比较不同进/线程数量对插入的影响

理想型测试(一)——方法比较

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

关键方法:

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

  2. 比较不同的插入方法

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

 

必发娱乐官方网站 7

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

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

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

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

 

必发娱乐官方网站 8

理想型测试(二)——多表值SQL条数比较

 

必发娱乐官方网站 9

 

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

 

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

理想型测试(三)——连接数比较

 

必发娱乐官方网站 10

必发娱乐官方网站 11

 

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

 

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

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

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

关键方法:

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

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

 

必发娱乐官方网站 12

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

 

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

压力测试

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

关键方法:

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

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

必发娱乐官方网站 13

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

 

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

四、结论

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

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

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

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

 

理想型测试(二)——多表值SQL条数比较

必发娱乐官方网站 14

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

理想型测试(三)——连接数比较

必发娱乐官方网站 15

必发娱乐官方网站 16

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

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

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

关键方法:

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

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

必发娱乐官方网站 17

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

压力测试

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

关键方法:

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

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

必发娱乐官方网站 18

结果和我们之前的规律类似,性能出现了极端下降。并且这里验证了随着记录的增大(可能已经超过了一个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批量插入数据的优化方法,建议大家也可以多看下脚本之家以前的文章。

您可能感兴趣的文章:

  • MySQL
    4G内存服务器配置优化
  • Mysql中基本语句优化的十个原则小结
  • mysql处理海量数据时的一些优化查询速度方法
  • Mysql占用过高CPU时的优化手段(必看)
  • MySQL优化之缓存优化
  • MySQL优化之InnoDB优化
  • mysql如何优化插入记录速度
  • 简单谈谈MySQL优化利器-慢查询
  • 优化 MySQL 3
    个简单的小调整

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注