7.5 優(yōu)化 MySQL 服務(wù)器

7.5.1 調(diào)整系統(tǒng)因素及啟動(dòng)參數(shù)

我們從系統(tǒng)級別的因素開始說起，因?yàn)橛行┓矫娴囊蛩乇仨毐M早決定才能取得較大性能改進(jìn)。其他情況下，只需要快速看一下本章節(jié)即可。不過，在這個(gè)級別看看能做什么以取得更高性能更合適。
使用默認(rèn)的操作系統(tǒng)這很重要。想要最有效地使用多CPU機(jī)器，就使用Solaris(因?yàn)樗木€程實(shí)現(xiàn)確實(shí)很好)或Linux(因?yàn)?.2的內(nèi)核對SMP有良好的支持)。請注意，老版本的Linux內(nèi)核默認(rèn)會(huì)有2GB文件大小限制。如果使用這樣的內(nèi)核而文件又確實(shí)需要大于2GB，那么就必須對ext2文件系統(tǒng)打大文件支持(LFS)補(bǔ)丁。其他文件系統(tǒng)諸如 ReiserFS 和 XFS 則沒有這個(gè)限制。
在MySQL投入生產(chǎn)之前，我們建議你在欲使用的平臺(tái)上先做一下測試。

其他tips：

• 如果有足夠的RAM(隨機(jī)存儲(chǔ)器)，則應(yīng)該去掉所有的交換設(shè)備。有些操作系統(tǒng)在一些情景中盡管有剩余內(nèi)存也會(huì)使用交換設(shè)備。
• 使用MySQL選項(xiàng) --skip-external-locking 來避免外部鎖。從MySQL 4.0開始，這個(gè)選項(xiàng)默認(rèn)是打開的。在這之前，只有編譯支持
  MIT-pthreads 才能默認(rèn)打開，因?yàn)樵谒衅脚_(tái)上的MIT-pthreads 不能全部都支持 flock()。這在Linux上也是默認(rèn)打開的，因?yàn)長inux的文件鎖還不安全。注意，--skip-external-locking 選項(xiàng)在服務(wù)器運(yùn)行時(shí)并不會(huì)影響其功能性。只要記住在運(yùn)行
  myisamchk 前要關(guān)閉服務(wù)器(或者鎖定并且刷新相關(guān)數(shù)據(jù)表)。在一些操作系統(tǒng)上這個(gè)選項(xiàng)是強(qiáng)制的，因?yàn)橥獠挎i在任何情況下都無法使用。不能使用 --skip-external-locking 選項(xiàng)的唯一情況是：在同一個(gè)數(shù)據(jù)上運(yùn)行多個(gè)MySQL服務(wù)器(不是客戶端)，或者運(yùn)行
  myisamchk 檢查(不是修復(fù))數(shù)據(jù)表前沒有先告訴服務(wù)器要刷新并且鎖定該表。使用 --skip-external-locking 選項(xiàng)后依舊可以使用 LOCK TABLES 和 UNLOCK TABLES 語句。

7.5.2 調(diào)整服務(wù)器參數(shù)

可以使用以下 mysqld 命令(在MySQL 4.1以前，忽略 --verbose)來確定默認(rèn)的緩沖大?。?/p>

shell> mysqld --verbose --help

這個(gè)命令產(chǎn)生了所有的 mysqld 選項(xiàng)以及可以配置的系統(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ù)器在運(yùn)行，可以連接上去用以下命令來查看實(shí)際使用的系統(tǒng)變量：

mysql> SHOW VARIABLES;

也可以用以下語句來查看運(yùn)行中的系統(tǒng)的統(tǒng)計(jì)結(jié)果及狀態(tài)報(bào)告：

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)存就能運(yùn)行。不過，給MySQL更多的內(nèi)存通常能取得更好的性能。
調(diào)整MySQL服務(wù)器時(shí)，兩個(gè)最重要的變量就是 key_buffer_size 和 table_cache。在試圖修改其他變量前應(yīng)該首先確認(rèn)已經(jīng)合理設(shè)定這兩個(gè)變量了。
以下例子展示了在不同的運(yùn)行時(shí)配置一些典型的變量值。這些例子使用 mysqld_safe 腳本和 --var_name=value 語法來設(shè)定變量 var_name 的值為 value。這個(gè)語法在MySQL 4.0以后就可以用了，在舊版本的MySQL中，考慮到如下一些不同之處：

• 使用 safe_mysqld 腳本而非 mysqld_safe。

   

   

• 使用 --set-variable=var_name=value 或 -O var_name=value 語法來設(shè)置變量。

   

   

• 如果變量名以 _size 結(jié)尾，就必須去掉 _size。例如，一個(gè)舊變量名為 sort_buffer_size 就是 sort_buffer，舊變量名read_buffer_size 就是 record_buffer。用 mysqld --help來要看那些變量是當(dāng)前服務(wù)器版本可以識別的。

