通過 EXPLAIN 淺析資料庫查詢優化方法

知識點

explain 或者 desc 命令

獲取Myswl如何執行SELECT語句的信息，包括在SELECT語句執行過程中表如何連接和連接的順序。

index：

這種類型表示是mysql會對整個該索引進行掃描。

要想用到這種類型的索引，對這個索引並無特別要求，只要是索引，或者某個複合索引的一部分，mysql都可能會採用index類型的方式掃描。

缺點是效率不高，mysql會從索引中的第一個數據一個個的查找到最後一個數據，直到找到符合判斷條件的某個索引。

ref：

這種類型表示mysql會根據特定的演算法快速查找到某個符合條件的索引，而不是會對索引中每一個數據都進行一一的掃描判斷，也就是平常理解的使用索引查詢會更快的取出數據。而要想實現這種查找，索引卻是有要求的，要實現這種能快速查找的演算法，索引就要滿足特定的數據結構。

索引欄位的數據必須有序才能實現這種類型的查找，才能利用到索引。

Using index

列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對錶的全部的請求列都是同一個索引的部分的時候。

Using where

使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。

在一般稍大的系統中，基本儘可能的減少join ，子查詢等等。mysql就使用最簡單的查詢，這樣效率最高。至於 join 等，可以放在應用層去解決。

Using temporary

優化。MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上

Using filesort

優化。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行

執行計劃中什麼情況下會出現using filesort和using tempatory？

通常當查詢中出現連接（JOIN）且連接中的所有列都來自連接的第一個表時，就會出現using filesort。

除此之外的所有情況，mysql都會先將連接結果放到臨時表中，然後在所有的連接都結束後，再進行文件排序（實際上是快速排序），不管這種排序是在內存中還是在硬碟中進行的。

即使查詢中出現limit 子句，也會在排序結束後才做分頁處理，所以實際上臨時表和需要排序的數據量依舊會非常大。

當執行計劃中出現使用臨時表的情況時，首先要檢查一下是不是ORDER BY 和GROUP BY 語句中欄位的問題。

如果調整過欄位還是出現使用臨時表的情況，考慮聯合索引來覆蓋這些欄位，並儘可能的使用小結果集驅動大結果集。

explain 示例代碼

更改排序欄位後：

mysql > explain select A.id , A.title , B.title from base_content A left join base_categories B on A.catid = B.id left join base_sections C on A.sectionid = C.id order by A.id ; +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+ | 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 46585 | Using filesort | | 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | | | 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+

第一個使用了Using temporary，而第二個沒有用呢？

因為如果有ORDER BY子句和一個不同的GROUP BY子句，或者如果ORDER BY或GROUP BY中的欄位都來自其他的表而非連接順序中的第一個表的話，就會創建一個臨時表了。

那麼，對於上面例子中的第一條語句，我們需要對base_categories的id進行排序，可以將SQL做如下改動：

mysql > explain select B.id , B.title , A.title from base_categories A left join base_content B on A.id = B.catid left join base_sections C on B.sectionid = C.id order by A.id ; +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+ | 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 18 | Using filesort | | 1 | SIMPLE | B | ref | idx_catid | idx_catid | 4 | joomla_test . A . id | 3328 | | | 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . B . sectionid | 1 | Using index | +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+

這樣我們發現，不會再有Using temporary了，而且在查詢base_content時，查詢的記錄明顯有了數量級的降低，這是因為base_content的idx_catid起了作用。

結論：

為提高查詢效率，應盡量對第一個表的索引鍵進行排序

擴展：

Using filesort 字面意思：使用文件進行排序或中文件中進行排序。這是不正確的，當我們試圖對一個沒有索引的欄位進行排序時，就是filesoft。它跟文件沒有任何關係，實際上是內部的一個快速排序。

我們再看：

我們會發現Using filesort沒有了,而這條語句中C表的主鍵對排序起了作用。

而儘管在上面的語句中也是對第一個表的主鍵進行排序，卻沒有得到想要的效果（第一個表的主鍵沒有用到），這是為什麼呢？

