当前位置： 首页 > news >正文

【数据库】SQL设计指南：如何编写性能优异的SQL

news 2025/3/19 15:03:40

引言

SQL是数据库操作的核心语言，编写高效的SQL语句对系统性能至关重要。本文通过30个场景案例，展示如何优化SQL语句，提升查询效率。每个案例均包含优化前、优化后的SQL代码，并详细说明优化前后的性能差异。

案例1：避免使用SELECT *

场景：查询用户表中所有字段。
SQL代码：

-- 优化前  
SELECT * FROM users;  -- 优化后  
SELECT id, name, email FROM users;

说明：SELECT *会查询所有字段，包括不需要的字段，增加数据传输和处理的开销。优化后仅查询所需字段，减少资源消耗。

案例2：使用LIMIT限制结果集

场景：查询前10条订单记录。
SQL代码：

-- 优化前  
SELECT * FROM orders;  -- 优化后  
SELECT * FROM orders LIMIT 10;

说明：未使用LIMIT会查询所有记录，导致性能下降。优化后仅查询前10条，显著减少数据量。

案例3：使用索引加速查询

场景：根据用户ID查询用户信息。
SQL代码：

-- 优化前  
SELECT * FROM users WHERE id = 100;  -- 优化后  
CREATE INDEX idx_user_id ON users(id);  
SELECT * FROM users WHERE id = 100;

说明：未使用索引时，数据库需要全表扫描。创建索引后，查询速度大幅提升。

案例4：避免在WHERE子句中使用函数

场景：查询2025年3月17日的订单。
SQL代码：

-- 优化前  
SELECT * FROM orders WHERE DATE(order_date) = '2025-03-17';  -- 优化后  
SELECT * FROM orders WHERE order_date BETWEEN '2025-03-17 00:00:00' AND '2025-03-17 23:59:59';

说明：在WHERE子句中使用函数会导致索引失效。优化后使用范围查询，提升性能。

案例5：使用JOIN代替子查询

场景：查询有订单的用户信息。
SQL代码：

-- 优化前  
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);  -- 优化后  
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;

说明：子查询效率较低，使用JOIN可以显著提升查询速度。

案例6：避免使用OR条件

场景：查询状态为“已支付”或“已完成”的订单。
SQL代码：

-- 优化前  
SELECT * FROM orders WHERE status = 'paid' OR status = 'completed';  -- 优化后  
SELECT * FROM orders WHERE status IN ('paid', 'completed');

说明：OR条件可能导致索引失效，使用IN可以优化查询性能。

案例7：使用EXISTS代替COUNT

场景：查询有订单的用户。
SQL代码：

-- 优化前  
SELECT * FROM users WHERE (SELECT COUNT(*) FROM orders WHERE user_id = users.id) > 0;  -- 优化后  
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

说明：COUNT需要扫描所有记录，EXISTS在找到第一条匹配记录后即返回，效率更高。

案例8：使用UNION ALL代替UNION

场景：合并两个查询结果。
SQL代码：

-- 优化前  
SELECT * FROM orders WHERE status = 'paid' UNION SELECT * FROM orders WHERE status = 'completed';  -- 优化后  
SELECT * FROM orders WHERE status = 'paid' UNION ALL SELECT * FROM orders WHERE status = 'completed';

说明：UNION会去重，增加开销。UNION ALL不去重，效率更高。

案例9：避免使用NOT IN

场景：查询没有订单的用户。
SQL代码：

-- 优化前  
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);  -- 优化后  
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

说明：NOT IN效率较低，NOT EXISTS更高效。

案例10：使用分页查询

场景：查询第11到20条订单记录。
SQL代码：

-- 优化前  
SELECT * FROM orders;  -- 优化后  
SELECT * FROM orders LIMIT 10 OFFSET 10;

说明：未分页会查询所有记录，分页后仅查询所需数据，提升性能。

案例11：使用GROUP BY优化聚合查询

场景：统计每个用户的订单数量。
SQL代码：

-- 优化前  
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;  -- 优化后  
CREATE INDEX idx_user_id ON orders(user_id);  
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

