Post view

insoler照片的EXIF資料表從舊版bnw_photo_exif更新為bx_photos_exif

 insoler相簿的照片EXIF模組使用的資料表名稱是bnw_photo_exif,專門負責儲存所有照片的EXIF資料。到目前為止,儲存了將近14萬張照片。資料表的大小大約是146.2MB。

其實bnw_photo_exif資料表裡面只有2個欄位,一個是med_id,用來儲存照片的流水編號。這個編號當然是對應到主要儲存照片資料的bx_photos_main編號。另外一個則是params參數欄位是儲存從PHP的「陣列變數」使用serialize函式轉換成「純文字字串」的格式,再儲存到params資料表裡。因此從資料表讀取parama欄位的文字資料以後,還要用PHP的unserialize函式把純文字轉換回「陣列變數」。

bnw_photo_exif資料表的「結構」非常簡單,只有一個int純整數的med_id欄位,以及一個text純文字的params欄位。使用的資料庫編碼格式是最新的utf8mb4_bin。

我花了很多時間,重新設計了一個新的bx_photos_exif資料表,再寫了兩個轉換程式,分別是負責前半部資料的bxPhotoExif1.php、以及負責後半部資料的bxPhotoExif2.php。因為一次要轉換將近14萬筆資料到新的資料庫,需要超過5分鐘以上的時間,為了避免Timeout錯誤,所以把14萬筆資料分為各別處理7萬筆資料。

另外還寫了兩個檢查程式,分別是bxPhotoExif_check1.php、bxPhotoExif_check2.php。這是用來比對前半部7萬筆資料的的「bnw_photo_exif資料表」與「bx_photos_exif資料表」內容的正確性。如果發現在原始的「bnw_photo_exif資料表」就有錯誤的EXIF資訊,還必須修正EXIF程式,更新舊的「bnw_photo_exif資料表」,再轉換到新的「bx_photos_exif資料表」,然後再次比對看看資料內容是否一致?

你可以看到重新轉換成功到新的「bx_photos_exif資料表」的資料筆數與主要的「bx_photos_main資料表」的資料筆數完全一致,都是138990筆資料。也就是每一張照片都會對應到一筆EXIF資料。舊的「bnw_photo_exif資料表」只儲存有EXIF的照片,如果上傳的是視窗螢幕圖片,或是故意刪除EXIF的照片,就不會儲存在「bnw_photo_exif資料表」,所以會造成資料筆數不同。資料筆數不同也會造成資料維護上的困擾。

另外,你可以看到新的「bx_photos_exif資料表」的大小是49.4MB,比舊的「bnw_photo_exif資料表」大小是146.2MB,大約是3.3倍!換句話說,新的資料表大小縮小了3.3倍!

雖然新的資料表縮小了3.3倍,不過新的「bx_photos_exif資料表」總共有91個欄位,比舊的只有2個欄位,可說是複雜很多!

順便說明一下,目前insoler使用的資料庫是MySQL Community Server 5.6.37,這個版本在OS X 10.11.6,已經可以非常穩定的運作!到目前為止,已經連續運作8天,而且完全沒有任何的「嘗試失敗」錯誤,以及「中斷連線」錯誤!你可以看到錯誤次數都是0!

自 2017 年 09 月 19 日 20:07 開始,此 MySQL 伺服器已經執行了 8 天 5 小時,9 分 22 秒。

流量  # ø 每小時
已接收 2.3 GB 11.8 MB
已發送 12.2 GB 63.4 MB
總計 14.5 GB 75.2 MB
連線 # ø 每小時 %
最大同時連線數 24 ---  --- 
嘗試失敗 0 0 0%
中斷連線 0 0 0%
總計 270 k 1,371.62  100.00%

雖然花了很多時間,從舊的「bnw_photo_exif資料表」轉換到新的「bx_photos_exif資料表」,當然還要修改相關的bx_photos_exif資料庫處理PHP程式碼,以及EXIF程式碼。但是花了這麼多的精神、時間,到底有多少效益?

沒有實際上的「效能測試」只靠「經驗」與「理順上」是絕對不行的!所以我又寫了一個效能比較測試程式,計算從舊的「bnw_photo_exif資料表」讀取500筆資料需要多少時間?以及計算從新的「bx_photos_exif資料表」同樣讀取500筆資料需要多少時間?

你可以看到很無奈的是,新的「bx_photos_exif資料表」讀取500筆資料的速度幾乎比舊的慢一倍左右!新的資料表需要0.4秒,舊的資料表竟然只要0.22秒左右!sigh

再測試不同的資料筆數,而且重複測試幾次,在MySQL有快取資料表內容的情況下,新的資料表會更快一些,只需要0.31秒左右,舊的資料表還是要0.21秒左右!

為什麼新的資料表會比舊的資料表還要更慢?undecided我猜想應該是新的資料表有「91個欄位」,但是舊的卻只有「2個欄位」。雖然「2個欄位」的資料內容大小是新的3.3倍大,但是MySQL不需要複雜的運算,只要直接讀出3.3倍大的資料即可,所以就算檔案大小更大3.3倍,還是比多達「91個欄位」的資料表快很多。

從 110001到 110500、讀取 bnw_photo_exif 資料表所需時間:0.24151301383972
從 110001到 110500、讀取 bx_photos_exif 資料表所需時間:0.33329319953918

新的比舊的資料表的處理速度還要更慢一倍左右,到底是要繼續使用「舊的」?還是要改用「新的」?undecided我想應該是沒有人會想要選「更慢」「效能更差」的方式。

但是,這是測試一次讀取500筆資料的結果,如果每次只有讀取1筆資料的話,效能的差異就不會這麼明顯。另外,新的「bx_photos_exif資料表」因為每一個EXIF項目都對應到一個資料欄位,所以日後要查詢某一個欄位資料的時候,會比舊版只有「純文字」會更簡單,更快。例如,查詢使用某一台相機拍攝的所有照片。例如點選EXIF資訊裡面的「Canon EOS 5D」的相機,就可以找到使用這台相機拍攝的照片。不過,目前insoler沒有這種搜尋功能,我還要花很多時間研究、思考,要如何做出這種功能?

如果繼續使用「舊的資料表」,因為整個EXIF是儲存在一個純文字欄位裡面,就不可能完成這種功能,勢必還是要改成新的EXIF資料表。

蘇言霖 2017/09/28 2 976
Comments
Order by: 
Per page:
 
Rate
0 votes
Post info
蘇言霖
「超級懶貓級」社群網站站長
2017/09/28 (2631 days ago)
Actions