当前位置：首页 > article >正文

MySQL颠覆版系列————MySQL新特性（开启数据库的新纪元）上篇

article 2025/3/26 4:56:21

文章目录

前言
一、窗口函数（Window Functions）
- 1.1 窗口函数概念
- 1.2 常见的窗口函数
二、公用表表达式（Common Table Expressions, CTEs）
- 2.1 公用表表达式的概念
- 2.2 常见的公用表表达式
三、JSON增强
- 3.1 JSON增强的概念
- 3.2 常见的JSON增强
四、Invisible Indexes
- 4.1 Invisible Indexes的概念
- 4.2 常见的Invisible Indexes
献给读者

在这里插入图片描述

前言

在这个信息爆炸的时代，数据已经成为我们生活中不可或缺的一部分。无论你是社交媒体的重度使用者、企业中的数据分析员，还是软件开发工程师，都不可避免地与各种各样的数据库打交道。而在众多的关系型数据库管理系统（RDBMS）中，MySQL以其开源性、易用性和强大的功能脱颖而出，成为了最受欢迎的选择之一。

但你知道吗？MySQL不仅仅是一个传统的数据库系统；它一直在进化，不断地引入令人兴奋的新特性和改进，以适应日益增长的数据需求和技术挑战。想象一下，就像一艘宇宙飞船，在浩瀚的数据星空中航行，每一次升级都是对引擎的一次优化，让它飞得更快、更远。而MySQL 8.x版本系列就像是给这艘飞船装上了超级推进器，准备带你进入一个全新的探索之旅。

从过去到现在
回顾历史，MySQL自1995年首次发布以来，已经走过了漫长的路程。最初，它的目标是提供一个快速、可靠且易于使用的数据库解决方案。随着时间的发展，MySQL逐渐成长为支持全球数百万应用的核心技术之一。然而，随着互联网的发展和大数据时代的到来，传统的关系型数据库面临着前所未有的挑战。用户需要处理更大规模的数据集，执行更加复杂的查询操作，并确保数据的安全性和一致性。面对这些需求，MySQL没有停滞不前，而是选择了拥抱变化，不断创新。

迈向未来
那么，MySQL是如何应对这些挑战的呢？答案在于其不断推出的创新特性。例如，MySQL 8.0引入了窗口函数（Window Functions），这一功能让开发者能够以前所未有的方式分析数据。你可以轻松地计算排名、移动平均值或累计总和，而无需编写复杂的子查询。这就像是为你的SQL工具箱添加了一把瑞士军刀，让你在处理复杂业务逻辑时游刃有余。

不仅如此，MySQL还增强了对JSON的支持。在当今的世界里，非结构化数据越来越普遍，无论是日志文件、社交媒体帖子还是物联网设备生成的信息，都可以方便地存储和查询。通过使用JSON数据类型和相关函数，你可以高效地管理和检索半结构化数据，为应用程序带来更大的灵活性。

此外，MySQL 8.x还带来了许多其他激动人心的功能，比如公用表表达式（Common Table Expressions, CTEs）、持久化的全局变量设置、角色管理以及资源管理等。每一个新特性都是为了帮助你更好地管理和利用数据，提升工作效率的同时也保障了系统的稳定性和安全性。

探索之旅
在这篇博客文章中，我们将一起深入探讨MySQL 8.x版本中的一些关键新特性。我们将揭开它们背后的秘密，了解如何将这些强大的工具应用到实际工作中去。无论是想要提高查询性能，还是寻求更高级别的安全措施，亦或是希望简化日常维护任务，你都能在这里找到答案。

让我们开始这段奇妙的旅程吧！准备好迎接那些改变游戏规则的技术，学习如何充分利用MySQL提供的最新功能来解决现实世界中的问题。记住，最好的还在后面——因为正如科技界常说的那样，“唯一不变的就是变化本身”。而MySQL正是这场变革中最耀眼的明星之一，引领着我们走向一个充满无限可能的未来。所以，系好安全带，打开你的想象力，跟我们一起踏上这条通往数据巅峰的道路吧！

MySQL不断演进，引入了许多新特性和改进。截至2025年初，MySQL的最新稳定版本中包含了一系列增强功能和新增特性。本文我们将详细介绍MySQL中的一些重要新属性和功能。

一、窗口函数（Window Functions）

从MySQL 8.0开始支持窗口函数，这极大地增强了SQL查询的能力，允许执行复杂的分析操作，如排名、移动平均等，而无需使用子查询或连接。在SQL查询中，窗口函数（Window Functions）提供了一种强大的方式来执行复杂的分析操作。与聚合函数不同，窗口函数不会将行分组为单一的输出行；相反，它们允许我们在不减少原始行数的情况下对数据集进行计算。这种特性使得窗口函数非常适合于需要在结果集中保留所有记录的同时执行排序、排名或累计计算的任务。

1.1 窗口函数概念

窗口函数的核心在于“窗口”，即一个逻辑分区或子集，定义了函数应用的数据范围。每个窗口由三个主要部分组成：

PARTITION BY：类似于GROUP BY，但它不会减少结果集中的行数。它用于定义窗口内的逻辑分区。
ORDER BY：指定窗口内数据的排序顺序。
窗口框架：定义了窗口的具体范围，如当前行之前的所有行、之后的所有行或者两者之间的任意组合。

