Post view

insoler社群網站正式升級到MySQL 8.0.16,資料搜尋速度更快將近2倍?1.5倍?

在MySQL網站的首頁可以看到MySQL 8.0的主打重點就是比「速度比MySQL 5.7更快2倍」注意MySQL並沒有5.8、6.0、7.0版本而是直接從5.7跳到8.0版就像Windows也沒有Windows 9而是從7、8就直接跳到Windows 10。在改進項目部分有提到3個重要的改善

  • Improved! Up to 2x faster and sets new benchmarks records with upto 1.8 Million Queries/Second.
  • Improved! OpenSSL as the default TLS/SSL library in MySQL.
  • Improved! Performance Schema with queries that are up to 30x faster.
  • Improved! Information Schema with queries that are up to 100x faster.

這裡面主要是「改進速度提高2倍並創下新的效能測試記錄在每秒多達180萬個查詢」。至於Performance Schema快30倍、Information Schema快100倍就不是很重要。

MySQL 8.0真的能像一台F1方程式賽車那樣超級飛快我想還是要實際安裝測試以後才能知道。

從這個我自己寫的「MySQL Server資料庫伺服器狀態」的PHP程式碼你可以看到insoler網站目前已經是使用最新的MySQL 8.0.16版。而且按照慣例每次進行insoler首頁、相簿首頁的benchmarks測試都重新啟動MySQL以避免MySQL內建的cache快取功能影響benchmarks測試的時間。

MySQL 8.0.16版相簿首頁的SELECT搜尋時間是0.56秒

我使用的是Dolphin 7.4.2海豚系統的相簿首頁標準的SELECT資料搜尋指令重新啟動MySQL以後就直接在phpMyAdmin網頁上執行這個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

你可以看到「查詢用了0.5633秒」的結果。

大約是「0.56秒」的查詢速度,對許多超大型網站而言,應該是非常的龜速!那些超大型網站,甚至很可能不允許一個簡單的SELECT資料搜尋指令的執行時間超過0.005秒。

但是「0.56秒」畢竟是MySQL 8.0.16的執行時間。如果換成MySQL 5.6.41(我是從這個版本,略過更龜速的5.7版,直接從5.6.41升級到8.0.16)的話,這個速度到底是更快?還是更慢?

請參考這篇文章:

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

在MySQL 5.6.41執行前面完全相同的SELECT資料搜尋指令,執行速度是「0.86秒」。確實是:

0.86秒 / 0.56秒 = 1.53倍

雖然還不到MySQL宣稱的更快2倍(這應該是在某種SELECT的搜尋條件下,但是MySQL並沒有公佈測試方式),但至少可以比MySQL 5.6.41更快「1.5倍」!我相信絕對可以比更龜速的MySQL 5.7還要更快2倍以上!

雖然更快「1.5倍」令人相當滿意。不過這是使用InnoDB儲存引擎的情況。如果是舊的MyISAM儲存引擎,事實上執行完全相同的SELECT資料搜尋指令,只需要「0.016秒」!而且這還是在MySQL 5.6.41版本上。

如果換成是MySQL 8.0.16搭配舊的MyISAM儲存引擎,事實上絕對不會「更快2倍」反而還會變得更慢!雖然我沒有在MySQL 8.0.16測試過,但已經在之前的MySQL 8.0版上測試過。

從MySQL 5.6.14的MyISAM的「0.016秒」變成MySQL 8.0.16的InnoDB的「0.56秒」,我想大概很多人都還是會選擇移除MySQL 8.0.16,重新安裝老舊版本還比較好!

不過,早在MySQL 4.1.5版InnoDB就成為預設的儲存引擎,應該是有許多理由。雖然MySQL 8.0.16還是支援MyISAM但並不表示速度快的MyISAM就比InnoDB優異。

為什麼要升級到MySQL 8.0.16最新版本?

