Mysql 的邏輯架構(gòu)
MySQL由以下幾部分組成:
- 連接池組件
- 管理服務(wù)和工具組件
- SQL接口組件
- 查詢(xún)分析器組件
- 優(yōu)化器組件
- 緩沖(Cache)組件
- 插件式存儲(chǔ)引擎
- 物理文件
MySQL執(zhí)行一個(gè)查詢(xún)的過(guò)程
如圖:
- 客戶(hù)端發(fā)送一個(gè)查詢(xún)給服務(wù)器龄糊。
- 服務(wù)器先檢查查詢(xún)緩存椅您,如果命中傻寂,則直接返回緩存中的結(jié)果鳍寂。如果沒(méi)有沒(méi)有命中咐旧,則進(jìn)入下一階段(解析器)响蕴。
- 服務(wù)器由解析器檢查sql語(yǔ)法是否正確焕毫,然后由預(yù)處理器檢查sql中的表和字段是否存在沛善,最后由查詢(xún)優(yōu)器生成執(zhí)行計(jì)劃航揉。這一步很耗資源。
- mysql根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃路呜,調(diào)用存儲(chǔ)引擎的API來(lái)執(zhí)行查詢(xún)迷捧。
- 將結(jié)果返回給客戶(hù)端。
MySQL客戶(hù)端和服務(wù)器之間的通信
在mysql服務(wù)器和客戶(hù)端之間的通信時(shí)“半雙工”的胀葱。就是在同一時(shí)刻要么由客戶(hù)向Mysql服務(wù)器發(fā)送數(shù)據(jù)漠秋,要么由MySQL服務(wù)器向客戶(hù)端發(fā)送數(shù)據(jù)。就像來(lái)回拋球游戲抵屿,任何時(shí)候只有一個(gè)人能控制球庆锦,而且只有控制球的人才能將球拋出去(發(fā)送消息)。
當(dāng)客戶(hù)端從MySQL服務(wù)器獲取數(shù)據(jù)時(shí)轧葛,看起來(lái)像像是客戶(hù)端向MySQL服務(wù)器拉取數(shù)據(jù)搂抒,但實(shí)際上是MySQL服務(wù)器向客戶(hù)端推送數(shù)據(jù)艇搀。客戶(hù)端不斷的接受從服務(wù)推送過(guò)來(lái)的數(shù)據(jù)求晶,客戶(hù)端也沒(méi)有辦法讓服務(wù)器停下來(lái)焰雕。
大多數(shù)連接MySQL的庫(kù)函數(shù)都可以獲取全部的結(jié)果集并緩存到內(nèi)存中。MySQL通常需要等到數(shù)據(jù)全部推送給客戶(hù)端后才能釋放這條語(yǔ)句查詢(xún)所暫用的資源芳杏。矩屁、
查詢(xún)緩存
對(duì)執(zhí)行計(jì)劃的緩存
對(duì)于很多的數(shù)據(jù)庫(kù)系統(tǒng)都能夠緩存執(zhí)行計(jì)劃,對(duì)于完全相同的sql爵赵,可以使用已經(jīng)已經(jīng)存在的執(zhí)行計(jì)劃吝秕,從而跳過(guò)解析和生成執(zhí)行計(jì)劃的過(guò)程。
緩存查詢(xún)計(jì)劃我們通過(guò)JDBC的PreparedStatement進(jìn)行說(shuō)明空幻。
PreparedStatement是用來(lái)執(zhí)行SQL查詢(xún)語(yǔ)句的API之一烁峭,Java提供了Statement、PreparedStatement和CallableStatement三種方式來(lái)執(zhí)行查詢(xún)語(yǔ)句秕铛,其中Statement用于通用查詢(xún)约郁,PreparedStatement用于執(zhí)行參數(shù)化查詢(xún),而CallableStatement則是用于存儲(chǔ)過(guò)程但两。
Mysql執(zhí)行計(jì)劃的生成會(huì)占用相當(dāng)多的CPU棍现。理想的情況是,當(dāng)我們多次發(fā)送一個(gè)statement到數(shù)據(jù)庫(kù)镜遣,數(shù)據(jù)庫(kù)應(yīng)該對(duì)statement的存取方案進(jìn)行重用己肮。如果方案曾經(jīng)被生成過(guò)的話(huà),這將減少CPU的使用率悲关。
數(shù)據(jù)庫(kù)已經(jīng)具有了類(lèi)似的功能谎僻。它們通常會(huì)用如下方法對(duì)statement進(jìn)行緩存。使用statement本身作為key并將存取方案存入與statement對(duì)應(yīng)的緩存中寓辱。這樣數(shù)據(jù)庫(kù)引擎就可以對(duì)曾經(jīng)執(zhí)行過(guò)的statements中的存取方案進(jìn)行重用艘绍。舉個(gè)例子,如果我們發(fā)送一條包含SELECT a, b FROM t WHERE c = 2的statement到數(shù)據(jù)庫(kù)秫筏,然后首先會(huì)將存取方案進(jìn)行緩存诱鞠。當(dāng)我們?cè)俅伟l(fā)送相同的statement時(shí),數(shù)據(jù)庫(kù)會(huì)對(duì)先前使用過(guò)的存取方案進(jìn)行重用这敬,這樣就降低了CPU的開(kāi)銷(xiāo)航夺。
注意,這里使用了整個(gè)statement為key崔涂。也就是說(shuō)阳掐,如果我們發(fā)送一個(gè)包含SELECT a, b FROM t WHERE c = 3的statement的話(huà),緩存中不會(huì)沒(méi)有與之對(duì)應(yīng)的存取方案。這是因?yàn)椤癱=3”與曾經(jīng)被緩存過(guò)的“c=2”不同缭保。所以汛闸,舉個(gè)例子:
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();
}
在這里緩存不會(huì)被使用,因?yàn)槊恳淮蔚紩?huì)發(fā)送一條包含不同SQL語(yǔ)句的statement給數(shù)據(jù)庫(kù)艺骂。并且每一次迭代都會(huì)生成一個(gè)新的存取方案≈罾希現(xiàn)在讓我們來(lái)看看下一段代碼:
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();
}
這樣就具有了更好的效率,這個(gè)statement發(fā)送給數(shù)據(jù)庫(kù)的是一條帶有參數(shù)“钳恕?”的SQL語(yǔ)句孕锄。這樣每次迭代會(huì)發(fā)送相同的statement到數(shù)據(jù)庫(kù),只是參數(shù)“c=?”不同苞尝。這種方法允許數(shù)據(jù)庫(kù)重用statement的存取方案,這樣就具有了更好的效率宦芦。這可以讓你的應(yīng)用程序速度更快宙址,并且使用更少的CPU,這樣數(shù)據(jù)庫(kù)服務(wù)器就可以為更多的人提供服務(wù)调卑。
- PreparedStatement是預(yù)編譯的,對(duì)于批量處理可以大大提高效率. 也叫JDBC存儲(chǔ)過(guò)程
- 使用 Statement 對(duì)象抡砂。在對(duì)數(shù)據(jù)庫(kù)只執(zhí)行一次性存取的時(shí)侯,用 Statement 對(duì)象進(jìn)行處理恬涧。PreparedStatement 對(duì)象的開(kāi)銷(xiāo)比Statement大注益,對(duì)于一次性操作并不會(huì)帶來(lái)額外的好處。
- statement每次執(zhí)行sql語(yǔ)句溯捆,相關(guān)數(shù)據(jù)庫(kù)都要執(zhí)行sql語(yǔ)句的編譯丑搔,preparedstatement是預(yù)編譯得,preparedstatement支持批處理
- PreparedStatement可以防止SQL注入式攻擊
對(duì)完整的select查詢(xún)結(jié)果的緩存
查詢(xún)緩存的工作機(jī)制
Mysql 判斷是否命中緩存的辦法很簡(jiǎn)單,首先會(huì)將要緩存的結(jié)果放在引用表中提揍,然后使用查詢(xún)語(yǔ)句啤月,數(shù)據(jù)庫(kù)名稱(chēng),客戶(hù)端協(xié)議的版本等因素算出一個(gè)hash值劳跃,這個(gè)hash值與引用表中的結(jié)果相關(guān)聯(lián)谎仲。如果在執(zhí)行查詢(xún)時(shí),根據(jù)一些相關(guān)的條件算出的hash值能與引用表中的數(shù)據(jù)相關(guān)聯(lián)刨仑,則表示查詢(xún)命中通過(guò)have_query_cache服務(wù)器系統(tǒng)變量指示查詢(xún)緩存是否可用:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
變量名 | 值 |
---|---|
Variable_name | Value |
have_query_cache | YES |
為了監(jiān)視查詢(xún)緩存性能郑诺,使用SHOW STATUS查看緩存狀態(tài)變量:
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 |
查詢(xún)緩存機(jī)制失效的場(chǎng)景
如果查詢(xún)語(yǔ)句中包含一些不確定因素時(shí)(例如包含函數(shù)Current()),該查詢(xún)不會(huì)被緩存,不確定因素主要包含以下情況杉武。
- 引用了一些返回值不確定的函數(shù)
函數(shù)名 | 函數(shù)名 | 函數(shù)名 | 函數(shù)名 |
---|---|---|---|
BENCHMARK() | CONNECTION_ID() | CURDATE() | CURRENT_DATE() |
CURRENT_TIME() | CURRENT_TIMESTAMP() | CURTIME() | DATABASE() |
帶一個(gè)參數(shù)的ENCRYPT() | FOUND_ROWS() | GET_LOCK() | LAST_INSERT_ID() |
LOAD_FILE() | MASTER_POS_WAIT() | NOW() | RAND() |
RELEASE_LOCK() | SYSDATE() | 不帶參數(shù)的UNIX_TIMESTAMP() | USER() |
- 引用自定義函數(shù)(UDFs)辙诞。
- 引用自定義變量
- 引用mysql系統(tǒng)數(shù)據(jù)庫(kù)中的表。
- 引用臨時(shí)表
- 引用存儲(chǔ)函數(shù)
- 任何包含列級(jí)別權(quán)限的表
- 不使用任何表
- 下面方式中的任何一種:
語(yǔ)句 | 是否緩存 |
---|---|
SELECT ...IN SHARE MODE | 否 |
SELECT ...FOR UPDATE | 否 |
SELECT ...INTO OUTFILE ... | 否 |
SELECT ...INTO DUMPFILE ... | 否 |
SELECT * FROM ...WHERE autoincrement_col IS NULL | 否 |
查詢(xún)緩存的額外的消耗
如果使用查詢(xún)緩存轻抱,在進(jìn)行讀寫(xiě)操作時(shí)會(huì)帶來(lái)額外的資源消耗倘要,消耗主要體現(xiàn)在以下幾個(gè)方面:
- 查詢(xún)的時(shí)候會(huì)檢查是否命中緩存,這個(gè)消耗相對(duì)較小
- 如果沒(méi)有命中查詢(xún)緩存,MYSQL會(huì)判斷該查詢(xún)是否可以被緩存封拧,而且系統(tǒng)中還沒(méi)有對(duì)應(yīng)的緩存志鹃,則會(huì)將其結(jié)果寫(xiě)入查詢(xún)緩存
- 如果一個(gè)表被更改了,那么使用那個(gè)表的所有緩沖查詢(xún)將不再有效泽西,并且從緩沖區(qū)中移出曹铃。這包括那些映射到改變了的表的使用MERGE表的查詢(xún)。一個(gè)表可以被許多類(lèi)型的語(yǔ)句更改捧杉,例如INSERT陕见、UPDATE、DELETE味抖、TRUNCATE评甜、ALTER TABLE、DROP TABLE或DROP DATABASE仔涩。
對(duì)于InnoDB而言忍坷,事物的一些特性還會(huì)限制查詢(xún)緩存的使用。當(dāng)在事物A中修改了B表時(shí)熔脂,因?yàn)樵谑挛锾峤恢芭逖校瑢?duì)B表的修改對(duì)其他的事物而言是不可見(jiàn)的。為了保證緩存結(jié)果的正確性霞揉,InnoDB采取的措施讓所有涉及到該B表的查詢(xún)?cè)谑挛顰提交之前是不可緩存的旬薯。如果A事物長(zhǎng)時(shí)間運(yùn)行,會(huì)嚴(yán)重影響查詢(xún)緩存的命中率
查詢(xún)緩存的空間不要設(shè)置的太大适秩。
因?yàn)椴樵?xún)緩存是靠一個(gè)全局鎖操作保護(hù)的绊序,如果查詢(xún)緩存配置的內(nèi)存比較大且里面存放了大量的查詢(xún)結(jié)果,當(dāng)查詢(xún)緩存失效的時(shí)候秽荞,會(huì)長(zhǎng)時(shí)間的持有這個(gè)全局鎖政模。因?yàn)椴樵?xún)緩存的命中檢測(cè)操作以及緩存失效檢測(cè)也都依賴(lài)這個(gè)全局鎖,所以可能會(huì)導(dǎo)致系統(tǒng)僵死的情況蚂会。
查詢(xún)緩存的配置參數(shù)
MYSQL提供了一些參數(shù)來(lái)控制查詢(xún)緩存的行為淋样,參數(shù)如下:
- query_cache_limit
MYSQL能夠緩存的最大查詢(xún)結(jié)果,查詢(xún)結(jié)果大于該值時(shí)不會(huì)被緩存胁住。默認(rèn)值是1048576(1MB)趁猴。如果某個(gè)查詢(xún)的結(jié)果超出了這個(gè)值,Qcache_not_cached的值會(huì)加1彪见,如果某個(gè)操作總是超出可以考慮在SQL中加上SQL_NO_CACHE來(lái)避免額外的消耗
- query_cache_min_res_unit
查詢(xún)緩存分配的最小塊的大小(字節(jié))儡司。 默認(rèn)值是4096(4KB)
- query_cache_size
為緩存查詢(xún)結(jié)果分配的內(nèi)存的數(shù)量,單位是字節(jié)余指,且數(shù)值必須是1024的整數(shù)倍捕犬。默認(rèn)值是0跷坝,即禁用查詢(xún)緩存。請(qǐng)注意即使query_cache_type設(shè)置為0也將分配此數(shù)量的內(nèi)存碉碉。
- query_cache_type
設(shè)置查詢(xún)緩存類(lèi)型,默認(rèn)設(shè)為ON柴钻。設(shè)置GLOBAL值可以設(shè)置后面的所有客戶(hù)端連接的類(lèi)型」噶福客戶(hù)端可以設(shè)置SESSION值以影響他們自己對(duì)查詢(xún)緩存的使用贴届。
下面的表顯示了可能的值:
選項(xiàng) | 描述 |
---|---|
0或OFF | 不要緩存或查詢(xún)結(jié)果。請(qǐng)注意這樣不會(huì)取消分配的查詢(xún)緩存區(qū)蜡吧。要想取消毫蚓,你應(yīng)將query_cache_size設(shè)置為0。 |
1或ON | 緩存除了以SELECT SQL_NO_CACHE開(kāi)頭的所有查詢(xún)結(jié)果昔善。 |
2或DEMAND | 只緩存以SELECT SQL_NO_CACHE開(kāi)頭的查詢(xún)結(jié)果元潘。 |
- query_cache_wlock_invalidate
一般情況,當(dāng)客戶(hù)端對(duì)MyISAM表進(jìn)行WRITE鎖定時(shí)君仆,如果查詢(xún)結(jié)果位于查詢(xún)緩存中翩概,則其它客戶(hù)端未被鎖定,可以對(duì)該表進(jìn)行查詢(xún)袖订。將該變量設(shè)置為1,則可以對(duì)表進(jìn)行WRITE鎖定嗅虏,使查詢(xún)緩存內(nèi)所有對(duì)該表進(jìn)行的查詢(xún)變得非法洛姑。這樣當(dāng)鎖定生效時(shí),可以強(qiáng)制其它試圖訪(fǎng)問(wèn)表的客戶(hù)端來(lái)等待皮服。
查詢(xún)緩存的優(yōu)化流程
當(dāng)開(kāi)啟了查詢(xún)緩存的功能后楞艾,可以通過(guò)一些參數(shù)以及狀態(tài)值來(lái)觀察查詢(xún)緩存的使用情況。
流程以及涉及到的參數(shù)參見(jiàn)下圖:
查詢(xún)緩存的優(yōu)化
除了上圖提到的一些優(yōu)化策略外龄广,還可以通過(guò)下面的措施來(lái)提高查詢(xún)緩存的效率
- 盡量用小表的簡(jiǎn)單替代大表的復(fù)雜查詢(xún)
- 盡量用批量寫(xiě)入取代單條寫(xiě)入
- 控制query_cache_size的大小硫眯,甚至是禁用查詢(xún)緩存
- 通過(guò)DEMAND+SQL_CACHE/SQL_NO_CACHE來(lái)靈活控制某個(gè)select是否需要進(jìn)行緩存
- 對(duì)于寫(xiě)密集型的應(yīng)用,直接禁用查詢(xún)緩存