MySQL 8.0 新特性详解

一、数据字典与系统表改进

1. 原子数据字典

详细说明：

• 完全重构了元数据存储系统，使用InnoDB引擎存储数据字典
• 取代了之前的.frm文件、.par文件等元数据文件
• 系统表（如mysql.user、mysql.db等）现在也存储在InnoDB中

优势：

• 崩溃安全：确保DDL操作的原子性
• 性能提升：减少了文件系统操作
• 统一性：所有元数据统一存储在事务性存储引擎中

影响：

• 不再支持MyISAM系统表
• 数据目录中不再看到.frm文件

二、SQL功能增强

1. 通用表表达式(CTE)和递归查询

语法：

sql

复制

WITH [RECURSIVE] cte_name AS (
    SELECT ...  -- 基础查询
    UNION [ALL]
    SELECT ...  -- 递归部分
)
SELECT * FROM cte_name;

特点：

• 非递归CTE：简化复杂查询，提高可读性
• 递归CTE：处理层次结构数据（如组织结构、评论树）
• 可以引用自身，支持多级递归

示例（生成序列）：

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1 FROM seq WHERE n < 10
)
SELECT * FROM seq;

2. 窗口函数

支持函数类型：

• 排名函数：ROW_NUMBER(), RANK(), DENSE_RANK()
• 聚合函数：SUM(), AVG(), COUNT()等配合OVER子句
• 分布函数：PERCENT_RANK(), CUME_DIST()
• 前后函数：LAG(), LEAD()
• 首尾函数：FIRST_VALUE(), LAST_VALUE()
• 分桶函数：NTILE()

语法：

SELECT 
    column1,
    ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS row_num
FROM table;

优势：

• 避免使用复杂的自连接或子查询
• 实现高级分析功能
• 保持原始行数的同时进行计算

3. 横向派生表(LATERAL)

功能：

• 允许派生表引用它左侧表中的列
• 类似于其他数据库中的LATERAL JOIN或CROSS APPLY

示例：

SELECT * FROM employees e,
LATERAL (
    SELECT * FROM salaries s 
    WHERE s.emp_no = e.emp_no
    ORDER BY s.from_date DESC LIMIT 1
) AS current_salary;

4. 函数索引

功能：

• 基于表达式或函数结果创建索引
• 索引虚拟列（Generated Column）上的索引

示例：

-- 基于函数创建索引
CREATE INDEX idx_name_upper ON employees((UPPER(last_name)));

-- 使用虚拟列创建索引
ALTER TABLE employees ADD COLUMN name_upper VARCHAR(255) AS (UPPER(last_name));
CREATE INDEX idx_name_upper ON employees(name_upper);

限制：

• 必须使用确定的函数（相同输入总是产生相同输出）
• 不能使用用户定义函数

5. 降序索引

功能：

• 明确指定索引的排序方向
• 优化ORDER BY ... DESC查询

示例：

CREATE INDEX idx_desc ON orders(order_date DESC, customer_id ASC);

优势：

• 避免反向扫描索引
• 提高特定排序查询的性能

三、JSON增强

1. JSON聚合函数

JSON_ARRAYAGG()：

• 将多行值聚合为JSON数组

JSON_OBJECTAGG()：

• 将键值对聚合为JSON对象

示例：

SELECT 
    department_id,
    JSON_ARRAYAGG(employee_name) AS employees,
    JSON_OBJECTAGG(employee_id, salary) AS salaries
FROM employees
GROUP BY department_id;

2. JSON合并函数

JSON_MERGE_PATCH()：

• 合并JSON文档，重复键保留最后一个值

JSON_MERGE_PRESERVE()：

• 合并JSON文档，重复键的值合并为数组

示例：

SELECT 
    JSON_MERGE_PATCH('{"a":1,"b":2}', '{"a":3,"c":4}');
-- 结果: {"a":3,"b":2,"c":4}

SELECT 
    JSON_MERGE_PRESERVE('{"a":1,"b":2}', '{"a":3,"c":4}');
-- 结果: {"a":[1,3],"b":2,"c":4}

3. JSON表函数

功能：

• 将JSON数据转换为关系型表格形式

语法：

SELECT * FROM JSON_TABLE(
    json_doc,
    path COLUMNS (
        column_name column_type PATH path_expression [ERROR ON ERROR]
        [, ...]
    )
) [AS] alias;

示例：

SELECT * FROM JSON_TABLE(
    '[{"x":10,"y":"A"},{"x":20,"y":"B"}]',
    '$[*]' COLUMNS(
        x INT PATH '$.x',
        y CHAR(1) PATH '$.y'
    )
) AS jt;

四、性能提升

