数据库运维实战：MySQL 8.4窗口函数与JSON查询的性能调优方案

窗口函数在MySQL 8.4中的执行计划分析

MySQL 8.4对窗口函数的优化器做了进一步改进，但在处理大数据集时仍需人工调优。窗口函数的执行通常需要全表扫描加排序，如果缺少合适的索引，查询性能会急剧下降。

通过EXPLAIN ANALYZE可以观察到窗口函数的实际执行耗时分布。以下是一个常见的按分组排名场景的执行计划分析：

-- 按部门分组查询薪资排名前3的员工
EXPLAIN ANALYZE
SELECT 
    department_id,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees
WHERE hire_date >= '2024-01-01';

-- 典型执行计划输出关键信息：
-- -> Window aggregate: rank() OVER (PARTITION BY department_id ORDER BY salary DESC)
--    (actual time=0.15..1200.30 rows=50000 loops=1)
--    -> Sort: department_id, salary DESC
--       (actual time=0.12..800.20 rows=50000 loops=1)
--       -> Filter: (hire_date >= '2024-01-01')
--          (actual time=0.05..200.10 rows=50000 loops=1)

-- 优化：为PARTITION BY + ORDER BY创建复合索引
CREATE INDEX idx_dept_salary ON employees(department_id, salary DESC);

-- 添加索引后，排序步骤可被消除
-- Sort步骤从800ms降至0（Using index）

SQL查询优化：窗口函数替代子查询的实践

传统写法中，分组排名通常使用关联子查询实现，这会导致N+1查询问题。窗口函数可以将多轮扫描合并为单次扫描，性能提升可达10倍以上。以下是三种写法的对比：

-- 写法1：关联子查询（性能最差）
SELECT e.*
FROM employees e
WHERE salary = (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.department_id = e.department_id
);
-- 执行时间: ~8.5s（10万行数据）

-- 写法2：窗口函数 + 子查询过滤
SELECT * FROM (
    SELECT *,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 1;
-- 执行时间: ~0.8s

-- 写法3：窗口函数 + CTE（可读性最优）
WITH ranked AS (
    SELECT *,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT * FROM ranked WHERE rnk <= 3;
-- 执行时间: ~0.8s，可读性显著优于写法2

JSON查询优化：MySQL 8.4的JSON_TABLE函数实战

MySQL 8.4增强了JSON_TABLE函数，允许将JSON数组展开为关系表进行关联查询。这在处理半结构化数据时非常实用，但如果JSON文档体积过大，查询性能会严重劣化。

-- 示例：订单表中items字段存储JSON数组
-- {"items": [{"sku": "A001", "qty": 2, "price": 99.9}, ...]}

-- 错误做法：对每行执行JSON_EXTRACT逐个提取
SELECT 
    order_id,
    JSON_EXTRACT(items, '$.items[0].sku') AS sku_0,
    JSON_EXTRACT(items, '$.items[1].sku') AS sku_1
FROM orders
WHERE JSON_EXTRACT(items, '$.items[0].price') > 100;
-- 问题：无法利用索引，全表扫描+JSON解析

-- 正确做法：使用JSON_TABLE展开后关联
SELECT o.order_id, jt.sku, jt.qty, jt.price
FROM orders o
JOIN JSON_TABLE(
    o.items,
    '$.items[*]' COLUMNS(
        sku VARCHAR(20) PATH '$.sku',
        qty INT PATH '$.qty',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt
WHERE jt.price > 100;

-- 进一步优化：为JSON字段创建生成列+索引
ALTER TABLE orders 
ADD COLUMN first_item_price DECIMAL(10,2) 
    GENERATED ALWAYS AS (JSON_EXTRACT(items, '$.items[0].price')) STORED,
ADD INDEX idx_first_price(first_item_price);

MySQL性能调优：InnoDB缓冲池与JSON查询的配合

JSON字段的查询性能很大程度上取决于InnoDB缓冲池命中率。JSON文档存储在BLOB/TEXT格式中，大文档可能溢出行存储存储到溢出页。当缓冲池命中率低于95%时，JSON查询的IO开销会急剧上升。

建议将innodb_buffer_pool_size设置为物理内存的70%-80%，并开启innodb_buffer_pool_instances（每1GB缓冲池一个instance）。对于JSON字段频繁查询的表，可以借助information_schema.innodb_buffer_page监控其缓冲池占用情况。

分库分表方案：JSON字段的分片策略

当单表数据量超过5000万行时，分库分表成为必要手段。JSON字段的存在使分片键选择更加复杂——如果分片键嵌套在JSON内部，路由计算需要额外解析JSON。推荐在写入时将分片键同步提取到独立列，路由层直接读取独立列值，避免实时JSON解析。

数据库高可用架构：JSON数据的一致性保障

在主从复制架构中，JSON字段的修改使用Statement格式复制时可能出现函数执行结果不一致的问题。建议设置binlog_format=ROW，确保主从数据完全一致。对于使用组复制的InnoDB Cluster架构，JSON字段的写冲突检测依赖完整行比较，大JSON文档会增加冲突检测开销。设计表结构时，将频繁更新的字段拆出JSON单独建表，可以降低冲突概率并提高复制效率。