MySQL Innodb資料庫效能實踐——合適的表記錄數

在實際工作中，經常有同事問道：MySQL Innodb表記錄數多大是合適的？
一般的理解肯定是表越大效能越低，但具體低多少呢，是緩慢下降還是急劇下降，是1000萬就下降還是1億才下降呢？

針對這些問題，我做了一下基準測試，基準測試環境如下：
【硬體設定】

硬體	配置
CPU	Intel(R) Xeon(R) CPU E5620 主頻2.40GHz， 物理CPU 2個，邏輯CPU 16個
記憶體	24G（6塊 * 4G  DDR3 1333 REG）
硬碟	300G * 3個，SAS硬碟 15000轉，無RAID，有RAID卡，且開了回寫功能
OS	RHEL5
MySQL	5.1.49/5.1.54

【MySQL配置】

配置項	配置
innodb_buffer_pool_size	18G
innodb_log_file_size	200M
innodb_log_files_in_group	3
sync_binlog	100
innodb_flush_log_at_trx_commit	2

【表配置】

配置項	配置
記錄數	1000萬，2000萬，5000萬，1億
儲存引擎	Innodb
行格式	compact

效能測試結果如下：
1）同一張表，不同記錄數，測試結果如下：

分析如下：
（1）當表大小小於Inndob buffer pool時，整體效能會隨著表記錄數的增加而略微降低，但各種操作的效能差別總體不大（例如1KW/2KW是12000TPS，5KW是10000TPS，相差16%）。

（2）當表大小大於Innodb buffer pool（10KW）時，效能急劇下降（從12000降到1000），效能接近高安全性配置的效能，因為此時磁碟IO成為了效能的主要影響因素。

因此，表記錄數本身對效能影響不大，關鍵是表的大小是否小於Innodb buffer pool。

2）不同的表，不同的記錄數
為了進一步驗證表記錄對效能的影響，還驗證了另外一種情況：記錄數很大但每條記錄較短，和記錄數較少但每條記錄很長兩種表對比。

主要對比了Value長度為10（以下簡稱Value10）和Value長度為250（以下簡稱Value250）的表的效能。由於Innodb只有在buffer pool大於表大小的時候效能才最高，因此測試對比時為了滿足這個條件，Value10的表記錄數是10KW，而Value250的表記錄數是2KW。

從可以看出，即使Value10的表記錄數是Value250的表記錄數的5倍，但效能反而高出16%左右，由此可以看出，相比表記錄數來說，行長度對效能影響更大，行越長效能越低。

【應用建議】
基於以上分析，對於表記錄數需要考慮的是記錄數的臨界點，即：表達到這個記錄數後，表大小（資料和索引）超過了Innodb buffer pool的大小；而設計時推薦盡量設計和試用行長度小而精的表。

註：
測試資料只為對比用，不代表一般情況下MySQL的效能就這麼高，因為為了能夠對比，測試時做了很多準備工作，測試操作也是比較特殊的