1 8.0.x版本新变化
mysql8.0版本新变化
- 0.字符集的变化
由默认的latin1转换到utf8mb4,其中关于系统参数 1)character_set_server 2)character_set_database 参数也有latin1 转换为了utf8mb4, 编码规则参数collation_server和collation_database也由latin_swedish_ci 变成了utf8mb4_0900_ai_ci.
- 1. innodb_read_only
该参数在8.0之前,当它开启的时候,它只对innodb storage表生效(意思是开启时不允许删除和创建innodb 表,但是可以创建myisam表和删除myisam表)
但是该参数在8.0.0之后,当它开启的时候,所有的存储引擎都不允许删除和创建表
- 2. set_persist
MySQL now supports a SET PERSIST
variant of SET
statement syntax, for making configuration changes at runtime that also persist across server restarts. Like SET GLOBAL
, SET PERSIST
is permitted for any global system variable that is dynamic (settable at runtime). The statement changes the runtime variable value, but also writes the variable setting to an option file named mysqld-auto.cnf
in the data directory. At startup, the server processes this file after all other option files
set persist 是弥补不能用客户端命令永久配置mysql参数,之前用的set global这种配置mysql参数的方式只是临时生效,当数据库重启之后,该配置就失效了,但是set persist配置后会将配置参数写到一个叫mysqld-auto.cnf文件,该文件位于数据目录下,当mysql再次重启之后,会读这个文件,将该文件下的配置参数给应用到mysql中使之生效。
用法:
SET PERSIST max_connections = 1000; SET @@PERSIST.max_connections = 1000; RESET PERSIST; RESET PERSIST system_var_name;
- 3.数据目录的改变
mysql8.0.0删除了所有的.frm文件,只保留了.ibd文件,
- 4.优化器的一些变化
- InnoDB: The storage engine interface now enables the optimizer to provide information about the size of the record buffer to be used for scans that the optimizer estimates will read multiple rows. The buffer size can vary based on the size of the estimate.
InnoDB
uses this variable-size buffering capability to take advantage of row prefetching, and to reduce the overhead of latching and B-tree navigation. Previously,InnoDB
used a small, fixed-size buffer. -
The optimizer now supports table-level
MERGE
andNO_MERGE
hints for specifying whether derived tables or views should be merged into the outer query block or materialized using an internal temporary table. Examples:
- InnoDB: The storage engine interface now enables the optimizer to provide information about the size of the record buffer to be used for scans that the optimizer estimates will read multiple rows. The buffer size can vary based on the size of the estimate.
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt; SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
-
- 支持不可见索引,不可见索引不能被mysql的优化器使用,它可以测试如果一个表不建索引时,对一个表的查询性能的影响。只适用于innodb表,不能将不可见索引建立在主键上。
CREATE TABLE t1 ( i INT, j INT, k INT, INDEX i_idx (i) INVISIBLE ) ENGINE = InnoDB; CREATE INDEX j_idx ON t1 (j) INVISIBLE; ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE; ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; ALTER TABLE t1 ALTER INDEX i_idx VISIBLE; mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | i_idx | YES | | j_idx | NO | | k_idx | NO | +------------+------------+ mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ > i, j FROM t1 WHERE j >= 50\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: j_idx key: j_idx key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using index condition mysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 filtered: 33.33 Extra: Using where
参考: 不可见索引 https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html
-
5.自增变量持久化
在8.0之前的版本,自增主键AUTO_INCREMENT的值如果大于max(primary key)+1,在MySQL重启后,会重置AUTO_INCREMENT=max(primary key)+1,这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id=199),一直到8.0才被解决,8.0版本将会对AUTO_INCREMENT值进行持久化,MySQL重启后,该值将不会改变
- 6.DDL原子化
InnoDB表的DDL支持事务完整性,要么成功要么回滚,将DDL操作回滚日志写入到data dictionary 数据字典表mysql.innodb_ddl_log 中用于回滚操作,该表是隐藏的表,通过show tables无法看到
- 7.json特性增强
MySQL 8 大幅改进了对JSON 的支持,添加了基于路径查询参数从JSON字段中抽取数据的JSON_EXTRACT() 函数,以及用于将数据分别组到 JSON 数组和对象中的JSON_ARRAYAGG() 和JSON_OBJECTAGG() 聚合函数。
在主从复制中,新增参数 binlog_row_value_options,控制JSON数据的传输方式,允许对于Json类型部分修改,在binlog中只记录修改的部分,减少json大数据在只有少量修改的情况下,对资源的占用。
- 8.redo 和undo日志加密
可以通过参数innodb_redo_log_encrypt和innodb_undo_log_encrypt设置
- 9.增加SET_VAR语法
在sql语法中增加SET_VAR语法,动态调整部分参数,有利于提升语句性能。
· select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ; · insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);
- 10 undo空间自动回收
innodb_undo_log_truncate参数在8.0.2版本默认值由OFF变为ON,默认开启undo日志表空间自动回收。
innodb_undo_tablespaces参数在8.0.2版本默认为2,当一个undo表空间被回收时,还有另外一个提供正常服务。
innodb_max_undo_log_size参数定义了undo表空间回收的最大值,当undo表空间超过这个值,该表空间被标记为可回收。
- 11.增加角色管理
这里可以看我的这个系列的第二篇博客(mysql的角色管理)
- 12.窗口函数
语法: select 排序函数/聚合函数 over (<partition by ...> 分区字段 order by 排序字段) 说明:注意over后面有一个空格
解释:
partiton by是可选的。如果不使用partition by,那么就是将整张表作为一个集合,最后使用排序函数得到的就是每一条记录根据排序列的排序编号。 排序函数主要有rank()、dense_rank、row_number,他们主要区别: 1)rank(): 对同一个字段排序,出现相同时,会并列排名,并且会出现排名间隙。 2)dense_rank() : 对同一个字段排序,出现相同时,会出现并列排名,排名连续的 3)row_number(): 对同一个字段排序,排名是联系的,即使出现相同,不会并列排名次
补充:
online ddl增强, innodb parallel read(8.0.14新增参数,支持在聚集索引上并行check table,提升count()函数的计算速度)
innodb paralle ddl(8.0.27新增参数innodb_ddl_threads),用于创建二级索引时,设置排序和生成Btree的并发线程数。
functional index(函数索引):在5.7一般版本,如果索引列上使用函数,索引将会失效,但是8.0上可以创建函数索引来解决
descending indexes(倒排索引):对于业务中的大表 在索引上倒序查询时,可以创建倒序索引提升sql排序的可能性
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2022-01-08 1.oracle性能优化之锁