1.2 常见的窗口函数

排名函数

ROW_NUMBER(): 为窗口内的每一行分配唯一的序号。
RANK(): 根据指定的排序条件给每一行分配排名，相同的值会有相同的排名，但会跳过后续的排名。
DENSE_RANK(): 类似于RANK()，但是不会跳过后续的排名。

SELECT 
    employee_id, 
    department, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

此查询返回每个部门内按薪资降序排列的员工信息，并附带每种排名类型的数值。

聚合函数作为窗口函数
除了排名函数外，常见的聚合函数也可以用作窗口函数，如SUM(), AVG(), COUNT(), MIN(), MAX()等。这些函数可以用来计算累积和、移动平均等。

SELECT 
    date,
    sales,
    SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM daily_sales;

这段代码计算了每天的累计销售额。

NTILE()
NTILE(n)将结果集划分为n个大致相等的部分，并为每一行分配相应的桶编号。这对于划分数据集特别有用。

SELECT 
    customer_id,
    purchase_amount,
    NTILE(4) OVER (ORDER BY purchase_amount DESC) AS quartile
FROM purchases;

二、公用表表达式（Common Table Expressions, CTEs）

公用表表达式（Common Table Expressions，简称CTEs）是SQL中一种非常有用的工具，它提供了一种清晰且可读的方式来定义临时结果集，这些结果集可以在后续的SELECT, INSERT, UPDATE, DELETE语句中引用。CTEs特别适用于需要多次引用相同子查询的情况，或者用于递归查询。

2.1 公用表表达式的概念

定义
一个CTE由两部分组成：CTE名称和相应的查询定义。其语法结构如下：

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions
)
SELECT * FROM cte_name;

使用场景

简化复杂查询：通过将复杂的查询分解为多个逻辑部分，使整个查询更加易读。
递归查询：处理具有层次结构的数据，如组织结构图、分类树等。
提高代码重用性：避免在同一查询中重复编写相同的子查询。

2.2 常见的公用表表达式

假设我们有一个员工表employees，包含字段employee_id, name, manager_id。我们的目标是从这个表中获取每个员工的名字及其直接经理的名字。

WITH EmployeeCTE AS (
    SELECT e1.employee_id AS emp_id, e1.name AS emp_name, e2.name AS manager_name
    FROM employees e1
    LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id
)
SELECT * FROM EmployeeCTE;

在这个例子中，EmployeeCTE作为一个临时的结果集，包含了员工及其对应经理的信息。这使得最终的SELECT语句变得非常简洁明了。

三、JSON增强

随着数据类型的多样化，非结构化数据如JSON（JavaScript Object Notation）在现代应用程序中变得越来越重要。MySQL从5.7版本开始支持JSON数据类型，并且在后续的版本中不断增强了对JSON的支持，使得存储、查询和操作JSON数据变得更加高效和便捷。

3.1 JSON增强的概念

在MySQL中，JSON是一种专门用于存储JSON文档的数据类型。与传统的文本存储方式不同，JSON字段不仅能够保持数据的结构完整性，还能利用MySQL提供的内置函数进行高效的查询和修改。

3.2 常见的JSON增强

MySQL提供了丰富的内置函数来处理JSON数据，包括但不限于：

创建和解析JSON：如JSON_OBJECT(), JSON_ARRAY(), JSON_EXTRACT()
修改JSON：如JSON_SET(), JSON_INSERT(), JSON_REPLACE(), JSON_REMOVE()
检查JSON值：如JSON_CONTAINS(), JSON_CONTAINS_PATH()
获取JSON属性：如JSON_KEYS()

INSERT INTO users (name, profile) VALUES ('John Doe', JSON_OBJECT('age', 30, 'email', 'john@example.com'));

SELECT JSON_EXTRACT(profile, '$.email') AS email FROM users WHERE name = 'John Doe';

四、Invisible Indexes

在数据库管理中，索引是优化查询性能的关键工具。然而，并非所有的索引都对查询计划有积极的影响。有时候，某些索引可能会导致不必要的开销或干扰优化器的选择，尤其是在进行大规模的数据库重构或者性能调优时。MySQL从8.0版本开始引入了一个非常有用的功能——不可见索引（Invisible Indexes），它允许数据库管理员测试删除索引的影响而不实际移除它们。

4.1 Invisible Indexes的概念

不可见索引是一种特殊的索引类型，它不会被查询优化器考虑用于执行计划。这意味着虽然索引仍然存在于数据库中并且占用存储空间，但它不会影响查询的执行方式。这对于评估索引对查询性能的实际影响特别有用，因为它提供了一种无需永久删除索引来测试其重要性的方法。

4.2 常见的Invisible Indexes

在创建索引时，可以通过指定INVISIBLE关键字将其设为不可见：

CREATE INDEX idx_last_name ON employees (last_name) INVISIBLE;

修改现有索引的可见性

ALTER TABLE employees MODIFY INDEX idx_last_name INVISIBLE;

-- 或者将其变回可见
ALTER TABLE employees MODIFY INDEX idx_last_name VISIBLE;

查看索引状态

SELECT 
    table_schema, 
    table_name, 
    index_name, 
    is_visible 
FROM information_schema.statistics 
WHERE table_schema = 'your_database' AND table_name = 'employees';