MySQL 8新特性

選擇MySQL 8的背景：MySQL 5.6已經(jīng)停止版本更新了，對于 MySQL 5.7 版本，其將于 2023年 10月31日 停止支持。后續(xù)官方將不再進行后續(xù)的代碼維護。

另外，MySQL 8.0 全內(nèi)存訪問可以輕易跑到 200W QPS，I/O 極端高負載場景跑到 16W QPS，如下圖：

上面三個圖來自于MySQL官網(wǎng)：www.mysql.com/why-mysql/b…

除了高性能之外，MySQL 8還新增了很多功能，我找了幾個比較有特點的新特性，在這里總結一下。

本文使用的MySQL版本為 8.0.29

賬戶與安全

用戶的創(chuàng)建和授權

在MySQL之前的版本，創(chuàng)建用戶和給創(chuàng)建的用戶授權可以一條語句執(zhí)行完成：

grant all privileges on *.* to ‘zhangsan’@’%’ identified by ‘Fawai@kuangtu6’;

在MySQL 8中，創(chuàng)建用戶和授權需要分開執(zhí)行，否則會報錯，執(zhí)行不成功：

在 MySQL 8 中，需要分2不完成創(chuàng)建用戶和授權的操作：

— 創(chuàng)建用戶create user ‘zhangsan’@’%’ identified by ‘Fawai@kuangtu6’;– 授權grant all privileges on *.* to ‘zhangsan’@’%’;

再執(zhí)行創(chuàng)建用戶時，出現(xiàn)了如下錯誤：

這是因為我的 MySQL 8 安裝完成后，進入命令行用的還是臨時密碼，并未修改root的初始密碼，需要修改密碼才允許操作。

修改密碼操作：

— 修改root密碼alter user user() identified by ‘Root@001’;

再創(chuàng)建用戶即可：

mysql> create user ‘zhangsan’@’%’ identified by ‘Fawai@kuangtu6’;Query OK, 0 rows affected (0.01 sec)mysql> grant all privileges on *.* to ‘zhangsan’@’%’;Query OK, 0 rows affected (0.00 sec)

認證插件

在MySQL中，可以用 show variables 命令查看一些設置的MySQL變量，其中密碼認證插件的變量名稱是 default_authentication_plugin 。

MySQL 5.7版本 ：

mysql> show variables like ‘%default_authentication%’;+——————————-+———————–+| Variable_name | Value |+——————————-+———————–+| default_authentication_plugin | mysql_native_password |+——————————-+———————–+1 row in set (0.02 sec)

MySQL 8版本 ：

mysql> show variables like ‘%default_authentication%’;+——————————-+———————–+| Variable_name | Value |+——————————-+———————–+| default_authentication_plugin | caching_sha2_password |+——————————-+———————–+1 row in set (0.07 sec)

可以看出，5.7 版本的默認認證插件是 mysql_native_password ， 而 8.0 版本的默認認證插件是 caching_sha2_password 。

caching_sha2_password 這個認證插件帶來的問題是，我們直接在客戶端連接MySQL會連不上，比如用Navicat ：

我們可以臨時修改一下認證插件為 mysql_native_password ，再看一下是否能連接上，修改命令為：

mysql> alter user ‘zhangsan’@’%’ identified with mysql_native_password by ‘Fawai@kuangtu6’;

此時，我們來看一下 user 表中的插件信息：

zhangsan用戶的認證插件改為了mysql_native_password ，而其他的認證插件仍為默認的 caching_sha2_password 。

當然，alter user 修改插件的方式只能作為臨時修改，而要永久修改，則需要修改MySQL配置文件 /etc/my.cnf 中的配置：

然后重啟MySQL服務即可。

密碼管理

MySQL 8增加了密碼管理功能，開始允許限制重復使用以前的密碼：

這里有幾個屬性，其中：

• password_history ：此變量定義全局策略，表示在修改密碼時，密碼可以重復使用之前密碼的更改次數(shù)。如果值為 0（默認值），則沒有基于密碼更改次數(shù)的重用限制。eg：值為2，表示修改密碼不能和最近2次一致。
• password_require_current ：此變量定義全局策略，用于控制嘗試更改帳戶密碼是否必須指定要替換的當前密碼。意思就是是否需要校驗舊密碼（off 不校驗、 on校驗）(針對非root用戶)。
• password_reuse_interval ：對于以前使用的帳戶密碼，此變量表示密碼可以重復使用之前必須經(jīng)過的天數(shù)。如果值為 0（默認值），則沒有基于已用時間的重用限制。

修改 password_history 全局策略：

— 修改密碼不能和最近2次一致set persist password_history=2;

而如果要修改用戶級別的 password_history ，命令為：

alter user ‘zhangsan’@’%’ password history 2;

下面來修改一下密碼試試。

— zhangsan的原密碼是Fawai@kuangtu6，執(zhí)行修改密碼操作，仍修改密碼為Fawai@kuangtu6，根據(jù)密碼策略不允許與最近2次的密碼相同，應該修改不成功alter user ‘zhangsan’@’%’ identified by ‘Fawai@kuangtu6’;

