MySQL查詢性能優(yōu)化總結(jié)

查詢執(zhí)行路徑

1. 客戶端發(fā)送一條查詢給MySQL服務(wù)器
2. 服務(wù)器先檢查緩存，如果命中了緩存，則立刻返回緩存中的結(jié)果，否則進(jìn)行下一階段
3. 服務(wù)器端進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃
4. MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎的API來執(zhí)行查詢。
5. 將結(jié)果返回給客戶端

優(yōu)化數(shù)據(jù)訪問

不要向數(shù)據(jù)庫(kù)請(qǐng)求不需要的數(shù)據(jù)，例如多余的數(shù)據(jù)行、多余的字段、多表關(guān)聯(lián)返回所有列、多次取重復(fù)數(shù)據(jù)。

MySQL使用如下三種方式應(yīng)用WHERE條件，從好到壞以此為：

• 在索引中使用WHERE條件過濾不匹配的記錄。這是在存儲(chǔ)引擎層完成的。
• 使用索引覆蓋掃描(在Extra列中出現(xiàn)了Using index)來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結(jié)果，這是在MySQL服務(wù)層完成的，但無(wú)須再回表查詢記錄。
• 從數(shù)據(jù)表中返回?cái)?shù)據(jù)，然后過濾不滿足條件(在Extra列中出現(xiàn)Using Where)。這在MySQL服務(wù)層完成，MySQL需要先從數(shù)據(jù)表讀出記錄然后過濾。

重構(gòu)查詢方式

• 將復(fù)雜查詢拆分成多個(gè)不同功能的簡(jiǎn)單查詢?，F(xiàn)在不用受限于網(wǎng)絡(luò)通信、帶寬等因素，且MySQL的連接和斷開都是輕量級(jí)的，所以運(yùn)行多個(gè)小查詢已經(jīng)很容易，但具體業(yè)務(wù)還是要多實(shí)踐。

• 切分查詢。將一個(gè)大的查詢切分成每個(gè)查詢功能都一樣的小查詢。例如定期清楚大量數(shù)據(jù)時(shí)，如果用大查詢則可能需要一次鎖住很多數(shù)據(jù)、占滿整個(gè)事物日志、耗盡系統(tǒng)資源、阻塞很多小但重要的查詢，這時(shí)我們則可以切分下大SQL。

  -- 一次執(zhí)行大的查詢
  DELETE FROM notes WHERE createdAt < DATE_SUB(NOW(), INTERVAL 3 MONTH)
  -- 多次執(zhí)行同樣功能的小查詢,可以使用存儲(chǔ)過程寫循環(huán)，也可以在業(yè)務(wù)中做循環(huán)，下面示例是在業(yè)務(wù)中循環(huán)的
  -- 這樣分批次刪除，則可以減輕服務(wù)器的壓力
  const rows_affected = 0
  do{
  rows_affected = do_query("DELETE FROM notes WHERE createdAt < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
  } WHERE rows_affected > 0
  

• 分解關(guān)聯(lián)查詢。很多對(duì)性能要求較高的應(yīng)用都會(huì)對(duì)關(guān)聯(lián)應(yīng)用進(jìn)行分解。

  SELECT * FROM tag
    JOIN tag_post ON tag_post.tag_id=tag.id
    JOIN post ON tag_post.post_id=post.id
  WHERE tag.tag='mysql';
  

  可以分解成下面的SQL。

  SELECT * FROM tag WHERE tag='mysql';
  SELECT * FROM tag_post WHERE tag_id=1234;
  SELECT * FROM post WHERE post.id IN (123,456,234,789);
  

  分解的好處：

  • 讓緩存的效率更高，許多應(yīng)用程序可以緩存單表查詢結(jié)果對(duì)象
  • 執(zhí)行單個(gè)查詢，可以減少鎖的競(jìng)爭(zhēng)
  • 在應(yīng)用層關(guān)聯(lián)，可以更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，更容易做到高性能和可擴(kuò)展
  • 查詢本身效率也可能會(huì)提升
  • 可以減少冗余記錄的查詢

查詢執(zhí)行基礎(chǔ)

1. MySQL客戶端/服務(wù)器通信協(xié)議

   客戶端與服務(wù)器是“半雙工”形式，在任意時(shí)刻，要么由S向C發(fā)送數(shù)據(jù)，要么由C向S發(fā)送數(shù)據(jù)，這兩個(gè)動(dòng)作不能同時(shí)發(fā)生。這也導(dǎo)致當(dāng)C向S發(fā)送超長(zhǎng)查詢語(yǔ)句時(shí)，需要關(guān)注max_allowed_packet參數(shù)，S向C響應(yīng)大量數(shù)據(jù)時(shí)，不僅數(shù)據(jù)庫(kù)服務(wù)器需要占用大量時(shí)間計(jì)算和大量?jī)?nèi)存來保存結(jié)果，一次性返回給系統(tǒng)服務(wù)器，還會(huì)占用系統(tǒng)服務(wù)器的大量?jī)?nèi)存，而且C必須接收完，才能再釋放這條查詢所占用的資源，所以通常的做法是再應(yīng)用系統(tǒng)中通過流查詢，一部分一部分的接收數(shù)據(jù)。

查詢優(yōu)化

1. 關(guān)聯(lián)子查詢，WHERE后IN和EXISTS的選擇，NOT IN和 NOT EXISTS的選擇

   外表數(shù)據(jù)集大，內(nèi)表查詢數(shù)據(jù)集小，使用IN，因?yàn)镸ySQL5.7默認(rèn)200條數(shù)據(jù)內(nèi)，IN會(huì)使用索引

   外表數(shù)據(jù)集小，內(nèi)表查詢數(shù)據(jù)集大，使用EXISTS，MySQL的嵌套循環(huán)優(yōu)化優(yōu)勢(shì)更大

   NOT IN不會(huì)使用索引，NOT EXISTS子查詢會(huì)使用到索引，無(wú)論外表大還是內(nèi)表大NOT EXISTS效率都比NOT IN高。但世事無(wú)絕對(duì)，且關(guān)乎MySQL版本問題，遇到和類問題，還是需要多手動(dòng)測(cè)試。

2. UNION的限制

   如果UNION的各個(gè)子句能夠根據(jù)LIMIT只取部分結(jié)果集，或者希望先排好序再合并結(jié)果集。我們可以先排好各個(gè)子句的順序并且取限制條數(shù)，而不用先合并多個(gè)子句，再排序取LIMIT條數(shù)。這樣可以避免UNION生成一個(gè)很大的數(shù)據(jù)集中間表。

   -- 如果actor表有1000條數(shù)據(jù)，customer有1000條數(shù)據(jù)，則會(huì)生成2000條臨時(shí)表的數(shù)據(jù)，但我們卻只需要20條數(shù)據(jù)
   (
   SELECT first_name, last_name
   FROM actor 
   ORDER BY last_name
   )
   UNION ALL
   (
   SELECT first_name, last_name
   FROM customer 
   ORDER BY last_name
   )
   LIMIT 20
   

   減少臨時(shí)表數(shù)據(jù)的SQL

   -- 這樣臨時(shí)表就只用存儲(chǔ)40條數(shù)據(jù)了
   (
   SELECT first_name, last_name
   FROM actor 
   ORDER BY last_name
   LIMIT 20
   )
   UNION ALL
   (
   SELECT first_name, last_name
   FROM customer 
   ORDER BY last_name
   LIMIT 20
   )
   LIMIT 20
   

上面只是知識(shí)點(diǎn)的梳理，后期項(xiàng)目中遇到典型的優(yōu)化案例，我會(huì)持續(xù)更新進(jìn)來。