實際上以上運行過的所有left join的語句中，第一個表的索引都沒有用到，儘管對第一個表的主鍵進行了排序也無濟於事。不免有些奇怪！

測試：

於是我們繼續測試了下一條SQL：

再次進行排序操作的時候，Using filesoft也沒有再出現

結論：

對where條件裏涉及到的欄位，Mysql會使用索引進行搜索，而這個索引的使用也對排序的效率有很好的提升

測試

分別讓以下兩個SQL語句執行200次：

select A.id , A.title , B.title from base_content A left join base_categories B on A.catid = B.id left join base_sections C on A.sectionid = C.id

select A.id, A.title, B.title
from base_content A , base_categories B, base_sections C
where A.catid = B.id and A.sectionid = C.id

select A.id , A.title , B.title
from base_content A
left join base_categories B on A.catid = B.id
left join base_sections C on A.sectionid = C.id 　
order by rand ()
limit 10

select A.id from base_content A
left join base_categories B on B.id = A.catid
left join base_sections C on A.sectionid = C.id
order by A.id

結果是第(1)條平均用時27s ，第(2)條平均用時54s ，第(3)條平均用時80s ，第(4)條平均用時3s 。

用explain觀察第(3)條語句的執行情況，會發現它創建了temporary臨時表來進行排序。

知識點：

對需要查詢和排序的欄位要加索引。
盡量少地連接表。left join 比普通連接查詢效率要高，注意觀察索引是否起了作用。
排序盡量對第一個表的索引欄位進行，可以避免mysql創建臨時表，這是非常耗資源的。
對where條件裏涉及到的欄位，應適當地添加索引，這樣會對排序操作有優化的作用。
如果說在分頁時我們能先得到主鍵，再根據主鍵查詢相關內容，也能得到查詢的優化效果。
避免使用order by rand()。在執行過程中用show processlist查看，會發現第(3)條有Copying to tmp table on disk。
Slow queries 檢查一下是哪些語句降低的Mysql 的執行效率，並進行定期優化。

優化GROUP BY語句

如果查詢包括GROUP BY 但想要避免排序結果的消耗，則可以指定ORDER By NULL禁止排序

例如：

explain select id, sum(moneys) from sales2 group by id G explain select id, sum(moneys) from sales2 group by id order by null G

比較發現第一條語句會比第二句在Extra:裡面多了Using filesort.而恰恰filesort是最耗時的。

優化ORDER BY語句

在某些情況中，MySQL可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序。WHERE 條件和 ORDER BY使用相同的索引，並且ORDER BY的順序和索引順序相同，並且ORDER BY的欄位都是升序或者都是降序。

例如：

SELECT * FROM t1 ORDER BY key_part1,key_part2,....: SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC,key_part2 DESC; SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是以下的情況不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; --ORDER by的欄位混合ASC 和 DESC

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
--用於查詢行的關鍵字與ORDER BY 中所使用的不相同

SELECT * FROM t1 ORDER BY key1, key2;
--對不同的關鍵字使用ORDER BY

優化LIMIT分頁

當需要分頁操作時，通常會使用LIMIT加上偏移量的辦法實現，同時加上合適的ORDER BY字句。

如果有對應的索引，通常效率會不錯，否則，MySQL需要做大量的文件排序操作。

當偏移量非常大的時候，比如：LIMIT 20000 20這樣的查詢，MySQL需要查詢120020條記錄然後只返回20條記錄，前面的20000條都將被拋棄，這樣的代價非常高。

優化這種查詢一個最簡單的辦法就是儘可能的使用覆蓋索引掃描，而不是查詢所有的列。然後根據需要做一次關聯查詢再返回所有的列。

測試：

select film_id, description from base_film order by title limit 50,5;

如果這張表非常大，那麼這個查詢最好改成下面的樣子：

select film.film_id, film.description from base_film INNER JOIN( select film_id from base_film order by title limit 50,5 ) as tmp USING(film_id);

這裡的延遲關聯將大大提升查詢效率，讓MySQL掃描儘可能少的頁面，獲取需要訪問的記錄後在根據關聯列回原表查詢所需要的列。

