科普文：软件架构数据库系列之【MySQL5.7和MySQL 8.0的差异】

引言

MySQL作为最常用的开源关系型数据库管理系统之一，一直在不断发展和改进。随着时间的推移，MySQL也经历了多个版本的演进，每个版本都带来了一系列重要的更新和改进。其中，MySQL 5.7和MySQL 8是两个备受关注的版本，它们之间存在一些关键的差异。

本文将深入探讨这两个版本之间的主要差异，以帮助开发人员和数据库管理员决定是否升级到MySQL 8，并了解升级后可能遇到的挑战。

总的来说，MySQL 8为那些希望获得更好性能、安全性和功能的用户提供了一个强大的选择。

通过深入了解MySQL 5.7与MySQL 8之间的差异，开发人员和数据库管理员可以更明智地决定是否升级到MySQL 8，以满足他们的特定需求。尽管升级可能需要一些工作，但它为那些希望在数据库管理方面获得更多优势的用户提供了一个有前途的选择。

1. 数据字典和系统表的变化

MySQL 5.7和MySQL 8之间最明显的差异之一是数据字典的变化。MySQL 8引入了新的数据字典架构，用于管理数据库的元数据信息。这一变化对于数据库的管理和性能都具有深远的影响。

在MySQL 5.7及以前的版本中，系统表被用于存储数据库的元数据信息。这些系统表具有一定的限制，包括性能瓶颈和可扩展性的问题。而MySQL 8通过引入新的数据字典解决了这些问题。数据字典提高了元数据的存储效率，降低了元数据访问的成本，并使MySQL更容易扩展和维护。

数据字典的另一个优势是用户可以通过SQL语句来查询元数据信息，而不再依赖于特定的系统表。这使得管理数据库的元数据变得更加灵活和方便。

1.1 表结构配置-bool类型

示例：建表时bool类型在mysql8.4下报错，但是在MySQL5.7下成功。

mysql> CREATE TABLE kube_resource_version  (one_row_id BOOL NOT NULL DEFAULT true, resource_version VARCHAR(255),  PRIMARY KEY (one_row_id),  CONSTRAINT kube_resource_version_one_row_id CHECK (one_row_id),  CHECK (one_row_id IN (0, 1)));
Query OK, 0 rows affected (0.04 sec)

mysql>  -- MySQL5.7中BOOL 其实会被默认转换tinyint
mysql> show create table  kube_resource_version \G
*************************** 1. row ***************************
       Table: kube_resource_version
