?曾幾何時，我信誓旦旦的認為只要 SQL 寫的好，面試過程永不倒。結果在一次又一次的征途中，「最終還是以完敗落下帷幕」。

結果都源于注重使用而忽略原理，從而不知也不了解「SQL」優化應如何面對。讀文的你，如今可有這樣的滄桑？

1、SQL 執行原理過程分析

「話說開篇就談 SQL 的執行原理，不太好吧！」

「錯，那是你 矮了，心胸狹隘了」。這都是優化的根基呀，SQL 語句最終落地執行的場景如果都不明白，那根本就能擁有優化的思路。那 SQL 優化的定義是什么？

「簡單點，就是以最快的時間獲取到需要的結果。那怎么快？如何快？」

首先來看看 「MySQL」 檢索數據原理過程。例：select * from table id > 5 and name = 'zhazha'

假如。ID 為「主鍵索引」，那么 SQL 執行過程可分為兩步：

• 優化器調用存儲引擎提供的 API 接口，通過主鍵索引搜索 ID > 5 的記錄，讀取并載入到 MySQL 服務層內部(即內存)
• 在服務層中，獲取的記錄要一一和 name 字段進行對比。一旦記錄滿足條件就開始逐步發送給客戶端。每一性的結果集都通過 MySQL 協議進行封包，并寫入緩存區，然后再批量傳輸。

故此 「SQL 優化」 提速應從三方面入手：

• 「掃描的行數，使用索引減少掃描行記錄」
• 「返回的行數，通過 where 條件減少不必要行的判斷。一般 MySQL 有三種應用 where 條件」
  • ps: 指定 where 條件字段為 索引列，針對聚集索引這種情況
  • 在索引中使用 where 條件來過濾不匹配的記錄。這是在存儲引擎層完成的。
  • 使用索引覆蓋掃描(在 Extra 列中出現 Using index 情況)來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結果。where 條件將在 MySQL 服務器層完成，但無須再回表查詢記錄。
  • 從數據表中返回數據，然后過濾不滿足條件的記錄(在 Extra 列中出現 Using Where)。where 條件將在 MySQL 服務器層 完成，MySQL 需先從數據表讀出記錄然后過濾。

在這里，條件的選擇，將決定服務器層過濾多少數據。
比如：用戶表有 10000 條記錄，每個用戶的 「email」 是唯一的，如果用戶使用 「email」 作為用戶名登陸的話，大多數可能會這些寫。

SELECT * FROM t_user WHERE email=?;

但上面的語句實現了查詢 email 對應的用戶信息，但由于 email 這一列沒有加索引，會導致全表掃描。然后在服務層里面再進行 where 條件的一一對比。如何改呢？

　SELECT * FROM t_user WHERE email=? LIMIT 1;

加上 LIMIT 1，只要找到了對應的一條記錄，就不會繼續向下掃描。

所以在寫 SQL 條件語句時，可查看針會對多少數據結果集。

• 「響應時間，即服務時間和排隊的時間。像 I/O 執行、鎖等待消耗的時間」

可似 我還是有幾個疑問？就下面

• 數據庫響應為什么要一條條結果發送呢？不是一起批量響應結果更為方便嗎？結果集以每條記錄發送，后面又怎么成為批量傳輸了？
• 那如果是 join 怎么執行的呀？SQL 語句怎么來呢？

騷年，就你這個發問，真乃骨骼驚奇、萬中挑一的編程奇才。實力雄厚這門東西，都是指日可待。到這里是不是應該來個贊呢？

「數據庫傳輸問題：」MySQL 逐步返回結果有兩個好處:

• 服務器端無須存儲太多結果，也就不會因為要返回太多結果而 消耗太多內存。
• 這樣的處理也讓 MySQL 客戶端第一時間獲的返回的結果。

「數據批量傳輸：」主要因為 MySQL 協議采用 TCP 協議傳輸，而 TCP 是一個流式協議傳輸，類似于你打開水龍頭后，水就一直流出來。

所以數據發送前都會先寫入到網卡的緩沖區里面，滿了之后才會一并的發給客戶端。而 MySQL 協議封包，為的就是防止數據出現丟或和數據不一致問題。

