您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

資深工程師必會的 4 條進階 SQL 技巧：從 show processlist 到聯表操作

編輯：MySQL 綜合教學

資深工程師必會的 4 條進階 SQL 技巧：從 show processlist 到聯表操作

會寫 CRUD 不代表精通資料庫。本文精選四個能直觀體現工程師經驗深淺的實戰技巧：進程診斷、資料一致性驗證、索引哲學、複雜聯表操作，附帶真實故障案例與最佳實踐，助你從「能跑通」進階到「懂原理、會調優、敢負責」。

一、show processlist：資料庫的「心電圖」

SHOW [FULL] PROCESSLIST;
-- 關鍵欄位：Id, User, Host, db, Command, Time, State, Info

核心狀態解碼與應對策略：

State	含義	典型成因	應對動作
Sleep	連線閒置待命	連線池洩漏、未釋放連線、長事務	檢查應用 close()、縮短 wait_timeout、kill 閒置進程
Waiting for net / Reading/Writing to net	網路 I/O 等待	結果集過大、頻寬飽和、跨機房延遲	優化查詢縮小結果集、啟用壓縮、就近部署
Locked	表級鎖等待	MyISAM 長查詢阻塞寫入、DDL 操作	遷移 InnoDB 行鎖、拆分大查詢、維護窗口執行 DDL
Copy to tmp table	建立臨時表	索引缺失導致無法覆蓋、複雜 JOIN/ORDER BY/GROUP BY	補齊索引、拆分查詢、調大 tmp_table_size/max_heap_table_size
Sending data	讀取行並發送	掃描行數過多、隨機 I/O、結果集大	優化索引、分頁優化、覆蓋索引、縮小選擇列
Sorting for group/order	排序操作	排序列無索引、排序緩衝不足	建立符合順序的複合索引、增大 sort_buffer_size
Storing result to query cache	寫入查詢快取	快取碎片化、鎖競爭	FLUSH QUERY CACHE、調優 query_cache_size、或直接關閉

實戰案例：某社區站點資料庫阻塞，show processlist 發現某冷門小站產生海量 Copy to tmp table，佔滿磁碟 I/O 導致全站雪崩。KILL 該進程瞬間恢復。教訓：定期巡檢長時間運行進程，建立自動化慢查詢/阻塞告警。

二、checksum 與 load data：資料遷移的「體檢單」與「高速公路」

CHECKSUM TABLE：邏輯備份前後對比 checksum 值，秒級驗證資料一致性，避免「備份成功、恢復失敗」
LOAD DATA INFILE：批量導入比 INSERT 快 20 倍以上
關鍵點：
LOAD DATA [LOCAL] INFILE 'path/file.csv' INTO TABLE t FIELDS TERMINATED BY ',';
Windows 路徑用正斜線或雙反斜線；需 FILE 權限；LOCAL 關鍵字決定讀取端（客戶端 vs 伺服端）

三、索引哲學：克制與精準

必要性原則：只為 WHERE/JOIN/ORDER BY/GROUP BY 高選擇性列建索引
唯一優先：業務鍵（手機、身分證、訂單號）必建唯一索引，兼具約束與查詢加速
主鍵設計：InnoDB 聚簇索引即主鍵，推薦自增 BIGINT，避免 UUID/字串導致頁分裂
定期審計：pt-index-usage 分析未用索引，pt-duplicate-key-checker 檢測冗餘索引

四、複雜聯表操作：更新、刪除、事務一氣呵成

-- 多表更新（別名必加）
UPDATE t1
LEFT JOIN t2 ON t1.uid = t2.uid
SET t1.name = t2.name, t1.desc = 'xxx'
WHERE t1.date = '2023-10-11';

-- 多表刪除（指定刪除目標表別名）
DELETE t1, t2, t3
FROM t1
LEFT JOIN t2 ON t1.uid = t2.uid
INNER JOIN t3 ON t2.uid = t3.uid
WHERE t1.date = '2023-10-11';

-- 顯式事務控制
SET @@autocommit = 0;
START TRANSACTION;
  -- 業務 SQL 序列
COMMIT; -- 或 ROLLBACK;

避坑指南：