Mysql 的邏輯架構

image.png

MySQL由以下幾部分組成：

• 連接池組件
• 管理服務和工具組件
• SQL接口組件
• 查詢分析器組件
• 優(yōu)化器組件
• 緩沖（Cache）組件
• 插件式存儲引擎
• 物理文件

MySQL執(zhí)行一個查詢的過程

如圖：

image

1. 客戶端發(fā)送一個查詢給服務器。
2. 服務器先檢查查詢緩存，如果命中，則直接返回緩存中的結果。如果沒有沒有命中，則進入下一階段（解析器）。
3. 服務器由解析器檢查sql語法是否正確，然后由預處理器檢查sql中的表和字段是否存在，最后由查詢優(yōu)器生成執(zhí)行計劃。這一步很耗資源。
4. mysql根據(jù)優(yōu)化器生成的執(zhí)行計劃，調(diào)用存儲引擎的API來執(zhí)行查詢。
5. 將結果返回給客戶端。

MySQL客戶端和服務器之間的通信

1. 在mysql服務器和客戶端之間的通信時“半雙工”的。就是在同一時刻要么由客戶向Mysql服務器發(fā)送數(shù)據(jù)，要么由MySQL服務器向客戶端發(fā)送數(shù)據(jù)。就像來回拋球游戲，任何時候只有一個人能控制球，而且只有控制球的人才能將球拋出去（發(fā)送消息）。

2. 當客戶端從MySQL服務器獲取數(shù)據(jù)時，看起來像像是客戶端向MySQL服務器拉取數(shù)據(jù)，但實際上是MySQL服務器向客戶端推送數(shù)據(jù)?？蛻舳瞬粩嗟慕邮軓姆胀扑瓦^來的數(shù)據(jù)，客戶端也沒有辦法讓服務器停下來。

3. 大多數(shù)連接MySQL的庫函數(shù)都可以獲取全部的結果集并緩存到內(nèi)存中。MySQL通常需要等到數(shù)據(jù)全部推送給客戶端后才能釋放這條語句查詢所暫用的資源。、

查詢緩存

對執(zhí)行計劃的緩存

對于很多的數(shù)據(jù)庫系統(tǒng)都能夠緩存執(zhí)行計劃，對于完全相同的sql，可以使用已經(jīng)已經(jīng)存在的執(zhí)行計劃，從而跳過解析和生成執(zhí)行計劃的過程。

緩存查詢計劃我們通過JDBC的PreparedStatement進行說明。
PreparedStatement是用來執(zhí)行SQL查詢語句的API之一，Java提供了Statement、PreparedStatement和CallableStatement三種方式來執(zhí)行查詢語句，其中Statement用于通用查詢，PreparedStatement用于執(zhí)行參數(shù)化查詢，而CallableStatement則是用于存儲過程。

Mysql執(zhí)行計劃的生成會占用相當多的CPU。理想的情況是，當我們多次發(fā)送一個statement到數(shù)據(jù)庫，數(shù)據(jù)庫應該對statement的存取方案進行重用。如果方案曾經(jīng)被生成過的話，這將減少CPU的使用率。

數(shù)據(jù)庫已經(jīng)具有了類似的功能。它們通常會用如下方法對statement進行緩存。使用statement本身作為key并將存取方案存入與statement對應的緩存中。這樣數(shù)據(jù)庫引擎就可以對曾經(jīng)執(zhí)行過的statements中的存取方案進行重用。舉個例子，如果我們發(fā)送一條包含SELECT a, b FROM t WHERE c = 2的statement到數(shù)據(jù)庫，然后首先會將存取方案進行緩存。當我們再次發(fā)送相同的statement時，數(shù)據(jù)庫會對先前使用過的存取方案進行重用，這樣就降低了CPU的開銷。

注意，這里使用了整個statement為key。也就是說，如果我們發(fā)送一個包含SELECT a, b FROM t WHERE c = 3的statement的話，緩存中不會沒有與之對應的存取方案。這是因為“c=3”與曾經(jīng)被緩存過的“c=2”不同。所以，舉個例子：

for (int i = 0; i < 1000; i++)  {
    PreparedStatement ps = conn.prepareStatement("select a,b from t where c = " + i);
    ResultSet rs = Ps.executeQuery();
    rs.close();
    ps.close();
}

在這里緩存不會被使用，因為每一次迭代都會發(fā)送一條包含不同SQL語句的statement給數(shù)據(jù)庫。并且每一次迭代都會生成一個新的存取方案?，F(xiàn)在讓我們來看看下一段代碼：

PreparedStatement ps = conn.prepareStatement("select a,b from t where c = ?");
for (int i = 0; i < 1000; i++)  {
    ps.setInt(1, i);
    ResultSet rs = ps.executeQuery();
    rs.close();
    ps.close();
}

這樣就具有了更好的效率，這個statement發(fā)送給數(shù)據(jù)庫的是一條帶有參數(shù)“？”的SQL語句。這樣每次迭代會發(fā)送相同的statement到數(shù)據(jù)庫，只是參數(shù)“c=?”不同。這種方法允許數(shù)據(jù)庫重用statement的存取方案，這樣就具有了更好的效率。這可以讓你的應用程序速度更快，并且使用更少的CPU，這樣數(shù)據(jù)庫服務器就可以為更多的人提供服務。

