Mysql 8.0新特性详解

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

1、新增降序索引

MySQL在语法上很早就已经支持降序索引,但实际上创建的仍然是升序索引,如下MySQL 5.7 所示,c2字段降序,但是从show create table看c2仍然是升序。8.0可以看到,c2字段降序。只有Innodb存储引擎支持降序索引。

# ====MySQL 5.7演示====
mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.04 sec)mysql> insert into t1 (c1,c2) values(1, 10),(2,50),(3,50),(4,100),(5,80);
Query OK, 5 rows affected (0.02 sec)mysql> show create table t1\G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE `t1` (`c1` int(11) DEFAULT NULL,`c2` int(11) DEFAULT NULL,KEY `idx_c1_c2` (`c1`,`c2`)    --注意这里,c2字段是升序
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)mysql> explain select * from t1 order by c1,c2 desc;  --5.7也会使用索引,但是Extra字段里有filesort文件排序
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    1 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)# ====MySQL 8.0演示====
mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.02 sec)mysql> insert into t1 (c1,c2) values(1, 10),(2,50),(3,50),(4,100),(5,80);
Query OK, 5 rows affected (0.02 sec)mysql> show create table t1\G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE `t1` (`c1` int DEFAULT NULL,`c2` int DEFAULT NULL,KEY `idx_c1_c2` (`c1`,`c2` DESC)  --注意这里的区别,降序索引生效了
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)mysql> explain select * from t1 order by c1,c2 desc;  --Extra字段里没有filesort文件排序,充分利用了降序索引
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from t1 order by c1 desc,c2;  --Extra字段里有Backward index scan,意思是反向扫描索引;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    1 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from t1 order by c1 desc,c2 desc;  --Extra字段里有filesort文件排序,排序必须按照每个字段定义的排序或按相反顺序才能充分利用索引
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    1 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from t1 order by c1,c2;    --Extra字段里有filesort文件排序,排序必须按照每个字段定义的排序或按相反顺序才能充分利用索引
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_c1_c2 | 10      | NULL |    1 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

2、group by 不再隐式排序

mysql 8.0 对于group by 字段不再隐式排序,如需要排序,必须显式加上order by 子句。

# ====MySQL 5.7演示====
mysql> select count(*),c2 from t1 group by c2;
+----------+------+
| count(*) | c2   |
+----------+------+
|        1 |   10 |
|        2 |   50 |
|        1 |   80 |
|        1 |  100 |
+----------+------+
4 rows in set (0.00 sec)# ====MySQL 8.0演示====
mysql> select count(*),c2 from t1 group by c2;   --8.0版本group by不再默认排序
+----------+------+
| count(*) | c2   |
+----------+------+
|        1 |   10 |
|        2 |   50 |
|        1 |  100 |
|        1 |   80 |
+----------+------+
4 rows in set (0.00 sec)mysql> select count(*),c2 from t1 group by c2 order by c2;  --8.0版本group by不再默认排序,需要自己加order by
+----------+------+
| count(*) | c2   |
+----------+------+
|        1 |   10 |
|        2 |   50 |
|        1 |   80 |
|        1 |  100 |
+----------+------+
4 rows in set (0.00 sec)

3、增加隐藏索引

使用 invisible 关键字在创建表或者进行表变更中设置索引为隐藏索引。索引隐藏只是不可见,但是数据库后台还是会维护隐藏索引的,在查询时优化器不使用该索引,即使用force index,优化器也不会使用该索引,同时优化器也不会报索引不存在的错误,因为索引仍然真实存在,必要时,也可以把隐藏索引快速恢复成可见。注意,主键不能设置为 invisible。

软删除就可以使用隐藏索引,比如我们觉得某个索引没用了,删除后发现这个索引在某些时候还是有用的,于是又得把这个索引加回来,如果表数据量很大的话,这种操作耗费时间是很多的,成本很高,这时,我们可以将索引先设置为隐藏索引,等到真的确认索引没用了再删除。

