全部标签
写点什么
OpenHarmony算法元宇宙MySQL移动开发学习方法学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

30 个 sql 调优及高级 sql 技巧

作者：威哥爱编程
  • 2024-10-09
    北京

  • 本文字数：4653 字

    阅读完需：约 15 分钟

大家好，我是 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 等）进行细化和测试，同时也需要对数据库的执行计划和锁定机制有深入的理解。关注威哥爱编程，码码通畅不掉发。

划线
评论
复制
发布于: 刚刚阅读数: 4

版权声明: 本文为 InfoQ 作者【威哥爱编程】的原创文章。

原文链接:【http://xie.infoq.cn/article/f338ee6f7f2f200a821ab1348】。

本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。

MySQLoracle#SQL
用户头像

威哥爱编程

关注

华为 HDE、CSDN 博客专家、Java畅销书作者 2018-05-30 加入

全栈领域优质创作者（Java/HarmonyOS/AI），公众号：威哥爱编程

评论

发布
暂无评论
Copyright © 2024, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
30个sql调优及高级sql技巧_MySQL_威哥爱编程_InfoQ写作社区