10个SQL优化技巧，性能提升300%-技术教程-四时宝库

环境：SpringBoot3.4.2

1. 简介

SQL作为数据库交互的核心语言，其查询效率直接决定着应用的响应速度与资源消耗。低效的SQL语句（如全表扫描、无索引查询、复杂嵌套子句等）可能让数据库陷入"CPU狂飙、内存耗尽"的困境，导致系统响应延迟数秒甚至宕机，尤其在处理千万级数据时，性能瓶颈更会指数级放大。而一次精准的SQL优化，往往能让查询耗时从分钟级压缩至毫秒级，显著降低服务器负载，提升用户体验。

本文将通过10个SQL优化实战技巧，助你轻松应对慢查询性能顽疾，让数据库成为应用的"性能引擎"而非"拖油瓶"。

2.实战案例

2.1 能用 GROUP BY 替代时避免使用 DISTINCT

慢查询

SELECT DISTINCT customer_id FROM orders

优化后

SELECT customer_id FROM orders GROUP BY customer_id

数据库引擎对 GROUP BY 的优化有时优于 DISTINCT，尤其在查询字段包含索引列时，而DISTINCT需要额外创建临时表。

2.2 UNION ALL 替代 OR（索引列）

慢查询

SELECT * FROM products WHERE category = 'Electronics' OR category = 'Books'

优化后

SELECT * FROM products WHERE category = 'Electronics'
UNION ALL
SELECT * FROM products WHERE category = 'Books'

当查询条件中有OR且涉及索引列时，使用UNION ALL可以让数据库分别对每个条件使用索引，避免全表扫描。而OR可能导致索引失效，特别是当两个条件涉及不同列时。

2.3 避免对索引列使用函数操作

慢查询

SELECT * FROM users WHERE UPPER(username) = 'JOHNDOE';

优化后

-- 存储时统一大写化，或直接搜索原始值而不应用函数
SELECT * FROM users WHERE username = 'JohnDoe';

函数操作（如UPPER(username)）会破坏索引的有序性，导致全表扫描。如果你的mysql版本在8.0.13+以上版本，则可以创建函数索引：

# 注意这里函数索引，需要用括号 "()"
create index idx_name on t_person ((UPPER(username)));

2.4 对大型子查询使用 EXISTS 替代 IN

慢查询

SELECT
  * 
FROM
  orders 
WHERE
  customer_id IN ( SELECT customer_id FROM blacklist )

优化后

SELECT * FROM orders o WHERE EXISTS (
  SELECT 1 FROM blacklist b WHERE b.customer_id = o.customer_id
);

IN 对小型数据集表现良好（此时与EXISTS性能差异可忽略），但当子查询涉及大型数据集或关联查询（Correlated Subquery）时，EXISTS 的性能更优。

2.5 使用覆盖索引

慢查询

SELECT
  order_id,
  customer_id 
FROM
  orders 
WHERE
  order_date >= '2025-05-10'

优化后

CREATE INDEX idx_date_oid_cid ON orders(order_date, order_id, customer_id)

覆盖索引包含查询所需的所有列，数据库无需回表读取数据页（称为 "Index Only Scan"）。需要注意：索引列顺序需与查询字段顺序一致，且查询条件包含索引前缀列。

2.6 对大型表进行分区（Partitioning）

预估 orders 表可能达到上千万的数据量甚至亿级。那么我们在建立表时就针对某个字段建立分区，比如：根据订单的时间字段，如下示例：

CREATE TABLE `t_orders` (
  `id` int NOT NULL,
  `custom_id` varchar(255) DEFAULT NULL,
  `order_no` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `total_amount` decimal(9,2) DEFAULT NULL,
  `order_date` datetime NOT NULL,
  PRIMARY KEY (`id`, `order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
    PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
    -- 其它月份
    PARTITION p202512 VALUES LESS THAN ('2026-01-01'),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

插入如下数据：

执行如下查询计划

从指定的分区查询数据。

适用场景：时间范围查询（如按日期分区）、区域查询等。

注意：分区键需与查询条件一致，否则可能失效。

2.7 用 UNION ALL 替代 OR

优化前

SELECT
  * 
FROM
  products 
WHERE
  category = 'Electronics' OR price > 2000

上面SQL执行计划如下：

没有走索引。

优化后

SELECT * FROM products WHERE category = 'PS5'
UNION ALL
SELECT * FROM products WHERE price > 2000;

查看该SQL的执行计划

OR 条件常导致索引失效，而 UNION ALL 可分别对两部分条件使用索引扫描。影响：对大型表可提速 10-100 倍。

2.8 用 NOT EXISTS 替代 NOT IN

优化前

SELECT * FROM customers 
WHERE id NOT IN (SELECT customer_id FROM orders)

优化后

SELECT * FROM customers c 
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
)

NOT IN对子查询结果中的NULL值敏感（若子查询含NULL，NOT IN永远返回空），而NOT EXISTS无此问题。对大型子查询可提速 5-50 倍。

2.9 使用LIMIT限制ORDER BY结果集

优化前

SELECT * FROM products ORDER BY price DESC

优化后

SELECT * FROM products ORDER BY price DESC LIMIT 10

原查询需对全表数据排序（开销巨大），而 LIMIT 可仅返回前 N 条记录，避免不必要的全表排序。

2.10 使用JOIN替代子查询

优化前

SELECT
  * 
FROM
  employees 
WHERE
  department_id IN ( SELECT id FROM departments WHERE active = 1 );

优化后

SELECT
  e.* 
FROM
  employees e
  JOIN departments d ON e.department_id = d.id 
WHERE
  d.active = 1

数据库对 JOIN 操作的优化更高效（如合并连接、哈希连接等），而子查询可能触发多次扫描或临时表生成。

四时宝库

程序员的知识宝库

10个SQL优化技巧，性能提升300%