(Structured Query Language)標準結構化查詢語言簡稱SQL，編寫SQL語句是每位後端開發日常職責中，接觸最多的一項工作，SQL是關係型資料庫誕生的產物，無論是什麼資料庫，MySQL、Oracle、SQL Server、DB2、PgSQL....，只要還處於關係型資料庫這個範疇，都會遵循統一的SQL標準，這句話簡單來說也就是：無論什麼關係型資料庫，本質上SQL的語法都是相同的，因為它們都實現了相同的SQL標準，不同資料庫之間僅支持的特性不同而已。

寫SQL語句不難，稍微系統學習過資料庫相關技術的人都能做到，但想要寫好SQL卻也不是一件易事，在大多數編寫SQL的時候，很多人都是以實現需求為原則去撰寫的，當一條SQL寫出來之後，只要能滿足業務需求就行，不會考慮它有沒有優化點，能不能讓它跑的更快。

而所謂的SQL優化，就是指將一條SQL寫的更加簡潔，讓SQL的執行速度更快，易讀性與維護性更好。

但要記住！SQL優化是建立在不影響業務的前提之上的，畢竟技術是為業務提供服務，如果為了提高執行效率，把SQL改成了不符合業務需求的樣子，這是不行的，這就好比一個流行的梗：

• 記者：你有什麼特長嗎？
• 路人：我心算特別快！
• 記者：哪請問565848654224 * 415414141 / 5145 + 44456 - 6644546 = ？
• 路人：51354545452314！
• 記者：（拿出計算器，算了一下）你這算的不對啊。
• 路人：對啊，我也知道不對，但你就說快不快吧！

從這個經典的網絡流行梗中，就能看出一個問題，如果一件事違背了初衷，就算再好也無濟於事，比如心算特別快，但如果算的不准，再快也沒意義，這個道理放在SQL優化中亦是同理，優化一定要建立在不違背業務需求的情況下進行！

一、編寫SQL的基本功

對於簡單的SQL語句編寫工作，相信這點對於每位略有經驗的程式設計師都是手到拈來的事情，但往往實際業務場景中，咱們需要編寫一些邏輯較為複雜的SQL語句，有可能涉及很多表、很多欄位的複雜運算，這時編寫SQL時就會出現「卡殼」情況，包括我在內也不例外，日常開發中也會遇到這類情況。

那當遇到「卡殼」情況時，該如何處理才好呢？很多人在這種情況下，首先會試圖在網上查找是否有類似業務的實現可參考，如果沒有的情況下，會選擇去問身邊的同事或技術Leader，或者也會去技術交流群問問潛水大佬。但這種方式都屬於藉助外力來解決問題，一旦外力也無法提供幫助時，「卡殼情況」就會演變為「死機情況」，徹底的陷入僵局，最終導致項目進度無法繼續推進。

在這裡我教大家一個比較實用的SQL編寫技巧，即：拆解業務需求，先以定值推導SQL。學習過算法的小夥伴應該知道有一種算法思想叫做分而治之，也包括之前聊時，該線程池就是分治思想的落地產物，當一個任務較為龐大且複雜時，在ForkJoin內部會對任務進行拆分，然後分別執行拆分後的小任務，最終將所有小任務結果合併，最終得出大任務的執行結果。

我所謂的SQL編寫技巧亦是如此，面對一個較為複雜或較難實現的業務需求時，就可以按照需求進行逐步拆分，化繁為簡後逐步實現。其實對於這個道理很多人都懂，但往往在實際編寫SQL時卻想著一步到位，這也是我接觸很多程式設計師後發現的問題：經驗尚未豐富的開發，面對一個需求時通常都想著從頭寫到尾。但這樣寫就很容易卡殼，對於簡單的業務需求可以這樣做，但面對複雜業務時一定要先拆解需求後再逐步實現。

同時前面還提到一句：先以定值推導SQL，這是啥意思呢？因為有些情況下，一個查詢條件會依賴於另一條SQL的執行結果來決定，很多人在這種情況下會直接組合起來一起寫，但這會導致編寫SQL的複雜度再次提升，因此在這種情況下，可以先用指定值作為條件去查詢，例如xx = "xxx"，後面等整體SQL完成後，再套入SQL。

