热门标签
热门文章
- 1如何在iPadpro2021及以上型号的iOS 17系统下安装UTM虚拟机运行Windows 11_ipad安装utm
- 2OBS推流 rtmp服务器（docker） python opencv拉流_docker obs
- 3配置git账号以及常用命令
- 4Hadoop+Hive数据分析综合案例_1.聊天平台每天都会有大量的用户在线,会出现大量的聊天数据,通过对聊天数据的
- 5【spring cloud zookeeper】KeeperErrorCode = Unimplemented for
- 6git代码回退_git 代码回退
- 7第11.25节 Python正则表达式编译re.compile及正则对象使用_什么叫把正则表达式编译re
- 8Mac可以卸载掉系统自带的软件吗 Mac第三方软件无法卸载是为什么
- 9大厂面试官问我：布隆过滤器有不能扩容和删除的缺陷，有没有可以替代的数据结构呢？【后端八股文二：布隆过滤器八股文合集】
- 10人工智能与人类：挑战与创新的平衡
当前位置: article > 正文
MySQL8.0新特性详解及全局优化
作者：小小林熬夜学编程 | 2024-06-11 07:25:12
赞
踩
mysql8.0新特性
一、前言
目前MySQL8.0及以上版本在我们日常工作中已经比较常见了，MySQL8.0版本增加了很多新特性，比如开窗函数，降序索引，函数索引，隐藏索引等。了解并会使用这些新特性，无论是在面试还是工作中都是非常加分的。
二、开窗函数
从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种分组聚合函数类似，在聚合函数后面加上over()就变成窗口函数了，在括号里可以加上partition by等分组关键字指定如何分组，窗口函数即便分组也不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要再使用 GROUP BY。
之前接触过一些大数据的工作，这个开窗函数和大数据的开窗函数很像，也是在聚合函数后加over(partition by 字段 group by 字段)。
我们来对比一下和group by有什么不同。
account_channel 表中数据如下:
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 |
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
使用group by查询
mysql> select name,sum(balance) from account_channel group by name;
+-----------+--------------+
| name | sum(balance) |
+-----------+--------------+
| zhuge | 600 |
| lilei | 300 |
| hanmeimei | 500 |
+-----------+--------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
使用开窗函数查询
在聚合函数后面加上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 | +-----------+---------+---------+-------------+ 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 | +-----------+---------+---------+-------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
三、新增函数索引
根据以往我们的经验，查询的列上有计算函数，将会导致索引失效。8.0以后引入了函数索引，可以在索引中使用函数。
函数索引基于虚拟列功能实现，在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: 0 mysql> create index func_idx on t3((UPPER(c2))); --创建一个大写的函数索引 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t3\G *************************** 1. row *************************** Table: t3 Non_unique: 1 Key_name: idx_c1 Seq_in_index: 1 Column_name: c1 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: t3 Non_unique: 1 Key_name: func_idx Seq_in_index: 1 Column_name: NULL Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: 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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
四、group by不再隐式排序
mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。
以前版本默认会按从小到大排序。
五、新增降序索引
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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
六、binlog日志文件过期时间精确到秒
以前是精确到天的，在8.0以后可以精确到天。
七、undo文件不再使用系统表空间
默认创建2个UNDO表空间，不再使用系统表空间。
八、默认字符集由latin1变为utf8mb4
在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。
九、自增变量持久化
在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重启后，该值将不会改变。
十、删除了.frm等文件
MySQL 8.0删除了之前版本的元数据文件，例如表结构.frm等文件，全部集中放入mysql.ibd文件里。
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/702264
推荐阅读
相关标签