如果把全局參數(shù) password_history 改為0，則對于root用戶就沒有此限制了：

索引增強

MySQL 8 對索引也有相應的增強，增加了方便測試的 隱藏索引 ，真正的 降序索引 ，還增加了 函數(shù)索引。

隱藏索引

MySQL 8開始支持隱藏索引 (invisible index)，也叫不可見索引。隱藏索引不會被優(yōu)化器使用，但仍然需要進行維護-創(chuàng)建、刪除等。 其常見應用場景有：軟刪除、灰度發(fā)布。

• 軟刪除：就是我們在線上會經(jīng)常刪除和創(chuàng)建索引，以前的版本，我們?nèi)绻麆h除了索引，后面發(fā)現(xiàn)刪錯了，我又需要創(chuàng)建一個索引，這樣做的話就非常影響性能。在MySQL 8中我們可以這么操作，把一個索引變成隱藏索引（索引就不可用了，查詢優(yōu)化器也用不上），最后確定要進行刪除這個索引我們才會進行刪除索引操作。
• 灰度發(fā)布：也是類似的，我們想在線上進行一些測試，可以先創(chuàng)建一個隱藏索引，不會影響當前的生產(chǎn)環(huán)境，然后我們通過一些附加的測試，發(fā)現(xiàn)這個索引沒問題，那么就直接把這個索引改成正式的索引，讓線上環(huán)境生效。

有了 隱藏索引 ，大大方便了我們做測試，可以說是非常的體貼了！

下面舉個例子看看隱藏索引怎么用法。

創(chuàng)建一個表 t_test ，并創(chuàng)建一個正常的索引 idx_name ，一個隱藏索引 idx_age ：

create table t_test(id int, name varchar(20), age int);create index idx_name on t_test(name);create index idx_age on t_test(age) invisible;

此時，看一下索引信息：

mysql> show index from t_testG*************************** 1. row *************************** Table: t_test Non_unique: 1 Key_name: idx_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 2. row *************************** Table: t_test Non_unique: 1 Key_name: idx_age Seq_in_index: 1 Column_name: age Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: NO Expression: NULL2 rows in set (0.01 sec)

普通索引的 Visible 屬性值為OFF，隱藏索引為ON。

再來看一下MySQL優(yōu)化器怎么處理這兩種索引的：

可以看到，隱藏索引在查詢的時候并不會用到，就跟沒有這個索引一樣，那么 隱藏索引 的用處到底是個什么玩意呢？

這里可以通過優(yōu)化器的開關–optimizer_switch ，

mysql> select @@optimizer_switchG*************************** 1. row ***************************@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

看到 use_invisible_indexes 配置默認是 OFF 的，將其打開看看效果：

— 在會話級別設置查詢優(yōu)化器可以看到隱藏索引set session optimizer_switch=”use_invisible_indexes=on”;

再來看一下隱藏索引 idx_age 是否生效：

666?。。?/p>

這樣的話就方便我們項目做灰度發(fā)布了，項目上線前，我想測試一下添加的新索引是否有用，可以現(xiàn)將其設置為隱藏索引，這樣不會影響線上業(yè)務，在會話級別將隱藏索引打開進行測試，發(fā)現(xiàn)沒有問題后轉為可見索引。

可見索引與隱藏索引轉換的SQL語句：

— 轉換成可見索引alter table t_test alter index idx_age visible;– 轉換成隱藏索引alter table t_test alter index idx_age invisible;

降序索引

MySQL 8支持 降序索引 ：DESC在索引中定義不再被忽略，而是導致鍵值以降序存儲。

以前，可以以相反的順序掃描索引，但會降低性能。降序索引可以按正序掃描，效率更高。

當最有效的掃描順序混合了某些列的升序和其他列的降序時，降序索引還使優(yōu)化器可以使用多列索引。

舉個例子，在 MySQL 8 和 MySQL 5.7 中均執(zhí)行如下建表語句：

CREATE TABLE t ( c1 INT, c2 INT, INDEX idx1 (c1 ASC, c2 ASC), INDEX idx2 (c1 ASC, c2 DESC), INDEX idx3 (c1 DESC, c2 ASC), INDEX idx4 (c1 DESC, c2 DESC));

然后看一下表的索引信息：

具體的用處在哪里呢？插入一些數(shù)據(jù)看一下。

insert into t(c1, c2) values (1, 10),(2, 20),(3, 30),(4, 40),(5, 50);

函數(shù)索引

在之前的MySQL版本中，查詢時對索引進行函數(shù)操作，則該索引不生效，基于此，MySQL 8中引入了 函數(shù)索引 。

還是舉個簡單的例子看一下：創(chuàng)建一個表t2，字段c1上建普通索引，字段c2上建upper函數(shù)（將字母轉成大寫的函數(shù)）索引。

create table t2(c1 varchar(10), c2 varchar(10));create index idx_c1 on t2(c1);create index idx_c2 on t2((upper(c2)));

