【技术精讲】30个SQL调优技巧及高级SQL技巧详解

大家好，我是 V 哥。SQL调优对于提升数据库查询性能至关重要，特别是当数据量大时。以下是20个详细的SQL调优指南和高级技巧，结合案例说明，帮助优化SQL查询的性能。

1. 选择合适的索引

• 技巧: 对查询频繁使用的列创建合适的索引（单列索引、组合索引等）。
• 案例:
  • 问题SQL: SELECT name FROM employees WHERE department_id = 10;
  • 优化: 为department_id创建索引：

CREATE INDEX idx_department_id ON employees(department_id);

2. 避免使用SELECT

• 技巧: 只查询所需的列，减少返回的数据量。
• 案例:
  • 问题SQL: SELECT * FROM employees WHERE department_id = 10;
  • 优化: 只查询需要的列：

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');

• 优化: 使用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查询的执行计划，找到性能瓶颈。
• 案例:

EXPLAIN SELECT name FROM employees WHERE department_id = 10;

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

• 技巧: ORDER BY 会消耗大量资源，尤其是大数据量时，只有在需要排序时才使用。
• 案例:
  • 问题SQL: SELECT name FROM employees WHERE department_id = 10 ORDER BY hire_date;
  • 优化: 如果不需要排序，去掉ORDER BY。

6. 优化LIMIT分页查询

• 技巧: 分页时使用LIMIT，对于大偏移量的查询，可以通过索引或缓存减少开销。
• 案例:
  • 问题SQL: SELECT name FROM employees ORDER BY hire_date LIMIT 100000, 10;
  • 优化: 使用主键或索引来提高分页性能：

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

7. 避免在WHERE条件中使用函数

• 技巧: 函数调用会阻止索引的使用，应尽量避免。
• 案例:
  • 问题SQL: SELECT name FROM employees WHERE YEAR(hire_date) = 2023;
  • 优化: 改为范围查询：

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

8. 合理选择联合索引的顺序

• 技巧: 在组合索引中，把选择性高的列放在索引的前面。
• 案例:
  • 假设查询为：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);

• 优化: 使用批量插入：

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);

• 优化: 使用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

• 技巧: 只有在真正有重复数据时才使用DISTINCT。
• 案例:
  • 问题SQL:

SELECT DISTINCT name FROM employees WHERE department_id = 10;

• 优化: 如果不需要去重，移除DISTINCT。

12. 使用适当的表连接类型

• 技巧: 尽量使用INNER JOIN，除非明确需要所有数据，避免使用LEFT JOIN或RIGHT JOIN。
• 案例:
  • 问题SQL:

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

• 优化: 改为INNER JOIN：

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

13. 使用表分区

• 技巧: 对大表使用分区可以提高查询性能。
• 案例:

     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. 优化GROUP BY查询

• 技巧: 通过索引优化GROUP BY查询。
• 案例:

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

15. 优化IN的使用

• 技巧: 对大量IN操作，可以将数据放到临时表中，使用JOIN代替。
• 案例:
  • 问题SQL:

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

• 优化: 将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。
• 案例: 复杂视图查询可以通过优化SQL语句代替。

17. 锁的优化

• 技巧: 使用适当的锁机制，避免全表锁（如LOCK IN SHARE MODE）。
• 案例:

SELECT * FROM employees WHERE id = 10 FOR UPDATE;

18. 优化INSERT INTO SELECT语句

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

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

19. 使用连接池

• 技巧: 对频繁的数据库操作，使用连接池可以提高效率。
• 案例: 在应用层面配置连接池。

20. 监控和调整内存参数

• 技巧: 调整数据库服务器的内存设置（如MySQL的innodb_buffer_pool_size）来匹配查询需求。
• 案例: 根据查询的内存需求进行调整配置。

这些技巧可以帮助大多数SQL查询在不同场景下提高性能，但每种数据库和业务场景都有其特定的优化需求，因此调优时应根据实际情况灵活应用。

以下是 更复杂的情况 SQL 优化技巧

高级SQL优化技巧通常涉及到复杂的数据库结构、查询计划的深入理解、并发控制和事务处理等领域。以下是更复杂的SQL优化技巧和相关案例，适用于大型数据库和复杂查询场景。

21. 分布式查询优化

• 技巧: 在分布式数据库环境中，尽量减少跨节点的数据传输，优化查询计划以提高查询效率。
• 案例:
  • 问题SQL: 查询在多个分区节点上操作

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

• 优化: 将与location相关的数据先在本地节点处理，再进行全局数据汇总，避免跨节点传输。

22. 多列索引与索引合并

• 技巧: 在多列上进行查询时，如果无法为每个查询场景创建组合索引，数据库会尝试通过索引合并来提高性能。
• 案例:
  • 问题SQL: 使用多个单列索引

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

• 优化: 数据库可以将customer_id和product_id的单列索引合并执行。结合EXPLAIN分析，数据库是否使用了索引合并功能。

23. CUBE和ROLLUP优化多维分析查询

• 技巧: 使用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;

• 优化: 使用ROLLUP进行多层次的聚合分析，减少多次查询

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

24. 基于窗口函数的复杂分析查询

• 技巧: 使用窗口函数（如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;

• 优化: 使用窗口函数替代自连接

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

25. 分区表与分区裁剪（Partition Pruning）

• 技巧: 对非常大的表使用分区裁剪技术，减少数据扫描范围。

• 案例:

• 问题SQL: 查询无分区的大表

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

• 优化: 将表按日期分区，并利用分区裁剪来提升性能

     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;

• 优化: 直接使用子查询或CTE（公共表表达式）减少临时表依赖

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

27. 并行查询优化（Parallel Query）

• 技巧: 充分利用数据库的并行查询能力，尤其在数据量巨大的查询时，开启并行执行计划提高效率。

• 案例:

• 问题SQL: 没有并行查询计划的大数据扫描

     SELECT SUM(sales) FROM sales_data;

• 优化: 开启并行查询

     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;

• 优化: 创建物化视图存储预计算结果

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

29. 避免锁争用，优化并发查询

• 技巧: 在高并发环境下，避免使用全表锁或行锁，可以通过索引锁和锁定必要行的方式减少锁争用。

• 案例:

• 问题SQL: 全表锁，导致高并发下性能下降

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

• 优化: 改为锁定特定行，减少锁定范围

     SELECT * FROM orders WHERE order_id = 123 FOR UPDATE;

30. 优化事务处理，减少锁定时间

• 技巧: 对于长时间运行的事务，应尽量减少锁定时间，避免不必要的锁持有，减少表锁定范围。

• 案例:

• 问题SQL: 大量数据操作，锁住整个事务期间的表

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

• 优化: 将长事务拆分为多个小事务，或者减少锁的持有时间

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

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

这些高级技巧需要结合具体的数据库环境（如MySQL、PostgreSQL、Oracle等）进行细化和测试，同时也需要对数据库的执行计划和锁定机制有深入的理解。关注威哥爱编程，跟技术死磕到底。