优化慢速查询：未使用索引的全表扫描问题

题目

优化慢速查询：未使用索引的全表扫描问题

信息

• 类型：问答
• 难度：⭐

考点

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

快速回答

当遇到未使用索引的全表扫描时，优化方案包括：

• 为WHERE条件列创建索引
• 避免在WHERE子句中对列进行函数操作
• 使用EXPLAIN分析查询执行计划
• 确保索引列的数据类型匹配查询条件

问题场景

假设有一个订单表orders，包含数百万条记录，执行以下查询时响应缓慢：

SELECT * FROM orders 
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2023-01';

原理说明

导致性能问题的核心原因：

• 全表扫描：对order_date列使用DATE_FORMAT()函数导致无法使用索引
• 数据类型不匹配：函数操作改变了列的数据类型，破坏了索引有效性
• 返回所有列：SELECT *会读取不必要的列数据

优化方案

1. 修改查询条件（避免函数操作）

SELECT order_id, customer_id, total_amount 
FROM orders 
WHERE order_date >= '2023-01-01' 
  AND order_date < '2023-02-01';

优化点：

• 使用日期范围代替函数操作
• 显式指定需要的列（减少数据传输）

2. 创建索引

CREATE INDEX idx_order_date ON orders(order_date);

索引原理：B+树索引允许快速定位日期范围，将时间复杂度从O(n)降低到O(log n)

3. 使用EXPLAIN验证

EXPLAIN SELECT ...  -- 检查type列是否为range/index而非ALL

优化后执行计划应显示：

• type: range（范围扫描）
• key: idx_order_date（使用索引）
• rows: <1000（扫描行数显著减少）

最佳实践

• 左匹配原则：WHERE条件中的列应是索引的最左列
• 数据类型匹配：确保查询条件与索引列数据类型一致
• 覆盖索引：若查询只需索引列，可考虑SELECT索引包含的列

常见错误

• 在WHERE子句中对索引列进行运算或函数操作
• 创建索引后不验证实际使用情况
• 过度索引（每个查询都建索引会导致写操作变慢）

扩展知识

• 索引类型选择：日期范围查询适合B+树索引，全文搜索适合FULLTEXT索引
• 复合索引：如果常按(customer_id, order_date)查询，可创建复合索引
• 统计信息：定期执行ANALYZE TABLE更新统计信息，帮助优化器选择索引