Post view

MySQL資料庫儲存引擎MyISAM只要0.01秒,InnoDB卻需要1.7秒,竟然慢了170倍!

由於Joomla、Drupal與許多CMS網站系統都從老舊的MyISAM改成新的InnoDB儲存引擎,雖然海豚目前的7.4.2最新版本仍舊是使用MyISAM,我想還是花一些時間自己動手改成InnoDB儲存引擎,希望能讓insoler社群網站跟上資訊時代的進化腳步,而不是一直使用幾年前,甚至還在用10幾年、20幾年前的恐龍時代的過時技術。

請參考這篇文章:

insoler社群網站的MySQL資料庫從MyISAM更換成InnoDB儲存引擎,以及utf8mb4編碼系統🍏

當年的MySQL在2008年被昇揚公司併購(In January 2008, Sun Microsystems bought MySQL AB for $1 billion),昇揚後來又被Oracle甲骨文併購。而甲骨文早就在2005年10月併購了Innobase公司,所以Innobase研發的InnoDB資料庫引擎,後來也因為Oracle併購Sun的MySQL,最後被Oracle加入到MySQL裡面。因此才會從MySQL 5.6版開始InnoDB成為預設的儲存引擎。

雖然MySQL 5.6版本的預設資料庫引擎,被改成InnoDB,而且「從MySQL 5.6.4之後版本InnoDB也開始支援Full-text search全文檢索與壓縮功能,可以達到與MyISAM相同的功能,所以在MySQL 5.6.4 (2011-12-20) 以後的版本,幾乎可以完全取代MyISAM。唯一的問題是「效能」。

網路上有非常多對於MyISAM與InnoDB比較的文章。在Wiki也有簡單的說明。不過Wiki內容已經過時,甚至還寫「壓縮功能已計畫於未來的新版中推出」事實上早就已經發表,而且也可以在MySQL 5.5以後的版本使用。

MyISAM是MySQL的預設資料庫引擎(5.5版之前),由早期的ISAM所改良。雖然效能極佳,但卻有一個缺點:不支援交易處理(transaction)。不過,在這幾年的發展下,MySQL也導入了InnoDB(另一種資料庫引擎),以強化參考完整性與並行違規處理機制,後來就逐漸取代MyISAM。

與InnoDB之比較

InnoDB可藉由交易記錄檔(Transaction Log)來恢復程式崩潰(crash),或非預期結束所造成的資料錯誤;而MyISAM遇到錯誤,必須完整掃瞄後才能重建索引,或修正未寫入硬碟的錯誤。InnoDB的修復時間,大略都是固定的,但MyISAM的修復時間,則與資料量的多寡成正比。相對而言,隨著資料量的增加,InnoDB會有較佳的穩定性。

MyISAM必須依靠作業系統來管理讀取與寫入的快取,而InnoDB則是有自己的讀寫快取管理機制。(InnoDB不會將被修改的資料分頁立即交給操作系統)因此在某些情況下,InnoDB的資料存取會比MyISAM更有效率。

InnoDB目前並不支援MyISAM所提供的壓縮與terse row formats,所以對硬碟與快取記憶體的使用量較大。因此MySQL從5.0版開始,提供另一個負載較輕的格式,他可減少約略20%的系統負載,而壓縮功能已計畫於未來的新版中推出。

當操作完全相容ACID(交易)時,雖然InnoDB會自動合併數筆連線,但每次有交易產生時,仍至少須寫入硬碟一次,因此對於某些硬碟或磁碟陣列,會造成每秒200次的交易处理上限。若希望達到更高的效能且保持交易的完整性,就必使用磁碟快取與電池備援。當然InnoDB也提供數種對效能衝擊較低的模式,但相對的也會降低交易的完整性。而MyISAM則無此問題,但這並非因為它比較先進,這只是因為它不支援交易。

由於Wiki的文章毫無參考價值,想要了解InnoDB與MyISAM的話,可以閱讀一下這些文章:

MySQL 超新手入門(8)儲存引擎與資料型態

「MyISAM」是MySQL預設的儲存引擎,「預設」的意思是如果你在建立表格的時候沒有指定一種儲存引擎,MySQL會幫你建立的新表格指定為「MyISAM」儲存引擎。

MySQL資料庫伺服器從3.23.49版本開始把「InnoDB」儲存引擎列為正式支援的功能,所以從這個版本開始,MySQL也提供與大型商用資料庫軟體一樣的功能。

MySQL 超新手入門(20)效率

存引擎在效率上也是一個很重要的因素,你會考慮資料庫的大小與種類,還有使用者的數量,然後選擇一個適合的儲存引擎。

MyISAM儲存引擎使用的「table-level」鎖定方式,適合使用在查詢工作非常多,資料維護比較少的資料庫,這樣的資料庫運作起來的效率會比較好。

InnoDB儲存引擎使用的「row-level」鎖定方式,適合使用在查詢與資料維護工作都差不多的資料庫,這樣的資料庫運作起來的效率會比較好。

