Post view

MySQL 8.0.20版insoler首頁、相簿首頁的SQL查詢指令從「0.57秒」變成「0.002秒」的密技

全新大改版的MySQL 8.0其實是早在2016年的2016-09-12就開始進行研發,直到一年後的2017-09-21才正式釋出第一個MySQL 8.0.3 RC版,發表1年半以後,到2018-04-19才發布可以正式安裝使用的MySQL 8.0.11版。

我想很多技術先進的網站,也一直關注MySQL 8.0的發展,也許有的網站早在2年前就已經升級到MySQL 8.0.11版,有的網站可能會繼續關注,等到去年2019-02-01發表MySQL 8.0.15以後才決定正式升級到這個版本。

MySQL 8.0 Release Notes

Changes in MySQL 8.0.22 (Not yet released, General Availability)
Changes in MySQL 8.0.21 (Not yet released, General Availability)
Changes in MySQL 8.0.20 (2020-04-27, General Availability)
Changes in MySQL 8.0.19 (2020-01-13, General Availability)
Changes in MySQL 8.0.18 (2019-10-14, General Availability)
Changes in MySQL 8.0.17 (2019-07-22, General Availability)
Changes in MySQL 8.0.16 (2019-04-25, General Availability)
Changes in MySQL 8.0.15 (2019-02-01, General Availability)
Changes in MySQL 8.0.14 (2019-01-21, General Availability)
Changes in MySQL 8.0.13 (2018-10-22, General Availability)
Changes in MySQL 8.0.12 (2018-07-27, General Availability)
Changes in MySQL 8.0.11 (2018-04-19, General Availability)
Changes in MySQL 8.0.5 - 8.0.10 (Skipped version numbers)
Changes in MySQL 8.0.4 (2018-01-23, Release Candidate)
Changes in MySQL 8.0.3 (2017-09-21, Release Candidate)
Changes in MySQL 8.0.2 (2017-07-17, Development Milestone)
Changes in MySQL 8.0.1 (2017-04-10, Development Milestone)
Changes in MySQL 8.0.0 (2016-09-12, Development Milestone)

由於MySQL沒有6.0版、7.0版,而是直接從5.7版直升8.0版,因此在沒有升級到MySQL 8.0以前,全球許多網站應該都是使用MySQL 5.7版,目前最新版本是MySQL 5.7.29版。

但是insoler一直都沒有升級到MySQL 5.7版,而是使用更舊的MySQL 5.6.41版。雖然目前最新的版本是MySQL 5.6.48,但是從MySQL 5.6.42以後就只有支援Linux、Windows,不再支援macOS,因此在7天前寫的這篇文章的時候,insoler仍舊是使用有點老舊的MySQL 5.6.41版(這是2018-07-27發表的版本):

insoler社群網站正式升級到最新版macOS Catalina 10.15.4、PHP Version 7.4.5、ImageMagick 3.4.4

從7天前的PHP效能測試畫面可以看到,MySQL Server資料庫伺服器版本是MySQL 5.6.41版,而且insoler相簿首頁的資料查詢時間只要「0.002秒」!想要追求比這個速度還要更快10倍、100倍的速度,對「奈米社群網站」來說並沒有任何意義。

雖然insoler一直是使用2018-07-27發表的MySQL 5.6.41版(這是因為升級到MySQL 5.7版就會導致整個網站的開啟速度明顯變慢許多),但只要有新的MySQL 8.0版本,我就會試著升級看看,因此在一個星期以前2020-04-27剛剛發表的MySQL 8.0.20也安裝測試看看。

這是第一個可以正常安裝在macOS Catalina 10.15.4的版本!前一個首次支援macOS 10.15的MySQL 8.0.19竟然完全無法安裝在10.15,甚至無法啟動!

雖然MySQL 8.0.20可以正常安裝在macOS Catalina 10.15.4,也可以穩定的運作、執行,但是可以看到「insoler相簿首頁的資料查詢時間」從「0.002秒」變成「0.562秒」!竟然慢了281倍!

請參考我在前一天寫的這篇文章:

insoler社群網站正式從MySQL 5.6.41直接升級到MySQL 8.0.20最新版

