SQL語句的優化

一：SQL優化的小技巧

1：編寫SQL時的注意點

1.1：查詢時盡量不要使用*

除非確確實實的想要返回所有的字段，并且字段個數比較多。至于為什么不建議使用 *，有如下幾點原因：

網絡開銷變大

當使用*時，查詢時每條數據會返回所有字段值，然后這些查詢出的數據會先被放到結果集中，最終查詢完成后會統一返回給客戶端

但線上Java程序和MySQL都是分機器部署的，所以返回數據時需要經過網絡傳輸，而由于返回的是所有字段數據，因此網絡數據包的體積就會變大

從而導致占用的網絡帶寬變高，影響數據傳輸的性能和資源開銷。

但實際上可能僅需要用到其中的某幾個字段值，所以寫清楚字段后查詢，能讓網絡數據包體積變小，從而減小資源消耗、提升響應速度。

分析成本變高

一條SQL在執行前都會經過分析器解析，當使用時，解析器需要先去解析出當前要查詢的表上表示哪些字段，因此會額外增加解析成本。

如果明確寫出了查詢字段，分析器則不會有這一步解析*的開銷。

內存占用變高

當查詢一條數據時都會將其結果集放入到BufferPool的數據緩沖頁中，如果每次用*來查詢數據，查到的結果集自然會更大，占用的內存也會越大

單個結果集的數據越大，整個內存緩沖池中能存下的數據也就越少

當其他SQL操作時，在內存中找不到數據，又會去觸發磁盤IO，最終導致MySQL整體性能下降。

維護性變差

正常情況下，我們都使用半ORM框架Mybatis進行開發【針對java】，而一般為了對應查詢結果與實體對象的關系，通常都需要配置resultMap來聲明表字段和對象屬性的映射關系

如果每次使用*來查詢數據，當表結構發生變更時，就算變更的字段結構在當前業務中用不到，也需要去維護已經配置好的resultMap，所以會導致維護性變差。但聲明了需要的字段時，配置的resultMap和查詢字段相同，因此當變更的表結構不會影響當前業務時，也無需變更當前的resultMap。

1.2：連表查詢時盡量不要關聯太多表

一旦關聯太多的表，就會導致執行效率變慢，執行時間變長，原因如下：

• 數據量會隨表數量呈直線性增長，數據量越大檢索效率越低。
• 當關聯的表數量過多時，無法控制好索引的匹配，涉及的表越多，索引不可控風險越大。

交互型業務和后臺型業務

• 交互型的業務中，關聯的表數量應當控制在5張表之內

• 后臺型的業務由于不考慮用戶體驗感，有時候業務比較復雜，又需要關聯十多張表做查詢，此時可以這么干，但按照《高性能MySQL》上的推薦，最好也要控制在16~18張表之內（阿里開發規范中要求控制在3張表以內）。

1.3：多表查詢時一定要以小驅大

所謂的以小驅大即是指用小的數據集去驅動大的數據集，說簡單一點就是先查小表，再用小表的結果去大表中檢索數據

其實在MySQL的優化器也會有驅動表的優化，當執行多表聯查時，MySQL的關聯算法為Nest Loop Join

該算法會依照驅動表的結果集作為循環基礎數據，然后通過該結果集中一條條數據，作為過濾條件去下一個表中查詢數據，最后合并結果得到最終數據集，MySQL優化器選擇驅動表的邏輯如下：

• 如果指定了連接條件，滿足查詢條件的小數據表作為驅動表。
• 如果未指定連接條件，數據總行數少的表作為驅動表。

如果在做連表查詢時，你不清楚具體用誰作為驅動表，哪張表去join哪張表，這時可以交給MySQL優化器自己選擇

但有時候優化器不一定能夠選擇正確，因此寫SQL時最好自己去選擇驅動表，小表放前，大表放后！

1.4：like不要使用左模糊或者全模糊

如若like關鍵字以%號開頭會導致索引失效，從而導致SQL觸發全表查詢，因此需要使用模糊查詢時，千萬要避免%xxx、%xxx%這兩種情況出現

