MySQL 8新特性

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

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

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

除了高性能之外，MySQL 8還新增了很多功能，我找了幾個(gè)比較有特點(diǎn)的新特性，在這里總結(jié)一下。

本文使用的MySQL版本為 8.0.29

賬戶與安全

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

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

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

在MySQL 8中，創(chuàng)建用戶和授權(quán)需要分開(kāi)執(zhí)行，否則會(huì)報(bào)錯(cuò)，執(zhí)行不成功：

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

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

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

這是因?yàn)槲业?MySQL 8 安裝完成后，進(jìn)入命令行用的還是臨時(shí)密碼，并未修改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)

認(rèn)證插件

在MySQL中，可以用 show variables 命令查看一些設(shè)置的MySQL變量，其中密碼認(rèn)證插件的變量名稱是 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 版本的默認(rèn)認(rèn)證插件是 mysql_native_password ， 而 8.0 版本的默認(rèn)認(rèn)證插件是 caching_sha2_password 。

caching_sha2_password 這個(gè)認(rèn)證插件帶來(lái)的問(wèn)題是，我們直接在客戶端連接MySQL會(huì)連不上，比如用Navicat ：

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

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

此時(shí)，我們來(lái)看一下 user 表中的插件信息：

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

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

然后重啟MySQL服務(wù)即可。

密碼管理

MySQL 8增加了密碼管理功能，開(kāi)始允許限制重復(fù)使用以前的密碼：

這里有幾個(gè)屬性，其中：

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

修改 password_history 全局策略：

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

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

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

下面來(lái)修改一下密碼試試。

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

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

索引增強(qiáng)

MySQL 8 對(duì)索引也有相應(yīng)的增強(qiáng)，增加了方便測(cè)試的 隱藏索引 ，真正的 降序索引 ，還增加了 函數(shù)索引。

隱藏索引

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

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

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

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

創(chuàng)建一個(gè)表 t_test ，并創(chuàng)建一個(gè)正常的索引 idx_name ，一個(gè)隱藏索引 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;

此時(shí)，看一下索引信息：

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。

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

可以看到，隱藏索引在查詢的時(shí)候并不會(huì)用到，就跟沒(méi)有這個(gè)索引一樣，那么 隱藏索引 的用處到底是個(gè)什么玩意呢？

這里可以通過(guò)優(yōu)化器的開(kāi)關(guān)–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 配置默認(rèn)是 OFF 的，將其打開(kāi)看看效果：

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

再來(lái)看一下隱藏索引 idx_age 是否生效：

666?。。?/p>

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

可見(jiàn)索引與隱藏索引轉(zhuǎn)換的SQL語(yǔ)句：

— 轉(zhuǎn)換成可見(jiàn)索引alter table t_test alter index idx_age visible;– 轉(zhuǎn)換成隱藏索引alter table t_test alter index idx_age invisible;

降序索引

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

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

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

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

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í)對(duì)索引進(jìn)行函數(shù)操作，則該索引不生效，基于此，MySQL 8中引入了 函數(shù)索引 。

還是舉個(gè)簡(jiǎn)單的例子看一下：創(chuàng)建一個(gè)表t2，字段c1上建普通索引，字段c2上建upper函數(shù)（將字母轉(zhuǎn)成大寫的函數(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)));

通過(guò)show index from t2G 看一下：

下面來(lái)分別查詢一下，看看索引的使用情況：

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

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

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

原子DDL操作

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

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

舉個(gè)簡(jiǎn)單的例子：數(shù)據(jù)庫(kù)中有表t1，沒(méi)有表t2，執(zhí)行語(yǔ)句刪除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 中的操作，可以看到該操作并沒(méi)有刪除掉表t1，那么在之前的版本呢，下面在 MySQL 5.7 版本中進(jìn)行同樣的操作：

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)

雖然也有報(bào)錯(cuò)提示說(shuō)t2表不存在，但是t1表是真實(shí)的被刪除掉了！

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

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

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

支持與表相關(guān)的 DDL：

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

通用表達(dá)式（CTE）

Common Table Expressions（CTE）通用表達(dá)式，也就是MySQL 8中的 with 語(yǔ)句。

通過(guò)一個(gè)簡(jiǎn)單的例子了解一下。

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;

通過(guò)CTE表達(dá)式，可以用遞歸的方式簡(jiǎn)化為如下寫法：

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

再比如，有這樣一個(gè)場(chǎng)景，查看某個(gè)員工的上下級(jí)關(guān)系，就可以通過(guò)CTE遞歸查出來(lái)。

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

其他

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

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

JSON增強(qiáng)

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

這里不多做舉例了（有沒(méi)有一種可能是作者太懶？），官方文檔上面那是相當(dāng)?shù)脑敿?xì)！

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

“做程序員，圈子和學(xué)習(xí)最重要”因?yàn)橛杏辛巳ψ涌梢宰屇闵僮邚澛罚瑪U(kuò)寬人脈，擴(kuò)展思路，學(xué)習(xí)他人的一些經(jīng)驗(yàn)及學(xué)習(xí)方法！同時(shí)在這分享一下是一直以來(lái)整理的Java后端進(jìn)階筆記文檔和學(xué)習(xí)資料免費(fèi)分享給大家！需要資料的朋友私信我扣【1】