当前位置：网站首页 > IT知识 > 正文

MySQL 8.0 SQL优化黑科技，面试官都不一定知道!

liuian 2025-07-06 14:06 45 浏览

前言

提到SQL优化，大多数人想到的还是那些经典套路：建索引、避免全表扫描、优化JOIN顺序…这些确实是基础，但如果你还停留在MySQL 5.7时代的优化思维，那就out了。

MySQL 8.0已经发布好几年了，带来了大量革命性的新特性。然而令人意外的是，很多开发者（甚至包括一些面试官）对这些新功能还不够了解，依然在用老思路解决新问题。

今天我们就来聊聊MySQL 8.0那些鲜为人知的优化黑科技。这些特性不仅能让你的SQL性能飞起，更重要的是，当你在面试中展示这些技巧时，很可能连面试官都会被惊艳到。毕竟，谁不喜欢学到点新东西呢？

如果你的项目还在用MySQL 5.7或更早版本，这篇文章也值得收藏。

因为升级到MySQL 8.0后，你就能立刻用上这些强大的功能了。

耐心看完，你一定有所收获。

正文

老套路快速回顾

在展示黑科技之前，我们先快速过一遍那些“传统艺能”：

索引优化：B+树索引、复合索引、覆盖索引
查询重写：避免SELECT *、合理使用LIMIT
连接优化：INNER JOIN vs LEFT JOIN，驱动表选择
子查询优化：EXISTS vs IN，避免相关子查询
表设计优化：字段类型选择、范式vs反范式

这些基础知识相信大家都太熟悉了。

现在让我们来看看MySQL 8.0带来的那些降维打击式的新玩法。

骚操作一：利用MySQL 8.0的窗口函数进行复杂查询优化

传统的分组查询往往需要多次子查询，性能堪忧。MySQL 8.0引入的窗口函数可以优雅地解决这个问题。

传统写法（需要多次扫描表）：

-- 查询每个部门薪资最高的前3名员工
SELECT * FROM employees e1
WHERE (
    SELECT COUNT(DISTINCT e2.salary) 
    FROM employees e2 
    WHERE e2.department = e1.department 
    AND e2.salary >= e1.salary
) <= 3;

窗口函数优化（只需一次扫描）：

WITH ranked_employees AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
)
SELECT * FROM ranked_employees WHERE rn <= 3;

这种写法不仅代码更清晰，执行效率也大幅提升。

骚操作二：反直觉的索引设计 - 降序索引的妙用

MySQL 8.0开始支持真正的降序索引。很多人以为ORDER BY col DESC用普通索引就够了，但在某些场景下，降序索引能带来意想不到的效果。

-- 创建混合排序的索引
CREATE INDEX idx_mixed ON orders (status ASC, created_time DESC);

-- 这个查询现在可以完全利用索引顺序，避免filesort
SELECT * FROM orders 
WHERE status = 'pending' 
ORDER BY created_time DESC 
LIMIT 10;

特别是在分页查询中，这种索引设计能显著减少排序开销。

骚操作三：Generated Column的隐藏威力

Generated Column（生成列，又叫虚拟列）不仅仅是用来存储计算结果，还能用来进行一些曲线救国的优化。

关于虚拟列的具体使用可以看这篇文章：MySQL虚拟列：一个被低估的MySQL特性

-- 为JSON字段的某个属性创建生成列和索引
ALTER TABLE user_profiles 
ADD COLUMN age_generated INT GENERATED ALWAYS AS (JSON_EXTRACT(profile_data, '$.age')) STORED,
ADD INDEX idx_age (age_generated);

-- 现在可以高效查询JSON内的数据
SELECT * FROM user_profiles WHERE age_generated BETWEEN 25 AND 35;

这个方式其实特别适合处理JSON字段查询慢的问题。

骚操作四：Invisible Index - 安全的索引测试

在生产环境中，如果想给某个表加索引，又会担心新索引会影响其他SQL的执行计划，或者不确定这个索引是否真的有效果。

MySQL 8.0 的隐形索引（Invisible Index）就是为了解决这个问题而生：

-- 1. 先创建一个"隐形"的索引，这时索引会被创建，但优化器看不到它
CREATE INDEX idx_user_age ON users (age) INVISIBLE;

-- 2. 在测试会话中"激活"这个隐形索引
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- 3. 测试查询效果
EXPLAIN SELECT * FROM users WHERE age BETWEEN 25 AND 35;
-- 这时能看到是否使用了新索引

-- 4. 如果效果好，就让所有人都能看到这个索引
ALTER INDEX idx_user_age ON users VISIBLE;

