程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 高性能MySql進化論(十三):查詢緩存機制

高性能MySql進化論(十三):查詢緩存機制

編輯：MySQL綜合教程

對於很多的數據庫系統都能夠緩存執行計劃，對於完全相同的sql, 可以使用已經已經存在的執行計劃，從而跳過解析和生成執行計劃的過程。MYSQL以及Oracle提供了更為高級的查詢結果緩存功能，對於完全相同的SQL (字符串完全相同且大小寫敏感) 可以執行返回查詢結果。本文主要介紹MYSQL 查詢緩存的一些特性，Oracle query cache可以參考http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html

如果你有一個不經常改變的表並且服務器收到該表的大量相同查詢，查詢緩存在這樣的應用環境中十分有用。對於許多Web服務器來說存在這種典型情況，它根據數據庫內容生成大量的動態頁面。

1 查詢緩存的工作機制

Mysql 判斷是否命中緩存的辦法很簡單，首先會將要緩存的結果放在引用表中，然後使用查詢語句，數據庫名稱，客戶端協議的版本等因素算出一個hash值，這個hash值與引用表中的結果相關聯。如果在執行查詢時，根據一些相關的條件算出的hash值能與引用表中的數據相關聯，則表示查詢命中

通過have_query_cache服務器系統變量指示查詢緩存是否可用：

mysql> SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| have_query_cache | YES   |

+------------------+-------+

為了監視查詢緩存性能，使用SHOW STATUS查看緩存狀態變量：

mysql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+--------+

|變量名                   |值 |

+-------------------------+--------+

| Qcache_free_blocks      | 36     |

| Qcache_free_memory      | 138488 |

| Qcache_hits             | 79570  |

| Qcache_inserts          | 27087  |

| Qcache_lowmem_prunes    | 3114   |

| Qcache_not_cached       | 22989  |

| Qcache_queries_in_cache | 415    |

| Qcache_total_blocks     | 912    |

+-------------------------+--------+

1.1 查詢緩存機制失效的場景

先不論查詢緩存機制有利有弊，先看看哪些場景下會導致緩存機制失效

1. 如果查詢語句中包含一些不確定因素時（例如包含函數Current()），該查詢不會被緩存,不確定因素主要包含以下情況

· 引用了一些返回值不確定的函數

BENCHMARK()

CONNECTION_ID()

CURDATE()

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

CURTIME()

DATABASE()

帶一個參數的ENCRYPT()

FOUND_ROWS()

GET_LOCK()

LAST_INSERT_ID()

LOAD_FILE()

MASTER_POS_WAIT()

NOW()

RAND()

RELEASE_LOCK()

SYSDATE()

不帶參數的UNIX_TIMESTAMP()

USER()

· 引用自定義函數(UDFs)。

· 引用自定義變量。

· 引用mysql系統數據庫中的表。

· 下面方式中的任何一種：

SELECT ...IN SHARE MODE

SELECT ...FOR UPDATE

SELECT ...INTO OUTFILE ...

SELECT ...INTO DUMPFILE ...

SELECT * FROM ...WHERE autoincrement_col IS NULL

· 被作為編寫好的語句，即使沒有使用占位符。例如，下面使用的查詢：

char *my_sql_stmt = "SELECT a，b FROM table_c";

   /* ...*/

mysql_stmt_prepare(stmt，my_sql_stmt，strlen(my_sql_stmt));

不被緩存。

· 使用TEMPORARY表。

· 不使用任何表。

· 用戶有某個表的列級別權限。

1.2 額外的消耗

如果使用查詢緩存，在進行讀寫操作時會帶來額外的資源消耗，消耗主要體現在以下幾個方面

· 查詢的時候會檢查是否命中緩存，這個消耗相對較小

· 如果沒有命中查詢緩存，MYSQL會判斷該查詢是否可以被緩存，而且系統中還沒有對應的緩存，則會將其結果寫入查詢緩存

· 如果一個表被更改了，那麼使用那個表的所有緩沖查詢將不再有效，並且從緩沖區中移出。這包括那些映射到改變了的表的使用MERGE表的查詢。一個表可以被許多類型的語句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。

對於InnoDB而言，事物的一些特性還會限制查詢緩存的使用。當在事物A中修改了B表時，因為在事物提交之前，對B表的修改對其他的事物而言是不可見的。為了保證緩存結果的正確性，InnoDB采取的措施讓所有涉及到該B表的查詢在事物A提交之前是不可緩存的。如果A事物長時間運行，會嚴重影響查詢緩存的命中率

查詢緩存的空間不要設置的太大。

因為查詢緩存是靠一個全局鎖操作保護的，如果查詢緩存配置的內存比較大且裡面存放了大量的查詢結果，當查詢緩存失效的時候，會長時間的持有這個全局鎖。因為查詢緩存的命中檢測操作以及緩存失效檢測也都依賴這個全局鎖，所以可能會導致系統僵死的情況