1. С какими проблемами мы столкнулись
В стандартном SQL мы обычно пишем следующий оператор вставки SQL.
|
1
|
INSERT INTO TBL_TEST (id) VALUES(1);
|
Очевидно, что и в MYSQL такой способ возможен. Но когда нам нужно вставлять данные пакетами, у такого оператора будут проблемы с производительностью. Например, если необходимо вставить 100 000 фрагментов данных, то потребуется 100 000 операторов вставки, и каждый оператор должен быть отправлен в реляционный механизм для анализа и оптимизации, а затем он может достичь механизма хранения для выполнения фактической работы по вставке. .
Именно из-за узкого места производительности в официальной документации MYSQL также упоминается использование пакетных вставок, то есть вставка нескольких значений в инструкцию INSERT. который,
|
1
|
INSERT INTO TBL_TEST (id) VALUES (1), (2), (3)
|
Такой подход действительно может ускорить пакетную вставку.Причину понять несложно.Поскольку на сервер отправляется меньше INSERT-операторов и меньше нагрузка на сеть, самое главное, что время на синтаксический анализ и оптимизацию вроде бы увеличивается, но на самом деле Линии данных, которые работают на нем, на самом деле намного больше. Таким образом, общая производительность улучшается. По некоторым данным в Интернете, этот метод можно улучшить в десятки раз.
Однако я также видел в Интернете несколько других методов, таких как предварительная обработка SQL, например пакетная отправка. Так какова же производительность этих методов? В этой статье будет проведено сравнение этих методов.
2. Сравнительная среда и метод
Моя среда жесткая, в основном отсталая виртуальная машина. Только2 ядра, памяти 6G. Операционная система — SUSI Linux, версия MYSQL — 5.6.15.
Вполне возможно, что производительность этой машины привела к моемуTPS должен быть очень низким, так что все приведенные ниже данные бессмысленны, нотрендНо он другой, он может видеть тенденцию производительности всей прошивки.
Из-за бизнес-характеристик таблицы, которые мы используем, очень большие, в общей сложности195 полей, и full (каждое поле заполнено, включая varchar) будет иметь размер чуть меньше 4 КБ, и вообще говоря,Размер записи также составляет 3 КБ..
Поскольку из нашего реального опыта мы почти уверены, что, фиксируя большое количество операторов INSERT в одной транзакции, производительность может быть значительно улучшена. такВсе приведенные ниже тесты основаны на практике фиксации каждых 5000 вставленных записей.
И последнее замечание: все приведенные ниже тесты были выполнены с использованием MYSQL C API и механизма хранения INNODB.
3. Метод сравнения
Идеальный тест (1) - сравнение методов
Цель: найти наиболее подходящий механизм введения в идеальной ситуации.
Ключевой метод:
1. Нажмите основную клавишу для каждого входа/потокаприказвставлять
2. Сравните различные методы вставки
3. Сравните влияние различного количества входов/потоков на вставки.
*"Обычный метод" относится к случаю, когда INSERT вставляет только VALUE.
* «Предварительно обработанный SQL» относится к использованиюПредварительная обработка MYSQL C APIСлучай.
* «SQL с несколькими табличными значениями (10 элементов)» — это случай вставки 10 записей с помощью одного оператора INSERT. Почему 10? Более поздняя проверка говорит нам, что это самый производительный.
В заключение ясно, что из тенденции трех методов,Многотабличный SQL (10 записей) — наиболее эффективный способ.
Тест идеального типа (2) — сравнение количества записей SQL с несколькими табличными значениями
Очевидно, что при увеличении объема данных практика вставки 10 записей на оператор INSERT является наиболее эффективной.
Идеальный тест (3) — сравнение количества подключений
Вывод: При подключении и работе с 2-кратным количеством ядер ЦП производительность самая высокая
Общее тестирование - тестирование на основе нашего объема бизнеса
Цель: лучший механизм вставки для распространенных транзакционных ситуаций?
Ключевой метод:
1. Моделирование производственных данных (около 3 КБ на запись)
2. на потокпервичный ключ вышел из строявставлять
Очевидно, что если он будет вставлен не по порядку в соответствии с первичным ключом, производительность резко упадет. Это на самом деле согласуется с явлением, показанным внутренним принципом реализации INNODB. Но по-прежнему очевидно, что случай многотабличного SQL (10) оптимален.
испытание давлением
Цель: Лучший механизм вставки для экстремальных торговых ситуаций?
Ключевой метод:
1. Заполните каждое поле строки данных (около 4 КБ на запись)
2. на потокпервичный ключ вышел из строявставлять
Результат аналогичен нашему предыдущему шаблону с резким падением производительности. И здесь проверяется, что по мере увеличения записи (возможно, она превысила размер страницы, ведь информация о слоте и заголовке страницы все еще занимает место) будет происходить разбиение страницы и другие явления, а производительность будет снижаться.
4. Вывод
На основании приведенных выше тестов и того, что мы знаем о INNODB, мы можем сделать следующие выводы.
• Принять последовательную стратегию первичного ключа (например, автоматическое увеличение первичного ключа или изменение бизнес-логики, чтобы сделать вставляемые записи как можно более последовательными).
• Наиболее удобно использовать многозначную таблицу (10 элементов) для вставки
• Относительно целесообразно контролировать количество процессов/потоков в 2 раза по сравнению с количеством процессоров.
5. Приложение
В Интернете я нашел очень мало полных примеров предварительной обработки операторов SQL для MYSQL. Вот простой пример.
|
1
2
3
4
5
6
7
8
9
|
--建表语句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
); |
/*====================================================*/
|
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 [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;
} |
------------------------------------------------------------------------
email : aicrosoft1104@126.com
Ешьте по всему миру.