「join 查詢問題：」

首先我們得明白數據庫是如何進行關聯查找，對于聯合查詢，MySQL 先將一系列的單個查詢結果放到一個臨時表中，然后再重新讀出臨時表數據來完成聯合查詢。

「注：以 關聯表 和 主表 說明，當前表 為查詢的主表，關聯表為 join 連接的表」

MySQL 先根據 where 條件在主表中找到滿足條件的記錄，然后循環取出單條數據，并嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有關聯表中匹配的行為止。然后根據各個表匹配到的行，返回查詢中需要的各個列。

MySQL 會嘗試在最后一個關聯表中找到所有匹配的行，如果最后一個關聯表無法找到更多的行，MySQL 就返回到上一層次關聯表，看是否能夠找到更多的匹配記錄，依此類推迭代執行。

注：在 MySQL 的概念中，每個查詢都是一次關聯。所以讀取結果臨時表也是一次關聯。

綜上，關聯查詢實際就是一個分解查詢的關系，用主表關聯字段的只做為條件，去找到關聯表中滿足數條件得數據。

這也是為什么要 join 字段上建立索引的原因，像什么 「order by limit group by」等函數，實際上都是在拿到每個查詢結果集之后或者之前在索引里面行處理。你所寫的條件，會根據表的執行順序來進行使用，但有時優化器會更改執行順序。

但我想給關聯表加入限制條件呢？

你直接在 on 字段后面 用邏輯連接符號，加入 where 即可。例下：

ON a.id = b.id and b.name = '吒吒輝'

2、前方高能預警，怎么優化 SQL，這方向怎么選？

到現在，大家應該對 SQL 優化有了屬于自己的側重點吧。根據業務定位到時間消耗最大并優化，試問，這樣你還不能寫出符合你業務的語句嗎？

?

在這里請給我來一個點贊和關注吧，救救在下吧

?

首先定位到某些業務查詢很慢，然后再拆解到底是哪個部分最耗時間。

響應時間

上面談到，響應時間=服務時間+排隊時間。

• 服務時間是指數據庫處理這個查詢真正花了多長時間。
• 排隊時間是指服務器因為等待某些資源而沒有真正執行查詢的時間。可能是等 I/O 操作完成，也可能是等待行鎖一般最常見和重要的等待是 I/O 和鎖等待，但是實際情況更加復雜，你免 SQL 語句中的執行函數和其它內存上的分配等等吧。
  所以針對一個查詢很慢語句，首先得看這個語句是查詢上的問題還是服務器的問題。如果是查詢語句慢，那你優化 SQ 語句。如果是服務器上整體程序都比較慢，大多數都是服務器負責太高。

那要怎么做呢？

• 使用 SHOW GLOBAL STATUS
  通過 SHOW GLOBAL STATUS查看每秒的查詢數(Queries)、Threads* connected 和 Threads* running (表示當前正在執行查詢的線程數)。

進而定位 mysql 工作線程是否達到瓶頸。

這三個數據的趨勢對于服務器級別偶爾停頓的敏感性很高。一般發生此類問題時，根據原因的不同和應用連接數據庫方式的不同，每秒的查詢數一般會下跌。所以我們可寫個腳本每秒去獲取數據庫的執行狀態，從而定位目前數據庫的負載能力。

• 使用 SHOW PROCESSLIST

通過 SHOW PROCESSLIST 找到數據庫下面，每個工作線程的執行狀態，如果某一業務下執行比較慢，那么它的工作線程一定長時間處于查詢狀態(query),這時你就需要針對線程所在業務的 SQL 進行優化。

• 「主要列含義」：
  • id 列:一個標識，你要 kill 一個語句連接就有用它。
  • user 列: 顯示當前用戶，如果不是 root，這個命令就只顯示你權限范圍內的 sql 語句。
  • command 列:顯示當前連接的執行的命令，一般就是休眠(sleep)，查詢(query)，連接(connect)，其他查詢鎖住(Locked)。
  • state 列:顯示使用當前連接的 sql 語句的狀態，很重要的列，請注意，state 只是語句執行中的某一個狀態，一個 sql 語句，以查詢為例，可能需要經過 copying to tmp table，Sorting result，Sending data 等狀態才可以完成。
