MySQL 8.0 新特性梳理彙總

一 歷史版本發佈回顧

從上圖可以看出，基本遵循 5+3+3 模式

5---GA發佈後，5年 就停止通用常規的更新了（功能不再更新了）；

3---企業版的，+3年功能不再更新了；

3 ---完全停止更新了。

總之，一句話， 已經到了升級的時候了。

二 InnoDB增強

2.1自增列

官方定義

當前最大自動遞增計數器值每次寫入重做日誌時更改、並保存到每個檢查點上的引擎專用系統表中。這些更改使當前最大自動增量計數器值在伺服器重新啟動時保持不變。

數據類型--auto_increment。

新特性：

自增列方面，現在自增列計數器會在每次值修改時，將值寫到redo_log中，並且在checkpoint時寫到存儲引擎私有的系統表中。這樣就消除了以往重啟實例自增列不連續的問題。

2.2 索引損壞

官方定義

當遇到索引樹損壞時，InnoDB會在redo日誌中寫入一個損壞標誌，這會使損壞標誌安全崩潰。InnoDB還將記憶體損壞標誌數據寫入每個檢查點的私有系統表中。

在恢復的過程中，InnoDB會從這兩個位置讀取損壞標誌，併合並結果，然後將記憶體中的表和索引對象標記為損壞。

含義

InnoDB會向redolog中寫入一個損壞標誌。同時也會 checkpoint時將記憶體中損壞頁的數據記錄到存儲引擎私有的系統表中。

如果涉及數據修複，會將兩個地方的結果進行合併，確保損壞的索引不再恢復，保障了數據的已執行。

降低了之前使用innodb_force_recovery 和innodb_fast_shutdown的必要。

2.3 InnoDB memcached插件

官方定義

InnoDB memcached插件支持多個get操作（在一個memcached查詢中獲取多個鍵值對）和範圍查詢。

實現

安裝daemon_memcached插件;

在innodb_memcache schema 中，一張containers表用來與innodb表之間做映射。

優勢

減少客戶端和伺服器之間的通信流量，在單個memcached查詢中獲取多個鍵、值對的功能可以提高讀取性能。

更少的事務和開放式表操作。

2.4 死鎖檢測

A new dynamic variable, innodb_deadlock_detect, may be used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

2.5 數據字典

官方定義

MySQL 現在合併了一個事務數據字典，它存儲有關資料庫對象的信息。在以前的MySQL本版中，字典數據存儲在元數據文件和非事務表中。

數據字典的定義

數據字典是指對數據的數據項、數據結構、數據流、數據存儲、處理邏輯等進行定義和描述，其目的是對數據流程圖中的各個元素做出詳細的說明，使用數據字典為簡單的建模項目。簡而言之，數據字典是描述數據的信息集合，是對系統中使用的所有數據元素的定義的集合。

老版本

在之前的版本中，字典數據存儲在元數據文件和非事務表中。（存儲在.frm文件中）

8.0版本

數據字典以InnoDB表存儲字典數據，位於mysql資料庫下，對外不可見。.frm文件也不存在了。

好處

（1）統一集中存儲字典數據，方便了系統管理；

（2）移除了基於文件的元數據存儲；

（3）數據字典的事務性，安全性；

（4）字典對象的集中和統一的緩存；

（5）一些 information_schema中表的更簡單和改進的實現。

2.6 原子DDL

InnoDB表的DDL支持事務完整性，要麼成功要麼回滾，將DDL操作回滾日誌寫入到data dictionary 數據字典表 mysql.innodb_ddl_log 中用於回滾操作，該表是隱藏的表，通過show tables無法看到。通過設置參數，可將ddl操作日誌列印輸出到mysql錯誤日誌中。

mysql> set global log_error_verbosity=3;
mysql> set global innodb_print_ddl_logs=1;

2.7 redo優化

mysql8.0一個新特性就是redo log提交的無鎖化。在8.0以前，各個用戶線程都是通過互斥量競爭，串列的寫log buffer，因此能保證lsn的順序無間隔增長。

mysql8.0通過redo log無鎖化，解決了用戶線程寫redo log時競爭鎖帶來的性能影響。同時將redo log寫文件、redo log刷盤從用戶線程中剝離出來，抽成單獨的線程，用戶線程只負責將redo log寫入到log buffer，不再關心redo log的落盤細節，只需等待log_writer線程或log_flusher線程的通知。

