sql-optimization

SQL 效能最佳化助手

針對 ${selection}（若未選取則針對整個專案）提供專業 SQL 效能最佳化。專注於跨 MySQL、PostgreSQL、SQL Server、Oracle 及其他 SQL 資料庫的通用最佳化技巧。

🎯 核心最佳化領域

查詢效能分析

-- ❌ 不良：低效率的查詢模式
SELECT * FROM orders o
WHERE YEAR(o.created_at) = 2024
  AND o.customer_id IN (
      SELECT c.id FROM customers c WHERE c.status = 'active'
  );

-- ✅ 良好：最佳化查詢搭配適當的索引提示
SELECT o.id, o.customer_id, o.total_amount, o.created_at
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01' 
  AND o.created_at < '2025-01-01'
  AND c.status = 'active';

-- 建議索引：
-- CREATE INDEX idx_orders_created_at ON orders(created_at);
-- CREATE INDEX idx_customers_status ON customers(status);
-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);

索引策略最佳化

-- ❌ 不良：不佳的索引策略
CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);

-- ✅ 良好：最佳化複合索引
-- 針對先依 email 篩選，再依 created_at 排序的查詢
CREATE INDEX idx_users_email_created ON users(email, created_at);

-- 針對全文名稱搜尋
CREATE INDEX idx_users_name ON users(last_name, first_name);

-- 針對使用者狀態查詢
CREATE INDEX idx_users_status_created ON users(status, created_at)
WHERE status IS NOT NULL;

子查詢最佳化

-- ❌ 不良：關聯子查詢
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(price) 
    FROM products p2 
    WHERE p2.category_id = p.category_id
);

-- ✅ 良好：使用視窗函數
SELECT product_name, price
FROM (
    SELECT product_name, price,
           AVG(price) OVER (PARTITION BY category_id) as avg_category_price
    FROM products
) ranked
WHERE price > avg_category_price;

📊 效能調校技巧

JOIN 最佳化

-- ❌ 不良：低效率的 JOIN 順序與條件
SELECT o.*, c.name, p.product_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01'
  AND c.status = 'active';

-- ✅ 良好：最佳化 JOIN 搭配篩選
SELECT o.id, o.total_amount, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at > '2024-01-01';

分頁最佳化

-- ❌ 不良：使用 OFFSET 的分頁（大偏移量時效能差）
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ 良好：基於游標的分頁
SELECT * FROM products 
WHERE created_at < '2024-06-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- 或使用基於 ID 的游標
SELECT * FROM products 
WHERE id > 1000
ORDER BY id 
LIMIT 20;

聚合最佳化

-- ❌ 不良：多個獨立的聚合查詢
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders WHERE status = 'shipped';
SELECT COUNT(*) FROM orders WHERE status = 'delivered';

-- ✅ 良好：單一查詢搭配條件聚合
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count
FROM orders;

🔍 查詢反模式

SELECT 效能問題

-- ❌ 不良：SELECT * 反模式
SELECT * FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

-- ✅ 良好：明確選取欄位
SELECT lt.id, lt.name, at.value
FROM large_table lt
JOIN another_table at ON lt.id = at.ref_id;

WHERE 子句最佳化

-- ❌ 不良：在 WHERE 子句中使用函數
SELECT * FROM orders 
WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';

-- ✅ 良好：對索引友善的 WHERE 子句
SELECT * FROM orders 
WHERE customer_email = 'john@example.com';
-- 考慮：CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));

OR 與 UNION 最佳化

-- ❌ 不良：複雜的 OR 條件
SELECT * FROM products 
WHERE (category = 'electronics' AND price < 1000)
   OR (category = 'books' AND price < 50);

-- ✅ 良好：使用 UNION 以獲得更好的最佳化
SELECT * FROM products WHERE category = 'electronics' AND price < 1000
UNION ALL
SELECT * FROM products WHERE category = 'books' AND price < 50;

📈 資料庫無關的最佳化

批次操作

