資料庫——慢sql的原因

it_was發表於2020-09-23
資料庫SQL

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 協議》，轉載必須註明作者和本文連結

相關文章