非常不幸的是insoler相簿首頁的資料查詢時間是「0.002秒」這樣的成績必須是在滿足某種條件下,MySQL 5.6.41才能跑出這樣的優異成績!那麼,是什麼樣的條件就可以把「0.586秒」像是魔法一般變成只要「0.002秒」?

更不幸的是,我完全不清楚到底要怎麼做才能讓MySQL 5.6.41爽快、滿意?我只知道,如果發現網站的開啟速度變的異常緩慢的時候,就只好這麼做:

  • 備份匯出整個insoler.sql資料庫
  • 使用MySQL Workbench刪除insoler資料庫
  • 匯入備份好的insoler.sql資料庫
  • 重新啟動MySQL 5.6.41

運氣好的話,重新啟動MySQL 5.6.41幾分鐘以後,就可以看到「0.586秒」變成「0.002秒」的魔法!但如果運氣不好,還是會跑出「0.57秒」這樣的速度,就只好重做以上的動作,再測試看看。

事實上,這1-2年以來,只要遇到問題(並沒有週期性,不一定什麼時候會突然變慢)我一直都是在發現insoelr首頁開啟速度突然變慢以後,默默的做著以上的備份與還原資料庫的動作。

然而,直到最近就算做著已經非常熟練的備份與還原資料庫的動作,也無法讓MySQL 5.6.41跑出「0.002秒」的優異成績,而是幾乎一直變成「0.57秒」「0.58秒」,偶爾可以跑出「0.002秒」。但是我無法讓網頁開啟速度是依賴「運氣」!運氣好的時候就非常快,運氣差的時候,就只好等0.6秒以上...

既然MySQL 5.6.41已經無法展現「0.002秒」的魔法,而MySQL 8.0.20又可以正常安裝在macOS Catalina 10.15.4最新版,也可以穩定的正常運作,那就決定還是升級到MySQL 8.0.20。反正無論是MySQL 5.6.41還是MySQL 8.0.20跑出來的成績都是「0.57秒」「0.58秒」左右,運氣不好的時候還會看到0.6秒、0.8秒的SQL執行速度!

要如何將「0.57秒」「0.58秒」變成「0.002秒」?這只好靠我自己的魔法法術來解決問題!

解決以後就會看到就算是在MySQL 8.0.20版,「insoler相簿首頁的資料查詢時間」再次跑出與MySQL 5.6.41版本完全相同從「0.002秒」的成績!

雖然MySQL 8.0.20執行「SELECT BENCHMARK 指令執行時間」需要「0.503秒」還是比MySQL 5.6.41的「0.267秒」慢一倍,但已經算是相當令人滿意的版本。畢竟已經沒有新的更新版本,也無法升級的MySQL 5.6.41版,雖然還可以安裝在macOS 10.15.4版本上,但能不能繼續安裝在今年新的macOS 10.16、10.17新版本上就很難說。不可能因為無法安裝在macOS 10.16上,就一直使用舊的macOS 10.15.4與更老舊的MySQL 5.6.41。

由於「insoler Benchmark 效能測試程式」是我自己寫的測試程式(我並不打算公開原始碼,因為即使公開原始碼,別人沒有使用BOONEX的海豚系統,他安裝上去也無法得到正確的測試結果!),也許有人會認為是我寫的PHP程式碼本身的問題,才會有「0.002秒」變成「0.56」秒的問題!

因此我使用每個網站幾乎都有的phpMyAdmin來進行相同的測試。打開insoler資料庫以後,點選網頁上的「SQL」標籤頁面,然後複製貼上SELECT資料搜尋指令,這個指令就是insoler首頁與相簿首頁的資料查詢指令:

你可以看到這個SELECT資料查詢指令的執行時間竟然需要0.5677秒!這個執行結果與我寫的PHP程式碼完全相同!因為我也是在我寫的PHP程式碼裡面執行完全相同的SELECT資料查詢指令。

點選「結構」就會看到bx_photos_main相簿資料表的資料結構。這不是我設計的資料表,這個資料庫的資料表幾乎全部都是BOONEX為海豚Dolphin 7設計的資料表。