1. 不可见索引

功能：

• 将索引标记为对优化器不可见
• 测试删除索引的影响而不实际删除

语法：

-- 创建不可见索引
CREATE INDEX idx_invisible ON table_name(col) INVISIBLE;

-- 修改索引可见性
ALTER TABLE table_name ALTER INDEX idx_name VISIBLE|INVISIBLE;

2. 直方图统计

功能：

• 存储列值分布统计信息
• 帮助优化器为不均匀分布的数据选择更好的执行计划

语法：

-- 创建直方图
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column1, column2;

-- 删除直方图
ANALYZE TABLE table_name DROP HISTOGRAM ON column1;

3. 资源组管理

功能：

• 将服务器资源分配给不同的线程组
• 控制CPU亲和性和线程优先级

语法：

-- 创建资源组
CREATE RESOURCE GROUP rg_name
TYPE = {USER|SYSTEM}
VCPU = cpu_list  -- 如0-3,8-11
THREAD_PRIORITY = priority;  -- -20到19

-- 分配线程到资源组
SET RESOURCE GROUP rg_name FOR thread_id;

4. 并行查询

有限支持：

• 主要用于特定类型的扫描操作

通过设置参数启用：

• sql复制SET SESSION innodb_parallel_read_threads = 4;

五、安全性增强

1. 角色管理

功能：

• 将权限集合抽象为角色
• 简化用户权限管理

语法：

-- 创建角色并授权
CREATE ROLE 'app_developer';
GRANT ALL ON app_db.* TO 'app_developer';

-- 将角色授予用户
GRANT 'app_developer' TO 'user1'@'localhost';

-- 激活角色
SET DEFAULT ROLE 'app_developer' TO 'user1'@'localhost';

2. 密码策略改进

特性：

• 密码过期：ALTER USER ... PASSWORD EXPIRE
• 密码重用限制：password_history和password_reuse_interval系统变量
• 密码验证：password_require_current系统变量
• 双密码支持：ALTER USER ... RETAIN CURRENT PASSWORD

3. OpenSSL替代yaSSL

改进：

• 支持更多加密算法
• 更好的安全性
• 支持TLS 1.3（MySQL 8.0.16+）

六、InnoDB改进

1. 自增持久化

问题解决：

• 之前版本在服务器重启后可能重用已分配的自增值
• 8.0版本将自增计数器持久化到redo日志

2. 临时表改进

变化：

• 使用独立的临时表空间(ibtmp1)
• 会话临时表现在在第一次使用时创建
• 性能提升，元数据管理更高效

3. DDL原子性

特性：

• 大多数DDL操作现在是原子的
• 失败时会自动回滚
• 基于新的数据字典实现

4. 新的锁机制

SKIP LOCKED：

• 跳过已被锁定的行

SELECT * FROM table FOR UPDATE SKIP LOCKED;

NOWAIT：

• 如果遇到锁立即返回错误

SELECT * FROM table FOR UPDATE NOWAIT;

七、复制与高可用

1. 组复制(Group Replication)增强

改进：

• 更好的分布式恢复
• 改进的消息压缩
• 支持IPv6
• 性能提升

2. 克隆插件

功能：

• 快速创建数据副本
• 用于搭建新的复制节点

语法：

-- 安装插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

-- 克隆远程服务器
CLONE INSTANCE FROM 'user'@'host':port IDENTIFIED BY 'password';

3. 复制过滤改进

特性：

• 支持在从库上设置多个复制通道的过滤规则
• 更灵活的过滤选项

八、其他实用特性

1. SET PERSIST

功能：

• 持久化全局系统变量设置
• 修改同时写入mysqld-auto.cnf文件

语法：

SET PERSIST max_connections = 200;

2. 默认字符集改为utf8mb4

变化：

• 默认字符集从latin1改为utf8mb4
• 完全支持Unicode，包括emoji
• 默认排序规则改为utf8mb4_0900_ai_ci

3. 正则表达式增强

新函数：

• REGEXP_LIKE()
• REGEXP_INSTR()
• REGEXP_REPLACE()
• REGEXP_SUBSTR()

示例：

SELECT * FROM users WHERE REGEXP_LIKE(name, '^[A-Z][a-z]+$');

4. GIS功能增强

改进：

• 支持地理哈希(geohash)
• 新增ST_Distance_Sphere()函数
• 更好的空间索引支持

5. 批量数据加载优化

特性：

• 提高LOAD DATA性能
• 支持并行加载
• 减少索引更新开销

这些新特性使MySQL 8.0成为功能更全面、性能更高、更安全的数据库系统，特别适合现代应用开发需求。

官方地址：https://www.mysql.com/downloads/