MySQL查询在引入Where子句时变慢

MySQL Query Slowing Down on introduction of Where Clause

提问人：Abhishek Halder 提问时间：10/12/2023 最后编辑：Abhishek Halder 更新时间：10/13/2023 访问量：120

问：

我正在编写一个MySQL过程，用于计算在实际库存审计中添加的库存项目的代理成本。我创建了一个包含日期和产品代理成本的成本映射，以及另一个包含库存 ID 和代理成本（基于库存审核日期和产品）的“inventory_assisted_cost”映射。

为了在过程中迭代此过程，我首先计算满足审计项目条件的最小库存 ID，成本为 0，存在于成本映射中，并且不存在于“inventory_assisted_cost”中。如果我在没有 where null 子句的情况下运行此查询片段，我会在 0.5 秒内获得结果。一旦我添加 where null / where not null 子句，执行时间就会超过 10 分钟。

下面是不带 where 子句的查询版本：

select fi.id
from flat_inventories fi 
left join fi_cogs_map fcm on fcm.product_id = fi.product_id and fi.id between fcm.min_fi and fcm.max_fi
left join fi_assisted_costs fac on fac.flat_inventory_id = fi.id
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 s.id from niyoweb.storages s where s.zone = 'virtual' or s.category = 'virtual')
and fi.warehouse_id > 1
and fcm.product_id is not null
limit 1

以下是上述解释查询的输出：

编号 select_type 桌子 类型 possible_keys 钥匙 key_len 裁判 行 过滤 额外
1 主要 fi 裁判 flat_inventories_warehouse_id_foreign，flat_inventories_source_type_source_id_index，flat_inventories_status_index flat_inventories_source_type_source_id_index 767 常量 6405844 3.1 使用索引条件;使用where
1 主要 FCM公司 裁判 fi_cogs_map_product_id_index，fi_cogs_map_min_fi_index，fi_cogs_map_max_fi_index fi_cogs_map_product_id_index 4 fi.product_id 3 100 使用where
1 主要 外交 事务 委员会 裁判 fi_assisted_costs_flat_inventory_id，fi_assisted_costs_assisted_cost_basis，fi_assisted_costs_flat_inventory_id_unique fi_assisted_costs_assisted_cost_basis 8 fi.id 1 100 使用索引
2 子查询 s 都 主要 23669 19 使用where

下面是带有 where 子句的查询版本：

select fi.id
from flat_inventories fi 
left join fi_cogs_map fcm on fcm.product_id = fi.product_id and fi.id between fcm.min_fi and fcm.max_fi
left join fi_assisted_costs fac on fac.flat_inventory_id = fi.id
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 s.id from niyoweb.storages s where s.zone = 'virtual' or s.category = 'virtual')
and fi.warehouse_id > 1
and fcm.product_id is not null 
and fac.flat_inventory_id is null
limit 1

以下是上述解释查询的输出：

编号 select_type 桌子 类型 possible_keys 钥匙 key_len 裁判 行 过滤 额外
1 主要 fi 裁判 主要，flat_inventories_warehouse_id_foreign，flat_inventories_source_type_source_id_index，flat_inventories_index_composite，flat_inventories_status_index flat_inventories_source_type_source_id_index 767 常量 6405844 3.1 使用索引条件;使用where
1 主要 FCM公司 裁判 fi_cogs_map_product_id_index，fi_cogs_map_min_fi_index，fi_cogs_map_max_fi_index fi_cogs_map_product_id_index 4 fi.product_id 3 11.11 使用where
1 主要 外交 事务 委员会 裁判 fi_assisted_costs_flat_inventory_id，fi_assisted_costs_assisted_cost_basis，fi_assisted_costs_flat_inventory_id_unique fi_assisted_costs_assisted_cost_basis 8 fi.id 1 100 在哪里使用;不存在;使用索引
2 子查询 s 都 主要 23669 19 使用where

我不知如何让它与附加的 where 子句一起工作。

编辑：

以下是四个表的 DDL：

