1. 預設字符集由latin1變為utf8mb4

在8.0版本之前，預設字符集為latin1，utf8指向的是utf8mb3，8.0版本預設字符集為utf8mb4，utf8預設指向的也是utf8mb4。
注：在Percona Server 8.0.15版本上測試，utf8仍然指向的是utf8mb3，與官方文件有出入。

Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |

2. MyISAM系統表全部換成InnoDB表

系統表全部換成事務型的innodb表，預設的MySQL例項將不包含任何MyISAM表，除非手動建立MyISAM表。

# MySQL 5.7

mysql > select distinct( ENGINE ) from information_schema.tables;

+--------------------+

| ENGINE |

+--------------------+

| MEMORY |

| InnoDB |

| MyISAM |

| CSV |

| PERFORMANCE_SCHEMA |

| NULL |

+--------------------+

6 rows in set ( 0.00 sec)

# MySQL 8.0

mysql > select distinct( ENGINE ) from information_schema.tables;

+--------------------+

| ENGINE |

+--------------------+

| NULL |

| InnoDB |

| CSV |

| PERFORMANCE_SCHEMA |

+--------------------+

4 rows in set ( 0.00 sec)

3. 自增變數持久化

在8.0之前的版本，自增主鍵AUTO_INCREMENT的值如果大於max(primary key)+1，在MySQL重啟後，會重置AUTO_INCREMENT=max(primary key)+1，這種現象在某些情況下會導致業務主鍵衝突或者其他難以發現的問題。自增主鍵重啟重置的問題很早就被發現，一直到8.0才被解決，8.0版本將會對AUTO_INCREMENT值進行持久化，MySQL重啟後，該值將不會改變。

4. 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 ;

mysql > create table t1 (c int ) engine = innodb;

# MySQL 錯誤日誌 :

201 9 - 06 - 26 T11 : 25 : 25.817245 + 08 : 00 44 [Note] [MY - 012473 ] [InnoDB] InnoDB : DDL log insert : [DDL record : DELETE SPACE, id = 41 , thread_id = 44 , space_id = 6 , old_file_path = . / db / t1.ibd]

201 9 - 06 - 26 T11 : 25 : 25.817369 + 08 : 00 44 [Note] [MY - 012478 ] [InnoDB] InnoDB : DDL log delete : by id 41

201 9 - 06 - 26 T11 : 25 : 25.819753 + 08 : 00 44 [Note] [MY - 012477 ] [InnoDB] InnoDB : DDL log insert : [DDL record : REMOVE CACHE, id = 42 , thread_id = 44 , table_id = 1063 , new_file_path = db / t1]

201 9 - 06 - 26 T11 : 25 : 25.819796 + 08 : 00 44 [Note] [MY - 012478 ] [InnoDB] InnoDB : DDL log delete : by id 42

201 9 - 06 - 26 T11 : 25 : 25.820556 + 08 : 00 44 [Note] [MY - 012472 ] [InnoDB] InnoDB : DDL log insert : [DDL record : FREE, id = 43 , thread_id = 44 , space_id = 6 , index_id = 140 , page_no = 4 ]

201 9 - 06 - 26 T11 : 25 : 25.820594 + 08 : 00 44 [Note] [MY - 012478 ] [InnoDB] InnoDB : DDL log delete : by id 43

201 9 - 06 - 26 T11 : 25 : 25.825743 + 08 : 00 44 [Note] [MY - 012485 ] [InnoDB] InnoDB : DDL log post ddl : begin for thread id : 44

201 9 - 06 - 26 T11 : 25 : 25.825784 + 08 : 00 44 [Note] [MY - 012486 ] [InnoDB] InnoDB : DDL log post ddl : end for thread id : 44

來看另外一個例子，庫裡只有一個t1表，drop table t1,t2; 試圖刪除t1,t2兩張表,在5.7中，執行報錯，但是t1表被刪除，在8.0中執行報錯，但是t1表沒有被刪除，證明了8.0 DDL操作的原子性，要麼全部成功，要麼回滾。