-- ❌ 不良：逐筆操作
INSERT INTO products (name, price) VALUES ('Product 1', 10.00);
INSERT INTO products (name, price) VALUES ('Product 2', 15.00);
INSERT INTO products (name, price) VALUES ('Product 3', 20.00);

-- ✅ 良好：批次插入
INSERT INTO products (name, price) VALUES 
('Product 1', 10.00),
('Product 2', 15.00),
('Product 3', 20.00);

暫存表使用

-- ✅ 良好：對複雜操作使用暫存表
CREATE TEMPORARY TABLE temp_calculations AS
SELECT customer_id, 
       SUM(total_amount) as total_spent,
       COUNT(*) as order_count
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;

-- 使用暫存表進行後續計算
SELECT c.name, tc.total_spent, tc.order_count
FROM temp_calculations tc
JOIN customers c ON tc.customer_id = c.id
WHERE tc.total_spent > 1000;

🛠️ 索引管理

索引設計原則

-- ✅ 良好：涵蓋索引設計
CREATE INDEX idx_orders_covering 
ON orders(customer_id, created_at) 
INCLUDE (total_amount, status);  -- SQL Server 語法
-- 或：CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- 其他資料庫

部分索引策略

-- ✅ 良好：針對特定條件的部分索引
CREATE INDEX idx_orders_active 
ON orders(created_at) 
WHERE status IN ('pending', 'processing');

📊 效能監控查詢

查詢效能分析

-- 識別慢查詢的通用方法
-- （具體語法因資料庫而異）

-- 針對 MySQL：
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC;

-- 針對 PostgreSQL：
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC;

-- 針對 SQL Server：
SELECT 
    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

🎯 通用最佳化檢查清單

查詢結構

[ ] 在正式環境查詢中避免使用 SELECT *
[ ] 使用適當的 JOIN 類型（INNER 與 LEFT/RIGHT）
[ ] 在 WHERE 子句中儘早進行篩選
[ ] 在適當情況下使用 EXISTS 取代 IN 子查詢
[ ] 避免在 WHERE 子句中使用會阻礙索引使用的函數

索引策略

[ ] 對經常查詢的欄位建立索引
[ ] 以正確的欄位順序使用複合索引
[ ] 避免過度索引（影響 INSERT/UPDATE 效能）
[ ] 在有益的情況下使用涵蓋索引
[ ] 針對特定查詢模式建立部分索引

資料型別與綱要

[ ] 使用適當的資料型別以提升儲存效率
[ ] 適當正規化（OLTP 使用 3NF，OLAP 使用反正規化）
[ ] 使用條件約束協助查詢最佳化器
[ ] 在適當情況下對大型資料表進行分割

查詢模式

[ ] 使用 LIMIT/TOP 控制結果集
[ ] 實作高效的分頁策略
[ ] 對大量資料變更使用批次操作
[ ] 避免 N+1 查詢問題
[ ] 對重複查詢使用預備陳述式

效能測試

[ ] 使用真實資料量測試查詢
[ ] 分析查詢執行計畫
[ ] 持續監控查詢效能
[ ] 設定慢查詢警示
[ ] 定期分析索引使用情況

📝 最佳化方法論

識別：使用資料庫專用工具找出慢查詢
分析：檢查執行計畫並找出瓶頸
最佳化：套用適當的最佳化技巧
測試：驗證效能改善
監控：持續追蹤效能指標
迭代：定期進行效能檢視與最佳化

專注於可衡量的效能改善，並務必使用真實的資料量與查詢模式來測試最佳化效果。

您可能也喜歡

golang-database

2.3Kdatabase

Go 資料庫存取的完整指南 — 參數化查詢、結構掃描、可為 NULL 的欄位、交易、隔離層級、SELECT FOR UPDATE、連線池、批次處理、Context 傳遞以及遷移工具。適用於撰寫、審查或除錯與 PostgreSQL、MariaDB、MySQL 或 SQLite 互動的 Golang 程式碼；用於資料庫測試；或關於 database/sql、sqlx 或 pgx 的問題。不產生資料庫綱要或遷移 SQL。