• 使用慢日志查詢

如果要通過查詢日志發現問題，需要開啟慢查詢日志并在全局級別設置 「long_query_time 為 0」 , 還一個 log_output 參數要注意，它有 table 和 file 選擇，分別代表日志存儲為數據表還是文件。

并設置并且要確認所有的連接都采用了新的設置。這可能需要重置所有連接以使新的全局設置生效;

隨著服務器運行過久，日志文件可能達到幾百 G，這時候如果打開文件查找就不是一件理智的事情。可以根據 MySQL 每秒將當前時間寫入日志中的模式統計每秒的查詢數量:

awk /^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' slow-query.1og

如果發現 mysql 吞吐量有高低起伏，直接根據對應的時間點看到詳細的記錄，進而分析問題。

針對優化之前，首先得看到 MySQL 的性能是否為服務資源不夠處理？什么業務線程執行的慢？這個慢操作產生的原因是什么？等確定要優化的問題。所以 show global status 和 show processlist 都是很好的手段。

減少掃描的行

要想減少掃描的行，就得看到有哪些途徑可以減 MySQL 查詢的行？「索引」 就是選擇

對索引原理不了解可以去看 互聯網大廠面試，談索引就直逼這些底層？難的是我不懂這些原理

當你查詢中用到了索引字段，那么一般都會使用到索引，這里列舉不會使用索引的方式，更重要的是明白索引的底層結構是什么。

索引走不走還是得看字段在 where 條件后上的搭配與索引原理。因為多條件會涉及到「索引合并優化」

注：索引合并優化(Index merge optimization)，當查詢中單張表可以使用多個索引時，同時掃描多個索引并將掃描結果進行合并。

• OR
  使用它的前提條件，你需保證 OR 兩邊的字段都需要建立索引。如果不保證將退化為全表掃描。如：查詢

 SELECT * FROM T1 WHERE a1="xxx" OR a2=""xxx"

如果 c1 和 c2 列上分別有索引，可以按照 c1 和 c2 條件進行查詢，再將查詢結果合并(union)操作，得到最終結果。「使用了索引合并有什么特點？」

通過 explain 對 sql 語句進行分析，如果使用了索引合并，那么會在 type 列會顯示 index_merge，key 列會顯示出所有使用的索引。

為什么當 where 字段列，一旦其中有一個不是索引列。就不走索引掃描呢？

因為全表只掃一次，而你索引是根據字段的索引樹找的，索引最終就會合并全表和索引方式查找的的結果返回給客戶端。但掃描的方式就終確認為全表方式。

以下為匹配記錄:

如下是索引使用情況

很多人說 OR 需要用 union all 或者 in 優化，如果都知道 OR 的索引觸發條件，還需進行結果搜索合并與 in 的條件限制嗎？你細品

• in
  in 是查詢上面的一種組合方式，根據里面其字段值進行搜索，但如果這個組合條件過多就導致優化器沒辦法很好的使用索引，并且使用 in 的字段需建立索引，這樣才能提升查找效率。

如：EXPLAIN SELECT * FROM users WHERE id in (5,8,9) AND name in ('Prof. Chase McKenzie II','Mr. Porter Prosacco IV') AND remember_token IN ('Wjz2H8WrpK', '5WYeM5FPKf');有索引：

無索引，直接全表掃描

EXPLAIN SELECT * FROM users WHERE name in ('Prof. Chase McKenzie II','Mr. Porter Prosacco IV') AND remember_token IN ('Wjz2H8WrpK', '5WYeM5FPKf');

• 范圍匹配

范圍條件的查詢，無法在使用范圍列后面的其他的索引列。一般可以優化為多個等值查詢。即轉為 in 的過列字段進行值的匹配。

• != 和<>

如果索引字段使用到 != 和 <> 操作符，那么查詢引擎將放棄使用索引而進行全表掃描。因為優化器會權衡查找的行數，如果取反，那么剩下的數據查找的次數可能接近于全表，如果是二級索引還有回表的影響，更會加大 MySQL 的查找次數。

返回的行數

返回行數主要由不同類型的查詢，把數據從磁盤讀取到服務層后使用 where 后面的條件或者查詢相關語句來進行限制。