# MySQL 5.7

mysql > show tables;

+---------------+

| Tables_in_db |

+---------------+

| t1 |

+---------------+

1 row in set ( 0.00 sec)

mysql > drop table t1, t2;

ERROR 1051 ( 42 S02): Unknown table 'db.t2'

mysql > show tables;

Empty set ( 0.00 sec)

# MySQL 8.0

mysql > show tables;

+---------------+

| Tables_in_db |

+---------------+

| t1 |

+---------------+

1 row in set ( 0.00 sec)

mysql > drop table t1, t2;

ERROR 1051 ( 42 S02): Unknown table 'db.t2'

mysql > show tables;

+---------------+

| Tables_in_db |

+---------------+

| t1 |

+---------------+

1 row in set ( 0.00 sec)

5. 引數修改持久化

MySQL 8.0版本支援線上修改全域性引數並持久化，透過加上PERSIST關鍵字，可以將修改的引數持久化到新的配置檔案（mysqld-auto.cnf）中，重啟MySQL時，可以從該配置檔案獲取到最新的配置引數。
例如執行：
set PERSIST expire_logs_days=10 ;
系統會在資料目錄下生成一個包含json格式的 mysqld-auto.cnf 的檔案，格式化後如下所示，當 my.cnf 和 mysqld-auto.cnf 同時存在時，後者具有更高優先順序。

