基于MySQL JSON的趋势标签实现技术问询

Great questions—let's tackle each one with MySQL-specific insights and practical code examples tailored to your time-series tag aggregation needs:

Q1) 是否必须使用已知的通用JSON键才能执行分组操作？ #

Nope, you don’t need pre-known keys to group on dynamic tags—MySQL 8.0+ provides JSON_TABLE, a powerful function that lets you unpack JSON objects/arrays into relational rows, even with completely dynamic, unknown tag names. This eliminates the need to hardcode tag names in your queries.

Q2) 若采用数组格式[{"tag_name": "tag1", "tag_count": 1}]，如何修改INSERT ON DUPLICATE KEY UPDATE语句？ #

For the array structure, you’ll need to check if each tag exists in the array first (using JSON_SEARCH to find its path), then either increment its count or append a new object if it doesn’t exist. Here’s how to write the statement for multiple tags in one request:

INSERT INTO TAG_COUNTER (account, time_id, counters)
VALUES ('google', '2018061023', '[{"tag_name": "tag1", "tag_count": 1}, {"tag_name": "tag2", "tag_count": 1}]')
ON DUPLICATE KEY UPDATE
-- Handle tag1
counters = CASE
  WHEN JSON_SEARCH(counters, 'one', 'tag1') IS NOT NULL THEN
    JSON_SET(
      counters,
      REPLACE(JSON_SEARCH(counters, 'one', 'tag1'), 'tag_name', 'tag_count'),
      JSON_EXTRACT(counters, REPLACE(JSON_SEARCH(counters, 'one', 'tag1'), 'tag_name', 'tag_count')) + 1
    )
  ELSE
    JSON_ARRAY_APPEND(counters, '$', JSON_OBJECT('tag_name', 'tag1', 'tag_count', 1))
END,
-- Handle tag2
counters = CASE
  WHEN JSON_SEARCH(counters, 'one', 'tag2') IS NOT NULL THEN
    JSON_SET(
      counters,
      REPLACE(JSON_SEARCH(counters, 'one', 'tag2'), 'tag_name', 'tag_count'),
      JSON_EXTRACT(counters, REPLACE(JSON_SEARCH(counters, 'one', 'tag2'), 'tag_name', 'tag_count')) + 1
    )
  ELSE
    JSON_ARRAY_APPEND(counters, '$', JSON_OBJECT('tag_name', 'tag2', 'tag_count', 1))
END;

Note: If you’re handling multiple tags regularly, wrapping this logic in a stored procedure will make your code cleaner and easier to maintain.

Q3) 应选择哪种JSON结构更利于插入和查询？ #

First, the nested object format you mentioned ({ {"tag_name": "tag1", ...} }) is invalid JSON—JSON objects require string keys, so that’s not an option to begin with.

Between the key-value object ({"tag1": 1}) and object array ([{"tag_name": "tag1", "tag_count": 1}]):

• Key-value objects are better for insert/update performance: Updating a tag’s count uses a simple JSON_SET on a known key, no need to traverse an array to find the tag. This is faster for high-volume API requests.
• Object arrays are slightly more straightforward for grouping queries: Unpacking the array with JSON_TABLE maps directly to rows without needing to extract keys first. However, the performance gap is minimal with modern MySQL versions.

If your workload is heavy on writes (API requests), stick with the key-value format. If reads (popular tag stats) are more frequent, either works—but key-value is still manageable with the right query.

Q4) 能否继续使用当前的{"key" : "value"}格式实现热门标签检索？ #

Absolutely! This format is more concise and performs better for writes, and you can still build flexible grouping queries with JSON_TABLE and JSON_KEYS. Here’s how to get hourly, daily, or monthly tag stats:

Hourly stats:

SELECT
  time_id AS hour,
  tag_name,
  SUM(CAST(JSON_EXTRACT(counters, CONCAT('$.', tag_name)) AS UNSIGNED)) AS total_hits
FROM TAG_COUNTER,
     JSON_TABLE(
       JSON_KEYS(counters),
       '$[*]' COLUMNS(tag_name VARCHAR(255) PATH '$')
     ) AS tag_list
GROUP BY hour, tag_name
ORDER BY total_hits DESC;

Daily stats (using substring to get yyyyMMdd):

SELECT
  SUBSTRING(time_id, 1, 8) AS day,
  tag_name,
  SUM(CAST(JSON_EXTRACT(counters, CONCAT('$.', tag_name)) AS UNSIGNED)) AS total_hits
FROM TAG_COUNTER,
     JSON_TABLE(
       JSON_KEYS(counters),
       '$[*]' COLUMNS(tag_name VARCHAR(255) PATH '$')
     ) AS tag_list
GROUP BY day, tag_name
ORDER BY total_hits DESC;

Monthly stats:

SELECT
  SUBSTRING(time_id, 1, 6) AS month,
  tag_name,
  SUM(CAST(JSON_EXTRACT(counters, CONCAT('$.', tag_name)) AS UNSIGNED)) AS total_hits
FROM TAG_COUNTER,
     JSON_TABLE(
       JSON_KEYS(counters),
       '$[*]' COLUMNS(tag_name VARCHAR(255) PATH '$')
     ) AS tag_list
GROUP BY month, tag_name
ORDER BY total_hits DESC;

This query dynamically extracts all tag names from the JSON object, then aggregates their counts across the desired time window.

Q5) 检索时使用SUBSTRING(time_id, 1, 6) AS month能否利用索引？是否需要拆分时间列？ #

No, using SUBSTRING (or any function) on the time_id column will prevent MySQL from using your primary key index. The database can’t efficiently look up partial values from an indexed column when wrapped in a function.

To fix this and speed up time-based grouping:

1. Add stored computed columns with indexes:

   -- Add computed columns for day and month
   ALTER TABLE TAG_COUNTER
     ADD COLUMN day_id VARCHAR(8) GENERATED ALWAYS AS (SUBSTRING(time_id, 1, 8)) STORED,
     ADD COLUMN month_id VARCHAR(6) GENERATED ALWAYS AS (SUBSTRING(time_id, 1, 6)) STORED;
   
   -- Create composite indexes for fast grouping
   CREATE INDEX idx_account_month ON TAG_COUNTER(account, month_id);
   CREATE INDEX idx_account_day ON TAG_COUNTER(account, day_id);
   

   Now you can query directly using month_id or day_id and MySQL will use the indexes to quickly filter/aggregate rows.

2. Replace time_id with a DATETIME column:
   If possible, store the hour as a DATETIME (e.g., STR_TO_DATE(time_id, '%Y%m%d%H')) instead of a string. Then you can use DATE_FORMAT or EXTRACT with indexed columns:

   ALTER TABLE TAG_COUNTER ADD COLUMN hour_dt DATETIME GENERATED ALWAYS AS (STR_TO_DATE(time_id, '%Y%m%d%H')) STORED;
   CREATE INDEX idx_account_hour ON TAG_COUNTER(account, hour_dt);
   

   Querying monthly stats becomes:

   SELECT
     DATE_FORMAT(hour_dt, '%Y%m') AS month,
     tag_name,
     SUM(CAST(JSON_EXTRACT(counters, CONCAT('$.', tag_name)) AS UNSIGNED)) AS total_hits
   FROM TAG_COUNTER,
        JSON_TABLE(
          JSON_KEYS(counters),
          '$[*]' COLUMNS(tag_name VARCHAR(255) PATH '$')
        ) AS tag_list
   GROUP BY month, tag_name
   ORDER BY total_hits DESC;
   

   This is more semantically correct for time data and works seamlessly with MySQL’s date functions.

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