資料庫——慢sql的原因

it_was 發表於 2020-09-23

sql語句執行速度慢可以分為兩種情況進行討論,

  1. 大多數情況正常,偶爾很慢
  2. 資料量不變,一直很慢

1.1 大多數情況正常,偶爾很慢

:one: 資料庫在重新整理髒頁(flush):worried:
當我們要往資料庫插入一條資料、或者要更新一條資料的時候,我們知道資料庫會在記憶體中把對應欄位的資料更新了,但是更新之後,這些更新的欄位並不會馬上同步持久化到磁碟中去,而是把這些更新的記錄寫入到 redo log 日記中去,等到空閒的時候,在通過 redo log 裡的日記把最新的資料同步到磁碟中去。

當記憶體資料頁跟磁碟資料頁內容不一致的時候,我們稱這個記憶體頁為“髒頁”。
記憶體資料寫入到磁碟後,記憶體和磁碟上的資料頁的內容就一致了,稱為“乾淨頁”。

:raising_hand:刷髒頁有下面4種場景:

  • :boom: redolog寫滿了:redo log 裡的容量是有限的,如果資料庫一直很忙,更新又很頻繁,這個時候 redo log 很快就會被寫滿了,這個時候就沒辦法等到空閒的時候再把資料同步到磁碟的,只能暫停其他操作,全身心來把資料同步到磁碟中去的,而這個時候,就會導致我們平時正常的SQL語句突然執行的很慢,所以說,資料庫在在同步資料到磁碟的時候,就有可能導致我們的SQL語句執行的很慢了。

  • :boom: 記憶體不夠用了:如果一次查詢較多的資料,恰好碰到所查資料頁不在記憶體中時,需要申請記憶體,而此時恰好記憶體不足的時候就需要淘汰一部分記憶體資料頁,如果是乾淨頁,就直接釋放,如果恰好是髒頁就需要刷髒頁。

  • MySQL 認為系統“空閒”的時候:這時系統沒什麼壓力。

  • MySQL 正常關閉的時候:這時候,MySQL 會把記憶體的髒頁都 flush 到磁碟上,這樣下次 MySQL 啟動的時候,就可以直接從磁碟上讀資料,啟動速度會很快。

:two:執行sql語句遇到鎖!:worried:
:boom:比如併發事務下,執行的sql語句涉及到了表鎖和行鎖,這種情況就只能等待了,導致sql語句很慢

1.2 資料量不變,一直很慢

如果每次都執行的很慢,應該需要考慮sql語句本身的問題了
假設有以下建表語句:

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

:one: 沒用上索引:worried:

  • 欄位沒有索引
    比如你沒有在目標欄位上加上索引,就只能走全表掃描了,例如下面這行語句,你並沒有給欄位c加上索引
    select * from t where 100 <c and c < 100000;
  • 欄位有索引,但卻沒有用索引
    下面這行sql語句,雖然你在c上加了索引,但卻因為運算操作而沒有走索引:
    select * from t where c - 1 < 100000;
    同理這種函式操作也沒有用到索引的!
    select * from t where pow(c,10) < 100000;
    還有下面這種聯合索引,根據最左匹配原則也沒有名中,故只能走全表掃描 :boom:
    ALERT TABLE 't' ADD INDEX 'union_index'('a','b','c');
    select * from t where b < 100 and c = 10;

:two: 資料庫選錯了索引:worried:

本作品採用《CC 協議》,轉載必須註明作者和本文連結