MySQL8.0 的新特性数据库zhengzhaoxiang的博客-

目录

一、优化器索引

二、InnoDB 增强

三、JSON 增强

四、账户与安全

五、通用表表达式

六、窗口函数

七、开发者功能

一、优化器索引

【1】 隐藏索引（invisible index）：在 MySQL 8.0 中，索引可以被“隐藏”和“显示”。当对索引进行隐藏时，它不会被查询优化器所使用，但仍需要维护。我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉。
【应用场景】：软删除：删除索引，在线上，如果删除错了索引，只能通过创建索引的方式将其添加回来，对于大的数据库而言，是比较耗性能的；为了避免删错，可以先将其设置为不可见，优化器这时候就不会使用它，但是后台仍然在维护，确定后，再删除。
【创建隐藏索引】：命令如下（如果是不隐藏，则不需要后面的 invisible关键字）：

 create index 索引名称 on 表名(字段名) invisible; 

使用 explain语句查看查询优化器对索引的使用情况：

 explain select * from 表名 where 条件;

查询优化器有很多开关，有一个是 use_invisible_indexes(是否使用隐藏索引)，默认是off(不适用)，将其设置成on，即可使用隐藏索引。查看当前查询优化器的所有开关，执行如下命令：

 select @@optimizer_switch;

设置已经存在的索引为可见或者隐藏，执行如下命令：主键不可以设置为隐藏索引。

 alter table 表名 alter index 索引名 visible; alter table 表名 alter index 索引名 invisible;

【2】降序索引（descending index）：MySQL 8.0按降序提供对索引的支持，只有 InnoDB引擎支持降序索引，且必须是 BTREE降序索引。这种索引中的值按降序排列，我们将其向前扫描。在8.0之前，当用户创建降序索引时，我们创建了一个升序索引并向后扫描。一个好处是前向索引扫描比后向索引扫描更快。真正的降序索引的另一个好处是，它使我们能够使用索引而不是文件夹作为ORDER BY具有混合ASC/DESC排序关键部分的子句。降序索引是一个频繁请求的SQL功能。MySQL8.0不在对 group by操作进行隐式排序。
【3】函数索引（functional index）：索引中使用函数表达式，支持 JSON数据节点的索引。函数索引是基于虚拟列的功能实现的假设用户表（tb_user）的的用户登录账号(username)不需要区分大小写，则可以创建一个函数索引：这样在查询的时候就会使用索引。

 create index username_upper_index on tb_user((upper(username))); --函数查询条件 SELECT * FROM tb_user WHERE upper(username) = 'ABD123DSJ';

二、InnoDB 增强

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

三、JSON 增强

【1】内联路径操作符：用”->>“表示去掉抽取结果的”号，下面三种效果是一样的：column->>path 等价于之前的。

 JSON_UNQUOTE( JSON_EXTRACT(column, path) ) JSON_UNQUOTE(column -> path) column->>path

【2】 JSON 聚合函数：MySQL8.0 和MySQL5.7.22 增加了2个聚合函数：
   ■ JSON_ARRAYAGG()：将多行数据组合成 json数组；示例：

 select j_id,JSON_ARRAYAGG(j_name) as j_name from json GROUP BY j_id;

  
   ■ JSON_OBJECTAGG()：用于生成 json对象；示例：

 select j_id,json_objectagg(j_name,j_id) as j_name from json GROUP BY j_id;

  
【注意】：json的聚合函数针对重复key，会使用最后的覆盖前面已有的值，如果下面的 z有2个值，一个2，一个3，使用生成 json的聚合函数的时候，前面的 green会被覆盖掉。
  
【3】JSON 实用函数：json_object() 输出 json数据的时候，格式化。

 select json_object(j_name,j_id,j_id,j_name) as j_name from json;

  json_pretty()输出 json数据的时候，格式化。

 select json_pretty(json_object(j_name,j_id,j_id,j_name)) as j_name from json;