所有的資料表都必須建立查詢專用的「索引表」這樣才能提高搜尋的速度。你可以看到索引表的類型全部都是BTREE的「二元樹」結構。Binary tree的「二元樹」其實是非常老舊的資料結構,只要有學過「資料結構」或是「演算法」的電腦資訊科系的學生,都應該知道「二元樹」。

「二元樹」其實非常簡單,就是把整個資料按照大小排序以後,使用「二分法」來搜尋資料。比如在1-10裡面想要找到8的所謂位置,並不是從1開始一一比較,找了8次才找到8。而是使用「二分法」,直接從1-10的中間的5來看看是否比8大?還是比8小?

如果5比8小,就從5-10中間的7,再看看是比8還要大?或是還要小?這樣只要找3次就可以找到正確的資料位置。而不需要從頭開始逐一搜尋。

在bx_photos_main資料表底下的索引表都被我逐一檢視過,除了以前BOONEX忘記的非常重要的Uri(網址欄位)以外,並沒有遺漏的索引表。

並不是資料表的所有欄位,都需要建立相對的索引表!沒有用在搜尋的欄位就不需要建立索引,例如Ext副檔名的欄位就不需要建立索引,因為照片主要想要找的是檔案名稱,並不需要搜尋副檔名。沒有使用的欄位也建立索引表,甚至是所有欄位都建立索引表,反而會造成MySQL資料庫在新增、刪除一筆資料時候的沈重負擔!

因為「二元樹」可以快速搜尋的前提是必須經過「排序完畢的資料」,無論是從小排到大,或是由大排序到小都沒關係。絕對不可以是雜亂無章的資料內容,這樣使用「二元樹」搜尋方式,反而會永遠都找不到資料(除非運氣好),對於雜亂無章的資料,反而只能使用從頭到尾一一比對的方式。這樣就非常浪費時間,特別是在超過幾百萬筆、幾千萬筆,甚至好幾百兆筆以上(例如Facebook的相簿資料庫)

換句話說,MySQL資料庫在新增、刪除一筆資料以後,就必須在背景自動完成「索引排序」的動作,這樣才能在下次使用SELECT搜尋資料的時候,能跑出「0.002秒」甚至是低於「0.001秒」的查詢速度!如果不使用的欄位也全部都建立索引表的話,就會造成新增、刪除一筆資料時候,必須重新排序全部的「索引表」,導致MySQL沈重負擔,佔用更多CPU時間。

因此,在建立資料表以前都必須考慮所有的資料表,那些欄位需要查詢?哪些不需要查詢?需要查詢的資料表就絕對要建立「索引表」這樣才能加快資料的搜尋速度。

事實上,BOONEX團隊還是忘記建立非常重要的索引表!我在將近3年前的2 Aug 2017,在BOONEX討論區上提出過這個問題「忘記建立相片URI網址的索引表」:

ISSUES: Forget to create an index in URI of photo

BoonEx forgets to create an INDEX in the URI of the bx_photos_main, which results in unusually slow browsing of the photos.

Because when user browse the previous and next photo, Is using the photo's URL to search for photos in MySQL.

In MySQL slow.log will find a select query that takes 2-5 seconds.

# Query_time: 1.335483  Lock_time: 0.000350 Rows_sent: 1  Rows_examined: 474884
use insoler;
SET timestamp=1487295159;
SELECT `bx_photos_main`.`ID` as `medID`, `bx_photos_main`.`Categories` as `Categories`, `bx_photos_main`.`Owner` as `medProfId`, `bx_photos_main`.`Title` as `medTitle`, `bx_photos_main`.`Uri` as `medUri`, `bx_photos_main`.`Desc` as `medDesc`, `bx_photos_main`.`Tags` as `medTags`, `bx_photos_main`.`Date` as `medDate`, `bx_photos_main`.`Views` as `medViews`, `bx_photos_main`.`Status` as `Approved`, `bx_photos_main`.`Featured` as `Featured`, `bx_photos_main`.`Rate` as `Rate`, `bx_photos_main`.`RateCount` as `RateCount`, `bx_photos_main`.`Ext` as `medExt`, `bx_photos_main`.`Size` as `medSize`, `bx_photos_main`.`Hash` as `Hash`, COUNT(`share1`.`ID`) as `Count`, `sys_albums`.`ID` as `albumId`, `sys_albums`.`Caption` as `albumCaption`, `sys_albums`.`Uri` as `albumUri`, `sys_albums`.`AllowAlbumView`, `sys_albums_objects`.`obj_order`
FROM `bx_photos_main`
LEFT JOIN `bx_photos_main` as `share1` USING (`Owner`)