# 创建t2表,里面的c2字段为隐藏索引
mysql> create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);
Query OK, 0 rows affected (0.02 sec)mysql> show index from t2\G
*************************** 1. row ***************************Table: t2Non_unique: 1Key_name: idx_c1Seq_in_index: 1Column_name: c1Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
*************************** 2. row ***************************Table: t2Non_unique: 1Key_name: idx_c2Seq_in_index: 1Column_name: c2Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: NO   --隐藏索引不可见Expression: NULL
2 rows in set (0.00 sec)mysql> explain select * from t2 where c1=1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_c1        | idx_c1 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from t2 where c2=1;  --隐藏索引c2不会被使用
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql> select @@optimizer_switch\G   --查看各种参数
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on
1 row in set (0.00 sec)mysql> set session optimizer_switch="use_invisible_indexes=on";  ----在会话级别设置查询优化器可以看到隐藏索引
Query OK, 0 rows affected (0.00 sec)mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)mysql> explain select * from t2 where c2=1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_c2        | idx_c2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> alter table t2 alter index idx_c2 visible;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> alter table t2 alter index idx_c2 invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

4、新增函数索引

之前我们知道,如果在查询中加入了函数,索引不生效,所以MySQL 8引入了函数索引,MySQL 8.0.13开始支持在索引中使用函数(表达式)的值。

函数索引基于虚拟列功能实现,在MySQL中相当于新增了一个列,这个列会根据你的函数来进行计算结果,然后使用函数索引的时候就会用这个计算后的列作为索引。

mysql> create table t3(c1 varchar(10),c2 varchar(10));
Query OK, 0 rows affected (0.02 sec)mysql> create index idx_c1 on t3(c1);     --创建普通索引
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> create index func_idx on t3((UPPER(c2)));  --创建一个大写的函数索引
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from t3\G
*************************** 1. row ***************************Table: t3Non_unique: 1Key_name: idx_c1Seq_in_index: 1Column_name: c1Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
*************************** 2. row ***************************Table: t3Non_unique: 1Key_name: func_idxSeq_in_index: 1Column_name: NULLCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: upper(`c2`)    --函数表达式
2 rows in set (0.00 sec)mysql> explain select * from t3 where upper(c1)='ZHUGE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from t3 where upper(c2)='ZHUGE';  --使用了函数索引
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t3    | NULL       | ref  | func_idx      | func_idx | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

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

对于select ... for share(8.0新增加查询共享锁的语法)或 select ... for update, 在语句后面添加NOWAIT、SKIP LOCKED语法可以跳过锁等待,或者跳过锁定。

在5.7及之前的版本,select...for update,如果获取不到锁,会一直等待,直到innodb_lock_wait_timeout超时。

在8.0版本,通过添加nowait,skip locked语法,能够立即返回。如果查询的行已经加锁,那么nowait会立即报错返回,而skip locked也会立即返回,只是返回的结果中不包含被锁定的行。

应用场景比如查询余票记录,如果某些记录已经被锁定,用skip locked可以跳过被锁定的记录,只返回没有锁定的记录,提高系统性能。

# 先打开一个session1:
mysql> select * from t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 |   10 |
|    2 |   50 |
|    3 |   50 |
|    4 |  100 |
|    5 |   80 |
+------+------+
5 rows in set (0.00 sec)mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> update t1 set c2 = 60 where c1 = 2;     --锁定第二条记录
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0# 另外一个session2:    
mysql> select * from t1 where c1 = 2 for update;   --等待超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> 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;  --查询立即返回,过滤掉了第二行记录
+------+------+
| c1   | c2   |
+------+------+
|    1 |   10 |
|    3 |   50 |
|    4 |  100 |
|    5 |   80 |
+------+------+
4 rows in set (0.00 sec)

6、新增innodb_dedicated_server自适应参数

能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size,innodb_log_file_size等参数,会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题,前提是服务器是专用来给MySQL数据库的,如果还有其他软件或者资源或者多实例MySQL使用,不建议开启该参数,不然会影响其它程序。

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 (MySQL 5.7.15)增加了一个新的动态变量 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文件不再使用系统表空间

