在前面的文章提到，把「舊的MyISAM轉成新的InnoDB儲存引擎」卻竟然導致相簿首頁慢了170倍，變成龜速相簿，這個非常頭痛的問題要如何解決？放棄新的InnoDB，回到舊的MyISAM？
因為做任何事以前都會事先備份整個網站，因此只要Restore匯入備份的資料就可以簡單的回到MyISAM。但是我並不想要回到MyISAM。MyISAM雖然速度很快，但相對的也有一些重大缺點。否則不會把從MySQL 5.6起，把資料庫儲存引擎預設為InnoDB。
MySQL資料庫儲存引擎MyISAM只要0.01秒，InnoDB卻需要1.7秒，竟然慢了170倍！
有沒有什麼兩全其美的辦法，既可以繼續使用新的InnoDB儲存引擎，又可以「把不可能變可能」，從1.7秒，或是0.8秒，變成0秒？身為「恐龍級電腦程式設計師」當然就只好在腦海裡面搜尋所有的可能性，再從這些可能性裡面分別實驗，找出「把不可能變可能」，變成「只要0秒」的密技？
我想盡各種辦法，能想到的辦法只有這些（或許你可以想到更多、更好的點子）：
- 試著簡化「SELECT `bx_photos_main`」太過複雜的查詢指令，移除不必要的WHERE查詢條件。
- 調整MySQL伺服器的my.cnf設定檔的內容，仔細檢視使用「innodb_」的相關變數，增加快取記憶體容量。
- 把整個MySQL資料庫檔案放在RAM Disk記憶體磁碟。
- 製作網頁快取功能，只要沒有上傳照片，沒有新的照片，就完全不需要從MySQL讀取資料！
- 製作資料庫快取功能，只要是執行相同的「SELECT `bx_photos_main`」指令，就直接從快取檔案讀取，不執行任何MySQL指令！
- 在MySQL資料庫用 CREATE VIEW bx_photos_home 新增一個「複雜查詢」專用的表格。
- 新增一個相簿首頁專用的 bx_photos_home 資料表並增加更多欄位，使用InnoDB格式，避免使用太多的left JOIN合併表格指令。
- 新增一個相簿首頁專用的 bx_photos_home 資料表，只有這個資料表改用舊的MyISAM格式，其他都是使用InnoDB。
- 將「SELECT `bx_photos_main`」查詢指令切開，改成搜尋2次或是多次資料庫，而不使用太多的left JOIN合併表格指令。
簡化「SELECT `bx_photos_main`」太過複雜的查詢指令
在海豚7.4.2原版的相簿首頁的SQL指令是：
- 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
看起來相當複雜的SELECT指令，可以大概區分成5個部分：
- 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
這5個部分，分別是：
- SELECT：用來指定要取得的資料表的欄位名稱。當同時使用多個資料表的時候，必須指定「`資料表`.`欄位名稱`」。
- FROM：指定主要的資料表名稱。
- JOIN：結合其他資料表。為了不同的使用目的，通常會切割成多個資料表，而不是在一個資料表裡面製作全部的資料欄位。例如：使用者帳號、討論區、相簿等。如果要查詢上傳照片的使用者帳號，就必須使用JOIN指令來結合「使用者帳號表格」與「相簿主表格」。
- WHERE：指定要搜尋的資料欄位。在這裡的搜尋條件的意義是：上傳照片必須被核准＋使用者帳號不可以是拒絕或是暫停＋相簿允許公開瀏覽或是僅會員可以瀏覽＋相簿的狀態是啟用＋相簿的型態是照片。
- ORDER LIMIT：指定排序的順序是「倒數排序」，時間數字最大的排在最前面，這樣才可以使最新上傳的照片排在最前面。LIMIT則是用來指定只取出從第幾筆開始，以及只取出幾筆資料。
這個SELECT指令的執行時間，需要花1.7秒。
每次都重新啟動MySQL伺服器
為了避免MySQL的InnoDB的Cache快取資料影響測試結果，所以每次進行測試以前，我都重新啟動MySQL，而且在重新啟動以後立即進行測試的動作！當然，測試用的insoler資料庫也只有「insoler研究開發室」的網站可以開啟，除了我以外，沒有任何人可以開啟「insoler研究開發室」的網頁與使用實驗用的insoler資料庫。
這樣可以確保絕對不會使用InnoDB的Cache快取功能！因為我認為透過Cache加速，只不過是治標不治本，實際效果非常有限的動作。
由於「最新相簿」只需要：照片的檔案名稱、副檔名、照片網址、照片的圖片網址、上傳的使用者名稱。所以可以先簡化SELECT後面的資料表的欄位名稱。
- SELECT `bx_photos_main`.`Title` as `title`, `bx_photos_main`.`Ext`, `bx_photos_main`.`Uri` as `uri`, `bx_photos_main`.`Hash`, `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 `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 `bx_photos_main`.`ID` DESC LIMIT 0, 15
在JOIN後面的指令其實沒有太大的改變，但是查詢結果的表格欄位，大幅度精簡成為這樣的指令：
- SELECT `bx_photos_main`.`Title` as `title`, `bx_photos_main`.`Ext`, `bx_photos_main`.`Uri` as `uri`, `bx_photos_main`.`Hash`, `Profiles`.`NickName` as `ownerName` FROM `bx_photos_main`
把查詢的結果欄位簡化到只需要5個欄位以後，查詢的時間也縮短到「查詢用了1.57秒」。雖然有變快一些，但也只有快一點點而已。只是搜尋15筆相簿資料，要花1.5秒，還是太慢了！
海豚原版的搜尋條件是：上傳照片必須被核准＋使用者帳號不可以是拒絕或是暫停＋相簿允許公開瀏覽或是僅會員可以瀏覽＋相簿的狀態是啟用＋相簿的型態是照片。但是因為insoler會自動核准上傳照片，不會隨意拒絕或是暫停使用者帳號，相簿也會自動啟用。因此搜尋條件可以簡化到只有檢查「相簿允許公開瀏覽或是僅會員可以瀏覽」。
因此，其實只需要「WHERE `sys_albums`.`AllowAlbumView` IN('3','4') 」應該就可以達到相同的效果。所以SELECT指令還可以再簡化成這樣的指令：
- SELECT `bx_photos_main`.`Title` as `title`, `bx_photos_main`.`Ext`, `bx_photos_main`.`Uri` as `uri`, `bx_photos_main`.`Hash`, `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 `sys_albums`.`AllowAlbumView` IN('3','4') ORDER BY `bx_photos_main`.`ID` DESC LIMIT 0, 15
這個查詢指令的執行時間是「查詢用了1.46秒」：
老實說，「查詢用了1.46秒」與「查詢用了1.57秒」並沒有太大的差別，甚至與前面「查詢用了1.76秒」也沒有太大的差別！因為時間雖然從「1.76秒」縮短到「1.46秒」，但是對user來說，只有縮短「0.3秒」的話，一般人很難感受到只有「0.3秒」的差異。甚至會認為並沒有太大的差別。
那麼，上面的SELECT指令還可以再繼續簡化嗎？答案是否定！這已經是最簡化的指令。不能省略「JOIN結合其他資料表」的部分，因為這樣會導致上傳的照片無法查詢是屬於哪一本相簿，或是屬於哪一個會員帳號。
調整MySQL伺服器的my.cnf設定檔
所有的MySQL版本都需要依靠一個純文字的「my.cnf」設定檔來調整伺服器的運作方式與參數。原本這種工作應該要在視窗操作環境底下的MySQL Workbench直接設定，或是完全不需要任何設定，MySQL Server本身應該要按照電腦的CPU、GPU、RAM記憶體容量、磁碟容量... 等自動決定。就像是Windows早就廢除完全不切實際的win.ini（主要用在Windows 3.1以前的版本）自動調整所需的參數。
然而MySQL落後Windows系統30年以上，到現在仍舊繼續使用完全不切實際的「my.cnf」設定檔！
在調整完全不切實際的「my.cnf」設定檔之前，都必須連續開機24小時以上。甚至要連續開機3天以上，才能按照phpMyAdmin等軟體的建議來調整設定值，而且每次變更「my.cnf」都必須重新啟動MySQL。目前的MySQL連續開機時間已經超過6天，並不是6天前MySQL當機，而是調整過「my.cnf」設定檔之後，必須重新啟動MySQL Server。
在這個畫面上，其實還隱藏了另外一個問題！那就是「MySQL Server 起動後到目前為止的 Max_used_connections 最大同時連接數：84」！如果一秒內同時打開相簿首頁的user有84個人的話，每個人都需要0.8秒左右，那麼第84個user就必須等67秒！這已經超過Apache網站伺服器主機預設30秒逾時（雖然可以改成60秒，但是一個網頁超過10秒以上都無法開啟，應該不會有人繼續等下去！）這只會造成有許多人認為無法開啟相簿首頁，直接關閉網頁！
MySQL可以調整的參數實在太多，很可能超過1000個以上！光是與innodb有關的參數就有122個！
目前innodb_buffer_pool_size設定值是2048MB，在phpMyAdmin會顯示2GB。
innodb_buffer_pool_size設在2048MB到底是太多？還是太少？
在經過連續開機6天以後，可以在看到MySQL Workbench看到innoDB Buffer Pool的使用量是55%。換句話說，如果設定在1024MB的話，就會太少一點。如果設定在4096MB以上，也只是浪費記憶體空間而已。
因此參考MySQL Workbench看到innoDB Buffer Pool的使用量，最後決定innodb_buffer_pool_size設定在2048MB。對某些人來說，似乎是理所當然，但是對我來說，這仍舊是完全不切實際的事情！MySQL應該要自動決定所有參數才對！
雖然innodb_buffer_pool_size的預設值是128MB。事實上就算改成2048MB也沒有多少加速的效果！我想就算調整其他innodb參數、加大快取記憶體、甚至加大真實的記憶體到64GB以上，效果應該都非常有限！因為這些參數頂多只能「加快從磁碟機讀入MySQL檔案資料的時間」無法加快CPU的運算時間！
把MySQL資料庫放在RAM Disk記憶體磁碟
把非常重要的資料庫檔案放在RAM Disk記憶體磁碟？瘋了嗎？
沒錯！這絕對是絕對不能做的餿主意！因為只要電腦當機、停電、自動重新開機（例如作業系統的安全性更新），放在RAM Disk上面的資料全部都會永遠消失！所有資料全部人間蒸發！
因此，沒有人會建議你把非常重要的檔案放在RAM Disk記憶體磁碟！不過，反正我是在「insoler開發研究室」進行實驗、測試。就算所有資料都消失不見，也絕對不會影響正式運作的insoler網站！
與其不斷想辦法調整Cache快取記憶體的設定，不如乾脆把整個MySQL資料庫都放在RAM Disk記憶體磁碟算了！（前提是整個MySQL資料庫檔案比RAM記憶體的可用空間小很多）這樣子甚至根本完全不需要任何快取，都應該可以達到最高極限的速度才對！
insoler目前整個MySQL檔案夾裡面有665個檔案，全部的檔案大小是1.05GB。我想現在任何電腦都有8GB以上的記憶體。比較好一點的電腦甚至都有內建16GB，更頂級的電腦還可以擴充到32GB、64GB。所以在記憶體上設定一個2GB的RAM Disk記憶體磁碟，應該都沒有問題。
反正是拿「實驗用的資料」來進行測試，就算是放在RAM Disk上面又有何不可？所以那就來測試看看吧！
根據以前的磁碟機速度測試，RAM Disk磁碟機的速度大約是SSD固態硬碟的10倍～20倍！不過，如果你是使用目前最頂級的SSD，讀寫速度高達3000MB/s以上，那麽大約7000MB/s左右的RAM Disk就只會更快2-3倍。在Mac mini (Late 2012) 電腦上，SSD的讀取速度大約是516MB/s，RAM Disk的讀取速度大約是讀取3548MB，所以RAM Disk比SSD快了6.8倍左右。
insoler網站Mac mini Intel Core i7 2.6GHz伺服器主機的SSD、網路磁碟的Benchmark效能測試
我在電腦上建立了一個2GB大小的RAM Disk，然後把MySQL的insoler檔案夾的檔案，全部copy到RAM Disk裡面。再重新啟動MySQL。
測試的結果，我發現就算使用RAM Disk也幾乎完全沒有任何效果！查詢資料指令還是需要花0.8秒〜1.7秒！
為什麼會幾乎完全沒有任何效果？明明RAM Disk的資料讀取速度比SSD更快6.8倍！我想這應該是慢在MySQL系統的CPU處理時間，而不是「從HDD或是SSD載入MySQL檔案的時間」。
假設從SATA III的SSD開啟MySQL檔案資料，大約需要0.07秒。如果直接從RAM Disk讀取MySQL檔案資料的話，或許只需要0.01秒！但問題就在於，就算可以用0.01秒的速度開啟檔案，但是CPU的搜尋、運算資料就需要花「1.7秒」的話，只是把檔案放在RAM Disk，從「0.07秒」縮短到「0.01秒」也沒有太大的意義！這麼做頂多只能讓「1.77秒」的MySQL查詢速度縮短到「1.71秒」而已！因此才會導致實驗結果會幾乎沒有任何效果！
簡單的說，「太過緩慢的CPU處理速度」完全抵消「超高速RAM Disk記憶體磁碟」提升6倍～20倍的速度差異！
製作「網頁快取功能」或是「資料庫快取功能」
簡化MySQL的查詢指令，調整my.cnf設定，甚至把整個MySQL資料庫放在RAM Disk記憶體磁碟機上都沒有任何效果！接下來只好想想看還有什麼其他的旁門左道？喔～不是，是想想看還有什麼其他更好的辦法？
由於insoler首頁的相簿與相簿首頁，並不是每秒鐘都有人上傳照片。如果每天只有上傳幾張照片的話，當沒有上傳新的照片的時候，事實上根本就不需要查詢MySQL資料庫，只要製作「網頁快取」或是「資料庫快取」檔案，直接從Cache檔案讀取所需的相簿首頁的資料即可。等到有上傳新照片的時候，再一併更新Cache快取檔案內容。這樣的話，平時就幾乎完全不需要執行MySQL的SELECT指令，就能解決相簿首頁需要花0.8秒～1.7秒的問題！
這個想法、做法，基本上並沒有太大的問題。所以我花了好幾天研究，終於分別完成「網頁快取功能」與「資料庫快取功能」功能。
但是，我忘記「相簿首頁」底下有「翻頁」功能。可以直接翻頁到指定的頁面！
網頁快取不會，也無法事先讀取每個頁面，也無法預測使用者會翻頁到哪一個頁面！因此做好的「網頁快取功能」與「資料庫快取功能」，會導致「翻頁」功能完全失效！不能翻頁的話，就會導致只能顯示最後上傳的12張或是15張照片，無法顯示上傳的更多照片！最後只能放棄「網頁快取功能」與「資料庫快取功能」的做法。
在MySQL資料庫新增一個「複雜查詢」專用的VIEW表格
在MySQL資料庫用 CREATE VIEW bx_photos_home 指令新增一個「複雜查詢」專用的表格，或許可以提高MySQL的查詢速度，所以只要事先建立「複雜查詢」專用的一個 VIEW 專用的表格：
CREATE VIEW bx_photos_home AS SELECT `bx_photos_main`.`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`, `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 `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 `bx_photos_main`.`ID` DESC
就可以簡化查詢指令，變成這樣：
SELECT * FROM `bx_photos_home` LIMIT 0, 15
雖然SELECT指令變成非常的簡短，但是資料的搜尋時間不變！仍然要花0.8秒～1.7秒！使用CREATE VIEW指令新增的「bx_photos_home」表格並不是一個真實存在的表格，只是一個SELECT指令的「指標」，用來指向原本的資料表，所以就完全沒有任何加速查詢的效果。
新增相簿首頁專用的 bx_photos 資料表
SELECT指令搜尋速度很慢的主要問題是出在「使用InnoDB格式」以及使用JOIN合併多個表格指令（left、right、inner都一樣）。所以解決辦法只有3個：
- 新增一個相簿首頁專用的 bx_photos_home 資料表並增加更多欄位，使用InnoDB格式，避免使用太多的left JOIN合併表格指令。
- 新增一個相簿首頁專用的 bx_photos_home 資料表並增加更多欄位，只有這個資料表改用舊的MyISAM格式，其他都是使用InnoDB。
- 將相簿專用的相關表格，全部改回舊的MyISAM格式，其他都是使用InnoDB。
前面1、2的方法，因為增加新的資料表格，就必須大幅度修改相簿PHP程式碼，在新增、更新、取代舊有表格的時候，還必須同時管理新的bx_photos_home資料表。雖然這不是「不可能的任務」，但問題是海豚系統只維護到2023年為止，對於剩餘壽命只有3-4年的系統，大幅度修改PHP程式碼，似乎不是什麼好主意。
而且我決定要在2020年底以前，最晚2021年底以前，就把海豚系統轉移到新的UNA系統。對於即將完全棄用的Dolphin系統，似乎也不值得花時間，大幅度修改PHP程式碼。
第3個方法則是比較簡單，因為相簿首頁的資料庫搜尋時間需要0.8秒～1.7秒，都是因為把舊的MyISAM格式轉成InnoDB格式造成的，只要改回舊的MyISAM格式，不需要修改任何PHP程式碼，就不會有問題了。不過，我並不想要在一個資料庫裡面同時使用InnoDB與MyISAM，這說不定會導致MySQL有其他新的問題。
雖然不太想要改海豚的PHP程式碼，但我還是需要在UNA動手寫一個「可以上傳RAW檔案的相簿管理模組」，所以先在海豚測試一下我的「構想」也不是什麼壞事。
我認為搜尋速度異常緩慢，是因為bx_photos_main、sys_albums_objects資料筆數太多，而且一個SQL指令就使用「left JOIN」整合bx_photos_main、Profiles、sys_albums、sys_albums_objects這4個表格。如果可以簡化成只需要3個表格，或許搜尋速度就可以提升許多。
這是目前的sys_albums_objects相簿物件的結構：
- id_album：指向sys_albums相簿表格的相簿編號。
- id_object：指向bx_photos_main相片表格的相片編號。
- obj_order：指定一本相簿裡面的照片順序。
這是目前的bx_photos_main照片資料結構：
這是我自己新增的bx_photos表格，你可以看到我在資料表的最後新增了
- id_album：指向sys_albums相簿表格的相簿編號。
- id_object：指向bx_photos_main相片表格的ID相片編號。
- obj_order：指定一本相簿裡面的照片順序。
這麼做的話，就會不需要id_object欄位。因為我已經把後面兩個欄位併入到原本id_object需要指向到ID編號。
使用舊的bx_photos_main來搜尋相簿首頁的話，需要花1.2秒。
使用新的bx_photos來搜尋相簿首頁的話，竟然只需要0.0129秒！
1.2秒 / 0.012秒 = 100倍！
只是把sys_albums_objects資料表的資料全部合併到bx_photos_main表格，少了一個「left JOIN」指令，竟然就可以「加速100倍」！真是令人驚訝的成績！
所以我推測的沒錯，就是因為「left JOIN」太多表格，導致MySQL需要花很多時間來比對資料，浪費了許多搜尋時間，還不如把必須要使用的資料欄位，直接寫在bx_photos還比較簡單。
不過，這只是在phpMyAdmin直接執行SQL指令。實際上要把bx_photos_main改成新的bx_photos，並簡化「left JOIN」指令，並不是一件簡單的任務（雖然不是不可能的任務），需要修改的程式碼會非常多。因為bx_photos_main牽涉照片的上傳、刪除、更新、搜尋等。
改成搜尋2次或是多次資料庫，不使用JOIN合併2個以上的表格
MySQL搜尋時間需要0.8秒～1.7秒主要的問題是因為「使用InnoDB格式」以及使用JOIN合併多個表格指令。但不是所有使用JOIN合併多個表格的SELECT都非常緩慢！如果只有合併2-3個表格，而且表格內的資料筆數不是很多的話，搜尋速度並不輸給MyISAM。
由於絕對不可能減少「資料筆數」（一筆資料就相當於一張照片，或是討論區上的一篇話題），所以唯一可以簡化的只有「減少JOIN合併表格的數量」，盡量指合併一個表格，這樣搜尋速度應該就可以提升許多。
所以，我將最前面的「SELECT `bx_photos_main`」查詢指令切開，改成搜尋2次或是多次資料庫，而不使用太多的JOIN合併表格指令。首先是執行這個指令，找出相簿首頁的最新照片，這樣就只要使用一個left JOIN指令：
- SELECT `sys_albums_objects`.`id_object` as `ID` FROM `sys_albums_objects` left JOIN `sys_albums` ON `sys_albums`.`ID`=`sys_albums_objects`.`id_album` WHERE `sys_albums`.`AllowAlbumView` IN('3','4') ORDER BY `ID` DESC LIMIT 0, 15
然後再從找到的照片 `ID` 編號，再搜尋一次 bx_photos_main 資料表，找出照片的相關資料。這樣就也要使用一個left JOIN指令：
- SELECT `bx_photos_main`.`Title` as `title`, `bx_photos_main`.`Ext`, `bx_photos_main`.`Uri` as `uri`, `bx_photos_main`.`Hash`, `Profiles`.`NickName` as `ownerName` FROM `bx_photos_main`
left JOIN `Profiles` ON `Profiles`.`ID`=`bx_photos_main`.`Owner` WHERE `bx_photos_main`.`ID` = 161444 OR `bx_photos_main`.`ID` = 161443 OR `bx_photos_main`.`ID` = 161442 OR `bx_photos_main`.`ID` = 161441 OR `bx_photos_main`.`ID` = 161440 OR `bx_photos_main`.`ID` = 161439 OR `bx_photos_main`.`ID` = 161438 OR `bx_photos_main`.`ID` = 161437 OR `bx_photos_main`.`ID` = 161436 OR `bx_photos_main`.`ID` = 161435 OR `bx_photos_main`.`ID` = 161434 OR `bx_photos_main`.`ID` = 161433 OR `bx_photos_main`.`ID` = 161432 OR `bx_photos_main`.`ID` = 161431 OR `bx_photos_main`.`ID` = 161430 ORDER BY `bx_photos_main`.`ID` DESC
可能你會認為，每次都要使用一個left JOIN指令，分別執行2次SELECT指令，這樣仍舊執行了2次left JOIN指令，這樣會比原本需要3個left JOIN指令更快很多？當然，「電腦資訊科技」絕對不能靠「感覺」來判斷！所以我加上時間測試指令，來計算整個SELECT指令的執行時間，在「insoler研究開發室」的Server上進行測試結果是：
2019-04-23 09:17:43 沒有加速網站首頁與相簿首頁程式的執行時間：1.6177139282227
2019-04-23 09:17:48 沒有加速網站首頁與相簿首頁程式的執行時間：1.5764429569244
2019-04-23 09:17:52 沒有加速網站首頁與相簿首頁程式的執行時間：1.5372340679169
2019-04-23 09:18:09 沒有加速網站首頁與相簿首頁程式的執行時間：1.7231681346893
2019-04-23 09:18:13 沒有加速網站首頁與相簿首頁程式的執行時間：1.6988799571991
2019-04-23 09:18:46 沒有加速網站首頁與相簿首頁程式的執行時間：1.6412229537964
2019-04-23 09:18:50 沒有加速網站首頁與相簿首頁程式的執行時間：1.4737870693207
2019-04-23 09:18:53 沒有加速網站首頁與相簿首頁程式的執行時間：1.4556331634521
2019-04-23 09:18:57 沒有加速網站首頁與相簿首頁程式的執行時間：1.4563670158386
2019-04-23 09:19:01 沒有加速網站首頁與相簿首頁程式的執行時間：1.4143218994141
2019-04-23 09:19:04 沒有加速網站首頁與相簿首頁程式的執行時間：1.6973850727081
2019-04-23 09:19:07 沒有加速網站首頁與相簿首頁程式的執行時間：1.6969101428986
2019-04-23 09:19:11 沒有加速網站首頁與相簿首頁程式的執行時間：1.5679252147675
2019-04-23 09:19:15 沒有加速網站首頁與相簿首頁程式的執行時間：1.6833720207214
2019-04-23 09:36:12 「加速」網站首頁與相簿首頁程式的執行時間：0.12486600875854
2019-04-23 09:36:14 「加速」網站首頁與相簿首頁程式的執行時間：0.12098121643066
2019-04-23 09:36:15 「加速」網站首頁與相簿首頁程式的執行時間：0.12091994285583
2019-04-23 09:36:17 「加速」網站首頁與相簿首頁程式的執行時間：0.12104392051697
2019-04-23 09:36:18 「加速」網站首頁與相簿首頁程式的執行時間：0.12255597114563
2019-04-23 09:36:20 「加速」網站首頁與相簿首頁程式的執行時間：0.1209089756012
2019-04-23 09:36:21 「加速」網站首頁與相簿首頁程式的執行時間：0.11947703361511
2019-04-23 09:36:23 「加速」網站首頁與相簿首頁程式的執行時間：0.1207389831543
2019-04-23 09:36:24 「加速」網站首頁與相簿首頁程式的執行時間：0.12018299102783
2019-04-23 08:26:11 「加速」網站首頁與相簿首頁程式的執行時間：0.11700391769409
你可以看到從「1.6秒」可以變成「0.1秒」，速度提升了「16倍」！「insoler研究開發室」使用的是BNW網站的MySQL Server主機，規格是Mac mini (Late 2009) 2.53 GHz Intel Core 2 Duo（雙核心、雙執行緒）、8GB 1067 MHz DDR3。
換成insoler網站的Mac mini (Late 2012) 2.6 GHz Intel Core i7（4核心、8執行緒）16GB 1600 MHz DDR3，相同的測試結果是：
2019-04-21 09:09:23 沒有加速網站首頁與相簿首頁程式的執行時間：0.81141304969788
2019-04-21 09:09:26 沒有加速網站首頁與相簿首頁程式的執行時間：0.81882786750793
2019-04-21 09:09:30 沒有加速網站首頁與相簿首頁程式的執行時間：0.8127179145813
2019-04-21 09:09:33 沒有加速網站首頁與相簿首頁程式的執行時間：0.81554388999939
2019-04-21 09:09:36 沒有加速網站首頁與相簿首頁程式的執行時間：0.80767893791199
2019-04-21 09:09:39 沒有加速網站首頁與相簿首頁程式的執行時間：0.81506204605103
2019-04-21 09:09:42 沒有加速網站首頁與相簿首頁程式的執行時間：0.80956196784973
2019-04-21 09:13:17 沒有加速網站首頁與相簿首頁程式的執行時間：0.30862903594971
2019-04-21 09:13:43 沒有加速網站首頁與相簿首頁程式的執行時間：0.82060194015503
2019-04-21 09:13:46 沒有加速網站首頁與相簿首頁程式的執行時間：0.82371711730957
2019-04-21 09:13:49 沒有加速網站首頁與相簿首頁程式的執行時間：0.81804013252258
2019-04-21 09:13:53 沒有加速網站首頁與相簿首頁程式的執行時間：0.81246900558472
2019-04-21 09:13:57 沒有加速網站首頁與相簿首頁程式的執行時間：0.80978012084961
2019-04-21 09:13:59 沒有加速網站首頁與相簿首頁程式的執行時間：0.82573294639587
2019-04-21 09:14:03 沒有加速網站首頁與相簿首頁程式的執行時間：0.81916403770447
2019-04-21 09:14:06 沒有加速網站首頁與相簿首頁程式的執行時間：0.82866907119751
2019-04-21 09:14:10 沒有加速網站首頁與相簿首頁程式的執行時間：0.80913591384888
2019-04-21 08:37:27 「加速」網站首頁與相簿首頁程式的執行時間：0.00136399269104
2019-04-21 08:39:19 「加速」網站首頁與相簿首頁程式的執行時間：0.063804864883423
2019-04-21 08:39:56 「加速」網站首頁與相簿首頁程式的執行時間：0.001309871673584
2019-04-21 08:41:51 「加速」網站首頁與相簿首頁程式的執行時間：0.0017428398132324
2019-04-21 08:42:42 「加速」網站首頁與相簿首頁程式的執行時間：0.00146484375
2019-04-21 08:42:48 「加速」網站首頁與相簿首頁程式的執行時間：0.00152587890625
2019-04-21 08:45:17 「加速」網站首頁與相簿首頁程式的執行時間：0.0014181137084961
2019-04-21 08:46:08 「加速」網站首頁與相簿首頁程式的執行時間：0.0011720657348633
2019-04-21 08:46:39 「加速」網站首頁與相簿首頁程式的執行時間：0.0013248920440674
2019-04-21 08:47:28 「加速」網站首頁與相簿首頁程式的執行時間：0.0023069381713867
2019-04-21 08:48:49 「加速」網站首頁與相簿首頁程式的執行時間：0.0016000270843506
2019-04-21 08:49:25 「加速」網站首頁與相簿首頁程式的執行時間：0.0012190341949463
2019-04-21 08:49:45 「加速」網站首頁與相簿首頁程式的執行時間：0.0012869834899902
2019-04-21 08:51:13 「加速」網站首頁與相簿首頁程式的執行時間：0.0013191699981689
2019-04-21 08:51:50 「加速」網站首頁與相簿首頁程式的執行時間：0.0013809204101562
2019-04-21 08:52:13 「加速」網站首頁與相簿首頁程式的執行時間：0.0015089511871338
你可以看到「0.8秒 / 0.001秒 = 800倍」竟然可以從前面的「16倍」大幅提升到「800倍」！最慢也有「0.8秒 /0.076秒 = 10倍」！至少還可以低於0.1秒！這樣的效果當然是令人非常滿意！
因為我使用的資料庫是MySQL 5.6.41 版（目前最新的版本是MySQL 8.0.16），雖然版本有點老舊，但已經是5.6系列支援macOS的最後版本。從測試結果來看，很明顯，在MySQL 5.6.41上，使用一個left JOIN指令，就算執行2次SELECT指令，仍舊比一個SELECT同時使用3個left JOIN指令快非常多！
當然，網路上也有許多人留意到JOIN指令速度非常緩慢的問題：
inner join and order by - why is it so slow?
inner join vs left join - huge performance difference
Why is LEFT JOIN slower than INNER JOIN?
目前最新的版本是MySQL 8.0.16有沒有解決這個問題？如果新的MySQL可以同時使用3-5個JOIN指令，也仍舊可以保持與只用1個JOIN相同的速度，就不需要以上的這些解決方案，只要把MySQL從MySQL 5.6.41升級到MySQL 8.0.16即可。但是我實際升級測試以後，發現完全無解！
不過，MySQL 8.0.15安裝在macOS 10.14.4系統上，竟然無法正常執行，也無法匯入備份的資料庫，完全無法使用！
MySQL 8.0.16修正了非常多與InnoDB有關的Bug，問題應該比MySQL 8.0.15少很多。因此我決定正式升級到MySQL 8.0.16版本。但是在InnoDB同時使用3-5個JOIN指令，速度會變成非常緩慢，這個問題依舊無解！
唯一的辦法只能重新思考資料表的結構，盡量放在同一個表格，減少或是避免使用「left JOIN」指令。
MySQL 8新增的innodb_dedicated_server參數
在MySQL 8.0新增了一個叫做innodb_dedicated_server開關參數（預設是OFF）。只要在my.cnf的純文字設定檔，新增innodb_dedicated_server = ON，就可以再啟動MySQL Server的時候，由MySQL按照電腦主機上的RAM記憶體容量來決定innodb_buffer_pool_size、innodb_log_file_size這兩個參數的大小。並依照作業系統環境自動設定innodb_flush_method參數。
New MySQL 8.0 innodb_dedicated_server Variable Optimizes InnoDB
MySQL 8.0 introduces a new variable called innodb_dedicated_server. When enabled, it auto tunes innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method at startup (if these variables are not explicitly defined in my.cnf).
The new MySQL 8.0 variable automatically sizes the following variables based on the RAM size of the system:
innodb_buffer_pool_size:
<1G: 128M(default value if innodb_dedicated_server is OFF)
<=4G: Detected Physical RAM * 0.5
>4G: Detected Physical RAM * 0.75
innodb_log_file_size:
<1G: 48M(default value if innodb_dedicated_server is OFF)
<=4G: 128M
<=8G: 512M
<=16G: 1024M
>16G: 2G
The variable also sets the following:
innodb_flush_method:
Set to O_DIRECT_NO_FSYNC if the setting is available on the system. If not, set it to the default InnoDB flush method
These new default values are very reasonable, and the changes to these three variables show considerable performance improvements from the get-go than using the old default values. As stated in the worklog of this feature, the current MySQL version (5.7) only uses around 512M RAM with the default settings. With the new feature, these variables can easily adapt to the amount of RAM allocated to the server for the convenience of the system/database administrator.
With that said, you can achieve the best setting for these three variables by tuning it to your workload and hardware.
For InnoDB buffer pool size (based on this article), consider allocating 80% of physical RAM for starters. You can increase it to as large as needed and possible, as long as the system doesn’t swap on the production workload.
For InnoDB log file size, it should be able to handle one hour of writes to allow InnoDB to optimize writing the redo log to disk. You can calculate an estimate by following the steps here, which samples one minute worth of writes to the redo log. You could also get a better estimate from hourly log file usage with Percona Monitoring and Management (PMM) graphs.
Finally, for innodb_flush_method, O_DIRECT_NO_FSYNC prevents double buffering between the OS cache and disk, and works well with low-latency IO devices such as RAID subsystem with write cache. On the other hand, in high-latency IO devices, commonly found on deployments where MySQL is stored in SAN drives, having an OS cache with the default flush method fsync is more beneficial.
All in all, the MySQL 8.0 innodb_dedicated_server variable provides a fairly well-tuned InnoDB configuration at startup. But if it’s not enough, you can still tune these variables based on your workload and hardware. While MySQL 8.0 isn’t released yet, you can take a look at this article that helps you tune the current version (MySQL 5.7) right after installation.
由於Mac mini (Late 2012)最多只能安裝16 GB 1600 MHz DDR3記憶體。所以設定「innodb_dedicated_server = ON」並重新啟動MySQL以後，就可以在phpMyAdmin的「變數」頁面查到：
- innodb_buffer_pool_size：12GB
- innodb_log_file_size：1GB
- innodb_flush_method：fsync
雖然從前面的畫面上，我認為innodb_buffer_pool_size只要設定在2GB就非常夠用，設定在12GB也沒太大的意義，但還是決定使用「innodb_dedicated_server = ON」這個設定。
