MySQL查詢(xún)的執(zhí)行過(guò)程

Mysql 的邏輯架構(gòu)

image.png

MySQL由以下幾部分組成:

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

MySQL執(zhí)行一個(gè)查詢(xún)的過(guò)程

如圖:


image
  1. 客戶(hù)端發(fā)送一個(gè)查詢(xún)給服務(wù)器龄糊。
  2. 服務(wù)器先檢查查詢(xún)緩存椅您,如果命中傻寂,則直接返回緩存中的結(jié)果鳍寂。如果沒(méi)有沒(méi)有命中咐旧,則進(jìn)入下一階段(解析器)响蕴。
  3. 服務(wù)器由解析器檢查sql語(yǔ)法是否正確焕毫,然后由預(yù)處理器檢查sql中的表和字段是否存在沛善,最后由查詢(xún)優(yōu)器生成執(zhí)行計(jì)劃航揉。這一步很耗資源。
  4. mysql根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃路呜,調(diào)用存儲(chǔ)引擎的API來(lái)執(zhí)行查詢(xún)迷捧。
  5. 將結(jié)果返回給客戶(hù)端。

MySQL客戶(hù)端和服務(wù)器之間的通信

  1. 在mysql服務(wù)器和客戶(hù)端之間的通信時(shí)“半雙工”的胀葱。就是在同一時(shí)刻要么由客戶(hù)向Mysql服務(wù)器發(fā)送數(shù)據(jù)漠秋,要么由MySQL服務(wù)器向客戶(hù)端發(fā)送數(shù)據(jù)。就像來(lái)回拋球游戲抵屿,任何時(shí)候只有一個(gè)人能控制球庆锦,而且只有控制球的人才能將球拋出去(發(fā)送消息)。

  2. 當(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)焰雕。

  3. 大多數(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ù)调卑。

  1. PreparedStatement是預(yù)編譯的,對(duì)于批量處理可以大大提高效率. 也叫JDBC存儲(chǔ)過(guò)程
  2. 使用 Statement 對(duì)象抡砂。在對(duì)數(shù)據(jù)庫(kù)只執(zhí)行一次性存取的時(shí)侯,用 Statement 對(duì)象進(jìn)行處理恬涧。PreparedStatement 對(duì)象的開(kāi)銷(xiāo)比Statement大注益,對(duì)于一次性操作并不會(huì)帶來(lái)額外的好處。
  3. statement每次執(zhí)行sql語(yǔ)句溯捆,相關(guān)數(shù)據(jù)庫(kù)都要執(zhí)行sql語(yǔ)句的編譯丑搔,preparedstatement是預(yù)編譯得,preparedstatement支持批處理
  4. 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ì)被緩存,不確定因素主要包含以下情況杉武。

  1. 引用了一些返回值不確定的函數(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()
  1. 引用自定義函數(shù)(UDFs)辙诞。
  2. 引用自定義變量
  3. 引用mysql系統(tǒng)數(shù)據(jù)庫(kù)中的表。
  4. 引用臨時(shí)表
  5. 引用存儲(chǔ)函數(shù)
  6. 任何包含列級(jí)別權(quán)限的表
  7. 不使用任何表
  8. 下面方式中的任何一種:
語(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è)方面:

  1. 查詢(xún)的時(shí)候會(huì)檢查是否命中緩存,這個(gè)消耗相對(duì)較小
  2. 如果沒(méi)有命中查詢(xún)緩存,MYSQL會(huì)判斷該查詢(xún)是否可以被緩存封拧,而且系統(tǒng)中還沒(méi)有對(duì)應(yīng)的緩存志鹃,則會(huì)將其結(jié)果寫(xiě)入查詢(xún)緩存
  3. 如果一個(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ù)如下:

  1. 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)避免額外的消耗

  1. query_cache_min_res_unit

查詢(xún)緩存分配的最小塊的大小(字節(jié))儡司。 默認(rèn)值是4096(4KB)

  1. 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)存碉碉。

  1. 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é)果元潘。
  1. 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)下圖:


image

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

除了上圖提到的一些優(yōu)化策略外龄广,還可以通過(guò)下面的措施來(lái)提高查詢(xún)緩存的效率

  1. 盡量用小表的簡(jiǎn)單替代大表的復(fù)雜查詢(xún)
  2. 盡量用批量寫(xiě)入取代單條寫(xiě)入
  3. 控制query_cache_size的大小硫眯,甚至是禁用查詢(xún)緩存
  4. 通過(guò)DEMAND+SQL_CACHE/SQL_NO_CACHE來(lái)靈活控制某個(gè)select是否需要進(jìn)行緩存
  5. 對(duì)于寫(xiě)密集型的應(yīng)用,直接禁用查詢(xún)緩存
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末择同,一起剝皮案震驚了整個(gè)濱河市两入,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌敲才,老刑警劉巖裹纳,帶你破解...
    沈念sama閱讀 223,002評(píng)論 6 519
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場(chǎng)離奇詭異紧武,居然都是意外死亡剃氧,警方通過(guò)查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,357評(píng)論 3 400
  • 文/潘曉璐 我一進(jìn)店門(mén)阻星,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)朋鞍,“玉大人,你說(shuō)我怎么就攤上這事±乃郑” “怎么了更舞?”我有些...
    開(kāi)封第一講書(shū)人閱讀 169,787評(píng)論 0 365
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)恨狈。 經(jīng)常有香客問(wèn)我疏哗,道長(zhǎng),這世上最難降的妖魔是什么禾怠? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 60,237評(píng)論 1 300
  • 正文 為了忘掉前任返奉,我火速辦了婚禮,結(jié)果婚禮上吗氏,老公的妹妹穿的比我還像新娘芽偏。我一直安慰自己,他們只是感情好弦讽,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,237評(píng)論 6 398
  • 文/花漫 我一把揭開(kāi)白布污尉。 她就那樣靜靜地躺著,像睡著了一般往产。 火紅的嫁衣襯著肌膚如雪被碗。 梳的紋絲不亂的頭發(fā)上,一...
    開(kāi)封第一講書(shū)人閱讀 52,821評(píng)論 1 314
  • 那天仿村,我揣著相機(jī)與錄音锐朴,去河邊找鬼。 笑死蔼囊,一個(gè)胖子當(dāng)著我的面吹牛焚志,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播畏鼓,決...
    沈念sama閱讀 41,236評(píng)論 3 424
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼酱酬,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼!你這毒婦竟也來(lái)了云矫?” 一聲冷哼從身側(cè)響起膳沽,我...
    開(kāi)封第一講書(shū)人閱讀 40,196評(píng)論 0 277
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎让禀,沒(méi)想到半個(gè)月后贵少,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,716評(píng)論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡堆缘,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,794評(píng)論 3 343
  • 正文 我和宋清朗相戀三年滔灶,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片吼肥。...
    茶點(diǎn)故事閱讀 40,928評(píng)論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡录平,死狀恐怖麻车,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情斗这,我是刑警寧澤槽片,帶...
    沈念sama閱讀 36,583評(píng)論 5 351
  • 正文 年R本政府宣布感论,位于F島的核電站拥褂,受9級(jí)特大地震影響镇匀,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜免钻,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,264評(píng)論 3 336
  • 文/蒙蒙 一彼水、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧极舔,春花似錦凤覆、人聲如沸。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 32,755評(píng)論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至渤刃,卻和暖如春拥峦,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背卖子。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 33,869評(píng)論 1 274
  • 我被黑心中介騙來(lái)泰國(guó)打工略号, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人揪胃。 一個(gè)月前我還...
    沈念sama閱讀 49,378評(píng)論 3 379
  • 正文 我出身青樓璃哟,卻偏偏與公主長(zhǎng)得像氛琢,于是被迫代替她去往敵國(guó)和親喊递。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,937評(píng)論 2 361

推薦閱讀更多精彩內(nèi)容