MySQL 网络通信浅析 MySQL的网络通信协议主要包含以下几个层次，从最上层的MySQL数据包协议层到最底层的socket传输： ~~~ | THD | Protocol | NET | VIO | SOCKET ~~~ 本文主要扫一下相关的代码，以下分析基于MySQL5.7。 ## 创建会话 在MySQL5.7中对会话协议层的代码进行了大量的重构以优化性能，并使得代码更加可读。以下这幅图大概展示了几个相关的类关系（未包含诸如windows平台的相关类）  创建用户线程堆栈是从主线程开始的，监听客户端请求并创建处理线程 ~~~ mysqld_main |-->connection_event_loop |-->listen_for_connection_event //根据不同的监听模式，去监听新请求, 当获取到一个新的监听请求时，会创建一个Channel_info类，用 来存储用户的socket信息 |-->Connection_handler_manager::process_new_connection |-->Per_thread_connection_handler::add_connection //我们通常用的one thread one connection对应的类为Per_thread_connection_handler |-->创建用户线程，线程函数为handle_connection ~~~ 在MySQL5.7里一个重大的优化，如上所述，就是关于用户会话的thd, net, vio等信息的初始化都不是在主线程进行的，而是创建用户线程后，由用户线程自己来完成。通过这种方式，主线程可以更高效的接受新的连接请求，从而优化了在短连接场景下的性能。见[官方博客](http://mysqlserverteam.com/improving-connectdisconnect-performance/) 及相应的[worklog](http://dev.mysql.com/worklog/task/?id=6606) 下面这幅图摘自官方博客，大家感受下5.7相比之前版本的短连接性能优化：  创建用户会话的主要函数栈包括： ~~~ handle_connection //线程入口函数 |-->init_new_thd |-->Channel_info_local_socket::create_thd |-->Channel_info::create_thd |-->create_and_init_vio |-->Protocol_classic::init_net |-->my_net_init |-->vio_fastsend //设置socket选项 * 设置IP_TOS为IPTOS_THROUGHPUT * 设置TCP_NODELAY |-->Global_THD_manager::add_thd // 加入到thd链表上 |-->thd_prepare_connection |-->login_connection |--> check_connection //检查链接，设置thd的链接信息， |--> vio_keepalive // 设置SO_KEEPALIVE选项 |--> acl_authenticate // 权限认证 |-->prepare_new_connection_state //如果连接打开了CLIENT_COMPRESS，设置NET::compress为true。 //如果设置了init_connect，则在这里执行对应的SQL语句 /* 循环接受请求并处理(do_command) */ |-->Protocol_classic::get_command |-->Protocol_classic::read_packet |-->my_net_read // 读取command包，这里的读超时时间由wait_timeout决定 |-->close_connection |-->THD::disconnect |-->THD::shutdown_active_vio |-->vio_shutdown /* 关闭socket */ ~~~ ## NET/VIO my_net_write 该函数用于将数据拷贝到NET缓冲区，当长度大于`MAX_PACKET_LENGTH`(即4MB-1字节)会对Packet进行拆分成多个packet。每个Packet的头部都会留4个字节，其中：1~3字节，存储该packet的长度，第4个字节存储当前的packet的序号，每存储一次后递增`net->pkt_nr`。 每个Net对象有一个Buff(`net->buff`)，即将发送的数据被拷贝到这个buffer中，当Buffer满时需要立刻发出到客户端。如果Buffer足够大，则只做memcpy操作。`net->write_pos`被更新到写入结束的位置偏移量 (`net_write_buff`) 如果一次写入的数据被拆分成多个Packet，那么net->pkt_nr也对应的递增多次. pkt_nr的作用是在客户端解析时，防止包发送乱序。 net_flush 实际上在`my_net_write`函数中，如果`net->buff`不够用，已经会做网络写了，`net_flush`最终保证所有在buff中的数据被写到网络 当客户端启用压缩协议时，这里会有些不同的，会给packet头部再加3个字节(`COMP_HEADER_SIZE`)，被压缩的数据不包含头部的7个字节： ~~~ [3bytes:Packet的长度] [1bytes: pkt_nr] [3bytes:压缩后的长度] [1bytes: compress_pkt_nr] ~~~ 同样的，每个压缩包都会递增`net->compress_pkt_nr` net_write_raw_loop 当packet准备好发送后，调用函数`net_write_raw_loop`开始进行数据发送 * 发送模式受`vio->write_timeout`影响(通过参数`net_write_timeout`控制)；当该参数被设置成大于等于0时，使用非阻塞模式send数据包(`MSG_DONTWAIT`) * 若网络发送被中断（EINTR），会去尝试重传 * 使用非阻塞模式send，每次并不保证数据全部发送完毕，因此需要循环的调用直到所有的数据都发送完毕 * 当输出缓冲区满时，获得错误码EWOULDBLOCK/EAGAIN,则阻塞等待(`vio_socket_io_wait`)，最大等待时间为`net_write_timeout`，超时则返回错误 my_net_read 根据NET接口先读取数据包(`net_read_packet`)： * 先读取packet header，一个普通的packet header包含4个字节，压缩协议下则另外再加3个字节，如上述(`net_read_packet_header`)。其中的pkt_nr会提取出来和本地的值相比较。在读写两段维持的pkt_nr自增值保证了服务器和客户端的通信以一种有序的方式进行，并用于校验包的有序性。如果不一致，则说明网络包发生了乱序。直接报错。如果一致，本地net->pkt_nr++ * 从packet header中提取剩下的packet长度，继续从socket读取 Vio Vio在NET的更下一层，封装了所有对socket的操作。根据不同的连接类型（TCP/IP, Socket, Name Pipe, SSL, SHARED MEMORY），相关函数指针在vio_init函数中定义，这里不展开描述 相关参数 * connect_timeout: 在连接认证阶段的网络交互超时时间(ref `login_connection`); * wait_timeout: 等待来自客户端的新的command请求; * net_read_timeout: 一般情况下的SQL通常直接从command发过来，但拿到command后，在一条语句内可能还需要和客户端交互，这里会用到该timeout值，例如`load data local infile`语句; * net_write_timeout: 就是通过网络发送数据的最大超时时间; * interactive_timeout: 当客户端打开选项CLIENT_INTERACTIVE时，将当前会话的NET的wait_timeout设置为该值; ## 结果集 MySQL有两种常用的数据协议，一种是用于Prepared Statement，对应类为`Protocol_binary`，另外一种是普通的协议，对应类为`Protocol_classic` 我们以一个简单的表为例: ~~~ mysql> create table t1 (a int, b int); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values (1,1),(2,2); Query OK, 2 rows affected (0.00 sec) ~~~ 当执行最后一条select操作时，这里使用的类为Protocol_classic 发送metadata * ref: `Protocol_classic::start_result_metadata` 将列的个数写入Net缓冲区 * ref: `Protocol_classic::send_field_metadata` 逐列的准备元数据信息，包含： ~~~ | 3bytes 标识符：def | db_name | table_name | org_table_name | col_name | org_col_name | 字符集编码 | 列长度 | 列类型 | flags | decimals(这里为0) | 预留 | 预留 ~~~ 可以看到每个列的元数据都包含了非常多的信息，使用字符串存储，这也意味着对于一条简单的SQL，你的网络传输的内容可能大多数都是元数据，即时你的客户端可能并不需要引用到。 有多个列就写多个packet到Net buffer (Protocol_classic::end_row) * ref: `Protocol_classic::end_result_metadata` write_eof_packet函数会被调用，用于标识元数据信息到此结束。此处共写5个字节（不含packet header） 发送数据 ref: `end_send --> Protocol_classic::end_row` 如上例，发送两行数据的packet包括 | 1 | ‘1’ | 1 | ‘1’ | | 1 | ‘2’ | 1 | ‘2’ | 结束发送 ref: `THD::send_statement_status -->net_send_eof --> write_eof_packet` 发送结果结束标记，其中包含了sql执行过程中产生的warning个数 元数据开销 从上述可以看到，结果集中有很大一部分的开销是给元数据的，这意味着类似普通的pk查询，元数据的开销可能会非常昂贵。 以下贴下我之前测试过的一个例子，增加了几个选项来控制发送的元数据量： ~~~ 0/METADATA_FULL: return all metadata, default value. 1/METADATA_REAL_COLUMN: only column name; 2/METADATA_FAKE_COLUMN: fake column name ,use 1,2...N instead of real column name 3/METADATA_NULL_COLUMN: use NULL to express the metadata information 4/METADATA_IGNORE: ignore metadata information, just for test.. ~~~ 测试表： ~~~ CREATE TABLE `test_meta_impact` ( `abcdefg1` int(11) NOT NULL AUTO_INCREMENT, `abcdefg2` int(11) DEFAULT NULL, `abcdefg3` int(11) DEFAULT NULL, `abcdefg4` int(11) DEFAULT NULL, …… …… `abcdefg40` int(11) DEFAULT NULL, PRIMARY KEY (`abcdefg1`) ) ENGINE=InnoDB AUTO_INCREMENT=229361 DEFAULT CHARSET=utf8 ~~~ 使用mysqlslap测试并发pk查询 ~~~ mysqlslap --no-defaults -uxx --create-schema=test -h$host -P $port --number-of-queries=1000000000 --concurrency=100 --query='SELECT * FROM test.test_meta_impact where abcdefg1 = 2' ~~~ 测试结果 ~~~ METADATA_FULL : 3.48w TPS, Net send 113M METADATA_REAL_COLUMN: 7.2W TPS, Net send 111M METADATA_FAKE_COLUMN: 9.2W TPS , Net send 116M METADATA_NULL_COLUMN: 9.6w TPS , Net send 115M METADATA_IGNORE: 13.8w TPS, Net send 30M ~~~ 很显然无论网络流量还是TPS吞吐量，在这个人为构造的极端场景下，元数据的开销都非常的显著。