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