MySQL Insert...Select+ON DUPLICATE KEY UPDATE全表扫描问题求助

问题根源 #

你的查询出现全表扫描的核心原因是索引覆盖场景的差异：

• 当仅查询product_sku和sale_price时，ix_product_id_country二级索引会自动包含InnoDB主键列（product_sku, country_code），因此查询可以直接从索引中获取所有需要的数据（索引覆盖扫描），无需回表，优化器会选择走索引。
• 当查询加入大量常量字段后，优化器可能误判执行成本，认为全表扫描代价更低；或者统计信息过时，导致优化器做出错误选择。

具体优化建议 #

1. 强制指定索引（快速应急） #

直接在查询中强制使用目标索引，跳过优化器的错误判断：

insert into table_first(email, sku_id, product_id, country_code,
           event_name, event_time, price)
  select  'abc@gmail.com', pa.product_sku, '111', 'IN', 'test',
          '2022-11-18 03:51:18', pa.sale_price
    from  table_second pa FORCE INDEX (ix_product_id_country)
    where  pa.product_id = '111'
      and  pa.country_code = 'IN'
ON DUPLICATE KEY  UPDATE
      event_name=VALUES(event_name),
      price=VALUES(price);

注：已移除不必要的product_id更新，因为插入值与过滤条件一致，重复更新无意义

2. 创建覆盖索引（根本解决） #

构建包含过滤字段和查询所需字段的覆盖索引，让查询完全无需回表，优化器必然选择索引扫描：

ALTER TABLE table_second ADD INDEX ix_product_id_country_sale_price (product_id, country_code, sale_price);

这个索引包含了WHERE条件的过滤列，以及查询需要的sale_price，加上InnoDB二级索引自带的主键列product_sku，整个查询的所有字段都能从索引中直接获取。

3. 更新表统计信息 #

如果优化器误判是因为统计信息过时，执行以下命令更新统计数据：

ANALYZE TABLE table_second;

4. 统一字符集（消除潜在风险） #

table_first使用utf8，table_second使用utf8mb4，虽然当前查询无跨表关联，但后续操作可能因字符集不一致引发隐式转换，导致索引失效。建议统一字符集：

ALTER TABLE table_first CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

内容的提问来源于stack exchange，提问作者thakur babban