mysql5.7性能对比mysql5.6_MySQL5.7.23 VS MySQL5.6.21 分區表性能對比測試

為評估MySQL從5.6.21升級到5.7.23版本的性能，針對分區表的讀寫做了對比測試。

【測試環境】

1、 兩台HP380的物理機，配置一致，CPU：Intel(R) Xeon(R) CPU E5-2650 v3 @ 2.30GHz，內存：128G，磁盤：2*300G,10K(1) 8*900G,10K(10)

2、測試實例還原了生產集群的實例，測試分區表記錄數約3800萬

3、采用生產真實樣本SQL語句，模擬並發線程16、32、64、128、256情況下，對比SQL執行1000萬次的QPS和TPS性能。

4、MySQL的配置為各自版本的上線標准配置

【測試結論】

1、 分區表在只讀情況下，MySQL 5.7的性能略低於5.6的性能，約在15%左右，但隨着並發線程數的增加，MySQL5.7的性能接逐漸近於5.6的性能

為什么MySQL5.7分區表的查詢性能會有下降，后面我再進一步分析。

2、 分區表在只寫情況下，MySQL 5.7的性能明顯好於5.6的性能，性能提升在40%以上，隨着並發線程數的增加，MySQL5.7的性能比較穩定，但5.6的性能開始逐步下降

這個差異可能與5.7的配置參數調整有關，

innodb_io_capacity=1000

innodb_read_io_threads=8

innodb_write_io_threads=8

3、 分區表在混合讀寫(讀寫比例約1:1)情況下，MySQL 5.7的性能明顯好於5.6的性能，性能提升在30%以上，隨着並發線程數的增加，MySQL5.7的性能比較穩定，但5.6的性能開始逐步下降

【進一步分析】

分區表在只讀場景，並發線程不高的情況下，MySQL 5.7的性能略低於5.6的性能。下面從一些維度對比了兩個版本下相同查詢SQL的執行性能。

【兩個版本差異的補充說明】

1、external_lock不同，按照文檔上的說明計算，5.7分區鎖定數為0，5.6分區鎖定數為11

The server increments this variable for each call to its external_lock() function, which generally occurs at the beginning and end of access to a table instance. There might be differences among storage engines. This variable can be used, for example, to discover for a statement that accesses a partitioned table how many partitions were pruned before locking occurred: Check how much the counter increased for the statement, subtract 2 (2 calls for the table itself), then divide by 2 to get the number of partitions locked.

2、執行計划顯示不同，在符合的分區partitions，優化器預估的記錄條數rows，根據條件過濾到的記錄百分比filtered，格外信息extra四個字段不一致

MySQL5.7

id: 1

select_type: SIMPLE

partitions: p20190223,p20190224,p20190225,p20190226,p20190227,pMax

type: ref

key_len: 610

ref: const,const

rows: 1

filtered: 100.00

Extra: NULL

MySQL5.6

id: 1

select_type: SIMPLE

type: ref

key_len: 610

ref: const,const

rows: 12

Extra: Using where

執行計划中partitions和filtered的差異，是由於explain命令默認選項不同造成的。

在5.7以前的版本中，想要顯示partitions需要使用explain partitions命令；要顯示filtered需要使用explain extended命令。在5.7版本后，默認explain直接顯示partitions和filtered中的信息。

3、16並發線程壓測平均CPU使用率(usr%)，MySQL5.7在QPS小於MySQL5.6的情況下，用戶CPU使用率卻高於5.6

4、CPU高消耗的函數調用棧不同，主要差異在於MySQL5.7.23在execute_sqlcom_select函數中調用handle_query，而MySQL5.6.21為handle_select，代碼本身邏輯做了修改。

MySQL5.7

MySQL5.6

數據庫運維經驗分享&MySQL源碼學習漫漫路