歡迎光臨
每天分享高質量文章

MySQL史上最全性能優化方式

MySQL有哪些性能優化方式?這個問題可以涉及到 MySQL 的很多核心知識,就像要考你計算機網絡的知識時,問你“輸入URL回車之後,究竟發生了什麼”一樣,看看你能說出多少了。

 

所以今天,給我帶大家詳細介紹一下有哪些原因,相信看完之後一定會有所收穫。

 

一、設置索引


 

索引是一種可以讓SELECT陳述句提高效率的資料結構,可以起到快速定位的作用。

 

索引的優缺點:

優點:某些情況下使用select陳述句大幅度提高效率,合適的索引可以優化MySQL服務器的查詢性能,從而起到優化MySQL的作用。

 

缺點:表行資料的變化(index、update、delect),簡歷在表列上的索引也會自動維護,一定程度上會使DML操作變慢。索引還會占用磁盤額外的儲存空間。

 

給表列創建索引

 

建表時創建索引:

create table t(id int,name varchar(20),index idx_name (name));

給表追加索引:

alter table t add unique index idx_id(id);

給表的多列上追加索引

alter table t add index idx_id_name(id,name);或者create index idx_id_name on t(id,name);

 

查看索引

 

使用show陳述句查看t表上的索引:

show index from t;

 

show keys from t;–mysql中索引也被稱作keys

 

 

使用show create table陳述句查看索引:

show create table t\G

 

 

刪除索引:

 

  • 使用alter table命令刪除索引:
  • alter table 表 drop index 索引名
  • 使用drop index命令刪除索引:
  • drop index 索引名 on 表

 

索引原理:

例如一個學生信息表,我們設置學號(stu_id)為索引:

 

索引頁之間存在一定的關聯關係,一般為樹形結構;分為根節點、分支節點、和葉子節點

根節點頁中存放分段stu_id的起始值,以及值所對應的分支索引頁號

分支索引頁中存放分段stu_id的起始值,以及值所對應的葉子索引頁號

葉子索引頁中存放排序後的stu_id值,該值所對應的表頁號, 下一個葉子索引頁的頁號

 

 

stu_id建立索引後,執行select name,sex,height from stu where stu_id=13查詢過程如下:

 

  1. 索引頁存在關聯關係,先找索引頁號20的根節點,13在>=11和<17的範圍內,需要查找25號索引頁
  2. 讀取25號索引頁,13在>=11和<14範圍內,得到了26號葉子索引頁
  3. 讀取26號葉子索引頁,找到了13這個值,以及該值所對應表頁的頁號161,目前只得到了stu_id的值,還要得到name,sex,height等,因此需要再讀一次編號為161的表頁,裡面存放了stu_id之外的值。
  4. 讀取161號表頁,獲得sname,sex,height等值

     

 

以上4步,只讀取了3個索引頁1個表頁,共4個頁,比讀取所有表頁(5000個頁),按照stu_id=13挨個翻一遍效率要高,這也是有些情況下索引可以加速查詢的原因。

 

二、分類討論


 

一條 SQL 陳述句執行的很慢,那是每次執行都很慢呢?還是大多數情況下是正常的,偶爾出現很慢呢?所以我覺得,我們還得分以下兩種情況來討論。

 

1. 大多數情況是正常的,只是偶爾會出現很慢的情況。

 

2. 在資料量不變的情況下,這條SQL陳述句一直以來都執行的很慢。

 

針對這兩種情況,我們來分析下可能是哪些原因導致的。

 

三、針對偶爾很慢的情況


 

一條 SQL 大多數情況正常,偶爾才能出現很慢的情況,針對這種情況,我覺得這條SQL陳述句的書寫本身是沒什麼問題的,而是其他原因導致的,那會是什麼原因呢?

 

1. 資料庫在掃清臟頁(flush)

 

當我們要往資料庫插入一條資料、或者要更新一條資料的時候,我們知道資料庫會在記憶體中把對應欄位的資料更新了,但是更新之後,這些更新的欄位並不會馬上同步持久化到磁盤中去,而是把這些更新的記錄寫入到 redo log 日記中去,等到空閑的時候,在通過 redo log 里的日記把最新的資料同步到磁盤中去。

 

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

 