说明：未使用索引时，GROUP BY效率较低。创建索引后，性能显著提升。

案例12：避免使用HAVING过滤

场景：查询订单数量大于10的用户。
SQL代码：

-- 优化前  
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 10;  -- 优化后  
SELECT user_id, COUNT(*) FROM orders WHERE status = 'paid' GROUP BY user_id HAVING COUNT(*) > 10;

说明：HAVING在聚合后过滤，效率较低。优化后先过滤再聚合，提升性能。

案例13：使用批量插入

场景：插入多条用户记录。
SQL代码：

-- 优化前  
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');  
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');  -- 优化后  
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');

说明：多次插入效率较低，批量插入减少数据库交互次数，提升性能。

案例14：使用预编译语句

场景：多次查询用户信息。
SQL代码：

-- 优化前  
SELECT * FROM users WHERE id = 1;  
SELECT * FROM users WHERE id = 2;  -- 优化后  
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';  
EXECUTE stmt USING 1;  
EXECUTE stmt USING 2;

说明：预编译语句减少SQL解析开销，提升效率。

案例15：使用覆盖索引

场景：查询用户ID和姓名。
SQL代码：

-- 优化前  
SELECT id, name FROM users;  -- 优化后  
CREATE INDEX idx_user_id_name ON users(id, name);  
SELECT id, name FROM users;

说明：覆盖索引直接从索引中获取数据，避免访问表数据，提升性能。

案例16：避免使用临时表

场景：查询复杂结果集。
SQL代码：

-- 优化前  
CREATE TEMPORARY TABLE temp_users AS SELECT * FROM users WHERE status = 'active';  
SELECT * FROM temp_users;  -- 优化后  
SELECT * FROM users WHERE status = 'active';

说明：临时表增加额外开销，直接查询更高效。

案例17：使用分区表

场景：查询2025年的订单。
SQL代码：

-- 优化前  
SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';  -- 优化后  
CREATE TABLE orders_partitioned (  id INT,  order_date DATE  
) PARTITION BY RANGE (YEAR(order_date)) (  PARTITION p2025 VALUES LESS THAN (2026)  
);  
SELECT * FROM orders_partitioned WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

说明：分区表减少扫描范围，提升查询效率。

案例18：使用缓存

场景：频繁查询用户信息。
SQL代码：

-- 优化前  
SELECT * FROM users WHERE id = 1;  -- 优化后  
-- 使用缓存机制（如Redis）存储查询结果

说明：缓存减少数据库访问次数，显著提升性能。

案例19：使用并行查询
场景：处理大数据量查询。
SQL代码：

-- 优化前  
SELECT * FROM orders;  -- 优化后  
-- 启用并行查询（如MySQL的并行查询功能）

说明：并行查询利用多核CPU，提升处理速度。

案例20：优化数据库配置

场景：提升整体查询性能。
SQL代码：

-- 优化前  
-- 默认配置  -- 优化后  
-- 调整配置参数（如缓冲区大小、连接数等）

说明：优化数据库配置可以显著提升整体性能。

案例21：避免使用DISTINCT

场景：查询唯一的用户邮箱。
SQL代码：

-- 优化前  
SELECT DISTINCT email FROM users;  -- 优化后  
SELECT email FROM users GROUP BY email;

说明：DISTINCT会去重，增加开销。使用GROUP BY可以优化查询性能。

案例22：使用BETWEEN代替多个OR条件

场景：查询2025年3月1日至2025年3月31日的订单。
SQL代码：

-- 优化前  
SELECT * FROM orders WHERE order_date >= '2025-03-01' OR order_date <= '2025-03-31';  -- 优化后  
SELECT * FROM orders WHERE order_date BETWEEN '2025-03-01' AND '2025-03-31';

说明：OR条件可能导致索引失效，BETWEEN更高效。

案例23：使用子查询优化复杂查询

场景：查询订单金额大于平均金额的订单。
SQL代码：

-- 优化前  
SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);  -- 优化后  
WITH avg_amount AS (SELECT AVG(amount) AS avg FROM orders)  
SELECT * FROM orders, avg_amount WHERE orders.amount > avg_amount.avg;