不過,文章的標題雖然是「效率」卻完全沒有提到InnoDB與MyISAM的「效率」到底是差多少?InnoDB與MyISAM到底是誰比較快?只說明「效率會比較好」非常的模糊,完全不知道到底會有多好多?究竟能快多少?

由於幾乎完全沒有MySQL Benchmark效能測試工具,再加上任何的Benchmark效能測試程式,會受到電腦的CPU性能、RAM記憶體容量、速度,硬碟或是SSD固態硬碟的存取速度,甚至連使用的作業系統版本(比如相同的程式在Windows 10與Windows 7可能就會有不同的效率)所以實際的MySQL Benchmark測試就會變成非常困難。

我的作法非常簡單,就是把InnoDB與MyISAM資料庫都安裝在相同的一台Mac mini電腦主機上。使用的電腦主機的規格是:

  • Mac mini (Late 2009)
  • 2.53 GHz Intel Core 2 Duo(雙核心、2個執行緒)
  • 8 GB 1067 MHz DDR3
  • 256GB SATAIII SSD

InnoDB與MyISAM資料庫的內容,也就是資料量也必須幾乎完全相同!因為幾乎沒有任何資料,或是資料內容差異極大,也沒有測試的價值。因此我使用的是insoler社群網站轉換前的MyISAM資料庫,以及轉換完畢的InnoDB資料庫。資料的筆數幾乎完全相同,而且轉換前後的兩個資料庫都是放在同一個MySQL Server上。使用的版本是MySQL Server 5.6.38。這個版本是OS X El Capitan 10.11.6可以安裝的最後一個版本,無法升級到MySQL 5.6.41以後的版本!

首先來看一下轉換前的MyISAM資料引擎:

我使用的測試指令是BOONEX寫在海豚Dolphin 7.4.2最新版本的指令。這個指令用來顯示insoler首頁,以及相簿首頁的照片:

  • SELECT `bx_photos_main`.`ID` as `id`, `bx_photos_main`.`Title` as `title`, `bx_photos_main`.`Ext`, `bx_photos_main`.`Uri` as `uri`, `bx_photos_main`.`Date` as `date`, `bx_photos_main`.`Size` as `size`, `bx_photos_main`.`Views` as `view`, `bx_photos_main`.`Rate`, `bx_photos_main`.`RateCount`, `bx_photos_main`.`Hash`, `bx_photos_main`.`ID`, `sys_albums_objects`.`id_album`, `bx_photos_main`.`Owner` as `ownerId`,  `sys_albums`.`AllowAlbumView`, `Profiles`.`NickName` as `ownerName`  FROM `bx_photos_main`  left JOIN `sys_albums_objects` ON `sys_albums_objects`.`id_object`=`bx_photos_main`.`ID` left JOIN `sys_albums` ON `sys_albums`.`ID`=`sys_albums_objects`.`id_album` left JOIN `Profiles` ON `Profiles`.`ID`=`bx_photos_main`.`Owner` WHERE 1 AND `bx_photos_main`.`Status` ='approved' AND `Profiles`.`Status` NOT IN('Rejected','Suspended') AND `sys_albums`.`AllowAlbumView` IN('3','4') AND `sys_albums`.`Status` ='active' AND `sys_albums`.`Type` ='bx_photos'  ORDER BY `ID` DESC LIMIT 0, 15

只要點選phpMyAdmin的「SQL」標籤頁面,然後貼上SQL查詢指令,再按一下網頁上的「執行」按鈕即可。

你可以看到這個相當複雜的SQL指令的執行的時間是「查詢用了0.0164秒」。扣除電腦主機本身需要執行macOS系統以及其他工作,所以0.01秒以下的數字全部忽略,因此這個SELECT查詢資料庫指令只需要「0.01秒」!

接下來測試轉換後的InnoDB資料引擎:

在InnoDB資料庫執行完全相同的SELECT指令:

你可以看到完全相同的SELECT指令,在完全相同的MySQL與完全相同的Mac mini電腦主機上,竟然跑出「查詢用了1.7644秒」的成績!這個時間還只是計算出在InnoDB資料庫執行SELECT指令的時間,實際上還需要把查詢的結果傳給海豚,再經過PHP處理,最後才能看到insoler首頁或是相簿首頁的最新的照片內容!

簡單的說,光是查詢InnoDB資料庫就需要花「1.7秒」的話,如果PHP程式需要跑0.1〜0.3秒的時間來處理的話,光是開啟insoler首頁或是相簿首頁,甚至要等2秒以上!這個速度可說是慢的非常離譜!

因為在轉換以前MyISAM資料引擎只需要「0.01秒」,可說是insoler首頁或是相簿首頁一點就開,幾乎不需要等待太多的時間。但是在我花了許多時間把MyISAM資料庫轉成InnoDB資料引擎,開啟insoler首頁或是相簿首頁竟然需要「1.7秒」!「慢了170倍」不要說是user,就連我自己都無法接受!

因為「慢了170倍」不可能靠升級電腦CPU主機,更換目前最快的SSD硬碟,甚至是使用SSD RAID陣列,來提升伺服器電腦的性能達到170倍或是200倍!就算買現在最新、最強的電腦,頂多也只能快10倍。更慘的是只能快2-3倍,絕對不可能快200倍,因此不可能思考「升級電腦」來解決。