雖然有許多個人網站、小型網站,到現在都還在使用10幾年前的PHP 4、HTML 4與恐龍時代的老舊CMS系統。但是對一個「社群網站」來說(雖然insoelr是超級奈米級的微小網站)我絕對不可能不升級系統,一直使用老舊的系統版本。雖然過時的老舊版本,不見得就會有容易被駭客入侵的安全性漏洞,但是過時的版本就表示因為功能不符合現代的需求(例如支援智慧手機的App)而早就被時代淘汰。

因此我升級到目前最新的macOS Mojave 10.14.4、PHP 7.1.23(這是macOS內建的PHP版本,穩定性比PHP 7.2、PHP 7.3更好),以及最新的MySQL 8.0.16。

不升級到MySQL 8.0.15以前的版本,是因為MySQL 8.0.15有非常多的問題!無法正常啟動MySQL,也無法穩定的運作。甚至在MySQL 8.0.16修正了多達35項關於InnoDB的問題!

Bugs Fixed

  • InnoDB: Undo tablespaces remained unencrypted after enabling undo tablespace encryption at startup. (Bug #29477795)

  • InnoDB: Problematic macros introduced with undo tablespace DDL support in MySQL 8.0.14 were revised. (Bug #29324132, Bug #94243)

  • InnoDB: Static thread local variables defined at the wrong scope were not released at thread exit. (Bug #29305186)

  • InnoDB: The performance_schema.data_locks LOCK_DATA column only showed the secondary index values of the locked record for a lock placed on a unique secondary index, which was not sufficient to ensure the uniqueness of identified records. The clustered index column values of the locked record are now appended. (Bug #29296645)

  • InnoDB: An incorrect count of transactions using a rollback segment for recovery of an XA transaction prevented an undo tablespace truncation operation from proceeding and left the purge thread busy checking for the undo tablespace to become empty. (Bug #29273194)

    References: This issue is a regression of: Bug #29273194.

  • InnoDB: Invalid assertions were raised at startup after a failure to retrieve the space ID of a compressed file-per-table tablespace. The invalid assertion code was removed. (Bug #29221385, Bug #93760)

  • InnoDB: Optimized InnoDB internal temporary tables did not support in-place UPDATE operations, which caused the number of delete-marked records to increase continuously. The large number of delete-marked records could cause longer than expected query execution times. (Bug #29207450)

  • InnoDB: The std::sort function in the Variance-Aware Transaction Scheduling (VATS) algorithm was replaced by the std::stable_sortfunction to preserve the original FIFO order for transactions of equal weight. (Bug #29058967)

  • InnoDB: Write-ahead did not work as expected due to an incorrectly initialized variable.

    Thanks to Yuhui Wang for the contribution. (Bug #29028838, Bug #93442)

  • InnoDB: The base column information for a generated column was not stored. (Bug #29021730)

  • InnoDB: An implicit lock check on secondary indexes needlessly compared columns using collation rules. (Bug #29010725)

  • InnoDB: Assertion code related to the innodb_flush_method O_DIRECT_NO_FSYNC setting was no longer valid due to a recent modification to that setting. Assertion code was revised. (Bug #29007731)

    References: See also: Bug #27309336.

  • InnoDB: When starting the server with undo log encryption enabled, the master key for newly created undo tablespaces was generated without a server UUID. Undo tablespaces should use the DefaultMasterKey if the server UUID is not yet generated. (Bug #29006275)

  • InnoDB: Data dictionary code did not check for a returned data dictionary object, which could potentially cause the server to exit due to a null pointer access. (Bug #28977444, Bug #93362)

  • InnoDB: An undo tablespace file was left behind by a failed CREATE UNDO TABLESPACE operation. (Bug #28966457)

  • InnoDB: A CREATE UNDO TABLESPACE statement failed on Windows due to an invalid character in the file name. The failure resulted in a hang condition due to a missing OS_FILE_ON_ERROR_NO_EXIT attribute in the call that creates the undo tablespace file. (Bug #28955676)

  • InnoDB: Modifying the value of the innodb_undo_log_encrypt variable was not a blocking operation, which could lead to the modification being reverted by a background thread after the operation appeared to have been completed successfully. (Bug #28952870)

  • InnoDB: An invalid debug assertion was removed from the temptable::Handler::primary_key_is_clustered function. (Bug #28949332)

  • InnoDB: An ALTER TABLE ... EXCHANGE PARTITION operation did not properly update column table_id values in the data dictionary. (Bug #28927005)

  • InnoDB: Memory leaks discovered in the innochecksum utility were removed. (Bug #28917614, Bug #93164)

  • InnoDB: A DDL operation that followed a failed attempt to create an index on a virtual column resulted in an assertion failure. (Bug #28825718)

  • InnoDB: A performance regression was observed for partial update operations on compressed BLOBs less than or equal to 128KB in size. (Bug #28784301)

  • InnoDB: Running aggregated queries raised Valgrind warnings. (Bug #28711717)

  • InnoDB: A CHECK TABLE operation raised an assertion failure. A pointer to a local call stack variable was not set back to null before a function exit. (Bug #28525110)

  • InnoDB: DDL log functions were modified to handle ER_TOO_MANY_CONCURRENT_TRXS errors. (Bug #28523127, Bug #92071)

  • InnoDB: The purge thread failed to free LOB data pages. (Bug #28510599)

  • InnoDB: Some DDL log table transactions were not rolled back prior to DDL log recovery. (Bug #28494969)

  • InnoDB: A function invoked during SHOW CREATE TRIGGER processing that retrieves the table name did not perform the expected lowercase conversion. (Bug #28351038)

  • InnoDB: The INFORMATION_SCHEMA.INNODB_FOREIGN TYPE column reported incorrect values. (Bug #28315651, Bug #91577)

  • InnoDB: A Linux AIO handler function failed to check if completed I/O events succeeded. Thanks to Wei Zhao for the contribution. (Bug #27850600, Bug #90402)

  • InnoDB: An assertion failure was raised in a check that determines if a transaction holds an implicit lock on a secondary index. A transaction that does not change the columns of a secondary index that includes virtual columns could be incorrectly determined to hold an implicit lock. (Bug #27491839)

  • InnoDB: A function called by a CREATE TABLE thread attempted to access a table object after it was freed by a background thread.

    Thanks to Yan Huang for the patch. (Bug #27373959, Bug #89126)

  • InnoDB: Two sessions concurrently executing an INSERT ... ON DUPLICATE KEY UPDATE operation generated a deadlock. During partial rollback of a tuple, another session could update it. The fix for this bug reverts fixes for Bug #11758237, Bug #17604730, and Bug #20040791. (Bug #25966845)

  • InnoDB: When the method used to access a joined table was const, InnoDB attempted to unlock the matching row multiple times. (Bug #20939184)

  • InnoDB: The INDEX_LENGTH value in INFORMATION_SCHEMA.TABLES was not updated when adding an index. (Bug #19811005)

從MySQL 4.1.5開始,InnoDB成為預設的儲存引擎

為什麼MySQL 8.0.16修正了多達35項InnoDB相關的Bug錯誤?難道InnoDB是「新功能」的關係?其實完全不是!在MySQL官方網站的參考手冊裡面有提到:

MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 13.2 The InnoDB Storage Engine

Starting from MySQL 4.1.5, the improved Windows installer makes InnoDB the MySQL default storage engine on Windows.

從MySQL 4.1.5開始,改進Windows安裝程式,使InnoDB成為Windows的MySQL預設儲存引擎。而且在Wiki也有提到:

InnoDB is a storage engine for the database management system MySQL. MySQL 5.5, December 2010, and later use it by default replacing MyISAM.

InnoDB是MySQL資料庫管理系統的儲存引擎。在2010年12月的MySQL 5.5以後使用InnoDB替換MyISAM。

現在都已經是2019年。想不到經過9年以後,MySQL竟然連一個老舊的InnoDB儲存引擎都還有多達35個以上的Bug!我想在幾個月(很可能是6個月以上)預定要發表的MySQL 8.0.17、MySQL 8.0.18還會修正許多InnoDB的問題!

感覺上就很像明明多年來都沒有新功能的Adobe Flash Player卻不斷修正各種詭異的Bug,永遠修補不完,最後乾脆放棄、廢除Flash Player!

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