(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線上排查篇》來詳細闡述~

SQL優化篇：如何成為一位寫優質SQL語句的絕頂高手