在編寫和優化SQL語句時，有一些通用的技巧和最佳實踐可以幫助提高查詢性能。以下是10條SQL優化語句的示例和說明：

1. 使用索引

CREATE INDEX idx_users_email ON users(email);

為經常在查詢中使用的列創建索引。

2. 選擇必要的列

SELECT id, name, email FROM users WHERE status = 'active';

避免使用 SELECT *，只選擇需要的列。

3. 避免函數操作列

SELECT * FROM users WHERE created_at >= '2023-01-01';

避免在 WHERE 子句中對列使用函數操作，如 DATE(created_at)，因為這樣會使索引失效。

4. 使用聯合查詢代替子查詢

SELECT orders.id, orders.total, users.name

FROM orders

JOIN users ON orders.user_id = users.id

WHERE orders.total > 100;

使用 JOIN 查詢代替子查詢，提高性能。

5. 避免冗余和重復的表掃描

SELECT id, name FROM users WHERE email = 'example@example.com' LIMIT 1;

使用 LIMIT 限制返回的行數，避免全表掃描。

6. 使用 EXISTS 代替 IN

SELECT name FROM users WHERE EXISTS (

    SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.total > 100

);

使用 EXISTS 替代 IN 來檢查子查詢的存在，通常性能更好。

7. 批量更新或插入

INSERT INTO orders (user_id, product_id, quantity) VALUES 

(1, 1, 2), 

(2, 3, 1), 

(3, 2, 4);

使用批量插入或更新，減少數據庫交互次數。

8. 使用覆蓋索引

CREATE INDEX idx_users_status ON users(status, id, name);

SELECT id, name FROM users WHERE status = 'active';

創建覆蓋索引，包含查詢中涉及的所有列。

9. 避免選擇不必要的大數據集

SELECT id, name FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31';

在查詢中使用合理的條件限制數據集的大小。

10. 優化排序

CREATE INDEX idx_users_created_at ON users(created_at);

SELECT id, name FROM users WHERE status = 'active' ORDER BY created_at DESC;

在排序列上創建索引，優化 ORDER BY 子句的性能。

11. 使用連接條件

SELECT orders.id, users.name 

FROM orders 

JOIN users ON orders.user_id = users.id 

WHERE users.status = 'active';

在 JOIN 語句中使用明確的連接條件，避免產生笛卡爾積。

12. 使用聚合函數時避免重復計算

SELECT department, AVG(salary) AS avg_salary 

FROM employees 

GROUP BY department;

使用 GROUP BY 與聚合函數（如 AVG, SUM, COUNT）相結合，避免在應用層進行重復計算。

13. 使用適當的數據類型

ALTER TABLE users MODIFY age SMALLINT;

使用合適的數據類型，減少存儲空間和內存使用，提高查詢性能。

14. 優化分頁查詢

SELECT id, name FROM users ORDER BY created_at LIMIT 10 OFFSET 1000;

在分頁查詢中使用 LIMIT 和 OFFSET，盡量減少處理的數據量。

15. 避免不必要的排序

SELECT id, name FROM users WHERE status = 'active' ORDER BY created_at;

確保排序僅在必要時使用，并在排序列上創建索引。

16. 使用預處理語句

PREPARE stmt FROM 'SELECT id, name FROM users WHERE email = ?';

SET @email = 'example@example.com';

EXECUTE stmt USING @email;

使用預處理語句（Prepared Statements），提高查詢執行效率并增強安全性。

17. 合理使用事務

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT;

合理使用事務，確保數據一致性的同時，減少鎖的持有時間。

18. 使用分析工具

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

使用 EXPLAIN 語句分析查詢計劃，了解查詢執行的詳細信息，并根據結果進行優化。

19. 避免使用復雜的正則表達式

SELECT * FROM users WHERE email LIKE '%@example.com';

避免在查詢中使用復雜的正則表達式，改用簡單的字符串匹配。

20. 使用合適的存儲引擎

CREATE TABLE orders (

    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT,

    total DECIMAL(10, 2),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB;

選擇合適的存儲引擎，如InnoDB（支持事務、外鍵等）或MyISAM（適合讀多寫少的場景）。

通過這些優化技巧，可以顯著提升SQL查詢和數據庫的整體性能。但實際應用中，需要根據具體的業務場景和數據庫配置進行測試和調整，確保獲得最佳的性能。

來源：https://mp.weixin.qq.com/s/RBaRYS4gHfheqW7xgJi9tA