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_employeesAS(
    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 ONorders(status ASC, created_time DESC);

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

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

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

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

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

-- 现在可以高效查询JSON内的数据
SELECT * FROM user_profiles WHERE age_generated BETWEEN25 AND35;

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

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

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

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

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

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

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

--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 GROUPbatch_group
TYPE=USER
VCPU=0-1
THREAD_PRIORITY = -10;

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

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

-- 在线业务查询自动使用默认或online_group
SELECT * FROM ordersWHEREid=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 LIMIT1) 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 LIMIT1 OFFSET1) 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 LIMIT1 OFFSET2) as third_latest
FROM users u;

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

SELECT u.username, u.email, recent_orders.*
FROM users u
JOINLATERAL(
    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
    LIMIT3
) recent_orders ON TRUE;

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

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

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

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

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