且將新火試新茶 - MySQL Benchmark（全文）
收藏且將新火試新茶 - MySQL Benchmark
公司內(nèi)部最流行的數(shù)據(jù)庫就是MySQL，而關(guān)于MySQL性能，我聽過種種傳說和流言。而對(duì)于數(shù)據(jù)庫的性能優(yōu)化和測試，我一直有強(qiáng)烈的興趣，曾經(jīng)見過一篇Oracle的性能優(yōu)化文章，Linux Journal的Bert Scalzo所著的
《Linux Maximus, Part 1: Gladiator-like Oracle Performance》，國人的翻譯是
《角斗士般的Oracle性能》[注]，那兄弟為了壓榨Oracle的性能，從數(shù)據(jù)庫的參數(shù)到操作系統(tǒng)的參數(shù)都進(jìn)行了調(diào)整，最后得到的優(yōu)化性能比開始提高了10多倍。為什么我用了壓榨這個(gè)詞，你可以想像將文件的最后修改時(shí)間調(diào)整為不可改變都可以提升數(shù)據(jù)庫的性能。調(diào)整幾個(gè)參數(shù)就可以使系統(tǒng)的能力如此提升，何樂而不為呢？
對(duì)于MySQL的性能，基本分成兩種截然不同的聲音：MySQL自己聲稱的性能數(shù)據(jù)是如此的優(yōu)秀，超過Oracle等數(shù)據(jù)庫若干倍等等。其實(shí)大多數(shù)公司進(jìn)行的這樣的對(duì)比測試都是不可信的，自己產(chǎn)品用專有接口，別人產(chǎn)品用出了名的慢接口ODBC，自己不用事務(wù)，別人用事務(wù)操作，這樣的結(jié)果數(shù)據(jù)差距當(dāng)然十萬八千里。另外一種聲音基本來之公司內(nèi)部，主要質(zhì)疑的是大數(shù)量下的性能，普遍的態(tài)度是認(rèn)為在200000條記錄以上，MySQL的性能將急劇下降。我個(gè)人是后一種觀念的受害者，經(jīng)常為了活動(dòng)的開發(fā)還分表。最近有機(jī)會(huì)利用公司的設(shè)備資源，自己比較測試了若干種環(huán)境下的MySQL的表現(xiàn)，很多測試數(shù)據(jù)的結(jié)果顛覆了自己原來對(duì)MySQL的認(rèn)識(shí)。
本文名為benchmark,但是實(shí)際是沒有一個(gè)真正的基準(zhǔn)，大部分測試是不同狀況下的性能比較測試。如果非要找一個(gè)基準(zhǔn)，那就是MySQL本身。
根據(jù)自己的疑惑，主要把測試的重點(diǎn)放在了MySQL不同版本間的性能比較，MySQL的查詢緩存的性能，MySQL的MyISAM和InnoDB數(shù)據(jù)庫引擎間性能比較，同時(shí)模擬了壓力情況，真實(shí)環(huán)境測試性能，同時(shí)對(duì)于MySQL的新特性API接口STMT也進(jìn)行了測試。在文檔的最后，我給出了MySQL相關(guān)的優(yōu)化參數(shù)說明以及MySQL的相關(guān)狀態(tài)監(jiān)控方法。
1 測試的相關(guān)環(huán)境
1.1 測試的機(jī)器
既然打著Benchmark的旗號(hào)，就必須介紹一下用于測試的機(jī)器。由于懶和想測試的用例是逐步的加入的，到測試結(jié)束，我耗費(fèi)了將近6個(gè)月的時(shí)間。中間被我占用來測試的機(jī)器居然有4臺(tái)，2種，HP380G3和HP380G4， 380G4的機(jī)器配置上要好一些（雖然我感覺上表現(xiàn)和G4的基本相同），所以可能影響測試結(jié)果數(shù)據(jù)。而且在后期整理數(shù)據(jù)階段，發(fā)現(xiàn)了很多測試數(shù)據(jù)偏差過大。我不得不懷疑機(jī)器有人同時(shí)使用造成了影響。
所幸我最后基本上把所有的測試用例在一臺(tái)絕對(duì)空閑380G3上全部重做了一次。如果沒有特別指明之處，大家就把測試機(jī)器看作內(nèi)核是2.6.8.1（這個(gè)內(nèi)核好像有些不完善.）的380G3吧。
其中模擬多表訪問的測試是在另有一臺(tái)2.4.21的內(nèi)核的380G3測試了。為了保證對(duì)比的公平性，相關(guān)的比較都是相同機(jī)器上的測試數(shù)據(jù)。
表1 HP380G4和HP380G3
CPU
MEM
DISK
HP380G4
CPU*2 （3000M or 3200M Hz）
4G ECC
SCSI RAID 5 36G *4
HP380G3
CPU*2 （2800M Hz）
2G ECC
SCSI RAID 5 73 G *4
兩個(gè)的SCSI控制器好像都是Ultra 320 Smart Array 6i （64MB緩存）
1.2 測試的表
測試的表采用單鍵索引，主鍵為INT類型，記錄類型覆蓋了我們常用的記錄類型，記錄的長度為120多個(gè)字節(jié)，和我們平常的記錄表類似。
CREATE TABLE IF NOT EXISTS benchmark.test1
(
F1 INT NOT NULL ,
F2 INT NOT NULL DEFAULT 0,
F3 INT NOT NULL DEFAULT 0,
F4 INT NOT NULL DEFAULT 0,
F5 INT NOT NULL DEFAULT 0,
F6 INT NOT NULL DEFAULT 0,
F7 INT NOT NULL DEFAULT 0,
F8 INT NOT NULL DEFAULT 0,
F9 INT NOT NULL DEFAULT 0,
F10 INT NOT NULL DEFAULT 0,
F11 FLOAT(10,4) NOT NULL DEFAULT 0,
F12 DOUBLE(10,4) NOT NULL DEFAULT 0,
F13 VARCHAR(64) NOT NULL DEFAULT “,
F14 DATETIME NOT NULL DEFAULT ‘0’,
PRIMARY KEY (F1)
);
2 MySQL不同版本間的性能比較
不知道大家初次見到MySQL是何種感覺，反正我見到MySQL3.23時(shí)很不以為然，沒有視圖，沒有事務(wù)，沒有觸發(fā)器，沒有子查詢，沒有存儲(chǔ)過程，沒有……，懶得提了，但是要說是今天MySQL5.0的能力已經(jīng)得到了很大的提升，至少上面說明的這些問題，MySQL已經(jīng)解決了大部分了。
2.1 比較的版本
選擇比較的版本是3.23版本,4.0版本, 4.1版本，5.0 Beta版本。其中3.23版本為MySQL的最早的可用版本，XXX等產(chǎn)品也一直在使用，4.0為MySQL支持查詢緩沖的版本，也有很多產(chǎn)品使用。MySQL 4.1和5.0版本為較新的版本，支持一些新的特性。
MySQL的安裝可以使用源代碼自己編譯，也可以使用MySQL官方提供的編譯版本，相對(duì)而言官方提供的靜態(tài)編譯版本比較容易安裝(動(dòng)態(tài)編譯的版本要依賴glibc庫版本)。
另外MySQL提供從4.1以后的提供了Intel C++ Compiler編譯的版本，據(jù)稱Intel C++ Compiler的版本比GCC編譯的版本速度要快20%以上，所以也找來看看。
從下面這個(gè)表，我們也可以看出MySQL發(fā)展史和各個(gè)版本的基本特點(diǎn)，可以看出MySQL從一個(gè)過家家類型的數(shù)據(jù)庫開始越來越像一個(gè)成熟的商用數(shù)據(jù)庫了。
表2 測試的版本
說明
版本特點(diǎn)
MySQL3.23.51
公司內(nèi)部分產(chǎn)品使用的版本，
為自己用gcc編譯
MySQL3.23.55
官房網(wǎng)站下載的3.23的gcc編譯static版本
3.23的一個(gè)成熟版本
MySQL 4.0 gcc
官房網(wǎng)站下載的gcc編譯static版本下載4.0版本，公司相當(dāng)產(chǎn)品使用
提供了查詢Cache
提供了FULLTEXT的文本檢索索引
提供了嵌入式的MySQL
InnoDB開始成為內(nèi)建引擎,InnoDB支持事務(wù)，外鍵，操作行鎖定等特性
可以動(dòng)態(tài)調(diào)整MySQL的某些運(yùn)行參數(shù), ”SET”
部分功能性能提升，如批量插入，
功能增加，如TRUNCATE，UNION查詢等
MySQL 4.1 gcc
官房網(wǎng)站下載的gcc編譯static版本
提供的新特性
子查詢的功能，SELECT的嵌套
使用MYSQL_STMT加快了C/S通訊速度，
增加了一些新函數(shù)
MySQL 4.1 icc
官房網(wǎng)站下載的intel c++ 編譯4.1版本，要intel的動(dòng)態(tài)庫支持
Intel C++編譯的版本號(hào)稱速度要快20%以上
MySQL 5.0 gcc (beta)
最新的MySQL版本，官房網(wǎng)站下載版本
從這個(gè)版本開始MySQL開始真正像一個(gè)商用數(shù)據(jù)庫了。
MySQL5.0的正式頒布已經(jīng)發(fā)布
提供很多成熟商業(yè)數(shù)據(jù)庫的特性，
視圖，以及相關(guān)一些管理功能
存儲(chǔ)過程，光標(biāo)
觸發(fā)器，已經(jīng)支持時(shí)間和事件觸發(fā)
VARCHAR長度的增加
InnoDB支持分布式事務(wù)
支持一些新的存儲(chǔ)引擎(ARCHIVE,只支持插入和查詢操作，F(xiàn)EDERATED, 訪問遠(yuǎn)程數(shù)據(jù))
2.2 測試的方法和結(jié)果
分別，插入，查詢，修改，刪除，1000000，5000000，10000000，條記錄。嘗試MySQL在不同的記錄數(shù)量級(jí)別下的表現(xiàn)。表不進(jìn)行壓縮存儲(chǔ)。
測試環(huán)境使用使用MyISAM數(shù)據(jù)庫引擎，配置中比較關(guān)鍵的參數(shù)為，對(duì)于4.0后的查詢版本使用查詢Cache。同時(shí)為了模擬真實(shí)環(huán)境，記錄二進(jìn)制日志。
使用的關(guān)鍵參數(shù)為：
set-variable = key_buffer_size=384M
set-variable = query_cache_size=384M
set-variable = sort_buffer_size =1M
set-variable = read_buffer_size=1M
set-variable = table_cache=256
1. 測試采用腳本分組進(jìn)行，每次測試前刪除掉原來的數(shù)據(jù)，同時(shí)刪除掉二進(jìn)制日志（可能flush-log一下更好）。每次操作前sleep 20秒。
2. 插入記錄為插入0-N條記錄，每次單條插入。
3. 查詢?yōu)槊看尾樵円粭l的N次查詢。遍歷所有的記錄。查詢使用主鍵作為查詢條件。查詢操作完成轉(zhuǎn)儲(chǔ)結(jié)果集的操作。
4. 修改所有的數(shù)據(jù)，每個(gè)記錄修改部分信息（4個(gè)字段），每次修改1條。
5. 刪除所有的數(shù)據(jù)，為每次單獨(dú)刪除。刪除使用主鍵作為查詢條件。
表3 數(shù)據(jù)文件的大小
記錄條數(shù)
數(shù)據(jù)文件大小
索引文件大小
1000000
111999988
8209408
5000000
559999988
41036800
10000000
1119999988
82071552
測試的性能數(shù)據(jù)為：
表4 不同版本的表現(xiàn)的耗時(shí)
數(shù)據(jù)庫
3.23.51
耗時(shí)(s)
3.23.55
耗時(shí)(s)
4.0
耗時(shí)(s)
4.1gcc
耗時(shí)(s)
4.1icc
耗時(shí)(s)
5.0
耗時(shí)(s)
插入1000000條記錄
175
170
169
176
183
185
查詢1000000條記錄
208
203
282
306
323
345
改寫1000000條記錄
173
167
165
174
185
178
刪除1000000條記錄
183
169
172
179
188
184
插入5000000條記錄
899
876
876
898
947
949
查詢5000000條記錄
1029
1014
1472
1595
1671
1773
改寫5000000條記錄
871
841
816
859
908
911
刪除5000000條記錄
914
855
958
976
1005
1006
插入10000000條記錄
1811
1745
1758
1817
1896
1954
查詢10000000條記錄
2080
2033
3001
3257
3386
3607
改寫10000000條記錄
1884
1793
1844
1904
1969
2019
刪除10000000條記錄
1976
1897
1908
1982
2045
2102
表5 不同版本處理性能比較
數(shù)據(jù)庫
3.23.51處理速度(條/s)
3.23.55處理速度 (條/s)
4.0處理速度
(條/s)
4.1gcc處理速度 (條/s)
4.1icc
處理速度
(條/s)
5.0Beta
處理速度
(條/s)
插入1000000條記錄
5714.29
5882.35
5917.16
5681.82
5464.48
5405.41
查詢1000000條記錄
4807.69
4926.11
3546.10
3267.97
3095.98
2898.55
改寫1000000條記錄
5780.35
5988.02
6060.61
5747.13
5405.41
5617.98
刪除1000000條記錄
5464.48
5917.16
5813.95
5586.59
5319.15
5434.78
插入5000000條記錄
5561.74
5707.76
5707.76
5567.93
5279.83
5268.70
查詢5000000條記錄
4859.09
4930.97
3396.74
3134.80
2992.22
2820.08
改寫5000000條記錄
5740.53
5945.30
6127.45
5820.72
5506.61
5488.47
刪除5000000條記錄
5470.46
5847.95
5219.21
5122.95
4975.12
4970.18
插入10000000條記錄
5521.81
5730.66
5688.28
5503.58
5274.26
5117.71
查詢10000000條記錄
4807.69
4918.84
3332.22
3070.31
2953.34
2772.39
改寫10000000條記錄
5307.86
5577.24
5422.99
5252.10
5078.72
4952.95
刪除10000000條記錄
5060.73
5271.48
5241.09
5045.41
4889.98
4757.37
比較圖表如下：
多個(gè)版本間的性能比較
從上面兩張表可以看出，MySQL的簡單查詢，修改，插入，刪除性能還是非常可圈可點(diǎn)的。在1000000的記錄級(jí)別，效率也可以達(dá)到3000-6000條左右。
總體來看，除了查詢，大家的性能其實(shí)都差不多。從4.0開始的版本，查詢速度都有一個(gè)明顯的下降，個(gè)人估計(jì)是由于4.0后MySQL支持查詢Cache造成的，我的查詢方式幾乎不會(huì)利用到Cache數(shù)據(jù)，所以Cache不但沒有幫助提高查詢效率，反而降低了查詢的效率?；谶@個(gè)問題，我們后面單獨(dú)對(duì)MySQL的Cache進(jìn)行了單獨(dú)的測試。
我在測試后驚訝的發(fā)現(xiàn)，最好的數(shù)據(jù)居然是mysql3.23.55的版本的性能。我只能猜測無數(shù)的功能堆加后，性能多少有下降，5.0Beta版本也的確排名最后。而對(duì)于現(xiàn)有使用的版本3.23.51和3.23.55版本的性能很接近。但是看來使用安裝包版本比自己編譯版本要好。
icc編譯的版本沒有想傳言那樣體現(xiàn)優(yōu)勢。我個(gè)人估計(jì)我的測試用例主要測試的是MySQL的I/O性能，而不是運(yùn)算。icc的強(qiáng)項(xiàng)體現(xiàn)不出來。
即使到了10000000的數(shù)據(jù)量級(jí)別，查詢和更新速度仍然可以接受。不想傳說中那么恐怖。
插入速度的確快過查詢速度。B樹的重建索引速度大于查詢的。？而且即使對(duì)于MySQL3.23的版本，查詢的性能還是要落后于其他操作，個(gè)人估計(jì)是由于查詢結(jié)果要返回結(jié)果集合造成的。
從這組數(shù)據(jù)我們可以發(fā)現(xiàn)，在數(shù)據(jù)量增加后，插入數(shù)據(jù)的性能變化很小，查詢數(shù)據(jù)的性能成非常輕微的下降，這很好理解，這是由于查詢的索引增大造成的。
個(gè)人覺得這些性能數(shù)據(jù)接近這些操作在MySQL在380G3上的極限性能。因?yàn)檫@些基本操作的瓶頸在都在I/O上，并發(fā)不可能提高這些性能[注]。倒是調(diào)整操作系統(tǒng)的參數(shù)和內(nèi)核還可能有一些優(yōu)化的余地。
當(dāng)然改善硬件設(shè)備和關(guān)閉二進(jìn)制日志等優(yōu)化還是可以提高一些性能。
2.3 MySQL的版本
從上面的測試結(jié)果來，使用MyISAM引擎，MySQL的數(shù)據(jù)庫的基本功能性能沒有太多變化。MySQL后面的幾個(gè)版本介紹說明有性能提高的地方也都不包括基本功能這塊。
而對(duì)于4.0后提供的查詢Cache這個(gè)功能，其實(shí)也是差強(qiáng)人意，（后面會(huì)著重討論）。所以指望升級(jí)數(shù)據(jù)庫大規(guī)模改善數(shù)據(jù)表的基礎(chǔ)功能性能看來不現(xiàn)實(shí)。但考慮到4.0和4.1版本提供了一些新的功能，而且更加穩(wěn)定。使用4.0以后的版本還是正確的選擇[注]。
MySQL的升級(jí)版本其實(shí)比想像的容易，MyISAM,InnoDB的文件格式都是與位置無關(guān)的（當(dāng)然操作系統(tǒng)的限制除外）。3.23的數(shù)據(jù)庫升級(jí)4.0運(yùn)行一個(gè)更改數(shù)據(jù)庫權(quán)限表的腳步就可以萬事大吉。而升級(jí)到4.1會(huì)有一些麻煩，4.1后的數(shù)據(jù)庫的認(rèn)證方式發(fā)生了變化，所以使用舊有的數(shù)據(jù)庫客戶端版本無法登陸。但可以通過修正新數(shù)據(jù)庫的密碼解決這個(gè)問題。詳細(xì)見
《Client does not support authentication protocol》3 MySQL的查詢Cache測試
我曾經(jīng)對(duì)MySQL的Cache作了非常大的期望。因?yàn)樘岣邤?shù)據(jù)庫的性能的最好方法就是使用大規(guī)模的Cache，但是通過查詢相關(guān)資料我了解到MySQL的查詢Cache有很多的限制。
3.1 Without Cache測試
可以看到,MySQL從4.0后查詢速度有所下降,所以我們也測試了一下不使用Cache的數(shù)據(jù)。我測試了4.0gcc編譯版本和4.1gcc編譯版本在在使用Cache和不使用Cache的情況下的速度。
關(guān)閉查詢Cache的方法為調(diào)整參數(shù) query_cache_size為0 或者設(shè)置 query_cache_type為0 [注]。
表6 是否使用cache的環(huán)境下的測試對(duì)比
數(shù)據(jù)庫
4.0gcc with cache
耗時(shí)（s）
4.0gcc without cache
耗時(shí)（s）
4.1gcc with cache
耗時(shí)（s）
4.1gcc without cache
耗時(shí)（s）
插入1000000條記錄
169
169
176
178
查詢1000000條記錄
282
200
306
219
改寫1000000條記錄
165
164
174
172
刪除1000000條記錄
172
171
179
181
插入5000000條記錄
876
866
898
905
查詢5000000條記錄
1472
995
1595
1096
改寫5000000條記錄
816
832
859
875
刪除5000000條記錄
958
875
976
942
插入10000000條記錄
1758
1743
1817
1878
查詢10000000條記錄
3001
2034
3257
2215
改寫10000000條記錄
1844
1744
1904
1862
刪除10000000條記錄
1908
1892
1982
2031
表7 是否使用cache的環(huán)境下的測試對(duì)比
數(shù)據(jù)庫
4.0gcc with cache
處理速度(條/s)
4.0gcc without cache
處理速度(條/s)
4.1gcc with cache
處理速度(條/s)
4.1gcc without cache
處理速度(條/s)
插入1000000條記錄
5917.16
5917.16
5681.82
5617.98
查詢1000000條記錄
3546.10
5000.00
3267.97
4566.21
改寫1000000條記錄
6060.61
6097.56
5747.13
5813.95
刪除1000000條記錄
5813.95
5847.95
5586.59
5524.86
插入5000000條記錄
5707.76
5773.67
5567.93
5524.86
查詢5000000條記錄
3396.74
5025.13
3134.80
4562.04
改寫5000000條記錄
6127.45
6009.62
5820.72
5714.29
刪除5000000條記錄
5219.21
5714.29
5122.95
5307.86
插入10000000條記錄
5688.28
5737.23
5503.58
5324.81
查詢10000000條記錄
3332.22
4916.42
3070.31
4514.67
改寫10000000條記錄
5422.99
5733.94
5252.10
5370.57
刪除10000000條記錄
5241.09
5285.41
5045.41
4923.68
是否使用Cache的性能比較
這個(gè)測試可以證明，使用Cache時(shí)，同樣是有成本的，特別是對(duì)查詢語句，成本還比較高,單條查詢SQL的使用Cache成本大約是比不是使用Cache的成本高40%，這個(gè)成本應(yīng)該來自MySQL要消耗將結(jié)果保存到Cache和淘汰出Cache的時(shí)間。[注]
這兒說的是成本，不是性能比較。不過還是懷疑MySQL的Cache設(shè)計(jì)有問題。
MySQL有一個(gè)配置參數(shù)query_cache_type 表示查詢的Cache類型。0表示 OFF，不進(jìn)行緩沖，1 表示ON，進(jìn)行緩沖，2表示 DEMAND，只對(duì)SELECT SQL_CACHE開頭的查詢進(jìn)行緩沖。
3.2 查詢時(shí)使用Cache
如何讓查詢時(shí)能使用上Cache，我考慮這樣進(jìn)行測試，查詢1條記錄后，再查詢4次。同時(shí)不進(jìn)行任何操作，保證后面的查詢命中Cache。測試的數(shù)量為1000000條。
測試的結(jié)果如下：
表8 查詢1000000條記錄5次的消耗時(shí)間
數(shù)據(jù)庫
查詢1000000條記錄5次耗時(shí)
平均
MySQL3.23
1082
4621.07
MySQL 4.0 gcc
892
5605.38
MySQL 4.1 gcc
855
5847.95
MySQL 4.1 gcc(不使用查詢Cache)
1410
3546.10
MySQL 4.1 icc
858
5827.51
MySQL 4.1 icc(不使用查詢Cache)
1530
3267.97
MySQL 5.0
942
5307.86
是否使用cache對(duì)查詢效率的影響
可以看到MySQL的Cache在這個(gè)理想的測試環(huán)境下確實(shí)提高了。但是這些提高也是要付出代價(jià)的。
3.3 查詢Cache的代價(jià)
正當(dāng)我對(duì)上面的結(jié)果歡心鼓舞是，而查詢的資料發(fā)現(xiàn)給自己澆了些冷水，上面的測試非常理想，MySQL的Cache使用有很多限制。
首先我們從Without Cache組測試數(shù)據(jù)可以看到，使用Cache后進(jìn)行查詢是有成本的。
在Cache中的MySQL查詢結(jié)果是和查詢SQL對(duì)應(yīng)的，如果你想命中Cache，兩次使用的查詢語句必須完全一樣。所以你最好使用同1個(gè)人編寫的API查詢數(shù)據(jù)庫。
另外，MySQL的查詢Cache淘汰策略近乎弱智，對(duì)于MyISAM引擎，對(duì)數(shù)據(jù)表的任何一次都會(huì)淘汰所有相關(guān)此表在Cache中的數(shù)據(jù)[注]，而InnoDB的引擎會(huì)在任何一次提交后淘汰所有的和此次交易相關(guān)表在Cache中的數(shù)據(jù)。所以對(duì)于一個(gè)頻繁進(jìn)行操作的數(shù)據(jù)庫，MySQL的查詢Cache的命中率肯定不會(huì)太高，而且查詢同時(shí)要增加和Cache相關(guān)的成本，要消耗有入Cache和淘汰出Cache的時(shí)間。
所以你在使用MySQL Cache時(shí)最好仔細(xì)考慮一下。如果數(shù)據(jù)庫的數(shù)據(jù)表基本是靜態(tài)數(shù)據(jù)?？梢允褂眠@個(gè)Cache。如果是動(dòng)態(tài)數(shù)據(jù)，而且改寫操作頻繁，是否值得用這個(gè)Cache很值得懷疑。[注]
更加理想的淘汰方式當(dāng)然是和鍵相關(guān)。這正是c4a 和其他很多大型數(shù)據(jù)庫Cache的設(shè)計(jì)思想。
從個(gè)人觀察得數(shù)據(jù)來看，真實(shí)環(huán)境的查詢Cache的命中率大約在3%左右，我觀察得最好的情況某產(chǎn)品的DB命中率為30%，但是我懷疑其數(shù)據(jù)庫中有一段時(shí)間不更改的靜態(tài)表（混合靜態(tài)數(shù)據(jù)和動(dòng)態(tài)數(shù)據(jù)這不是一個(gè)良好的數(shù)據(jù)庫設(shè)計(jì)風(fēng)格）。即使有30%的命中率，考慮到前面所提到的查詢成本，還是得不償失，所以我個(gè)人建議在大規(guī)模更新處理的數(shù)據(jù)庫上，關(guān)閉查詢Cache。
4 InnoDB的性能
第一次認(rèn)識(shí)InnoDB時(shí)，就是聽說InnoDB能提供MySQL的支持事務(wù)和外鍵等支持，但是其性能大大不如MyISAM引擎。但是后來發(fā)現(xiàn)公司內(nèi)部的游戲產(chǎn)品都在使用InnoDB數(shù)據(jù)庫引擎，翻閱文檔也發(fā)現(xiàn)InnoDB已經(jīng)是MySQL自己從4.0開始內(nèi)建的引擎。所以拿來比較一下。
4.1 MySQL數(shù)據(jù)庫引擎
下面先介紹MySQL現(xiàn)有的數(shù)據(jù)庫引擎，其中的InnoDB引擎已經(jīng)不是一個(gè)過家家的數(shù)據(jù)庫引擎，其為MySQL提供了許多標(biāo)準(zhǔn)數(shù)據(jù)庫的基本支持。
MySQL支持的引擎種類之多有點(diǎn)嚇人，但多少給人一些有點(diǎn)濫的感覺。
表9 MySQL的DB引擎
MySQL數(shù)據(jù)庫引擎
特點(diǎn)
說明
isam
ISAM為一種的專為磁盤存取文件設(shè)計(jì)的文件組織方式，好像還是IBM開發(fā)的。mysql 3.23版本前使用的古董
即將被淘汰。
myisam
ISAM文件格式的改進(jìn)版本
采用1張表定義對(duì)應(yīng)1個(gè)文件，表數(shù)據(jù)對(duì)應(yīng)一個(gè)文件，索引對(duì)應(yīng)一個(gè)文件的方式，
有長度限制，
很多數(shù)據(jù)操作都是表鎖定
恢復(fù)能力較弱，但有修復(fù)工具
3.23后默認(rèn)的引擎
merge
可以將多個(gè)同一庫相同結(jié)構(gòu)MyISAM表組織為一個(gè)邏輯單元，依靠它可以突破MyISAM文件的大小限制，有點(diǎn)像一個(gè)自己建立視圖（加索引）
限制很多，查詢性能低下，打開一個(gè)MERGE表相當(dāng)于打開所有的相關(guān)表。
算不上一個(gè)引擎，只能說是一個(gè)表格式
4.0以后支持一個(gè)查詢關(guān)鍵字UION，沒有太大使用的必要
HEAP
放在內(nèi)存中使用的數(shù)據(jù)表，要求字段長度固定。
速度是快，但完全沒有后備存儲(chǔ)，只能存零時(shí)數(shù)據(jù)
索引采用散列，只對(duì)=,<=>操作有提高效率作用
BDB
大名鼎鼎的Berkeley DB引擎，
有事務(wù)性(為頁面加鎖)
Berkeley DB好像只支持key->data的模式，不知道具體結(jié)合情況。
國內(nèi)幾乎沒有人在MySQL上用這個(gè)引擎
InnoDB
支持事務(wù)，外鍵，
采用數(shù)據(jù)表空間對(duì)應(yīng)數(shù)據(jù)文件的管理方式，數(shù)據(jù)表沒有大小限制
采用日志方式記錄操作，在崩潰后大部分情況可以自動(dòng)恢復(fù)，但是如果恢復(fù)后仍有問題修復(fù)較為繁瑣
對(duì)于包含檢索和修改的查詢命令支持行鎖定
4.0后的版本內(nèi)嵌支持
有點(diǎn)像1個(gè)簡化的Oracle
count(*),. truncate等操作慢，
MaxDB
MaxDB是SAP 授權(quán)給MySQL 的基于 SAP DB的數(shù)據(jù)庫引擎。
國內(nèi)用的人也很少
也不太了解授權(quán)協(xié)議內(nèi)容
ARCHIVE, ,
只支持插入和查詢操作，
5.0后支持
FEDERATED
訪問遠(yuǎn)程數(shù)據(jù)
5.0后支持
4.2 InnoDB的性能
InnoDB引擎的性能可以用冰火兩重天形容，網(wǎng)上對(duì)它的評(píng)價(jià)也可謂大相徑庭。其實(shí)核心問題只有一個(gè)。你如何使用事務(wù)。其實(shí)坦白說所有的數(shù)據(jù)庫，包括Oracle，DB2的頻繁的提交事務(wù)都將大大影響數(shù)據(jù)庫的性能。
重要的參數(shù)如下：
數(shù)據(jù)庫為4.0.22的數(shù)據(jù)庫,使InnoDB作為引擎，
數(shù)據(jù)文件每個(gè)2G，8個(gè)
日志文件3個(gè)，每個(gè)150M
Cache為打開狀態(tài)
set-variable = key_buffer_size=384M
set-variable = query_cache_size=64M
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit= 1 [這個(gè)參數(shù)很重要]
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
4.2.1 AUTOCOMMIT自動(dòng)提交
MySQL對(duì)于事務(wù)的處理有兩種方式：
自動(dòng)提交方式，SET AUTOCOMMIT=1就是每條SQL MySQL都自動(dòng)幫你提交。這樣就相當(dāng)于每條SQL都是一個(gè)事務(wù)。
手動(dòng)提交方式，SET AUTOCOMMIT=0，必須自己寫事物的開始（START TRANSACTION）,結(jié)束（COMMIT）語句或者回滾（ROLLBACK）語句。
自動(dòng)提交的方式將使系統(tǒng)的事務(wù)成千倍的增加，所以性能自然會(huì)下降很多。而有趣的是MySQL的默認(rèn)事務(wù)使用方式居然是自動(dòng)提交方式，而且好像沒有方法進(jìn)行配置修改，SET AUTOCOMMIT是針對(duì)對(duì)每個(gè)SESSION處理。（后面會(huì)提到這個(gè)問題）
對(duì)于InnoDB的測試我們分為自動(dòng)提交和手動(dòng)提交兩種，手動(dòng)提交在連接數(shù)據(jù)庫后，發(fā)送SET AUTOCOMMIT=0設(shè)置，然后通知開始交易START TRANSACTION，然后再進(jìn)行后面的操作，在所有的操作完成后，在進(jìn)行提交COMMIT，（注意只有一次交易事務(wù)）。 而自動(dòng)提交和前面的方式一樣。
對(duì)比測試數(shù)據(jù)為100000條。(不是我偷懶不測試大數(shù)據(jù)量，自動(dòng)提交太慢)
測試結(jié)果數(shù)據(jù)：
表10 AUTOCOMMIT測試100000條記錄
參數(shù)配置
插入100000條記錄耗時(shí)
平均
查詢100000條記錄耗時(shí)
平均
修改100000條記錄耗時(shí)
平均
刪除100000條記錄耗時(shí)
平均
SET AUTOCOMMIT=1
2500s
40/s
27s
3704/s
2503s
40
2506s
40/s
SET AUTOCOMMIT=0
17s
5882/s
21s
4761
16s
6250
16s
6250
結(jié)果數(shù)據(jù)差別就是這么大，自動(dòng)提交的性能真是慘不忍睹，所有的修改操作和非自動(dòng)提交都有200多倍的性能差距。
從上面的結(jié)果我們看出，自動(dòng)提交是一種不可接受的方案，但是如果使用非自動(dòng)提交有兩個(gè)限制，現(xiàn)有代碼要更改，對(duì)于CGI這樣的應(yīng)用，每次就是一個(gè)語句進(jìn)行修改，這樣仍然是1個(gè)會(huì)話1個(gè)事務(wù)，對(duì)于大規(guī)模的這樣使用效率仍然會(huì)造成低下。
正當(dāng)我對(duì)此無比疑惑的時(shí)候，zengyu老大告訴我他們修改一個(gè)默認(rèn)參數(shù)innodb_flush_log_at_trx_commit提高性能，查詢了這個(gè)參數(shù)的解釋如下：
表11 innodb_flush_log_at_trx_commit的取值說明
innodb_flush_log_at_trx_commit取值
說明
0
每秒寫1次日志，將數(shù)據(jù)刷入磁盤，相當(dāng)于每秒提交一次事務(wù)。
1
每次提交事務(wù)寫日志，同時(shí)將刷新相應(yīng)磁盤，默認(rèn)參數(shù)。
2
每提交事務(wù)寫一次日志，但每隔一秒刷新一次相應(yīng)的磁盤文件[注]
如果你對(duì)安全沒有苛刻要求，可以忍受極少量錯(cuò)誤，而且你的業(yè)務(wù)是大量小規(guī)模的交易，你可以使用innodb_flush_log_at_trx_commit =0來加快處理效率。
上面取值說明解釋完全抄于《MySQL權(quán)威指南》，其實(shí)我對(duì)這段話有點(diǎn)疑惑，到底最后刷新的是日志文件，還是日志文件和數(shù)據(jù)文件都刷新？從MySQL的參考手冊(cè)的英文看是日志文件(commit the log is flushed to disk, and the modifications made by the transaction become permanent)。InnoDB引擎應(yīng)該和大部分商用數(shù)據(jù)庫相同，先改寫日志，再改寫數(shù)據(jù)文件，所以可以保證在故障后快速恢復(fù)。
既然知道還有竅門，我們將innodb_flush_log_at_trx_commit=0后，再進(jìn)行一組測試。
表12 innodb_flush_log_at_trx_commit=0下的AUTOCOMMIT測試100000條記錄
參數(shù)配置
插入100000條記錄耗時(shí)
平均
查詢100000條記錄耗時(shí)
平均
修改100000條記錄耗時(shí)
平均
刪除100000條記錄耗時(shí)
平均
innodb_flush_log_at_trx_commit=0
SET AUTOCOMMIT=1
18s
5556/s
27s
3704/s
17s
5882/s
17s
5882/s
innodb_flush_log_at_trx_commit=0
SET AUTOCOMMIT=0
17s
6667/s
20s
5000/s
15s
5882/s
14s
7142/s
我們可以看出這樣，操作的效率可以大大提高，改寫操作的速度都提高了很多?？梢钥醋鲗?duì)于innodb_flush_log_at_trx_commit=0的設(shè)置下，InnoDB引擎自己控制提交的時(shí)機(jī)。
看來如果使用InnoDB，而且你的應(yīng)用又是大數(shù)量級(jí)小事務(wù)操作（我們公司的業(yè)務(wù)基本上都是），還是使用innodb_flush_log_at_trx_commit=0比較好，對(duì)于一次有大規(guī)模的操作最好還是自己控制事務(wù)[注]。
即使使用innodb_flush_log_at_trx_commit=0，你只要在會(huì)話中使用SET AUTOCOMMIT=0標(biāo)示不使用提交，你仍然可以使用START TRANSACTION 和COMMIT保證事務(wù)性。（事務(wù)性對(duì)于數(shù)據(jù)庫和網(wǎng)絡(luò)分布設(shè)計(jì)中的重要性是無需多言）
4.2.2 InnoDB和MyISAM的性能比較
本是同根生，相煎何太急。J
仍然采用大規(guī)模的數(shù)據(jù)的方式進(jìn)行測試。測試環(huán)境為4.0,數(shù)據(jù)仍然保留Cache和二進(jìn)制日志。
表13 MyISAM和InnoDB的性能比較
比較項(xiàng)目
MySQL4.0 MyISAM引擎
InnoDB引擎
innodb_flush_log_at_trx_commit=1
使用AUTOCOMMIT=0
InnoDB引擎
innodb_flush_log_at_trx_commit=0
(默認(rèn)使用AUTOCOMMIT=1)
耗時(shí)(s)
處理速度（條/s）
耗時(shí)(s)
處理速度（條/s）
耗時(shí)(s)
處理速度（條/s）
插入1000000條記錄
169
5917.16
162
6172.84
176
5681.82
查詢1000000條記錄
282
3546.10
195
5128.21
277
3610.11
改寫1000000條記錄
165
6060.61
156
6410.26
182
5494.51
刪除1000000條記錄
172
5813.95
138
7246.38
164
6097.56
插入5000000條記錄
876
5707.76
869
5753.74
937
5336.18
查詢5000000條記錄
1472
3396.74
987
5065.86
1413
3538.57
改寫5000000條記錄
816
6127.45
778
6426.74
937
5336.18
刪除5000000條記錄
958
5219.21
710
7042.25
863
5793.74
插入10000000條記錄
1758
5688.28
1805
5540.17
2107
4746.08
查詢10000000條記錄
3001
3332.22
1994
5015.05
2879
3473.43
改寫10000000條記錄
1844
5422.99
1588
6297.23
1858
5382.13
刪除10000000條記錄
1908
5241.09
1453
6882.31
1732
5773.67
從測試數(shù)據(jù)可以看出，MyISAM引擎和InnoDB在性能上基本沒有太大的區(qū)別，半斤八兩。AUTOCOMMIT=0的方式有較好的性能，但其對(duì)WEB的應(yīng)用不是太實(shí)用（事務(wù)一多性能還是要急劇下降），我們重點(diǎn)比較InnoDB在innodb_flush_log_at_trx_commit=0下和MyISAM引擎的性能。
由于MyISAM的特性而且測試用例是順序插入, MyISAM引擎占了些便宜。在MyISAM數(shù)據(jù)文件沒有空洞時(shí)(刪除記錄會(huì)產(chǎn)生一個(gè)沒有使用的記錄區(qū))，插入記錄操作沒有進(jìn)行鎖表操作（應(yīng)該說是寫鎖），（因?yàn)樗械挠涗浂际遣迦胛募┪玻?。由于我的測試正好屬于這種情況，在測試中MyISAM引擎在INSERT語句上表現(xiàn)優(yōu)秀，但是對(duì)于實(shí)際的環(huán)境，這個(gè)優(yōu)勢估計(jì)不會(huì)存在。
修改部分MyISAM引擎較好這不能反映真實(shí)情況，InnoDB的紀(jì)錄鎖的優(yōu)勢無法體現(xiàn)。[注]
InnoDB采用記錄鎖同步操作，MyISAM采用的是表鎖。
MyIsam的的客戶在訪問數(shù)據(jù)前，必須得到相應(yīng)的鎖，而且同時(shí)寫操作的優(yōu)先級(jí)高于讀取操作，可以這樣理解，MyIsam引擎對(duì)于表的查詢操作有兩個(gè)鎖隊(duì)列，一個(gè)是讀取鎖隊(duì)列，一個(gè)是寫入鎖隊(duì)列，MyIsam引擎總是優(yōu)先處理寫入鎖隊(duì)列等待的請(qǐng)求。而一個(gè)寫入鎖將阻塞后面寫入和讀取操作，而讀取鎖操作只阻塞所有的寫操作。
而InnoDB的引擎對(duì)于讀取操作幾乎不加鎖，如果此記錄正在被寫，才會(huì)阻塞此記錄相關(guān)的讀取操作和寫入操作。
一般認(rèn)為MyISAM引擎利于檢索，因?yàn)椴樵儾僮魇褂米x取鎖可以并發(fā)，InnoDB的引擎在大量更改的更改操作環(huán)境有更好的表現(xiàn)。
比較圖表如下：
圖5 InnoDB和MyISAM的性能比較
必須要指明的是，這個(gè)測試其實(shí)是比較利于MyISAM 引擎的，由于MyISAM的引擎的設(shè)計(jì)是1個(gè)表1個(gè)文件的，所以在真實(shí)環(huán)境，數(shù)據(jù)文件內(nèi)部必然有記錄碎片，（定期使用交互命令可以優(yōu)化這個(gè)問題），而這些碎片會(huì)MyISAM的降低處理性能，而且MyISAM采用文件鎖處理模式，不利于真實(shí)環(huán)境下的并發(fā)操作，后面的一些模擬測試也證明了這個(gè)問題。
4.3 總結(jié)
InnoDB和MyIASM引擎誰優(yōu)誰劣其實(shí)是一個(gè)很難衡量的問題，我把我知道的兩者的情況都拿出來讓你自己對(duì)比一下。
表14 MyISAM引擎和InnoDB引擎的對(duì)比
MyISAM
InnoDB
性能
也不錯(cuò)，
在AUTO COMMIT=0的情況下好于MyISAM，
支持事務(wù)
不支持
支持
支持外鍵
不支持
支持
可移植
理論可以
理論可以
表，索引大小是否有限制
有
無
鎖類型
數(shù)據(jù)表的讀寫鎖
記錄鎖定
是否存在死鎖的可能
幾乎無
存在
FULLTEXT索引
支持
不支持（路標(biāo)版本有規(guī)劃）
備份恢復(fù)
支持mysqldump備份為SQL語句
也支持mysqlhotcopy完全備份
支持mysqldump備份
完全熱備份要依靠innodb的付費(fèi)備份工具
維護(hù)
簡單
較MyISAM復(fù)雜
故障率
在大數(shù)訪問量下容易出現(xiàn)故障，但是提供了修復(fù)工具[注]
故障率低，一般的故障可以自動(dòng)恢復(fù)
但如果恢復(fù)后仍然有故障修復(fù)比較麻煩，
文件碎片
在刪除后存在碎片，但是可以通過命令進(jìn)行
文件內(nèi)部分片，也有碎片，但是影響較小。
已知缺陷
在很多數(shù)據(jù)的基本特性上沒有提供支持
大壓力環(huán)境下故障率較高
Select count(*) 慢
Truncate慢
不支持AUTO_INCREA初始化數(shù)值
innodb 可能存在一個(gè)限制， innodb引擎中使用的內(nèi)存總和不能超過2G,否則會(huì)宕機(jī)。根據(jù)《MySQL Reference Manual》中的說明是由于glic引發(fā)的問題。對(duì)于這個(gè)問題我比較疑惑，實(shí)際測試的結(jié)果沒有出現(xiàn)類似問題，而且感覺按照文檔的描述問題原因,，mysqlisam也應(yīng)該有類似的缺陷，但是從未見過說明？
個(gè)人感覺在大訪問量的情況下，InnoDB引擎還是一個(gè)更好的選擇。
MyISAM引擎的故障主要就是索引錯(cuò)誤（數(shù)據(jù)文件和索引不一致），其實(shí)出現(xiàn)這個(gè)錯(cuò)誤，主要是由于操作的不當(dāng)造成的：
(1)在聯(lián)機(jī)狀態(tài)kill的mysqld。
(2)不鎖表，在mysqld運(yùn)行的時(shí)候拷貝，讀寫,備份數(shù)據(jù)文件，(很多備份數(shù)據(jù)文件采用直接的拷貝方式，這樣不僅得不到可用的備份文件，還會(huì)破壞現(xiàn)有的數(shù)據(jù)文件)
(3)不鎖表，在mysqld運(yùn)行的時(shí)候，使用外部程序影響數(shù)據(jù)文件，比如用myisamchk在聯(lián)機(jī)狀態(tài)下修復(fù)。（mysqlcheck可以用于聯(lián)機(jī)修復(fù)，一直錯(cuò)誤認(rèn)為mysqlcheck 也有問題，謝謝 owenzhuang指明。）
在聯(lián)機(jī)狀態(tài)下對(duì)數(shù)據(jù)表的外部操作必須按照LOCK TABLE，F(xiàn)LUSH TABLE，操作，UNLOCK TABLE的方式進(jìn)行。
對(duì)于損壞的表，MySQL 提供了幾種修復(fù)方法，一種是脫機(jī)使用myisamchk 進(jìn)行修復(fù)，一種是聯(lián)機(jī)使用交互命令CHECK TABLE 和 REPAIR TABLE 進(jìn)行修復(fù)。另外，MyISAM引擎也有自我恢復(fù)功能。
InnoDB現(xiàn)在已經(jīng)被Oracle收購，作為了Oracle的一個(gè)開源項(xiàng)目，一方面我期待有新的強(qiáng)大技術(shù)背景的公司大的支持下，InnoDB的性能能得到更大的提高的同時(shí)，另一方面，我倒要為它的未來捏把汗。（
《Oracle收購InnoDB對(duì)MySQL的影響》一文對(duì)此有一些討論）
5 模擬真實(shí)環(huán)境下的性能
再次強(qiáng)調(diào)的是，前面的測試數(shù)據(jù)都是性能測試，不能作為實(shí)際環(huán)境的性能數(shù)據(jù)，這些數(shù)據(jù)表現(xiàn)的應(yīng)該是理想環(huán)境下的極限性能，其只能作為參考。
既然測試用例不完整，我們就考慮模擬一下真實(shí)的環(huán)境。
5.1 壓力測試
先準(zhǔn)備檢測一下MySQL在壓力情況下的性能變化情況，測試的版本為MysQL4.0 gcc靜態(tài)版本，使用的配置參數(shù)和原來相同。
我先在數(shù)據(jù)庫中插入10000000條記錄，然后我在另外一臺(tái)機(jī)器機(jī)器上啟動(dòng)1組壓力進(jìn)程對(duì)數(shù)據(jù)庫進(jìn)行查詢或者修改操作，測試的進(jìn)程組包括10，50，100個(gè)讀的進(jìn)程，10，50，100個(gè)讀寫進(jìn)程（讀寫各一半），這些進(jìn)程的操作間隔時(shí)間為10000微秒（0.01秒執(zhí)行一次）或者1000微妙（0.001秒執(zhí)行一次）。其中0.01秒執(zhí)行一次間隔的壓力相對(duì)較輕，0.001秒執(zhí)行一次間隔壓力就比較重了，一般用mysqladmin processlist檢查，大部分進(jìn)程的狀態(tài)都是執(zhí)行狀態(tài)。
同時(shí)在這臺(tái)機(jī)機(jī)器（本機(jī)上）測試查詢1000000條記錄（總記錄數(shù)的1/10）所耗費(fèi)的時(shí)間。同時(shí)為了比較，使用vmstat記錄相關(guān)的CPU占用率的情況。分別記錄壓力進(jìn)程運(yùn)行后，測試進(jìn)程運(yùn)行前后的CPU空閑率。
MyISAM 引擎的測試結(jié)果如下：
表15 MyISAM引擎的壓力測試
測試項(xiàng)目
壓力進(jìn)程的操作間隔為0.01秒
壓力進(jìn)程的操作間隔為0.001秒
運(yùn)行前CPU空閑(%)
運(yùn)行后CPU空閑(%)
耗時(shí)(s)
處理速度(條記錄/s)
運(yùn)行前CPU空閑(%)
運(yùn)行前CPU空閑(%)
耗時(shí)(s)
處理速度(條記錄/s)
沒有壓力進(jìn)程
100
69
258
3875.97
100
69
258
3875.97
10個(gè)讀取壓力進(jìn)程
97
69
282
3546.10
85
57
318
4048.58
50個(gè)讀取壓力進(jìn)程
88
59
316
3164.56
35
21
700
3333.33
100個(gè)讀取壓力進(jìn)程
73
45
379
2638.52
22
10
2093
2283.11
10個(gè)讀寫壓力進(jìn)程(5讀+5寫)
94
67
247
3144.65
69
51
361
2770.08
50個(gè)讀寫壓力進(jìn)程(25讀+25寫)
80
54
300
1428.57
25
16
4206
237.76
100個(gè)讀寫壓力進(jìn)程(50讀+50寫)
59
37
438
477.78
27
25
大約需要200000
0.50
對(duì)于MyISAM引擎，最后一組測試我沒能作完，實(shí)在是太慢了，在6個(gè)小時(shí)后我檢查發(fā)現(xiàn)查詢才進(jìn)行到12477，我?guī)缀醣罎?，估?jì)要讓他跑完要5-6天，只好作罷。
比較的圖表：
圖6 MyISAM引擎的壓力測試
通過比較，我們可以看出，在只有讀取操作的壓力下，MyISAM引擎表現(xiàn)還不錯(cuò)，在壓力增加的情況下性能沒有出現(xiàn)陡降。而對(duì)于讀寫的混合壓力測試情況下，MyISAM引擎的表現(xiàn)急轉(zhuǎn)直下，特別是最后的100個(gè)讀寫0.001s間隔壓力進(jìn)程測試情況下，速度已經(jīng)不可接受。這是檢查MySQL的狀態(tài)計(jì)數(shù)變量Table_locks_waited數(shù)值非常大，而且遠(yuǎn)遠(yuǎn)大于Table_locks_immediate。這表示系統(tǒng)操作中，等待鎖的操作非常多。
由于MyISAM引擎采用表鎖，所以在讀寫壓力測試下，由于寫操作鎖會(huì)阻塞所有的讀取鎖，而且優(yōu)先基本大于讀取鎖，所以此時(shí)讀取操作必須等待。而且由于最后的壓力測試寫操作頻度很高，所以造成了幾乎1秒1條的讀取速度。而且值得注意的是此時(shí)CPU性能壓力并不大，系統(tǒng)的能力并沒有得以發(fā)揮。
為了橫向比較MyISAM引擎和InnoDB引擎在不同壓力的表現(xiàn)，我也測試了InnoDB在壓力情況下的表現(xiàn)。
表16 InnoDB引擎的壓力測試
測試項(xiàng)目
壓力進(jìn)程的操作間隔為0.01秒
壓力進(jìn)程的操作間隔為0.001秒
運(yùn)行前CPU空閑(%)
運(yùn)行后CPU空閑(%)
耗時(shí)(s)
處理速度(條記錄/s)
運(yùn)行前CPU空閑(%)
運(yùn)行前CPU空閑(%)
耗時(shí)(s)
處理速度(條記錄/s)
沒有壓力進(jìn)程
100
69
278
3597.12
100
69
278
3597.12
10個(gè)讀取壓力進(jìn)程
97
68
281
3558.72
94
65
387
4504.50
50個(gè)讀取壓力進(jìn)程
86
58
312
3205.13
76
48
634
3690.04
100個(gè)讀取壓力進(jìn)程
73
46
326
2583.98
54
28
892
2832.86
10個(gè)讀寫壓力進(jìn)程(5讀+5寫)
83
55
222
3067.48
72
45
276
3623.19
50個(gè)讀寫壓力進(jìn)程(25讀+25寫)
40
20
271
1577.29
4
2
1243
804.51
100個(gè)讀寫壓力進(jìn)程(50讀+50寫)
24
9
353
1121.08
3
1
4011
249.31
比較的圖表：
圖7 InnoDB引擎的壓力測試
可以看出InnoDB引擎在壓力測試面前表現(xiàn)得不錯(cuò)，基本上是隨著壓力的增加，速度呈現(xiàn)線性的下降（注意第2步測試的進(jìn)程是50個(gè)為第一步的5倍）。
為了方便比較，我也給出InnoDB和MySQL的兩個(gè)比較圖表，必須指出的是，這樣的比較意義有一定的局限性。因?yàn)椴⒉荒鼙ＷC每個(gè)連接對(duì)于兩種引擎的壓力都一致。
MyISAM和InnoDB的讀取壓力測試比較
圖9 MyISAM和InnoDB的讀寫壓力測試比較
從對(duì)比圖表可以看出[注]，InnoDB引擎可以更好的承擔(dān)查詢壓力。
MyISAM 為了避免在大壓力的環(huán)境下性能下降，可能必須將表分割的更細(xì)。
如果你夠心細(xì)應(yīng)該可以發(fā)現(xiàn)在加入0.01秒讀寫壓力測試后，得到的性能反而提升了。？而且MyISAM引擎和InnoDB引擎的數(shù)據(jù)都有提升？這個(gè)現(xiàn)象非常有趣。為了驗(yàn)證不是干擾，我重新測試了一次InnoDB在這個(gè)情況下的表現(xiàn)，發(fā)現(xiàn)測試結(jié)果沒有錯(cuò)誤。我猜測這還是和查詢的Cache有關(guān)，InnoDB將查詢結(jié)果放入Cache可能是在提交操作時(shí)，而在在提交時(shí)發(fā)現(xiàn)同時(shí)有更改語句的情況下，InnoDB引擎沒有將查詢語句的結(jié)果放入Cache。由于減少了Cache的成本，而且相對(duì)而言此時(shí)的更改操作壓力很輕，所以這樣反而加快了查詢的速度。
5.2 多表訪問環(huán)境模擬
前面測試的方式都是使用單張表進(jìn)行測試，在多張表的情況下，MySQL的表現(xiàn)如何？我打算這樣模擬，采用10張表，每張表存放5000000條記錄，在另外一臺(tái)機(jī)器上，每個(gè)進(jìn)程都查詢?cè)L問一個(gè)表，查詢頻率為0.005秒一次，查詢記錄為隨機(jī)數(shù)選取，同時(shí)在本機(jī)上進(jìn)行插入，查詢，更改，刪除1000000條記錄的操作。
對(duì)于MyISAM引擎，同時(shí)由于增加了表的數(shù)量，調(diào)整了一些相關(guān)的參數(shù)，
set-variable = key_buffer_size=512M
set-variable = query_cache_size=128M
對(duì)于InnoDB引擎，仍然使用原來的配置，同時(shí)由于測試機(jī)器發(fā)生了更改[注]，為了對(duì)比的公正性，我重新測試了沒有壓力（沒有程序同時(shí)訪問）情況下的對(duì)比數(shù)據(jù)。
表17 多表訪問的環(huán)境模擬
參數(shù)配置
插入100000條記錄耗時(shí)
處理速度(條記錄/s)
查詢100000條記錄耗時(shí)
處理速度(條記錄/s)
修改100000條記錄耗時(shí)
處理速度(條記錄/s)
刪除100000條記錄耗時(shí)
處理速度(條記錄/s)
MyISAM引擎,
197
5076.14
333
3003.00
173
5780.35
175
5714.29
MyISAM引擎,模擬多表環(huán)境
209
4784.69
3366
297.09
566
1766.78
507
1972.39
InnoDB引擎,
221
4524.89
425
2352.94
255
3921.57
234
4273.50
InnoDB引擎, 模擬多表環(huán)境
520
1923.08
1853
539.67
677
1477.10
629
1589.83
對(duì)比圖表為
圖10 模擬多表訪問的測試
在非模擬情況下，MyISAM 引擎有少量優(yōu)勢，但是在模擬環(huán)境下查詢記錄情況,MyISAM引擎的表現(xiàn)遠(yuǎn)慢于InnoDB的表現(xiàn)，對(duì)于這個(gè)我感覺有點(diǎn)奇怪，因?yàn)榇藭r(shí)不應(yīng)該有鎖排隊(duì)的情況發(fā)生。？
在壓力情況下，MyISAM引擎的插入語句性能看似不錯(cuò)，但是這是由于在我的測試用例中，MyISAM引擎不用加入寫鎖鎖定表。（見前InnoDB和MyISAM引擎的對(duì)比測試章節(jié)）
由于所有的操作都是順序操作，而且沒有加入寫操作，感覺測試比較傾向于MyISAM引擎[注]。
測試的機(jī)器也是為380G3，但是內(nèi)核為2.4.21，初步看來2.6內(nèi)核的性能更好一些。
對(duì)這個(gè)模擬感覺不是太理想，模擬正式環(huán)境和測試極限速度這兩者可能就有矛盾。
5.3 網(wǎng)絡(luò)訪問速度
前面的測試基本都是基于本機(jī)操作訪問數(shù)據(jù)庫的操作，但是更多的應(yīng)用是基于網(wǎng)絡(luò)訪問。所以也業(yè)考慮測試一下網(wǎng)絡(luò)環(huán)境下MySQL訪問速度。
基于網(wǎng)絡(luò)的訪問分為2種，一種是利用長鏈接，一種是短鏈接，就是每次訪問都重新進(jìn)行一次鏈接。
測試環(huán)境，數(shù)據(jù)庫使用MyISAM引擎，使用Cache。網(wǎng)絡(luò)帶寬為100Mbps。
由于對(duì)于1個(gè)服務(wù)器，可以循環(huán)使用的SOCKET端口是有限，所以我只執(zhí)行了20000次操作進(jìn)行比較，由于測試的數(shù)據(jù)較少，對(duì)于這些數(shù)據(jù)比較差距就可以了。
比較項(xiàng)目
本地操作
網(wǎng)絡(luò)操作
使用長鏈接
網(wǎng)絡(luò)操作
使用短鏈接
網(wǎng)絡(luò)操作(STMT)
使用長鏈接
耗時(shí)(s)
處理速度（條/s）
耗時(shí)(s)
處理速度（條/s）
耗時(shí)(s)
處理速度（條/s）
耗時(shí)(s)
處理速度（條/s）
插入20000條記錄
4
5000.00
13
1538.46
27
740.74
8
2500.00
查詢20000條記錄
4
5000.00
21
952.38
34
588.24
15
1333.33
改寫20000條記錄
4
5000.00
13
1538.46
29
689.66
8
2500.00
刪除20000條記錄
3
6666.67
13
1538.46
36
555.56
7
2857.14
比較圖表如下：
圖11 網(wǎng)絡(luò)環(huán)境性能下的測試
對(duì)于網(wǎng)絡(luò)的訪問，網(wǎng)絡(luò)長鏈接的訪問速度是本地執(zhí)行速度的3-4倍（降低了60%），網(wǎng)絡(luò)短鏈接的訪問速度是本地執(zhí)行速度的7-8倍（降低了85%）。[注]
注意這個(gè)速度只是單鏈接下的極限速度，所以這并不代表MySQL在網(wǎng)絡(luò)環(huán)境下的處理速度（但個(gè)人認(rèn)為極限速度不可能超過2倍），但是我們還是看出，MySQL在網(wǎng)絡(luò)環(huán)境下速度有相當(dāng)?shù)南陆礫注]。而使用網(wǎng)絡(luò)短鏈接要付出連接和斷開連接的成本。
STMT的操作方式（參看STMT的章節(jié)）由于不用重新傳遞分析SQL，在網(wǎng)絡(luò)環(huán)境也表現(xiàn)了出了優(yōu)于其他方式的性能。
由于測試的數(shù)據(jù)較少，可能影響到測試的時(shí)間準(zhǔn)確程度。
對(duì)于長鏈接的網(wǎng)絡(luò)訪問速度有如此的下降，我開始很懷疑，我查詢了我原來對(duì)Oracle作過的一組入庫測試數(shù)據(jù)，在同樣的100M網(wǎng)絡(luò)環(huán)境環(huán)境下速度只降低了30%左右。但我重新測試了1000000條的數(shù)據(jù)，但得到的結(jié)果還是如此。（當(dāng)然也可能是IDC內(nèi)部的網(wǎng)絡(luò)過于繁忙）
基于MySQL在網(wǎng)絡(luò)環(huán)境中的表現(xiàn)差強(qiáng)人意，如果硬件條件允許，在作DB服務(wù)器設(shè)計(jì)時(shí)，將DB服務(wù)器和MySQL放在同一個(gè)機(jī)器下可能是一個(gè)更好的選擇。
5.4 小結(jié)
如何模擬真實(shí)的環(huán)境一直是我比較頭大的問題，不管我如何模擬，好像感覺仍然只能專注到某個(gè)方面從而有相對(duì)性，而且模擬現(xiàn)實(shí)和測試極限性能本身可能就相互矛盾。這些測試數(shù)據(jù)更多的應(yīng)該是一種參考，使你了解系統(tǒng)地性能極限和性能瓶頸[注]。如果你能有更好的方法模擬真實(shí)環(huán)境，請(qǐng)通知我。
通過前面的測試，個(gè)人感覺對(duì)于類似我測試環(huán)境的系統(tǒng)（機(jī)器+DB+表），個(gè)人感覺真實(shí)環(huán)境的最高本地處理速度可以接近2500條左右（包括讀寫），網(wǎng)絡(luò)操作的最高速度可以接近1500條。
6 STMT的測試
MySQL的API為什么在網(wǎng)絡(luò)環(huán)境下表現(xiàn)較差？一方面由于MySQL的API全部直接使用SQL語句執(zhí)行，SQL的分析成本是比較大的，同時(shí)每次都要傳遞SQL也占用帶寬，一方面由于MySQL的結(jié)果是采用字符串的方式傳遞的，也是一個(gè)比較耗費(fèi)資源的事情。為了解決這些不足，MySQL推出了一組新的API STMT。(我到現(xiàn)在沒有搞明白是什么的縮寫？)
STMT是MySQL 4.1版本的一個(gè)重要的噱頭，其提供一種和以往的API不同的形式訪問數(shù)據(jù)庫。其類似Oracle等提供的綁定變量的API方式。
這樣的方式基本方法是使用一個(gè)綁定參數(shù)的SQL，這個(gè)SQL在使用前先要進(jìn)行分析準(zhǔn)備（mysql_stmt_prepare），在執(zhí)行前還必須，為輸入?yún)?shù)綁定變量（mysql_stmt_bind_param）比如查詢參數(shù)，插入語句參數(shù)，為輸出參數(shù)綁定結(jié)果（mysql_stmt_bind_result），比如查詢語句。下次在進(jìn)行插入和更新操作時(shí)就可以直接修改相關(guān)的參數(shù)，由于SQL語句已經(jīng)經(jīng)過分析，再次處理的速度可以得到大大的提高。
STMT的API基本是由自己獨(dú)立的一套，如mysql_stmt_execute，mysql_stmt_errno
STMT的重要優(yōu)勢是避免了語法分析SQL的成本，同時(shí)由于STMT語句在次執(zhí)行的時(shí)候無需傳輸大量的SQL語句數(shù)據(jù)，可以大大減少網(wǎng)絡(luò)流量，在網(wǎng)絡(luò)環(huán)境下也更有優(yōu)勢。
為了進(jìn)行橫向的比較，我將測試分成兩種方法，一種是使用每次都使用Prepare語句的測試，一種是只在第一次使用Prepare語句，后面的操作只更新邦定變量。查詢的操作都完成轉(zhuǎn)儲(chǔ)結(jié)果集。
表18 STMT的性能測試
MySQL4.1使用傳統(tǒng)API的
MySQL4.1使用STMT API,每次都使用Prepare
MySQL4.1使用STMT API,只有第一次都使用Prepare
耗時(shí)(s)
處理速度（條/s）
耗時(shí)(s)
處理速度（條/s）
耗時(shí)(s)
處理速度（條/s）
插入1000000條記錄
176
5681.82
334
2994.01
123
8130.08
查詢1000000條記錄
306
3267.97
406
2463.05
170
5882.35
改寫1000000條記錄
174
5747.13
279
3584.23
117
8547.01
刪除1000000條記錄
179
5586.59
257
3891.05
130
7692.31
插入5000000條記錄
898
5567.93
1689
2960.33
653
7656.97
查詢5000000條記錄
1595
3134.80
2020
2475.25
863
5793.74
改寫5000000條記錄
859
5820.72
1430
3496.50
677
7385.52
刪除5000000條記錄
976
5122.95
1380
3623.19
798
6265.66
插入10000000條記錄
1815
5509.64
3423
2921.41
1360
7352.94
查詢10000000條記錄
3257
3070.31
4065
2460.02
1742
5740.53
改寫10000000條記錄
1904
5252.10
2969
3368.14
1482
6747.64
刪除10000000條記錄
1982
5045.41
2794
3579.10
1693
5906.67
相關(guān)數(shù)據(jù)的比較圖表如下。
STMT API性能測試
從上面看出，如果每個(gè)語句都使用Prepare，居然還慢了好多?？磥磉@種STMT模式并不適合SQL語句要反復(fù)重新更換的環(huán)境。否則Prepare的成本反而更加突出。（只能執(zhí)行一次CGI的環(huán)境就不要用這個(gè)東西了。）
而只在第一次執(zhí)行前使用Prepare的測試結(jié)果令人驚喜，普遍的速度提高在30%左右，這個(gè)速度的提升是非常鼓舞人心的。所以看來STMT的API非常適合數(shù)據(jù)庫服務(wù)器SERVER類的開發(fā)。
另外要補(bǔ)充說明的是STMT的語句不使用查詢Cache。
7 MySQL的優(yōu)化
熟悉Oracle優(yōu)化的人都知道Oracle的重要優(yōu)化對(duì)象就是SGA區(qū),其實(shí)MySQL也是一樣的.MySQL的參數(shù)配置在my.cnf文件中。[注]。
mysql的配置大部分在my.cnf中，參數(shù)和SHOW VARIABLES顯示的變量一樣名稱，但是有些卻不是，有些是在啟動(dòng)參數(shù)中設(shè)置，有些是必須用SET語句更改。而且多個(gè)版本中有些參數(shù)名稱還不一樣。有時(shí)候搞得人也挺頭大的。
7.1 配置參數(shù)
MySQL有兩種途徑途徑了解其的配置參數(shù)，一個(gè)是MySQL交互模式下的命令SHOW VARIABLES，一個(gè)使用mysqladmin variables 查詢。
MySQL的配置參數(shù)分為2種，全局的和局部的。局部的配置變量可以在每次會(huì)話中自己更改。
從MySQL 4.0以后開始，在SHOW VARIABLES中顯示的參數(shù)，大部分可以動(dòng)態(tài)使用SET命令進(jìn)行更改。
基本參數(shù)配置：
參數(shù)
說明
bind-address
綁定的IP地址
user
用戶
port
端口號(hào)
datadir
數(shù)據(jù)文件目錄
basedir
msyql應(yīng)用程序的目錄
socket
socket文件，默認(rèn)在/tmp目錄下，但是建議不要這樣設(shè)置，/tmp目錄是一個(gè)大家都愿意破壞的目錄
default-table-type
默認(rèn)表類型
查詢的Cache的是從MySQL4.0版本開始提供的功能。相關(guān)的參數(shù)為：
參數(shù)
說明
query_cache_size
查詢Cache的尺寸
query_cache_type
查詢的Cache類型。
0 OFF，不進(jìn)行緩沖
1 ON，進(jìn)行緩沖
2 DEMAND，對(duì)SELECT SQL_CACHE開頭的查詢進(jìn)行緩沖
query_cache_limit
查詢的結(jié)果的限制長度，小于這個(gè)長度的數(shù)據(jù)才能Cache
MyISAM的索引參數(shù)：key_buffer_size為MyISAM引擎的最關(guān)鍵的優(yōu)化參數(shù)之一。
參數(shù)
說明
key_buffer_size
(關(guān)鍵參數(shù)),索引塊用的緩沖區(qū)大小，所有的連接程序線程共用
key_cache_block_size
每一個(gè)索引block的大小，默認(rèn)1024字節(jié),從4.1.1后才出現(xiàn)這個(gè)參數(shù)，原來都是直接采用1024字節(jié)作為Block的長度
InnoDB使用的參數(shù)：InnoDB的參數(shù)較少，籠統(tǒng)而不細(xì)致，內(nèi)存的管理多由InnoDB引擎自己負(fù)責(zé)，主要的緩沖就是innodb_buffer_pool_size參數(shù)分配的緩沖。這樣配置倒是簡單了，但沒有了細(xì)致優(yōu)化樂趣。
參數(shù)
說明
innodb_buffer_pool_size
innodb的緩沖區(qū)大小，存放數(shù)據(jù)和索引,一般設(shè)置為機(jī)器內(nèi)存的50%-80%
(關(guān)鍵參數(shù))
innodb_log_buffer_size
InnoDB日志緩沖區(qū)大小
innodb_flush_method
刷新日志的方法
innodb_additional_mem_pool_size
innodb內(nèi)存池的大小，存放著各種內(nèi)部使用的數(shù)據(jù)結(jié)構(gòu)
innodb_data_home_dir
InnoDB數(shù)據(jù)文件的目錄
innodb_data_file_path
數(shù)據(jù)文件配置
innodb_log_files_in_group
Innodb日志的
innodb_log_file_size
Innodb日志文件的尺寸
innodb_lock_wait_timeout
等待數(shù)據(jù)鎖的超時(shí)時(shí)間，避免死鎖的一種措施
innodb_flush_log_at_trx_commit
日志提交方式 (關(guān)鍵參數(shù))
0每秒寫1次日志，將數(shù)據(jù)刷入磁盤，相當(dāng)于每秒提交一次事務(wù)。
1每次提交事務(wù)寫日志，同時(shí)將刷新相應(yīng)磁盤，默認(rèn)參數(shù)。
2每提交事務(wù)寫一次日志，但每隔一秒刷新一次相應(yīng)的磁盤文件[注]
innodb_force_recovery
在Innodb的自動(dòng)恢復(fù)失敗后，從崩潰中強(qiáng)制啟動(dòng)，有1-6個(gè)級(jí)別，數(shù)值越低恢復(fù)的方式也保守，默認(rèn)為4。盡量使用較保守方式恢復(fù)。
恢復(fù)后要注釋刪除這一行。
Log的參數(shù)：MySQL的日志有6種，查詢?nèi)罩荆樵內(nèi)罩?，變更日志，二進(jìn)制變更日志，告警日志，錯(cuò)誤日志。my.cnf中可以配置日志的前綴和日志參數(shù)。日志是監(jiān)控?cái)?shù)據(jù)庫系統(tǒng)的重要途徑。
參數(shù)
說明
log
查詢?nèi)罩?，記錄所有的MySQL的命令操作，在跟蹤數(shù)據(jù)庫運(yùn)行時(shí)非常有幫助，但在實(shí)際環(huán)境中就不要使用了
log-update
變更日志，用文本方式記錄所有改變數(shù)據(jù)的變更操作，
log-bin
二進(jìn)制變更日志，更加緊湊，使用mysqlbinlog讀取，操作，轉(zhuǎn)換
binlog_cache_size
臨時(shí)存放某次事務(wù)的SQL語句緩沖長度
max_binlog_cache_szie
最大的二進(jìn)制Cache日志緩沖區(qū)尺寸
max_binlog_size
最大的二進(jìn)制日志尺寸
log-error
導(dǎo)致無法啟動(dòng)的錯(cuò)誤日志
log-warnings
告警日志
long_query_time
慢查詢時(shí)間限度，超過這個(gè)限度，mysqld認(rèn)為是一個(gè)慢查詢
log-queries-not-using-indexes
沒有使用索引查詢的日志,方便記錄長時(shí)間訪問的查詢進(jìn)行優(yōu)化
log-slow-queries
慢速的查詢?nèi)罩荆?div style="height:15px;">
mysqld保留的文件描述符號(hào)個(gè)數(shù)，和table_cache和max_connections設(shè)置相關(guān)，默認(rèn)為0
設(shè)置為0, 系統(tǒng)設(shè)置max_connections*5或者max_connections + table_cache*2中的最大值
每個(gè)會(huì)話使用的buffer設(shè)置，默認(rèn)使用my.cnf的配置，也可以使用每個(gè)會(huì)話設(shè)置。不要設(shè)置的過大。
對(duì)于磁盤緩式寫入的一些選項(xiàng)，delay_key_write，flush，flush_time參數(shù)可能可以進(jìn)一步提高M(jìn)yISAM引擎的性能，但是在服務(wù)器Crash的時(shí)候，可能會(huì)丟失數(shù)據(jù)，造成表損壞。
MySQL對(duì)于插入語句支持一個(gè)選項(xiàng)INSERT DELAYED，如果有這個(gè)選項(xiàng)，MySQL將這些插入語句放入一個(gè)隊(duì)列，并不馬上讀入磁盤。delay_insert_XXX的選項(xiàng)都是配置這個(gè)功能，
MySQL創(chuàng)建表的時(shí)候也有一個(gè)選項(xiàng)，DELAY_KEY_WRITE，有這個(gè)選項(xiàng)描述的表的鍵發(fā)生改動(dòng)后，改動(dòng)可以緩沖在key_buffer中，不立即回寫磁盤。
在處理完INSERT DELAYED對(duì)列的插入數(shù)據(jù)后，MYSQL等待delay_insert_timeout秒后看看是否有INSERT DELAYED數(shù)據(jù)，如果有繼續(xù)，如果沒有結(jié)束這次操作。
關(guān)閉某些選項(xiàng)：關(guān)閉某些選項(xiàng)可以加快MySQL的運(yùn)行速度，這些選項(xiàng)在MySQL SHOW VARIABLES 中顯示為have_XXX 的變量。
不使用外部鎖,MySQL的外部鎖用于防止其他程序修改正在數(shù)據(jù)文件，但其在部分系統(tǒng)上不可靠，一般都不使用。(4.03版本前叫skip-locking)
MySQL有兩種途徑途徑了解其的運(yùn)行狀態(tài)，一個(gè)是MySQL交互模式下的命令SHOW STATUS，一個(gè)使用mysqladmin extended-status 。兩種方法異曲同工，通過觀察其運(yùn)行狀態(tài)可以了解我們的參數(shù)設(shè)置是否合理，是否有要優(yōu)化的表和數(shù)據(jù)。
SHOW STATUS顯示了MySQL從運(yùn)行開始到現(xiàn)在為止?fàn)顟B(tài)，大部分為一些計(jì)數(shù)器，使用FLUSH STATUS可以重新對(duì)各種狀態(tài)變量進(jìn)行計(jì)數(shù)。
因客戶沒有正確關(guān)閉而丟棄的連接數(shù)量，沒有正確關(guān)閉指沒有調(diào)用mysql_close就退出，連接超時(shí)，數(shù)據(jù)傳送中客戶端退出
使用磁盤創(chuàng)建臨時(shí)表的次數(shù)，如果要?jiǎng)?chuàng)建的臨時(shí)表的尺寸大于tmp_table_size，那么臨時(shí)表將創(chuàng)建在磁盤上，
根據(jù)索引讀取下個(gè)數(shù)據(jù)行的請(qǐng)求次數(shù). 在一個(gè)索引的區(qū)間內(nèi)進(jìn)行查詢( > < ,orderby 這類查詢條件)會(huì)影響這個(gè)計(jì)數(shù)器。
通過一個(gè)固定位置(應(yīng)該就是不通過索引)讀取一個(gè)數(shù)據(jù)行的次數(shù)。這個(gè)數(shù)值很高表示你的很多查詢操作的結(jié)果需要排序，可能這些查詢操作不能適當(dāng)使用索引而要檢索整個(gè)表。
請(qǐng)求從數(shù)據(jù)文件中讀取下一個(gè)記錄的次數(shù).如果有很多全表的檢索這個(gè)值將很高. 通常這表示數(shù)據(jù)表沒有合適的索引。
索引緩沖區(qū)塊中已經(jīng)被使用的區(qū)塊大小。Block的尺寸默認(rèn)是1024字節(jié)，4.1.1后可以通過key_cache_block_size參數(shù)設(shè)置?？梢愿鶕?jù)key_buffer_size/(1024 or key_cache_block_size) 得到Block總數(shù)，然后知道key_buffer的利用率
必須等待后才能完成表鎖定的請(qǐng)求個(gè)數(shù)，如果這個(gè)數(shù)值和下面數(shù)值的比率過大，表示數(shù)據(jù)庫的性能較低
InnoDB的狀態(tài)監(jiān)控的要在交互模式下使用show innodb status命令。相對(duì)的可以利用InnoDB狀態(tài)參數(shù)也過少。
了解了參數(shù)的含義，剩下的事情就是如何設(shè)置一個(gè)合理的MySQL參數(shù)了。下面我們結(jié)合幾個(gè)關(guān)鍵參數(shù)講解以下如何通過根據(jù)狀態(tài)了解參數(shù)是否設(shè)置合理。
比如MySQL的重要參數(shù)table_cache,如果設(shè)置過小，SHOW STATUS顯示的變量Opened_tables 會(huì)迅速增加，而正常狀態(tài)下應(yīng)該是保持穩(wěn)定或者緩慢增加。
又比如想要了解查詢Cache的狀態(tài)，可以查詢SHOW STATUS顯示的Qcache_XXX 變量。變量Com_select表示查詢語句的數(shù)量（實(shí)際放入Cache的語句數(shù)量是Qcache_inserts），Qcache_hits表示查詢?cè)贑ache中命中的數(shù)量，除一下就可以得到你的查詢Cache的命中率。是否值得使用查詢Cache，就很明確了。
Com_XXX相關(guān)的狀態(tài)計(jì)數(shù)是反映數(shù)據(jù)庫處理的SQL語句的。通過這寫計(jì)數(shù)器你可以了解你的數(shù)據(jù)庫什么操作更多，從而更好的優(yōu)化。
key_buffer_size對(duì)于MyISAM引擎是最重要的參數(shù)之一，其的使用情況可以通過key_XXX參數(shù)了解，key_blocks_used 狀態(tài)計(jì)數(shù)器可以讓你了解Key_buffer的使用情況，Block的尺寸默認(rèn)是1024字節(jié)，MySQL4.1.1版本后可以通過key_cache_block_size參數(shù)設(shè)置?？梢愿鶕?jù)key_buffer_size/(1024 or key_cache_block_size) 得到Block總數(shù)，然后知道key_buffer的利用率。
最大連接數(shù)max_connections 是否設(shè)置合理可以查詢SHOW STATUS的變量Connections，Connections變量為嘗試連接的數(shù)量，監(jiān)控其的增長速率就也可以知道客戶對(duì)連接數(shù)的需求程度。
檢查Table_locks_waited的數(shù)量以及和Table_locks_immediate的比率，可以了解查詢操作是否有過多的鎖等待，如果等待數(shù)量很大，建議你考慮更換引擎或者繼續(xù)分割你的表。
Thread_XXX相關(guān)的狀態(tài)參數(shù)可以告訴你MySQL的線程處理情況，Thead_running和Thread_connected的比率可以讓你知道大部分連接的狀態(tài)如何，是在SLEEP還是RUNNIG。
如果狀態(tài)中的Created_tmp_disk_tables和Created_tmp_tables的比率過大，表示大量的臨時(shí)表是在磁盤上創(chuàng)建的。這樣效率當(dāng)然是很低的，所以建議調(diào)整tmp_table_size的大小。
Slow_queries也是一個(gè)關(guān)鍵的計(jì)數(shù)器，MySQL將查詢時(shí)間超過long_query_time的查詢時(shí)為一個(gè)慢查詢，出現(xiàn)一個(gè)慢查詢就會(huì)將Slow_queries+1 。而long_query_time的默認(rèn)設(shè)置是10秒，這個(gè)時(shí)間是比較長的，你可以調(diào)整的更加短一些，同時(shí)MySQL提供了日志可以記錄慢查詢的查詢語句。my.cnf中可以配置log-queries-not-using-indexes參數(shù)記錄沒有使用索引查詢的日志,log-slow-queries參數(shù)記錄慢速的查詢?nèi)罩?，在?shù)據(jù)庫設(shè)計(jì)初期階段，通過這兩個(gè)日志可以了解數(shù)據(jù)表的設(shè)計(jì)是否合理。
kenix問過一個(gè)奇怪的問題，如果想最小的成本進(jìn)行優(yōu)化,如何進(jìn)行？對(duì)于這個(gè)問題我和sunbirdcui的答案一致，把硬盤改為RAID10（0+1）的。對(duì)于提高性能，升級(jí)硬件可能是最簡單最省力的方式，而且對(duì)于我們大部分的DB，在磁盤空間不夠前，機(jī)器的性能可能已經(jīng)遠(yuǎn)遠(yuǎn)跟不上服務(wù)增長而要分布了。所以建議DB服務(wù)器不要采用RAID 5而采用RAID 10，這樣可以輕松的提高20-30%的性能。網(wǎng)上能找到的相關(guān)比較的文章是
而對(duì)于內(nèi)存的使用方面，MySQL好像沒有過高要求，主要的除了每個(gè)連接使用的read_buffer_size 和sort_buffer_size尺寸的內(nèi)存外，全局有一個(gè)Qcache使用query_cache_size尺寸的cache。MyISAM引擎主要使用的是key_buffer_size尺寸的內(nèi)存,InnoDB主要使用內(nèi)存就是 innodb_buffer_pool_size 尺寸內(nèi)存。而且InnoDB可能還有一個(gè)2G內(nèi)存使用限制（是否真沒有這個(gè)問題有待驗(yàn)證）。所以依靠大規(guī)模增加內(nèi)存提高M(jìn)ySQL性能未必有效。MySQL自己在內(nèi)存管理可以提高的地方還很多。
其他的方面也還有很多，比如Linux的內(nèi)核優(yōu)化，2.6的內(nèi)核的更加傾向于I/O的應(yīng)用。大家也許記得我前面有臺(tái)測試機(jī)器使用的是380G3 2.4.21的內(nèi)核，一臺(tái)是380G3 2.6.8.1的內(nèi)核我們把它們相同測試的數(shù)據(jù)列出來。但請(qǐng)大家注意的是，由于不是同一臺(tái)機(jī)器（型號(hào)倒是一致），參考意義有待進(jìn)一步證實(shí)。
另外的一個(gè)非常重要的決定性能的因素就是你對(duì)數(shù)據(jù)表的設(shè)計(jì)了。這部內(nèi)容分包括數(shù)據(jù)表結(jié)構(gòu)的設(shè)計(jì)，范式和索引，以及MySQL本身的查詢優(yōu)化.但是它們不在本文的討論范疇之內(nèi)了。
終于，終于在2005年把手上的測試數(shù)據(jù)整理為文檔了。其實(shí)手上最早的測試記錄已經(jīng)是今年6月的了。畢業(yè)后工作之外幾乎就沒有寫過5頁以上的文檔，這個(gè)文檔幾乎又是半途而廢。激勵(lì)自己完成這個(gè)稿子的事情很偶然，一次培訓(xùn)，liddlechen介紹說MySQL Insert的語句快于Select，我當(dāng)即反駁，但是回來翻看自己的測試記錄，卻的確如此。一方面慚愧，一方面感覺到測試的數(shù)據(jù)如果不文檔化幾乎沒有任何意義。
如何看待這些測試數(shù)據(jù)？正如我一直強(qiáng)調(diào)的任何Benchmark測試所產(chǎn)生的數(shù)據(jù)都只是參考，對(duì)于真實(shí)環(huán)境都是一種模擬，比如對(duì)于查詢，如果是多個(gè)表的真實(shí)環(huán)境，數(shù)據(jù)表的查詢效率可能要低很多，因?yàn)橄嚓P(guān)的索引必須在內(nèi)存中換進(jìn)換出，效率肯定成倍降低。
本文的作用是給出比較，對(duì)比參考，讓你了解什么什么狀態(tài)下可能具有更好的性能，比如MySQL的本地環(huán)境下的性能是網(wǎng)絡(luò)環(huán)境下的性能的3倍多。STMT比原有的API可以提高處理30%的性能，這才是Benchmark參考的意義。
感謝 zengyu提供InnoDB 測試的指導(dǎo)，感謝wang,chris對(duì)文檔提供意見。同時(shí)也向?qū)μ峁C(jī)器給我測試的echoqin,penghao表示謝意。
下面是我的測試程序，IP和密碼部分已經(jīng)注釋了，請(qǐng)參考時(shí)注意，另外，所有的測試程序基于我自己的一套程序庫zenlib，如果有需要，可以請(qǐng)向我索要，您也可以直接使用MySQL API替換之。而且對(duì)于Benchmark，我建議你自己寫自己的測試用例，這樣才能有最好的效果。
Robert D. Schneider
《MySQL:The definitive guide to using,programming,and administering MySQL 4 (second Edition)》
本著自由的精神，閱讀者可以無須授權(quán)就可以自由的轉(zhuǎn)載這個(gè)文檔，我只保留作者的署名權(quán)利，也就是說，你轉(zhuǎn)載只需保留這段說明和文檔的完整性。