當然，說了這麼多都是理論，在編程中有句話叫做：紮實的基礎理論知識，會決定一個人水平飛得有多高，但能夠將相應的理論用於實踐，這才能真正體現出一個人的水平有多牛，只懂理論不懂實踐，這無異於紙上談兵，所以下面上一個簡單的SQL練習題，實踐一下上述的理論：

select * from zz_users;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊貓      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
|       4 | 黑熊      | 男       | 8888     | 2022-09-17 23:48:29 |
|       8 | 貓熊      | 女       | 8888     | 2022-09-27 17:22:29 |
|       9 | 棕熊      | 男       | 0369     | 2022-10-17 23:48:29 |
+---------+-----------+----------+----------+---------------------+
複製代碼

上面是本次練習題會用到的一張用戶表，需求如下：

• 基於性別欄位分組，然後ID排序，最後顯示各組中的所有姓名，每個姓名之間用，隔開。

這裡大家可以先自己動手實操一下這個練習題，然後再看文章後面的解析。

這個需求看起來不太複雜，但如果直接開寫也會令人有些許懵逼，所以先來拆解一下這個需求：

• ①首先要基於性別分組，因此需要對user_sex欄位使用group by關鍵字。
• ②要對ID欄位做排序，因此需要對user_id欄位使用order by關鍵字。
• ③將排序語句應用於分組查詢的結果中，然後再根據user_id排序輸出姓名。

拆解明白了需求之後，接下來逐步實現每個小需求：

-- ①首先要基於性別分組，因此需要對`user_sex`欄位使用`group by`關鍵字。
select user_sex,user_id from `zz_users` group by user_sex;
+----------+---------+
| user_sex | user_id |
+----------+---------+
| 女       |       1 |
| 男       |       3 |
+----------+---------+
複製代碼

上述這條SQL在MySQL5.x版本會得到如上結果，放在MySQL8.x版本則會報錯，但不管是任何版本，似乎都未曾得到咱們需要的數據，因為現在我們想要的是先根據性別對user_id做分組，那此時需要用到一個新的函數來輔助實現該功能，即group_concat()，它可以給我們返回指定欄位分組組合返回的結果，如下：

select 
    user_sex as "性別",
    convert(group_concat(user_id) using utf8) as "ID"
from 
    `zz_users` group by user_sex;
-- 執行結果如下：
+------+---------+
| 性別 |  ID     |
+------+---------+
| 女   |     1,8 |
| 男   | 2,3,4,9 |
+------+---------+
複製代碼

OK，在上面就基於性別實現了ID分組，接著是需要對ID做排序工作，排序其實比較簡單，大家應該都學習過order by關鍵字，如下：

-- ②要對`ID`欄位做排序，因此需要對`user_id`欄位使用`order by`關鍵字。
select user_id from zz_users order by user_id desc;
+---------+
| user_id |
+---------+
|       9 |
|       8 |
|       4 |
|       3 |
|       2 |
|       1 |
+---------+
複製代碼

這個效果很容易理解，但問題在於如何套入到之前的分組語句中呢？這裡會令人有些費腦，其實很簡單，如下：

select 
    user_sex as "性別",
    convert(
        group_concat(user_id order by user_id desc separator ",") 
    using utf8) as "ID" 
from `zz_users` group by user_sex;
-- 執行結果如下：
+------+---------+
| 性別 |  ID     |
+------+---------+
| 女   |     8,1 |
| 男   | 9,4,3,2 |
+------+---------+
複製代碼

直接把order by語句套入到group_concat()函數中即可，最後聲明一下各個值之間的分隔符即可，到這一步為止已經實現了ID分組排序工作，接著是需要按照排序好的ID，將對應的姓名按順序顯示出來，在這裡第一時間有小夥伴可能想到的是嵌套子查詢，使用in來做，如下：

select user_name from zz_users where user_id in (8,1);
+-----------+
| user_name |
+-----------+
| 熊貓      |
| 貓熊      |
+-----------+
複製代碼

然後對兩個不同的ID分組，分別in一次，然後使用union合併結果，再一次做分組，這樣也可以，但實際上會複雜很多很多，其實實現遠遠沒有那麼複雜，只需要基於之前的SQL，換個欄位即可，如下：