说明：使用WITH子句减少子查询的执行次数，提升性能。

案例24：使用索引优化LIKE查询

场景：查询邮箱以“alice”开头的用户。
SQL代码：

-- 优化前  
SELECT * FROM users WHERE email LIKE 'alice%';  -- 优化后  
CREATE INDEX idx_email ON users(email);  
SELECT * FROM users WHERE email LIKE 'alice%';

说明：未使用索引时，LIKE查询效率较低。创建索引后，性能显著提升。

案例25：避免使用ORDER BY NULL

场景：查询用户信息，无需排序。
SQL代码：

-- 优化前  
SELECT * FROM users ORDER BY NULL;  -- 优化后  
SELECT * FROM users;

说明：ORDER BY NULL增加额外开销，直接查询更高效。

案例26：使用EXPLAIN分析查询计划

场景：分析查询性能。
SQL代码：

-- 优化前  
SELECT * FROM orders WHERE status = 'paid';  -- 优化后  
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

说明：使用EXPLAIN分析查询计划，找出性能瓶颈，优化查询。

案例27：使用批量更新

场景：更新多个用户的邮箱。
SQL代码：

-- 优化前  
UPDATE users SET email = 'new@example.com' WHERE id = 1;  
UPDATE users SET email = 'new@example.com' WHERE id = 2;  -- 优化后  
UPDATE users SET email = 'new@example.com' WHERE id IN (1, 2);

说明：多次更新效率较低，批量更新减少数据库交互次数，提升性能。

案例28：使用CASE WHEN优化复杂逻辑

场景：根据用户状态设置标签。
SQL代码：

-- 优化前  
SELECT status,  IF(status = 'active', 'Active User', 'Inactive User') AS label  
FROM users;  -- 优化后  
SELECT status,  CASE WHEN status = 'active' THEN 'Active User'  ELSE 'Inactive User' END AS label  
FROM users;

说明：CASE WHEN比IF更灵活，性能更好。

案例29：使用事务保证数据一致性

场景：转账操作。
SQL代码：

-- 优化前  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 优化后  
START TRANSACTION;  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  
COMMIT;

说明：使用事务保证数据一致性，避免数据错误。

案例30：使用存储过程优化复杂操作

场景：执行复杂的业务逻辑。
SQL代码：

-- 优化前  
-- 多次SQL语句  -- 优化后  
CREATE PROCEDURE complex_operation()  
BEGIN  -- 复杂逻辑  
END;  
CALL complex_operation();

说明：存储过程减少数据库交互次数，提升性能。

总结
通过以上30个案例，我们可以看到，编写高效的SQL语句需要从多个角度入手，包括索引、查询结构、数据库配置等。合理运用这些优化技巧，可以显著提升数据库性能，为系统提供更好的支持。


http://www.mrgr.cn/news/94993.html

相关文章：

  • el-table的行向上移动向下移动，删除选定行
  • Diffie-Hellman 加密协议介绍 （DH，DHE，ECDHE）
  • docker(1) -- centos镜像
  • CAN及CANFD协议
  • 使用 Promise 和 .then() 解决同异步问题
  • Optiplex 3060 MT 电脑型号与尺寸
  • Qwen2.5-VL 开源视觉大模型，模型体验、下载、推理、微调、部署实战
  • C++基础: Rule of five/zero/three
  • 【数据结构】顺序表(附源码)
  • 《大语言模型》学习笔记（三）
  • 生成PDF文件：从html2canvas和jsPdf渲染到Puppeteer矢量图
  • SSL/TLS 和 SSH 区别
  • 2025最新！人工智能领域大模型学习路径、大模型使用、AI工作流学习路径
  • SpringCloud 学习笔记3（OpenFeign）
  • 【Netty】消息分发处理方式
  • 【数据库】掌握MySQL事务与锁机制-数据一致性的关键
  • MySQL ：参数修改
  • 鸿蒙NEXT开发问题大全（不断更新中.....）
  • C++继承 ---- 继承是面向对象三大特性之一【好处：可以减少重复的代码】
  • Python教学:lambda表达式的应用-由DeepSeek产生