MYSQL开发性能研究——批量插入的优化措施
转自http://www.cnblogs.com/aicro/p/3851434.html一、我们遇到了什么问题在标准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. 比较不同进/线程数量对插入的影响
http://images.cnitblog.com/blog/68413/201407/171635103494398.png
*“普通方法”指的是一句INSERT只插入一个VALUE的情况。
*“预处理SQL”指的是使用预处理MYSQL C API的情况。
* “多表值SQL(10条）”是使用一句INSERT语句插入10条记录的情况。为什么是10条？后面的验证告诉了我们这样做性能最高。
结论，很显然，从三种方法的趋势上来看，多表值SQL(10条）的方式最为高效。
理想型测试（二）——多表值SQL条数比较
http://images.cnitblog.com/blog/68413/201407/171635112408727.png
很显然，在数据量提高的情况下，每条INSERT语句插入10条记录的做法最为高效。
理想型测试（三）——连接数比较
http://images.cnitblog.com/blog/68413/201407/171635125686754.png
http://images.cnitblog.com/blog/68413/201407/171635162568393.png
结论：在2倍与CPU核数的连接和操作的时候，性能最高
一般性测试—— 根据我们的业务量进行测试目的：最佳插入机制适合普通交易情况？
关键方法：
1. 模拟生产数据(每条记录约3KB)
2. 每个线程主键乱序插入
http://images.cnitblog.com/blog/68413/201407/171635180848334.png
很显然，如果是根据主键乱序插入的话，性能会有直线下降的情况。这一点其实和INNODB的内部实现原理所展现出来的现象一致。但是仍然可以肯定的是，多表值SQL(10条）的情况是最佳的。
压力测试目的：最佳插入机制适合极端交易情况？
关键方法：
1. 将数据行的每一个字段填满（每条记录约为4KB）
2. 每个线程主键乱序插入
http://images.cnitblog.com/blog/68413/201407/171635190996392.png
结果和我们之前的规律类似，性能出现了极端下降。并且这里验证了随着记录的增大（可能已经超过了一个page的大小，毕竟还有slot和page head信息占据空间），会有page split等现象，性能会下降。
四、结论根据上面的测试，以及我们对INNODB的了解，我们可以得到如下的结论。
•采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）
•采用多值表（10条）插入方式最为合适
•将进程/线程数控制在2倍CPU数目相对合适
五、附录我发现网上很少有完整的针对MYSQL 预处理SQL语句的例子。这里给出一个简单的例子。
1
2
3
4
5
6
7
8
9
--建表语句
CREATE TABLE tbl_test
(
pri_keyvarchar(30),
nor_char char(30),
max_num DECIMAL(8,0),
long_num DECIMAL(12, 0),
rec_upd_ts TIMESTAMP
);
/*====================================================*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
#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 = "123456";
char nor_char = "abcabc";
char rec_upd_ts = "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 = {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 = {0};
MYSQL_BIND bind;
memset(bind, 0, FIELD_NUM * sizeof(MYSQL_BIND));
unsigned long str_length = strlen(pri_key);
bind.buffer_type = MYSQL_TYPE_STRING;
bind.buffer = (char *)pri_key;
bind.buffer_length = STRING_LEN;
bind.is_null = 0;
bind.length = &str_length;
unsigned long str_length_nor = strlen(nor_char);
bind.buffer_type = MYSQL_TYPE_STRING;
bind.buffer = (char *)nor_char;
bind.buffer_length = STRING_LEN;
bind.is_null = 0;
bind.length = &str_length_nor;
bind.buffer_type = MYSQL_TYPE_LONG;
bind.buffer = (char*)&max_num;
bind.is_null = 0;
bind.length = 0;
bind.buffer_type = MYSQL_TYPE_LONG;
bind.buffer = (char*)&max_num;
bind.is_null = 0;
bind.length = 0;
MYSQL_TIMEts;
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.buffer_type = MYSQL_TYPE_TIMESTAMP;
bind.buffer = (char *)&ts;
bind.is_null = 0;
bind.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;
}
------------------------------------------------------------------------
这篇帖子是我看到的非常不错的插入性能优化的帖子。
但是它未能深入分析其原因。只是把结果告诉我们了。
页:
[1]