Mysql8.0的特性

最新推荐文章于 2024-09-11 08:43:09 发布

胡尚

最新推荐文章于 2024-09-11 08:43:09 发布

阅读量203

点赞数

分类专栏：深入学习总结笔记 Mysql 文章标签：数据库 sql sqlserver

本文链接：https://blog.csdn.net/qq_44027353/article/details/129394283

版权

深入学习总结笔记同时被 2 个专栏收录

115 篇文章 8 订阅

订阅专栏

Mysql

25 篇文章 0 订阅

订阅专栏

MySQL8.0引入了多项新特性，如降序索引、GROUPBY不再隐式排序、隐藏索引、函数索引、死锁检查控制、binlog日志过期时间精确到秒、窗口函数以及默认字符集从latin1变为utf8mb4。此外，还改进了InnoDB存储引擎，支持DDL原子化操作，并允许参数修改持久化。

摘要由CSDN通过智能技术生成

Mysql8.0的特性

建议使用8.0.17及之后的版本，更新的内容比较多。

新增降序索引

-- 如下所示，我们可以在创建索引时 在字段名后面指定desc进行降序排序
create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));

group by 不再隐式排序

mysql5.7的版本我们在进行分组时会自动的对分组字段先进行排序操作，但是8.0版本就去除了这个功能，目的是在进行表迁移时其他数据库没有这个功能，进而出现一个业务问题，所以8.0版本就做到了和其他数据库统一。
```
-- 8.0版本group by不再默认排序，需要自己加order by
select count(*),c2 from t1 group by c2 order by c2;  
```

增加隐藏索引

有两种应用场景可以使用隐藏索引：不确定某个索引是否还在使用进而不确定是否要删除，或者是新创建索引不确定线上环境是否能用。

如果想删除某个索引，可以先改为隐藏索引，这个时候隐藏索引是不会被sql语句使用，但还是会进行维护，这样如果此索引还在使用就能立刻改回来进行使用，避免了因为数据量大创建索引的耗时操作。

 -- 在创建索引的后面加上invisible关键字
 create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);
 
 -- 这个时候此索引是不会被使用的，我们可以通过修改一个参数  让我们当前session会话能够使用隐藏索引
 select @@optimizer_switch\G   -- 查看各种参数
 
 -- 然后将use_invisible_indexes参数改为on  前面需要加session哦 在会话级别设置查询优化器可以看到隐藏索引
 set session optimizer_switch="use_invisible_indexes=on";  
 
 
 -- 对已存在的索引进行修改   
 alter table t2 alter index idx_c2 visible;
 alter table t2 alter index idx_c2 invisible;

新增函数索引

就是在创建索引时，索引树中存储的其实是经过函数运算后的值，当然在使用这个索引时where条件中的字段也就必要使用函数了
```
create index idx_c1 on t3(c1);     -- 创建普通索引
create index func_idx on t3((UPPER(c2)));  -- 创建一个大写的函数索引
```

innodb存储引擎select for update跳过锁等待

对于select … for share(8.0新增加查询共享锁的语法)或 select … for update

在语句后面添加NOWAIT、SKIP LOCKED语法可以跳过锁等待，或者跳过锁定。

5.7的版本中如果发生了锁等待就会一直等待直到innodb_lock_wait_timeout超时。而8.0版本可以可以添加NOWAIT关键字不需要等到直接报错，添加SKIP LOCKED关键字就会跳过当前正在加锁的数据，返回其他未加锁的数据

-- session1 给c2字段加一个行锁
update t1 set ... where c1 = 2; 

-- 另一个session2  这里就会等待超时
mysql> select * from t1 where c1 = 2 for update;   
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 查询立即返回
mysql> select * from t1 where c1 = 2 for update nowait;   
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

-- 查询立即返回，过滤掉了第二行记录
mysql> select * from t1 for update skip locked;

新增innodb_dedicated_server自适应参数

当服务器仅仅只安装了mysql服务，才可以使用此参数，它会根据服务器当前空闲性能自动自动配置innodb_buffer_pool_size，innodb_log_file_size等参数。

如果当前服务器还有其他中间件服务则不建议开启，否则会影响其他程序

mysql> show variables like '%innodb_dedicated_server%';   --默认是OFF关闭，修改为ON打开
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_dedicated_server | OFF   |
+-------------------------+-------+
1 row in set (0.02 sec)

死锁检查控制

MySQL 8.0增加了一个新的动态变量 innodb_deadlock_detect，用于控制系统是否执行 InnoDB 死锁检查，默认是打开的。死锁检测会耗费数据库性能的，对于高并发的系统，我们可以关闭死锁检测功能，提高系统性能。但是我们要确保系统极少情况会发生死锁，同时要将锁等待超时参数调小一点，以防出现死锁等待过久的情况。
```
mysql> show variables like '%innodb_deadlock_detect%';  --默认是打开的
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set, 1 warning (0.01 sec)
```
undo文件不再使用系统表空间

在data目录下默认创建2个UNDO表空间，不再使用系统表空间。
binlog日志过期时间精确到秒

之前我们是在my.conf文件中配置expire_logs_days参数设置保留binlog多少天，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数，并精确到了秒
窗口函数(Window Functions)：也称分析函数

窗口函数与 SUM()、COUNT() 这种分组聚合函数类似，在聚合函数后面加上over()就变成窗口函数了，在括号里可以加上partition by等分组关键字指定如何分组，窗口函数即便分组也不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要再使用 GROUP BY。

详情可以看此小节的有道云笔记
默认字符集由latin1变为utf8mb4

在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。
MyISAM系统表全部换成InnoDB表

将系统表(mysql)和数据字典表全部改为InnoDB存储引擎，默认的MySQL实例将不包含MyISAM表，除非手动创建MyISAM表。
自增变量持久化

解决了以前版本的一个bug

DDL原子化

InnoDB表的DDL支持事务完整性，要么成功要么回滚。

-- 删除表时，此时t2表不存在 删除表报错不会回滚，t1表会被删除
mysql> drop table t1,t2;
ERROR 1051 (42S02): Unknown table 'test.t2'


-- 而8.0版本 删除表报错会回滚，t1表依然还在
mysql> drop table t1,t2;  
ERROR 1051 (42S02): Unknown table 'test.t2'

参数修改持久化

我们使用set global 或者set session设置的变量参数在mysql重启后会失效。

MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。
```
mysql> set persist innodb_lock_wait_timeout=25
```
系统会在数据目录下生成一个包含json格式的mysqld-auto.cnf 的文件，格式化后如下所示，当my.cnf 和mysqld-auto.cnf 同时存在时，后者具有更高优先级。
```
{
	"Version": 1,
	"mysql_server": {
		"innodb_lock_wait_timeout": {      // key
			"Value": "25",				   //value
			"Metadata": {
				"Timestamp": 1675290252103863,
				"User": "root",
				"Host": "localhost"
			}
		}
	}
}
```