萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> MySQL 緩存 Query Cache

MySQL 緩存 Query Cache

過濾所有注釋

去掉SQL文本前後的空格,TAB等字符。注意,是文本前面和後面的。中間的不會被去掉。

下面的三條SQL裡,因 為SELECT大小寫的關系,最後一條和其他兩條在QC裡肯定是用的不一樣的存儲位置。而第一條和第二條,區別在於後者有個注釋,在不同客戶端,會有不一 樣的結果。所以,保險起見,請盡量不要使用動態的注釋。在PHP的mysql教程擴展裡,SQL的注釋是不會被去掉的。也就是三條SQL會被存儲在三個不同的 緩存裡,雖然它們的結果都是一樣的。

select * FROM people where name='surfchen';

select * FROM people where /*hey~*/name='surfchen';

SELECT * FROM people where name='surfchen';

目前只有select語句會被cache,其他類似show,use的語句則不會被cache。

因為QC是如此前端,如此簡單的一個緩存系統,所以如果一個表被更新,那麼和這個表相關的SQL的所有QC都會被失效。假設一個聯合查詢裡涉及到了表A和表B,如果表A或者表B的其中一個被更新(update或者delete),這個查詢的QC將會失效。

也就是說,如果一個表被頻繁更新,那麼就要考慮清楚究竟是否應該對相關的一些SQL進行QC了。一個被頻繁更新的表如果被應用了QC,可能會加重數 據庫的負擔,而不是減輕負擔。我一般的做法是默認打開QC,而對一些涉及頻繁更新的表的SQL語句加上SQL_NO_CACHE關鍵詞來對其禁用 CACHE。這樣可以盡可能避免不必要的內存操作,盡可能保持內存的連續性。

那些查詢很分散的SQL語句,也不應該使用QC。例如用來查詢用戶和密碼的語句——“select pass from user where name='surfchen'”。這樣的語句,在一個系統裡,很有可能只在一個用戶登陸的時候被使用。每個用戶的登陸所用到的查詢,都是不一樣的SQL 文本,QC在這裡就幾乎不起作用了,因為緩存的數據幾乎是不會被用到的,它們只會在內存裡占地方。

存儲塊

在本節裡“存儲塊”和“block”是同一個意思

QC緩存一個查詢結果的時候,一般情況下不是一次性地分配足夠多的內存來緩存結果的。而是在查詢結果獲得的過程中,逐塊存儲。當一個存儲塊被填滿之 後,一個新的存儲塊將會被創建,並分配內存(allocate)。單個存儲塊的內存分配大小通過query_cache_min_res_unit參數控 制,默認為4KB。最後一個存儲塊,如果不能被全部利用,那麼沒使用的內存將會被釋放。如果被緩存的結果很大,那麼會可能會導致分配內存操作太頻繁,系統 系能也隨之下降;而如果被緩存的結果都很小,那麼可能會導致內存碎片過多,這些碎片如果太小,就很有可能不能再被分配使用。

除了查詢結果需要存儲塊之外,每個SQL文本也需要一個存儲塊,而涉及到的表也需要一個存儲塊(表的存儲塊是所有線程共享的,每個表只需要一個存儲 塊)。存儲塊總數量=查詢結果數量*2+涉及的數據庫教程表數量。也就是說,第一個緩存生成的時候,至少需要三個存儲塊:表信息存儲塊,SQL文本存儲塊,查 詢結果存儲塊。而第二個查詢如果用的是同一個表,那麼最少只需要兩個存儲塊:SQL文本存儲塊,查詢結果存儲塊。

通過觀察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每個緩存結果占用的存儲塊。它們的 比例如果接近1:2,則說明當前的query_cache_min_res_unit參數已經足夠大了。如果Qcache_total_blocks比 Qcache_queries_in_cache多很多,則需要增加query_cache_min_res_unit的大小。

Qcache_queries_in_cache*query_cache_min_res_unit(sql文本和表信息所在的block占用的 內存很小,可以忽略)如果遠遠大於query_cache_size-Qcache_free_memory,那麼可以嘗試減小 query_cache_min_res_unit的值。

調整大小

如果Qcache_lowmem_prunes增長迅速,意味著很多緩存因為內存不夠而被釋放,而不是因為相關表被更新。嘗試加大query_cache_size,盡量使Qcache_lowmem_prunes零增長。

