mysql8的安装及新特性

迷南。 2023-01-14 09:59 199阅读 0赞

`简介`
    官方表示 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），下次服务器重启的时候除了读取全局配置文件，还会读取这个配置文件,这条配置就会被读入从而达到持久化的目的

![配置文件生产][20210420145447539.png]

`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查询。

[20210420145447539.png]: /images/20221022/5c721ed5be46423bb1e77a2619a56f93.png