今天聊一聊sql優(yōu)化的一則案例分析。

適用數(shù)據(jù)庫：達(dá)夢（DM8）/ MySQL

場景：任務(wù)列表查詢接口 work_hour_task 壓力測試

一、背景

測試人員對任務(wù)列表查詢接口進(jìn)行并發(fā)壓測時(shí)，出現(xiàn)大量 404 響應(yīng)錯(cuò)誤。經(jīng)初步排查，這些 404 并非業(yè)務(wù)邏輯主動返回，而是接口響應(yīng)超時(shí)后由網(wǎng)關(guān)/Nginx 拋出的超時(shí)錯(cuò)誤。

觸發(fā)鏈路如下：

SQL 慢查詢（全表掃描 + N+1 子查詢 + filesort）
  → 接口響應(yīng)時(shí)間飆升
  → 壓測并發(fā)請求大量堆積
  → 網(wǎng)關(guān)請求排隊(duì)超時(shí)
  → 返回 404

二、問題 SQL

原始 SQL（MySQL 版本）

EXPLAIN SELECT
    t.id, t.tenant_id, t.task_name, t.is_project_task, t.team_code, t.project_code,
    t.task_type, t.task_sub_type, t.responsible_person_code, t.work_hours, t.estimate_work_hours,
    t.task_desc, t.process, t.plan_start_date, t.plan_end_date, t.task_source,
    t.actual_start_time, t.actual_end_time, t.related_team_code, t.rel_id,
    t.STATUS, t.ext_info, t.version, t.deleted,
    t.created_by, t.created_time, t.updated_by, t.updated_time
FROM work_hour_task t
WHERE t.deleted = 0
  AND (
    t.team_code IN ('webDev')
    OR EXISTS (
      SELECT 1 FROM work_hour_task_related_team tr
      WHERE tr.task_rel_id = t.rel_id
        AND tr.deleted = 0
        AND tr.team_code IN ('webDev')
        AND tr.TENANT_ID = 'hkbank'
    )
  )
  AND t.TENANT_ID = 'hkbank'
ORDER BY t.created_time DESC
LIMIT 15

達(dá)夢版本結(jié)構(gòu)基本一致，WHERE 條件中額外使用了 FIND_IN_SET 函數(shù)代替 EXISTS 子查詢。

三、執(zhí)行計(jì)劃解讀

MySQL 版本執(zhí)行計(jì)劃

達(dá)夢版本執(zhí)行計(jì)劃

四、三大核心問題

問題一：全表掃描（type = ALL）

主表 t 有 3 個(gè)候選索引（idx_task_team、idx_task_page_core、idx_task_count_core），但優(yōu)化器最終 key = NULL，全部放棄，被迫全表掃描 1233 行。

根本原因： WHERE 條件中對列使用了函數(shù)（FIND_IN_SET）或存在關(guān)聯(lián)子查詢（EXISTS），優(yōu)化器無法利用索引進(jìn)行范圍掃描。

問題二：DEPENDENT SUBQUERY（N+1 問題）

EXISTS 子查詢的 select_type 為 DEPENDENT SUBQUERY，意味著它依賴外層主表的每一行逐行觸發(fā)執(zhí)行。

主表掃描 1233 行 × 子查詢執(zhí)行 1233 次 = I/O 實(shí)際放大 1233 倍

子查詢雖然單次走了唯一索引（eq_ref，rows=1），但積累后總代價(jià)極高，這是典型的 N+1 問題。

問題三：Using filesort（額外排序）

ORDER BY t.created_time DESC 無法利用現(xiàn)有索引完成排序，數(shù)據(jù)庫須在內(nèi)存或磁盤中對全量結(jié)果集做額外排序。高并發(fā)壓測時(shí)，排序操作大量占用 CPU 和內(nèi)存，進(jìn)一步拖慢響應(yīng)時(shí)間。

五、優(yōu)化方案

改寫思路：兩段式查詢

將原來「一條復(fù)雜 SQL 承包所有邏輯」的寫法，拆分為兩步：

• 第一步（ID 收集層）：輕量查詢先收集符合條件的 task id 列表，用 UNION ALL 分別處理兩種匹配條件，各自加 ORDER BY + LIMIT，合并后取 Top N。
• 第二步（明細(xì)查詢層）：用主鍵 IN (id1, id2, ...) 查詢完整字段，走主鍵索引，無子查詢，無 filesort。

這種「分步查詢」模式是處理 OR + 子查詢 + 排序分頁 組合場景的標(biāo)準(zhǔn)實(shí)踐，徹底解耦了「找哪些記錄」和「取這些記錄的字段」兩個(gè)問題。