有時候如果可以使用書籤記錄上次取數據的位置，那麼下次就可以直接從該書籤記錄的位置開始掃描，這樣就可以避免使用OFFSET，比如下面的查詢：

select id from t limit 10000, 10; select id from t where id > 10000 limit 10;

其他優化的辦法還包括使用預先計算的匯總表，或者關聯到一個冗餘表，冗餘表中只包含主鍵列和需要做排序的列。

優化UNION

MySQL處理UNION的策略是先創建臨時表，然後再把各個查詢結果插入到臨時表中，最後再來做查詢。因此很多優化策略在UNION查詢中都沒有辦法很好的時候。經常需要手動將WHERE、LIMIT、ORDER BY等字句「下推」到各個子查詢中，以便優化器可以充分利用這些條件先優化。

除非確實需要伺服器去重，否則就一定要使用UNION ALL，如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這會導致整個臨時表的數據做唯一性檢查，這樣做的代價非常高。當然即使使用ALL關鍵字，MySQL總是將結果放入臨時表，然後再讀出，再返回給客戶端。雖然很多時候沒有這個必要，比如有時候可以直接把每個子查詢的結果返回給客戶端。

特定類型查詢優化

優化COUNT()查詢

COUNT()有兩種不同的作用，其一是統計某個列值的數量，其二是統計行數。

統計列值時，要求列值是非空的，它不會統計NULL。如果確認括弧中的表達式不可能為空時，實際上就是在統計行數。

最簡單的就是當使用COUNT(*)時，並不是我們所想像的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計行數。

我們最常見的誤解也就在這兒，在括弧內指定了一列卻希望統計結果是行數，而且還常常誤以為前者的性能會更好。

但實際並非這樣，如果要統計行數，直接使用COUNT(*)，意義清晰，且性能更好。

有時候某些業務場景並不需要完全精確的COUNT值，可以用近似值來代替，EXPLAIN出來的行數就是一個不錯的近似值，而且執行EXPLAIN並不需要真正地去執行查詢，所以成本非常低。通常來說，執行COUNT()都需要掃描大量的行才能獲取到精確的數據，因此很難優化，MySQL層面還能做得也就只有覆蓋索引了。如果不還能解決問題，只有從架構層面解決了，比如添加匯總表，或者使用redis這樣的外部緩存系統。

優化關聯查詢

在大數據場景下，表與表之間通過一個冗餘欄位來關聯，要比直接使用JOIN有更好的性能。如果確實需要使用關聯查詢的情況下，需要特別注意的是：

確保ON和USING字句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表A和表B用列c關聯的時候，如果優化器關聯的順序是A、B，那麼就不需要在A表的對應列上創建索引。沒有用到的索引會帶來額外的負擔，一般來說，除非有其他理由，只需要在關聯順序中的第二張表的相應列上創建索引（具體原因下文分析）。

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

要理解優化關聯查詢的第一個技巧，就需要理解MySQL是如何執行關聯查詢的。當前MySQL關聯執行的策略非常簡單，它對任何的關聯都執行嵌套循環關聯操作，即先在一個表中循環取出單條數據，然後在嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為為止。然後根據各個表匹配的行，返回查詢中需要的各個列。

太抽象了？以上面的示例來說明，比如有這樣的一個查詢：

select A.xName, b.yName from A INNER JOIN B USING(c) WHERE A.xName IN (5,6)

假設MySQL按照查詢中的關聯順序A、B來進行關聯操作，那麼可以用下面的偽代碼表示MySQL如何完成這個查詢：

outer_iterator = select A.xName, A.c from A where A.xName IN(5,6); outer_row = outer_iterator.next; while(outer_row){ inner_iterator = SELECT B.yName from B WHERE B.c = outer_row.c; inner_row = inner_iterator.next; while(outer_row) { outpur[inner_row.yName, outer_row.xx]; inner_row = inner_iterator.next; } outer_row = outer_iterator.next; }