INNER JOIN `sys_albums_objects` ON `sys_albums_objects`.`id_object`=`bx_photos_main`.`ID`
INNER JOIN `sys_albums` ON (`sys_albums`.`ID`=`sys_albums_objects`.`id_album` AND `sys_albums`.`Type`='bx_photos')

WHERE `bx_photos_main`.`Uri`='14405226653176' GROUP BY `share1`.`Owner` LIMIT 1;

 

# Query_time: 4.736743  Lock_time: 0.000352 Rows_sent: 1  Rows_examined: 497338
SET timestamp=1500400274;
SELECT `bx_photos_main`.`ID` as `medID`, `bx_photos_main`.`Categories` as `Categories`, `bx_photos_main`.`Owner` as `medProfId`, `bx_photos_main`.`Title` as `medTitle`, `bx_photos_main`.`Uri` as `medUri`, `bx_photos_main`.`Desc` as `medDesc`, `bx_photos_main`.`Tags` as `medTags`, `bx_photos_main`.`Date` as `medDate`, `bx_photos_main`.`Views` as `medViews`, `bx_photos_main`.`Status` as `Approved`, `bx_photos_main`.`Featured` as `Featured`, `bx_photos_main`.`Rate` as `Rate`, `bx_photos_main`.`RateCount` as `RateCount`, `bx_photos_main`.`Ext` as `medExt`, `bx_photos_main`.`Size` as `medSize`, `bx_photos_main`.`Hash` as `Hash`, COUNT(`share1`.`ID`) as `Count`, `sys_albums`.`ID` as `albumId`, `sys_albums`.`Caption` as `albumCaption`, `sys_albums`.`Uri` as `albumUri`, `sys_albums`.`AllowAlbumView`, `sys_albums_objects`.`obj_order`
FROM `bx_photos_main`
LEFT JOIN `bx_photos_main` as `share1` USING (`Owner`)

INNER JOIN `sys_albums_objects` ON `sys_albums_objects`.`id_object`=`bx_photos_main`.`ID`
INNER JOIN `sys_albums` ON (`sys_albums`.`ID`=`sys_albums_objects`.`id_album` AND `sys_albums`.`Type`='bx_photos')

WHERE `bx_photos_main`.`Uri`='14016060603254' GROUP BY `share1`.`Owner` LIMIT 1;

I spent half a year to trace, and finally find the problem is out of this program : BxDolFilesDb.php

This MySQL script is too complicated and MySQL's bx_photos_main's Uri is not indexed

因為BOONEX忘記建立相片URI網址的索引表,因此導致瀏覽、開啟任何一個照片,每個MySQL的Query查詢的SELECT指令都需要花1.3秒~4.7秒!只是瀏覽相簿裡面的相片,每次都要花1-5秒,這真是非常離譜的事情!

只能猜想BOONEX團隊在多年研發Dolphin 5、Dolphin 6、Dolphin 7(最新版是7.4版,發表在一年多前的11th of February 2019)的時候,一直都沒有進行龐大資料庫的「壓力測試」!

所謂「壓力測試」就是利用PHP程式碼建立超過100萬筆,甚至是1000萬筆隨機的亂數虛擬資料,無論是註冊的會員數、討論區資料、照片、相簿等,都應該要建立超過1000萬筆虛擬資料,在每個版本正式發表以前,都進行龐大資料庫的「壓力測試」,來確定全新大改版的Dolphin能不能在會員的網站上正常執行?

從我多年來(我的註冊時間是2012年的registered 02.03.12)我使用海豚的經驗來看,BOONEX團隊不要說是老舊的Dolphin系統,應該連新的UNA系統都從來沒做過建立龐大資料庫的「壓力測試」!頂多是建立數10筆、數100筆的超小型資料庫來進行測試,因此在大型資料庫、超大型資料庫上才會發生很多問題!