實在需要使用這兩類模糊查詢時，可以適當建立全文索引來代替，數據量較大時可以使用ES、Solr…這類搜索引擎來代替。

1.5：查詢時盡量不要對字段做空值判斷

select * from xxx where yyy is null;
select * from xxx where yyy not is null;

當出現基于字段做空值判斷的情況時，會導致索引失效，因為判斷null的情況不會走索引，因此切記要避免這樣的情況

一般在設計字段結構的時候，請使用not null來定義字段

同時如果想為空的字段，可以設計一個0、""這類空字符代替：

• 一方面要查詢空值時可通過查詢空字符的方式走索引檢索
• 另一方面也能避免MyBatis注入對象屬性時觸發空指針異常。

1.6：不要在條件查詢=前對字段做任何運算

select * from users where user_id * 2 = 8;
select * from users where trim(user_name) = "張三";

users用戶表中user_id、user_name字段上都創建了索引，但上述這類情況都不會走索引

因為MySQL優化器在生成執行計劃時，發現這些=前面涉及到了邏輯運算，因此就不會繼續往下走了，會將具體的運算工作留到執行時完成

也正是由于優化器沒有繼續往下走，因此不會為運算完成后的字段選擇索引，最終導致索引失效走全表查詢。

所以不要在條件查詢=前對字段做任何運算

1.7：!=、!<>、not in、or…要慎用

總之，就是各種可能導致索引失效的寫法要慎用，在實際過程中可以使用其他的一些語法代替，比如or可以使用union all來代替：

select user_name from zz_users where user_id=1 or user_id=2;
-- 可以替換成：
select user_name from zz_users where user_id=1
union all
select user_name from zz_users where user_id=2;

1.8：必要情況下可以強制指定索引

在表中存在多個索引時，有些復雜SQL的情況下，或者在存儲過程中，必要時可強制指定某條查詢語句走某個索引

因為MySQL優化器面對存儲過程、復雜SQL時并沒有那么智能，有時可能選擇的索引并不是最好的，這時我們可以通過force index，如下：

select * from users force index(unite_index) where user_name = "張三";

這樣就能夠100%強制這條SQL走某個索引查詢數據

🎉 這種強制指定索引的方式，一定要建立在對索引結構足夠熟悉的情況下，否則效果會適得其反。

1.9：避免頻繁創建、銷毀臨時表

臨時表是一種數據緩存，對于一些常用的查詢結果可以為其建立臨時表，這樣后續要查詢時可以直接基于臨時表來獲取數據

MySQL默認會在內存中開辟一塊臨時表數據的存放空間，所以走臨時表查詢數據是直接基于內存的，速度會比走磁盤檢索快上很多倍。

但一定要切記一點，只有對于經常查詢的數據才對其建立臨時表，不要盲目的去無限制創建，否則頻繁的創建、銷毀會對MySQL造成不小的負擔。

1.10：盡量將大事務拆分為小事務執行

一個事務在執行事，如果其中包含了寫操作，會先獲取鎖再執行，直到事務結束后MySQL才會釋放鎖。

而一個事務占有鎖之后，會導致其他要操作相同數據的事務被阻塞

如果當一個事務比較大時，會導致一部分數據的鎖定周期較長，在高并發情況下會引起大量事務出現阻塞，從而最終拖垮整個MySQL系統。

show status like ‘innodb_log_waits’;查看是否有大事務由于redo_log_buffer不足，而在等待寫入日志。

大事務也會導致日志寫入時出現阻塞，這種情況下會強制觸發刷盤機制

大事務的日志需要阻塞到有足夠的空間時，才能繼續寫入日志到緩沖區，這也可能會引起線上出現阻塞。

1.11：從業務設計層面減少大量數據返回的情況

如果一次性返回的數據量過于巨大時，就會引起網絡阻塞、內存占用過高、資源開銷過大的各類問題出現

