MySQL 8.0 新特性完整匯總餐弱，見(jiàn)鏈接：
https://mytecdb.com/blogDetail.php?id=170

1. 默認(rèn)字符集由latin1變?yōu)閡tf8mb4

在8.0版本之前钳榨，默認(rèn)字符集為latin1柜砾，utf8指向的是utf8mb3，8.0版本默認(rèn)字符集為utf8mb4舷暮，utf8默認(rèn)指向的也是utf8mb4屈呕。
注：在Percona Server 8.0.15版本上測(cè)試沟饥，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系統(tǒng)表全部換成InnoDB表

系統(tǒng)表全部換成事務(wù)型的innodb表，默認(rèn)的MySQL實(shí)例將不包含任何MyISAM表叮姑，除非手動(dòng)創(chuàng)建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重啟后耘沼，會(huì)重置AUTO_INCREMENT=max(primary key)+1，這種現(xiàn)象在某些情況下會(huì)導(dǎo)致業(yè)務(wù)主鍵沖突或者其他難以發(fā)現(xiàn)的問(wèn)題朱盐。自增主鍵重啟重置的問(wèn)題很早就被發(fā)現(xiàn)(https://bugs.mysql.com/bug.php?id=199)群嗤，一直到8.0才被解決，8.0版本將會(huì)對(duì)AUTO_INCREMENT值進(jìn)行持久化托享，MySQL重啟后骚烧，該值將不會(huì)改變。

4. DDL原子化

InnoDB表的DDL支持事務(wù)完整性闰围，要么成功要么回滾赃绊，將DDL操作回滾日志寫入到data dictionary 數(shù)據(jù)字典表 mysql.innodb_ddl_log 中用于回滾操作，該表是隱藏的表羡榴，通過(guò)show tables無(wú)法看到碧查。通過(guò)設(shè)置參數(shù)，可將ddl操作日志打印輸出到mysql錯(cuò)誤日志中校仑。

mysql> set global log_error_verbosity=3;
mysql> set global innodb_print_ddl_logs=1;
mysql> create table t1(c int) engine=innodb;
 
# MySQL錯(cuò)誤日志:
2018-06-26T11: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]
2018-06-26T11:25:25.817369+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 41
2018-06-26T11: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]
2018-06-26T11:25:25.819796+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 42
2018-06-26T11: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]
2018-06-26T11:25:25.820594+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 43
2018-06-26T11:25:25.825743+08:00 44 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 44
2018-06-26T11:25:25.825784+08:00 44 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 44

來(lái)看另外一個(gè)例子忠售，庫(kù)里只有一個(gè)t1表，drop table t1,t2; 試圖刪除t1,t2兩張表,在5.7中迄沫，執(zhí)行報(bào)錯(cuò)稻扬，但是t1表被刪除，在8.0中執(zhí)行報(bào)錯(cuò)羊瘩，但是t1表沒(méi)有被刪除泰佳，證明了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 (42S02): 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 (42S02): Unknown table 'db.t2'
mysql> show tables;
+---------------+
| Tables_in_db |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

5. 參數(shù)修改持久化

MySQL 8.0版本支持在線修改全局參數(shù)并持久化，通過(guò)加上PERSIST關(guān)鍵字睬捶，可以將修改的參數(shù)持久化到新的配置文件（mysqld-auto.cnf）中黔宛，重啟MySQL時(shí)，可以從該配置文件獲取到最新的配置參數(shù)擒贸。
例如執(zhí)行：
set PERSIST expire_logs_days=10 ;
系統(tǒng)會(huì)在數(shù)據(jù)目錄下生成一個(gè)包含json格式的 mysqld-auto.cnf 的文件臀晃，格式化后如下所示觉渴，當(dāng) my.cnf 和 mysqld-auto.cnf 同時(shí)存在時(shí)，后者具有更高優(yōu)先級(jí)积仗。

{
    "Version": 1,
    "mysql_server": {
        "expire_logs_days": {
            "Value": "10",
            "Metadata": {
                "Timestamp": 1529657078851627,
                "User": "root",
                "Host": "localhost"
            }
        }
    }
}

6. 新增降序索引

MySQL在語(yǔ)法上很早就已經(jīng)支持降序索引疆拘，但實(shí)際上創(chuàng)建的仍然是升序索引，如下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)

再來(lái)看看降序索引在執(zhí)行計(jì)劃中的表現(xiàn)，在t1表插入10萬(wàn)條隨機(jī)數(shù)據(jù)渺氧，查看select * from t1 order by c1 , c2 desc;的執(zhí)行計(jì)劃旨涝。從執(zhí)行計(jì)劃上可以看出，5.7的掃描數(shù)100113遠(yuǎn)遠(yuǎn)大于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;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
|  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;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  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)