可以看到，最外層的查詢是根據A.xName，A.c上如果有索引的話，整個關聯查詢也不會使用。再看內層的查詢，很明顯B.c上如果有索引的話，能夠加速查詢，因此只需要在關聯順序中的第二張表的相應列上創建索引即可。

1、MySQL不會使用索引的情況：非獨立的列

「獨立的列」是指索引列不能是表達式的一部分，也不能是函數的參數。比如：

select * from t where id + 1 = 15

其等價於 id = 14，但是MySQL無法自動解析這個表達式，使用函數是同樣的道理。

2、前綴索引

如果列很長，通常可以索引開始的部分字元，這樣可以有效節約索引空間，從而提高索引效率。

3、多列索引和索引順序

在多數情況下，在多個列上建立獨立的索引並不能提高查詢性能。

因為MySQL不知道選擇哪個索引的查詢效率更好，所以在老版本，比如MySQL5.0之前就會隨便選擇一個列的索引，而新的版本會採用合併索引的策略。

示例：

在一張電影演員表中，在actor_id和film_id兩個列上都建立了獨立的索引，然後有如下查詢：

select film_id, actor_id from film_actor where actor_id = 1 or film_id = 1

老版本的MySQL會隨機選擇一個索引，但新版本做如下的優化：

select film_id, actor_id from film_actor where actor_id = 1 union all select film_id, actor_id from film_actor where film_id and actor_id <> 1

當出現多個索引做相交操作時（多個AND條件），通常來說一個包含所有相關列的索引要優於多個獨立索引。

當出現多個索引做聯合操作時（多個OR條件），對結果集的合併、排序等操作需要耗費大量的CPU和內存資源，特別是當其中的某些索引的選擇性不高，需要返回合併大量數據時，查詢成本更高。所以這種情況下還不如走全表掃描。

結論：

如果發現有索引合併（Extra欄位出現Using union），檢查查詢和表結構，檢查索引（或許一個包含所有相關列的多列索引更適合）。

多列索引時索引的順序對於查詢是至關重要的，應該把選擇性更高的欄位放到索引的前面，這樣通過第一個欄位就可以過濾掉大多數不符合條件的數據。

索引選擇性

索引選擇性是指不重複的索引值和數據表的總記錄數的比值，選擇性越高查詢效率越高，因為選擇性越高的索引可以讓MySQL在查詢時過濾掉更多的行。

唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

示例：

select * from base_payment where staff_id = 2 and customer_id = 785

是應該創建(staff_id,customer_id)的索引還是應該顛倒一下順序？

elct count(distinct staff_id)/count(*) as staff_id_selectivity, count(distinct customer_id)/count(*) as customer_id_selectivity, count(*) from base_payment

哪個欄位的選擇性更接近1就把哪個欄位索引前面，多數情況下使用這個原則沒有任何問題，但仍然注意你的數據中是否存在一些特殊情況。

示例：

比如要查詢某個用戶組下有過交易的用戶信息：

select user_id from base_trade where user_group_id = 1 and trade_amount > 0

MySQL為這個查詢選擇了索引(user_group_id,trade_amount)，看起來沒有任何問題。

但實際情況是這張表的大多數數據都是從老系統中遷移過來的，由於新老系統的數據不兼容，所以就給老系統遷移過來的數據賦予了一個默認的用戶組。

這種情況下，通過索引掃描的行數跟全表掃描基本沒什麼區別，索引也就起不到任何作用。

經驗法則可以指導我們開發和設計，但實際業務場景下的某些特殊情況可能會摧毀你的整個設計。

4、避免多個範圍條件

實際開發中，我們會經常使用多個範圍條件，比如想查詢某個時間段內登錄過的用戶：

select USER.* from base_user USER where login_time > 2019-01-01 and age between 18 and 30

這個查詢有一個問題：它有兩個範圍條件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但無法同時使用它們。

5、覆蓋索引

如果一個索引包含或者說覆蓋所有需要查詢的欄位的值，那麼就沒有必要再回表查詢，這就稱為覆蓋索引。

覆蓋索引可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：

