mysql8的安装及新特性

`简介`
官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍。MySQL 8.0 上做了许多重大更新，在往企业级数据库的路上大步前行，全新 Data Dictionary 设计，支持 Atomic DDL，全新的版本升级策略，安全和账号管理加强，InnoDB 功能增强等。

1、mysql8安装

`centos`
#1、下载安装包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar
#2、解压
tar xvf mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar
#3、安装
yum localinstall mysql-common-*
#4、启动
systemctl start mysqld
`ubuntu`
#1、下载安装包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-server_8.0.23-1ubuntu20.04_amd64.deb-bundle.tar
#2、解压
tar xvf mysql-server_8.0.23-1ubuntu20.04_amd64.deb-bundle.tar
#3、安装
dpkg -i *.deb
#若安装失败执行
apt-get -f -y install
#4、启动
systemctl start mysql

2、修改密码

MySQL使用alter命令来进行修改，而且由于密码策略也变化了，设置密码时需要达到一定的复杂程度才行，一般是需要大小字母、特殊字符和数字。
mysql> create user mysql8 identified by 'Mysql@888';
Query OK, 0 rows affected (0.01 sec)

mysql> alter user mysql8 identified by 'Mysql@666';
Query OK, 0 rows affected (0.01 sec)

3、认证插件

MySQL5.7默认身份插件是mysql_native_password；MySQL8.0默认的身份插件是caching_sha2_password
mysql> show variables like 'default_authentication_plugin%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.02 sec)

4、密码管理

MySQL8.0的密码管理策略有3个变量：
password_history 修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制
password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重复，默认是0,即不限制
password_require_current 修改密码是否需要提供当前的登录密码，默认是OFF,即不需要；如果需要，则设置成ON
mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| password_history         | 0     |
| password_require_current | OFF   |
| password_reuse_interval  | 0     |
+--------------------------+-------+
3 rows in set (0.00 sec)

1)设置全局的密码管理策略，在my.cnf配置文件中，设置以上3个变量的值这种设置方式，需要重启mysql服务器；某些生产环境不允许重启，MySQL8.0提供了关键字persist,持久化，执行以下命令：
mysql> set persist password_history=6;
Query OK, 0 rows affected (0.00 sec)
这条命令会在数据目录下生成新的配置文件（/var/lib/mysql/mysqld-auto.cnf），下次服务器重启的时候除了读取全局配置文件，还会读取这个配置文件,这条配置就会被读入从而达到持久化的目的

2)针对某一个用户单独设置密码管理策略
mysql> alter user mysql8 password history 5;
Query OK, 0 rows affected (0.01 sec)
这样，这个用户的password_history 就被设置成了5,查看一下：
mysql> select user,host,Password_reuse_history from mysql.user;
+------------------+-----------+------------------------+
| user             | host      | Password_reuse_history |
+------------------+-----------+------------------------+
| mysql8           | %         |                      5 |
| root             | %         |                   NULL |
| mysql.infoschema | localhost |                   NULL |
| mysql.session    | localhost |                   NULL |
| mysql.sys        | localhost |                   NULL |
| root             | localhost |                   NULL |
+------------------+-----------+------------------------+
6 rows in set (0.00 sec)

5、角色管理

角色：一组权限的集合
一组权限赋予某个角色，再把某个角色赋予某个用户，那用户就拥有角色对应的权限
1)创建一个角色
`create role '角色名';`
mysql> create role dbinsert;
Query OK, 0 rows affected (0.00 sec)
2)为这个角色赋予相应权限
`grant insert on *.* to '角色名';`
mysql> grant insert on *.* to dbinsert;
Query OK, 0 rows affected (0.00 sec)
3)为这个用户赋予角色的权限
`grant '角色' on *.* to '用户';`
mysql> grant dbinsert to mysql8;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4)启用角色,设置了角色，如果不启用，用户登录的时候，依旧没有该角色的权限
`set default role '角色名' to '用户名';`
mysql> set default role dbinsert to mysql8;
Query OK, 0 rows affected (0.00 sec)
5)如果一个用户有多个角色，使用以下命令
`set default role all to '用户名';`
mysql> set default role  all to mysql8;
Query OK, 0 rows affected (0.00 sec)
6)撤销权限
`revoke insert,update on *.* from '角色名';`

6、隐藏索引

1)创建隐藏索引
`create index 索引名称 on 表名(字段名) invisible;`
mysql> create index idx_id on tb1(id) invisible;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
2)查看索引
`show index from 表名；`
mysql> show index from tb1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb1   |          1 | idx_id   |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
3)使用explain语句查看查询优化器对索引的使用情况
`explain select * from 表名 where 条件;`
说明：查询优化器有很多开关，有一个是use_invisible_indexes(是否使用隐藏索引),默认是off(不适用)，将其设置成on,即可使用隐藏索引。
4)查看当前查询优化器的所有开关变脸
`select @@optimizer_switch;`
mysql> select @@optimizer_switch;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5)设置已经存在的索引为可见或者隐藏
`alter table 表名 alter index 索引名 visible;
 alter table 表名 alter index 索引名 invisible;`
 说明：主键不可以设置为隐藏所以。

