SQL优化技巧提升性能

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

1、 能用 GROUP BY 替代时避免使用 DISTINCT

慢查询

SELECT DISTINCT customer_id FROM orders

优化后

SELECT customer_id FROM orders GROUP BY customer_id

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

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可能导致索引失效，特别是当两个条件涉及不同列时。

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

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 的性能更优。

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"）。需要注意：索引列顺序需与查询字段顺序一致，且查询条件包含索引前缀列。

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

);

插入如下数据：

INSERT INTO `t_orders`(`id`,`custom_id`,`order_no`,`address`,`total_amount`,`order_date`)

VALUES

(1,'C1001','DD1000001','江苏省南京市',188.00,'2025-05-19 10:10:10'),

(2,'C1002','DD1000002','北京市朝阳区',288.00,'2025-05-19 10:10:10'),

(3,'C1003','DD1000003','福建省福州市',388.00,'2025-05-19 10:10:10'),

(4,'C1004','DD1000004','重庆市万州区',488.00,'2025-05-19 10:10:10'),

(5,'C1005','DD1000005','山东省济南市',588.00,'2025-05-19 10:10:10'),

(6,'C1006','DD1000006','山东省青岛市',688.00,'2025-05-19 10:10:10'),

(7,'C1007','DD1000007','江苏省苏州市',788.00,'2025-05-19 10:10:10'),

(8,'C1008','DD1000008','广东省广州市',888.00,'2025-05-19 10:10:10'),

(9,'C1009','DD1000009','安徽省合肥市',988.00,'2025-05-19 10:10:10'),

(10,'C1010','DD1000010','江苏省南京市',188.00,'2025-05-19 10:10:10'),

(11,'C1011','DD1000011','江苏省南京市',168.00,'2025-05-19 10:20:10'),

(12,'C1012','DD1000012','北京市朝阳区',268.00,'2025-05-19 10:20:10'),

(13,'C1013','DD1000013','福建省福州市',368.00,'2025-05-19 10:20:10'),

(14,'C1014','DD1000014','重庆市万州区',468.00,'2025-05-19 10:20:10'),

(15,'C1015','DD1000015','山东省济南市',568.00,'2025-05-19 10:20:10'),

(16,'C1016','DD1000016','山东省青岛市',668.00,'2025-05-19 10:20:10'),

(17,'C1017','DD1000017','江苏省苏州市',768.00,'2025-05-19 10:20:10'),

(18,'C1018','DD1000018','广东省广州市',868.00,'2025-05-19 10:20:10'),

(19,'C1019','DD1000019','安徽省合肥市',968.00,'2025-05-19 10:20:10');

执行如下查询计划

EXPLAIN select * from `t_orders` where `custom_id`='C1018' and `order_date`='2025-05-19 10:20:10'

从指定的分区查询数据。

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

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

7、 用 UNION ALL 替代 OR

优化前

SELECT

*

FROM

products

WHERE

category = 'Electronics' OR price > 2000

上面SQL执行计划如下：

EXPLAIN select * from products where category = 'Electronics' OR price > 2000

没有走索引。

优化后

SELECT * FROM products WHERE category = 'PS5'

UNION ALL

SELECT * FROM products WHERE price > 2000;

查看该SQL的执行计划

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

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 倍。

9、 使用LIMIT限制ORDER BY结果集

优化前

SELECT * FROM products ORDER BY price DESC

优化后

SELECT * FROM products ORDER BY price DESC LIMIT 10

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

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 操作的优化更高效（如合并连接、哈希连接等），而子查询可能触发多次扫描或临时表生成。

来源：https://www.iwmyx.cn/10gsqlyhjqxna.html