如何优化耗时120秒的MySQL存储过程？缓存怎么用？

一、SQL语句优化（直接提升执行速度） #

1. 修复字段类型转换问题，避免索引失效 #

• 把存储过程参数startDate、endDate的类型从VARCHAR(50)改为DATE，消除字符串转日期的额外开销：

  CREATE DEFINER=`xxxx`@`%` PROCEDURE `sp_test`( IN merchantId int, IN startDate DATE,IN endDate DATE)
  

• 原条件CAST(transaction.time as DATE) BETWEEN startDate AND endDate会导致transaction.time的索引无法被利用，替换为范围查询：

  WHERE transaction.time >= startDate 
    AND transaction.time < DATE_ADD(endDate, INTERVAL 1 DAY)
  

2. 添加针对性索引，加速关联与过滤 #

给以下字段创建复合索引或单列索引，大幅减少表扫描次数：

• transaction表：(payment_status, time, id)（先过滤支付状态，再按时间范围筛选，最后关联ticket表）
• ticket表：(transaction_id, level, type, reseller_id, id)（关联transaction表，过滤level/type条件，关联channel表）
• v_transaction_items_details表：(ticket_id, item_name)（关联ticket表，关联inventory表）
• inventory表：(status, merchant_id, sku, inventory_category_id, inventory_sub_category_id)（过滤状态、商户ID，关联分类表）
• channel表：(sub_agent_id, merchant_id)（关联ticket表，获取商户ID）

3. 拆分WHERE条件中的OR逻辑，避免索引失效 #

原条件(inventory.merchant_id = merchantId OR tbl_ticket.merchant_id = merchantId)会导致索引无法生效，拆分为UNION ALL查询（避免重复数据）：

SELECT 
  inventory.sku AS 'SKU',
  inventory.description AS 'Description',
  inventory_category.name as 'Category',
  inventory_sub_category.name as 'Sub Category',
  IFNULL(tbl_ticket.price,0) AS 'Unit Price',
  IFNULL(tbl_ticket.quantity_total,0) AS 'Total Issued',
  IFNULL(ROUND(tbl_ticket.price,2),0) * IFNULL(tbl_ticket.quantity_total,0) AS 'Gross Amount',
  IFNULL(tbl_ticket.promo_code_discount, 0 ) AS 'Total Discount',
  ROUND((IFNULL(tbl_ticket.price,0) * IFNULL(tbl_ticket.quantity_total,0) ) -  IFNULL(tbl_ticket.promo_code_discount, 0 ),2) AS 'Net Amount'
FROM inventory
LEFT JOIN inventory_category ON inventory.inventory_category_id = inventory_category.id
LEFT JOIN inventory_sub_category ON inventory.inventory_sub_category_id = inventory_sub_category.id
LEFT JOIN(
  SELECT 
    inv.sku, inv.price, channel.merchant_id, 
    SUM(ticket.quantity_total) as 'quantity_total',
    SUM(ticket.promo_code_discount) as 'promo_code_discount'
  FROM inventory inv
  JOIN v_transaction_items_details ON inv.sku = v_transaction_items_details.item_name
  JOIN ticket on ticket.id = v_transaction_items_details.ticket_id
  JOIN transaction ON ticket.transaction_id = transaction.id
  JOIN channel ON channel.sub_agent_id = ticket.reseller_id
  WHERE transaction.time >= startDate 
    AND transaction.time < DATE_ADD(endDate, INTERVAL 1 DAY)
    AND transaction.payment_status = 2
    AND ticket.level = 1
    AND ticket.type = 1
  GROUP BY inv.sku, inv.price, channel.merchant_id
) tbl_ticket on tbl_ticket.sku = inventory.sku
WHERE inventory.status = 1 AND inventory.merchant_id = merchantId

UNION ALL

SELECT 
  inventory.sku AS 'SKU',
  inventory.description AS 'Description',
  inventory_category.name as 'Category',
  inventory_sub_category.name as 'Sub Category',
  IFNULL(tbl_ticket.price,0) AS 'Unit Price',
  IFNULL(tbl_ticket.quantity_total,0) AS 'Total Issued',
  IFNULL(ROUND(tbl_ticket.price,2),0) * IFNULL(tbl_ticket.quantity_total,0) AS 'Gross Amount',
  IFNULL(tbl_ticket.promo_code_discount, 0 ) AS 'Total Discount',
  ROUND((IFNULL(tbl_ticket.price,0) * IFNULL(tbl_ticket.quantity_total,0) ) -  IFNULL(tbl_ticket.promo_code_discount, 0 ),2) AS 'Net Amount'