更多內容那個可以參考: https://www.h5w3.com/231084.html

2.8 instant特性（Online DDL ）

As of MySQL 8.0.12, ALGORITHM=INSTANT is supported for the following ALTER TABLE operations:

• Adding a column. This feature is also referred to as “Instant ADD COLUMN”. Limitations apply.

• Adding or dropping a virtual column.

• Adding or dropping a column default value.

• Modifying the definition of an ENUM or SET column.

• Changing the index type. • Renaming a table.

instant 的好處

Operations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. No metadata locks are taken on the table, and table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by operations that support it. If ALGORITHM=INSTANT is specified but not supported, the operation fails immediately with an error.

需要註意的是

Prior to MySQL 8.0.29, a column can only be added as the last column of the table. Adding a column to any other position among other columns is not supported. From MySQL 8.0.29, an instantly added column can be added to any position in the table.

三 性能增強

3.1 hash join 增強

MySQL 8.0.23 reimplements the hash table used for hash joins, resulting in several improvements in hash join performance.

The new hash table is generally faster than the old one, and uses less memory for alignment, keys/values, and in scenarios where there are many equal keys. In addition, the server can now free old memory when the size of the hash table increases.

3.2 anti join 優化

MySQL 8.0.17版本引入了一個antijoin的優化，這個優化能夠將where條件中的not in(subquery)， not exists(subquery)，in(subquery) is not true，exists(subquery) is not true，在內部轉化成一個antijoin(反連接），以便移除裡面的子查詢subquery，這個優化在某些場景下，能夠將性能提升20%左右。

antijoin適用的場景案例通常如下：

*** 找出在集合A且不在集合B中的數據

*** 找出在當前季度里沒有購買商品的客戶

*** 找出今年沒有通過考試的學生

*** 找出過去3年，某個醫生的病人中沒有進行醫學檢查的部分

原文地址；

https://mytecdb.com/blogDetail.php?id=108

3.3 直方圖

優化器會利用column_statistics的數據，判斷欄位的值的分佈，得到更準確的執行計劃。

可以通過ANALYZE TABLE table_name [UPDATE HISTOGRAM on colume_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 來收集或者刪除直方圖信息。

直方圖統計了表中某些欄位的數據分佈情況，為優化選擇高效的執行計劃提供參考，直方圖與索引有著本質的區別，維護一個索引有代價。每一次的insert、update、delete都需要更新索引，會對性能有一定的影響。而直方圖一次創建永不更新，除非明確去更新它，因此不會影響insert、update、delete的性能。

3.4 倒序索引

MySQL now supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others. 

3.5 不可見索引

在 MySQL 8.0 中，索引可以被“隱藏”和“顯示”。

當對索引進行隱藏時，它不會被查詢優化器所使用。我們可以使用這個特性用於性能調試，例如我們先隱藏一個索引，然後觀察其對資料庫的影響。如果資料庫性能有所下降，說明這個索引是有用的，然後將其“恢復顯示”即可；如果資料庫性能看不出變化，說明這個索引是多餘的，可以考慮刪掉。

使用INVISIBLE關鍵字在創建表或者進行表變更中設置索引是否可見。索引不可見只是在查詢時優化器不使用該索引，即使使用force index，優化器也不會使用該索引，同時優化器也不會報索引不存在的錯誤，因為索引仍然真實存在。

3.6 多值索引

Beginning with MySQL 8.0.17, InnoDB supports the creation of a multi-valued index, which is a secondary index defined on a JSON column that stores an array of values and which can have multiple index records for a single data record. Such an index uses a key part definition such as CAST(data->'$.zipcode' AS UNSIGNED ARRAY). A multi-valued index is used automatically by the MySQL optimizer for suitable queries, as can be viewed in the output of EXPLAIN.

3.7 函數索引

MySQL 8.0.13 以及更高版本支持函數索引（functional key parts），也就是將表達式的值作為索引的內容，而不是列值或列值首碼。 將函數作為索引鍵可以用於索引那些沒有在表中直接存儲的內容。

其實MySQL5.7中推出了虛擬列的功能，而MySQL8.0的函數索引也是依據虛擬列來實現的。

• 只有那些能夠用於計算列的函數才能夠用於創建函數索引。
• 函數索引中不允許使用子查詢、參數、變數、存儲函數以及自定義函數。
• SPATIAL 索引和 FULLTEXT 索引不支持函數索引。

3.8 關閉QC（Query Cache ）

具體原因和效果可參照:https://blog.csdn.net/db_murphy/article/details/117392718

 四 優化器增強

4.1 Cost Model改進

優化器能夠感知到頁是否存在緩衝池中。5.7其實已經開放介面，但是不對記憶體中的頁進行統計，返回都是1.0.

4.2 可伸縮的讀寫負載 Scaling Read/Write Workloads

8.0版本對於讀寫皆有和高寫負載的拿捏恰到好處。在集中的讀寫均有的負載情況下，我們觀測到在4個用戶併發的情況下，對於高負載，和5.7版本相比有著兩倍性能的提高。在5.7上我們顯著了提高了只讀情況下的性能，8.0則顯著提高了讀寫負載的可擴展性。為MySQL提升了硬體性能的利用率，其改進是基於重新設計了InnoDB寫入Redo日誌的方法。對比之前用戶線程之前互相爭搶著寫入其數據變更，在新的Redo日誌解決方案中，現在Re'do日誌由於其寫入和刷緩存的操作都有專用的線程來處理。用戶線程之間不在持有Redo寫入相關的鎖，整個Redo處理過程都是時間驅動。

8.0版本允許馬力全開的使用存儲設備，比如使用英特爾奧騰快閃記憶體盤的時候，我們可以在IO敏感的負載情況下獲得1百萬的採樣 QPS（這裡說的IO敏感是指不在IBP中，且必須從二級存儲設備中獲取）。這個改觀是由於我們擺脫了 file_system_mutex全局鎖的爭用。

4.3 在高爭用（熱點數據）負載情況下的更優性能 Better Performance upon High Contention Loads (“hot rows”)

8.0版本顯著地提升了高爭用負載下的性能。高爭用負載通常發生在許多事務爭用同一行數據的鎖，導致了事務等待隊列的產生。在實際情景中，負載並不是平穩的，負載可能在特定的時間內爆發（80/20法則）。8.0版本針對短時間的爆發負載無論在每秒處理的事務數（換句話，延遲）還是95%延遲上都處理的更好。對於終端用戶來說體現在更好的硬體資源利用率（效率）上。因為系統需要儘量使用榨盡硬體性能，才可以提供更高的平均負載。

五 安全性增強

安全以及賬戶管理

5.1 認值加密插件

老版本：認證方式為sha256_password

8.0 版本：在老版本的基礎上，新增caching_sha2_password,可以使用緩存解決連接時的延時問題。

需要註意的問題是：如果客戶端與服務端配置不同，無法進行連接，兩者的加密認證方式需要一樣。

5.2 用戶密碼增強

（1）密碼的重覆使用策略

 密碼次數：無法從指定數量的最近密碼中選擇新密碼。如果密碼更改的最小數量設置為5，則新密碼不能與最近最新的5個密碼相同。

 時間間隔： 無法從歷史記錄中比指定天數更新的密碼中選擇新密碼。例如，密碼重用間隔設置為90，則新密碼不能和過去90天內的密碼相同。

（2）修改密碼必要的驗證策略

修改密碼，要輸入當前的密碼。增加了用戶的安全性。

（3）雙密碼

相比於一個用戶只有一個密碼最大優點就是:修改密碼不會導致應用不可用。那麼應用就可以自動使用副密碼（副密碼和當前密碼保持一致）連接資料庫庫。確保了業務的不中斷。修改密碼不會導致應用不可用；應用就可以自動使用副密碼連接資料庫。

5.3 角色功能

MySQL角色是指定許可權集合。像用戶賬戶一樣，角色可以擁有授予和撤銷的許可權。

可以授予用戶賬戶角色，授予該賬戶與每個角色相關的許可權。

方便了用戶許可權管理和維護。很好地解決了多個用戶使用相同的許可權集。許可權--》角色--》用戶。

5.4 redo & undo 日誌加密

增加以下兩個參數，用於控制redo、undo日誌的加密。
innodb_undo_log_encrypt
innodb_undo_log_encrypt

六 功能性增強

6.1 EXPLAIN ANALYZE

Explain 是我們常用的查詢分析工具，可以對查詢語句的執行方式進行評估，給出很多有用的線索。但他僅僅是評估，不是實際的執行情況，比如結果中的 rows，可能和實際結果相差甚大。

Explain Analyze 是 MySQL 8 中提供的新工具,可貴之處在於可以給出實際執行情況。Explain Analyze 是一個查詢性能分析工具，可以詳細的顯示出 查詢語句執行過程中，都在哪兒花費了多少時間。Explain Analyze 會做出查詢計劃，並且會實際執行，以測量出查詢計劃中各個關鍵點的實際指標，例如耗時、條數，最後詳細的列印出來。

6.2 CTE --公用表達式Common Table Expression

MySQL 8.0 帶來了支持遞歸的公用表達式的功能。非遞歸的公用表達式由於允許由form子句派生的臨時表的原因可以被多次引用，因而被解釋為改進型的派生表（from子句中的臨時表）。而遞歸的公用表達式則由一組原始住居，經過處理後得到新的一組數據，再被帶入處理得到更多的新數據，迴圈往複直到再也無法產生更多新數據為止。公用表達式也是一個用戶呼聲頻繁的SQL功能。

CTE(Common Table Expression)可以認為是派生表(derived table)的替代，在一定程度上，CTE簡化了複雜的join查詢和子查詢，提高了SQL的可讀性和執行性能。

CTE優勢

• 查詢語句的可讀性更好
• 在一個查詢中，可以被引用多次
• 能夠鏈接多個CTE
• 能夠創建遞歸查詢
• 能夠提高SQL執行性能
• 能夠有效地替代視圖

原文地址:

https://mytecdb.com/blogDetail.php?id=75

6.3 視窗函數（Window Functions）

從 MySQL 8.0 開始，新增了一個叫視窗函數的概念。

它可以用來實現若幹新的查詢方式。視窗函數與 SUM()、COUNT() 這種聚合函數類似，但它不會將多行查詢結果合併為一行，而是將結果放回多行當中。即視窗函數不需要 GROUP BY。

在MySQL 5.7中欄位名為rank是可以的，但是在8.0中因為有了視窗函數，欄位名為rank就報錯，順著這個思路，其實我們一窺視窗函數

 其實就會發現不光是rank,欄位名是first_value也不可以了，隨之帶來的就是SQL語法錯誤，可能會讓人開始有點抓不著頭腦。

create table test3(id int primary key,first_value varchar(30));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual  that corresponds to your MySQL server version for the right syntax to use near  'first_value varchar(30))' at line 1

6.4 clone plugin

MySQL 8.0 新增了一個clone插件，能夠實現本地或者遠程MySQL實例快速克隆。克隆的數據是InnoDB引擎的快照數據，包括資料庫(schema)，表(tables)，表空間(tablespaces)，數據字典元數據(data dictionary metadata)。克隆的數據是一個功能齊全的數據目錄，使用該目錄能夠快速添加從節點或者組複製節點。

從遠程MySQL實例克隆數據時，遠程的MySQL實例稱之為donor，它是數據提供者，是克隆的數據源。本地克隆數據的接收者，稱之為recipient。克隆的數據通過網路，從donor傳輸給recipient。recipient本地的數據預設會被清除，當然也可以為recipient指定一個不同的目錄用於存儲克隆數據。

克隆插件除了克隆數據之外，還支持搭建複製。克隆操作能夠獲取複製的坐標點，在克隆完成之後，利用複製的坐標點信息，能夠很方便的搭建主從複製，或者增加組複製節點。克隆插件支持表數據加密和壓縮，在使用clone插件之前，需要安裝插件。

原文地址；

https://mytecdb.com/blogDetail.php?id=173

6.5 ReplicaSet

InnoDB ReplicaSet 由一個主節點和多個從節點構成. 可以使用MySQL Shell的ReplicaSet對象和AdminAPI操作管理複製集, 例如檢查InnoDB複製集的狀態, 併在發生故障時手動故障轉移到新的主伺服器.

ReplicaSet 所有的節點必須基於GTID，並且數據複製採用非同步的方式。使用複製集還可以接管既有的主從複製，但是需要註意，一旦被接管，只能通過AdminAPI對其進行管理。

6.6 備份鎖

在MySQL 8.0中，引入了一個輕量級的備份鎖，這個鎖可以保證備份一致性，而且阻塞的操作相對比較少，是一個非常重要的新特性。

在MySQL 8.0中，為瞭解決備份FTWRL的問題，引入了輕量級的備份鎖；可以通過LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE，以獲取和釋放備份鎖，執行該語句需要BACKUP_ADMIN許可權。

backup lock不會阻塞讀寫操作。不過，backup lock會阻塞大部分DDL操作，包括創建/刪除表、加/減欄位、增/刪索引、optimize/analyze/repair table等。

總的來說，備份鎖還是非常實用的，畢竟其不會影響業務的正常讀寫；至於備份鎖和DDL操作的衝突，還是有很多方法可以避免，比如錯開備份和變更的時間、通過pt-online-schema-change/gh-ost避免長時間阻塞等等。隨著備份鎖的引入，Oracle官方備份工具MEB 8.0和Percona開源備份工具XtraBackup 8.0，也是更新了對backup lock的支持。

原文地址:

https://cloud.tencent.com/developer/article/1747961

6.7 Binlog增強（例如更新Json欄位，只記錄部分內容）

MySQL 8.0.20 版本增加了binlog日誌事務壓縮功能，將事務信息使用zstd演算法進行壓縮，然後再寫入binlog日誌文件，這種被壓縮後的事務信息，在binlog中對應為一個新的event類型，叫做Transaction_payload_event。

原文地址：

https://mytecdb.com/blogDetail.php?id=131

6.8 預設字元集由latin1變為utf8mb4

6.9 新增innodb_dedicated_server參數

能夠讓InnoDB根據伺服器上檢測到的記憶體大小自動配置innodb_buffer_pool_size，innodb_log_file_size，innodb_flush_method三個參數。

6.10 JSON特性增強

MySQL 8 大幅改進了對JSON 的支持，添加了基於路徑查詢參數從JSON欄位中抽取數據的JSON_EXTRACT() 函數，以及用於將數據分別組合到JSON 數組和對象中的JSON_ARRAYAGG() 和JSON_OBJECTAGG() 聚合函數。

七.其他增強

 7.1.組複製 

 消息碎片化 、通信協議設

7.2 支持線上修改全局參數並持久化

通過加上PERSIST關鍵字，可以將修改的參數持久化到新的配置文件（mysqld-auto.cnf）中，重啟MySQL時，可以從該配置文件獲取到最新的配置參數。

系統會在數據目錄下生成mysqld-auto.cnf 文件，該文件內容是以json格式存儲的。當my.cnf 和mysqld-auto.cnf 同時存在時，後者優先順序更高。

It is created by the server upon execution of SET PERSIST or SET PERSIST_ONLY statements.

例如：

SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;

This SET syntax enables you to make configuration changes at runtime that also persist across server restarts. Like SET GLOBAL, SET PERSIST sets the global variable runtime value, but also writes the variable setting to the mysqld-auto.cnf file (replacing any existing variable setting if there is one).

7.3 binlog日誌過期時間精確到秒

之前是天，並且參數名稱發生變化. 在8.0版本之前，binlog日誌過期時間設置都是設置expire_logs_days參數，而在8.0版本中，MySQL預設使用binlog_expire_logs_seconds參數。

7.4  undo空間自動回收

innodb_undo_log_truncate參數在8.0.2版本預設值由OFF變為ON，預設開啟undo日誌表空間自動回收。

innodb_undo_tablespaces參數在8.0.2版本預設為2，當一個undo表空間被回收時，還有另外一個提供正常服務。

innodb_max_undo_log_size參數定義了undo表空間回收的最大值，當undo表空間超過這個值，該表空間被標記為可回收。

7.5 地理信息系統 GIS

8.0 版本提供對地形的支持，其中包括了對空間參照系的數據源信息的支持，SRS aware spatial數據類型，空間索引，空間函數。總而言之，8.0版本可以理解地球錶面的經緯度信息，而且可以在任意受支持的5000個空間參照系中計算地球上任意兩點之間的距離.

註意:升級，一定要驗證jdbc驅動是否匹配，是否需要隨著升級。

八.參考

1.我為什麼強烈建議升級到MySQL 8.0

https://www.bilibili.com/video/av200848981/

2.MySQL性能基準測試對比：MySQL 5.7與MySQL 8.0

https://www.yisu.com/zixun/11215.html

3.MySQL8.0 GA版本的新特性有哪些

https://www.yisu.com/zixun/30454.html

4.MySQL5.6、5.7、8.0線上DDL對比情況

.https://www.yisu.com/zixun/29320.html

5.MySQL 8.0 新特性完整彙總

https://mytecdb.com/blogDetail.php?id=170

6.MySQL 8.0 預設值發生改變的參數彙總

https://mytecdb.com/blogDetail.php?id=140