WHERE子句用函数的代价与7个实战索引优化技巧

一、为什么说WHERE子句是性能关键？

当我们在SQL查询的WHERE条件中使用DATE(order_time)、LOWER(username)这类函数时，就像让数据库戴着拳击手套操作精密仪器——即使字段建立了索引，数据库也无法有效利用。这是因为索引存储的是原始数据值，而函数处理后的结果与索引结构完全不匹配，最终导致全表扫描这种性能灾难。

二、WHERE子句使用函数的三大影响

2.1 索引失效的连锁反应

某电商平台订单表500万记录，使用WHERE YEAR(create_time)=2023查询时：

• 索引扫描时间：0.05ms → 全表扫描时间：1200ms
• CPU使用率从5%飙升至95%

2.2 隐式类型转换陷阱

当字段类型与条件值类型不匹配时，例如字符串字段用数字查询：

WHERE product_id = 1001  -product_id是VARCHAR类型

这会触发隐式的CAST()函数转换，同样导致索引失效。

三、高级索引优化技巧

3.1 计算列解决方案

针对常用的函数表达式，在SQL Server中创建计算列索引：

ALTER TABLE orders 
ADD create_year AS YEAR(create_time) PERSISTED

CREATE INDEX idx_create_year ON orders(create_year)

3.2 表达式索引实践

PostgreSQL支持直接对表达式建立索引：

CREATE INDEX idx_lower_username 
ON users (LOWER(username))

3.3 覆盖索引的正确姿势

通过包含所有需要字段，减少回表操作：

CREATE INDEX idx_covering ON orders (
    status, 
    create_time
) INCLUDE (amount, customer_id)

3.4 索引选择性的黄金法则

对性别这种低区分度字段建索引，性能提升微乎其微：

• 性别字段索引：区分度0.1% → 无效
• 手机号字段索引：区分度99.9% → 高效

四、超越索引的其他优化策略

4.1 执行计划深度分析

使用EXPLAIN ANALYZE查看MySQL执行计划时，重点关注：

• type列是否出现ALL（全表扫描）
• Extra列是否出现Using filesort

4.2 分页查询优化方案

将传统分页：

SELECT  FROM products 
LIMIT 10000,20

优化为游标分页：

SELECT  FROM products
WHERE id > 10000
ORDER BY id LIMIT 20

4.3 冷热数据分离策略

对历史数据采用分区表管理：

CREATE TABLE orders (
    ...
) PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2025)
)

五、性能优化检查清单

每次编写SQL时对照检查：

1. WHERE条件是否避免使用函数？
2. JOIN字段类型是否完全一致？
3. 是否使用SELECT 获取不需要的字段？
4. ORDER BY字段是否有索引支持？
5. 分页查询是否超过1000页？

通过以上优化手段，某物流系统将核心查询的平均响应时间从850ms降低到23ms，数据库服务器数量从12台缩减到3台。记住：好的SQL优化不是炫技，而是建立在对执行机制的深刻理解和持续的性能监控之上。