一、核心优化思想

• 减少数据扫描量 数据库查询的物理 I/O 和内存消耗与扫描的数据量正相关。通过索引、分区等手段缩小扫描范围，可直接降低磁盘读取和内存占用。
• 降低计算复杂度 复杂操作（如全表扫描、多表 JOIN）会占用大量 CPU 资源。简化计算逻辑可减少查询时间，提升并发处理能力。
• 分步处理复杂查询 单次复杂查询容易导致锁竞争和资源独占。分步操作利用应用层灵活性，降低数据库单次负载。

二、索引优化策略

2.1 索引设计原则

2.1.1最左匹配原则

（1）索引生效的查询

-- 联合索引 (a, b, c)
-- 有效：按索引顺序使用 a 和 b
WHERE a = 1 AND b = 2;

-- 无效：跳过 a，索引无法使用
WHERE b = 2;

（2）范围查询影响索引

-- `b` 是范围查询，`c` 无法使用索引
WHERE a = 100 AND b > 200 AND c = 300;

-- 同样的问题
WHERE a = 100 AND b BETWEEN 200 AND 300;

索引使用情况如下：

解决方案：尽量将范围查询放在 WHERE 末尾，避免影响后续索引。

（3）IN 查询

WHERE a IN (100, 101) AND b IN (200, 201) AND c = 300;

索引使用情况如下：

（4）LIKE 非前缀匹配导致索引失效

WHERE a = 100 AND b LIKE '%abc%' AND c = 300;

索引使用情况如下：

解决方案：使用 LIKE 'abc%' 进行前缀匹配，可保持索引可用。

小结

1. **尽量使用等值查询 (= 或 IN)**，避免范围查询影响索引。
2. 范围查询 (> < BETWEEN LIKE '%abc%') 应尽量放在 WHERE 末尾，减少索引失效风险。
3. **LIKE 查询应使用前缀匹配 (LIKE 'abc%')**，避免索引失效。

2.1.2 覆盖索引

若查询字段全部包含在索引中（即覆盖索引），数据库可直接从索引树获取数据，无需回表查询主键索引。

回表代价：若未覆盖字段，需通过主键 ID 回主索引读取完整数据，增加磁盘 I/O。

-- 覆盖索引示例
CREATE INDEX idx_cover ON users(city, age);
SELECT city, age FROM users WHERE city = 'Shanghai';  --  无需回表
SELECT name FROM users WHERE city = 'Shanghai';       --  需回表获取name

2.2 索引失效场景

2.2.1 隐式类型转换

若索引字段类型与查询条件类型不一致（如字符串字段匹配数字），数据库需隐式转换类型，导致无法使用索引。

--  索引失效（supplier_id为VARCHAR）
SELECT * FROM orders WHERE supplier_id = 7067;
--  正确写法
SELECT * FROM orders WHERE supplier_id = '7067';

2.2.2 函数操作索引列

对索引列使用函数（如 YEAR(date_column)）会破坏 B+树的有序性，导致索引失效。

--  索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2023;
--  优化写法
SELECT * FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

三、查询语句优化

3.1 分页优化

• LIMIT offset, N 需要扫描前 offset + N 行数据，偏移量越大性能越差。
• 游标分页：利用索引有序性，直接定位起始位置，减少扫描行数。

--  低效分页（扫描100万+20行）
SELECT * FROM orders LIMIT 1000000, 20;
--  高效分页（基于主键游标）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

3.2 用 JOIN 替代子查询

• 关联子查询（Correlated Subquery）对外层查询的每一行执行一次，时间复杂度为 O(N^2)。
• JOIN 优化：将嵌套查询扁平化，利用哈希连接（Hash Join）或嵌套循环连接（Nested Loop Join）降低复杂度。

--  时间复杂度 O(N^2)
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id)
FROM users u;

--  优化为 JOIN（时间复杂度 O(N)）
SELECT u.name, COUNT(o.id)
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

3.3 减少结果集

• 在查询优化阶段，尽早将过滤条件（WHERE、JOIN ON）应用到数据源，减少中间结果集大小。

--  先JOIN后过滤（处理全量数据）
SELECT u.name, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Shanghai';

--  先过滤后JOIN（减少JOIN数据量）
SELECT u.name, o.amount
FROM (SELECT * FROM users WHERE city = 'Shanghai') u
LEFT JOIN orders o ON u.id = o.user_id;

3.4 减少关联查询

分步查询 + 应用层组装

-- 原始复杂查询  
SELECT u.name, o.amount, p.product_name  
FROM users u  
INNER JOIN orders o ON u.id = o.user_id  
INNER JOIN products p ON o.product_id = p.id  
WHERE u.city = 'Beijing' AND p.category = 'Electronics';  

-- 优化步骤：  
-- 1. 查询北京用户ID  
SELECT id FROM users WHERE city = 'Beijing';  
-- 2. 查询这些用户的订单  
SELECT user_id, product_id FROM orders WHERE user_id IN (1001, 1002);  
-- 3. 查询商品信息  
SELECT id, product_name FROM products WHERE category = 'Electronics' AND id IN (2001, 2002);  
-- 应用层组装最终结果  

四、执行计划分析

优化器工作流程：

1. 解析 SQL：生成抽象语法树（AST）。
2. 逻辑优化：重写查询（如子查询展开、谓词下推）。
3. 物理优化：选择访问路径（索引扫描 vs 全表扫描）。
4. 成本估算：基于统计信息（如行数、索引基数）选择最低成本计划。

关键字段解读

感谢您的阅读！如果觉得内容有帮助，不妨点个赞支持一下，留下您的想法一起交流，别忘了关注，精彩内容不错过！您的每一个互动，都是我持续创作的动力！