【技術精講】30個SQL調(diào)優(yōu)技巧及高級SQL技巧詳解

2024-12-17 14:22 更新

大家好，我是 V 哥。SQL調(diào)優(yōu)對于提升數(shù)據(jù)庫查詢性能至關重要，特別是當數(shù)據(jù)量大時。以下是20個詳細的SQL調(diào)優(yōu)指南和高級技巧，結合案例說明，幫助優(yōu)化SQL查詢的性能。

1. 選擇合適的索引

  • 技巧: 對查詢頻繁使用的列創(chuàng)建合適的索引（單列索引、組合索引等）。
  • 案例:
    • 問題SQL: SELECT name FROM employees WHERE department_id = 10;
    • 優(yōu)化: 為department_id創(chuàng)建索引：

CREATE INDEX idx_department_id ON employees(department_id);

2. 避免使用SELECT

  • 技巧: 只查詢所需的列，減少返回的數(shù)據(jù)量。
  • 案例:
    • 問題SQL: SELECT * FROM employees WHERE department_id = 10;
    • 優(yōu)化: 只查詢需要的列：

SELECT name FROM employees WHERE department_id = 10;

3. 盡量使用JOIN代替子查詢

  • 技巧: 子查詢通常效率低，JOIN性能更好。
  • 案例:
    • 問題SQL: 

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

  • 優(yōu)化: 使用JOIN代替子查詢：

SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.location = 'New York';

4. 使用EXPLAIN分析查詢

  • 技巧: 使用EXPLAIN或EXPLAIN ANALYZE來查看SQL查詢的執(zhí)行計劃，找到性能瓶頸。
  • 案例:

EXPLAIN SELECT name FROM employees WHERE department_id = 10;

5. 避免不必要的ORDER BY操作

  • 技巧: ORDER BY 會消耗大量資源，尤其是大數(shù)據(jù)量時，只有在需要排序時才使用。
  • 案例:
    • 問題SQL: SELECT name FROM employees WHERE department_id = 10 ORDER BY hire_date;
    • 優(yōu)化: 如果不需要排序，去掉ORDER BY。

6. 優(yōu)化LIMIT分頁查詢

  • 技巧: 分頁時使用LIMIT，對于大偏移量的查詢，可以通過索引或緩存減少開銷。
  • 案例:
    • 問題SQL: SELECT name FROM employees ORDER BY hire_date LIMIT 100000, 10;
    • 優(yōu)化: 使用主鍵或索引來提高分頁性能：

SELECT name FROM employees WHERE id > 100000 ORDER BY hire_date LIMIT 10;

7. 避免在WHERE條件中使用函數(shù)

  • 技巧: 函數(shù)調(diào)用會阻止索引的使用，應盡量避免。
  • 案例:
    • 問題SQL: SELECT name FROM employees WHERE YEAR(hire_date) = 2023;
    • 優(yōu)化: 改為范圍查詢：

SELECT name FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

8. 合理選擇聯(lián)合索引的順序

  • 技巧: 在組合索引中，把選擇性高的列放在索引的前面。
  • 案例:
    • 假設查詢?yōu)椋?code>SELECT * FROM employees WHERE department_id = 10 AND status = 'active';
    • 通過選擇性分析，可以將status放在索引前面：

CREATE INDEX idx_status_department ON employees(status, department_id);

9. 使用批量插入替代逐條插入

  • 技巧: 批量插入可以顯著減少IO和鎖的開銷。
  • 案例:
    • 問題SQL: 每次插入一條記錄：

INSERT INTO employees (name, department_id) VALUES ('John', 10);

  • 優(yōu)化: 使用批量插入：

INSERT INTO employees (name, department_id) VALUES ('John', 10), ('Alice', 20), ('Bob', 30);

10. 避免使用NOT IN

  • 技巧: NOT IN性能較差，改用NOT EXISTS或LEFT JOIN。
  • 案例:
    • 問題SQL: 

SELECT name FROM employees WHERE department_id NOT IN (SELECT id FROM departments);

  • 優(yōu)化: 使用LEFT JOIN：

SELECT e.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id WHERE d.id IS NULL;

11. 避免冗余的DISTINCT

  • 技巧: 只有在真正有重復數(shù)據(jù)時才使用DISTINCT。
  • 案例:
    • 問題SQL: 

SELECT DISTINCT name FROM employees WHERE department_id = 10;

  • 優(yōu)化: 如果不需要去重，移除DISTINCT。

12. 使用適當?shù)谋磉B接類型

  • 技巧: 盡量使用INNER JOIN，除非明確需要所有數(shù)據(jù)，避免使用LEFT JOIN或RIGHT JOIN。
  • 案例:
    • 問題SQL: 

SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

  • 優(yōu)化: 改為INNER JOIN：

SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

