2024-04-18|閱讀時間 ‧ 約 27 分鐘

MariaDB速度慢?進階正確使用,沒設定對,不要再嫌棄他慢24.04|22.04|20.04|18.04

一開始先定義好你的需求: 你需要極高的讀取效率或是極高的寫入效率? 需求定義好之後,我們就開始今天的內容。


講結論:

需要極高讀取效率請選擇MyISAM 儲存引擎;

需要極高寫入效率請選擇InnoDB 儲存引擎;


MyISAM/InnoDB原理與比較:


原理相異處:

MyISAM 儲存引擎採用了一種稱為「表層級鎖定」(Table-level Locking)的鎖定機制,這意味著對於一張表的讀取操作和寫入操作是互斥的,也就是說,當一個執行緒在寫入資料時,其他所有執行緒都不能進行任何讀寫操作。但是,在讀取數據時,MyISAM 儲存引擎可以使用「同時讀」(Concurrent Reading)的方式,多個執行緒可以同時讀取同一張表中的數據,因此 MyISAM 儲存引擎的讀取效率非常高。


InnoDB 儲存引擎採用了一種更細緻的鎖定機制,即「行層級鎖定」(Row-level Locking)。這種鎖定機制可以保證在Concurrent的情況下,每個執行緒都只鎖定自己需要的行而不是整張表,從而避免了 MyISAM 儲存引擎的寫入衝突問題。因此,InnoDB 儲存引擎在寫入資料時會比 MyISAM 儲存引擎更有效率。


結構部分:

MyISAM:每個MyISAM在磁碟上儲存成三個檔案。名列前茅的文件的名字會以表的名字開始,副檔名是文件類型。.frm檔案儲存表定義。資料檔案的副檔名為.MYD (MYData)。索引檔的副檔名是.MYI (MYIndex)。


InnoDB:基於磁碟的資源是InnoDB表空間資料檔案和它的日誌文件,InnoDB 表的大小只受限於作業系統文件的大小,一般為 2GB。



任務部分:

MyISAM類型的表格強調的是效能,其執行數度比InnoDB類型更快,但不提供交易支援。

InnoDB提供交易支援,外鍵(foreign key)等高階資料庫功能。


SELECT UPDATE,INSERT,DELETE操作部分:


MyISAM:因為鎖定寫入,天然的適合執行大量的SELECT。


InnoDB:如果要執行大量的INSERT或UPDATE,出於效能方面的考慮,應該使用InnoDB表。

DELETE FROM table時,InnoDB不會重新建立表,而是一行一行的刪除。

LOAD TABLE FROM MASTER操作對InnoDB是不起作用的,解決方法是先把InnoDB表改成MyISAM表,導入資料後再改成InnoDB表,但是對於使用的額外的InnoDB特性(例如外鍵)的表不適用。



AUTO_INCREMENT的操作部分:

MyISAM:INSERT和UPDATE作業時自動更新列。這使得AUTO_INCREMENT列更快(至少10%)。在序列頂的值被刪除之後就不能再用。 (當AUTO_INCREMENT列被定義為多列索引的最後一列,可以出現重複使用從序列頂部刪除的值的情況)。

AUTO_INCREMENT值可用ALTER TABLE或myisamch來重設。

對於AUTO_INCREMENT類型的欄位,InnoDB中必須包含只有該欄位的索引,但是在MyISAM表中,可以和其他欄位一起建立聯合索引。


InnoDB:如果為一個表指定AUTO_INCREMENT,在資料詞典裡的InnoDB表將包含一個名為自動計數器的計數器,它被用在為該列賦予新值。

自動增長計數器僅儲存在主記憶體中,而不是存在磁碟上。


表的具體行數不同部分:

MyISAM:select count() from table,MyISAM只要簡單的讀出保存好的行數,注意的是,當count()語句包含 where條件時,兩種表的操作是一樣的。

InnoDB:InnoDB 中不保存表格的具體行數,也就是說,執行select count(*) from table時,InnoDB要掃描一遍整個表來計算有多少行。


鎖不同的部分:

MyISAM:以表為鎖。

InnoDB:提供行鎖(locking on row level),提供與Oracle 類型一致的不加鎖讀取(non-locking read in SELECTs),另外,InnoDB表的行鎖也不是絕對的,如果在執行一個SQL語句時MySQL無法確定要掃描的範圍,InnoDB表同樣會鎖定全表,例如update table set num=1 where name like “%H%”。


MYISAM和INNODB是MariaDB提供的兩種儲存引擎。兩者的優劣可謂各有千秋。 INNODB會支援一些關聯式資料庫的高階功能,MYISAM不支援。但MYISAM的性能更優,佔用的儲存空間少。所以,選擇何種儲存引擎,視具體應用而定。


如果你的應用程式一定要使用多元或高階功能,毫無疑問你要選擇INNODB引擎。但要注意,INNODB的行級鎖定是有條件的。在where條件沒有使用主鍵時,照樣會鎖定全表。例如DELETE FROM ATable這樣的刪除語句。


如果你的應用程式對查詢效能要求較高,就要使用MYISAM了。 MYISAM索引和資料是分開的,而且其索引是壓縮的,可以更好地利用記憶體。所以它的查詢效能明顯優於INNODB。壓縮後的索引也能節省一些磁碟空間。 MYISAM擁有全文索引的功能,這可以大幅優化LIKE查詢的效率。


有人說MYISAM只能用於小型應用,其實這只是一種偏見。如果資料量比較大,這是需要透過升級架構來解決,例如分表分庫(實際上真實的檔案獨立等),而不是單純地依賴儲存引擎。




分享至
成為作者繼續創作的動力吧!
© 2024 vocus All rights reserved.