{

"Version" : 1 ,

"mysql_server" : {

"expire_logs_days" : {

"Value" : "10" ,

"Metadata" : {

"Timestamp" : 1529657078851627 ,

"User" : "root" ,

"Host" : "localhost"

}

6. 新增降序索引

MySQL在語法上很早就已經支援降序索引，但實際上建立的仍然是升序索引，如下MySQL 5.7 所示，c2欄位降序，但是從show create table看c2仍然是升序。8.0可以看到，c2欄位降序。

# MySQL 5.7

mysql > create table t1 (c1 int ,c2 int ,index idx_c1_c2 (c1,c2 desc));

Query OK, 0 rows affected ( 0.03 sec)

mysql > show create table t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int ( 11 ) DEFAULT NULL,

`c2` int ( 11 ) DEFAULT NULL,

KEY `idx_c1_c2` ( `c1` , `c2` )

) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4

1 row in set ( 0.00 sec)

# MySQL 8.0

mysql > create table t1 (c1 int ,c2 int ,index idx_c1_c2 (c1,c2 desc));

Query OK, 0 rows affected ( 0.06 sec)

mysql > show create table t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int ( 11 ) DEFAULT NULL,

`c2` int ( 11 ) DEFAULT NULL,

KEY `idx_c1_c2` ( `c1` , `c2` DESC)

) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC

1 row in set ( 0.00 sec)

再來看看降序索引在執行計劃中的表現，在t1表插入10萬條隨機資料，檢視select * from t1 order by c1 , c2 desc;的執行計劃。從執行計劃上可以看出，5.7的掃描數100113遠遠大於8.0的5行，並且使用了filesort。

DELIMITER ;;

CREATE PROCEDURE test_insert ()

BEGIN

DECLARE i INT DEFAULT 1 ;

WHILE i < 100000

DO

insert into t1 select rand() * 100000 , rand() * 100000 ;

SET i = i + 1 ;

END WHILE ;

commit;

END ;;

DELIMITER ;

CALL test_insert();

# MySQL 5.7

mysql > explain select * from t1 order by c1 , c2 desc limit 5 ;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 100113 | 100.00 | Using index; Using filesort |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

1 row in set, 1 warning ( 0.00 sec)

# MySQL 8.0

mysql > explain select * from t1 order by c1 , c2 desc limit 5 ;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 5 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

1 row in set, 1 warning ( 0.00 sec)

降序索引只是對查詢中特定的排序順序有效，如果使用不當，反而查詢效率更低，比如上述查詢排序條件改為 order by c1 desc, c2 desc，這種情況下，5.7的執行計劃要明顯好於8.0的，如下：

# MySQL 5.7

mysql > explain select * from t1 order by c1 desc , c2 desc limit 5 ;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 5 | 100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

1 row in set, 1 warning ( 0.01 sec)

# MySQL 8.0

mysql > explain select * from t1 order by c1 desc , c2 desc limit 5 ;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 100429 | 100.00 | Using index; Using filesort |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+

1 row in set, 1 warning ( 0.01 sec)

7. group by 不再隱式排序

mysql 8.0 對於group by 欄位不再隱式排序，如需要排序，必須顯式加上order by 子句。

# 表結構

mysql > show create table tb1\ G

*************************** 1. row ***************************

Table : tb1

Create Table : CREATE TABLE `tb1` (

`id` int( 11 ) NOT NULL AUTO_INCREMENT ,

`name` varchar( 50 ) DEFAULT NULL ,

`group_own` int( 11 ) DEFAULT '0' ,

PRIMARY KEY (`id`)

) ENGINE = InnoDB AUTO_INCREMENT = 11 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC

1 row in set ( 0.00 sec)

# 表資料

mysql > select * from tb1;

+----+------+-----------+

| id | name | group_own |

+----+------+-----------+

| 1 | 1 | 0 |

| 2 | 2 | 0 |

| 3 | 3 | 0 |

| 4 | 4 | 0 |

| 5 | 5 | 5 |

| 8 | 8 | 1 |

| 10 | 10 | 5 |

+----+------+-----------+

7 rows in set ( 0.00 sec)

# MySQL 5.7

mysql > select count(id), group_own from tb1 group by group_own;

+-----------+-----------+

| count(id) | group_own |

+-----------+-----------+

| 4 | 0 |

| 1 | 1 |

| 2 | 5 |

+-----------+-----------+

3 rows in set ( 0.00 sec)

# MySQL 8.0.11

mysql > select count(id), group_own from tb1 group by group_own;

+-----------+-----------+

| count(id) | group_own |

+-----------+-----------+

| 4 | 0 |

| 2 | 5 |

| 1 | 1 |

+-----------+-----------+

3 rows in set ( 0.00 sec)

# MySQL 8.0.11 顯式地加上 order by 進行排序

mysql > select count(id), group_own from tb1 group by group_own order by group_own;

+-----------+-----------+

| count(id) | group_own |

+-----------+-----------+

| 4 | 0 |

| 1 | 1 |

| 2 | 5 |

+-----------+-----------+

3 rows in set ( 0.00 sec)

8. JSON特性增強

MySQL 8 大幅改進了對 JSON 的支援，新增了基於路徑查詢引數從 JSON 欄位中抽取資料的 JSON_EXTRACT() 函式，以及用於將資料分別組合到 JSON 陣列和物件中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函式。

在主從複製中，新增引數 binlog_row_value_options，控制JSON資料的傳輸方式，允許對於Json型別部分修改，在binlog中只記錄修改的部分，減少json大資料在只有少量修改的情況下，對資源的佔用。

9. redo & undo 日誌加密

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

10. innodb select for update跳過鎖等待

select ... for update，select ... for share(8.0新增語法) 新增 NOWAIT、SKIP LOCKED語法，跳過鎖等待，或者跳過鎖定。
在5.7及之前的版本，select...for update，如果獲取不到鎖，會一直等待，直到innodb_lock_wait_timeout超時。

在8.0版本，透過新增nowait，skip locked語法，能夠立即返回。如果查詢的行已經加鎖，那麼nowait會立即報錯返回，而skip locked也會立即返回，只是返回的結果中不包含被鎖定的行。

# session1:

mysql > begin;

mysql > select * from t1 where c1 = 2 for update;

+------+-------+

| c1 | c2 |

+------+-------+

| 2 | 60530 |

| 2 | 24678 |

+------+-------+

2 rows in set ( 0.00 sec)

# session2:

mysql > select * from t1 where c1 = 2 for update nowait;

ERROR 3572 (HY000): Statement aborted because lock (s) could not be acquired immediately and NOWAIT is set .

mysql > select * from t1 where c1 = 2 for update skip locked;

Empty set ( 0.00 sec)

11. 增加SET_VAR語法

在sql語法中增加SET_VAR語法，動態調整部分引數，有利於提升語句效能。

· select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ;

· insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

12. 支援不可見索引

使用INVISIBLE關鍵字在建立表或者進行表變更中設定索引是否可見。索引不可見只是在查詢時最佳化器不使用該索引，即使使用force index，最佳化器也不會使用該索引，同時最佳化器也不會報索引不存在的錯誤，因為索引仍然真實存在，在必要時，也可以快速的恢復成可見。

# 建立不可見索引

create table t2 (c1 int ,c2 int ,index idx_c1_c2 (c1,c2 desc) invisible );

# 索引可見

alter table t2 alter index idx_c1_c2 visible;

# 索引不可見

alter table t2 alter index idx_c1_c2 invisible;

13. 支援直方圖

最佳化器會利用column_statistics的資料，判斷欄位的值的分佈，得到更準確的執行計劃。

可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 來收集或者刪除直方圖資訊。

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

# 新增 / 更新直方圖

mysql > analyze table t1 update histogram on c1, c2 with 32 buckets;

+--------+-----------+----------+-----------------------------------------------+

+--------+-----------+----------+-----------------------------------------------+

+--------+-----------+----------+-----------------------------------------------+

2 rows in set ( 2.57 sec)

# 刪除直方圖

mysql > analyze table t1 drop histogram on c1, c2;

+--------+-----------+----------+-----------------------------------------------+

+--------+-----------+----------+-----------------------------------------------+

+--------+-----------+----------+-----------------------------------------------+

2 rows in set ( 0.13 sec)

14. 新增innodb_dedicated_server引數

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

15. 日誌分類更詳細

在錯誤資訊中新增了錯誤資訊編號[MY-010311]和錯誤所屬子系統[Server]

# MySQL 5.7

201 9 -06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

201 9 -06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

201 9 -06-08T09:07:20.117868+08:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

# MySQL 8.0

201 9 -06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

201 9 -06-21T17:53:13.040520+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

201 9 -06-21T17:53:13.040542+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

16. 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表空間超過這個值，該表空間被標記為可回收。

17. 增加資源組

MySQL 8.0新增了一個資源組功能，用於調控執行緒優先順序以及繫結CPU核。
MySQL使用者需要有 RESOURCE_GROUP_ADMIN許可權才能建立、修改、刪除資源組。
在Linux環境下，MySQL程式需要有 CAP_SYS_NICE 許可權才能使用資源組完整功能。

[root@localhost ~ ]# sudo setcap cap_sys_nice + ep / usr / local / mysql8. 0 / bin / mysqld

[root@localhost ~ ]# getcap / usr / local / mysql8. 0 / bin / mysqld

/ usr / local / mysql8. 0 / bin / mysqld = cap_sys_nice + ep

預設提供兩個資源組，分別是USR_default，SYS_default

建立資源組：
create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;
將當前執行緒加入資源組：
SET RESOURCE GROUP test_resouce_group;
將某個執行緒加入資源組：
SET RESOURCE GROUP test_resouce_group FOR thread_id;
檢視資源組裡有哪些執行緒：
select * from Performance_Schema.threads where RESOURCE_GROUP='test_resouce_group';
修改資源組：
alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8;
刪除資源組：
drop resource group test_resouce_group;

# 建立資源組

mysql > create resource group test_resouce_group type = USER vcpu = 0 , 1 thread_priority = 5 ;

Query OK , 0 rows affected ( 0.03 sec)

mysql > select * from RESOURCE_GROUPS ;

+---------------------+---------------------+------------------------+----------+-----------------+

+---------------------+---------------------+------------------------+----------+-----------------+

| USR_default | USER | 1 | 0 - 3 | 0 |

| SYS_default | SYSTEM | 1 | 0 - 3 | 0 |

| test_resouce_group | USER | 1 | 0 - 1 | 5 |

+---------------------+---------------------+------------------------+----------+-----------------+

3 rows in set ( 0.00 sec)

# 把執行緒 id 為 60 的執行緒加入到資源組 test_resouce_group 中，執行緒 id 可透過 Performance_Schema.threads 獲取

mysql > SET RESOURCE GROUP test_resouce_group FOR 60 ;

Query OK , 0 rows affected ( 0.00 sec)

# 資源組裡有執行緒時，刪除資源組報錯

mysql > drop resource group test_resouce_group;

ERROR 3656 ( HY000 ): Resource group test_resouce_group is busy.

# 修改資源組

mysql > alter resource group test_resouce_group vcpu = 2 , 3 THREAD_PRIORITY = 8 ;

Query OK , 0 rows affected ( 0.10 sec)

mysql > select * from RESOURCE_GROUPS ;

+---------------------+---------------------+------------------------+----------+-----------------+

+---------------------+---------------------+------------------------+----------+-----------------+

| USR_default | USER | 1 | 0 - 3 | 0 |

| SYS_default | SYSTEM | 1 | 0 - 3 | 0 |

| test_resouce_group | USER | 1 | 2 - 3 | 8 |

+---------------------+---------------------+------------------------+----------+-----------------+

3 rows in set ( 0.00 sec)

# 把資源組裡的執行緒移出到預設資源組 USR_default

mysql > SET RESOURCE GROUP USR_default FOR 60 ;

Query OK , 0 rows affected ( 0.00 sec)

# 刪除資源組

mysql > drop resource group test_resouce_group;

Query OK , 0 rows affected ( 0.04 sec)

18. 增加角色管理

角色可以認為是一些許可權的集合，為使用者賦予統一的角色，許可權的修改直接透過角色來進行，無需為每個使用者單獨授權。

# 建立角色

mysql > create role role_test;

Query OK , 0 rows affected ( 0.03 sec)

# 給角色授予許可權

mysql > grant select on db. * to 'role_test' ;

Query OK , 0 rows affected ( 0.10 sec)

# 建立使用者

mysql > create user 'read_user' @ '%' identified by '123456' ;

Query OK , 0 rows affected ( 0.09 sec)

# 給使用者賦予角色

mysql > grant 'role_test' to 'read_user' @ '%' ;

Query OK , 0 rows affected ( 0.02 sec)

# 給角色 role_test 增加 insert 許可權

mysql > grant insert on db. * to 'role_test' ;

Query OK , 0 rows affected ( 0.08 sec)

# 給角色 role_test 刪除 insert 許可權

mysql > revoke insert on db. * from 'role_test' ;

Query OK , 0 rows affected ( 0.10 sec)

# 檢視預設角色資訊

mysql > select * from mysql.default_roles;

+------+-----------+-------------------+-------------------+

+------+-----------+-------------------+-------------------+

| % | read_user | % | role_test |

+------+-----------+-------------------+-------------------+

1 row in set ( 0.00 sec)

# 檢視角色與使用者關係

mysql > select * from mysql.role_edges;

+-----------+-----------+---------+-----------+-------------------+

+-----------+-----------+---------+-----------+-------------------+

| % | role_test | % | read_user | N |

+-----------+-----------+---------+-----------+-------------------+

1 row in set ( 0.00 sec)

# 刪除角色

mysql > drop role role_test;

Query OK , 0 rows affected ( 0.06 sec)

好程式設計師大資料培訓分享MySQL8.0新特性

相關文章