ECSHOP 高 CPU 佔用根因分析與 MySQL 調優實戰

現象描述

• 高峰期（幾十人在線）CPU 飆升至 100%
• 虛擬主機與自建伺服器均複現
• 慢查詢日誌鎖定兩條核心 SQL

問題 SQL 剖析

SQL 1：熱銷商品統計

SELECT DISTINCT SUM(goods_number) AS num
FROM ecs_order_info AS o, ecs_order_goods AS g
WHERE o.order_id = g.order_id
  AND o.order_status = '1'
  AND o.shipping_status IN ('1','2')
  AND o.pay_status IN ('2','1')
GROUP BY g.goods_id
HAVING num > 31

SQL 2：銷量排行 TOP 10

SELECT g.goods_id, g.goods_name, g.goods_img,
       SUM(og.goods_number) AS goods_number
FROM ecs_goods AS g, ecs_order_info AS o, ecs_order_goods AS og
WHERE g.is_on_sale = 1
  AND g.is_alone_sale = 1
  AND g.is_delete = 0
  AND og.order_id = o.order_id
  AND og.goods_id = g.goods_id
  AND o.order_status = '1'
  AND (o.pay_status = '2' OR o.pay_status = '1')
  AND (o.shipping_status = '1' OR o.shipping_status = '2')
GROUP BY g.goods_id
ORDER BY goods_number DESC, g.goods_id DESC
LIMIT 10

根因定位

索引優化實戰

建議索引（按 WHERE/JOIN/GROUP BY/ORDER BY 欄位）

-- ecs_order_info
ALTER TABLE ecs_order_info
  ADD INDEX idx_status_ship_pay (order_status, shipping_status, pay_status);

-- ecs_order_goods
ALTER TABLE ecs_order_goods
  ADD INDEX idx_order_id (order_id),
  ADD INDEX idx_goods_id (goods_id);

-- ecs_goods
ALTER TABLE ecs_goods
  ADD INDEX idx_sale_alone_delete (is_on_sale, is_alone_sale, is_delete);

優化後 EXPLAIN 對比

深度調優建議

1. SQL 改寫：消除 DISTINCT + SUM 混用、優化 IN/OR

-- SQL 1 改寫：移除無意義 DISTINCT（SUM 已聚合）
SELECT g.goods_id, SUM(g.goods_number) AS total_num
FROM ecs_order_goods g
JOIN ecs_order_info o ON o.order_id = g.order_id
WHERE o.order_status = 1
  AND o.shipping_status IN (1,2)
  AND o.pay_status IN (1,2)
GROUP BY g.goods_id
HAVING total_num > 31

-- SQL 2 改寫：使用覆蓋索引、避免 filesort
SELECT g.goods_id, g.goods_name, g.goods_img, SUM(og.goods_number) AS goods_number
FROM ecs_goods g
JOIN ecs_order_goods og ON og.goods_id = g.goods_id
JOIN ecs_order_info o ON o.order_id = og.order_id
WHERE g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0
  AND o.order_status = 1
  AND o.pay_status IN (1,2)
  AND o.shipping_status IN (1,2)
GROUP BY g.goods_id, g.goods_name, g.goods_img
ORDER BY goods_number DESC, g.goods_id DESC
LIMIT 10

2. 架構層面：快取與異步化

• Redis 快取熱銷排行：每 5-10 分鐘離線任務重算，前端直接讀快取，DB 壓力歸零
• 資料歸檔：半年以上訂單移至 ecs_order_info_archive，主表維持 < 5 萬行
• 分表策略：按月/季分表 ecs_order_info_2024_Q1，查詢透過 Merge 表或應用層路由

3. MySQL 參數微調

[mysqld]
# 針對臨時表與排序
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
read_rnd_buffer_size = 2M

# 針對連接與查詢快取
thread_cache_size = 100
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

驗證成果

• CPU 佔用從 100% 降至 < 20%（峰值）
• 頁面響應從 3-5 秒降至 < 200ms
• 慢查詢日誌不再出現該兩條 SQL

避坑清單

1. ❌ 盲目加大 tmp_table_size → ✅ 先看 EXPLAIN 定位全表掃描
2. ❌ 只加單欄索引 → ✅ 組合索引匹配 WHERE/JOIN/GROUP BY/ORDER BY 順序
3. ❌ 忽略資料增長 → ✅ 定期歸檔、分表、快取分層
4. ❌ 依賴應用層修補 → ✅ SQL 改寫 + 索引 + 快取 三位一體