大綱
1.禁止或改寫SQL避免自動半連線最佳化
2.指定索引避免按聚簇索引全表掃描大表
3.按聚簇索引掃描小表減少回表次數
4.避免產生長事務長時間執行
1.禁止或改寫SQL避免自動半連線最佳化
(1)業務場景介紹
(2)SQL效能問題分析
(3)SQL效能調優
(1)業務場景介紹
某網際網路公司的使用者量比較大，有百萬級日活使用者的一個量級。該公司的運營系統會專門透過各種條件篩選出大量使用者傳送推送訊息，比如一些促銷活動的訊息、辦會員卡的訊息、特價商品的訊息。在這個過程中，比較耗時的是篩選使用者的過程。
因為該公司當時的使用者情況是：日活百萬級、註冊使用者是千萬級，而且還沒有進行分庫分表，其資料庫裡的使用者表可能就一張，單表裡是上千萬的使用者資料。
現在對運營系統篩選使用者的SQL做一個簡化，這個SQL經過簡化看起來可能是這樣的：
SELECT id, name FROM users WHERE id IN
(SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)一般儲存使用者資料的表會分為兩張表：users表用來儲存使用者的核心資料，如id、name、暱稱、手機號等資訊。users_extent_info表則儲存使用者的一些擴充資訊，如最近登入時間等。
所以上面的SQL語句的意思就是：首先有個子查詢向使用者擴充資訊表查詢最近登入時間小於某時間的使用者，然後在外層查詢用in去查詢id在子查詢結果範圍裡的users表的所有資料，此時這個SQL往往會查出來很多資料，可能幾千、幾萬、幾十萬。
所以一般執行這類SQL前，會先跑一個count聚合函式看有多少條資料，然後在記憶體裡做一個小批次多批次讀取資料的操作。
SELECT COUNT(id) FROM users WHERE id IN
(SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)比如判斷如果在1000條以內，那麼就一下子讀取出來。如果超過1000條，可透過limit語句每次從該結果集裡查1000條資料。查1000條就做一次批次push，然後再查下一批1000條。
這就是這個案例的一個完整的業務背景，那麼它會產生的問題是：在千萬級資料量的大表場景下，執行上面查詢數量的SQL需耗時幾十秒，所以這個SQL急需最佳化。
(2)SQL效能問題分析
透過如下語句可得這個複雜SQL的執行計劃：
EXPLAIN SELECT COUNT(id) FROM users WHERE id IN
(SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)為了方便檢視，下面對執行計劃進行簡化，只保留最關鍵的欄位。另外，下面執行計劃是在測試環境的單表5萬條資料場景下跑出來的。即使是幾萬條資料，這個SQL都跑了十多秒，所以足夠復現生產問題。
+----+-------------+-------------------+-------+----------------+------+----------+-------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+-------------+-------------------+-------+----------------+-----------------+-------+
| 1 | SIMPLE | <subquery2> | ALL | NULL | NULL | 100.00 | NULL |
| 1 | SIMPLE | users | ALL | NULL | 49651| 10.00 | Using where; Using join buffer(Block Nested Loop) |
| 2 | MATERIALIZED| users_extent_info | range | idx_login_time | 4561 | 100.00 | NULL |
+----+-------------+-------------------+-------+----------------+------+----------+-------+從上面的執行計劃由下往上看，可清晰看到這個SQL語句的執行過程。首先第一條執行計劃是在第三行，針對的是子查詢的執行計劃。表示對users_extent_info使用idx_login_time索引做了range型別的查詢，查出來4561條資料，沒有做其他的額外篩選，所以filtered是100%。MATERIALIZED表明把子查詢的結果集進行物化，物化成一個臨時表。這個臨時表物化，會把4561條資料臨時存放到磁碟檔案裡，該過程較慢。
然後第二條執行計劃的ALL型別表明，會針對users表進行全表掃描。會掃出來49651條資料，其中Extra顯示了一個Using join buffer的資訊。這個Using join buffer明確表示，此處在執行join操作。
最後第三條針對子查詢的物化臨時表，也就是做全表查詢。把裡面的資料都掃描一遍，那麼為什麼要對這個臨時表進行全表掃描呢？原因就是讓users表的每一條資料，跟物化臨時表裡的資料進行join。所以針對users表裡的每一條資料，會去全表掃描一遍物化臨時表，查詢物化臨時表裡哪條資料是跟它匹配的，從而篩選出一條結果。
第二條執行計劃的全表掃描結果表明一共掃到了49651條資料，但是全表掃描的過程中，因為去跟物化臨時表執行了一個join操作，而物化臨時表就4561條資料，所以第二條執行計劃的filtered顯示10%。也就是說，最終從users表裡篩選出的也是4000多條資料。
以上的執行計劃，不同MySQL版本可能不一樣，甚至差別很大。但是對這個SQL語句的執行計劃過程的分析過程基本是一樣的。
(3)SQL效能調優
一.總結上述SQL的執行過程
EXPLAIN SELECT COUNT(id) FROM users WHERE id IN
(SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)
+----+-------------+-------------------+-------+----------------+------+----------+-------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+-------------+-------------------+-------+----------------+-----------------+-------+
| 1 | SIMPLE | <subquery2> | ALL | NULL | NULL | 100.00 | NULL |
| 1 | SIMPLE | users | ALL | NULL | 49651| 10.00 | Using where; Using join buffer(Block Nested Loop) |
| 2 | MATERIALIZED| users_extent_info | range | idx_login_time | 4561 | 100.00 | NULL |
+----+-------------+-------------------+-------+----------------+------+----------+-------+首先執行子查詢查出4561條資料，然後將這些資料物化成一個臨時表。接著對users主表進行全表掃描，本質就是和物化臨時表進行join操作。全表掃描的過程會把users表的每條資料都放到物化臨時表裡全表匹配。
二.上述SQL為什麼會這麼慢
根據explain出來的執行過程，對users表的全表掃描很耗時。對users表的每一條資料再到物化臨時表裡做全表掃描，也很耗時。所以整個過程必然是非常慢的，幾乎沒怎麼用到索引。
三.semi join是什麼意思
那麼為什麼會出現上述的這麼一個過程：即首先全表掃描users表，然後再和物化臨時表進行join，接著join的時候還全表掃描物化臨時表。
這裡有一個技巧，就是在執行完SQL的explain命令，看到執行計劃後，可以執行一下show warnings命令。這個show warnings命令此時顯示出來的內容如下：
/* select#1 */
select count(`d2.`users`.`user_id``) AS `COUNT(users.user_id)`
from `d2`.`users` `users` semi join xxxxxx
...下面省略一大段內容，因為可讀性實在不高，重點關注的應該是裡面的semi join這個關鍵字。這裡就顯而易見了，MySQL在生成執行計劃時：自動把一個普通的in子句最佳化成基於semi join來進行in + 子查詢的操作。
semi join的意思就是：對users表裡每一條資料，去物化臨時表進行全表掃描做semi join。此時不需要把users表裡的資料真的跟物化臨時表裡的資料join上，而是隻要users表裡的一條資料在物化臨時表裡找到匹配的資料就返回。這就叫做semi join，它是用來篩選的，所以慢就慢在這裡了。
四.如何最佳化semi join
那既然知道了是semi join和物化臨時表導致的問題，那應該如何最佳化？
先執行SET optimizer_switch='semijoin=off'，即關閉掉半連線最佳化。再執行EXPLAIN命令看一下執行計劃，發現此時會恢復為正常的狀態。有個SUBQUERY子查詢，基於range方式掃描索引查詢出4561條資料。有一個PRIMARY主查詢，基於id這個PRIMARY聚簇索引去執行的搜尋。最後再重新執行該SQL，發現效能一下提升了幾十倍，變成100多毫秒。
因此到此為止，這個SQL的效能問題，就是MySQL自動執行的semi join半連線最佳化導致的。一旦禁止semi join自動最佳化，讓MySQL基於索引執行，效能是可以的。
當然生產環境是不能更改這些設定的，所以要嘗試修改SQL語句的寫法。在不影響語義的情況下，儘可能的去改變SQL語句的結構和格式，最終被嘗試出了一個寫法如下所示：
SELECT COUNT(id)
FROM users
WHERE (
id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)
OR id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < -1)
)上述寫法WHERE語句的OR後面的第二個條件，根本是不可能成立的。因為沒有資料的latest_login_time小於-1，所以不會影響SQL語義。但是可以發現改變了SQL的寫法後，執行計劃也隨之改變。沒有再進行semi join最佳化，而是正常用子查詢，主查詢也基於索引執行。上線了這個SQL語句後，效能從幾十秒一下子就變成幾百毫秒了。
2.指定索引避免按聚簇索引全表掃描大表
(1)業務場景引入
(2)SQL效能問題分析
(3)SQL效能調優分析
(4)案例總結
(1)業務場景引入
前面案例的主要問題在於MySQL內部自動使用了半連線最佳化，結果半連線時導致大量無索引的全表掃描，引發了效能的急劇下降。接下來的案例也類似，MySQL在選擇索引時選了一個不太合適的索引。
先從當時線上的商品系統出現的一個慢查詢告警開始講起，某天晚上突然收到線上資料庫的頻繁報警。這個報警的意思是，資料庫突然湧現出了大量慢查詢。
因為大量的慢查詢，導致每個資料庫連線執行一個慢查詢都要耗費很久。這樣也必然導致突然過來的很多查詢需要讓資料庫開闢出更多的連線。因此這時也發出了報警，顯示資料庫的連線突然也暴增了。於是連線池被打滿，每個連線都要執行一個慢查詢，慢查詢還特別慢。
接著引發的問題，就是資料庫的連線池全部被打滿，沒法開闢新的連線。但是還持續有新的查詢傳送過來，導致資料庫沒法處理新的查詢。很多查詢發到資料庫直接阻塞然後超時，導致線上商品系統頻繁報警，出現了大量資料庫查詢超時報錯的異常。
這種情況基本意味著商品資料庫以及商品系統瀕臨崩潰，大量慢查詢耗盡了資料庫的連線資源，最終導致資料庫沒法執行新查詢。商品資料庫沒法執行查詢，使用者就沒法正常使用商品系統。雖說商品資料會有多級快取，但在下單等過程，還是會大量請求MySQL。也就是晚高峰時，商品系統本身TPS大致是在每秒幾千；因此這時資料庫的監控裡顯示，每分鐘的慢查詢超過了10w+，也就是商品系統大量的查詢都變成了慢查詢。
那麼慢查詢的都是一些什麼語句呢？其實主要就是下面這條語句，這裡做了一個簡化：
select * from products
where category='xx' and sub_category='xx'
order by id desc limit xx,xx這是一個很平常的SQL語句，就是根據商品的品類以及子類來瀏覽商品。這個語句執行的商品表裡大致是1億左右的資料量，這個量級已經穩定了很長時間，主要也就是這麼多商品。但上面的語句居然一執行就需要幾十秒，基本上資料庫的連線池會全部被慢查詢打滿。一個連線要執行幾十秒的SQL，然後才能執行下一個SQL，此時資料庫基本就廢了，沒法執行什麼查詢了，所以商品系統大量進行報警說查詢資料庫超時異常了。
(2)SQL效能問題分析
上面那條讓使用者根據品類篩選商品的SQL語句，在一個億級資料量的商品表裡執行，需要耗時幾十秒，結果導致資料庫的連線池全部打滿，商品系統無法執行，處於崩潰狀態。
下面來分析一下，到底為什麼會出現這樣的一個情況。這個表當時肯定是對經常用到的查詢欄位都建立好了索引的。那麼可以認為索引index_category(catetory, sub_category)肯定存在的，所以基本可以確認上面的SQL語句是可以用上索引的。
理論上一旦用上index_category索引，那麼按品類和子類在索引裡篩選。第一篩選很快速，第二篩出來的資料是不多的。所以按道理這個SQL語句應該執行的速度是很快的，即使表裡有億級資料，但是執行時間也最多不應超過1s。但是實際上這個SQL語句跑了幾十秒，那麼說明它肯定沒用上建立的那個索引，所以才會這麼慢。
那麼它到底是怎麼執行的呢，可以先來看一下它的執行計劃：
explain select * from products
where category='xx' and sub_category='xx'
order by id desc limit xx,xx從執行計劃中的possible_keys裡發現是有index_category的，結果實際用的key不是index_category索引，而是PRIMARY主鍵索引，而且Extra裡清晰寫了Using where。
到此為止，這個SQL語句為什麼效能這麼差，就真相大白了。它其實就是在主鍵的聚簇索引上進行掃描，一邊掃描一邊還用where條件裡的兩個欄位去進行篩選。所以這麼掃描的話，那必然會耗費幾十秒了。
因此為了快速解決這個問題，就需要強制改變MySQL自動選擇不合適的聚簇索引進行掃描的行為，也就是透過使用force index語法來強制改變，如下：
select * from products
force index(index_category)
where category='xx' and sub_category='xx'
order by id desc limit xx,xx使用上述語法過後，強制讓SQL語句使用指定的索引。此時再次執行會發現僅僅耗費100多毫秒而已，效能瞬間就提升上來了。因此當時在緊急關頭中，一下子就把這個問題給解決了。
這也是如何去強制改變MySQL執行計劃的實戰技巧，也就是如果MySQL使用了錯誤的執行計劃，可以使用force index語法。
(3)SQL效能調優分析
一.為什麼案例中的MySQL會預設選擇對主鍵的聚簇索引進行掃描
二.為什麼案例中沒有使用index_category這個二級索引進行掃描
三.即使使用了聚簇索引，為什麼這個SQL以前沒問題，現在突然有問題
一.為什麼MySQL預設選擇聚簇索引而不選擇index_category二級索引
因為這個商品表是一個億級資料量的大表，那麼對於它來說，index_category這個二級索引也是比較大的。所以此時MySQL就會對判斷要選擇執行方式一還是選擇執行方式二。
執行方式一：
首先從index_category二級索引裡來查詢到符合where條件的大量資料，接著需要做完order by id desc limit 0,10這個排序和分頁的操作，然後回表回到聚簇索引裡把所有欄位的資料都查出來。
舉個例子，比如首先從index_category二級索引裡查詢出幾萬條資料。接著因為二級索引裡包含主鍵id值，所以會按order by id desc執行排序。排序時需要對這幾萬條資料基於臨時磁碟檔案進行filesort排序。排序完後再按照limit 0,10語法，把指定位置的幾條資料拿出來。拿出資料後，再回到聚簇索引里根據id查詢，把資料的完整欄位查出來。
以上就是MySQL認為使用index_category索引時可能會發生的執行情況。MySQL可能會擔心從index_category二級索引裡查出來的資料太多了，這麼多資料還需要在臨時磁碟裡排序，這樣效能就會很差。因此MySQL就會認為使用index_category二級索引的方式不太好。
執行方式二：
MySQL可能會選擇這一種方式，也就是直接掃描主鍵的聚簇索引。因為聚簇索引都是按id值有序的，所以掃描時可按order by id desc掃描。而且已經知道是limit 0,10，也就是僅僅只要拿到10條資料就行了。所以MySQL在按order by id desc掃描聚簇索引時，就會對每一條資料都採用Using where的方式，使用where category='xx' and sub_category='xx'的條件進行匹配。符合條件的就直接放入結果集裡，最多隻需要放10條資料就可以返回。
此時MySQL認為，按順序掃描聚簇索引拿到10條符合where條件的資料，很可能比使用index_category二級索引那個方案更快，因此MySQL於是就採用了掃描聚簇索引的方式。
二.為什麼這個SQL以前掃描聚簇索引沒有問題，現在突然就有問題了
這個SQL語句以前線上上系統執行一直沒什麼問題，也就是之前即使採用掃描聚簇索引的方式，該SQL語句也沒有執行很慢。為什麼會在某一天晚上突然就大量報慢查詢，耗時幾十秒了呢？
原因如下：
where category='x' and sub_category='x'這個條件以前通常有返回值，也就是掃描聚簇索引時，通常都能很快找到符合條件的值然後進行返回，所以之前其實效能也沒什麼問題。但後來可能運營人員，在商品管理的時候加了幾種商品分類和子類，而這幾種分類和子類的組合其實還沒有對應的商品。
恰好那天晚上很多使用者使用這種分類和子類去篩選商品，而where category='新分類' and sub_category='新子類'是查不到資料的。所以底層在掃描聚簇索引時，掃來掃去都掃不到符合where條件的結果。一下子就把聚簇索引全部掃了一遍，等於是上億資料全表掃描了一遍，都沒找到where category='新分類' and sub_category='新子類'的資料。也正因如此才導致這個SQL語句頻繁的出現幾十秒的慢查詢，進而導致MySQL連線資源打滿，系統崩潰。
(4)案例總結
第一個案例，透過禁用MySQL半連線最佳化或改寫SQL語句結構來避免自動半連線最佳化。
第二個案例，就得透過force index語法來強制某個SQL使用指定的索引。
3.按聚簇索引掃描小表減少回表次數
(1)業務背景介紹
(2)如何進行最佳化
(3)案例總結
(1)業務背景介紹
有個商品評論系統的資料量非常大，擁有多達十億量級的評論資料，所以對這個評論資料庫進行了分庫分表。基本上分完庫和表過後，單表的評論資料在百萬級別。每一個商品的所有評論都是放在同一個庫的同一張表裡，這樣確保分頁查詢一個商品的評論時，從一個庫的一張表查詢即可。
有一些熱門商品，可能銷量多達上百萬，商品的評論可能多達幾十萬條。有一些使用者可能就喜歡看商品評論，不停對某熱門商品的評論進行分頁。一頁一頁翻，有時候還會用上分頁跳轉功能，直接輸入要跳到第幾頁去。所以這時就會涉及到一個問題，針對一個商品幾十萬評論的深分頁問題。
先來看一個經過簡化後的對評論表進行分頁查詢的SQL語句：
SELECT * FROM comments
WHERE product_id ='xx' and is_good_comment='1'
ORDER BY id desc LIMIT 100000,20這個SQL語句的意思就是：比如使用者選擇了檢視某個商品的評論，因此必須限定Product_id，同時還選了只看好評，所以is_good_commit也要限定一下。接著使用者要看第5001頁評論，那麼此時limit的offset就是(5001 - 1) * 20。其中20是每一頁的數量，起始offset是100000，所以limit後100000,20。
而這個評論表核心的索引就一個，即index_product_id。所以對上述SQL語句，正常情況下，肯定是會使用這個索引的。
步驟一：
透過index_product_id索引，根據product_id ='xx'條件，從表裡先篩選出指定商品的評論資料。
步驟二：
按照is_good_comment='1'條件，篩選這個商品評論資料裡的所有好評。但問題來了，這個index_product_id索引裡並沒有is_good_commet欄位。所以此時只能進行回表，即對這個商品的每一條評論都要進行一次回表。回到聚簇索引，根據id找到那條資料，取出is_good_comment欄位的值，接著對is_good_comment='1'條件進行匹配，篩選出符合條件的資料。如果這個商品的評論有幾十萬條，那麼就要做幾十萬次回表操作了。雖然每次回表都是根據id在聚簇索引裡快速查詢，但每條資料都回表。
步驟三：
假設篩選完所有符合條件的資料有十多萬條，那麼就要按id倒序排序。此時還得基於臨時磁碟檔案進行倒序排序，又要耗時很久。
步驟四：
排序完畢後，才能基於limit 100000,20獲取第5001頁的20條資料返回。
整個過程因為有幾十萬次回表查詢 + 有十多萬條資料的磁碟檔案排序。所以當時發現這條SQL語句基本要執行1秒~2秒。
(2)如何進行最佳化
第二個案例中會基於商品品類查商品表，儘量避免對聚簇索引進行掃描。因為有可能找不到指定的品類下的商品，出現聚簇索引全表掃描的問題。所以當時第二個案例裡，選擇強制使用一個聯合索引，快速定位到資料。接著根據id在臨時磁碟檔案排序後找到10條分頁資料，只需回表查10次。因此當時對第二個案例而言，因為不涉及到大量回表的問題。所以這麼做基本是合適的，效能通常在1s以內。
但是這個案例裡，就不是這麼回事了，這個案例的最佳化思路反而和前面的第二個案例反過來了。因為在WHERE product_id ='xx' and is_good_comment='1'這個條件中，product_id和is_good_comment不是一個聯合索引。所以這個案例中無論如何都會出現大量的回表操作，這個耗時是極高的。既然按二級索引還是按聚簇索引都要大量回表，還不如直接用聚簇索引。
由於第二個案例中如果指定使用聯合索引，則不會出現大量的回表操作，所以第二個案例最好還是指定使用聯合索引比較好。
由於這個案例中即便使用二級索引，也可能會出現大量的回表操作，所以還不如直接用聚簇索引，因此通常會採取如下方式改造分頁查詢語句：
SELECT * from comments a,
(SELECT id FROM comments WHERE product_id ='xx' and is_good_comment='1' ORDER BY id desc LIMIT 100000, 20) b
WHERE a.id=b.id上面那個SQL語句的執行計劃就會徹底改變MySQL的執行方式，即會先執行括號裡的子查詢，子查詢通常會使用PRIMARY聚簇索引。也就是會按照聚簇索引的id值的倒序方向進行掃描，選出符合WHERE product_id ='xx' and is_good_comment='1'的資料。
比如這裡篩選出十萬多條的資料，並不需要把符合條件的資料都找到。理論上只要有100000+20條符合條件的資料，而且按照id有序的。此時就可以根據limit 100000,20提取出第5001頁的這20條資料了，接著會看到執行計劃裡會針對這個子查詢的結果集進行全表掃描。這個子查詢的結果集就是一個只有20條資料的臨時表，拿到20條資料後，就會接著對20條資料遍歷，每一條資料都按照id去聚簇索引裡查詢完整的資料即可。
可見，出現臨時表並非都不好，如果臨時表的數量很少還是不影響的。這個案例就是透過少量資料的臨時表替換大量資料的回表來提升效能。所以針對這個場景，反而是最佳化成這種方式來執行分頁，會更加合理。分頁深度越深掃描資料越多，分頁深度越淺掃描資料就越少，然後再對篩選出的20條資料進行20次回表查詢即可。當做了這個最佳化後，執行時間降低到了幾百毫秒。
(3)案例總結
對於第二個案例來說：
按順序掃描聚簇索引可能會因找不到資料導致億級資料量的全表掃描，所以最好透過force index來強制指定根據聯合索引去查詢。
對於第三個案例來說：
因為前提是做了分庫分表，評論表單表資料一般在一百萬左右。首先即使一個商品沒有評論，全表掃描也不會像掃描上億資料表那麼慢。其次如果根據product_id的二級索引查詢，反而可能出現幾十萬次回表。所以按二級索引查詢反而不適合，而按聚簇索引掃描回表更少更加適合。
簡而言之，針對不同的場景，要具體情況具體分析。慢的原因在哪兒，為什麼慢，然後再用針對性的方式去最佳化。
4.避免產生長事務長時間執行
(1)業務背景引入
(2)出現SQL慢查詢的伺服器原因
(3)出現慢SQL的排查方法總結
(4)使用profilling工具對SQL語句進行分析
(5)SQL語句效能調優
(1)業務背景引入
當時有運維刪除了千萬級的資料，結果導致了頻繁的慢查詢。接下來介紹這個案例整個排查、定位以及解決的一個過程。
這個案例一開始是從線上收到大量的慢查詢告警，當收到大量慢查詢告警後，就去檢查慢查詢SQL，結果發現是普通SQL。這些SQL語句主要都是針對一個表的，同時也比較簡單，而且基本都是單行查詢，看起來不應該會慢查詢。
所以這時就感覺到特別奇怪的，因為SQL本身完全不應該有慢查詢。按道理那種SQL語句，基本上會直接根據索引查詢出來，效能是極高的。那麼可能慢查詢就不是SQL問題，而是MySQL生產伺服器的問題。
(2)出現SQL慢查詢的伺服器原因
事實上在某些特定的情況下：MySQL出現慢查詢並不是SQL語句的問題，而是它所在伺服器負載太高，從而導致SQL語句執行很慢。
特定情況一：磁碟IO負載特別高
比如現在MySQL伺服器的磁碟IO負載特別高，即每秒執行大量高負載的隨機IO，但磁碟每秒能執行的隨機IO是有限的。結果就導致正常的SQL語句去磁碟執行時，因為磁碟太繁忙而需要等待。從而導致本來很快的一個SQL，要等很久才能執行完畢，這時就可能導致正常SQL語句也會變成慢查詢。
特定情況二：網路負載很高
同理，除了磁碟外還有的因素是網路。如果網路負載很高，那就可能會導致一個SQL語句傳送到MySQL上，光是等待獲取和連線都很久，或MySQL網路負載太高頻寬打滿了。這樣即使一個SQL執行很快，但返回資料網路傳輸很慢，也是慢查詢。
特定情況三：CPU負載很高
另外一個關鍵的因素就是CPU負載。如果CPU負載過高，也會導致CPU過於繁忙在執行別的任務，而沒時間執行SQL語句，此時也有可能會導致SQL語句出現慢查詢。
所以出現慢查詢不一定就是SQL導致的，如果覺得SQL不應該慢查詢，結果某個時間段跑這個SQL就是慢，此時應該排查一下當時MySQL伺服器的負載。尤其看看磁碟、網路以及CPU的負載是否正常，如果發現那個時間段MySQL伺服器的磁碟、網路或CPU負載特別高，那麼可能就是伺服器負載導致的。
舉個例子，如果某個離線作業瞬間大批次把資料往MySQL裡寫入時，那麼這一瞬間伺服器磁碟、網路以及CPU的負載超高。此時一個正常SQL執行下去，短時間內一定會慢查詢的。針對類似問題，最佳化手段更多的是控制導致MySQL負載過高的那些行為。比如寫入大量資料時，最好在凌晨低峰期寫入，不要影響線上系統執行。
(3)出現慢SQL的排查方法總結
一.檢查SQL是否有問題，主要就看執行計劃
二.檢查MySQL伺服器的負載
三.都不行再用profilling工具去細緻的分析SQL語句的執行過程和耗時
(4)使用profilling工具對SQL語句進行分析
回到千萬級資料刪除導致的慢查詢的案例中，針對某個表的大量簡單的單行資料查詢SQL變成慢查詢問題，於是先排查了SQL執行計劃以及MySQL伺服器負載，發現都沒有問題。
此時就必須用上一個SQL調優的利器了，也就是profiling工具。這個工具可以對SQL語句的執行耗時進行非常深入和細緻的分析。使用這個工具的過程，大致如下所示：
步驟一：
首先要使用命令：set profiling=1，開啟profiling。接著MySQL就會自動記錄查詢語句的profiling資訊。
此時如果執行show profiles命令，會列出各種查詢語句的profiling資訊。這裡很關鍵的一點，就是它會記錄下來每個查詢語句的query id。所以要針對需要分析的query找對它的query id，假設針對慢查詢的那個SQL語句找到了query id。
步驟二：
然後就可以針對單個查詢語句，看一下它的profiling具體資訊。使用命令：show profile cpu, block io for query xx。這裡的xx是數字，此時就可以看到這個SQL語句執行時的profile資訊了。除了CPU以及Block IO以外，還可指定去看其他各項負載和耗時。
步驟三：
使用show profile展示出SQL語句執行時的各種耗時等profiling資訊，如磁碟IO耗時、CPU等待耗時、傳送資料耗時、複製資料臨時表耗時等。
當仔細檢查一下這個SQL語句的profiling資訊時，發現一個問題，就是它的Sending Data的耗時是最高的。幾乎使用了1s的時間，佔據了SQL執行耗時的99%，這就很嚴重了。畢竟這種簡單SQL執行速度真的很快，基本就是10ms級別的。結果跑成了1s，那肯定Sending Data就是罪魁禍首了。
這個Sending Data是在幹什麼呢？
MySQL的官方釋義是：為一個SELECT語句讀取和處理資料行，同時傳送資料給客戶端的過程。簡單來說就是為你的SELECT語句把資料讀出來，同時傳送給客戶端。
可是為什麼這個過程會這麼慢呢？
profiling確實能提供更多的線索了，但是似乎還是沒法解決掉問題。但畢竟已捕獲到了第一個比較異常的點，就是Sending Data的耗時很高。
步驟四：
接著使用命令：show engine innodb status，檢視innodb儲存引擎狀態。此時發現一個奇怪的指標，就是history list length這個指標。這個指標它的值特別高，達到了上萬這個級別。這個history list length與MVCC機制有關，MVCC與Read View機制有關，同時還與資料的undo多版本快照鏈有關。
當有大量事務執行時，就會構建這種undo多版本快照鏈條，此時history list length的值就會很高。然後在事務提交後，會有一個多版本快照鏈條的自動purge清理機制。只要有清理，那麼這個history list length值就會降低。
一般來說，這個值是不應該過高的。而展示innodb儲存引擎的狀態表示，history list length值過高，這表明大量的undo多版本鏈條資料沒被清理。所以推測可能有的事務長時間執行，導致其undo日誌不能被purge清理，從而導致history list length的值過高。
至此，大量簡單SQL語句變成慢查詢，基本可以肯定的兩點是：一.一些SQL因為Sending Data環節異常耗時過高。二.同時出現一些長事務長時間執行，導致大量undo日誌無法purge清理。
(5)SQL語句效能調優
此時發現有大量的更新語句在活躍，而且是那種長期活躍的超長事務。結果一問系統負責人，發現他在後臺跑了一個定時任務，定時清理資料，結果清理時一下子清理了上千萬資料。
這個清理是怎麼做的呢，就是居然開了一個事務。然後在一個事務裡刪除上千萬資料，導致這個事務一直在執行，所以才看到這個案例出現的一些奇怪現象。
然後這種長事務的執行會導致一個問題：就是刪除時只是對資料加了一個刪除標記，事實上並沒有徹底刪除掉。
此時如果有跟長事務同時執行的其他事務，它們在查詢時是可能會把那上千萬被標記為刪除的資料都掃一遍的。因為每次掃描到一批資料，都發現標記為刪除了。接著就會再繼續往下掃描，所以才導致一些查詢語句會那麼慢。
那麼為什麼啟動一個事務，在事務裡查詢，憑什麼要去掃描之前那個長事務標記為刪除狀態的垃圾資料呢？那些資料都被刪除了，跟當前事務沒關係了，應該可以不用掃描它們的。
這個問題的關鍵點就在於，那個刪除千萬級資料的事務是個長事務。當啟動新事務查詢時，那個刪除千萬級資料的長事務一直在執行活躍的。而啟動一個新事務查詢時，會生成一個Read View，這個Read View裡包含了當前活躍事務的最大id、最小id和事務id。
然後它有一個判定規則：新事務查詢時，會根據ReadView判斷哪些資料是可見的，以及可見版本。因為一個資料有一個版本鏈條，有時可見的只是該資料的一個歷史版本。
所以正是因為這個長事務一直在執行，還在刪除大量的資料。而且這些資料僅僅是標記為刪除，實際還沒刪除。所以此時新開啟的事務，查詢時會讀到所有被標記為刪除的資料。於是就會導致千萬級的資料掃描，從而造成慢查詢。
因此，永遠不要在業務高峰期去執行那種刪除大量資料的語句。所以解決方案很簡單，直接kill掉那個正在刪除千萬級資料的長事務。