FROM inventory
LEFT JOIN inventory_category ON inventory.inventory_category_id = inventory_category.id
LEFT JOIN inventory_sub_category ON inventory.inventory_sub_category_id = inventory_sub_category.id
LEFT JOIN(
  SELECT 
    inv.sku, inv.price, channel.merchant_id, 
    SUM(ticket.quantity_total) as 'quantity_total',
    SUM(ticket.promo_code_discount) as 'promo_code_discount'
  FROM inventory inv
  JOIN v_transaction_items_details ON inv.sku = v_transaction_items_details.item_name
  JOIN ticket on ticket.id = v_transaction_items_details.ticket_id
  JOIN transaction ON ticket.transaction_id = transaction.id
  JOIN channel ON channel.sub_agent_id = ticket.reseller_id
  WHERE transaction.time >= startDate 
    AND transaction.time < DATE_ADD(endDate, INTERVAL 1 DAY)
    AND transaction.payment_status = 2
    AND ticket.level = 1
    AND ticket.type = 1
  GROUP BY inv.sku, inv.price, channel.merchant_id
) tbl_ticket on tbl_ticket.sku = inventory.sku
WHERE inventory.status = 1 AND tbl_ticket.merchant_id = merchantId AND inventory.merchant_id != merchantId

4. 移除不必要的外层GROUP BY #

如果inventory.sku是主键或唯一约束，外层的GROUP BY inventory.sku,inventory.description,inventory_category.name,inventory_sub_category.name完全多余，直接删除即可，减少分组计算的开销。

5. 简化子查询关联逻辑 #

子查询中无需重复JOINinventory，可以通过v_transaction_items_details.item_name直接关联获取inventory.price，减少表关联次数：

SELECT 
  v_transaction_items_details.item_name AS sku,
  inv.price,
  channel.merchant_id,
  SUM(ticket.quantity_total) as quantity_total,
  SUM(ticket.promo_code_discount) as promo_code_discount
FROM v_transaction_items_details
JOIN ticket ON ticket.id = v_transaction_items_details.ticket_id
JOIN transaction ON ticket.transaction_id = transaction.id
JOIN channel ON channel.sub_agent_id = ticket.reseller_id
JOIN inventory inv ON inv.sku = v_transaction_items_details.item_name
WHERE transaction.time >= startDate 
  AND transaction.time < DATE_ADD(endDate, INTERVAL 1 DAY)
  AND transaction.payment_status = 2
  AND ticket.level = 1
  AND ticket.type = 1
GROUP BY v_transaction_items_details.item_name, inv.price, channel.merchant_id

二、缓存方案实现 #

1. 应用层Redis缓存（推荐） #

• 缓存Key设计：用查询参数组合作为唯一Key，比如sp_test:{merchantId}:{startDate}:{endDate}，确保不同参数对应不同缓存。
• 缓存内容：将存储过程的查询结果序列化为JSON字符串存储到Redis。
• 过期策略：根据数据更新频率设置过期时间，比如按天统计的报表设置24小时过期；若实时性要求高，可设置1-5分钟过期。
• 缓存失效：当inventory、transaction、ticket等相关表发生数据新增/修改/删除时，主动删除对应参数组合的缓存Key，避免脏数据。

2. 物化视图（适合非实时报表场景） #

• 创建结果表sp_test_result，结构与存储过程查询结果一致：

  CREATE TABLE sp_test_result (
    SKU VARCHAR(50),
    Description TEXT,
    Category VARCHAR(100),
    Sub_Category VARCHAR(100),
    Unit_Price DECIMAL(10,2),
    Total_Issued INT,
    Gross_Amount DECIMAL(10,2),
    Total_Discount DECIMAL(10,2),
    Net_Amount DECIMAL(10,2),
    merchantId INT,
    startDate DATE,
    endDate DATE,
    PRIMARY KEY (merchantId, startDate, endDate, SKU)
  );
  

• 编写定时任务（比如MySQL事件调度器或外部脚本），定期执行存储过程并将结果插入/更新到sp_test_result中。
• 查询时直接从sp_test_result中获取对应merchantId、startDate、endDate的数据，速度极快。

3. MySQL查询缓存（仅适合5.7及以下版本） #

• 在存储过程的SELECT语句前添加SQL_CACHE关键字：

  SELECT SQL_CACHE ...
  

• 注意：MySQL 8.0已移除查询缓存，且该缓存对频繁更新的表效率极低，仅适合数据极少变动的场景。

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