索引條目遠小於數據行大小，如果只讀取索引，極大減少數據訪問量
索引是有按照列值順序存儲的，對於I/O密集型的範圍查詢要比隨機從磁碟讀取每一行數據的IO要少的多

6、使用索引掃描來排序

MySQL有兩種方式可以生產有序的結果集

其一是對結果集進行排序的操作，
其二是按照索引順序掃描得出的結果自然是有序的。

如果type列的值為index表示使用了索引掃描來做排序。掃描索引本身很快，因為只需要從一條索引記錄移動到相鄰的下一條記錄。

但如果索引本身不能覆蓋所有需要查詢的列，那麼就不得不每掃描一條索引記錄就回表查詢一次對應的行。

這個讀取操作基本上是隨機I/O，因此按照索引順序讀取數據的速度通常要比順序地全表掃描要慢。

知識點：

索引最好既能夠滿足排序，又滿足查詢。只有當索引的列順序和ORDER BY子句的順序完全一致，並且所有列的排序方向也一樣時，纔能夠使用索引來對結果做排序。
如果查詢需要關聯多張表，則只有ORDER BY子句引用的欄位全部為第一張表時，才能使用索引做排序。
ORDER BY子句和查詢的限制是一樣的，都要滿足最左前綴的要求，其他情況下都需要執行排序操作，而無法利用索引排序。

示例：

有一種情況例外，就是最左的列被指定為常數

// 最左列為常數，索引：(date,staff_id,customer_id)

select staff_id,customer_id from base_staff where date = 2015-06-01 order by staff_id,customer_id

7、冗餘和重複索引

立即刪除。冗餘索引是指在相同的列上按照相同的順序創建的相同類型的索引。比如有一個索引(A,B)，再創建索引(A)就是冗餘索引。

大多數情況下都應該盡量擴展已有的索引而不是創建新索引，冗餘索引經常發生在為表添加新索引時，比如有人新建了索引(A,B)，但這個索引不是擴展已有的索引(A)。

但有極少情況下出現性能方面的考慮需要冗餘索引，比如擴展已有索引而導致其變得過大，從而影響到其他使用該索引的查詢。

8、定期檢查，可刪除長期未使用的索引

只有當索引幫助提高查詢速度帶來的好處大於其帶來的額外工作時，索引纔是有效的。所以 explain 後再提測是一種美德。

talk is easy, show me the code.

作者：一隻阿木木

鏈接：http://www.imooc.com/article/286452

來源：慕課網

本文首次發佈於慕課網，轉載請註明出處，謝謝合作

通過 EXPLAIN 淺析資料庫查詢優化方法

知識點

explain 示例代碼

知識點：

索引選擇性

熱門新聞

週熱門

通過 EXPLAIN 淺析資料庫查詢優化方法

知識點

explain 示例代碼

知識點：

索引選擇性

如何系統學習hadoop等大數據行業知識？

如何看待螞蟻金服OceanBase拿下世界第一，性能超老牌資料庫Oracle 100％?

mysql 導入 很大的 CSV ?

前端Flutter+Dart，後端Golang，資料庫Mysql的技術棧是否會成為未來開發的標配？

mysql百萬級表在不停機的情況下增加一個欄位要怎麼處理的？

大數據用什麼資料庫?

怎麼實現一個簡單的資料庫系統？

web安全需不需要學習sql資料庫？

關於android跟資料庫的一些問題和我的理解?

sql資料庫中什麼情況該加索引index？

在oracle中，select * from dual where =為什麼返回的是空？

學習SQL怎麼開頭？

軟體工程的學生，資料庫應該學哪種?MySQL，Oracle，還是SqlServer？

條件：mysql中多個表的欄位完全相同 需求：在增/刪/改欄位時，同時修改這多個表？請問相關工具和辦法

sql如何獲取 使用distinct後的record所對應的原表id？

熱門新聞

週熱門

mysql 導入很大的 CSV ?

條件：mysql中多個表的欄位完全相同需求：在增/刪/改欄位時，同時修改這多個表？請問相關工具和辦法

sql如何獲取使用distinct後的record所對應的原表id？