優(yōu)化后 SQL（MySQL 版本）

-- 第二步：用主鍵 IN 查詢明細(xì)，消除子查詢和 filesort
SELECT
    id, tenant_id, task_name, is_project_task, team_code, project_code,
    task_type, task_sub_type, responsible_person_code, work_hours, estimate_work_hours,
    task_desc, process, plan_start_date, plan_end_date, task_source,
    actual_start_time, actual_end_time, related_team_code, rel_id,
    STATUS, ext_info, version, deleted,
    created_by, created_time, updated_by, updated_time
FROM work_hour_task
WHERE id IN (1942, 1941, 1940, 1939, 1938, 1937, 1936, 1935,
             1934, 1933, 1932, 1931, 1930, 1929, 1928)
  AND deleted = 0
  AND TENANT_ID = 'hkbank'

優(yōu)化后執(zhí)行計(jì)劃

達(dá)夢版本額外改寫

達(dá)夢原 SQL 中 FIND_IN_SET 將函數(shù)施加于列上導(dǎo)致索引失效，需在應(yīng)用層將參數(shù)預(yù)先拆分：

-- 改前（索引失效）
OR FIND_IN_SET(?, RELATED_TEAM_CODE) > 0
-- 改后（應(yīng)用層拆分參數(shù)后傳入，索引可正常使用）
OR t.related_team_code IN (?, ?, ?)

推薦覆蓋索引

-- MySQL
ALTER TABLE work_hour_task
ADD INDEX idx_covering (TENANT_ID, deleted, team_code, created_time DESC);

-- 達(dá)夢
CREATE INDEX idx_covering
ON work_hour_task (TENANT_ID, deleted, team_code, created_time DESC);

六、優(yōu)化前后對比

優(yōu)化前執(zhí)行路徑：全表掃描 1233 行 → 逐行觸發(fā)子查詢（× 1233 次）→ filesort 排序 → 取 Top 15

優(yōu)化后執(zhí)行路徑：主鍵 range 掃描 15 行 → 直接返回（無子查詢，無排序）

七、經(jīng)驗(yàn)總結(jié)與規(guī)范建議

本次問題根因清單

SQL 開發(fā)規(guī)范建議

禁止在 WHERE 條件的列上直接使用函數(shù)（FIND_IN_SET、DATE()、YEAR() 等），改為在參數(shù)側(cè)做處理，保持列的"裸露"。

慎用 EXISTS / IN 關(guān)聯(lián)子查詢，考慮改寫為 JOIN 或分步查詢，避免產(chǎn)生 DEPENDENT SUBQUERY。

分頁列表接口推薦「兩段式查詢」：先查 id 列表（輕查詢，走索引），再用主鍵 IN 查完整字段，兩步走比一步復(fù)雜查詢更可控。

新建索引需覆蓋 WHERE 過濾字段 + ORDER BY 字段，減少回表和 filesort，字段順序按選擇性從高到低排列。

上線前必須通過 EXPLAIN 驗(yàn)證執(zhí)行計(jì)劃，重點(diǎn)關(guān)注：

• type 不得為 ALL
• Extra 不得出現(xiàn) Using filesort / Using temporary

壓測出現(xiàn)大量非業(yè)務(wù) 404 時(shí)，優(yōu)先排查接口響應(yīng)時(shí)間和數(shù)據(jù)庫慢查詢?nèi)罩?，而非只看?yīng)用層錯(cuò)誤日志。

執(zhí)行計(jì)劃關(guān)鍵字速查

總結(jié)

這次優(yōu)化的核心收獲是：慢不一定在業(yè)務(wù)代碼里，404 也不一定是路由問題。當(dāng)壓測出現(xiàn)大量超時(shí)類 404 時(shí)，第一步應(yīng)該打開慢查詢?nèi)罩?，?EXPLAIN 拿出來看。

記住三個(gè)關(guān)鍵詞：全表掃描、N+1、filesort。這三者任意一個(gè)在高并發(fā)下都足以拖垮接口，三個(gè)疊加則必然超時(shí)。

優(yōu)化的本質(zhì)不是"加索引"這么簡單，而是要理解優(yōu)化器的決策邏輯——讓 WHERE 條件能走索引，讓子查詢不隨主表行數(shù)膨脹，讓 ORDER BY 不產(chǎn)生額外排序，三點(diǎn)都滿足，性能自然就上去了。

到此這篇關(guān)于SQL性能優(yōu)化之壓測404的根因追查與解決方案的文章就介紹到這了,更多相關(guān)SQL壓測404錯(cuò)誤排查與解決內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論