.jpg)
Mysql關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)
mysql是一個(gè)開(kāi)放源碼的小型關(guān)聯(lián)式數(shù)據(jù)庫(kù)管理系統(tǒng)，開(kāi)發(fā)者為瑞典mysql ab公司。mysql被廣泛地應(yīng)用在internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開(kāi)放源碼這一特點(diǎn)，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了mysql作為網(wǎng)站數(shù)據(jù)庫(kù)。
本文主要給大家講解的是mysql優(yōu)化過(guò)程中比較重要的2個(gè)參數(shù)緩沖和緩存的設(shè)置，希望大家能夠喜歡
MySQL 可調(diào)節(jié)設(shè)置可以應(yīng)用于整個(gè) mysqld進(jìn)程，也可以應(yīng)用于單個(gè)客戶(hù)機(jī)會(huì)話(huà)。
服務(wù)器端的設(shè)置
每個(gè)表都可以表示為磁盤(pán)上的一個(gè)文件，必須先打開(kāi)，后讀取。為了加快從文件中讀取數(shù)據(jù)的過(guò)程，mysqld對(duì)這些打開(kāi)文件進(jìn)行了緩存，其最大數(shù)目由 /etc/mysqld.conf 中的table_cache 指定。清單 4給出了顯示與打開(kāi)表有關(guān)的活動(dòng)的方式。
清單 4. 顯示打開(kāi)表的活動(dòng)
mysql>?SHOW?STATUS?LIKE?'open%tables'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?Open_tables??|?5000?| |?Opened_tables?|?195??| +---------------+-------+ 2?rows?in?set?(0.00?sec)
清單 4 說(shuō)明目前有 5,000 個(gè)表是打開(kāi)的，有 195個(gè)表需要打開(kāi)，因?yàn)楝F(xiàn)在緩存中已經(jīng)沒(méi)有可用文件描述符了（由于統(tǒng)計(jì)信息在前面已經(jīng)清除了，因此可能會(huì)存在 5,000 個(gè)打開(kāi)表中只有 195個(gè)打開(kāi)記錄的情況）。如果 Opened_tables 隨著重新運(yùn)行SHOW STATUS 命令快速增加，就說(shuō)明緩存命中率不夠。如果Open_tables 比table_cache設(shè)置小很多，就說(shuō)明該值太大了（不過(guò)有空間可以增長(zhǎng)總不是什么壞事）。例如，使用 table_cache =5000 可以調(diào)整表的緩存。
與表的緩存類(lèi)似，對(duì)于線(xiàn)程來(lái)說(shuō)也有一個(gè)緩存。 mysqld在接收連接時(shí)會(huì)根據(jù)需要生成線(xiàn)程。在一個(gè)連接變化很快的繁忙服務(wù)器上，對(duì)線(xiàn)程進(jìn)行緩存便于以后使用可以加快最初的連接。
清單 5 顯示如何確定是否緩存了足夠的線(xiàn)程。
清單 5. 顯示線(xiàn)程使用統(tǒng)計(jì)信息
mysql>?SHOW?STATUS?LIKE?'threads%'; +-------------------+--------+ |?Variable_name???|?Value?| +-------------------+--------+ |?Threads_cached??|?27???| |?Threads_connected?|?15???| |?Threads_created??|?838610?| |?Threads_running??|?3???| +-------------------+--------+ 4?rows?in?set?(0.00?sec)
此處重要的值是 Threads_created，每次mysqld 需要?jiǎng)?chuàng)建一個(gè)新線(xiàn)程時(shí)，這個(gè)值都會(huì)增加。如果這個(gè)數(shù)字在連續(xù)執(zhí)行SHOW STATUS 命令時(shí)快速增加，就應(yīng)該嘗試增大線(xiàn)程緩存。例如，可以在my.cnf 中使用 thread_cache = 40 來(lái)實(shí)現(xiàn)此目的。
關(guān)鍵字緩沖區(qū)保存了 MyISAM 表的索引塊。理想情況下，對(duì)于這些塊的請(qǐng)求應(yīng)該來(lái)自于內(nèi)存，而不是來(lái)自于磁盤(pán)。清單 6顯示了如何確定有多少塊是從磁盤(pán)中讀取的，以及有多少塊是從內(nèi)存中讀取的。
清單 6. 確定關(guān)鍵字效率
mysql>?show?status?like?'%key_read%'; +-------------------+-----------+ |?Variable_name???|?Value???| +-------------------+-----------+ |?Key_read_requests?|?163554268?| |?Key_reads?????|?98247???| +-------------------+-----------+ 2?rows?in?set?(0.00?sec)
Key_reads 代表命中磁盤(pán)的請(qǐng)求個(gè)數(shù)，Key_read_requests是總數(shù)。命中磁盤(pán)的讀請(qǐng)求數(shù)除以讀請(qǐng)求總數(shù)就是不中比率 —— 在本例中每 1,000 個(gè)請(qǐng)求，大約有 0.6 個(gè)沒(méi)有命中內(nèi)存。如果每1,000 個(gè)請(qǐng)求中命中磁盤(pán)的數(shù)目超過(guò) 1 個(gè)，就應(yīng)該考慮增大關(guān)鍵字緩沖區(qū)了。例如，key_buffer =384M 會(huì)將緩沖區(qū)設(shè)置為 384MB。
臨時(shí)表可以在更高級(jí)的查詢(xún)中使用，其中數(shù)據(jù)在進(jìn)一步進(jìn)行處理（例如 GROUPBY字句）之前，都必須先保存到臨時(shí)表中；理想情況下，在內(nèi)存中創(chuàng)建臨時(shí)表。但是如果臨時(shí)表變得太大，就需要寫(xiě)入磁盤(pán)中。清單 7給出了與臨時(shí)表創(chuàng)建有關(guān)的統(tǒng)計(jì)信息。
清單 7. 確定臨時(shí)表的使用
mysql>?SHOW?STATUS?LIKE?'created_tmp%'; +-------------------------+-------+ |?Variable_name??????|?Value?| +-------------------------+-------+ |?Created_tmp_disk_tables?|?30660?| |?Created_tmp_files????|?2???| |?Created_tmp_tables???|?32912?| +-------------------------+-------+ 3?rows?in?set?(0.00?sec)
每次使用臨時(shí)表都會(huì)增大 Created_tmp_tables；基于磁盤(pán)的表也會(huì)增大 Created_tmp_disk_tables。對(duì)于這個(gè)比率，并沒(méi)有什么嚴(yán)格的規(guī)則，因?yàn)檫@依賴(lài)于所涉及的查詢(xún)。長(zhǎng)時(shí)間觀(guān)察Created_tmp_disk_tables會(huì)顯示所創(chuàng)建的磁盤(pán)表的比率，您可以確定設(shè)置的效率。 tmp_table_size和 max_heap_table_size都可以控制臨時(shí)表的最大大小，因此請(qǐng)確保在 my.cnf 中對(duì)這兩個(gè)值都進(jìn)行了設(shè)置。
每個(gè)會(huì)話(huà) 的設(shè)置
下面這些設(shè)置針對(duì)于每個(gè)會(huì)話(huà)。在設(shè)置這些數(shù)字時(shí)要十分謹(jǐn)慎，因?yàn)樗鼈冊(cè)诔艘钥赡艽嬖诘倪B接數(shù)時(shí)候，這些選項(xiàng)表示大量的內(nèi)存！您可以通過(guò)代碼修改會(huì)話(huà)中的這些數(shù)字，或者在 my.cnf 中為所有會(huì)話(huà)修改這些設(shè)置。
當(dāng) MySQL必須要進(jìn)行排序時(shí)，就會(huì)在從磁盤(pán)上讀取數(shù)據(jù)時(shí)分配一個(gè)排序緩沖區(qū)來(lái)存放這些數(shù)據(jù)行。如果要排序的數(shù)據(jù)太大，那么數(shù)據(jù)就必須保存到磁盤(pán)上的臨時(shí)文件中，并再次進(jìn)行排序。如果 sort_merge_passes狀態(tài)變量很大，這就指示了磁盤(pán)的活動(dòng)情況。清單 8 給出了一些與排序相關(guān)的狀態(tài)計(jì)數(shù)器信息。
清單 8. 顯示排序統(tǒng)計(jì)信息
mysql>?SHOW?STATUS?LIKE?"sort%"; +-------------------+---------+ |?Variable_name???|?Value??| +-------------------+---------+ |?Sort_merge_passes?|?1????| |?Sort_range????|?79192??| |?Sort_rows?????|?2066532?| |?Sort_scan?????|?44006??| +-------------------+---------+ 4?rows?in?set?(0.00?sec)
如果 sort_merge_passes 很大，就表示需要注意sort_buffer_size。例如，sort_buffer_size = 4M 將排序緩沖區(qū)設(shè)置為 4MB。
MySQL也會(huì)分配一些內(nèi)存來(lái)讀取表。理想情況下，索引提供了足夠多的信息，可以只讀入所需要的行，但是有時(shí)候查詢(xún)（設(shè)計(jì)不佳或數(shù)據(jù)本性使然）需要讀取表中大量數(shù)據(jù)。要理解這種行為，需要知道運(yùn)行了多少個(gè) SELECT語(yǔ)句，以及需要讀取表中的下一行數(shù)據(jù)的次數(shù)（而不是通過(guò)索引直接訪(fǎng)問(wèn)）。實(shí)現(xiàn)這種功能的命令如清單 9 所示。
清單 9. 確定表掃描比率
mysql>?SHOW?STATUS?LIKE?"com_select"; +---------------+--------+ |?Variable_name?|?Value?| +---------------+--------+ |?Com_select??|?318243?| +---------------+--------+ 1?row?in?set?(0.00?sec) mysql>?SHOW?STATUS?LIKE?"handler_read_rnd_next"; +-----------------------+-----------+ |?Variable_name?????|?Value???| +-----------------------+-----------+ |?Handler_read_rnd_next?|?165959471?| +-----------------------+-----------+ 1?row?in?set?(0.00?sec)
Handler_read_rnd_next /Com_select 得出了表掃描比率 —— 在本例中是 521:1。如果該值超過(guò)4000，就應(yīng)該查看 read_buffer_size，例如read_buffer_size = 4M。如果這個(gè)數(shù)字超過(guò)了8M，就應(yīng)該與開(kāi)發(fā)人員討論一下對(duì)這些查詢(xún)進(jìn)行調(diào)優(yōu)了！
查看數(shù)據(jù)庫(kù)緩存配置情況
mysql>?SHOW?VARIABLES?LIKE?‘%query_cache%'; +——————————+———+ |?Variable_name?|?Value?| +——————————+———+ |?have_query_cache?|?YES?|?–查詢(xún)緩存是否可用 |?query_cache_limit?|?1048576?|?–可緩存具體查詢(xún)結(jié)果的最大值 |?query_cache_min_res_unit?|?4096?| |?query_cache_size?|?599040?|?–查詢(xún)緩存的大小 |?query_cache_type?|?ON?|?–阻止或是支持查詢(xún)緩存 |?query_cache_wlock_invalidate?|?OFF?| +——————————+———+
配置方法：
在MYSQL的配置文件my.ini或my.cnf中找到如下內(nèi)容：
#?Query?cache?is?used?to?cache?SELECT?results?and?later?returnthem #?without?actual?executing?the?same?query?once?again.?Having?thequery #?cache?enabled?may?result?in?significant?speed?improvements,?ifyour #?have?a?lot?of?identical?queries?and?rarely?changing?tables.See?the #?"Qcache_lowmem_prunes"?status?variable?to?check?if?the?currentvalue #?is?high?enough?for?your?load. #?Note:?In?case?your?tables?change?very?often?or?if?your?queriesare #?textually?different?every?time,?the?query?cache?may?result?ina #?slowdown?instead?of?a?performance?improvement. query_cache_size=0
以上信息是默認(rèn)配置，其注釋意思是說(shuō)，MYSQL的查詢(xún)緩存用于緩存select查詢(xún)結(jié)果，并在下次接收到同樣的查詢(xún)請(qǐng)求時(shí)，不再執(zhí)行實(shí)際查詢(xún)處理而直接返回結(jié)果，有這樣的查詢(xún)緩存能提高查詢(xún)的速度，使查詢(xún)性能得到優(yōu)化，前提條件是你有大量的相同或相似的查詢(xún)，而很少改變表里的數(shù)據(jù)，否則沒(méi)有必要使用此功能。可以通過(guò)Qcache_lowmem_prunes變量的值來(lái)檢查是否當(dāng)前的值滿(mǎn)足你目前系統(tǒng)的負(fù)載。注意：如果你查詢(xún)的表更新比較頻繁，而且很少有相同的查詢(xún)，最好不要使用查詢(xún)緩存。
具體配置方法：
1． 將query_cache_size設(shè)置為具體的大小，具體大小是多少取決于查詢(xún)的實(shí)際情況，但最好設(shè)置為1024的倍數(shù)，參考值32M。
2． 增加一行：query_cache_type=1
query_cache_type參數(shù)用于控制緩存的類(lèi)型，注意這個(gè)值不能隨便設(shè)置，必須設(shè)置為數(shù)字，可選項(xiàng)目以及說(shuō)明如下：
如果設(shè)置為0，那么可以說(shuō)，你的緩存根本就沒(méi)有用，相當(dāng)于禁用了。但是這種情況下query_cache_size設(shè)置的大小系統(tǒng)是否要為其分配呢，這個(gè)問(wèn)題有待于測(cè)試？
如果設(shè)置為1，將會(huì)緩存所有的結(jié)果，除非你的select語(yǔ)句使用SQL_NO_CACHE禁用了查詢(xún)緩存。
如果設(shè)置為2，則只緩存在select語(yǔ)句中通過(guò)SQL_CACHE指定需要緩存的查詢(xún)。
OK，配置完后的部分文件如下：
query_cache_size=128M query_cache_type=1
保存文件，重新啟動(dòng)MYSQL服務(wù)，然后通過(guò)如下查詢(xún)來(lái)驗(yàn)證是否真正開(kāi)啟了：
mysql>?show?variables?like?'%query_cache%'; +——————————+———–+ |?Variable_name??????|Value??| +——————————+———–+ |?have_query_cache?????|YES???| |?query_cache_limit?????|1048576??| |?query_cache_min_res_unit??|4096???| |?query_cache_size?????|?134217728| |?query_cache_type?????|ON????| |?query_cache_wlock_invalidate?|?OFF???| +——————————+———–+ 6?rows?in?set?(0.00?sec)
主要看query_cache_size和query_cache_type的值是否跟我們?cè)O(shè)的一致：
這里query_cache_size的值是134217728，我們?cè)O(shè)置的是128M，實(shí)際是一樣的，只是單位不同，可以自己換算下：134217728 = 128*1024*1024。
query_cache_type設(shè)置為1，顯示為ON，這個(gè)前面已經(jīng)說(shuō)過(guò)了。
總之，看到上邊的顯示表示設(shè)置正確，但是在實(shí)際的查詢(xún)中是否能夠緩存查詢(xún)，還需要手動(dòng)測(cè)試下，我們可以通過(guò)show statuslike ‘%Qcache%’;語(yǔ)句來(lái)測(cè)試，現(xiàn)在我們開(kāi)啟了查詢(xún)緩存功能，在執(zhí)行查詢(xún)前，我們先看看相關(guān)參數(shù)的值：
mysql>?show?status?like?'%Qcache%'; +————————-+———–+ |?Variable_name????|Value??| +————————-+———–+ |?Qcache_free_blocks???|1????| |?Qcache_free_memory???|?134208800| |?Qcache_hits?????|0????|
以上就是mysql緩沖和緩存設(shè)置詳解的內(nèi)容，更多相關(guān)內(nèi)容請(qǐng)關(guān)注PHP中文網(wǎng)（www.php.cn）！
.png)
