优化简单查询性能

题目

优化简单查询性能

信息

• 类型：问答
• 难度：⭐

考点

索引使用,避免全表扫描,WHERE子句优化

快速回答

优化该查询的核心要点：

• 为status和order_date字段创建索引
• 避免在WHERE子句中对字段进行函数操作
• 使用范围查询替代函数计算

问题场景

现有订单表orders结构：

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2),
  status VARCHAR(20),  -- 状态值如'pending','shipped'
  order_date DATE     -- 订单日期
);

需要优化以下查询：

SELECT * FROM orders 
WHERE YEAR(order_date) = 2023 
  AND status = 'shipped';

问题分析

当前查询存在两个主要性能问题：

1. 函数导致索引失效：YEAR(order_date)使日期索引无法使用
2. 全表扫描风险：当数据量大时，没有合适索引会导致全表扫描

优化方案

1. 创建复合索引

CREATE INDEX idx_status_order_date ON orders(status, order_date);

原理说明：复合索引可同时覆盖status和order_date的查询条件，避免全表扫描。

2. 改写WHERE子句

SELECT * FROM orders
WHERE status = 'shipped'
  AND order_date >= '2023-01-01' 
  AND order_date < '2024-01-01';

优化点：

• 消除YEAR()函数，使日期索引生效
• 明确日期范围，利用索引的有序性

最佳实践

• 左前缀原则：复合索引中=条件列放前面，范围列放后面
• 避免列运算：WHERE子句中不对索引列进行函数/表达式计算
• 覆盖索引：若只需部分列，可创建包含这些列的复合索引

常见错误

• 错误1：单独创建单列索引（可能无法同时利用两个索引）
• 错误2：使用BETWEEN替代范围查询（可能包含边界问题）
• 错误3：忽略数据分布（如status值重复率过高时索引效果差）

扩展知识

• 执行计划检查：使用EXPLAIN验证索引使用情况
• 索引选择性：高唯一性值的列更适合建索引
• 统计信息更新：定期ANALYZE TABLE确保优化器选择正确索引