13. 使用表分區(qū)

  • 技巧: 對大表使用分區(qū)可以提高查詢性能。
  • 案例: 

     CREATE TABLE employees (
         id INT,
         name VARCHAR(50),
         hire_date DATE
     )
     PARTITION BY RANGE (YEAR(hire_date)) (
         PARTITION p2020 VALUES LESS THAN (2021),
         PARTITION p2021 VALUES LESS THAN (2022)
     );

14. 優(yōu)化GROUP BY查詢

  • 技巧: 通過索引優(yōu)化GROUP BY查詢。
  • 案例:

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

15. 優(yōu)化IN的使用

  • 技巧: 對大量IN操作，可以將數(shù)據(jù)放到臨時表中，使用JOIN代替。
  • 案例:
    • 問題SQL: 

SELECT name FROM employees WHERE department_id IN (1, 2, 3, 4, 5);

  • 優(yōu)化: 將ID放入臨時表：

CREATE TEMPORARY TABLE temp_ids (id INT);
INSERT INTO temp_ids (id) VALUES (1), (2), (3), (4), (5);
SELECT e.name FROM employees e JOIN temp_ids t ON e.department_id = t.id;

16. 限制使用復雜的視圖

  • 技巧: 視圖會增加查詢的復雜性和性能開銷，復雜查詢可以直接寫SQL。
  • 案例: 復雜視圖查詢可以通過優(yōu)化SQL語句代替。

17. 鎖的優(yōu)化

  • 技巧: 使用適當?shù)逆i機制，避免全表鎖（如LOCK IN SHARE MODE）。
  • 案例: 

SELECT * FROM employees WHERE id = 10 FOR UPDATE;

18. 優(yōu)化INSERT INTO SELECT語句

  • 技巧: INSERT INTO SELECT語句中使用索引，提高性能。
  • 案例:

INSERT INTO employees_backup (id, name) SELECT id, name FROM employees WHERE hire_date < '2020-01-01';

19. 使用連接池

  • 技巧: 對頻繁的數(shù)據(jù)庫操作，使用連接池可以提高效率。
  • 案例: 在應用層面配置連接池。

20. 監(jiān)控和調(diào)整內(nèi)存參數(shù)

  • 技巧: 調(diào)整數(shù)據(jù)庫服務器的內(nèi)存設置（如MySQL的innodb_buffer_pool_size）來匹配查詢需求。
  • 案例: 根據(jù)查詢的內(nèi)存需求進行調(diào)整配置。

這些技巧可以幫助大多數(shù)SQL查詢在不同場景下提高性能，但每種數(shù)據(jù)庫和業(yè)務場景都有其特定的優(yōu)化需求，因此調(diào)優(yōu)時應根據(jù)實際情況靈活應用。

以下是 更復雜的情況 SQL 優(yōu)化技巧

高級SQL優(yōu)化技巧通常涉及到復雜的數(shù)據(jù)庫結構、查詢計劃的深入理解、并發(fā)控制和事務處理等領域。以下是更復雜的SQL優(yōu)化技巧和相關案例，適用于大型數(shù)據(jù)庫和復雜查詢場景。

21. 分布式查詢優(yōu)化

  • 技巧: 在分布式數(shù)據(jù)庫環(huán)境中，盡量減少跨節(jié)點的數(shù)據(jù)傳輸，優(yōu)化查詢計劃以提高查詢效率。
  • 案例:
    • 問題SQL: 查詢在多個分區(qū)節(jié)點上操作

SELECT e.name, d.name 
     FROM employees e JOIN departments d 
     ON e.department_id = d.id
     WHERE e.location = 'New York';

  • 優(yōu)化: 將與location相關的數(shù)據(jù)先在本地節(jié)點處理，再進行全局數(shù)據(jù)匯總，避免跨節(jié)點傳輸。

22. 多列索引與索引合并

  • 技巧: 在多列上進行查詢時，如果無法為每個查詢場景創(chuàng)建組合索引，數(shù)據(jù)庫會嘗試通過索引合并來提高性能。
  • 案例:
    • 問題SQL: 使用多個單列索引

SELECT * FROM orders WHERE customer_id = 10 AND product_id = 5;

  • 優(yōu)化: 數(shù)據(jù)庫可以將customer_id和product_id的單列索引合并執(zhí)行。結合EXPLAIN分析，數(shù)據(jù)庫是否使用了索引合并功能。

23. CUBE和ROLLUP優(yōu)化多維分析查詢

  • 技巧: 使用CUBE和ROLLUP進行多維聚合分析，減少多次單獨的GROUP BY操作。

  • 案例:

  • 問題SQL: 分別進行多次GROUP BY分析

     SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;
     SELECT region, SUM(sales) FROM sales_data GROUP BY region;

  • 優(yōu)化: 使用ROLLUP進行多層次的聚合分析，減少多次查詢

     SELECT department_id, region, SUM(sales) FROM sales_data 
     GROUP BY department_id, region WITH ROLLUP;

