導致sql執行速度慢的幾種情況盤點(生產環境踩過的坑)

2023-03-11 06:02:39

前言

當我們遇到慢sql，第一反應可能就是去優化我們的sql語句。一些比較複雜的語句如果執行慢可能還能理解，但是有時一些特別簡單的查詢也會變得卡頓，“查一行”，也會執行得特別慢。今天，我們盤點一下，都有哪些情況會導致sql執行速度慢。

1，資料庫本身壓力較大

如果資料庫本身的效能壓力就比較大，資源比較緊張，CPU佔用率或者IO利用率很高，這時會導致所有的語句執行起來都比較慢。這種情況下首先要做的應該是提升伺服器的設定，然後觀察伺服器的效能指標是否平穩。

2，表鎖衝突

如果遇到一個簡單的查詢長時間未返回結果，那麼大概率是表被鎖住了。一般遇到這種情況，都是通過show processlist命令，檢視sql語句的狀態。

如圖所示，id為8的語句正在等待一個MDL鎖，我們可以使用kill命令殺掉這個阻塞執行緒。

另外還可以通過表sys.schema_table_lock_waits查詢阻塞的執行緒id。

3，行鎖衝突

mysql> select * from temp where id =3 for update;

當我們存取id=3這條記錄時，使用了for update，表示要對這條語句加鎖。但是如果此時已經有另一個事務對這條記錄加了鎖，並且一直持有不釋放鎖，那麼當前語句就會一直阻塞。

通過上圖可以看出，第一個語句不提交事務，第二個語句就一直處於等待阻塞狀態。

我們執行show processlist,

可以看出確實有一個執行緒處於阻塞狀態。

鎖衝突會導致執行效率降低，進而影響到業務，需要我們重點關注。

4，索引未命中

mysql> select * from t where c=50000 limit 1;

如果欄位c上面沒有索引，那麼就只能走主鍵id順序掃描，一直掃描到第50000行才能停下來。

給表資料加索引可以快速提升查詢效能，一般來說，因為索引失效導致的慢sql可能是我們平常開發過程中經常遇到的。

5，多表join查詢

有了索引，並不代表萬事大吉。如果一個比較複雜的sql，需要關聯很多表進行查詢，即使每張表的索引都可以起到作用，但是由於資料量過多，即使都命中索引，掃描的行數仍然是巨大的。這樣sql的執行速度仍然會受到很大影響。

一般來說，超過3個表的join就應該儘量避免，將其拆分為多個查詢，使用空間換時間也不失為一個好辦法。

補充知識：慢 SQL 語句的幾種常見誘因

1. 無索引、索引失效導致

慢查詢如果在一張幾千萬資料的表中以一個沒有索引的列作為查詢條件，大部分情況下查詢會非常耗時，這種查詢毫無疑問是一個慢 SQL 查詢。

所以對於巨量資料量的查詢，需要建立適合的索引來優化查詢。雖然我們很多時候建立了索引，但在一些特定的場景下，索引還有可能會失效，所以索引失效也是導致慢查詢的主要原因之一。

2. 鎖等待

常用的儲存引擎有 InnoDB 和 MyISAM，前者支援行鎖和表鎖，後者只支援表鎖。

如果資料庫操作是基於表鎖實現的，試想下，如果一張資料表在更新時，需要鎖住整張表，那麼其它大量資料庫操作（包括查詢）都將處於等待狀態，這將嚴重影響到系統的並行效能。這時，InnoDB 儲存引擎支援的行鎖更適合高並行場景。

行鎖升級為表鎖的可能：

在批次更新操作時，行鎖就很可能會升級為表鎖。

MySQL 認為如果對一張表使用大量行鎖，會導致事務執行效率下降，從而可能造成其它事務長時間鎖等待和更多的鎖衝突問題發生，致使效能嚴重下降，所以 MySQL 會將行鎖升級為表鎖。還有，行鎖是基於索引加的鎖，如果我們在更新操作時，條件索引失效，那麼行鎖也會升級為表鎖。

因此，基於表鎖的資料庫操作，會導致 SQL 阻塞等待，影響執行速度。

行鎖相對錶鎖來說，雖然粒度更細，並行能力提升了，但也帶來了死鎖的問題。因此，在使用行鎖時，我們要注意避免死鎖。

3. 不恰當的 SQL 語句

使用不恰當的 SQL 語句也是慢 SQL 最常見的誘因之一 :

在巨量資料表中使用分頁查詢，以及對非索引欄位進行排序等等。

總結

總的來說，sql執行速度慢會受到很多種情況的影響，比如表鎖，行鎖。在實際的業務場景中也許會更復雜，但是處理起來的步驟基本大同小異，只要找到問題的根源，知道如何解決，處理起來就可以做到心中有數，遊刃有餘。

到此這篇關於導致sql執行速度慢的幾種情況盤點的文章就介紹到這了,更多相關sql執行速度慢的情況內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！