③將排序語句應用於分組查詢的結果中，然後再根據`user_id`排序輸出姓名。
select 
    user_sex as "性別",
    convert(
        group_concat(user_name order by user_id desc separator "，") 
    using utf8) as "姓名" 
from `zz_users` group by user_sex;
-- 執行結果如下：
+------+------------------------+
| 性別 |          姓名          |
+------+------------------------+
| 女   | 貓熊，熊貓             |
| 男   | 棕熊，黑熊，子竹，竹子 |
+------+------------------------+
複製代碼

此時一步步的推敲，就達到了最開始的需求：「基於性別欄位分組，然後ID排序，最後顯示各組中的所有姓名，每個姓名之間用，隔開」：

同時也可以根據上圖中的完整數據，來對比看看查詢出的是否正確，觀察後會發現沒有任何問題！

上面經過一個例子的薰陶後，咱們逐步拆解了需求，並且套入了實現，最終會發現編寫SQL的過程異常順利，這還僅僅只是一些簡單的需求拆解，當業務需求越發複雜時，這套拆解法的作用越大，所以拆解實現法也是寫SQL的一大基本功。

二、SQL優化的小技巧

前面聊了一些寫SQL的基本功後，接著來聊一聊本文的核心：SQL優化，所謂的高手和普通人之間，最大的不同在於能將相同的事情做到更好，比如送外賣，相同的時間內一個人能夠送的更多，這是個送外賣的高手。比如玩遊戲，相同的角色和裝備，一個人的戰績能夠更出色，那這是個打遊戲的高手......。

上述的道理放在編程中同樣適用，一個人代碼敲得更快、代碼敲的更多、執行效率越高，這也可以被稱為是一個寫代碼的高手，俗稱「碼農Pro Max」，那作為一個普通碼農，如何達到「碼農Pro、碼農Plus、碼農Pro Max.....」的境界呢？首先你得能夠寫出一手好SQL！

掌握了寫SQL的基本功後，足以讓你寫代碼的效率提升，但引言中就聊到過：寫的快不代表寫的好，就算你能夠日碼三萬行，並且還能滿足業務需求，這也不見得的能被稱之為高手，真正的SQL高手除開編寫效率夠高之外，對於每條SQL的執行效率也要可控。如果寫的多，但有些業務SQL在大數據的情況下，一跑就是十多秒，這是萬萬不可的！

那麼問題又來了：如何讓自己的SQL又快又好呢？答案其實非常簡單，減小查詢的數據量、提升SQL的索引命中率即可，接著先來說說撰寫SQL時的一些注意點。

2.1、編寫SQL時的注意點

在寫SQL的時候，往往很多時候的細節不注意，就有可能導致索引失效，也因此會造成額外的資源開銷，而我們要做的就是避開一些誤區，確保自己的SQL在執行過程中能夠最大程度上節省資源、縮短執行時間，下面羅列一些經典的SQL注意點。

2.1.1、查詢時儘量不要使用*

一般在寫SQL為了方便，所以通常會採用*來代替所有欄位，畢竟用*號只要按鍵盤一下，寫欄位則需要一個個欄位名去寫。寫*的確能讓程式設計師更省力，但對機器就不太友好了，因此在寫查詢語句時一律不要使用*代替所有欄位，這條準則相信大家都知道，但到底是為什麼呢？

其實主要有如下幾方面的原因：

• ①分析成本變高。

在《SQL執行篇》中聊過，一條SQL在執行前都會經過分析器解析，當使用*時，解析器需要先去解析出當前要查詢的表上*表示哪些欄位，因此會額外增加解析成本。但如果明確寫出了查詢欄位，分析器則不會有這一步解析*的開銷。

• ②網絡開銷變大。

當使用*時，查詢時每條數據會返回所有欄位值，然後這些查詢出的數據會先被放到結果集中，最終查詢完成後會統一返回給客戶端，但線上Java程序和MySQL都是分機器部署的，所以返回數據時需要經過網絡傳輸，而由於返回的是所有欄位數據，因此網絡數據包的體積就會變大，從而導致占用的網絡帶寬變高，影響數據傳輸的性能和資源開銷。但實際上可能僅需要用到其中的某幾個欄位值，所以寫清楚欄位後查詢，能讓網絡數據包體積變小，從而減小資源消耗、提升響應速度。