• count
  使用 count 計數時，不要在括號里面指定一列來統計行數，直接采用 count(*)性能會更好。這就有點難為人。為什么呢？

因為 MySQL 確認括號內的表達式值不可能為空時，實際上就是在統計行數。當我們使用 COUNT()的時候，這種情況下通配符并不會像我們猜想的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計所有的行數。

都說：「MyISAM 的 COUNT()函數總是非常快,不過這是有前提條件的，即只有沒有任何 WHERE 條件的 COUNT(*) 才非常快」

因為此時無須實際地去計算表的行數。MySQL 可以利用存儲引擎的特性直接獲得這個值。如果 MySQL 知道某列 col 不可能為 NULL 值，那么 MySQL 內部會將 COUNT(col)表達式優化為 COUNT(*)。

那如何優化？

• 反向操作

統計 ID 大于 5 的城市。可以像下面這樣來寫這個查詢:mysql> SELECT COUNT(*) FROM world.City WHERE ID > 5;

如果將條件反轉一下，先查找 ID 小于等于 5 的城市數，然后用總城市數一一減就能得到同樣的結果，卻可以將掃描的行數減少到 5 行以內:

SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*) FROM world.City WHERE ID <= 5;

• 關聯查詢

確保 ON 或者 USING 子句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表 A 和表 B 用列 C 關聯的時候，如果優化器的關聯順序是 B、A,那么就不需要在 B 表的對應列上建索引。沒有用到的索引只會帶來額外的負擔。一般來說， 除非有其他理由，否則只需要在關聯順序中的第二個表的相應列上創建索引。

確保任何的 GROUP BY 和 ORDER BY 中的表達式只涉及到一個表中的列，這樣 MySQL 才有可能使用索引來優化這個過程。

• 子查詢關于子查詢優化就是盡可能使用關聯查詢代替
• Group by 和 DISTINCT

MySQL 都使用同樣的辦法優化這兩種查詢，事實上，MySQL 優化器會在內部處理的時候相互轉化這兩類查詢。它們都可以使用索引來優化，這也是最有效的優化辦法。在 MySQL 中，當無法使用索引的時候，GROUP BY 使用兩種策略來完成:使用臨時表或者文件排序來做分組

對于任何查詢語句，這兩種策略的性能都有可以提升的地方。可以通過使用提示 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 來讓優化器按照你希望的方式運行。

如果需要對關聯查詢做分組(GROUP BY)，并且是按照查找表中的某個列進行分組，通常采用查找表的標識列分組的效率會比其它列更高。例如下面的查詢效率不會很好;

SELECT actor.first_name, actor.last_name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY actor.first_name, actor.last_name;

如果查詢按照下面的寫法效率則會更高:

SELECT actor.first name, actor.last name, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor.id) GROUP BY film_actor.actor.id;

使用 actor.actor_id 列分組的效率甚至會比使用 film_actor.actor_id 更好。如果沒有通過 ORDER BY 子句顯式地指定排序列，當查詢使用 GROUP BY 子句的時候，結果集會自動按照分組的字段進行排序。如果不關心結果集的順序，而這種默認排序又導致了需要文件排序，則可以使用 ORDER BY NULL, 讓 MySQL 不再進行文件排序。也可以在 GROUP BY 子句中直接使用 DESC 或者 ASC 關鍵字，使分組的結果集按需要的方向排序。

• Limit
  在系統中需要進行分頁操作的時候，我們通常會使用 LIMIT 加上偏移量的辦法實現，同時加上合適的 ORDER BY 子句。如果有對應的索引，通常效率會不錯，否則，MySQL 需要做大量的文件排序操作。

一個非常令人頭疼的問題就是，在偏移量非常大的時候，例如可能是 LIMIT 10000,20 這樣的查詢，這時 MySQL 需要查詢 10020 條記錄然后只返回最后 20 條，前面 10000 條記錄都將被拋棄，這樣的代價非常高。如果所有的頁面被訪問的頻率都相同，那么這樣的查詢平均需要訪問半個表的數據。

