mysql查询优化中的SQL重写与索引策略_网络技术

对字段使用函数会导致索引失效；如DATE(order_time)使索引不可用，应改用范围查询order_time >= '2025-01-01 00:00:00' AND order_time

WHERE条件中对字段使用函数会导致索引失效

MySQL无法对表达式结果建立索引，一旦在查询条件里对索引列用函数（比如 DATE(created_at)、UPPER(name)），即使该列有索引，优化器也大概率放弃使用。
错误写法：
SELECT * FROM orders WHERE DATE(order_time) = '2025-01-01';
——order_time 上的索引完全失效
正确写法：改用范围查询，让索引可下推
SELECT * FROM orders WHERE order_time >= '2025-01-01 00:00:00' AND order_time < '2024-01-02 00:00:00';
如果必须大小写不敏感匹配，优先建函数索引（MySQL 8.0+）：
CREATE INDEX idx_upper_name ON users ((UPPER(name)));
，而非在 WHERE 里写 UPPER(name) = 'JOHN'
避免在WHERE中使用NOT、!=、和IS NULL（除非有对应索引）

这些操作符通常无法利用B+树索引的有序性做快速定位，容易触发全表扫描。尤其当字段选择性不高时，性能下降更明显。
status != 'done' 很难走索引；若业务允许，改用 status IN ('pending', 'processing') 并确保 status 列上有索引
is_deleted IS NULL 在无索引时很慢；如果该字段常用于过滤，建议单独建索引：
CREATE INDEX idx_is_deleted ON posts (is_deleted);
注意：MySQL 8.0+ 对 IS NULL 的支持有所增强，但依然不如等值查询高效；NOT IN 子查询还可能因NULL传播导致结果为空，应优先改写为 LEFT JOIN ... IS NULL
联合索引的最左前缀原则不是“包含”，而是“连续匹配”

联合索引 (a, b, c) 可以加速 WHERE a = ?、WHERE a = ? AND b = ?、WHERE a = ? AND b = ? AND c = ?，但不能加速 WHERE b = ? 或 WHERE a = ? AND c = ?（缺少 b）。

常见误判：以为只要SQL里出现了索引字段就一定走索引——实际要看是否满足最左连续性

排序场景更严格：如果 ORDER BY b, c，而索引是 (a, b, c)，则无法避免文件排序（Using filesort），因为缺失 a 条件，索引无法按 b,c 有序返回

优化建议：根据高频查询模式调整字段顺序，把等值查询字段放前面，范围查询或排序字段放后面；例如高频查 WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC，索引应建为 (tenant_id, status, created_at)

EXPLAIN结果里type=ALL或rows远大于实际返回行数，说明SQL重写比加索引更有效

很多同学一看到慢查询就立刻加索引，但有时问题出在SQL语义本身——比如隐式类型转换、子查询未展开、JOIN顺序不合理，这时强行加索引收效甚微，甚至让执行计划更差。

典型陷阱：user_id 是 VARCHAR 类型，但查询写成 WHERE user_id = 123（整数），触发隐式转换，索引失效；必须统一类型：WHERE user_id = '123'

关联子查询如 SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region = 'CN')，MySQL 5.6+ 通常会将子查询物化，但不如手动改写为 JOIN 稳定

真正该加索引的，是那些 EXPLAIN 显示 type=ref 或 range、但 rows 仍偏大的情况；如果 type=all 且 rows 接近表总行数，先看能不能把条件提前、拆分查询、或用覆盖索引减少回表

索引不是万能解药，SQL重写的边界往往比想象中更宽——尤其是当查询涉及时间范围、多表关联、或字段类型混用时，执行计划里的一个 Using temporary 就可能意味着整个逻辑需要重构。

# mysql # ai # 隐式类型转换 # 隐式转换 # sql # NULL # select # date # using # 类型转换 # 重构 # 重写 # 行数 # 尤其是 # 隐式 # 很难 # 收效甚微 # 上有 # 要看 # 不高 # 能把

相关栏目：【网站优化151355 】【网络推广146373 】【网络技术251813 】【 AI营销90571 】