面试被问烂的SQL优化，居然还能这么玩？
前言
提到SQL优化，大多数人想到的还是那些经典套路：建索引、避免全表扫描、优化JOIN顺序…这些确实是基础，但如果你还停留在MySQL 5.7时代的优化思维，那就out了。
MySQL 8.0已经发布好几年了，带来了大量革命性的新特性。然而令人意外的是，很多开发者（甚至包括一些面试官）对这些新功能还不够了解，依然在用老思路解决新问题。
今天我们就来聊聊MySQL 8.0那些鲜为人知的优化黑科技。这些特性不仅能让你的SQL性能飞起，更重要的是，当你在面试中展示这些技巧时，很可能连面试官都会被惊艳到。毕竟，谁不喜欢学到点新东西呢？
如果你的项目还在用MySQL 5.7或更早版本，这篇文章也值得收藏。
因为升级到MySQL 8.0后，你就能立刻用上这些强大的功能了。
耐心看完，你一定有所收获。
正文
老套路快速回顾
在展示黑科技之前，我们先快速过一遍那些“传统艺能”：
- 查询重写：避免SELECT *、合理使用LIMIT
-
连接优化：INNER JOIN vs LEFT JOIN，驱动表选择
- 子查询优化：EXISTS vs IN，避免相关子查询
这些基础知识相信大家都太熟悉了。
现在让我们来看看MySQL 8.0带来的那些降维打击式的新玩法。
骚操作一：利用MySQL 8.0的窗口函数进行复杂查询优化
传统的分组查询往往需要多次子查询，性能堪忧。MySQL 8.0引入的窗口函数可以优雅地解决这个问题。
传统写法（需要多次扫描表）
SELECT * FROM employees e1WHERE ( 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);SELECT * FROM orders WHERE status = 'pending' ORDER BY created_time DESC LIMIT 10;
特别是在分页查询中，这种索引设计能显著减少排序开销。
骚操作三：Generated Column的隐藏威力
Generated Column（生成列，又叫虚拟列）不仅仅是用来存储计算结果，还能用来进行一些曲线救国的优化。
关于虚拟列的具体使用可以看这篇文章：MySQL虚拟列：一个被低估的MySQL特性
ALTER TABLE user_profiles ADD COLUMN age_generated INT GENERATED ALWAYS AS (JSON_EXTRACT(profile_data, '$.age')) STORED,ADD INDEX idx_age (age_generated);SELECT * FROM user_profiles WHERE age_generated BETWEEN 25 AND 35;
这个方式其实特别适合处理JSON字段查询慢的问题。
骚操作四：Invisible Index - 安全的索引测试
在生产环境中，如果想给某个表加索引，又会担心新索引会影响其他SQL的执行计划，或者不确定这个索引是否真的有效果。
MySQL 8.0 的隐形索引（Invisible Index）就是为了解决这个问题而生：
CREATE INDEX idx_user_age ON users (age) INVISIBLE;SET SESSION optimizer_switch = 'use_invisible_indexes=on';EXPLAIN SELECT * FROM users WHERE age BETWEEN 25 AND 35;ALTER INDEX idx_user_age ON users VISIBLE;
这样可以避免在生产环境中直接创建索引可能带来的风险。
骚操作五：巧用Hint强制执行计划
有时候优化器的选择并不是最优的，搞不好就会自作聪明，选择了一个看似合理但实际很慢的执行计划，这时候可以用Hint来调教一下。
看这个例子：
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'pending' AND c.city = 'Shanghai';
那这时候就可以用 Hint 来指定索引：
SELECT * FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.city = 'Shanghai' AND o.status = 'pending';SELECT * FROM orders o WHERE o.status = 'pending';
但是这种方式对于SQL的认知要求比较高，谨慎使用。
骚操作六：Resource Group - 查询级别的资源控制
如果你的数据库上既有在线业务查询（要求快速响应），也有数据分析查询（可以慢一点但很耗资源）时，传统做法是分开部署，但成本比较高。
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'; 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_latestFROM users u;
LATERAL JOIN写法（简洁且高效） ：
SELECT u.username, u.email, recent_orders.*FROM users uJOIN 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 ORDER BY o.created_time DESC LIMIT 3) recent_orders ON TRUE;
LATERAL JOIN可以让右表引用左表的列，解决一些传统JOIN难以处理的场景。
骚操作八：Multi-Valued Index - 为JSON数组优化
MySQL 8.0.17引入的多值索引，专门为JSON数组查询设计：
CREATE INDEX idx_tags ON articles ((CAST(tags->'$[*]' AS CHAR(50) ARRAY)));SELECT * FROM articles WHERE JSON_OVERLAPS(tags, '["技术", "MySQL"]');
结尾
MySQL 8.0的这些新特性，代表着SQL优化正在进入一个新时代。
这些功能不仅让我们的代码更优雅，性能也更强劲。
遗憾的是，很多公司和开发者还没有充分利用这些新特性。
如果你能在项目中合理运用这些技巧，不仅能解决传统方案难以处理的问题，还能在技术分享和或者面试过程中展现出你对新技术的敏锐度。
当然，新特性虽好，也要结合实际场景使用，并不是所有的优化都需要用到这些高级功能。
有时候一个简单的索引也能很好地解决问题。
技术的魅力在于选择合适的工具来解决合适的问题。
如果你的项目还在用老版本MySQL，不妨考虑升级到8.0。这些新特性也许能带来性能、开发效率和代码质量的整体提升。
最后想说，技术永远在进步，保持学习的心态才能在这个行业中走得更远。
今天的黑科技，可能就是明天的常规操作。
来源：https://juejin.cn/post/7512744868957323279
Java1234_小锋，真名：曹锋，前世界500强央企软件工程师，12年Java,Python老司机，技术专家，资深Java,Python讲师，小锋网络科技 光杠司令员，司令部：www.java1234.com，www.python222.com每天坚持锻炼身体，坚持早睡早起，崇尚自由，平时喜欢带带Java学员 (已经成功指导2000+学员高薪就业)，喜欢搞搞Java技术自媒体，搞搞产品，后期继续研究主流技术，包括大数据，人工智能等。锋哥在公众号里整理了一份Java从入门到入土高级视频+Java常见笔试面试题+Java优秀简历模版，有需要的，可以关注下锋哥java1234公众号，回复【资料
】领取。
