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
  • 道士緝兇錄：失蹤的賣(mài)姜人
    文/不壞的土叔 我叫張陵，是天一觀的道長(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
  • 惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
    文/花漫 我一把揭開(kāi)白布污尉。 她就那樣靜靜地躺著，像睡著了一般往产。 火紅的嫁衣襯著肌膚如雪被碗。 梳的紋絲不亂的頭發(fā)上，一...
    開(kāi)封第一講書(shū)人閱讀 52,821評(píng)論 1贊 314
  • 城市分裂傳說(shuō)
    那天仿村，我揣著相機(jī)與錄音锐朴，去河邊找鬼。 笑死蔼囊，一個(gè)胖子當(dāng)著我的面吹牛焚志，可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播畏鼓，決...
    沈念sama閱讀 41,236評(píng)論 3贊 424
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開(kāi)眼酱酬，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來(lái)了云矫？” 一聲冷哼從身側(cè)響起膳沽，我...
    開(kāi)封第一講書(shū)人閱讀 40,196評(píng)論 0贊 277
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎让禀，沒(méi)想到半個(gè)月后贵少，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 46,716評(píng)論 1贊 320
  • ?護(hù)林員之死
    正文 獨(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
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布感论，位于F島的核電站拥褂，受9級(jí)特大地震影響镇匀，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜免钻，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,264評(píng)論 3贊 336
  • 男人毒藥：我在死后第九天來(lái)索命
    文/蒙蒙 一彼水、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧极舔，春花似錦凤覆、人聲如沸。這莊子的主人今日做“春日...
    開(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)容