刷臟頁有下麵4種場景

 

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

 

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

 

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

 

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

 

2. 拿不到鎖我能怎麼辦

 

這個就比較容易想到了,我們要執行的這條陳述句,剛好這條陳述句涉及到的表,別人在用,並且加鎖了,我們拿不到鎖,只能慢慢等待別人釋放鎖了。或者,表沒有加鎖,但要使用到的某個一行被加鎖了。

 

如果要判斷是否真的在等待鎖,我們可以用 show processlist這個命令來查看當前的狀態哦,這裡我要提醒一下,有些命令最好記錄一下。

 

下來我們來訪分析下第二種情況。

 

四、針對一直都這麼慢的情況


 

如果在資料量一樣大的情況下,這條 SQL 陳述句每次都執行的這麼慢,那就就要好好考慮下你的 SQL 書寫了,下麵我們來分析下哪些原因會導致我們的 SQL 陳述句執行的很不理想。

 

我們先來假設我們有一個表,表裡有下麵兩個欄位,分別是主鍵 id,和兩個普通欄位 c 和 d。

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

 

1. 沒用到索引

 

沒有用上索引,我覺得這個原因是很多人都能想到的,例如你要查詢這條陳述句

select * from t where 100 

 

a. 欄位沒有索引

 

剛好你的 c 欄位上沒有索引,那麼抱歉,只能走全表掃描了,你就體驗不會索引帶來的樂趣了,所以,這回導致這條查詢陳述句很慢。

 

b. 欄位有索引,但卻沒有用索引

 

好吧,這個時候你給 c 這個欄位加上了索引,然後又查詢了一條陳述句

select * from t where c - 1 = 1000;

 

我想問大家一個問題,這樣子在查詢的時候會用索引查詢嗎?

 

不會,如果我們在欄位的左邊做了運算,那麼很抱歉,在查詢的時候,就不會用上索引了,所以呢,大家要註意這種欄位上有索引,但由於自己的疏忽,導致系統沒有使用索引的情況了。

 

正確的查詢應該如下

select * from t where c = 1000 + 1;

 

有人可能會說,右邊有運算就能用上索引?難道資料庫就不會自動幫我們優化一下,自動把 c – 1=1000 自動轉換為 c = 1000+1。

 

c. 函式操作導致沒有用上索引

 

如果我們在查詢的時候,對欄位進行了函式操作,也是會導致沒有用上索引的,例如

select * from t where pow(c,2) = 1000;

 

這裡我只是做一個例子,假設函式 pow 是求 c 的 n 次方,實際上可能並沒有 pow(c,2)這個函式。其實這個和上面在左邊做運算也是很類似的。

 

所以呢,一條陳述句執行都很慢的時候,可能是該陳述句沒有用上索引了,不過具體是啥原因導致沒有用上索引的呢,你就要會分析了,我上面列舉的三個原因,應該是出現的比較多的吧。

 

2. 資料庫自己選錯索引了

 

我們在進行查詢操作的時候,例如

select * from t where 100 < c and c < 100000;

 

我們知道,主鍵索引和非主鍵索引是有區別的,主鍵索引存放的值是整行欄位的資料,而非主鍵索引上存放的值不是整行欄位的資料,而且存放主鍵欄位的值。 裡面有說到主鍵索引和非主鍵索引的區別。

 

不大懂的可以看這思維導圖

 

也就是說,我們如果走 c 這個欄位的索引的話,最後會查詢到對應主鍵的值,然後,再根據主鍵的值走主鍵索引,查詢到整行資料傳回。

 

好吧扯了這麼多,其實我就是想告訴你,就算你在 c 欄位上有索引,系統也並不一定會走 c 這個欄位上的索引,而是有可能會直接掃描掃描全表,找出所有符合 100 < c and c < 100000 的資料。

 

為什麼會這樣呢?

 