由於MySQL Server本身有快取常用資料的功能。為了避免因為Cache快取而影響測試結果,我每次都是關閉MySQL Server再重新啟動,然後再進行以上的測試。因此看到的測試結果會比較正確。你可以看到MySQL Server的uptime啟動時間只有經過2分8秒而已。

上面的測試是在有點老舊的Mac mini主機,而且只有雙核心的2.53 GHz Intel Core 2 Duo(這其實是擔任BNW網站的主機),所以接下來換成擔任insoler網站的MySQL資料庫伺服器主機,再做一次相同的測試看看。使用的Mac mini的電腦主機規格是:

  • Mac mini (Late 2012)
  • 2.6 GHz Intel Core i7(4核心、8執行緒)
  • 16 GB 1067 MHz DDR3
  • Apple原廠250GB SATAIII SSD

由於MyISAM速度非常快,不需要再測試。所以只要測試非常、異常龜速的InnoDB資料引擎即可。

測試的MySQL指令是完全相同的SELECT查詢指令。

你可以看到SELECT執行結果是「查詢用了0.8605秒」。這個速度雖然比前面的「1.7秒」快很多,但也只有快了一倍!仍舊比MyISAM資料引擎的「0.01秒」慢了86倍!

0.86秒 / 0.01秒 = 86倍

無論是「慢了86倍」還是「慢了170倍」,如果是任何人都沒有任何感覺的速度,例如最慢也只要0.0170秒,最快可以達到0.0001秒,我想除了程式設計人員、網站管理員、站長等,可能會比較在意。一般user不可能會察覺,也沒人會在意某一個指令,還是某一段程式碼的執行速度慢170倍!

在電腦上,幾乎所有功能都與「速度」有關。因此,如果電腦開機、電腦關機、某個程式的執行速度(例如照片轉檔、影片轉檔)、檔案copy、檔案下載...等等的速度、竟然慢了170倍,從「只要1秒」,變成「需要170秒=2.8分鐘」我想應該是沒人可以忍耐!相反的,如果可以「變快170倍」「提高200倍」的執行速度,從原本電腦開機需要3分鐘、影片轉檔需要170秒,都變成只要1秒,應該會有讓人驚喜的感受!

慢了170倍,需要0.8〜1.7秒的insoler首頁與相簿首頁

從「MyISAM轉成新的InnoDB儲存引擎」到底有哪些部分會受到影響,會變成異常遲緩?就像上面舉的例子,如果就算「變慢」但是MySQL指令的執行速度還是低於0.01秒以下,我想不會有人在意無法被人察覺的變慢。

因為不是所有的資料表的結構都很複雜,也不是所有的資料表的資料筆數都很多。對於資料結構簡單,資料筆數很少的話,例如前面畫面上的bx_blogs_views_track最多也只有928筆資料,無論是MyISAM還是InnoDB引擎,處理速度都會很快。但是其他的資料表,如果有超過20萬筆、100萬筆,甚至是幾億筆資料,就算有建立「索引表」,想要在幾億筆資料裡面,「瞬間、0.01秒以內」找到想要的資料,幾乎是不可能的任務!

只不過是開啟insoler首頁或是相簿首頁,每次都要花將近1秒(甚至是1.7秒)的時間,應該還是很難接受!

點選相簿首頁底下的其他頁面的「翻頁」按鈕,每一頁都要花將近1秒〜1.7秒的時間,我想大概會被user認為insoler網站不是電腦主機非常老舊,就是網路速度非常緩慢。雖然「升級電腦主機」是一個最簡單的解決方式,不過,不可能每年都換新的電腦主機。而且就算每年都換最頂級的伺服器主機,也不見得可以每年速度提高3倍,甚至是10倍以上。

就算MySQL Server的內建Cache快取有記錄insoler首頁、相簿首頁,以及前幾頁常用的相簿內容,也絕對不可能快取每一頁面的資料內容!因此翻頁到後面的第10頁、第12頁,一樣會遇到每一頁都需要等1秒~1.7秒的問題!

把「舊的MyISAM轉成新的InnoDB儲存引擎」卻竟然導致相簿首頁慢了170倍,變成龜速相簿,這個非常頭痛的問題要如何解決?放棄新的InnoDB,回到舊的MyISAM?因為做任何事以前都會事先備份整個網站,因此只要Restore匯入備份的資料就可以簡單的回到MyISAM。但是我並不想要回到MyISAM。MyISAM雖然速度很快,但相對的也有一些重大缺點。否則不會把從MySQL 5.6起,把資料庫儲存引擎預設為InnoDB。

解決辦法請繼續閱讀這篇文章:

MySQL資料庫InnoDB引擎從0.8秒變0秒,把不可能變可能,爆速800倍的神奇密技!

蘇言霖 2019/04/22 0 486
Comments
Order by: 
Per page:
 
  • There are no comments yet
Rate
0 votes
Post info
蘇言霖
「超級懶貓級」社群網站站長
2019/04/22 (312 days ago)
Actions