啟動參數

show variables like 'query_cache%'可以看到這些信息。

query_cache_limit

如果單個查詢結果大於這個值,則不Cache

query_cache_size

分 配給QC的內存。如果設為0,則相當於禁用QC。要注意QC必須使用大約40KB來存儲它的結構,如果設定小於40KB,則相當於禁用QC。QC存儲的最 小單位是1024 byte,所以如果你設定了一個不是1024的倍數的值,這個值會被四捨五入到最接近當前值的等於1024的倍數的值。

query_cache_type

0 完全禁止QC,不受SQL語句控制(另外可能要注意的是,即使這裡禁用,上面一個參數所設定的內存大小還是會被分配);1啟用QC,可以在SQL語句使用SQL_NO_CACHE禁用;2可以在SQL語句使用SQL_CACHE啟用。

query_cache_min_res_unit

每次給QC結果分配內存的大小

狀態

show status like 'Qcache%'可以看到這些信息。

Qcache_free_blocks

當一個表被更新之後,和它相關的cache blocks將被free。但是這個block依然可能存在隊列中,除非是在隊列的尾部。這些blocks將會被統計到這個值來。可以用FLUSH QUERY CACHE語句來清空free blocks。

Qcache_free_memory

可用內存,如果很小,考慮增加query_cache_size

Qcache_hits

自mysql進程啟動起,cache的命中數量

Qcache_inserts

自mysql進程啟動起,被增加進QC的數量

Qcache_lowmem_prunes

由於內存過少而導致QC被刪除的條數。加大query_cache_size,盡可能保持這個值0增長。

Qcache_not_cached

自mysql進程啟動起,沒有被cache的只讀查詢數量(包括select,show,use,desc等)

Qcache_queries_in_cache

當前被cache的SQL數量

Qcache_total_blocks

在 QC中的blocks數。一個query可能被多個blocks存儲,而這幾個blocks中的最後一個,未用滿的內存將會被釋放掉。例如一個QC結果要 占6KB內存,如果query_cache_min_res_unit是4KB,則最後將會生成3個blocks,第一個block用來存儲sql語句文 本,這個不會被統計到query+cache_size裡,第二個block為4KB,第三個block為2KB(先allocate4KB,然後釋放多 馀的2KB)。每個表,當第一個和它有關的SQL查詢被CACHE的時候,會使用一個block來存儲表信息。也就是說,block會被用在三處地方:表 信息,SQL文本,查詢結果。

排序緩沖

當一個查詢需要對結果進行排序的時候,MySQL會分配一定的內存用來排序。這個內存大小由sort_buffer_size來控制。記得,這個參數是針對每個查詢的,而不是所有查詢總共可分配的量。

如果sort_buffer_size不夠大,排序的結果將會被分段寫入臨時文件裡。每次結束之後再把文件中的排序結果拿出來合並,進行再次排序, 直到得出最後結果。sort_buffer_size越小,合並的次數就越多。合並次數可以通過狀態變量Sort_merge_passes獲得。理論 上,Sort_merge_passes越小,排序越快。但是在實際應用中可能並非如此。sort_buffer_size如何設置需要根據實際運行環境 來進行測試。如果實在不知道如何測試,那麼就設到使Sort_merge_passes為0吧。

read_buffer_size read_rnd_buffer_size join_buffer_size thread_cache

字段選擇

從二進制角度考慮

select col1,col2 from table PROCEDURE ANALYSE();,這條語句可以根據當前表的內容來給出一個字段類型的推薦。

MyISAM

key_buffer_size

CACHE INDEX Syntax

innodb

innodb_buffer_pool_size

這是和innodb有關的最重要的一個參數。這個參數指定了innodb緩存池的大小。這個緩存池被用來存儲

innodb_file_per_table innodb_additional_mem_pool_size=80M innodb_log_file_size=1G innodb_log_buffer_size=16M innodb_flush_method=O_DIRECT

(max_connections * (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size)) + key_buffer + innodb_bufer_pool_size + query_cache + tmp_table_size

系統相關

linux:

echo -n 0 >/proc/sys/vm/swappiness

huge page

memlock

優化工具

mysqltuner

copyright © 萬盛學電腦網 all rights reserved