其實是這樣的,系統在執行這條陳述句的時候,會進行預測:究竟是走 c 索引掃描的行數少,還是直接掃描全表掃描的行數少呢?顯然,掃描行數越少當然越好了,因為掃描行數越少,意味著I/O操作的次數越少。

 

如果是掃描全表的話,那麼掃描的次數就是這個表的總行數了,假設為 n;而如果走索引 c 的話,我們通過索引 c 找到主鍵之後,還得再通過主鍵索引來找我們整行的資料,也就是說,需要走兩次索引。而且,我們也不知道符合 100 c < and c < 10000 這個條件的資料有多少行,萬一這個表是全部資料都符合呢?這個時候意味著,走 c 索引不僅掃描的行數是 n,同時還得每行資料走兩次索引。

 

所以呢,系統是有可能走全表掃描而不走索引的。那系統是怎麼判斷呢?

 

判斷來源於系統的預測,也就是說,如果要走 c 欄位索引的話,系統會預測走 c 欄位索引大概需要掃描多少行。如果預測到要掃描的行數很多,它可能就不走索引而直接掃描全表了。

 

那麼問題來了,系統是怎麼預測判斷的呢?這裡我給你講下系統是怎麼判斷的吧,雖然這個時候我已經寫到脖子有點酸了。

 

系統是通過索引的區分度來判斷的,一個索引上不同的值越多,意味著出現相同數值的索引越少,意味著索引的區分度越高。我們也把區分度稱之為基數,即區分度越高,基數越大。所以呢,基數越大,意味著符合 100 < c and c < 10000 這個條件的行數越少。

 

所以呢,一個索引的基數越大,意味著走索引查詢越有優勢。

 

那麼問題來了,怎麼知道這個索引的基數呢?

 

系統當然是不會遍歷全部來獲得一個索引的基數的,代價太大了,索引系統是通過遍歷部分資料,也就是通過採樣的方式,來預測索引的基數的。

 

重點來了,居然是採樣,那就有可能出現失誤的情況,也就是說,c 這個索引的基數實際上是很大的,但是採樣的時候,卻很不幸,把這個索引的基數預測成很小。例如你採樣的那一部分資料剛好基數很小,然後就誤以為索引的基數很小。然後系統就不走 c 索引了,直接走全部掃描了。

 

所以呢,說了這麼多,得出結論:由於統計的失誤,導致系統沒有走索引,而是走了全表掃描,而這,也是導致我們 SQL 陳述句執行的很慢的原因。

 

這裡宣告一下,系統判斷是否走索引,掃描行數的預測其實只是原因之一,這條查詢陳述句是否需要使用使用臨時表、是否需要排序等也是會影響系統的選擇的。

 

不過呢,我們有時候也可以通過強制走索引的方式來查詢,例如

select * from t force index(a) where c < 100 and c < 100000;

 

我們也可以通過

show index from t;

 

來查詢索引的基數和實際是否符合,如果和實際很不符合的話,我們可以重新來統計索引的基數,可以用這條命令

analyze table t;

 

來重新統計分析。

 

既然會預測錯索引的基數,這也意味著,當我們的查詢陳述句有多個索引的時候,系統有可能也會選錯索引,這也可能是 SQL 執行的很慢的一個原因。

 

下麵做一個總結。

 

五、總結


 

以上是我的總結與理解,最後一個部分,我怕很多人不大懂資料庫居然會選錯索引,所以我詳細解釋了一下,下麵我對以上做一個總結。

 

一個 SQL 執行的很慢,我們要分兩種情況討論:

 

1. 大多數情況下很正常,偶爾很慢,則有如下原因

 

a. 資料庫在掃清臟頁,例如 redo log 寫滿了需要同步到磁盤。

 

b. 執行的時候,遇到鎖,如表鎖、行鎖。

 

2. 這條 SQL 陳述句一直執行的很慢,則有如下原因。

 

a. 沒有用上索引:例如該欄位沒有索引;由於對欄位進行運算、函式操作導致無法用索引。

 

b. 資料庫選錯了索引。

赞(0)

分享創造快樂