Create Table: CREATE TABLE `kube_resource_version` (
  `one_row_id` tinyint(1) NOT NULL DEFAULT '1',
  `resource_version` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`one_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql>
mysql>-- MySQL8.4 中直接报错 ERROR 3812 (HY000)


mysql> CREATE TABLE kube_resource_version  (one_row_id BOOL NOT NULL DEFAULT true, resource_version VARCHAR(255),  PRIMARY KEY (one_row_id),  CONSTRAINT kube_resource_version_one_row_id CHECK (one_row_id),  CHECK (one_row_id IN (0, 1)));
ERROR 3812 (HY000): An expression of non-boolean type specified to a check constraint 'kube_resource_version_one_row_id'.
mysql>

1.2 重构BLOB

重构 BLOB 加速了片段读取/更新操作，可以加速 JSON 数据的操作。大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。

1.3 隐藏索引

 可以将索引通过命令设置为 隐藏 或 显示。对于被 隐藏 的索引，它不会被查询优化器所使用，我们可以使用这一功能，对相关的查询进行性能调试，通过 隐藏 或 显示，分析数据库性能差异的原因，同时也可以去除无效的索引。

1.4 事务性数据字典

完全脱离了 MyISAM 存储引擎，真正将数据字典放到了 InnoDB 中的一些表中，不再需要 FRM、TRG、PAR 等文件；Information Schema 现在以数据字典表的一个视图出现。也就是原则上可以不需要 MyISAM 数据表类型，系统表都可以放到 InnoDB 之中。

1.5 show processlist

在MySQL里面如果对一张大表做delete,真是一件让人尴尬的事情，在MySQL 5.7里面有点后知后觉，在show processlist的输出中。State和Info列分别显示：

Executing event 和delete from xxxxx

同时Seconds_Behind_Master显示为0，实际上数据已经产生大量延迟了。

而相反在MySQL 8.0里面，State和Info列分别显示：

Applying batch of row changes (delete)和delete from xxxxx

可以明确的提示出批量操作，当然这延迟确实不体面，真是非常大。

2. JSON支持的改进

MySQL 5.7引入了对JSON数据类型的支持，但MySQL 8进一步改进了这一特性。MySQL 8支持更多的JSON函数和运算符，使得在处理和查询JSON数据时更加灵活。

MySQL 8还引入了一种新的二进制数据类型，JSON，用于存储JSON文档。这种新的数据类型可以帮助开发人员更好地处理和存储半结构化数据。此外，MySQL 8还改进了对JSON路径表达式的支持，这使得在JSON文档中查找和提取数据变得更加容易。

  MySQL 从 5.7 版本开始提供 NoSQL 存储功能，目前在 8.0 版本中这部分功能也得到了更大的改进。该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。

3. 新的数据类型

MySQL 8引入了一些新的数据类型，为开发人员提供了更多的选择。其中一个重要的新数据类型是GEOMETRY，用于处理地理空间数据。这个数据类型使得存储和查询地理信息变得更加容易。还有一个新的数据类型是UUID，用于存储全局唯一标识符。这对于需要在多个系统之间唯一标识数据的应用程序非常有用。

这些新的数据类型扩展了MySQL的功能，使其更适合不同类型的应用程序和用例。

4. 安全性增强

随着网络犯罪日益猖獗，安全性对于数据库管理至关重要。MySQL 8引入了一系列新的安全特性，以提高数据库的安全性。

首先，MySQL 8引入了加密连接的支持。这意味着数据在传输过程中会被加密，从而保护数据免受窃听和中间人攻击的威胁。

此外，MySQL 8还引入了密码策略，可以强制用户使用更强的密码，从而增加了数据库的安全性。用户可以根据自己的需求自定义密码策略。

最重要的是，MySQL 8改进了身份验证方法。新的身份验证插件提供了更强大的安全性，可以有效地防止未经授权的访问。

4.1认证插件更新

MySQL5.7 默认身份插件是 mysql_native_password MySQL8.0 默认的身份插件是 caching_sha2_password 查看身份认证插件命令：show variables like 'default_authentication_plugin%';

mysql> show variables like '%plugi%';
+-------------------------------+-------------------------------------------------------+
| Variable_name                 | Value                                                 |
+-------------------------------+-------------------------------------------------------+
| default_authentication_plugin | mysql_native_password                                 |
| plugin_dir                    | D:\Program Files\mysql-5.7.21-winx64-3307\lib\plugin\ |
+-------------------------------+-------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> show variables like '%plugi%';
+-----------------------------------------------+------------------------------------------------------+
| Variable_name                                 | Value                                                |
+-----------------------------------------------+------------------------------------------------------+
| plugin_dir                                    | D:\Program Files\mysql-8.4.2-winx64-3308\lib\plugin\ |
| replication_optimize_for_static_plugin_config | OFF                                                  |
+-----------------------------------------------+------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

身份认证插件可以通过以下2中方式改变：　 
1）系统变量default_authentication_plugin去改变，在my.ini文件的[mysqld]下设置default_authentication_plugin=mysql_native_password
 2）如果希望只是某一个用户通过mysql_native_password的方式认证，可以修改数据库mysql下面的user表的字段，执行以下命令： mysql> alter user '用户名'@'主机' identified with mysql_native_password by '密码';

4.2 SQL角色

可以创建角色，给用户设置或去除角色，大大方便权限的管理。

5. 性能改进

性能一直是数据库管理的重要关注点。MySQL 8带来了一系列性能改进，以提高数据库的响应能力。

首先，MySQL 8改进了查询优化器。新的查询优化器使用了一种新的执行计划生成器，可以更好地处理复杂查询。这意味着查询在执行时更加高效，可以提供更快的响应时间。

此外，MySQL 8引入了新的多线程复制机制。这一机制可以提高数据复制的速度，从而减少了主从复制之间的延迟。对于那些需要处理大量事务的应用程序，这是一个重要的性能改进。

官方表示MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。MySQL 8.0 在读/写工作负载、IO 密集型工作负载、以及高竞争工作负载时相比MySQL5.7有更好的性能

6. InnoDB存储引擎的改进

InnoDB存储引擎一直是MySQL的默认存储引擎，而MySQL 8进一步改进了它的性能和稳定性。

首先，MySQL 8引入了新的InnoDB事务日志格式，称为"redo log"。这个新的事务日志格式提高了并发性和可伸缩性，从而允许更多的并发操作。

此外，InnoDB现在支持全文本搜索。这意味着开发人员可以在使用InnoDB存储引擎的应用程序中进行全文本搜索，而不必依赖于其他存储引擎。

7.mysql5.7字符编码的问题

mysql5.7和之前版本，默认字符集为latin1，直接插入中文字符的时候会出现乱码的情况。

所以在安装5.7及之前的版本的mysql的时候要把字符编码修改为utf8字符集，utf8字符集指的是utf8mb3。

从mysql8.0开始，数据库默认字符编码改为utf8mb4。

查看编码命令：

 show variables like 'character_%';  
 show variables like 'collation_%';

8.用户的创建与授权

在MySQL5.7的版本：

 grant all privileges on *.* to 'bertram'@'localhost' identified by '1q2w3e4r';; #grant all privileges on *.* to '用户名'@'主机' identified by '密码'；

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

 mysql> create user 'bertram'@'localhost' identified by '1q2w3e4r';  
Query OK, 0 rows affected (0.04 sec)   
#create user '用户名'@'主机' identified by '密码'；  ​ 
mysql> grant all privileges on *.* to 'bertram'@'localhost'; 
 Query OK, 0 rows affected (0.04 sec)   
#grant all privileges on . to '用户名'@'主机'；

 注意事项：
 1）、8.0系已无query_cahe参数，注释参数

 2）、授权时需要分开授权，不能用grant 建立用户
   CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; 
  -- 给用户分配权限 
  grant all privileges on *.* to 'myuser'@'%' ; 
  -- 单独授予某种权限的写法;具体再详细的操作请参考其他文章 
  GRANT SELECT ON oilsystem.input TO 'myuser'@'%'; 
  -- 刷新权限，使操作生效 
   FLUSH PRIVILEGES;  
 3)、第一次修改root账号密码此采用alter，
不能用此方式，8系已不支持 
update user set password=password("123456") where user="root"; 
set password=password('dengzl123'); 
正确修改： 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'dengzl123'; 

9.窗口函数

在MySQL 5.7中字段名为rank是可以的，但是在8.0中因为有了窗口函数，字段名为rank就报错，顺着这个思路，其实我们一窥窗口函数。

可参考：科普文：软件架构数据库系列之【MySQL8.0窗口函数实践小结】-CSDN博客

新增的窗口函数如下： 
窗口函数和hive的窗口函数类似 

其实就会发现不光是rank,字段名是first_value也不可以了，随之带来的就是SQL语法错误，可能会让人开始有点抓不着头脑。

create table test3(id int primary key,first_value varchar(30));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'first_value varchar(30))' at line 1

准备建表和准备数据： 

按照课程分组， 

10.持久化设置

MySQL8.0 新增 SET PERSIST 的命令，该命令的配置值保存到数据目录下的mysqld-auto.cnf文件中，待重启后，读取该文件，用其中的配置覆盖缺省的配置文件，补充了SET GLOBAL 命令只能临时生效的不足；
命令的使下如：

11.允许跳过锁等待

select ... for update，select ... for share(8.0新增语法) 添加 NOWAIT、SKIP LOCKED语法，跳过锁等待，或者跳过锁定。
session1

session2

session3