因此如果項目中存在這類業務，一定要記住拆分掉它，比如分批返回給客戶端。

分批查詢的方式也被稱之為增量查詢，每次基于上次返回數據的界限，再一次讀取一批數據返回給客戶端，這也就是經典的分頁場景

通過分頁的思想能夠提升單次查詢的速度，以及避免大數據量帶來的一系列后患問題。

1.12：盡量避免深分頁的情況出現

分頁雖然比較好，但也依舊存在問題，也就是深分頁問題，如下：

select xx,xx,xx from yyy limit 100000,10; 

上述語句在MySQL的實際執行過程中，會先查詢出100010條數據，然后丟棄前面的10W條數據，將最后10條數據返回，這個過程無異極其浪費資源。

對于深分頁，有下面兩種方式解決

• 如果查詢出的結果集，存在遞增且連續的字段，可以基于有序字段來進一步做篩選后再獲取分頁數據

select xx,xx,xx from yyy where 有序字段 >= nnn limit 10; -- 例如
-- 第一頁
select xx,xx,xx from yyy where 有序字段 >= 1 limit 10; 
-- 第二頁
select xx,xx,xx from yyy where 有序字段 >= 11 limit 10; 
-- 第N頁.....-- 第10000頁
select xx,xx,xx from yyy where 有序字段 >= 100001 limit 10; 

• 在業務上限制深分頁的情況，以百度為例

一般用戶最多看前面30頁，如果還未找到他需要的內容，基本上就會換個更精準的關鍵詞重新搜索。

如果業務必須要求展現所有分頁數據，此時又不存在遞增的連續字段咋辦？

• 要么選擇之前哪種很慢的分頁方式
• 要么就直接拋棄所有！每次隨機十條數據出來給用戶，如果不想重復的話，每次新的分頁時，再對隨機過的數據加個標識即可。

1.13：SQL務必要寫完整，不要使用縮寫法

在寫的時候為了圖簡單，都會將一些能簡寫的SQL就簡寫，但其實這種做法也略微有些問題

因為隱式的這種寫法，在MySQL底層都需要做一次轉換，將其轉換為完整的寫法

因此簡寫的SQL會比完整的SQL多一步轉化過程，如果你考慮極致程度的優化，也切記將SQL寫成完整的語法。

1.14：基于聯合索引查詢時請務必確保字段的順序性

最左前綴原則，不再贅述

雖然8.0版本中推出索引跳躍掃描機制，但會存在較大的開銷，同時還有很強的局限性，所以最好在寫SQL時，依舊遵循索引的最左前綴原則撰寫。

1.15：客戶端的一些操作可以批量化完成

批量新增某些數據、批量修改某些數據的狀態…，這類需求在一個項目中也比較常見，一般的做法如下：

for (xxObject obj : xxObjs) {xxDao.insert(obj);
}/*** xxDao.insert(obj)對應的SQL如下：* insert into tb_xxx values(......);
**/

這種情況確實可以實現批量插入的效果，但是每次都需要往MySQL發送SQL語句，會帶來額外的網絡開銷以及耗時，因此上述實現可以更改為如下：

xxDao.insertBatch(xxObjs);/*** xxDao.insertBatch(xxObjs)對應的SQL如下：* insert into tb_xxx values(......),(......),(......),(......),.....;
**/

這樣會組合成一條SQL發送給MySQL執行，能夠在很大程度上節省網絡資源的開銷，提升批量操作的執行效率。

🎉 這樣的方式同樣適用于修改場景

1.17：明確僅返回一條數據的語句可以使用limit 1

select * from users where user_name = "張三";
select * from users where user_name = "張三" limit 1;

加上limit 1關鍵字后，當程序匹配到一條數據時就會停止掃描，如果不加的情況下會將所有數據都掃描一次。

所以一般情況下，如果確定了只需要查詢一條數據，就可以加上limit 1提升性能。

2：業內標準

2.1：3秒原則和5秒原則

