[MySQL](https://www.mysql.com/)是著名的开源的关系型数据库，为了使用户更快捷地访问mysql并充分利用bthread的并发能力，brpc直接支持mysql协议。示例程序：[example/mysql_c++](https://github.com/brpc/brpc/tree/master/example/mysql_c++/)

**注意**：只支持MySQL 4.1 及之后的版本的文本协议，支持事务，支持Prepared statement。目前支持的鉴权方式为mysql_native_password，使用事务的时候不支持single模式。

相比使用[libmysqlclient](https://dev.mysql.com/downloads/connector/c/)(官方client)的优势有：

- 线程安全。用户不需要为每个线程建立独立的client。
- 支持同步、异步、半同步等访问方式，能使用[ParallelChannel等](combo_channel.md)组合访问方式。
- 支持多种[连接方式](client.md#连接方式)。支持超时、backup request、取消、tracing、内置服务等一系列brpc提供的福利。
- 明确的返回类型校验，如果使用了不正确的变量接受mysql的数据类型，将抛出异常。
- 调用mysql标准库会阻塞框架的并发能力，使用本实现将能充分利用brpc框架的并发能力。
- 使用brpc实现的mysql不会造成pthread的阻塞，使用libmysqlclient会阻塞pthread [线程相关](bthread.md)，使用mysql的异步api会使编程变得很复杂。
# 访问mysql

创建一个访问mysql的Channel：

```c++
# include <brpc/policy/mysql/mysql.h>
# include <brpc/policy/mysql/mysql_authenticator.h>
# include <brpc/channel.h>

brpc::ChannelOptions options;
options.protocol = brpc::PROTOCOL_MYSQL;
options.connection_type = FLAGS_connection_type;
options.timeout_ms = FLAGS_timeout_ms /*milliseconds*/;
options.max_retry = FLAGS_max_retry;
options.auth = new brpc::policy::MysqlAuthenticator("yangliming01", "123456", "test", 
    "charset=utf8&collation_connection=utf8_unicode_ci");
if (channel.Init("127.0.0.1", 3306, &options) != 0) {
    LOG(ERROR) << "Fail to initialize channel";
    return -1;
}
```

向mysql发起命令。

```c++
// 执行各种mysql命令，可以批量执行命令如："select * from tab1;select * from tab2"
std::string command = "show databases"; // select,delete,update,insert,create,drop ...
brpc::MysqlRequest request;
if (!request.Query(command)) {
    LOG(ERROR) << "Fail to add command";
    return false;
}
brpc::MysqlResponse response;
brpc::Controller cntl;
channel.CallMethod(NULL, &cntl, &request, &response, NULL);
if (!cntl.Failed()) {
    std::cout << response << std::endl;
} else {
    LOG(ERROR) << "Fail to access mysql, " << cntl.ErrorText();
    return false;
}
return true;
```

上述代码的说明：

- 请求类型必须为MysqlRequest，回复类型必须为MysqlResponse，否则CallMethod会失败。不需要stub，直接调用channel.CallMethod，method填NULL。
- 调用request.Query()传入要执行的命令，可以批量执行命令，多个命令用分号隔开。
- 依次调用response.reply(X)弹出操作结果，根据返回类型的不同，选择不同的类型接收，如：MysqlReply::Ok，MysqlReply::Error，const MysqlReply::Columnconst MysqlReply::Row等。
- 如果只有一条命令则reply为1个，如果为批量操作返回的reply为多个。

目前支持的请求操作有：

```c++
bool Query(const butil::StringPiece& command);
```

对应的回复操作：

```c++
// 返回不同类型的结果
const MysqlReply::Auth& auth() const;
const MysqlReply::Ok& ok() const;
const MysqlReply::Error& error() const;
const MysqlReply::Eof& eof() const;
// 对result set结果集的操作
// get column number
uint64_t MysqlReply::column_number() const;
// get one column
const MysqlReply::Column& MysqlReply::column(const uint64_t index) const;
// get row number
uint64_t MysqlReply::row_number() const;
// get one row
const MysqlReply::Row& MysqlReply::next() const;
// 结果集中每个字段的操作
const MysqlReply::Field& MysqlReply::Row::field(const uint64_t index) const;
```

# 事务操作

事务可以保证在一个事务中的多个RPC请求最终要么都成功，要么都失败。

```c++
rpc::Channel channel;
// Initialize the channel, NULL means using default options.
brpc::ChannelOptions options;
options.protocol = brpc::PROTOCOL_MYSQL;
options.connection_type = FLAGS_connection_type;
options.timeout_ms = FLAGS_timeout_ms /*milliseconds*/;
options.connect_timeout_ms = FLAGS_connect_timeout_ms;
options.max_retry = FLAGS_max_retry;
options.auth = new brpc::policy::MysqlAuthenticator(
    FLAGS_user, FLAGS_password, FLAGS_schema, FLAGS_params);
if (channel.Init(FLAGS_server.c_str(), FLAGS_port, &options) != 0) {
    LOG(ERROR) << "Fail to initialize channel";
    return -1;
}

// create transaction
brpc::MysqlTransactionOptions options;
options.readonly = FLAGS_readonly;
options.isolation_level = brpc::MysqlIsolationLevel(FLAGS_isolation_level);
auto tx(brpc::NewMysqlTransaction(channel, options));
if (tx == NULL) {
    LOG(ERROR) << "Fail to create transaction";
    return false;
}

brpc::MysqlRequest request(tx.get());
if (!request.Query(*it)) {
    LOG(ERROR) << "Fail to add command";
    tx->rollback();
    return false;
}
brpc::MysqlResponse response;
brpc::Controller cntl;
channel.CallMethod(NULL, &cntl, &request, &response, NULL);
if (cntl.Failed()) {
    LOG(ERROR) << "Fail to access mysql, " << cntl.ErrorText();
    tx->rollback();
    return false;
}
// handle response
std::cout << response << std::endl;
bool rc = tx->commit();
```

# Prepared Statement

Prepared statement对于一个需要执行很多次的SQL语句，它把这个SQL语句注册到mysql-server，避免了每次请求在mysql-server端都去解析这个SQL语句，能得到性能上的提升。

```c++
rpc::Channel channel;
// Initialize the channel, NULL means using default options.
brpc::ChannelOptions options;
options.protocol = brpc::PROTOCOL_MYSQL;
options.connection_type = FLAGS_connection_type;
options.timeout_ms = FLAGS_timeout_ms /*milliseconds*/;
options.connect_timeout_ms = FLAGS_connect_timeout_ms;
options.max_retry = FLAGS_max_retry;
options.auth = new brpc::policy::MysqlAuthenticator(
    FLAGS_user, FLAGS_password, FLAGS_schema, FLAGS_params);
if (channel.Init(FLAGS_server.c_str(), FLAGS_port, &options) != 0) {
    LOG(ERROR) << "Fail to initialize channel";
    return -1;
}

auto stmt(brpc::NewMysqlStatement(channel, "select * from tb where name=?"));
if (stmt == NULL) {
    LOG(ERROR) << "Fail to create mysql statement";
    return -1;
}

brpc::MysqlRequest request(stmt.get());
if (!request.AddParam("lilei")) {
    LOG(ERROR) << "Fail to add name param";
    return NULL;
}

brpc::MysqlResponse response;
brpc::Controller cntl;
channel->CallMethod(NULL, &cntl, &request, &response, NULL);
if (cntl.Failed()) {
    LOG(ERROR) << "Fail to access mysql, " << cntl.ErrorText();
    return NULL;
}

std::cout << response << std::endl;
```



# 性能测试

我在example/mysql_c++目录下面写了两个测试程序，mysql_press.cpp mysqlclient_press.cpp，mysql_go_press.go 一个是使用了brpc框架，一个是使用了的libmysqlclient访问mysql，一个是使用[go-sql-driver](https://github.com/go-sql-driver)/**go-mysql**访问mysql

启动单线程测试

##### brpc框架访问mysql（单线程）

./mysql_press -thread_num=1 -op_type=0 // insert

```
qps=3071 latency=320
qps=3156 latency=311
qps=3166 latency=310
qps=3151 latency=312
qps=3093 latency=317
qps=3146 latency=312
qps=3139 latency=313
qps=3114 latency=315
qps=3055 latency=321
qps=3135 latency=313
qps=2611 latency=376
qps=3072 latency=320
qps=3026 latency=324
qps=2792 latency=352
qps=3181 latency=309
qps=3181 latency=309
qps=3197 latency=307
qps=3024 latency=325
```

./mysql_press -thread_num=1 -op_type=1

```
qps=6414 latency=151
qps=5292 latency=182
qps=6700 latency=144
qps=6858 latency=141
qps=6915 latency=140
qps=6822 latency=142
qps=6722 latency=144
qps=6852 latency=141
qps=6713 latency=144
qps=6741 latency=144
qps=6734 latency=144
qps=6611 latency=146
qps=6554 latency=148
qps=6810 latency=142
qps=6787 latency=143
qps=6737 latency=144
qps=6579 latency=147
qps=6634 latency=146
qps=6716 latency=144
qps=6711 latency=144
```

./mysql_press -thread_num=1 -op_type=2 // update

```
qps=3090 latency=318
qps=3452 latency=284
qps=3239 latency=303
qps=3328 latency=295
qps=3218 latency=305
qps=3251 latency=302
qps=2516 latency=391
qps=2874 latency=342
qps=3366 latency=292
qps=3249 latency=302
qps=3346 latency=294
qps=3486 latency=282
qps=3457 latency=284
qps=3439 latency=286
qps=3386 latency=290
qps=3352 latency=293
qps=3253 latency=302
qps=3341 latency=294
```

##### libmysqlclient实现（单线程）

./mysqlclient_press -thread_num=1 -op_type=0 // insert

```
qps=3166 latency=313
qps=3157 latency=314
qps=2941 latency=337
qps=3270 latency=303
qps=3305 latency=300
qps=3445 latency=287
qps=3455 latency=287
qps=3449 latency=287
qps=3486 latency=284
qps=3551 latency=279
qps=3517 latency=281
qps=3283 latency=302
qps=3353 latency=295
qps=2564 latency=386
qps=3243 latency=305
qps=3333 latency=297
qps=3598 latency=275
qps=3714 latency=267
```

./mysqlclient_press -thread_num=1 -op_type=1

```
qps=8209 latency=120
qps=8022 latency=123
qps=7879 latency=125
qps=8083 latency=122
qps=8504 latency=116
qps=8112 latency=121
qps=8278 latency=119
qps=8698 latency=113
qps=8817 latency=112
qps=8755 latency=112
qps=8734 latency=113
qps=8390 latency=117
qps=8230 latency=120
qps=8486 latency=116
qps=8038 latency=122
qps=8640 latency=114
```

./mysqlclient_press -thread_num=1 -op_type=2 // update

```
qps=3583 latency=276
qps=3530 latency=280
qps=3610 latency=274
qps=3492 latency=283
qps=3508 latency=282
qps=3465 latency=286
qps=3543 latency=279
qps=3610 latency=274
qps=3567 latency=278
qps=3381 latency=293
qps=3514 latency=282
qps=3461 latency=286
qps=3456 latency=286
qps=3517 latency=281
qps=3492 latency=284
```

##### golang访问mysql（单线程）

go run test.go -thread_num=1

```
qps = 6905 latency = 144
qps = 6922 latency = 143
qps = 6931 latency = 143
qps = 6998 latency = 142
qps = 6780 latency = 146
qps = 6980 latency = 142
qps = 6901 latency = 144
qps = 6887 latency = 144
qps = 6943 latency = 143
qps = 6880 latency = 144
qps = 6815 latency = 146
qps = 6089 latency = 163
qps = 6626 latency = 150
qps = 6361 latency = 156
qps = 6783 latency = 146
qps = 6789 latency = 146
qps = 6883 latency = 144
qps = 6795 latency = 146
qps = 6724 latency = 148
qps = 6861 latency = 145
qps = 6878 latency = 144
qps = 6842 latency = 146
```

从以上测试结果看来，使用brpc实现的mysql协议和使用libmysqlclient在插入、修改、删除操作上性能是类似的，但是在查询操作看会逊色于libmysqlclient，查询的性能和golang实现的mysql类似。

##### brpc框架访问mysql（50线程）

./mysql_press -thread_num=50 -op_type=1 -use_bthread=true

```
qps=18843 latency=2656
qps=22426 latency=2226
qps=22536 latency=2203
qps=22560 latency=2193
qps=22270 latency=2226
qps=22302 latency=2247
qps=22147 latency=2225
qps=22517 latency=2228
qps=22762 latency=2176
qps=23061 latency=2162
qps=23819 latency=2070
qps=23852 latency=2077
qps=22682 latency=2214
qps=22381 latency=2213
qps=24041 latency=2069
qps=24562 latency=2022
qps=24874 latency=2004
qps=24821 latency=1988
qps=24209 latency=2073
qps=21706 latency=2281
```

##### libmysqlclient实现（50线程）

./mysql_press -thread_num=50 -op_type=1 -use_bthread=true

```
qps=23656 latency=378
qps=16190 latency=555
qps=20136 latency=445
qps=22238 latency=401
qps=22229 latency=403
qps=19109 latency=470
qps=22569 latency=394
qps=26250 latency=343
qps=28208 latency=318
qps=29649 latency=301
qps=29874 latency=301
qps=30033 latency=301
qps=25911 latency=345
qps=28048 latency=317
qps=27398 latency=329
```

##### golang访问mysql（50协程）

go run ../mysql_go_press.go -thread_num=50

```
qps = 23660 latency = 2049
qps = 23198 latency = 2160
qps = 23765 latency = 2181
qps = 23323 latency = 2149
qps = 14833 latency = 2136
qps = 23822 latency = 2853
qps = 20389 latency = 2474
qps = 23290 latency = 2151
qps = 23526 latency = 2153
qps = 21426 latency = 2613
qps = 23339 latency = 2155
qps = 25623 latency = 2084
qps = 23048 latency = 2210
qps = 20694 latency = 2423
qps = 23705 latency = 2122
qps = 23445 latency = 2125
qps = 24368 latency = 2054
qps = 23027 latency = 2175
qps = 24307 latency = 2063
qps = 23227 latency = 2096
qps = 23646 latency = 2173
```

以上是启动50并发的查询请求，看上去qps都比较相似，但是libmysqlclient延时明显低。

##### brpc框架访问mysql（100线程）

./mysql_press -thread_num=100 -op_type=1 -use_bthread=true

```
qps=26428 latency=3764
qps=26305 latency=3780
qps=26390 latency=3779
qps=26278 latency=3787
qps=26326 latency=3787
qps=26266 latency=3792
qps=26394 latency=3773
qps=26263 latency=3797
qps=26250 latency=3783
qps=26362 latency=3782
qps=26212 latency=3796
qps=26260 latency=3800
qps=24666 latency=4035
qps=25569 latency=3896
qps=26223 latency=3794
qps=25538 latency=3890
qps=20065 latency=4958
qps=23023 latency=4331
qps=25808 latency=3875
```

##### libmysqlclient实现（100线程）

./mysql_press -thread_num=50 -op_type=1 -use_bthread=true

```
qps=29467 latency=304
qps=29413 latency=305
qps=29459 latency=304
qps=29562 latency=302
qps=30657 latency=291
qps=30445 latency=295
qps=30179 latency=298
qps=30072 latency=297
qps=29802 latency=299
qps=29752 latency=301
qps=29701 latency=304
qps=29731 latency=301
qps=29622 latency=299
qps=29440 latency=304
qps=29495 latency=306
qps=29297 latency=303
qps=29626 latency=306
qps=29482 latency=300
qps=28649 latency=313
qps=29537 latency=305
qps=29634 latency=299
```

##### golang访问mysql（100协程）

go run ../mysql_go_press.go -thread_num=100

```
qps = 22108 latency = 4553
qps = 21930 latency = 4536
qps = 20653 latency = 4906
qps = 22100 latency = 4443
qps = 21091 latency = 4850
qps = 21718 latency = 4600
qps = 21444 latency = 4488
qps = 17832 latency = 5859
qps = 18296 latency = 5378
qps = 20463 latency = 4963
qps = 21611 latency = 4880
qps = 18441 latency = 5424
qps = 20731 latency = 4834
qps = 20611 latency = 4837
qps = 20188 latency = 4979
qps = 15450 latency = 5723
qps = 20927 latency = 5328
qps = 19893 latency = 5027
qps = 21080 latency = 4782
qps = 20192 latency = 4970
```

以上是启动100并发的查询请求，看上去qps都比较相似，但是libmysqlclient延时明显低。

并发调整到150的时候，mysql-server已经报错"Too many connections"。

为什么并发数50或者100的时候libmysqlclient的延时会那么低呢？因为libmysqlclient使用的IO模式为阻塞模式，我们运行的mysql_press和mysqlclient_press都是使用的bthread模式（-use_bthread=true），底层默认都是9个pthread，使用阻塞模式的libmysqlclient和mysql交互的相当于并发度是9个线程，mysql会启动9个线程，使用非阻塞模式的rpc访问mysql并发度相当于100个，mysql会启动100个线程，所以会造成mysql的频繁上线文切换。

如果将libmysqlclient的执行方式改为不使用bthread，那么100个线程的执行效果为如下：

```
qps=26919 latency=1927
qps=27155 latency=2037
qps=28054 latency=1784
qps=26738 latency=1856
qps=27807 latency=1781
qps=26734 latency=1730
qps=26562 latency=1939
qps=27473 latency=1845
qps=26677 latency=1806
qps=27369 latency=1948
qps=27955 latency=1618
qps=26574 latency=2151
qps=27343 latency=1777
qps=26705 latency=1822
qps=26668 latency=1807
qps=25347 latency=2104
qps=26651 latency=1560
qps=27815 latency=1979
qps=27221 latency=1762
qps=26516 latency=2017
```

这个结果就和brpc框架启动100个bthread访问mysql的效果类似了。



以上为我的一些简单测试，以及一些简单的分析，在低并发的情况下同步IO的效率高于异步IO，可以阅读[IO相关的内容](io.md)有更多解释，后续还将继续分析性能问题，优化协议，给出更多测试。