• ③內存占用變高。

在《MySQL內存篇》中曾詳細講到了InnoDB引擎的工作原理，當查詢一條數據時都會將其結果集放入到BufferPool的數據緩衝頁中，如果每次用*來查詢數據，查到的結果集自然會更大，占用的內存也會越大，單個結果集的數據越大，整個內存緩衝池中能存下的數據也就越少，當其他SQL操作時，在內存中找不到數據，又會去觸發磁碟IO，最終導致MySQL整體性能下降。

• ④維護性變差。

用過MyBatis框架的小夥伴應該都知道一點，一般為了對應查詢結果與實體對象的關係，通常都需要配置resultMap來聲明表欄位和對象屬性的映射關係，但如果每次使用*來查詢數據，當表結構發生變更時，就算變更的欄位結構在當前業務中用不到，也需要去維護已經配置好的resultMap，所以會導致維護性變差。但聲明了需要的欄位時，配置的resultMap和查詢欄位相同，因此當變更的表結構不會影響當前業務時，也無需變更當前的resultMap。

綜上所述，使用*的情況下反而會帶來一系列弊端，所以能顯示寫明所需欄位的情況下，儘量寫明所需欄位，除開上述原因外，還有一點最關鍵的原因：基於非主鍵欄位查詢可能會產生回表現象，如果是基於聯合索引查詢數據，需要的結果欄位在聯合索引中有時，可能通過索引覆蓋原理去讀數據，從而減少一次回表查詢。但使用*查詢所有欄位數據時，由於聯合索引中沒有完整數據，因此只能做一次回表從聚簇索引中拿數據，對於索引覆蓋感興趣的可參考之前的《索引應用篇-索引覆蓋機制》。

2.1.2、連表查詢時儘量不要關聯太多表

對於這點的原因其實很簡單，一旦關聯太多的表，就會導致執行效率變慢，執行時間變長，原因如下：

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

一般來說，交互型的業務中，關聯的表數量應當控制在5張表之內，而後台型的業務由於不考慮用戶體驗感，有時候業務比較複雜，又需要關聯十多張表做查詢，此時可以這麼幹，但按照《高性能MySQL》上的推薦，最好也要控制在16~18張表之內（阿里開發規範中要求控制在3張表以內）。

2.1.3、多表查詢時一定要以小驅大

所謂的以小驅大即是指用小的數據集去驅動大的數據集，說簡單一點就是先查小表，再用小表的結果去大表中檢索數據，其實在MySQL的優化器也會有驅動表的優化，當執行多表聯查時，MySQL的關聯算法為Nest Loop Join，該算法會依照驅動表的結果集作為循環基礎數據，然後通過該結果集中一條條數據，作為過濾條件去下一個表中查詢數據，最後合併結果得到最終數據集，MySQL優化器選擇驅動表的邏輯如下：

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

如果在做連表查詢時，你不清楚具體用誰作為驅動表，哪張表去join哪張表，這時可以交給MySQL優化器自己選擇，但有時候優化器不一定能夠選擇正確，因此寫SQL時最好自己去選擇驅動表，小表放前，大表放後！

舉個例子感受一下兩者之間的區別，假設zz_student學生表中有10000條數據，zz_class班級表中有100條數據，當需要關聯這兩張表查詢數據時，SQL如下：

-- 大表在前，小表在後
select * from zz_student as s left join zz_class as c on s.class_id = c.class_id;
-- 小表在前，大表在後
select * from zz_class as c left join zz_student as s on c.class_id = s.class_id;
複製代碼

上述是兩種聯查的SQL語法，如果學生表在前作為驅動表，根據Nest Loop Join算法會循環一萬次查詢數據，而反之如果班級表在前，則只需要循環100次即可查詢出數據，因此諸位在寫SQL時一定要記得將小表作為驅動表。

這個道理不僅僅只存在於多表關聯查詢中，只要涉及到多表查詢的情況，都需遵循該原則，比如使用子查詢進行多表查詢時，請確保結果集小的SQL先執行。