• 客戶端訪問時，能夠在1s內得到響應，用戶會覺得系統響應很快，體驗非常好。
• 客戶端訪問時，1~3秒內得到響應，處于可以接受的階段，其體驗感還算不錯。
• 客戶端訪問時，需要等待3~5秒時才可響應，這是用戶就感覺比較慢了，體驗有點糟糕。
• 客戶端訪問時，一旦響應超過5秒，用戶體驗感特別糟糕，通常會選擇離開或刷新重試。

上述這四條是用戶體驗感的四個等級，一般針對于C端業務而言，基本上都需要將接口響應速度控制到第二等級，即最差也要三秒內給用戶返回響應

否則會導致體驗感極差，從而讓用戶對產品留下不好的印象。

所謂的三秒原則通常是基于C端業務而言的，對于B端業務來說，通常用戶的容忍度會高一些，也包括B端業務的業務邏輯會比C端更為復雜一些，所以可將響應速度控制到第三等級，也就是5s內能夠得到響應。

針對于一些特殊類型的業務，如后臺計算型的業務，好比跑批對賬、定時調度…等，這類因為本身業務就特殊，因此可不關注其響應速度。

2.2：mysql的500ms原則

用戶感受到的響應速度會由多方面的耗時組成，如下：

所謂給用戶的響應時間其實會包含各方面的耗時，也就是這所有的過程加一塊兒，必須要在1~3s內給出響應

而SQL耗時屬于「系統耗時→數據操作耗時」這部分，因此留給SQL語句執行的時間最多只能有500ms

一般在用戶量較大的門戶網站中，甚至要求控制在10ms、30ms、50ms以內

二：MySQL索引優化

1：explain分析工具

在之前的’正確建立和使用索引‘簡單的說了一下這個工具，是自帶的一個執行分析工具，可使用于select、insert、update、delete、repleace等語句上，需要使用時只需在SQL語句前加上一個explain關鍵字即可，然后MySQL會對應語句的執行計劃列出

1.1：id字段

這是執行計劃的ID值，一條SQL語句可能會出現多步執行計劃，所以會出現多個ID值，這個值越大，表示執行的優先級越高，同時還會出現四種情況：

• ID相同：當出現多個ID相同的執行計劃時，從上往下挨個執行。
• ID不同時：按照ID值從大到小依次執行。
• ID有相同又有不同：先從大到小依次執行，碰到相同ID時從上往下執行。
• ID為空：ID=null時，會放在最后執行。

1.2：select_type字段

當前執行的select語句其具體的查詢類型，有如下取值：

• SIMPLE：簡單的select查詢語句，不包含union、子查詢語句。
• PRIMARY：union或子查詢語句中，最外層的主select語句。
• SUBQUEPY：包含在主select語句中的第一個子查詢，如select … xx = (select …)。
• DERIVED：派生表，指包含在from中的子查詢語句，如select … from (select …)。
• DEPENDENT SUBQUEPY：復雜SQL中的第一個select子查詢（依賴于外部查詢的結果集）。
• UNCACHEABLE SUBQUERY：不緩存結果集的子查詢語句。
• UNION：多條語句通過union組成的查詢中，第二個以及更后面的select語句。
• UNION RESULT：union的結果集。
• DEPENDENT UNION：含義同上，但是基于外部查詢的結果集來查詢的。
• UNCACHEABLE UNION：含義同上，但查詢出的結果集不會加入緩存。
• MATERIALIZED：采用物化的方式執行的包含派生表的查詢語句。

這個字段主要是說明當前查詢語句所屬的類型，以及在整條大的查詢語句中，當前這個查詢語句所屬的位置。

1.3：table字段

表示當前這個執行計劃是基于哪張表執行的，這里會寫出表名，但有時候也不一定是物理磁盤中存在的表名，還有可能出現如下格式：

• <derivenN>：基于id=N的查詢結果集，進一步檢索數據。
• <unionM,N>：會出現在查詢類型為UNION RESULT的計劃中，表示結果由id=M,N…的查詢組成。
• <subqueryN>：基于id=N的子查詢結果，進一步進行數據檢索。
• <tableName>：基于磁盤中已創建的某張表查詢。

