mysql查询优化中的SQL重写与索引策略

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

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

MySQL无法对表达式结果建立索引，一旦在查询条件里对索引列用函数（比如 DATE(created_at)、UPPER(name)），即使该列有索引，优化器也大概率放弃使用。

• 错误写法：

  SELECT * FROM orders WHERE DATE(order_time) = '2024-01-01';

  ——order_time 上的索引完全失效
• 正确写法：改用范围查询，让索引可下推

  SELECT * FROM orders WHERE order_time >= '2024-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注入攻击

下载

• 常见误判：以为只要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 就可能意味着整个逻辑需要重构。