7、降序索引

MySQL8.0开始真正支持降序索引，只有InnoDB引擎支持降序所以，且必须是BTREE降序索引，MySQL8.0不在对group by操作进行隐式排序。

8、函数索引

索引中使用函数表达式
支持JSON数据节点的索引
函数索引是基于虚拟列的功能实现的
假设表（tb1）的(name)不需要区分大小写，则可以创建一个函数索引。
`create index 索引名 on 表名((upper(字段名)))`
mysql> create index username_upper_index on tb1((upper(name)));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
以下查询就会用到创建的函数索引
mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | mike |
|    2 | jone |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tb1 WHERE upper(name) = 'MIKE';
+------+------+
| id   | name |
+------+------+
|    1 | mike |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb1 WHERE upper(name) = 'MIKe';
+------+------+
| id   | name |
+------+------+
|    1 | mike |
+------+------+
1 row in set (0.00 sec)

9、InnoDB增强

1.集成数据字段
2.原子ddl操作
MySQL5.7执行drop命令 drop table t1,t2; 如果t1存在，t2不存在，会提示t2表不存在，但是t1表仍然会被删除。
MySQL8.0执行同样的drop命令，会提示t2表不存在，而且t1表不会被删除，保证了原子性。
说明：ddl操作（针对表）的原子性前提是该表使用的存储引擎是InnoDB
3.自增列持久化
MySQL5.7及其以前的版本，MySQL服务器重启，会重新扫描表的主键最大值，如果之前已经删除过id=100的数据，但是表中当前记录的最大值如果是99，那么经过扫描，下一条记录的id是100，而不是101。
MySQL8.0则是每次在变化的时候，都会将自增计数器的最大值写入redo log,同时在每次检查点将其写入引擎私有的系统表。则不会出现自增主键重复的问题。
4.死锁检查控制
5.锁定语句选项

10、JSON聚合函数

1)JSON_ARRAYAGG()
JSON_ARRAYAGG(),将多行数据组合成json数组
mysql> select * from tb1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | mike  |
|    2 | jone  |
|    1 | alice |
|    2 | amy   |
+------+-------+
4 rows in set (0.00 sec)
mysql> select id,json_arrayagg(name) as attributes from tb1 group by id;
+------+-------------------+
| id   | attributes        |
+------+-------------------+
|    1 | ["mike", "alice"] |
|    2 | ["jone", "amy"]   |
+------+-------------------+
2 rows in set (0.00 sec)
2)2)JSON_OBJECTAGG()
JSON_OBJECTAGG()，用于生成json对象
mysql> select id,json_objectagg(id,name) as attributes from tb1 group by id;
+------+----------------+
| id   | attributes     |
+------+----------------+
|    1 | {"1": "alice"} |
|    2 | {"2": "amy"}   |
+------+----------------+
2 rows in set (0.00 sec)
说明：json的聚合函数针对重复key,会使用最后的覆盖前面已有的值。

11、JSON实用函数

1)JSON_PRETTY(
JSON_PRETTY()  输出json数据的时候，格式化
mysql> select json_object('id',1,'name','mike');
+-----------------------------------+
| json_object('id',1,'name','mike') |
+-----------------------------------+
| {"id": 1, "name": "mike"}         |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select json_pretty(json_object('id',1,'name','mike'));
+------------------------------------------------+
| json_pretty(json_object('id',1,'name','mike')) |
+------------------------------------------------+
| {
  "id": 1,
  "name": "mike"
}                |
+------------------------------------------------+
1 row in set (0.00 sec)
2)JSON_STORAGE_SIZE() json数据所占用的存储空间（单位：字节）
3)JSON_STORAGE_FREE() json数据更新后所释放的空间（单位：字节）

12、JSON合并函数

MySQL8.0废弃了JSON_MERGE()函数，推荐使用以下两个函数合并JSON数据
1)JSON_MERGE_PATCH()
说明：JSON数据合并，遇到相同key的时候会用后面的覆盖前面的
2)JSON_MERGE_PRESERV()
说明：JSON数据合并，遇到相同key的时候会保留前面的
mysql> select json_merge_patch('{"a":1,"b":2}','{"a":3,"c":4}');
+---------------------------------------------------+
| json_merge_patch('{"a":1,"b":2}','{"a":3,"c":4}') |
+---------------------------------------------------+
| {"a": 3, "b": 2, "c": 4}                          |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_merge_preserve('{"a":1,"b":2}','{"a":3,"c":4}');
+------------------------------------------------------+
| json_merge_preserve('{"a":1,"b":2}','{"a":3,"c":4}') |
+------------------------------------------------------+
| {"a": [1, 3], "b": 2, "c": 4}                        |
+------------------------------------------------------+
1 row in set (0.00 sec)

13、JSON表函数

MySQL8.0新增了JSON_TABLE()函数，将JSON数据转换成关系表，可以将该函数的返回结果当做一个普通的临时表进行sql查询。