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