JSON_STORAGE_SIZE() ：json数据所占用的存储空间（单位：字节），
JSON_STORAGE_FREE() ：json数据更新后所释放的空间（单位：字节）；
【4】JSON 合并函数：MySQL8.0 废弃了 JSON_MERGE()函数，推荐使用以下两个函数合并 JSON数据。
   ■ JSON_MERGE_PATCH()，现有的JSON_MERGE()函数在MySQL 8.0中不推荐使用，以消除合并操作的歧义；

 JSON_MERGE('{"a":1,"b":2 }','{"a":3,"c":4 }'); # returns {"a":3,"b":2,"c":4}

   ■ JSON_MERGE_PRESERV()；

  JSON_MERGE_PRESERV('{"a": 1,"b":2}','{"a":3,"c":4}'); # returns {"a":[1,3],"b":2,"c":4}.

上面两个函数都是 JSON数据合并，最大的区别就是前者遇到相同key的时候会用后面的覆盖前面的，后者会都保留，看下面的截图：
【5】JSON 表函数：MySQL8.0新增了JSON_TABLE()函数，将 JSON数据转换成关系表，可以将该函数的返回结果当做一个普通的临时表进行sql查询。

四、账户与安全

【1】用户的创建与授权：在 MySQL5.7的版本：

 grant all privileges on *.* to '用户名'@'主机' identified by '密码'；

在 MySQL8.0需要分开执行：用以前的一条命令在8.0里面创建用户，会出现 sql语法错误。

 create user '用户名'@'主机' identified by '密码'； grant all privileges on *.* to '用户名'@'主机'；

【2】认证插件更新：MySQL5.7默认身份插件是mysql_native_password。MySQL8.0默认的身份插件是caching_sha2_password。查看身份认证插件命令：

 show variables like 'default_authentication_plugin%';

身份认证插件可以通过以下2中方式改变：
   ■ 系统变量 default_authentication_plugin去改变，在 my.ini文件的 [mysqld]下面设置：

 default_authentication_plugin=mysql_native_password

   ■ 如果希望只是某一个用户通过 mysql_native_password的方式认证，可以修改数据库 mysql下面的 user表的字段，执行以下命令：

 alter user '用户名'@'主机' identified width mysql_native_password by '密码';

【3】密码管理：MySQL8.0的密码管理策略有3个变量：
   ■ password_history 修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制；
   ■ password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重复，默认是0,即不限制；
   ■ password_require_current 修改密码是否需要提供当前的登录密码，默认是OFF，即不需要；如果需要，则设置成ON；
查询当前MySQL密码管理策略相关变量，使用以下命令：

 show variables like 'password%';

①、设置全局的密码管理策略，在 my.ini配置文件中，设置以上3个变量的值这种设置方式，需要重启mysql服务器；某些生产环境不允许重启，MySQL8.0提供了关键字 persist持久化，执行以下命令：

 set persist password_history=6;

这条命令会在数据目录下生成新的配置文件（/var/lib/mysql/mysqld-auto.cnf），下次服务器重启的时候除了读取全局配置文件，还会读取这个配置文件，这条配置就会被读入从而达到持久化的目的：
②、针对某一个用户单独设置密码管理策略：

 alter user '用户名'@'主机' password history 5;

这样，这个用户的 password_history 就被设置成了5，查看一下：

 show user,host,Password_reuse_history from user;

查看某一张的字段的所有字段，使用以下命令:

 desc 表名;

【4】角色管理：一组权限的集合，一组权限赋予某个角色，再把某个角色赋予某个用户，那用户就拥有角色对应的权限：
   ■ 创建一个角色

 create role '角色1';

   ■ 为这个角色赋予相应权限

 grant insert,update on *.* to '角色1';

   ■ 创建一个用户

 create user '用户1' identified by '用户1的密码';

   ■ 为这个用户赋予角色的权限，执行完上面4步，用户1就拥有了插入与更新的权限。

 grant '角色1' on *.* to '用户1'；

   ■ 再创建1个用户

 create user '用户2' identified by '用户2的密码';

   ■ 为这个用户赋予同样的角色，执行完上面2步，用户2也用了角色1的权限，即插入与更新

 grant '角色1' on *.* to '用户2';

   ■ 查看用户权限，执行以下命令：

 show grants for '用户名';

   ■ 启用角色，设置了角色，如果不启用，用户登录的时候，依旧没有该角色的权限

 set default role '角色名' to '用户名';

   ■ 如果一个用户有多个角色，使用以下命令。MySQL中与用户角色相关的表：mysql.default_roles、mysql.role_edges，有兴趣的朋友可以进去查看下。

 set default role all to '用户名';

   ■ 撤销权限

 revoke insert,update on *.* from '角色名';