1. PreparedStatement是預編譯的,對于批量處理可以大大提高效率. 也叫JDBC存儲過程
2. 使用 Statement 對象。在對數(shù)據(jù)庫只執(zhí)行一次性存取的時侯，用 Statement 對象進行處理。PreparedStatement 對象的開銷比Statement大，對于一次性操作并不會帶來額外的好處。
3. statement每次執(zhí)行sql語句，相關數(shù)據(jù)庫都要執(zhí)行sql語句的編譯，preparedstatement是預編譯得,preparedstatement支持批處理
4. PreparedStatement可以防止SQL注入式攻擊

對完整的select查詢結果的緩存

查詢緩存的工作機制

Mysql 判斷是否命中緩存的辦法很簡單，首先會將要緩存的結果放在引用表中，然后使用查詢語句，數(shù)據(jù)庫名稱，客戶端協(xié)議的版本等因素算出一個hash值，這個hash值與引用表中的結果相關聯(lián)。如果在執(zhí)行查詢時，根據(jù)一些相關的條件算出的hash值能與引用表中的數(shù)據(jù)相關聯(lián)，則表示查詢命中通過have_query_cache服務器系統(tǒng)變量指示查詢緩存是否可用：

mysql> SHOW VARIABLES LIKE 'have_query_cache';

為了監(jiān)視查詢緩存性能，使用SHOW STATUS查看緩存狀態(tài)變量：

mysql> SHOW STATUS LIKE 'Qcache%';

查詢緩存機制失效的場景

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

1. 引用了一些返回值不確定的函數(shù)

2. 引用自定義函數(shù)(UDFs)。
3. 引用自定義變量
4. 引用mysql系統(tǒng)數(shù)據(jù)庫中的表。
5. 引用臨時表
6. 引用存儲函數(shù)
7. 任何包含列級別權限的表
8. 不使用任何表
9. 下面方式中的任何一種：

查詢緩存的額外的消耗

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

1. 查詢的時候會檢查是否命中緩存，這個消耗相對較小
2. 如果沒有命中查詢緩存，MYSQL會判斷該查詢是否可以被緩存，而且系統(tǒng)中還沒有對應的緩存，則會將其結果寫入查詢緩存
3. 如果一個表被更改了，那么使用那個表的所有緩沖查詢將不再有效，并且從緩沖區(qū)中移出。這包括那些映射到改變了的表的使用MERGE表的查詢。一個表可以被許多類型的語句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。

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

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

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

查詢緩存的配置參數(shù)

MYSQL提供了一些參數(shù)來控制查詢緩存的行為，參數(shù)如下：

1. query_cache_limit

MYSQL能夠緩存的最大查詢結果，查詢結果大于該值時不會被緩存。默認值是1048576(1MB)。如果某個查詢的結果超出了這個值，Qcache_not_cached的值會加1，如果某個操作總是超出可以考慮在SQL中加上SQL_NO_CACHE來避免額外的消耗

2. query_cache_min_res_unit

查詢緩存分配的最小塊的大小(字節(jié))。 默認值是4096(4KB)

3. query_cache_size

為緩存查詢結果分配的內(nèi)存的數(shù)量，單位是字節(jié)，且數(shù)值必須是1024的整數(shù)倍。默認值是0，即禁用查詢緩存。請注意即使query_cache_type設置為0也將分配此數(shù)量的內(nèi)存。

4. query_cache_type

設置查詢緩存類型,默認設為ON。設置GLOBAL值可以設置后面的所有客戶端連接的類型?？蛻舳丝梢栽O置SESSION值以影響他們自己對查詢緩存的使用。

下面的表顯示了可能的值：

5. query_cache_wlock_invalidate

一般情況，當客戶端對MyISAM表進行WRITE鎖定時，如果查詢結果位于查詢緩存中，則其它客戶端未被鎖定，可以對該表進行查詢。將該變量設置為1，則可以對表進行WRITE鎖定，使查詢緩存內(nèi)所有對該表進行的查詢變得非法。這樣當鎖定生效時，可以強制其它試圖訪問表的客戶端來等待。

查詢緩存的優(yōu)化流程

當開啟了查詢緩存的功能后，可以通過一些參數(shù)以及狀態(tài)值來觀察查詢緩存的使用情況。

流程以及涉及到的參數(shù)參見下圖:

image

查詢緩存的優(yōu)化

除了上圖提到的一些優(yōu)化策略外，還可以通過下面的措施來提高查詢緩存的效率

1. 盡量用小表的簡單替代大表的復雜查詢
2. 盡量用批量寫入取代單條寫入
3. 控制query_cache_size的大小，甚至是禁用查詢緩存
4. 通過DEMAND+SQL_CACHE/SQL_NO_CACHE來靈活控制某個select是否需要進行緩存
5. 對于寫密集型的應用，直接禁用查詢緩存