MySQL 5与MySQL 8的区别

  在这些年的MySQL升级需求中，让我大跌眼镜的一个现象是：驱动业务从MySQL 5.5升级到MySQL 5.7的很大一个因素是因为JSON这个特性。

而让业务有所顾虑从MySQL 5.7升级到MySQL 8.0的一个主要原因是因为驱动版本升级，所以对于MySQL 5.7升级到MySQL 8.0来说，总体的升级动力明显要低一些，但是规划的一个优点就是可以把一些工作前置，或者让它的推行更加顺畅，比如我们对于新业务的推行，都是默认按照MySQL 8.0的方案来做。

 如果要说MySQL 5.7升级到MySQL 8.0的一些差异，从我的角度来说，其实变化是很大的，但是细数盘点，很多特性似乎是对于业务的一种友好或者透明支持。

1、创建表：

比如我们在MySQL 5.7版本中全面推行GTID，所以之前的create table xxx as select * from xx的使用模式就不奏效了，进而我们建议使用：

create table xxx like xxxxx;

insert into xxx select * from xxxxx;

这种使用模式，而MySQL8.0带来的很多特性是在体验和性能改造方面，原来不建议使用的模式竟然可以支持了，而很多业务侧是后知后觉，原本已经培养的习惯，让我们有些凌乱。

2、窗口函数：

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

其实就会发现不光是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

3、表结构：

这里顺便吐槽下airflow的表结构配置

airflow的一个表结构在MySQL 5.7中如下：

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.06 sec)

在MySQL中其实会被默认转换为如下的表结构：

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;

如果查看在线业务的实际数据如下：

mysql> select * from kube_resource_version;
+------------+------------------+
| one_row_id | resource_version |
+------------+------------------+
|          1 |                  |
+------------+------------------+
1 row in set (0.01 sec)

看起来这个boolean类型真是有些鸡肋，在数据库中已经默认使用tinyint(1)来间接转义了，但是实际上还是不对味。

带来的问题是在MySQL 5.7中可以成功创建，但是在8.0会报错：

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'.

而经过分析，其实8.0的报错提示更加合理，至少我觉得8.0对于数据层面的要求确实变高了。

4、state和info列的变化：

在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

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

5、 NoSql存储

Mysql从5.7 版本提供了NoSQL的存储功能,在8.0中这部分得到一些修改,不过这个在实际中用的极少

6、隐藏索引

隐藏索引的特性对于性能调试非常有用,在8.0 中,索引可以被隐藏和显示,当一个索引隐藏时,他不会被查询优化器所使用

也就是说可以隐藏一个索引,然后观察对数据库的影响.如果性能下降,就说明这个索引是有效的,于是将其”恢复显示”即可;如果数据库性能看不出变化,说明这个索引是多于的,可以删掉了

隐藏一个索引的语法

1	ALTER TABLE t ALTER INDEX i INVISIBLE;

恢复显示该索引的语法是：

1	ALTER TABLE t ALTER INDEX i VISIBLE;

当一个索引被隐藏时,我们可以从show index命令的输出汇总看出,该索引visible属性值为No

**注意:**当索引被隐藏时,他的内容仍然是和正常索引一样实时更新的,这个特性本身是专门为了优化调试而使用的,如果你长期隐藏一个索引,那还不如干掉,因为索引的存在会影响数据的插入\更新和删除功能

7、设置持久化

MySQL 的设置可以在运行时通过 SET GLOBAL 命令来更改，但是这种更改只会临时生效，到下次启动时数据库又会从配置文件中读取。
MySQL 8 新增了 SET PERSIST 命令，例如：
SET PERSIST max_connections = 500;
MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。

8、UTF-8 编码

从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4，这个编码包含了所有 emoji 字符。多少年来我们使用 MySQL 都要在编码方面小心翼翼，生怕忘了将缺省的 latin 改掉而出现乱码问题。从此以后就不用担心了。

9、通用表表达式（Common Table Expressions）

复杂的查询会使用嵌入式表，例如：

1 2 3

SELECT t1.*, t2.* FROM      (SELECT col1 FROM table1) t1,      (SELECT col2 FROM table2) t2;

而有了 CTE，我们可以这样写：

1 2 3 4 5

WITH  t1 AS (SELECT col1 FROM table1),  t2 AS (SELECT col2 FROM table2) SELECT t1.*, t2.* FROM t1, t2;

这样看上去层次和区域都更加分明，改起来也更清晰的知道要改哪一部分。
这个特性在很多报表场景是很有用的，也是mysql优化的一个很重要特性。
1.235窗口函数（Window Functions）
MySQL 被吐槽最多的特性之一就是缺少 rank() 函数，当需要在查询当中实现排名时，必须手写 @ 变量。但是从 8.0 开始，MySQL 新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。
窗口函数有点像是 SUM()、COUNT() 那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。也就是说，窗口函数是不需要 GROUP BY 的。
假设我们有一张 “班级学生人数” 表：

如果要对班级人数从小到大进行排名，可以这样利用窗口函数：

说明：在这里创建了名为 w 的 window，规定它对 stu_count 字段进行排序，然后在 select 子句中对 w 执行 rank() 方法，将结果输出为 rank 字段。
这个特性也是Oracle11g有的一个新特性，在优化也是起着很重要的作用。