舉個子查詢的小表驅動大表的例子：

select * from xxx where yyy in (select yyy from zzz where ....);
複製代碼

MySQL在執行上述這條SQL時，會先去執行in後面的子查詢語句，這時儘量要保證子查詢的結果集小於in前面主查詢的結果集，這樣能夠在一定程度上減少檢索的數據量。通常使用in做子查詢時，都要確保in的條件位於所有條件的最後面，這樣能夠在最大程度上減小多表查詢的數據匹配量，如下：

- 優化前：select xxx,xxx,xxx from table where colum in(sql) and id = 10;
- 優化後：select xxx,xxx,xxx from table where id = 10 and colum in(sql);
複製代碼

以小驅大這個規則也可以進一步演化，也就是當查詢多張表數據時，如果有多個欄位可以連接查詢，記得使用and來拼接多個聯查條件，因為條件越精準，匹配的數據量就越少，查詢速度自然會越快。

對於單表查詢時也是如此，比如要對數據做分組過濾，可以先用where過濾掉一部分不需要的數據後，再對處理後的數據做分組排序，因為分組前的數據量越小，分組時的性能會更好！

可以把SQL當成一個鏈式處理器，每一次新的子查詢、關聯查詢、條件處理....等情況時，都可以看成一道道的工序，我們在寫SQL時要注意的是：在下一道工序開始前儘量縮小數據量，為下一道工序儘可能提供更加精準的數據。

2.1.4、不要使用like左模糊和全模糊查詢

對於這點的原因十分明顯，因為在之前《索引應用篇-索引失效場景》中聊到過，如若like關鍵字以%號開頭會導致索引失效，從而導致SQL觸發全表查詢，因此需要使用模糊查詢時，千萬要避免%xxx、%xxx%這兩種情況出現，實在需要使用這兩類模糊查詢時，可以適當建立全文索引來代替，數據量較大時可以使用ES、Solr....這類搜尋引擎來代替。

2.1.5、查詢時儘量不要對欄位做空值判斷

select * from xxx where yyy not is null;
select * from xxx where yyy is null;
複製代碼

當出現基於欄位做空值判斷的情況時，會導致索引失效，因為判斷null的情況不會走索引，因此切記要避免這樣的情況，一般在設計欄位結構的時候，請使用not null來定義欄位，同時如果想為空的欄位，可以設計一個0、""這類空字符代替，一方面要查詢空值時可通過查詢空字符的方式走索引檢索，同時也能避免MyBatis注入對象屬性時觸發空指針異常。

2.1.6、不要在條件查詢=前對欄位做任何運算

select * from zz_users where user_id * 2 = 8;
select * from zz_users where trim(user_name) = "熊貓";
複製代碼

zz_users用戶表中user_id、user_name欄位上都創建了索引，但上述這類情況都不會走索引，因為MySQL優化器在生成執行計劃時，發現這些=前面涉及到了邏輯運算，因此就不會繼續往下走了，會將具體的運算工作留到執行時完成，也正是由於優化器沒有繼續往下走，因此不會為運算完成後的欄位選擇索引，最終導致索引失效走全表查詢。

從這裡可以得出一點，千萬不要在條件查詢的=前，對欄位做任何運算，包括了函數的使用也不允許，因為經過運算處理後的欄位會變成一個具體的值，而並非欄位了，所以壓根無法使用到索引！

2.1.7、 !=、!<>、not in、not like、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;
複製代碼

雖然這樣看起來SQL變長了，但實際情況中查詢效率反而更高一些，因為後面的SQL可以走索引（對於其他的一些關鍵字也一樣，可以使用走索引的SQL來代替這些關鍵字實現）。

2.1.8、必要情況下可以強制指定索引

在表中存在多個索引時，有些複雜SQL的情況下，或者在存儲過程中，必要時可強制指定某條查詢語句走某個索引，因為MySQL優化器面對存儲過程、複雜SQL時並沒有那麼智能，有時可能選擇的索引並不是最好的，這時我們可以通過force index，如下：

select * from zz_users force index(unite_index) where user_name = "熊貓";
複製代碼

這樣就能夠100%強制這條SQL走某個索引查詢數據，但這種強制指定索引的方式，一定要建立在對索引結構足夠熟悉的情況下，否則效果會適得其反。

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