一句話總結就是：這個字段會寫明，當前的這個執行計劃會基于哪個數據集查詢，有可能是物理表、有可能是子查詢的結果、也有可能是其他查詢生成的派生表。

1.4：partitions字段

這個字段在早版本的explain工具中不存在，這主要是用來顯示分區的，因為后續版本的MySQL中支持表分區，該列的值表示檢索數據的分區。

1.5：type字段

該字段表示當前語句執行的類型，可能出現的值如下：

• all：全表掃描，基于表中所有的數據，逐行掃描并過濾符合條件的數據。
• index：全索引掃描，和全表掃描類似，但這個是把索引樹遍歷一次，會比全表掃描要快。
• range：基于索引字段進行范圍查詢，如between、<、>、in…等操作時出現的情況。
• index_subquery：和上面含義相同，區別：這個是基于非主鍵、唯一索引字段進行in操作。
• unique_subquery：執行基于主鍵索引字段，進行in操作的子查詢語句會出現的情況。
• index_merge：多條件查詢時，組合使用多個索引來檢索數據的情況。
• ref_or_null：基于次級(非主鍵)索引做條件查詢時，該索引字段允許為null出現的情況。
• fulltext：基于全文索引字段，進行查詢時出現的情況。
• ref：基于非主鍵或唯一索引字段查找數據時，會出現的情況。
• eq_ref：連表查詢時，基于主鍵、唯一索引字段匹配數據的情況，會出現多次索引查找。
• const：通過索引一趟查找后就能獲取到數據，基于唯一、主鍵索引字段查詢數據時的情況。
• system：表中只有一行數據，這是const的一種特例。
• null：表中沒有數據，無需經過任何數據檢索，直接返回結果。

這個字段的值很重要，它決定了MySQL在執行一條SQL時，訪問數據的方式，性能從好到壞依次為：

• 完整的性能排序：null → system → const → eq_ref → ref → fulltext → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → all
• 常見的性能排序：system → const → eq_ref → ref → fulltext → range → index → all

一般在做索引優化時，一般都會要求最好優化到ref級別，至少也要到range級別，也就是最少也要基于次級索引來檢索數據，不允許出現index、all這類全掃描的形式。

1.6：possible_keys字段

這個字段會顯示當前執行計劃，在執行過程中可能會用到哪些索引來檢索數據

?? 可能會用到并不代表一定會用，在某些情況下，就算有索引可以使用，MySQL也有可能放棄走索引查詢。

1.7：key字段

前面的possible_keys字段表示可能會用到的索引，而key這個字段則會顯示具體使用的索引

一般情況下都會從possible_keys的值中，綜合評判出一個性能最好的索引來進行查詢，但也有兩種情況會出現key=null的這個場景：

• possible_keys有值，key為空：多半是由于表中數據不多，因此MySQL會放棄索引，選擇走全表查詢，也有可能是因為SQL導致索引失效。
• possible_keys、key都為空：表示當前表中未建立索引、或查詢語句中未使用索引字段檢索數據。

默認情況下，possible_keys有值時都會從中選取一個索引，但這個選擇的工作是由MySQL優化器自己決定的

如果你想讓查詢語句執行時走固定的索引，則可以通過force index、ignore index的方式強制指定。

1.8：key_len字段

這個表示對應的執行計劃在執行時，使用到的索引字段長度，一般情況下都為索引字段的長度，但有三種情況例外：

• 如果索引是前綴索引，這里則只會使用創建前綴索引時，聲明的前N個字節來檢索數據。
• 如果是聯合索引，這里只會顯示當前SQL會用到的索引字段長度，可能不是全匹配的情況。
• 如果一個索引字段的值允許為空，key_len的長度會為：索引字段長度+1。

1.9：ref字段

顯示索引查找過程中，查詢時會用到的常量或字段：

