如何优化MySQL中JSON_EXTRACT的查询性能？

查询目标记录速度快，但返回几百条结果时，从JSON字段提取数据耗时过长。500条结果时查询约3秒，移除提取操作则瞬间完成。当前SQL语句如下：

SELECT
    unit_uid,
    JSON_EXTRACT(rateplans, '$."2023-03-14"','$."2023-03-15"') AS rateplans
FROM
    property_listings_rateplans
WHERE
    unit_uid IN (1527,1639,5,9,17,20,24,27,1873,4279)

数据结构示例：

补充说明：未来会有数百万条unit_uid，每个JSON字段包含约2年的日期数据及更多键值对。

1. 改用JSON简写运算符提升性能 #

用MySQL原生的->>运算符替代JSON_EXTRACT，语法更简洁，同时能获得小幅性能提升：

SELECT
    unit_uid,
    rateplans->>'$."2023-03-14"' AS rate_20230314,
    rateplans->>'$."2023-03-15"' AS rate_20230315
FROM
    property_listings_rateplans
WHERE
    unit_uid IN (1527,1639,5,9,17,20,24,27,1873,4279)

如果需要保留原字段的JSON结构，可通过JSON_OBJECT组合结果：

SELECT
    unit_uid,
    JSON_OBJECT(
        '2023-03-14', rateplans->>'$."2023-03-14"',
        '2023-03-15', rateplans->>'$."2023-03-15"'
    ) AS rateplans
FROM
    property_listings_rateplans
WHERE
    unit_uid IN (1527,1639,5,9,17,20,24,27,1873,4279)

2. 拆分JSON结构到关系型表（长期最优方案） #

针对未来数百万级的数据量，拆分JSON结构到独立表是最彻底的优化方式：

• 创建新表property_daily_rates，字段包括unit_uid、rate_date、date、price、online
• 通过定时任务或触发器，同步property_listings_rateplans中的JSON数据到新表
• 给新表添加unit_uid + rate_date的联合索引

查询示例：

SELECT
    pr.unit_uid,
    JSON_OBJECT(
        '2023-03-14', JSON_OBJECT('date', pr1.date, 'price', pr1.price, 'online', pr1.online),
        '2023-03-15', JSON_OBJECT('date', pr2.date, 'price', pr2.price, 'online', pr2.online)
    ) AS rateplans
FROM
    (SELECT DISTINCT unit_uid FROM property_listings_rateplans WHERE unit_uid IN (1527,1639,5,9,17,20,24,27,1873,4279)) pr
LEFT JOIN property_daily_rates pr1 ON pr.unit_uid = pr1.unit_uid AND pr1.rate_date = '2023-03-14'
LEFT JOIN property_daily_rates pr2 ON pr.unit_uid = pr2.unit_uid AND pr2.rate_date = '2023-03-15'

3. 生成列+索引（固定日期查询场景适用） #

如果无法拆分表，可给常用的JSON提取路径创建生成列并添加索引：

ALTER TABLE property_listings_rateplans
ADD COLUMN rate_20230314 JSON GENERATED ALWAYS AS (rateplans->>'$."2023-03-14"') STORED,
ADD COLUMN rate_20230315 JSON GENERATED ALWAYS AS (rateplans->>'$."2023-03-15"') STORED;

CREATE INDEX idx_rateplans_dates ON property_listings_rateplans(rate_20230314, rate_20230315);

注意：该方案仅适合固定日期的查询，如果每次查询的日期不固定，生成列的维护成本会极高，不适合长期使用。

内容的提问来源于stack exchange，提问作者shiftyllama