降序索引只是對(duì)查詢中特定的排序順序有效，如果使用不當(dāng)贩耐，反而查詢效率更低弧腥，比如上述查詢排序條件改為 order by c1 desc, c2 desc，這種情況下潮太，5.7的執(zhí)行計(jì)劃要明顯好于8.0的管搪，如下：

# MySQL 5.7
mysql> explain select * from t1  order by c1 desc , c2 desc limit 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  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;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+
|  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 對(duì)于group by 字段不再隱式排序，如需要排序铡买，必須顯式加上order by 子句更鲁。

# 表結(jié)構(gòu)
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)
 
# 表數(shù)據(jù)
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進(jìn)行排序
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特性增強(qiáng)

MySQL 8 大幅改進(jìn)了對(duì) JSON 的支持，添加了基于路徑查詢參數(shù)從 JSON 字段中抽取數(shù)據(jù)的 JSON_EXTRACT() 函數(shù)奇钞，以及用于將數(shù)據(jù)分別組合到 JSON 數(shù)組和對(duì)象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函數(shù)澡为。

在主從復(fù)制中，新增參數(shù) binlog_row_value_options景埃，控制JSON數(shù)據(jù)的傳輸方式媒至，允許對(duì)于Json類型部分修改，在binlog中只記錄修改的部分纠亚，減少json大數(shù)據(jù)在只有少量修改的情況下，對(duì)資源的占用筋夏。

9. redo & undo 日志加密

增加以下兩個(gè)參數(shù)蒂胞，用于控制redo、undo日志的加密条篷。
innodb_undo_log_encrypt
innodb_undo_log_encrypt

10. innodb select for update跳過(guò)鎖等待

select ... for update骗随，select ... for share(8.0新增語(yǔ)法) 添加 NOWAIT蛤织、SKIP LOCKED語(yǔ)法，跳過(guò)鎖等待鸿染，或者跳過(guò)鎖定指蚜。
在5.7及之前的版本，select...for update涨椒，如果獲取不到鎖摊鸡，會(huì)一直等待，直到innodb_lock_wait_timeout超時(shí)蚕冬。

在8.0版本免猾，通過(guò)添加nowait，skip locked語(yǔ)法囤热，能夠立即返回猎提。如果查詢的行已經(jīng)加鎖，那么nowait會(huì)立即報(bào)錯(cuò)返回旁蔼，而skip locked也會(huì)立即返回锨苏，只是返回的結(jié)果中不包含被鎖定的行。

# 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語(yǔ)法

在sql語(yǔ)法中增加SET_VAR語(yǔ)法棺聊，動(dòng)態(tài)調(diào)整部分參數(shù)伞租，有利于提升語(yǔ)句性能。

• 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. 支持不可見(jiàn)索引

使用INVISIBLE關(guān)鍵字在創(chuàng)建表或者進(jìn)行表變更中設(shè)置索引是否可見(jiàn)躺屁。索引不可見(jiàn)只是在查詢時(shí)優(yōu)化器不使用該索引肯夏，即使使用force index份帐，優(yōu)化器也不會(huì)使用該索引卤妒，同時(shí)優(yōu)化器也不會(huì)報(bào)索引不存在的錯(cuò)誤，因?yàn)樗饕匀徽鎸?shí)存在减响，在必要時(shí)耐亏，也可以快速的恢復(fù)成可見(jiàn)徊都。

# 創(chuàng)建不可見(jiàn)索引
create table t2(c1 int,c2 int,index idx_c1_c2(c1,c2 desc) invisible );
# 索引可見(jiàn)
alter table t2 alter index idx_c1_c2 visible;
# 索引不可見(jiàn)
alter table t2 alter index idx_c1_c2 invisible;

13. 支持直方圖

優(yōu)化器會(huì)利用column_statistics的數(shù)據(jù)，判斷字段的值的分布广辰，得到更準(zhǔn)確的執(zhí)行計(jì)劃暇矫。

可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 來(lái)收集或者刪除直方圖信息。

直方圖統(tǒng)計(jì)了表中某些字段的數(shù)據(jù)分布情況择吊，為優(yōu)化選擇高效的執(zhí)行計(jì)劃提供參考李根，直方圖與索引有著本質(zhì)的區(qū)別，維護(hù)一個(gè)索引有代價(jià)几睛。每一次的insert房轿、update、delete都會(huì)需要更新索引，會(huì)對(duì)性能有一定的影響囱持。而直方圖一次創(chuàng)建永不更新夯接，除非明確去更新它。所以不會(huì)影響insert纷妆、update盔几、delete的性能。

