7.5 優(yōu)化 MySQL 服務(wù)器
7.5.1 調(diào)整系統(tǒng)因素及啟動參數(shù)
我們從系統(tǒng)級別的因素開始說起，因為有些方面的因素必須盡早決定才能取得較大性能改進。其他情況下，只需要快速看一下本章節(jié)即可。不過，在這個級別看看能做什么以取得更高性能更合適。
使用默認(rèn)的操作系統(tǒng)這很重要。想要最有效地使用多CPU機器，就使用Solaris(因為它的線程實現(xiàn)確實很好)或Linux(因為2.2的內(nèi)核對SMP有良好的支持)。請注意，老版本的Linux內(nèi)核默認(rèn)會有2GB文件大小限制。如果使用這樣的內(nèi)核而文件又確實需要大于2GB，那么就必須對ext2文件系統(tǒng)打大文件支持(LFS)補丁。其他文件系統(tǒng)諸如 ReiserFS 和 XFS 則沒有這個限制。
在MySQL投入生產(chǎn)之前，我們建議你在欲使用的平臺上先做一下測試。
其他tips：
- 如果有足夠的RAM(隨機存儲器)，則應(yīng)該去掉所有的交換設(shè)備。有些操作系統(tǒng)在一些情景中盡管有剩余內(nèi)存也會使用交換設(shè)備。
- 使用MySQL選項
--skip-external-locking來避免外部鎖。從MySQL 4.0開始，這個選項默認(rèn)是打開的。在這之前，只有編譯支持
MIT-pthreads 才能默認(rèn)打開，因為在所有平臺上的MIT-pthreads 不能全部都支持flock()。這在Linux上也是默認(rèn)打開的，因為Linux的文件鎖還不安全。注意，--skip-external-locking選項在服務(wù)器運行時并不會影響其功能性。只要記住在運行myisamchk前要關(guān)閉服務(wù)器(或者鎖定并且刷新相關(guān)數(shù)據(jù)表)。在一些操作系統(tǒng)上這個選項是強制的，因為外部鎖在任何情況下都無法使用。不能使用--skip-external-locking選項的唯一情況是：在同一個數(shù)據(jù)上運行多個MySQL服務(wù)器(不是客戶端)，或者運行myisamchk檢查(不是修復(fù))數(shù)據(jù)表前沒有先告訴服務(wù)器要刷新并且鎖定該表。使用--skip-external-locking選項后依舊可以使用LOCK TABLES和UNLOCK TABLES語句。
7.5.2 調(diào)整服務(wù)器參數(shù)
可以使用以下 mysqld 命令(在MySQL 4.1以前，忽略 --verbose)來確定默認(rèn)的緩沖大?。?/p>
shell> mysqld --verbose --help
這個命令產(chǎn)生了所有的 mysqld 選項以及可以配置的系統(tǒng)變量列表。結(jié)果中包括默認(rèn)值，看起來像是如下：
back_log current value: 5 bdb_cache_size current value: 1048540 binlog_cache_size current value: 32768 connect_timeout current value: 5 delayed_insert_limit current value: 100 delayed_insert_timeout current value: 300 delayed_queue_size current value: 1000 flush_time current value: 0 interactive_timeout current value: 28800 join_buffer_size current value: 131072 key_buffer_size current value: 1048540 long_query_time current value: 10 lower_case_table_names current value: 0 max_allowed_packet current value: 1048576 max_binlog_cache_size current value: 4294967295 max_connect_errors current value: 10 max_connections current value: 100 max_delayed_threads current value: 20 max_heap_table_size current value: 16777216 max_join_size current value: 4294967295 max_sort_length current value: 1024 max_tmp_tables current value: 32 max_write_lock_count current value: 4294967295 myisam_sort_buffer_size current value: 8388608 net_buffer_length current value: 16384 net_read_timeout current value: 30 net_retry_count current value: 10 net_write_timeout current value: 60 read_buffer_size current value: 131072 read_rnd_buffer_size current value: 262144 slow_launch_time current value: 2 sort_buffer current value: 2097116 table_cache current value: 64 thread_concurrency current value: 10 thread_stack current value: 131072 tmp_table_size current value: 1048576 wait_timeout current value: 28800
如果當(dāng)前有 mysqld 服務(wù)器在運行，可以連接上去用以下命令來查看實際使用的系統(tǒng)變量：
mysql> SHOW VARIABLES;
也可以用以下語句來查看運行中的系統(tǒng)的統(tǒng)計結(jié)果及狀態(tài)報告：
mysql> SHOW STATUS;
系統(tǒng)變量以及狀態(tài)信息也可以通過mysqladmin 來得到：
shell> mysqladmin variables shell> mysqladmin extended-status
在章節(jié)"5.2.3 Server System Variables"和"5.2.4 Server Status Variables"中可以找到全部的系統(tǒng)描述及狀態(tài)變量。
MySQL使用的算法有高伸縮性，因此它通?？梢灾皇褂煤苌賰?nèi)存就能運行。不過，給MySQL更多的內(nèi)存通常能取得更好的性能。
調(diào)整MySQL服務(wù)器時，兩個最重要的變量就是 key_buffer_size 和 table_cache。在試圖修改其他變量前應(yīng)該首先確認(rèn)已經(jīng)合理設(shè)定這兩個變量了。
以下例子展示了在不同的運行時配置一些典型的變量值。這些例子使用 mysqld_safe 腳本和 --var_name=value 語法來設(shè)定變量 var_name 的值為 value。這個語法在MySQL 4.0以后就可以用了，在舊版本的MySQL中，考慮到如下一些不同之處：
- 使用
safe_mysqld腳本而非mysqld_safe。 - 使用
--set-variable=var_name=value或-O var_name=value語法來設(shè)置變量。 - 如果變量名以
_size結(jié)尾，就必須去掉_size。例如，一個舊變量名為sort_buffer_size就是sort_buffer，舊變量名read_buffer_size就是record_buffer。用mysqld --help來要看那些變量是當(dāng)前服務(wù)器版本可以識別的。
如果至少有256MB內(nèi)存，且有大量的數(shù)據(jù)表，還想要在有中等數(shù)量的客戶端連接時能有最大性能，可以這么設(shè)定：
shell> mysqld_safe --key_buffer_size=64M --table_cache=256 --sort_buffer_size=4M --read_buffer_size=1M &
如果只有128MB內(nèi)存，且只有少量表，但是需要做大量的排序，可以這么設(shè)定：
shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
如果有大量的并發(fā)連接，除非 mysqld 已經(jīng)設(shè)置成對每次連接只是用很少的內(nèi)存，否則可能發(fā)生交換問題。mysqld 在對每次連接都有足夠內(nèi)存時性能更好。
如果只有很少內(nèi)存且有大量連接，可以這么設(shè)定：
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K --read_buffer_size=100K &
甚至這樣：
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K --table_cache=32 --read_buffer_size=8K --net_buffer_length=1K &
如果在一個比可用內(nèi)存大很多的標(biāo)上做 GROUP BY 或 ORDER BY 操作時，那么最好加大 read_rnd_buffer_size 的值以加速排序操作后的讀數(shù)據(jù)。
安裝MySQL后，在 `support-files' 目錄下會有一些不同的 `my.cnf' 樣例文件： `my-huge.cnf', `my-large.cnf', `my-medium.cnf' 和 `my-small.cnf'?？梢园阉鼈冏鳛閮?yōu)化系統(tǒng)的藍本。
注意，如果是通過命令行給 mysqld 或 mysqld_safe 指定參數(shù)，那么它只在那次啟動服務(wù)器時有效。想要讓這些選項在服務(wù)器啟動時都有效，可以把它們放到配置文件中。
想要看參數(shù)改變后的效果，可以用以下方法(在MySQL 4.1以前，忽略 --verbose)：
shell> mysqld --key_buffer_size=32M --verbose --help
這個變量就會在結(jié)果的靠近末尾列出來。確認(rèn) --verbose 和 --help 選項是放在最后面，否則，在命令行上列出來的結(jié)果中在它們之后的其他選項效果就不會被反映出來了。
關(guān)于調(diào)整 InnoDB 存儲引擎的詳細信息請參考"16.12 InnoDB Performance Tuning Tips"。
7.5.3 控制查詢優(yōu)化性能
查詢優(yōu)化程序的任務(wù)就是找到最佳的執(zhí)行SQL查詢的方法。因為"好"和"壞"方法之間的性能差異可能有數(shù)量級上的區(qū)別(也就是說，秒相對小時，甚至是天)，MySQL中的大部分查詢優(yōu)化程序或多或少會窮舉搜索可能的優(yōu)化方法，從中找到最佳的方法來執(zhí)行。拿連接查詢來說，MySQL優(yōu)化程序搜索的可能方法會隨著查詢中引用表數(shù)量的增加而指數(shù)增加。如果表數(shù)量較少(通常少于7-10個)，那么這基本上不是問題。不過，當(dāng)提交一個很大的查詢時，服務(wù)器的性能主要瓶頸很容易就花費在優(yōu)化查詢上。
MySQL 5.0.1引進了一個更靈活的方法，它允許用戶控制在查詢優(yōu)化程序窮舉搜索最佳優(yōu)化方法的數(shù)量。一般的考慮是，優(yōu)化程序搜索的方法越少，那么在編譯查詢時耗費的時間就越少。另一個方面，由于優(yōu)化程序可能會忽略一些方法，因此可能錯過找到最佳優(yōu)化方法。
關(guān)于控制優(yōu)化程序評估優(yōu)化方法的數(shù)量可以通過以下兩個系統(tǒng)變量：
- 變量
optimizer_prune_level告訴優(yōu)化程序在估算要訪問的每個表的記錄數(shù)基礎(chǔ)上忽略一定數(shù)量的方法。我們的經(jīng)驗表明，這種"學(xué)習(xí)猜測"方法很少會錯過最佳方法，因為它可能戲劇性地減少編譯時間。這就是為什么這個選項默認(rèn)是打開的(optimizer_prune_level=1)。不過，如果確信優(yōu)化程序會錯過更好的方法，這個選項可以關(guān)上(optimizer_prune_level=0)，不過要注意編譯查詢的時間可能會更長了。要注意盡管是用了這種試探方法，優(yōu)化程序仍會調(diào)查指數(shù)級的方法。 - 變量
optimizer_search_depth
告訴優(yōu)化程序"將來"的每次順序調(diào)查不完全的方法是否需要擴充的更遠的深度。optimizer_search_depth的值越小，可能會導(dǎo)致查詢編譯時間的越少。例如，有一個12-13或更多表的查詢很容易就需要幾小時甚至幾天的時間來編譯，如果optimizer_search_depth的值和表數(shù)量相近的話。同樣，如果optimizer_search_depth的值等于3或4，則編譯器可能至需要花不到幾分鐘的時間就完成編譯了。如果不能確定optimizer_search_depth的值多少才合適，就把它設(shè)置為0，讓優(yōu)化程序來自動決定。