要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。優化此類分頁查詢的個最簡單的辦法就是盡可能地使用索引覆蓋掃描，而不是查詢所有的列。然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候，這樣的效率會提升非常大。考慮下面的查詢:

SELECT f1lm_id, description FROM sakila.fi1m ORDER BY title LIMIT 50, 5;

如果表非常大，可改寫成這樣

SELECT film.film_id, film.description FROM sakila.film INNER JOIN ( SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5 ) AS limt USING(film_id);

這里的“延遲關聯”將大大提升查詢效率，它讓 MySQL 掃描盡可能少的頁面，獲取需要訪問的記錄后再根據關聯列回原表查詢需要的所有列。這個技術也可以用于優化關聯查詢中的 LIMIT 子句。

LIMIT 和 OFFSET 的問題，其實是 OFFSET 的問題，它會導致 MySQL 掃描大量不需要的行然后再拋棄掉。如果可以使用書簽記錄上次取數據的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用 OFFSET。例如，若需要按照租借記錄做翻頁，那么可以根據最新一條租借記錄向后追溯，這種做法可行是因為租借記錄的主鍵是單調增長的。首先使用下面的查詢獲得第一組結果:

SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;

假設上面的查詢返回的是主鍵為 16049 到 16030 的租借記錄，那么下一頁查詢就可以從 16030 這個點開始:

SELECT * FROM sakila. rental WHERE rental id < 16030 ORDER BY rental id DESC LIMIT 20;

這樣無論翻頁到多么后面，其性能都會很好。還可以包括預先計算的匯總表，或者關聯到一個冗余表，冗余表只包含主鍵列和需要做排序的數據列。

設置最后一頁，分頁的時候，常用的技巧是在 LIMIT 語句中加上 SQL_CALC_FOUND_ROWS 提示，這樣就可以獲得去掉 LIMIT 以后滿足條件的行數，因此可以作為分頁的總數。看起來，MySQL 做了一些非常“高深”的優化，像是通過某種方法預測了總行數。但實際上，MySQL 只有在掃描了所有滿足條件的行以后，才會知道行數，所以加上這個提示以后，不管是否需要，MySQL 都會掃描所有滿足條件的行，然后再拋棄掉不需要的行，而不是再滿足 LIMIT 的行數后就終止掃描。所以該提示的代價可能非常高。一個更好的設計是將具體的頁數換成“下一頁”按鈕，假設每頁顯示 20 條記錄，那么我們每次查詢時都是用 LIMIT 返回 21 條記錄并只顯示 20 條，如果第 21 條存在，那么我們就顯示“下一頁”按鈕，否則就說明沒有更多的數據，也就無須顯示“下一頁”按鈕了。

另一種做法是先獲取并緩存較多的數據，例如， 緩存 1000 條然后 每次分頁都從這個緩存中獲取。這樣做可以讓應用程序根據結果集的大小采取不同的策略，如果結果集少于 1000，就可以在頁面上顯示所有的分頁鏈接，因為數據都在緩存中，所以這樣做性能不會有問題。如果結果集中于 1000,則可以在頁面上設計一個額外的“找到的結果多于 1000 條”之類的按鈕。這兩種策略都比每次生成全部結果集再拋棄掉不需要的數據的效率要高很多。

• UNION
  MySQL 總是通過創建并填充臨時表的方式來執行 UNION 查詢。因此很多優化策略在 UNION 查詢中都沒法很好地使用。經常需要手工地將 WHERE、LIMIT ORDERBY 等子句“下推”到 UNION 的各個子查詢中，以便優化器可以充分利用這些條件進行優化(例如，直接將這些子句冗余地寫一份到各個子查詢)。

總結

• SQL 優化的重心由 3 方面消耗的時間來決定：響應時間、掃描時間、查找時間。
• 掃描查詢主要由索引提速，如果一些其它條件(order by )與索引列相關，那可保證掃描的數據行更少，這樣就不用在服務層做 where 處理。
• MySQL 優化器會自行優化 where 條件，讓執行條件顯得更簡單。且編寫 where 條件也需考慮條件對結果數據的處理。
• 服務器負載也是很關鍵的，如果整體負載過高，那數據庫性能肯定會直線下降。

如有幫助，歡迎關注@蓮花童子哪吒