# 添加/更新直方圖
mysql> analyze table t1 update histogram on c1, c2 with 32 buckets;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| db.t1 | histogram | status   | Histogram statistics created for column 'c1'. |
| db.t1 | histogram | status   | Histogram statistics created for column 'c2'. |
+--------+-----------+----------+-----------------------------------------------+
2 rows in set (2.57 sec)
 
# 刪除直方圖
mysql> analyze table t1 drop histogram on c1, c2;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| db.t1 | histogram | status   | Histogram statistics removed for column 'c1'. |
| db.t1 | histogram | status   | Histogram statistics removed for column 'c2'. |
+--------+-----------+----------+-----------------------------------------------+
2 rows in set (0.13 sec)

14. 新增innodb_dedicated_server參數(shù)

能夠讓InnoDB根據(jù)服務(wù)器上檢測(cè)到的內(nèi)存大小自動(dòng)配置innodb_buffer_pool_size掩幢，innodb_log_file_size逊拍，innodb_flush_method三個(gè)參數(shù)。

15. 日志分類更詳細(xì)

在錯(cuò)誤信息中添加了錯(cuò)誤信息編號(hào)[MY-010311]和錯(cuò)誤所屬子系統(tǒng)[Server]

# MySQL 5.7
2018-06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-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
2018-06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv'  entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-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.
2018-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空間自動(dòng)回收

• innodb_undo_log_truncate參數(shù)在8.0.2版本默認(rèn)值由OFF變?yōu)镺N粒蜈，默認(rèn)開(kāi)啟undo日志表空間自動(dòng)回收顺献。
• innodb_undo_tablespaces參數(shù)在8.0.2版本默認(rèn)為2，當(dāng)一個(gè)undo表空間被回收時(shí)枯怖，還有另外一個(gè)提供正常服務(wù)注整。
• innodb_max_undo_log_size參數(shù)定義了undo表空間回收的最大值，當(dāng)undo表空間超過(guò)這個(gè)值度硝，該表空間被標(biāo)記為可回收肿轨。

17. 增加資源組

MySQL 8.0新增了一個(gè)資源組功能，用于調(diào)控線程優(yōu)先級(jí)以及綁定CPU核蕊程。
MySQL用戶需要有 RESOURCE_GROUP_ADMIN權(quán)限才能創(chuàng)建椒袍、修改、刪除資源組藻茂。
在Linux環(huán)境下驹暑，MySQL進(jìn)程需要有 CAP_SYS_NICE 權(quán)限才能使用資源組完整功能。

[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

默認(rèn)提供兩個(gè)資源組辨赐，分別是USR_default优俘，SYS_default

創(chuàng)建資源組：
create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;
將當(dāng)前線程加入資源組：
SET RESOURCE GROUP test_resouce_group;
將某個(gè)線程加入資源組：
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;

# 創(chuàng)建資源組
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;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS |  THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| 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可通過(guò)Performance_Schema.threads獲取
mysql> SET RESOURCE GROUP test_resouce_group FOR 60;
Query OK, 0 rows affected (0.00 sec)

# 資源組里有線程時(shí)掀序，刪除資源組報(bào)錯(cuò)
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;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| 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)

# 把資源組里的線程移出到默認(rèn)資源組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. 增加角色管理

角色可以認(rèn)為是一些權(quán)限的集合帆焕，為用戶賦予統(tǒng)一的角色，權(quán)限的修改直接通過(guò)角色來(lái)進(jìn)行不恭，無(wú)需為每個(gè)用戶單獨(dú)授權(quán)叶雹。

# 創(chuàng)建角色
mysql> create role role_test;
Query OK, 0 rows affected (0.03 sec)
 
# 給角色授予權(quán)限
mysql> grant select on db.* to 'role_test';
Query OK, 0 rows affected (0.10 sec)
 
# 創(chuàng)建用戶
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權(quán)限
mysql> grant insert on db.* to 'role_test';
Query OK, 0 rows affected (0.08 sec)
 
# 給角色role_test刪除insert權(quán)限
mysql> revoke insert on db.* from 'role_test';
Query OK, 0 rows affected (0.10 sec)
 
# 查看默認(rèn)角色信息
mysql> select * from mysql.default_roles;
+------+-----------+-------------------+-------------------+
| HOST | USER      | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+-----------+-------------------+-------------------+
| %    | read_user | %                 | role_test         |
+------+-----------+-------------------+-------------------+
1 row in set (0.00 sec)
 
# 查看角色與用戶關(guān)系
mysql> select * from mysql.role_edges;
+-----------+-----------+---------+-----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER   | WITH_ADMIN_OPTION |
+-----------+-----------+---------+-----------+-------------------+
| %         | 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)