• const：如果顯示這個，則代表目前是在基于主鍵字段值或數據庫已有的常量（如null）查詢數據。
  • select … where 主鍵字段 = 主鍵值;
  • select … where 索引字段 is null;
• 顯示具體的字段名：表示目前會基于該字段查詢數據。
• func：如果顯示這個，則代表當與索引字段匹配的值是一個函數，如：
  • select … where 索引字段 = 函數(值);

1.10：rows字段

這一列代表執行時，預計會掃描的行數

這個數字對于InnoDB表來說，其實有時并不夠準確，但也具備很大的參考價值

如果這個值很大，在執行查詢語句時，其效率必然很低，所以該值越小越好。

1.11：filtered字段

這個字段在早版本中也不存在，它是一個百分比值，意味著表中不會掃描的數據百分比

該值越小則表示執行時會掃描的數據量越大，取值范圍是0.00~100.00。

1.12：extra字段

該字段會包含MySQL執行查詢語句時的一些其他信息，這個信息對索引調優而言比較重要，可以帶來不小的參考價值

這個字段會出現的值有很多種，如下：

• Using index：表示目前的查詢語句，使用了索引覆蓋機制拿到了數據。
• Using where：表示目前的查詢語句無法從索引中獲取數據，需要進一步做回表去拿表數據。
• Using temporary：表示MySQL在執行查詢時，會創建一張臨時表來處理數據。
• Using filesort：表示會以磁盤+內存完成排序工作，而完全加載數據到內存來完成排序。
• Select tables optimized away：表示查詢過程中，對于索引字段使用了聚合函數。
• Using where;Using index：表示要返回的數據在索引中包含，但并不是索引的前導列，需要做回表獲取數據。
• NULL：表示查詢的數據未被索引覆蓋，但where條件中用到了主鍵，可以直接讀取表數據。
• Using index condition：和Using where類似，要返回的列未完全被索引覆蓋，需要回表。
• Using join buffer (Block Nested Loop)：連接查詢時驅動表不能有效的通過索引加快訪問速度時，會使用join-buffer來加快訪問速度，在內存中完成Loop匹配。
• Impossible WHERE：where后的條件永遠不可能成立時提示的信息，如where 1!=1。
• Impossible WHERE noticed after reading const tables：基于唯一索引查詢不存在的值時出現的提示。
• const row not found：表中不存在數據時會返回的提示。
• distinct：去重查詢時，找到某個值的第一個值時，會將查找該值的工作從去重操作中移除。
• Start temporary, End temporary：表示臨時表用于DuplicateWeedout半連接策略，也就是用來進行semi-join去重。
• Using MRR：表示執行查詢時，使用了MRR機制讀取數據。
• Using index for skip scan：表示執行查詢語句時，使用了索引跳躍掃描機制讀取數據。
• Using index for group-by：表示執行分組或去重工作時，可以基于某個索引處理。
• FirstMatch：表示對子查詢語句進行Semi-join優化策略。
• No tables used：查詢語句中不存在from子句時提示的信息，如desc table_name;。
• …

🎉 基于Extra字段做個性能排序：Using index → NULL → Using index condition → Using where → Using where;Using index → Using join buffer → Using filesort → Using MRR → Using index for skip scan → Using temporary → Strart temporary,End temporary → FirstMatch

2：索引優化參考項

在做索引優化時，值得咱們參考的幾個字段為：

• key：如果該值為空，則表示未使用索引查詢，此時需要調整SQL或建立索引。
• type：這個字段決定了查詢的類型，如果為index、all就需要進行優化。
• rows：這個字段代表著查詢時可能會掃描的數據行數，較大時也需要進行優化。
• filtered：這個字段代表著查詢時，表中不會掃描的數據行占比，較小時需要進行優化。
• Extra：這個字段代表著查詢時的具體情況，在某些情況下需要根據對應信息進行優化。

在explain語句后面緊跟著show warings語句，可以得到優化后的查詢語句，從而看出優化器優化了什么。