如果至少有256MB內(nèi)存，且有大量的數(shù)據(jù)表，還想要在有中等數(shù)量的客戶端連接時(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)存時(shí)性能更好。

如果只有很少內(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 &

如果在一個(gè)比可用內(nèi)存大很多的標(biāo)上做 GROUP BY 或 ORDER BY 操作時(shí)，那么最好加大 read_rnd_buffer_size 的值以加速排序操作后的讀數(shù)據(jù)。
安裝MySQL后，在 `support-files' 目錄下會(huì)有一些不同的 `my.cnf' 樣例文件： `my-huge.cnf', `my-large.cnf', `my-medium.cnf' 和 `my-small.cnf'?？梢园阉鼈冏鳛閮?yōu)化系統(tǒng)的藍(lán)本。
注意，如果是通過命令行給 mysqld 或 mysqld_safe 指定參數(shù)，那么它只在那次啟動(dòng)服務(wù)器時(shí)有效。想要讓這些選項(xiàng)在服務(wù)器啟動(dòng)時(shí)都有效，可以把它們放到配置文件中。
想要看參數(shù)改變后的效果，可以用以下方法(在MySQL 4.1以前，忽略 --verbose)：

shell> mysqld --key_buffer_size=32M --verbose --help

這個(gè)變量就會(huì)在結(jié)果的靠近末尾列出來。確認(rèn) --verbose 和 --help 選項(xiàng)是放在最后面，否則，在命令行上列出來的結(jié)果中在它們之后的其他選項(xiàng)效果就不會(huì)被反映出來了。
關(guān)于調(diào)整 InnoDB 存儲(chǔ)引擎的詳細(xì)信息請參考"16.12 InnoDB Performance Tuning Tips"。

7.5.3 控制查詢優(yōu)化性能

查詢優(yōu)化程序的任務(wù)就是找到最佳的執(zhí)行SQL查詢的方法。因?yàn)?好"和"壞"方法之間的性能差異可能有數(shù)量級上的區(qū)別(也就是說，秒相對小時(shí)，甚至是天)，MySQL中的大部分查詢優(yōu)化程序或多或少會(huì)窮舉搜索可能的優(yōu)化方法，從中找到最佳的方法來執(zhí)行。拿連接查詢來說，MySQL優(yōu)化程序搜索的可能方法會(huì)隨著查詢中引用表數(shù)量的增加而指數(shù)增加。如果表數(shù)量較少(通常少于7-10個(gè))，那么這基本上不是問題。不過，當(dāng)提交一個(gè)很大的查詢時(shí)，服務(wù)器的性能主要瓶頸很容易就花費(fèi)在優(yōu)化查詢上。
MySQL 5.0.1引進(jìn)了一個(gè)更靈活的方法，它允許用戶控制在查詢優(yōu)化程序窮舉搜索最佳優(yōu)化方法的數(shù)量。一般的考慮是，優(yōu)化程序搜索的方法越少，那么在編譯查詢時(shí)耗費(fèi)的時(shí)間就越少。另一個(gè)方面，由于優(yōu)化程序可能會(huì)忽略一些方法，因此可能錯(cuò)過找到最佳優(yōu)化方法。

關(guān)于控制優(yōu)化程序評估優(yōu)化方法的數(shù)量可以通過以下兩個(gè)系統(tǒng)變量：

• 變量 optimizer_prune_level 告訴優(yōu)化程序在估算要訪問的每個(gè)表的記錄數(shù)基礎(chǔ)上忽略一定數(shù)量的方法。我們的經(jīng)驗(yàn)表明，這種"學(xué)習(xí)猜測"方法很少會(huì)錯(cuò)過最佳方法，因?yàn)樗赡軕騽⌒缘販p少編譯時(shí)間。這就是為什么這個(gè)選項(xiàng)默認(rèn)是打開的(optimizer_prune_level=1)。不過，如果確信優(yōu)化程序會(huì)錯(cuò)過更好的方法，這個(gè)選項(xiàng)可以關(guān)上(optimizer_prune_level=0)，不過要注意編譯查詢的時(shí)間可能會(huì)更長了。要注意盡管是用了這種試探方法，優(yōu)化程序仍會(huì)調(diào)查指數(shù)級的方法。

   

   

• 變量 optimizer_search_depth
  告訴優(yōu)化程序"將來"的每次順序調(diào)查不完全的方法是否需要擴(kuò)充的更遠(yuǎn)的深度。optimizer_search_depth 的值越小，可能會(huì)導(dǎo)致查詢編譯時(shí)間的越少。例如，有一個(gè)12-13或更多表的查詢很容易就需要幾小時(shí)甚至幾天的時(shí)間來編譯，如果 optimizer_search_depth 的值和表數(shù)量相近的話。同樣，如果 optimizer_search_depth 的值等于3或4，則編譯器可能至需要花不到幾分鐘的時(shí)間就完成編譯了。如果不能確定 optimizer_search_depth 的值多少才合適，就把它設(shè)置為0，讓優(yōu)化程序來自動(dòng)決定。