Mysql8.0的特性

Mysql8.0的特性

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

1. 新增降序索引

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

2. 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;  
   

3. 增加隐藏索引

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

   如果想删除某个索引，可以先改为隐藏索引，这个时候隐藏索引是不会被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;
   

4. 新增函数索引

   就是在创建索引时，索引树中存储的其实是经过函数运算后的值，当然在使用这个索引时where条件中的字段也就必要使用函数了

   create index idx_c1 on t3(c1);     -- 创建普通索引
   create index func_idx on t3((UPPER(c2)));  -- 创建一个大写的函数索引
   

5. 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; 
   

6. 新增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)
   

7. 死锁检查控制

   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)
   

8. undo文件不再使用系统表空间

   在data目录下 默认创建2个UNDO表空间，不再使用系统表空间。

   

9. binlog日志过期时间精确到秒

   之前我们是在my.conf文件中配置expire_logs_days参数设置保留binlog多少天，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数，并精确到了秒

10. 窗口函数(Window Functions)：也称分析函数

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

    详情可以看此小节的有道云笔记

11. 默认字符集由latin1变为utf8mb4

    在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。

12. MyISAM系统表全部换成InnoDB表

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

13. 自增变量持久化

    解决了以前版本的一个bug

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

15. 参数修改持久化

    我们使用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"}}}
    }