默认创建2个UNDO表空间,不再使用系统表空间。

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

之前是天,并且参数名称发生变化. 在8.0版本之前,binlog日志过期时间设置都是设置expire_logs_days参数,而在8.0版本中,MySQL默认使用binlog_expire_logs_seconds参数。

 10、窗口函数(Window Functions):也称分析函数

从 MySQL 8.0 开始,新增了一个叫窗口函数的概念,它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种分组聚合函数类似,在聚合函数后面加上over()就变成窗口函数了,在括号里可以加上partition by等分组关键字指定如何分组,窗口函数即便分组也不会将多行查询结果合并为一行,而是将结果放回多行当中,即窗口函数不需要再使用 GROUP BY。

# 创建一张账户余额表
CREATE TABLE `account_channel` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '姓名',`channel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '账户渠道',`balance` int DEFAULT NULL COMMENT '余额',PRIMARY KEY (`id`)
) ENGINE=InnoDB# 插入一些示例数据
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('1', 'zhuge', 'wx', '100');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('2', 'zhuge', 'alipay', '200');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('3', 'zhuge', 'yinhang', '300');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('4', 'lilei', 'wx', '200');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('5', 'lilei', 'alipay', '100');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('6', 'hanmeimei', 'wx', '500');mysql> select * from account_channel;
+----+-----------+---------+---------+
| id | name      | channel | balance |
+----+-----------+---------+---------+
|  1 | zhuge     | wx      |     100 |
|  2 | zhuge     | alipay  |     200 |
|  3 | zhuge     | yinhang |     300 |
|  4 | lilei     | wx      |     200 |
|  5 | lilei     | alipay  |     100 |
|  6 | hanmeimei | wx      |     500 |
+----+-----------+---------+---------+
6 rows in set (0.00 sec)mysql> select name,sum(balance) from account_channel group by name;
+-----------+--------------+
| name      | sum(balance) |
+-----------+--------------+
| zhuge     |          600 |
| lilei     |          300 |
| hanmeimei |          500 |
+-----------+--------------+
3 rows in set (0.00 sec)# 在聚合函数后面加上over()就变成分析函数了,后面可以不用再加group by制定分组,因为在over里已经用partition关键字指明了如何分组计算,这种可以保留原有表数据的结构,不会像分组聚合函数那样每组只返回一条数据
mysql> select name,channel,balance,sum(balance) over(partition by name) as sum_balance from account_channel;
+-----------+---------+---------+-------------+
| name      | channel | balance | sum_balance |
+-----------+---------+---------+-------------+
| hanmeimei | wx      |     500 |         500 |
| lilei     | wx      |     200 |         300 |
| lilei     | alipay  |     100 |         300 |
| zhuge     | wx      |     100 |         600 |
| zhuge     | alipay  |     200 |         600 |
| zhuge     | yinhang |     300 |         600 |
+-----------+---------+---------+-------------+
6 rows in set (0.00 sec)mysql> select name,channel,balance,sum(balance) over(partition by name order by balance) as sum_balance from account_channel;
+-----------+---------+---------+-------------+
| name      | channel | balance | sum_balance |
+-----------+---------+---------+-------------+
| hanmeimei | wx      |     500 |         500 |
| lilei     | alipay  |     100 |         100 |
| lilei     | wx      |     200 |         300 |
| zhuge     | wx      |     100 |         100 |
| zhuge     | alipay  |     200 |         300 |
| zhuge     | yinhang |     300 |         600 |
+-----------+---------+---------+-------------+
6 rows in set (0.00 sec)# over()里如果不加条件,则默认使用整个表的数据做运算
mysql> select name,channel,balance,sum(balance) over() as sum_balance from account_channel;
+-----------+---------+---------+-------------+
| name      | channel | balance | sum_balance |
+-----------+---------+---------+-------------+
| zhuge     | wx      |     100 |        1400 |
| zhuge     | alipay  |     200 |        1400 |
| zhuge     | yinhang |     300 |        1400 |
| lilei     | wx      |     200 |        1400 |
| lilei     | alipay  |     100 |        1400 |
| hanmeimei | wx      |     500 |        1400 |
+-----------+---------+---------+-------------+
6 rows in set (0.00 sec)mysql> select name,channel,balance,avg(balance) over(partition by name) as avg_balance from account_channel;
+-----------+---------+---------+-------------+
| name      | channel | balance | avg_balance |
+-----------+---------+---------+-------------+
| hanmeimei | wx      |     500 |    500.0000 |
| lilei     | wx      |     200 |    150.0000 |
| lilei     | alipay  |     100 |    150.0000 |
| zhuge     | wx      |     100 |    200.0000 |
| zhuge     | alipay  |     200 |    200.0000 |
| zhuge     | yinhang |     300 |    200.0000 |
+-----------+---------+---------+-------------+
6 rows in set (0.00 sec)

专用窗口函数

  • 序号函数:ROW_NUMBER()、RANK()、DENSE_RANK()
  • 分布函数:PERCENT_RANK()、CUME_DIST()
  • 前后函数:LAG()、LEAD()
  • 头尾函数:FIRST_VALUE()、LAST_VALUE()
  • 其它函数:NTH_VALUE()、NTILE()
    # 按照balance字段排序,展示序号
    mysql> select name,channel,balance,row_number() over(order by balance) as row_number1 from account_channel;
    +-----------+---------+---------+-------------+
    | name      | channel | balance | row_number1 |
    +-----------+---------+---------+-------------+
    | zhuge     | wx      |     100 |           1 |
    | lilei     | alipay  |     100 |           2 |
    | zhuge     | alipay  |     200 |           3 |
    | lilei     | wx      |     200 |           4 |
    | zhuge     | yinhang |     300 |           5 |
    | hanmeimei | wx      |     500 |           6 |
    +-----------+---------+---------+-------------+
    6 rows in set (0.00 sec)# 按照balance字段排序,first_value()选出排第一的余额
    mysql> select name,channel,balance,first_value(balance) over(order by balance) as first1 from account_channel;
    +-----------+---------+---------+--------+
    | name      | channel | balance | first1 |
    +-----------+---------+---------+--------+
    | zhuge     | wx      |     100 |    100 |
    | lilei     | alipay  |     100 |    100 |
    | zhuge     | alipay  |     200 |    100 |
    | lilei     | wx      |     200 |    100 |
    | zhuge     | yinhang |     300 |    100 |
    | hanmeimei | wx      |     500 |    100 |
    +-----------+---------+---------+--------+
    6 rows in set (0.01 sec)

    12、默认字符集由latin1变为utf8mb4

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

    13、MyISAM系统表全部换成InnoDB表

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

    14、元数据存储变动

    MySQL 8.0删除了之前版本的元数据文件,例如表结构.frm等文件,全部集中放入mysql.ibd文件里。可以看见下图test库文件夹里已经没有了frm文件

15、自增变量持久化

在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重启后,该值将不会改变。

# ====MySQL 5.7演示====
mysql> create table t(id int auto_increment primary key,c1 varchar(20));
Query OK, 0 rows affected (0.03 sec)mysql> insert into t(c1) values('zhuge1'),('zhuge2'),('zhuge3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  1 | zhuge1 |
|  2 | zhuge2 |
|  3 | zhuge3 |
+----+--------+
3 rows in set (0.00 sec)mysql> delete from t where id = 3;
Query OK, 1 row affected (0.01 sec)mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  1 | zhuge1 |
|  2 | zhuge2 |
+----+--------+
2 rows in set (0.00 sec)mysql> exit;
Bye# 重启MySQL服务,并重新连接MySQL
mysql> insert into t(c1) values('zhuge4');
Query OK, 1 row affected (0.01 sec)mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  1 | zhuge1 |
|  2 | zhuge2 |
|  3 | zhuge4 |
+----+--------+
3 rows in set (0.00 sec)mysql> update t set id = 5 where c1 = 'zhuge1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  2 | zhuge2 |
|  3 | zhuge4 |
|  5 | zhuge1 |
+----+--------+
3 rows in set (0.00 sec)mysql> insert into t(c1) values('zhuge5');
Query OK, 1 row affected (0.01 sec)mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  2 | zhuge2 |
|  3 | zhuge4 |
|  4 | zhuge5 |
|  5 | zhuge1 |
+----+--------+
4 rows in set (0.00 sec)mysql> insert into t(c1) values('zhuge6');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'# ====MySQL 8.0演示====
mysql> create table t(id int auto_increment primary key,c1 varchar(20));
Query OK, 0 rows affected (0.02 sec)mysql> insert into t(c1) values('zhuge1'),('zhuge2'),('zhuge3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  1 | zhuge1 |
|  2 | zhuge2 |
|  3 | zhuge3 |
+----+--------+
3 rows in set (0.00 sec)mysql> delete from t where id = 3;
Query OK, 1 row affected (0.01 sec)mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  1 | zhuge1 |
|  2 | zhuge2 |
+----+--------+
2 rows in set (0.00 sec)mysql> exit;
Bye
[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL... SUCCESS! # 重新连接MySQL
mysql> insert into t(c1) values('zhuge4');
Query OK, 1 row affected (0.00 sec)mysql> select * from t;  --生成的id为4,不是3
+----+--------+
| id | c1     |
+----+--------+
|  1 | zhuge1 |
|  2 | zhuge2 |
|  4 | zhuge4 |
+----+--------+
3 rows in set (0.00 sec)mysql> update t set id = 5 where c1 = 'zhuge1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  2 | zhuge2 |
|  4 | zhuge4 |
|  5 | zhuge1 |
+----+--------+
3 rows in set (0.00 sec)mysql> insert into t(c1) values('zhuge5');
Query OK, 1 row affected (0.00 sec)mysql> select * from t;
+----+--------+
| id | c1     |
+----+--------+
|  2 | zhuge2 |
|  4 | zhuge4 |
|  5 | zhuge1 |
|  6 | zhuge5 |
+----+--------+
4 rows in set (0.00 sec)

16、DDL原子化

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

MySQL 8.0 开始支持原子 DDL 操作,其中与表相关的原子 DDL 只支持 InnoDB 存储引擎。一个原子 DDL 操作内容包括:更新数据字典,存储引擎层的操作,在 binlog 中记录 DDL 操作。支持与表相关的 DDL:数据库、表空间、表、索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE。支持的其它 DDL :存储程序、触发器、视图、UDF 的 CREATE、DROP 以及ALTER 语句。支持账户管理相关的 DDL:用户和角色的 CREATE、ALTER、DROP 以及适用的 RENAME等等。

# MySQL 5.7
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account        |
| actor          |
| employee       |
| film           |
| film_actor     |
| leaf_id        |
| t1             |
| test_innodb    |
| test_myisam    |
| test_order_id  |
+----------------+
10 rows in set (0.01 sec)mysql> drop table t1,t2;  //删除表报错不会回滚,t1表会被删除
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account        |
| actor          |
| employee       |
| film           |
| film_actor     |
| leaf_id        |
| test_innodb    |
| test_myisam    |
| test_order_id  |
+----------------+
9 rows in set (0.00 sec)# MySQL 8.0  
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| account        |
| actor          |
| employee       |
| film           |
| film_actor     |
| leaf_id        |
| t1             |
| test_innodb    |
| test_myisam    |
| test_order_id  |
+----------------+
10 rows in set (0.00 sec)mysql> drop table t1,t2;  //删除表报错会回滚,t1表依然还在
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;  
+----------------+
| Tables_in_test |
+----------------+
| account        |
| actor          |
| employee       |
| film           |
| film_actor     |
| leaf_id        |
| t1             |
| test_innodb    |
| test_myisam    |
| test_order_id  |
+----------------+
10 rows in set (0.00 sec)

17、参数修改持久化

MySQL 8.0版本支持在线修改全局参数并持久化,通过加上PERSIST关键字,可以将修改的参数持久化到新的配置文件(mysqld-auto.cnf)中,重启MySQL时,可以从该配置文件获取到最新的配置参数。set global 设置的变量参数在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": {"Value": "25","Metadata": {"Timestamp": 1675290252103863,"User": "root","Host": "localhost"}}}
}

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/263420.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

SQL使用大全

一、SQL简介 SQL是一种用于管理关系型数据库的编程语言。它允许用户执行各种操作,如查询、插入、更新和删除数据,以及创建、修改和删除数据库对象(如表、索引等)。 目录 二、数据类型 SQL支持多种数据类型,包括数值…

华为HCIP Datacom H12-831 卷23

单选题 1、某园区部署IS-IS实现网络互通,在所有IS-IS路由器的进程中配置命令flash-flood 6 max-timer-interval 100 Leve1-2,则以下关于该场景的描述,正确的是哪—项? A、若某IS-IS路由器LSDB内更新的LSP数量为5,则在100毫秒内且路由计算完成前&#…

MFC 皮肤库配置

1.创建MFC 对话框 2.添加皮肤资源 添加资源 添加头文件 关闭SDL检测 添加静态库文件 修改字符集 添加头文件 将皮肤中的ssk文件加载到初始化实例中 > 运行即可

【C++】类与对象—— 初始化列表 、static 静态成员、

类与对象 1 再谈构造函数1.1 构造函数体赋值1.2 初始化列表语法:建议:初始化顺序:注意: 1.3 explicit关键字 2 static 静态成员2.1 概念2.2 声明成员变量2.3 使用类的静态成员2.4 定义静态成员总结 Thanks♪(・ω&#…

C/C++的内存管理(1)

内存管理 C与C的内存分布C语言中动态内存管理方式回顾C内存管理的方式 C与C的内存分布 我们学习C语言时就知道,储存不同的变量计算机会相应分配不同区块的内存。那为什么要把内存化为不同的区域呢?实质上是为了方便管理 下面我们来看看下面一道例题&…

Camunda7.18流程引擎启动出现Table ‘camunda_platform_docker.ACT_GE_PROPERTY‘的解决方案

文章目录 1、问题描述2、原因分析3、解决方案3.1、方案一:降低mysql版本3.2、方案二:增加nullCatalogMeansCurrent参数(推荐) 4、总结 1、问题描述 需要在docker中,部署Camunda流程引擎。通过启动脚本camunda-platfor…

Linux内核源码安装

文章目录 前言查看内核源码包安装内核源码编译内核源码最后 前言 我是醉墨居士,我们安装一下Linux内核源码,方便我们学习Linux内核 也方便我们进行eBPF开发时查看Linux内核的一些信息 查看内核源码包 apt-cache search linux-source安装内核源码 因为…

Unity实现帧序列

一、目的 1.想实现序列帧效果 自己使用Animation一直无法实现动画播放效果 二、参考 1. Unity序列帧动画——Sprite图片集制作UI动画_unity 序列帧动画图集-CSDN博客 结果:很好用,能实现效果 三、实操 新建Image,增加Animator组件&#x…

mac苹果电脑系统最好用的清理软件CleanMyMac2024功能介绍及如何激活解锁许可证

CleanMyMac X的界面设计简洁大气,为用户提供了直观且易于操作的使用体验。 布局清晰:界面布局非常明朗,左侧是功能栏,右侧则是信息界面。这种布局方式使得用户能够迅速找到所需的功能选项,提高了操作效率。色彩搭配&a…

Guava处理异常

guava由Google开发,它提供了大量的核心Java库,例如:集合、缓存、原生类型支持、并发库、通用注解、字符串处理和I/O操作等。 异常处理 传统的Java异常处理通常包括try-catch-finally块和throws关键字。 遇到FileNotFoundException或IOExce…

黑马JavaWeb开发跟学(一)Web前端开发HTML、CSS基础

黑马JavaWeb开发一.Web前端开发HTML、CSS基础 引子、Web开发介绍传统路线本课程全新路线本课程适用人群课程收获一、什么是web开发二、网站的工作流程三、网站的开发模式四、网站的开发技术 前端开发基础一、前端开发二、HTML & CSS2.1 HTML快速入门2.1.1 操作第一步第二步…

STM32F10X(Cortex-M3)系统定时器寄存器笔记和系统定时器精准延时函数

Cortex-M3系统定时器寄存器笔记和系统定时器精准延时函数 简介系统定时器寄存器STK_CTRLSTK_LOADSTK_VALSTK_CALIB STM32F10X(Cortex-M3)精准延时函数 简介 在STM32F10X(Cortex-M3)除了通用定时器和看门狗定时器外,还有一个系统定时器(SysTick) 拿STM32F103C8T6来说…

SpringBootWeb请求响应

SpringBootWeb请求响应 这里写目录标题 SpringBootWeb请求响应前言1. 请求1.1 Postman1.1.1 介绍1.1.2 安装 1.2 简单参数1.2.1 原始方式1.2.2 SpringBoot方式1.2.3 参数名不一致 1.3 实体参数1.3.1 简单实体对象1.3.2 复杂实体对象 1.4 数组集合参数1.4.1 数组1.4.2 集合 1.5 …

计算机网络-局域网

文章目录 局域网局域网拓扑结构以太网以太网传输介质以太网时隙提高传统以太网带宽的途径以太网帧格式 局域网协议IEEE 802参考模型IEEE802.2协议LLC帧格式及其控制字段LLC提供的三种服务 IEEE 802.3协议IEEE 802.4协议IEEE 802.5协议 高速局域网100M以太网千兆以太网万兆以太网…

SQL-Labs46关order by注入姿势

君衍. 四十六关 ORDER BY数字型注入1、源码分析2、rand()盲注3、if语句盲注4、时间盲注5、报错注入6、Limit注入7、盲注脚本 四十六关 ORDER BY数字型注入 请求方式注入类型拼接方式GET报错、布尔盲注、延时盲注ORDER BY $id 我们直接可以从界面中得知传参的参数为SORT&#x…

源代码管理——码云Gitee

目录 Git安装 Gitee配置SSH 源代码管理常规操作 1.idea配置git 2.常规操作 Git安装 安装Git是进行源代码管理的基本步骤之一。以下是在本地安装Git的通用步骤,适用于Windows系统: 下载Git安装程序: 访问Git官网的下载页面:Git官网下载地…

数据存储-文件存储

一、CSV文件存储 csv是python的标准库 列表数据写入csv文件 import csvheader [班级, 姓名, 性别, 手机号, QQ] # 二维数组 rows [[学习一班, 大娃, 男, a130111111122, 987456123],[学习二班, 二娃, 女, a130111111123, 987456155],[学习三班, 三娃, 男, a130111111124, …

【大数据】Flink 内存管理(二):JobManager 内存分配(含实际计算案例)

《Flink 内存管理》系列(已完结),共包含以下 4 篇文章: Flink 内存管理(一):设置 Flink 进程内存Flink 内存管理(二):JobManager 内存分配(含实际…

谷歌收购域名花费了100万美元的确让大家眼红

谷歌斥资100万美元购买了该域名。 卖个好价钱确实让大家眼红,但能不能卖到高价就是另一回事了。 首先,据统计,截至2008年底,我国域名总数达到1680万多个,可用的域名资源几乎无法统计,因为英文的组合太多了…

基于SpringBoot的家教管理系统

基于SpringBootVue的家教管理系统的设计与实现~ 开发语言:Java数据库:MySQL技术:SpringBootMyBatis工具:IDEA/Ecilpse、Navicat、Maven 系统展示 前台主页 家教 个人中心 管理员界面 摘要 本文介绍了基于SpringBoot框架开发的家…