24. 基于窗口函數(shù)的復雜分析查詢

  • 技巧: 使用窗口函數(shù)（如ROW_NUMBER()、RANK()、LAG()、LEAD()）進行復雜分析，避免自連接或嵌套查詢。

  • 案例:

  • 問題SQL: 通過自連接獲取上一條記錄

     SELECT a.*, (SELECT sales FROM sales_data b WHERE b.id = a.id - 1) AS previous_sales 
     FROM sales_data a;

  • 優(yōu)化: 使用窗口函數(shù)替代自連接

     SELECT id, sales, LAG(sales, 1) OVER (ORDER BY id) AS previous_sales 
     FROM sales_data;

25. 分區(qū)表與分區(qū)裁剪（Partition Pruning）

  • 技巧: 對非常大的表使用分區(qū)裁剪技術，減少數(shù)據(jù)掃描范圍。

  • 案例:

  • 問題SQL: 查詢無分區(qū)的大表

     SELECT * FROM transactions WHERE transaction_date BETWEEN '2023-01-01' AND '2023-01-31';

  • 優(yōu)化: 將表按日期分區(qū)，并利用分區(qū)裁剪來提升性能

     CREATE TABLE transactions (
         id INT,
         amount DECIMAL(10, 2),
         transaction_date DATE
     )
     PARTITION BY RANGE (YEAR(transaction_date)) (
         PARTITION p2023 VALUES LESS THAN (2024)
     );

26. 避免或最小化臨時表的使用

  • 技巧: 盡量減少復雜查詢中的臨時表使用，因為它們會導致磁盤I/O，影響性能。

  • 案例:

  • 問題SQL: 使用臨時表存儲中間結果

     CREATE TEMPORARY TABLE temp_sales AS
     SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;

  • 優(yōu)化: 直接使用子查詢或CTE（公共表表達式）減少臨時表依賴

     WITH temp_sales AS (
         SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id
     )
     SELECT * FROM temp_sales;

27. 并行查詢優(yōu)化（Parallel Query）

  • 技巧: 充分利用數(shù)據(jù)庫的并行查詢能力，尤其在數(shù)據(jù)量巨大的查詢時，開啟并行執(zhí)行計劃提高效率。

  • 案例:

  • 問題SQL: 沒有并行查詢計劃的大數(shù)據(jù)掃描

     SELECT SUM(sales) FROM sales_data;

  • 優(yōu)化: 開啟并行查詢

     ALTER SESSION ENABLE PARALLEL QUERY;
     SELECT /*+ PARALLEL(sales_data, 4) */ SUM(sales) FROM sales_data;

28. 使用Materialized Views加速復雜查詢

  • 技巧: 對復雜的聚合查詢，可以使用物化視圖（Materialized View）將計算結果存儲起來，避免每次查詢重新計算。

  • 案例:

  • 問題SQL: 復雜聚合查詢，性能瓶頸明顯

     SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;

  • 優(yōu)化: 創(chuàng)建物化視圖存儲預計算結果

     CREATE MATERIALIZED VIEW mv_sales_data AS
     SELECT department_id, SUM(sales) AS total_sales FROM sales_data GROUP BY department_id;

29. 避免鎖爭用，優(yōu)化并發(fā)查詢

  • 技巧: 在高并發(fā)環(huán)境下，避免使用全表鎖或行鎖，可以通過索引鎖和鎖定必要行的方式減少鎖爭用。

  • 案例:

  • 問題SQL: 全表鎖，導致高并發(fā)下性能下降

     SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

  • 優(yōu)化: 改為鎖定特定行，減少鎖定范圍

     SELECT * FROM orders WHERE order_id = 123 FOR UPDATE;

30. 優(yōu)化事務處理，減少鎖定時間

  • 技巧: 對于長時間運行的事務，應盡量減少鎖定時間，避免不必要的鎖持有，減少表鎖定范圍。

  • 案例:

  • 問題SQL: 大量數(shù)據(jù)操作，鎖住整個事務期間的表

     BEGIN;
     UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
     UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
     COMMIT;

  • 優(yōu)化: 將長事務拆分為多個小事務，或者減少鎖的持有時間

     BEGIN;
     UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
     COMMIT;

     
     BEGIN;
     UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
     COMMIT;

這些高級技巧需要結合具體的數(shù)據(jù)庫環(huán)境（如MySQL、PostgreSQL、Oracle等）進行細化和測試，同時也需要對數(shù)據(jù)庫的執(zhí)行計劃和鎖定機制有深入的理解。關注威哥愛編程，跟技術死磕到底。

以上內(nèi)容是否對您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號
微信公眾號

編程獅公眾號