-- 5. 如果效果不好，直接删除，对线上业务无影响
-- DROP INDEX idx_user_age ON users;

这样可以避免在生产环境中直接创建索引可能带来的风险。

骚操作五：巧用Hint强制执行计划

有时候优化器的选择并不是最优的，搞不好就会自作聪明，选择了一个看似合理但实际很慢的执行计划，这时候可以用Hint来调教一下。

看这个例子：

-- 假设你有这样一个查询
SELECT * FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'pending' AND c.city = 'Shanghai';

-- MySQL可能选择：
-- 1. 先扫描orders表找status='pending'的记录（假设有10万条）
-- 2. 再去JOIN customers表

-- 但你通过分析发现，Shanghai的客户只有1000个，应该：
-- 1. 先扫描customers表找city='Shanghai'的记录（1000条）
-- 2. 再去JOIN orders表

那这时候就可以用 Hint 来指定索引：

-- 强制让customers表作为驱动表（小表驱动大表）
SELECT /*+ STRAIGHT_JOIN */ * 
FROM customers c 
JOIN orders o ON o.customer_id = c.id 
WHERE c.city = 'Shanghai' AND o.status = 'pending';

-- 或者强制使用特定索引
SELECT /*+ INDEX(o idx_status) */ * 
FROM orders o 
WHERE o.status = 'pending';

但是这种方式对于SQL的认知要求比较高，谨慎使用。

骚操作六：Resource Group - 查询级别的资源控制

如果你的数据库上既有在线业务查询（要求快速响应），也有数据分析查询（可以慢一点但很耗资源）时，传统做法是分开部署，但成本比较高。

-- 创建一个专门给批处理任务用的资源组
-- VCPU = 0-1 表示只能使用CPU的0号和1号核心
-- THREAD_PRIORITY = -10 表示线程优先级较低
CREATE RESOURCE GROUP batch_group 
TYPE = USER 
VCPU = 0-1 
THREAD_PRIORITY = -10;

-- 创建一个给在线业务用的资源组（高优先级）
CREATE RESOURCE GROUP online_group 
TYPE = USER 
VCPU = 2-7 
THREAD_PRIORITY = 0;

-- 当你要跑一个大的统计查询时
SET RESOURCE GROUP batch_group;
SELECT COUNT(*), AVG(amount) 
FROM orders 
WHERE created_time >= '2023-01-01';  -- 这个查询可能要跑很久

-- 在线业务查询自动使用默认或online_group
SELECT * FROM orders WHERE id = 12345;  -- 这个查询不受影响

这样可以避免大查询影响在线业务，大查询再也不会抢占在线业务的CPU资源，一定程度上能保证系统的响应速度。

骚操作七：巧用LATERAL JOIN解决复杂关联

有些查询用传统JOIN很难实现，或者需要写复杂的子查询。

假设你要查询每个用户最近购买的3件商品信息。

传统写法（复杂且性能差） ：

SELECT u.username, u.email,
       (SELECT product_name FROM orders o1 JOIN products p1 ON o1.product_id = p1.id 
        WHERE o1.user_id = u.id ORDER BY o1.created_time DESC LIMIT 1) as latest_product,
       (SELECT product_name FROM orders o2 JOIN products p2 ON o2.product_id = p2.id 
        WHERE o2.user_id = u.id ORDER BY o2.created_time DESC LIMIT 1 OFFSET 1) as second_latest,
       (SELECT product_name FROM orders o3 JOIN products p3 ON o3.product_id = p3.id 
        WHERE o3.user_id = u.id ORDER BY o3.created_time DESC LIMIT 1 OFFSET 2) as third_latest
FROM users u;

LATERAL JOIN写法（简洁且高效） ：

SELECT u.username, u.email, recent_orders.*
FROM users u
JOIN LATERAL (
    SELECT p.product_name, o.created_time, o.amount
    FROM orders o 
    JOIN products p ON o.product_id = p.id
    WHERE o.user_id = u.id  -- 关键：这里可以引用左表u的字段
    ORDER BY o.created_time DESC 
    LIMIT 3
) recent_orders ON TRUE;

LATERAL JOIN可以让右表引用左表的列，解决一些传统JOIN难以处理的场景。

骚操作八：Multi-Valued Index - 为JSON数组优化

MySQL 8.0.17引入的多值索引，专门为JSON数组查询设计：

-- 为JSON数组创建多值索引
CREATE INDEX idx_tags ON articles ((CAST(tags->'$[*]' AS CHAR(50) ARRAY)));

-- 高效查询包含特定标签的文章
SELECT * FROM articles WHERE JSON_OVERLAPS(tags, '["技术", "MySQL"]');