臨時表是一種數據緩存，對於一些常用的查詢結果可以為其建立臨時表，這樣後續要查詢時可以直接基於臨時表來獲取數據，MySQL默認會在內存中開闢一塊臨時表數據的存放空間，所以走臨時表查詢數據是直接基於內存的，速度會比走磁碟檢索快上很多倍。但一定要切記一點，只有對於經常查詢的數據才對其建立臨時表，不要盲目的去無限制創建，否則頻繁的創建、銷毀會對MySQL造成不小的負擔。

2.1.11、儘量將大事務拆分為小事務執行

經過之前《MySQL事務機制》、《MySQL鎖機制》、《MySQL事務與鎖實現原理》這幾章的學習後，咱們應該會知道：一個事務在執行事，如果其中包含了寫操作，會先獲取鎖再執行，直到事務結束後MySQL才會釋放鎖。

而一個事務占有鎖之後，會導致其他要操作相同數據的事務被阻塞，如果當一個事務比較大時，會導致一部分數據的鎖定周期較長，在高並發情況下會引起大量事務出現阻塞，從而最終拖垮整個MySQL系統。

• show status like 'innodb_log_waits';查看是否有大事務由於redo_log_buffer不足，而在等待寫入日誌。

大事務也會導致日誌寫入時出現阻塞，這種情況下會強制觸發刷盤機制，大事務的日誌需要阻塞到有足夠的空間時，才能繼續寫入日誌到緩衝區，這也可能會引起線上出現阻塞。

因此基於上述原因，在面對一個較大的事務時，能走異步處理的可以拆分成異步執行，能拆分成小事務的則拆成小事務，這樣可以在很大程度上減小大事務引起的阻塞。

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

之前在做項目開發時碰到過一些奇葩需求，就是要求一次性將所有數據全部返回，而後在前端去做篩選展現，這樣做雖然也可以，但如果一次性返回的數據量過於巨大時，就會引起網絡阻塞、內存占用過高、資源開銷過大的各類問題出現，因此如果項目中存在這類業務，一定要記住拆分掉它，比如分批返回給客戶端。

分批查詢的方式也被稱之為增量查詢，每次基於上次返回數據的界限，再一次讀取一批數據返回給客戶端，這也就是經典的分頁場景，通過分頁的思想能夠提升單次查詢的速度，以及避免大數據量帶來的一系列後患問題。

2.1.13、儘量避免深分頁的情況出現

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

select xx,xx,xx from yyy limit 100000,10; 
複製代碼

上述這條SQL相當於查詢第1W頁數據，在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; 
複製代碼

這種情況下，MySQL就會先按where條件篩選到數據之後，再獲取前十條數據返回，甚至還可以通過between做優化：

select xx,xx,xx from yyy where 有序欄位 between 1000000 and 1000010; 
複製代碼

這種方式就完全捨棄了limit關鍵字來實現分頁，但這種方式僅適合於基於遞增且連續欄位分頁。

那麼例如搜索分頁呢？這種分頁情況是無序的，因為搜索到的數據可以位於表中的任意行，所以搜索出的數據中，就算存在有序欄位，也不會是連續的，這該如何是好？這種情況下就只能在業務上限制深分頁的情況出現了，以百度為例：

雖然搜索mysql關鍵字之後，顯示大約搜索到了一億條數據，但當咱們把分頁往後拉就會發現，最大只能顯示76頁，當你再嘗試往後翻頁時就會看到一個提示：「限於網頁篇幅，部分結果未予顯示」。

上述百度的這個例子中，就從根源上隔絕了深分頁的出現，畢竟你都沒給用戶提供接下來的分頁按鈕了，這時自然也就無法根據用戶操作生成深分頁的SQL。

但上述這種思想僅局限於業務允許的情況下，以搜索為例，一般用戶最多看前面30頁，如果還未找到他需要的內容，基本上就會換個更精準的關鍵詞重新搜索。

哪如果業務必須要求展現所有分頁數據，此時又不存在遞增的連續欄位咋辦？哪這種情況下要麼選擇之前哪種很慢的分頁方式，要麼就直接拋棄所有！每次隨機十條數據出來給用戶，如果不想重複的話，每次新的分頁時，再對隨機過的數據加個標識即可。

