添加WHERE子句后MySQL查询性能骤降的优化求助

核心问题定位 #

添加fac.flat_inventory_id IS NULL后性能骤降，本质是MySQL无法高效判断flat_inventory_id是否存在于fi_assisted_costs表中，大概率是缺少针对性索引，或执行计划选择了低效的全表扫描/关联方式。

具体优化步骤 #

1. 给fi_assisted_costs添加关键索引 #

针对排除条件的核心字段创建索引，让MySQL快速判断记录是否存在：

CREATE INDEX idx_fac_flat_inv_id ON fi_assisted_costs(flat_inventory_id);

如果flat_inventory_id是该表的业务主键，直接设为主键（主键自带唯一索引），性能会更优。

2. 用NOT EXISTS重构查询（确保写法正确） #

替换LEFT JOIN + IS NULL为NOT EXISTS，同时避免字段上的函数调用：

SELECT MIN(fi.id)
FROM flat_inventories fi
JOIN fi_cogs_map fcm 
  ON fi.product_id = fcm.product_id 
  AND fi.created_at >= fcm.cogs_date 
  AND fi.created_at < DATE_ADD(fcm.cogs_date, INTERVAL 1 DAY)
WHERE fi.source_type = 'audit'
  AND fi.unit_cost = 0
  AND fi.status NOT IN ('lost','damaged','expired','invalid')
  AND fi.storage_id NOT IN (SELECT id FROM storages WHERE is_virtual = 1)
  AND fi.warehouse_id > 1
  AND NOT EXISTS (
      SELECT 1 
      FROM fi_assisted_costs fac 
      WHERE fac.flat_inventory_id = fi.id
  );

关键优化点：去掉DATE(fi.created_at)函数，改用范围匹配，避免索引失效。

3. 给flat_inventories创建复合过滤索引 #

把查询的过滤条件、关联字段和结果字段整合到一个复合索引中，让MySQL直接通过索引完成过滤和关联，无需回表：

CREATE INDEX idx_flat_inv_audit_filter ON flat_inventories(
    source_type, 
    unit_cost, 
    warehouse_id, 
    status, 
    product_id, 
    created_at, 
    id
);

索引字段顺序遵循过滤性从高到低的原则：先放等值匹配字段，再放范围/枚举字段，最后放关联和结果字段。

4. 优化虚拟存储ID的查询 #

给storages表添加索引，加速虚拟存储ID的子查询：

CREATE INDEX idx_storages_virtual ON storages(is_virtual, id);

如果虚拟存储ID是固定值，可直接将子查询结果替换为常量列表（如fi.storage_id NOT IN (1,2,3)），进一步减少查询开销。

5. 更新表统计信息 #

让MySQL优化器获取最新的表数据分布，生成更合理的执行计划：

ANALYZE TABLE flat_inventories, fi_cogs_map, fi_assisted_costs, storages;

6. 用ORDER BY + LIMIT 1替代MIN() #

对于查找最小ID的场景，按ID排序后取第一条，可能比MIN()函数更高效：

SELECT fi.id
FROM flat_inventories fi
JOIN fi_cogs_map fcm 
  ON fi.product_id = fcm.product_id 
  AND fi.created_at >= fcm.cogs_date 
  AND fi.created_at < DATE_ADD(fcm.cogs_date, INTERVAL 1 DAY)
WHERE fi.source_type = 'audit'
  AND fi.unit_cost = 0
  AND fi.status NOT IN ('lost','damaged','expired','invalid')
  AND fi.storage_id NOT IN (SELECT id FROM storages WHERE is_virtual = 1)
  AND fi.warehouse_id > 1
  AND NOT EXISTS (
      SELECT 1 
      FROM fi_assisted_costs fac 
      WHERE fac.flat_inventory_id = fi.id
  )
ORDER BY fi.id ASC
LIMIT 1;

验证优化效果 #

执行优化后的查询，用EXPLAIN ANALYZE查看执行计划，确认：

• fi_assisted_costs的查询使用了idx_fac_flat_inv_id索引
• flat_inventories的查询使用了创建的复合索引
• 没有出现ALL类型的全表扫描操作

内容的提问来源于stack exchange，提问作者Abhishek Halder