由於bx_photos_main資料表都已經有該建立的索引表,而且在MySQL 5.6.41的環境下可以達到「0.002秒」的優異查詢速度,就一直沒有深究下去。既然相簿網頁需要使用bx_photos_main與sys_albums兩個資料表,這次就把研究的重點放在另外一個sys_albums資料表,這是sys_albums資料表全部的12個欄位格式:

這是sys_albums資料表目前建立的5個索引表格:

需要建立「索引」的資料表欄位,就是在SELECT指令後面的WHERE的搜尋指令。這是insoler首頁、相簿首頁的SELECT搜尋指令:

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

既然bx_photos_main與Profiles(用來存放會員帳號的資料表)都沒有缺少的索引鍵,剩下的可疑部分就只剩sys_albums資料表。由於BOONEX已經建立好Status與Type資料表的索引,唯一沒有建立的就只剩下AllowAlbumView欄位。

因此我按下建立索引的「執行」按鈕,輸入AllowAlbumView的索引名稱(使用與資料表欄位相同的名稱),然後點選「INDEX」索引功能,要建立索引的欄位要選AllowAlbumView。

phpMyAdmin最方便的地方就是我們不再需要背SQL指令!雖然SQL指令相較於電腦語言、作業系統指令並不會很多,但不需要背指令,還是比MySQL Workbench好用許多。

這是在insoler資料庫的sys_albums資料表新增AllowAlbumView索引的指令:

ALTER TABLE `insoler`.`sys_albums` ADD INDEX `AllowAlbumView` (`AllowAlbumView`);

新增AllowAlbumView索引表就會看到這樣的畫面:

因為sys_albums資料表並沒有很大,目前總共也只有6,525筆資料,這是相簿的管理資料,因此insoler網站上有6500多本相簿。

從0.5677秒變成0.0049秒!

再次執行相同的SELECT相簿指令,這次就會看到跑出只要0.0049秒的優異成績!在前面執行相同的SELECT搜尋指令,需要花0.5677秒!只是新增一個AllowAlbumView索引表,就可以大幅縮短到0.0049秒,相同指令的速度快了115倍!

0.5677秒 / 0.0049秒 = 115.85倍

再執行一次insoler Benchmark 效能測試程式,就可以看到insoler 相簿首頁的資料查詢時間變成只要0.001秒〜0.002秒!

只不過是新增一個AllowAlbumView索引表,就可以加快115倍!讓insoler首頁、相簿首頁的開啟時間從略慢的0.6秒變成只要0.002秒!說不定還有某些需要加以檢討、檢視的資料表還需要改進!

底下是insoler討論區的SELECT執行速度。雖然0.0306秒並不算太慢,但相對於相簿的0.0049秒來說,還是慢了6.2倍!

點選「結構」就可以看到bx_forum_topic討論區主題的資料表結構,以及目前建立好的5個索引表:

我發現bx_forum_topic資料表的SELECT查詢指令裡面有使用last_post_when資料表,但是從上面的索引表格裡面,卻可以看到沒有建立last_post_when索引鍵!因此我按下建立索引的「執行」按鈕,然後新增一個last_post_when索引:

然後就會看到新增的last_post_when索引鍵:

再執行一次相同的討論區首頁的SELECT指令,就會看到這次只要0.0016秒!從前面的「0.0306秒」大幅進步到只要「0.0016秒」,快了19.1倍!

insoler首頁、相簿首頁的開啟速度從「0.57秒」變成只要「0.002秒」,討論區的開啟速度從「0.0306秒」變成只要「0.0016秒」,只是新增「索引表」就有這麼好的表現,可見得在資料庫裡面正確使用「二元樹」的「索引表」的重要性。

對於相當成熟,而且是全世界最多人使用的WordPress、Joomla等網站系統,我不知道他們的研發團隊有沒有試著進行過龐大資料庫的「壓力測試」,至少可以肯定,他們應該是不會犯忘記建立「索引表」的錯誤!即使忘記了,應該會有很多使用者反應網頁開啟遲緩、龜速,或是忘記建立「索引表」的問題。

蘇言霖 2020/05/03 0 1230
Comments
Order by: 
Per page:
 
  • There are no comments yet
Rate
0 votes
Post info
蘇言霖
「超級懶貓級」社群網站站長
2020/05/03 (1425 days ago)
Actions