通過show index from t2G 看一下：

下面來分別查詢一下，看看索引的使用情況：

由于c1字段上是普通索引，使用upper(c1)查詢時并沒有用到索引優(yōu)化，而c2字段上有函數(shù)索引upper(c2)，可以把整個upper(c2)看成是一個索引字段，查詢時索引生效了！

函數(shù)索引的實現(xiàn)原理：

函數(shù)索引在MySQL中相當于新增了一個列，這個列會根據(jù)函數(shù)來進行計算結果，然后使用函數(shù)索引的時候就會用這個計算后的列作為索引，其實就是增加了一個虛擬的列，然后根據(jù)虛擬的列進行查詢，從而達到利用索引的目的。

原子DDL操作

MySQL 8.0 支持原子數(shù)據(jù)定義語言 (DDL) 語句。此功能稱為原子 DDL。原子 DDL 語句將與 DDL 操作關聯(lián)的數(shù)據(jù)字典更新、存儲引擎操作和二進制日志寫入組合到單個原子操作中。

操作要么被提交，適用的更改被持久化到數(shù)據(jù)字典、存儲引擎和二進制日志中，要么被回滾，即使服務器在操作期間停止。

舉個簡單的例子：數(shù)據(jù)庫中有表t1，沒有表t2，執(zhí)行語句刪除t1和t2。

mysql> create table t1(c1 int);Query OK, 0 rows affected (0.04 sec)mysql> show tables;+—————-+| Tables_in_test |+—————-+| t1 |+—————-+1 row in set (0.00 sec)mysql> drop table t1,t2;ERROR 1051 (42S02): Unknown table ‘test.t2’mysql> show tables;+—————-+| Tables_in_test |+—————-+| t1 |+—————-+1 row in set (0.00 sec)

上面是在 MySQL 8 中的操作，可以看到該操作并沒有刪除掉表t1，那么在之前的版本呢，下面在 MySQL 5.7 版本中進行同樣的操作：

mysql> create table t1(c1 int);Query OK, 0 rows affected (0.06 sec)mysql> show tables;+—————-+| Tables_in_test |+—————-+| t1 |+—————-+1 row in set (0.00 sec)mysql> drop table t1,t2;ERROR 1051 (42S02): Unknown table ‘test.t2’mysql> show tables;Empty set (0.00 sec)

雖然也有報錯提示說t2表不存在，但是t1表是真實的被刪除掉了！

TIPS：如果確需要執(zhí)行drop表操作，請使用 if exists 來防止刪除不存在的表時出現(xiàn)的錯誤。

一個原子 DDL 操作內(nèi)容包括：

• 更新數(shù)據(jù)字典
• 存儲引擎層的操作
• 在 binlog 中記錄 DDL 操作

支持與表相關的 DDL：

• 數(shù)據(jù)庫
• 表空間
• 表
• 索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE
• 支持的其他 DDL ：存儲程序、觸發(fā)器、視圖、UDF 的 CREATE、DROP 以及ALTER 語句。
• 支持賬戶管理相關的 DDL：用戶和角色的 CREATE、ALTER、DROP 以及適用的 RENAME，以及 GRANT 和 REVOKE 語句。

通用表達式（CTE）

Common Table Expressions（CTE）通用表達式，也就是MySQL 8中的 with 語句。

通過一個簡單的例子了解一下。

idx展示1~10行，可以直接select 1 union select 2 …select 10這樣：

select 1 as idxUNIONselect 2 as idxUNIONselect 3 as idxUNIONselect 4 as idxUNIONselect 5 as idxUNIONselect 6 as idxUNIONselect 7 as idxUNIONselect 8 as idxUNIONselect 9 as idxUNIONselect 10 as idx;

通過CTE表達式，可以用遞歸的方式簡化為如下寫法：

with recursive cte(idx) as (select 1UNIONselect idx+1 from cte where idx<10)select * from cte;

再比如，有這樣一個場景，查看某個員工的上下級關系，就可以通過CTE遞歸查出來。

這里 dev.mysql.com/doc/refman/… 有更多比較好的例子，大家可以看一下。

其他

MySQL 8 還有很多比較實用的新特性，比如 ：

Window Function，對于查詢中的每一行，使用與該行相關的行執(zhí)行計算。

JSON增強

InnoDB 其他改進功能 ，比如死鎖檢查控制 innodb_deadlock_detect，對于高并發(fā)的系統(tǒng)，禁用死鎖檢查可能帶來性能的提高。

這里不多做舉例了（有沒有一種可能是作者太懶？），官方文檔上面那是相當?shù)脑敿殻?/p>

作者：行百里er鏈接：https://juejin.cn/post/7111255789876019208

“做程序員，圈子和學習最重要”因為有有了圈子可以讓你少走彎路，擴寬人脈，擴展思路，學習他人的一些經(jīng)驗及學習方法！同時在這分享一下是一直以來整理的Java后端進階筆記文檔和學習資料免費分享給大家！需要資料的朋友私信我扣【1】