您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 特殊語法與優化原則大全：加密、分頁、比較、元數據、優化鐵律

編輯：MySQL 資料庫相關知識

SQL Server (T-SQL) 擁有大量專有語法與優化規則。本文匯總 14 類特殊語法 (加密、分頁、校驗和、元數據查詢、殺進程、重命名等) 與 4 大維度優化原則 (索引、游標、抽取上載、模式優化)，附反模式對比表，作為 T-SQL 開發速查手冊。

一、14 類特殊實用語法

場景	語法/函數	示例
按拼音/筆畫排序	`COLLATE Chinese_PRC_Stroke_CI_AS`	`SELECT * FROM T ORDER BY Name COLLATE Chinese_PRC_Stroke_CI_AS`
密碼加密/比較	`PWDENCRYPT()`, `PWDCOMPARE()`	`SELECT PWDCOMPARE('plain', pwd_hash) = 1`
動態獲取表列	`sys.columns` + 動態 SQL	見代碼片段
查看磁盤分區空間	`EXEC master..xp_fixeddrives`	返回驅動器、可用 MB
比較兩表數據一致性	`CHECKSUM_AGG(BINARY_CHECKSUM(*))`	`IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM A) = (SELECT ... FROM B) PRINT '相等'`
殺掉 Profiler 進程	游標遍歷 `sysprocesses` + `KILL`	見代碼片段
分頁查詢 (舊版)	`TOP N` + 子查詢排除	`SELECT TOP 10 * FROM T WHERE ID NOT IN (SELECT TOP 20 ID FROM T)`
分頁查詢 (2012+)	`OFFSET ... ROWS FETCH NEXT ... ROWS ONLY`	`SELECT * FROM T ORDER BY ID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`
重命名數據庫	`sp_renamedb 'old', 'new'`	需單用戶模式
獲取所有用戶表	`SELECT name FROM sysobjects WHERE xtype='U' AND status>=0`	或 `sys.tables`
獲取表所有列	`SELECT name FROM syscolumns WHERE id=OBJECT_ID('tbl')`	或 `sys.columns`
查看表相關對象 (視圖/SP/函數)	`sysobjects JOIN syscomments`	`WHERE b.text LIKE '%tbl%'`
查看所有存儲過程	`SELECT name FROM sysobjects WHERE xtype='P'`	或 `sys.procedures`
查看用戶創建的庫 (排除 sa)	`SELECT * FROM master..sysdatabases WHERE sid <> 0x01`	或 `sid NOT IN (SELECT sid FROM master..syslogins WHERE name='sa')`
查詢表列與數據類型	`INFORMATION_SCHEMA.COLUMNS`	`SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='tbl'`

二、優化四大鐵律 (附反模式對比)

1. 索引優化

反模式	優化模式	原理
大批量插入前不刪索引	插入 >10% 數據量時：刪索引 → 插數據 → 重建索引	避免逐行維護索引 B+樹
`WHERE func(col) = val`	計算列持久化 + 索引 / 應用層預計算	函數破壞 SARGability，導致掃描
`WHERE col != val` / `NOT`	改寫為 `NOT EXISTS` / `LEFT JOIN ... IS NULL`	否定條件無法 Index Seek
`WHERE col > 3`	`WHERE col >= 4` (整數列)	Seek 直接定位 4，避免掃描 3 後續
非要用函數列索引	計算列持久化 + 索引 (Computed Column)	將函數結果物化，恢復 SARGability

2. 游標優化 (盡量不用，必用則優)

源表加時間戳：增量抽取，僅處理最新修改數據。
過濾已存在記錄：INSERT ... WHERE NOT EXISTS (SELECT 1 FROM dim WHERE code = ods.code)。
顯式游標：DECLARE cur CURSOR FOR ... OPEN cur FETCH NEXT ... 避免隱式游標雙重檢查 too_many_rows。

3. 數據抽取/上載 SQL 優化

反模式	優化模式	原理
`WHERE` 連接順序隨意	表連接寫在前、過濾條件寫在後 (Oracle 自下而上解析)	早期減少結果集，降低後續連接成本
全表刪除用 `DELETE`	`TRUNCATE TABLE` (DDL、無日志、不可回滾、重置標識列)	極速、最小日志
事務提交稀疏	批次提交 (`COMMIT` 每 1000-5000 行)	釋放鎖/日志空間、減少阻塞
`IN` 子查詢	`EXISTS` / `JOIN`	`EXISTS` 半連接、早期終止
`NOT IN` 子查詢	`NOT EXISTS` (NULL 安全)	`NOT IN` 含 NULL 返回空集
`GROUP BY ... HAVING` 過濾	先 `WHERE` 過濾再 `GROUP BY`，或 `DISTINCT` 替代無聚合分組	減少分組輸入行數
`UNION` (去重)	`UNION ALL` (確無重複)	省去 Sort Distinct，快 3-5 倍
表與索引同文件組	分離數據文件組與索引文件組，不同物理磁盤	IO 並行、減少爭用

4. 模式優化 (Schema Design)

歸檔舊數據：分區表/歷史表移冷數據，縮小熱表掃描範圍。
精準數據類型：TINYINT/SMALLINT/INT/BIGINT 按范圍選、VARCHAR(n) 實際最大長度、DATETIME2(3) 替代 DATETIME (精度更高、存儲更小)。
BLOB/TEXT 拆分：大字段低頻訪問 → 獨立表 1:1 關聯，熱表瘦身。
會話數據外移：Redis/Memcached 存 Session，避免 DB 寫入風暴。
VARCHAR 替代 CHAR：變長存儲節省空間 (UTF-8 環境下 CHAR 也按字節固定)。
DDL 變更謹慎：大表加列/改類型用 pt-online-schema-change / 新建表換名。
測試環境鏡像生產：數據量、索引、統計信息一致，驗證執行計劃。

三、總結速查卡

寫 SQL 前問三句：
1. 這查詢能否走覆蓋索引？(SELECT 列 ⊆ 索引列)
2. WHERE/JOIN/ORDER BY 列是否有合適索引？(順序、方向、SARGability)
3. 結果集是否可控？(TOP/LIMIT、分頁、避免全表掃描)

優化優先級：
索引覆蓋消除掃描 > 統計信息修正估算 > SQL 改寫恢復 SARGability > 
參數調優 (並行度/內存/IO) > 架構調整 (分區/分片/讀寫分離/緩存)