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吞吐量，在這個人為構造的極端場景下，元數據的開銷都非常的顯著。