2.1.14、SQL務必要寫完整，不要使用縮寫法

很多開發者，包含我在內，往往都喜歡縮寫語法，能夠簡寫的絕不寫全，比如：

-- 為欄位取別名的簡單寫法
select user_name "姓名" from zz_users;
-- 為欄位取別名的完整寫法
select user_name as "姓名" from zz_users;

-- 內連表查詢的簡單寫法
select * from 表1,表2... where 表1.欄位 = 表2.欄位 ...; 
-- 內連表查詢的完整寫法
select * from 表1 別名1 inner join 表2 別名2 on 別名1.欄位 = 別名2.欄位;

......
複製代碼

這類情況下還有很多，在寫的時候為了圖簡單，都會將一些能簡寫的SQL就簡寫，但其實這種做法也略微有些問題，因為隱式的這種寫法，在MySQL底層都需要做一次轉換，將其轉換為完整的寫法，因此簡寫的SQL會比完整的SQL多一步轉化過程，如果你考慮極致程度的優化，也切記將SQL寫成完整的語法。

2.1.15、基於聯合索引查詢時請務必確保欄位的順序性

在之前聊到過《聯合索引的最左前綴原則》，想要基於建立的聯合索引查詢數據，就必須要按照索引欄位的順序去查詢數據，否則可能導致所以完全利用聯合索引，雖然MySQL8.0版本中推出了《索引跳躍掃描機制》，但這種方案也會存在較大的開銷，同時還有很強的局限性，所以最好在寫SQL時，依舊遵循索引的最左前綴原則撰寫。

2.1.16、客戶端的一些操作可以批量化完成

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

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執行，能夠在很大程度上節省網絡資源的開銷，提升批量操作的執行效率。

這樣的方式同樣適用於修改場景，如果一個業務會出現批量修改的情況時，也切記不要用for循環來調用update語句對應的接口，而是應該再寫一個update/replace語句的批量修改接口。

2.2、SQL優化的業內標準

評判任何一件事情到底有沒有做好都會有標準，而SQL語句的執行時間也一樣，業內也早有了相應的標準，相信大家一定都聽說過下述這個用戶體驗原則：

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

上述這四條是用戶體驗感的四個等級，一般針對於C端業務而言，基本上都需要將接口響應速度控制到第二等級，即最差也要三秒內給用戶返迴響應，否則會導致體驗感極差，從而讓用戶對產品留下不好的印象。

所謂的三秒原則通常是基於C端業務而言的，對於B端業務來說，通常用戶的容忍度會高一些，也包括B端業務的業務邏輯會比C端更為複雜一些，所以可將響應速度控制到第三等級，也就是5s內能夠得到響應。針對於一些特殊類型的業務，如後台計算型的業務，好比跑批對帳、定時調度....等，這類因為本身業務就特殊，因此可不關注其響應速度。

回歸前面的用戶三秒體驗原則，似乎三秒也不難做到對嘛？基本上SQL語句在1~3秒內都能執行完成呀，但請牢記：這個三秒並不能全部分配給SQL執行，為什麼呢？因為用戶感受到的響應速度會由多方面的耗時組成，如下：

從上圖觀察中可得知，所謂給用戶的響應時間其實會包含各方面的耗時，也就是這所有的過程加一塊兒，必須要在1~3s內給出響應，而SQL耗時屬於「系統耗時→數據操作耗時」這部分，因此留給SQL語句執行的時間最多只能有500ms，一般在用戶量較大的門戶網站中，甚至要求控制在10ms、30ms、50ms以內。

三、MySQL索引優化

10~50ms聽起來是個很難抵達的標準，但實際大部分走索引查詢的語句基本上都能控制在該標準內，那又該如何判斷一條SQL會不會走索引呢？這裡需要使用一個工具：explain，下面一起來聊一聊。

3.1、explain分析工具

在之前的《索引應用篇》中曾簡單聊到過ExPlain這個工具，它本身是MySQL自帶的一個執行分析工具，可使用於select、insert、update、delete、repleace等語句上，需要使用時只需在SQL語句前加上一個explain關鍵字即可，然後MySQL會對應語句的執行計劃列出，比如：