五、通用表表达式

通用表表达式(Common Table Expressions CTE)：在复杂的查询中使用嵌入式表时，使用 CTE 使得查询语句更清晰。
【1】 非递归 CTE：派生表：select * from (select 1) as dt; 通用表表达式：with cte as (select 1) select * from cte;

 with cte1(id) as (select 1),cte2 as (select id+1 from cte1) select * from cte1 join cte2;

【2】递归 CTE：公用表表达式（CTE）递归 CTE是一组迭代构建的行：从最初的一组行开始，一个进程派生新的行，然后将这些新的行重新输入到进程中，产生更多的行，等等，直到该过程不再生成行。CTE是一个通常需要的SQL功能。

六、窗口函数

从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中。即窗口函数不需要 GROUP BY。
窗口函数有两种形式：用作窗口函数和专用窗口函数的 SQL聚合函数。这是 MySQL中支持窗口化的集合函数集合：COUNT，SUM，AVG，MIN，MAX，BIT_OR，BIT_AND，BIT_XOR，STDDEV_POP（及其同义词STD，STDDEV），STDDEV_SAMP，VAR_POP（及其同义词VARIANCE）和 VAR_SAMP。这组专门的窗口函数是：RANK，DENSE_RANK，PERCENT_RANK，CUME_DIST，NTILE，ROW_NUMBER，FIRST_ ALUE，LAST_VALUE，NTH_VALUE，LEAD和LAG 对窗口函数（又名分析函数）的支持是一种频繁的用户请求。窗口函数一直是标准SQL（SQL 2003）的一部分。在这里可以看到Dag Wanvik的  博客文章以及Guilhem Bichot 在这里的博客文章。

七、开发者功能

【1】字符集：MySQL 开发人员需要新功能，而 MySQL 8.0在诸如 SQL，JSON，正则表达式和 GIS等领域提供了许多新的和更多需求的功能。开发人员也希望能够存储Emojis，因此 UTF8MB4现在是 8.0中的默认字符集。最后，数据类型得到了改进，在 BINARY数据类型上进行了按位操作，并且改进了 IPv6和 UUID功能。
【2】性能：MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。MySQL 8.0 在以下方面带来了更好的性能：读/写工作负载、IO 密集型工作负载、以及高竞争（”hot spot”热点竞争问题）工作负载。
 
【3】NoSQL：MySQL 从 5.7 版本开始提供 NoSQL 存储功能，目前在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。
   
【4】高可用性(High Availability)：InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。
【5】NOWAIT和SKIP LOCKED：MySQL的8.0提供了 NOWAIT与 SKIP LOCKED该 SQL锁定子句中的替代品。通常，当某行由于某个 UPDATE或某一行而被锁定时 SELECT … FOR UPDATE，任何其他事务都必须等待释放才能访问该锁定的行。在某些使用情况下，如果行被锁定或忽略锁定行，则需要立即返回。使用锁定子句 NOWAIT永远不会等待获取行锁。相反，查询将失败并显示错误。使用锁定子句 SKIP LOCKED永远不会等待获取列出的表上的行锁。相反，锁定的行将被跳过并且不会被读取。
【拓展】：问题：native连接 mysql8.0 时报1251错误？原因是因为上面说的用户加密规则修改的原因。解决办法一：可以升级native的 jar包。解决办法二：通过命令的方式登录 mysql 执行如下命令：

 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';

—-如果喜欢，点个 红心♡ 支持以下，谢谢—-