CREATE TABLE `flat_inventories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `storage_id` bigint unsigned DEFAULT NULL,
  `manufacturing_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `unit_cost` decimal(11,6) DEFAULT NULL,
  `source_type` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source_id` bigint unsigned DEFAULT NULL,
  `product_id` int unsigned NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `status` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`warehouse_id`),
  KEY `flat_inventories_storage_id_foreign` (`storage_id`),
  KEY `flat_inventories_warehouse_id_foreign` (`warehouse_id`),
  KEY `flat_inventories_source_type_source_id_index` (`source_type`,`source_id`),
  KEY `flat_inventories_index_composite` (`product_id`,`warehouse_id`,`status` DESC),
  KEY `flat_inventories_status_index` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=140432680 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `fi_cogs_map` (
  `fi_grp_by` date DEFAULT NULL,
  `min_date` date DEFAULT NULL,
  `max_date` date DEFAULT NULL,
  `min_fi` bigint unsigned DEFAULT NULL,
  `max_fi` bigint unsigned DEFAULT NULL,
  `min_fi_crt` timestamp NULL DEFAULT NULL,
  `max_fi_crt` timestamp NULL DEFAULT NULL,
  `fi_ids` int unsigned NOT NULL DEFAULT '0',
  `src_ids` int unsigned NOT NULL DEFAULT '0',
  `pids` int unsigned NOT NULL DEFAULT '0',
  `product_id` int unsigned NOT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `prod_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unit_mrp` decimal(10,2) DEFAULT NULL,
  `group_id` int unsigned DEFAULT NULL,
  `brand_id` int unsigned DEFAULT NULL,
  `marketer_id` int unsigned DEFAULT NULL,
  `level_id` tinyint unsigned DEFAULT NULL,
  `cl4_id` int unsigned DEFAULT NULL,
  `cl3_id` int unsigned DEFAULT NULL,
  `cl2_id` int unsigned DEFAULT NULL,
  `cl1_id` int unsigned DEFAULT NULL,
  `price_basis` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fi_crt` timestamp NULL DEFAULT NULL,
  `prd_cost` decimal(15,6) DEFAULT NULL,
  `grp_cost` decimal(15,6) DEFAULT NULL,
  `brd_cl4_cost` decimal(15,6) DEFAULT NULL,
  `brd_cl3_cost` decimal(15,6) DEFAULT NULL,
  `brd_cl2_cost` decimal(15,6) DEFAULT NULL,
  `brd_cl1_cost` decimal(15,6) DEFAULT NULL,
  `mkt_cl4_cost` decimal(15,6) DEFAULT NULL,
  `mkt_cl3_cost` decimal(15,6) DEFAULT NULL,
  `mkt_cl2_cost` decimal(15,6) DEFAULT NULL,
  `mkt_cl1_cost` decimal(15,6) DEFAULT NULL,
  `cl4_cost` decimal(15,6) DEFAULT NULL,
  `cl3_cost` decimal(15,6) DEFAULT NULL,
  `cl2_cost` decimal(15,6) DEFAULT NULL,
  `cl1_cost` decimal(15,6) DEFAULT NULL,
  KEY `fi_cogs_map_min_date_index` (`min_date` DESC),
  KEY `fi_cogs_map_max_date_index` (`max_date` DESC),
  KEY `fi_cogs_map_product_id_index` (`product_id`),
  KEY `fi_cogs_map_min_fi_index` (`min_fi`),
  KEY `fi_cogs_map_max_fi_index` (`max_fi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `fi_assisted_costs` (
  `flat_inventory_id` bigint unsigned NOT NULL,
  `assisted_cost` decimal(15,6) DEFAULT '0.000000',
  `assisted_cost_basis` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`flat_inventory_id`),
  KEY `fi_assisted_costs_assisted_cost_basis` (`assisted_cost_basis`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `storages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `warehouse_id` int unsigned NOT NULL,
  `zone` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sequence_id` double(13,3) NOT NULL DEFAULT '0.000',
  `length` decimal(8,2) DEFAULT NULL,
  `width` decimal(8,2) DEFAULT NULL,
  `height` decimal(8,2) DEFAULT NULL,
  `weight_capacity` decimal(8,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `storages_warehouse_id_zone_index` (`warehouse_id`,`zone`),
  KEY `storages_warehouse_id_label_index` (`warehouse_id`,`label`),
  CONSTRAINT `storages_warehouse_id_foreign` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27614 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

持平库存中的指数

桌子 Non_unique Key_name Seq_in_index Column_name 整理 基数 Sub_part 包装好的 零 Index_type 评论 Index_comment 可见 表达
flat_inventories 0 主要 1 编号 一个 134134576 树 是的
flat_inventories 0 主要 2 warehouse_id 一个 134128376 树 是的
flat_inventories 1 flat_inventories_storage_id_foreign 1 storage_id 一个 199163 是的 树 是的
flat_inventories 1 flat_inventories_warehouse_id_foreign 1 warehouse_id 一个 4415 树 是的
flat_inventories 1 flat_inventories_source_type_source_id_index 1 source_type 一个 1305 是的 树 是的
flat_inventories 1 flat_inventories_source_type_source_id_index 2 source_id 一个 5931168 是的 树 是的
flat_inventories 1 flat_inventories_index_composite 1 product_id 一个 316274 树 是的
flat_inventories 1 flat_inventories_index_composite 2 warehouse_id 一个 498305 树 是的
flat_inventories 1 flat_inventories_index_composite 3 地位 D 513456 树 是的
flat_inventories 1 flat_inventories_status_index 1 地位 一个 3631 树 是的

按状态对固定库存进行计数

地位 计数（fi.id）
audit_bad 109760
audit_blocked 21828
audit_hold 133631
audit_loss 1683656
嘴 38480
捆绑 22920
取消 23
破 128977
删除 421074
交付 123509123
派出 173295
争议 248171
过期 188399
拿 19849
无效 55531
清算 4462382
liquidated_manual 85049
失去 882495
表现 130
拿 117430
上架 85473
putaway_cancelled_shipment 283
putaway_return 27221
putaway_rto 10444
putaway_sto 4693
putway_return 7
ready_for_sale 1750057
RTO 276656
出售 3355983
非捆绑 612

按来源类型计算固定库存

source_type 计数（fi.id）
审计 2796338
bundle_recipe 9873
捆绑 2423
GRN公司 131064751
次序 2619
产品 705
返回 3559411
stock_transfer 377512

如果没有该条件，则应有大约 640 万行与所有条件匹配。在这种特定条件下，目前为 <100，但由于查询执行从未完成，我不确定确切的计数。fac.flat_inventory_id is null

MySQL版本为8.0.28

将标准反转到 并不能提高性能。and fi.storage_id not in (...)and exists (select 1 from storages s where s.zone <> 'virtual' and s.category <> 'virtual' and s.id = fi.storage_id)

第一个查询的 explain analyze 结果如下：

-> Limit: 1 row(s)  (cost=3217330.41 rows=1) (actual time=15.897..15.897 rows=1 loops=1)
    -> Nested loop inner join  (cost=3217330.41 rows=82301) (actual time=15.896..15.896 rows=1 loops=1)
        -> Filter: ((niyoweb.fi.unit_cost = 0.000000) and (niyoweb.fi.`status` not in ('lost','stolen','audit_lost','damaged','expired','invalid','audit_bad','deleted','audit_loss','cancelled','0','null')) and <in_optimizer>(niyoweb.fi.storage_id,niyoweb.fi.storage_id in (select #2) is false) and (niyoweb.fi.product_id is not null))  (cost=2958055.57 rows=202636) (actual time=15.826..15.826 rows=1 loops=1)
            -> Index lookup on fi using flat_inventories_source_type_source_id_index (source_type='audit_item_action'), with index condition: (niyoweb.fi.warehouse_id > 1)  (cost=2958055.57 rows=6405844) (actual time=0.376..2.500 rows=560 loops=1)
            -> Select #2 (subquery in condition; run only once)
                -> Filter: ((niyoweb.fi.storage_id = `<materialized_subquery>`.id))  (cost=3041.61..3041.61 rows=4497) (actual time=6.629..6.629 rows=0 loops=2)
                    -> Limit: 1 row(s)  (actual time=6.629..6.629 rows=0 loops=2)
                        -> Index lookup on <materialized_subquery> using <auto_distinct_key> (id=niyoweb.fi.storage_id)  (actual time=0.002..0.002 rows=0 loops=2)
                            -> Materialize with deduplication  (cost=3041.61..3041.61 rows=4497) (actual time=13.257..13.257 rows=11 loops=1)
                                -> Filter: ((niyoweb.s.`zone` = 'virtual') or (niyoweb.s.category = 'virtual'))  (cost=2591.90 rows=4497) (actual time=3.520..13.236 rows=11 loops=1)
                                    -> Table scan on s  (cost=2591.90 rows=23669) (actual time=0.030..8.437 rows=24704 loops=1)
        -> Filter: (niyoweb.fi.id between fcm.min_fi and fcm.max_fi)  (cost=0.91 rows=0) (actual time=0.068..0.068 rows=1 loops=1)
            -> Index lookup on fcm using fi_cogs_map_product_id_index (product_id=niyoweb.fi.product_id)  (cost=0.91 rows=4) (actual time=0.032..0.066 rows=7 loops=1)

第二个查询的 explain analyze 结果如下：

-> Limit: 1 row(s)  (cost=3307894.65 rows=1) (actual time=615.728..615.728 rows=1 loops=1)
    -> Filter: (fac.flat_inventory_id is null)  (cost=3307894.65 rows=82496) (actual time=615.727..615.727 rows=1 loops=1)
        -> Nested loop antijoin  (cost=3307894.65 rows=82496) (actual time=615.726..615.726 rows=1 loops=1)
            -> Nested loop inner join  (cost=3217148.99 rows=82496) (actual time=2.605..577.482 rows=14250 loops=1)
                -> Filter: ((niyoweb.fi.unit_cost = 0.000000) and (niyoweb.fi.`status` not in ('lost','stolen','audit_lost','damaged','expired','invalid','audit_bad','deleted','audit_loss','cancelled','0','null')) and (niyoweb.fi.product_id is not null))  (cost=2957260.45 rows=203115) (actual time=2.549..71.447 rows=14268 loops=1)
                    -> Index lookup on fi using flat_inventories_source_type_source_id_index (source_type='audit_item_action'), with index condition: (niyoweb.fi.warehouse_id > 1)  (cost=2957260.45 rows=6405844) (actual time=0.367..64.468 rows=15434 loops=1)
                -> Filter: (niyoweb.fi.id between fcm.min_fi and fcm.max_fi)  (cost=0.91 rows=0) (actual time=0.035..0.035 rows=1 loops=14268)
                    -> Index lookup on fcm using fi_cogs_map_product_id_index (product_id=niyoweb.fi.product_id)  (cost=0.91 rows=4) (actual time=0.004..0.034 rows=11 loops=14268)
            -> Single-row covering index lookup on fac using PRIMARY (flat_inventory_id=niyoweb.fi.id)  (cost=1.00 rows=1) (actual time=0.003..0.003 rows=1 loops=14250)

更改为不会运行得更好。left join fi_assisted_costs fac on fac.flat_inventory_id = fi.idand not exists (select 1 from fi_assisted_costs fac where fac.flat_inventory_id = fi.id)

当前的 tmp 表值分别为 16777216 （16 MB） 和 1000000000 （1 GB）。SELECT @@max_heap_table_size, @@tmp_table_size;

源类型实际上是“audit_item_action”、“grn_item”、“stock_transfer_item”、“return_item”等。我在原来的问题中更改了它们，使它们更短。这是为了找出最小 id，因为执行需要更多时间。limit 1min(fi.id)

SQL MySQL 性能 where-clause 查询优化

评论

0赞 Abhishek Halder 10/12/2023
@user1191247附在原帖中
0赞 Abhishek Halder 10/12/2023
是的。。。我的错误...这是一个错别字（这里，不在实际查询中）
0赞 Abhishek Halder 10/12/2023
flat_inventory_id （） 上有三个键，没有主键。现在修复它。更新后的 DDL 已添加到原始帖子中。但是，查询性能没有提高。
0赞 Abhishek Halder 10/12/2023
@user1191247在问题正文中附加了所有答案
0赞 Abhishek Halder 10/13/2023
@user1191247更改查询并没有使它更快。当前的 tmp 表值分别为 16777216 （16 MB） 和 1000000000 （1 GB）。源类型实际上是“audit_item_action”、“grn_item”、“stock_transfer_item”等。我在原来的问题中更改了它们，使它们更短。我已经在问题中附加了第一个查询的解释分析结果。限制 1 是找出最小 id，因为 min（fi.id） 需要更多时间来执行。

答：

0赞 Rick James 10/13/2023 #1

  • 这些索引可能会有所帮助：

    fi:  INDEX(source_type, unit_cost)
fcm:  INDEX(product_id,  min_fi, max_fi)  -- replacing (product_id)

  • 我认为与（没有空测试）相同。改成那个;优化器可能能够做得更好。LEFT JOIN ... IS NOT NULLJOIN

  • 这是无用的，而且浪费时间;删除它：

    left join  fi_assisted_costs fac  ON fac.flat_inventory_id = fi.id
  [where]  fcm.product_id is not null

  • 没有 an 的 a 没有意义。查询会给出一些随机结果行;可以吗？LIMITORDER BY

  • 这

      left join  fi_cogs_map fcm  ON fcm.product_id = fi.product_id
    and  fi.id between fcm.min_fi AND fcm.max_fi

    可能更好地表达为

      [where]
      EXISTS( SELECT 1 
        FROM fi_cogs_map fcm
        WHERE fcm.product_id = fi.product_id
          and  fi.id between fcm.min_fi AND fcm.max_fi )

  • 这很难优化：

      fi.id between fcm.min_fi AND fcm.max_fi

    IP 查找可能会提供更好的想法。

  • PRIMARY KEY (id, warehouse_id)是不寻常的。这背后的逻辑是什么？

在做出这些改变之后，我可能会回来再次尝试帮助你。

评论

1赞 Rick James 10/13/2023
@user1191247 - 感谢您指出这一点。当我洗牌项目时，我把那部分放错了地方。
0赞 user1191247 10/13/2023
我想你的意思是 ，而且它非常有选择性，将 ~6.4M 降至 < 100。[where] fac.flat_inventory_id is null

上一个：如何在 SQL Server 中的 where 子句中使用别名列名

下一个：Snowflake 扁平化 json SQL