上述這些欄位在之前也簡單提到過，但並未展開細聊，所以在這裡就先對其中的每個欄位做個全面詳解（MySQL8.0版本中才有12個欄位，MySQL5.x版本只有10個欄位）。

3.1.1、id欄位

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

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

3.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：採用物化的方式執行的包含派生表的查詢語句。

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

3.1.3、table欄位

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

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

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

3.1.4、partitions欄位

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

3.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這類全掃描的形式。

3.1.6、possible_keys欄位

這個欄位會顯示當前執行計劃，在執行過程中可能會用到哪些索引來檢索數據，但要注意的一點是：可能會用到並不代表一定會用，在某些情況下，就算有索引可以使用，MySQL也有可能放棄走索引查詢。

3.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的方式強制指定。

3.1.8、key_len欄位

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

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

3.1.9、ref欄位

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

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

3.1.10、rows欄位

這一列代表執行時，預計會掃描的行數，這個數字對於InnoDB表來說，其實有時並不夠準確，但也具備很大的參考價值，如果這個值很大，在執行查詢語句時，其效率必然很低，所以該值越小越好。

3.1.11、filtered欄位

這個欄位在早版本中也不存在，它是一個百分比值，意味著表中不會掃描的數據百分比，該值越小則表示執行時會掃描的數據量越大，取值範圍是0.00~100.00。

3.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;。
• ......

除開上述內容外，具體的可參考《explain-Extra欄位詳解》，其中介紹了Extra欄位可能會出現的所有值，最後基於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

上面這個排序中，僅列出了一些實際查詢執行時的性能排序，對於一些不重要的就沒有列出了。

3.2、索引優化參考項

在上面咱們簡單介紹了explain工具中的每個欄位值，欄位數量也比較多，但在做索引優化時，值得咱們參考的幾個欄位為：

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

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

3.3、索引優化實踐

上面了解了索引優化時的一些參考項，接著來聊聊索引優化的實踐，不過在優化之前要先搞清楚什麼是索引優化，其實無非就兩點：

• 把SQL的寫法進行優化，對於無法應用索引，或導致出現大數據量檢索的語句，改為精準匹配的語句。
• 對於合適的欄位上建立索引，確保經常作為查詢條件的欄位，可以命中索引去檢索數據。

總歸說來說去，也就是要讓SQL走索引執行，但要記住：並非走了索引就代表你的執行速度就快，因為如果掃描的索引數據過多，依舊可能會導致SQL執行比較耗時，所以也要參考type、rows、filtered三個欄位的值，來看看一條語句執行時會掃描的數據量，判斷SQL執行時是否掃描了額外的行記錄，綜合分析後需要進一步優化到更細粒度的檢索。

索引優化其實本質上，也就是遵循前面第二階段提出的SQL小技巧撰寫語句，以及合理的使用與建立索引，對於索引怎麼建立和使用才最好，具體可參考《索引應用篇-建立與使用索引的正確姿勢》。

一般來說，SQL寫好了，索引建對了，基本上就已經優化到位了，對於一些無可避免的慢SQL執行，比如複雜SQL的執行、深分頁等情況，要麼就從業務層面著手解決，要麼就接受一定的耗時，畢竟凡事不可能做到十全十美。

四、SQL優化篇總結

到這裡《SQL優化篇》又接近尾聲了，其實所謂的SQL優化，本質上是改善SQL的寫法，理解一些SQL導致索引失效的場景，以及撰寫SQL時的一些技巧，就能寫出一手優質SQL，當你寫的所有語句執行效率都還不錯，那你就能夠被稱得上是一位寫SQL的高手。

不過做過SQL優化的小夥伴，其實應該能夠發現這裡還少寫了一個十分重要的內容，也就是慢查詢語句優化，這裡是刻意為之，對於慢查詢語句的優化，本質上脫離了SQL優化的範疇，更多屬於線上問題的一種情況，有些SQL在開發環境中執行時，可能效率並不算低，但放到線上時可能會偶爾出現的執行緩慢的情況，因此對於這類SQL語句該如何排查呢？具體的方法會放到下篇文章：《MySQL線上排查篇》來詳細闡述~