mysql8.0的新特性

一.账户与安全

1）用户创建

## 查看版本信息
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)

## 不可以直接使用授权方式进行创建用户
mysql> grant all privileges on *.* to 'wangwu'@'172.16.0.%' identified by '123456';
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 'identified by '123456'' at line 1

## 先创建用户再授权分开执行
mysql> create user 'wangwu'@'172.16.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'wangwu'@'172.16.0.%';
Query OK, 0 rows affected (0.01 sec)

2）加密方式

• mysql5.7默认是方式是mysql_native_password
• mysql8.0默认是caching_sha2_password

二.优化器索引
1）隐藏索引

• 隐藏索引不会被优化器使用，但是仍需要维护
• 主键不可以设置为隐藏索引

## 创建隐藏索引
create index ix_name on tb_name(col) invisible;

## 修改索引模式
alter table tb_name alter index idx_name visible|invisible;

2）降序索引

• mysql8.0开始真正支持降序索引，只有innodb引擎支持降序索引，且必须是btree降序索引，mysql8.0不在对group by操作进行隐式排序

3）函数索引

• mysql8.0开始支持函数索引

三.mysql8.0的CTE

• CTE：公共表达的式，临时结果集
• 类似postgresql的CTE功能，关键字with

四.窗口函数

• MySQL8.0开始支持窗口函数，用法类似postgresql的窗口函数

五.innodb增强

1）秒级添加大表顺序列

• mysql5.7是使用in place算法添加列
• mysql8.0可以指定instant算法添加列，只能顺序加列，仅支持再最后添加列，而不支持在现有列的中间添加列。大表中顺序添加列可以达到秒级

列DDL操作是否支持instant算法

操作	添加列	删除列	重命名	更改顺序列	设置默认值	更改列数据类型	修改varchar列大小	删除列默认值	更改自增值	设置列为null/notnull	修改enum/set列定义
instant	顺序列	×	×	×	√	×	×	√	×	×	√

instant的一些限制：

• 如果alter column包含一些其他的操作
• 只能是顺序列，不能是中间列
• 不支持压缩表
• 不支持包含全文索引的表
• 不支持临时表
• 不支持那些数据在数据字典表空间中创建的表

2）原子ddl操作

• mysql8.0之前版本，基本上有两个数据字典，一个用于服务器层，一个用于innodb层，在某些崩溃情况下，数据字典可能不同步
• mysql8.0只有一个数据字典，确保原子性，崩溃安全DDL
• 字典数据存储在元数据文件和非事务表中，8.0合并在事务性数据字典中

## 在8.0下操作删除表tb_aa和tb_bb，由于tb_bb不存在，而不执行删除操作，tb_aa也不会被删除，保持了原子性
mysql> drop table tb_aa,tb_bb;
ERROR 1051 (42S02): Unknown table 'test.tb_bb'

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_aa          |
+----------------+

## 在mysql5.7下执行，会删除tb_aa
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)

mysql> create table tb_aa(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> drop table tb_aa,tb_bb;
ERROR 1051 (42S02): Unknown table 'test.tb_bb'

mysql> show tables;
Empty set (0.00 sec)

3）自增列持久化，解决主键重复问题

• mysql5.7版本之前，mysql服务器重启，会重新扫描主键最大值，即便之前已删除过id数据，但是新插入数据的id是max(id)+1
• mysql8.0在每次变化的时候，都会将自增计数器的最大值写入redo log,同时在每次检查点将其写入引擎私有的系统表，则不会出现自增主键重复的问题

## 表结构
Create Table: CREATE TABLE `aa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

## mysql5.7版本
mysql> select * from aa;
+----+--------+
| id | userid |
+----+--------+
|  1 |     10 |
|  2 |     11 |
|  3 |     12 |
|  4 |     13 |
|  5 |     14 |
|  6 |     15 |
+----+--------+
6 rows in set (0.00 sec)

mysql> delete from aa where id >4;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from aa;
+----+--------+
| id | userid |
+----+--------+
|  1 |     10 |
|  2 |     11 |
|  3 |     12 |
|  4 |     13 |
+----+--------+
4 rows in set (0.00 sec)

##关闭重启服务器
./mysqladmin -S /data/mysql_log2/mysql.sock -p shutdown

mysql>  insert into aa(userid) values(16),(17),(18);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


## 主键id是从当前最大id开始新增的
mysql> select * from aa;
+----+--------+
| id | userid |
+----+--------+
|  1 |     10 |
|  2 |     11 |
|  3 |     12 |
|  4 |     13 |
|  5 |     16 |
|  6 |     17 |
|  7 |     18 |
+----+--------+
7 rows in set (0.00 sec)

## mysql8.0+下操作
mysql> select * from aa;
+----+--------+
| id | userid |
+----+--------+
|  1 |     10 |
|  2 |     11 |
|  3 |     12 |
|  4 |     13 |
|  5 |     14 |
|  6 |     15 |
+----+--------+
6 rows in set (0.00 sec)

mysql> delete from aa where id >4;
Query OK, 2 rows affected (0.04 sec)

mysql> select * from aa;
+----+--------+
| id | userid |
+----+--------+
|  1 |     10 |
|  2 |     11 |
|  3 |     12 |
|  4 |     13 |
+----+--------+
4 rows in set (0.00 sec)

## 重启mysqld服务，插入新的数据
mysql> insert into aa(userid) values(16),(17),(18);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

## 自增id是从7开始的
mysql> select * from aa;
+----+--------+
| id | userid |
+----+--------+
|  1 |     10 |
|  2 |     11 |
|  3 |     12 |
|  4 |     13 |
|  7 |     16 |
|  8 |     17 |
|  9 |     18 |
+----+--------+
7 rows in set (0.00 sec)

4）死锁检查控制

• innodb_deadlock_detect,新增的动态变量，可用于控制innodb是否执行死锁检查，默认开启。高并发情况下，会影响性能，可以结合innodb_lock_wait_timeout
• mysql8.0重新设计innodb写入redo日志方式，用户线程现在是无锁的，redo写入和刷新由专用后台线程管理，整个redo处理变为事件驱动

mysql> show variables like 'innodb_deadlock%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_lock%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

5）锁定语句选项
nowait以及skip locked使用显式锁定方法，select … for update或者select … for share事务对相同的的行加锁的时候必须等待，直到产生阻塞的时间释放锁。

• nowait:使用了nowait选项的锁定读操作，会立即执行，如果读的记录被锁定了就会报错

• skip locked:使用了skip locked选项的锁定读操作，会立即执行，如果读的记录被锁定了就会从结果集移除改记录。返回数据是非一致性的，不使用与实务中。

• nowait和skip locked只适用于行级锁，而且对基于语句的复制是不安全的

select * from aa where city = '广州' for update nowait;

select * from aa where city = '广州' for update skip locked;

六.json增强

1）内联路径操作符

2）新增json函数

七.字符编码

• mysql8.0开始，utf8mb4作为默认字符集