如何优化MySQL Union All查询？索引未生效问题求助

问题背景 #

需要优化以下UNION ALL查询：WHERE子句的date字段已创建索引，但未被MySQL选用，尝试FORCE INDEX也无效果。

原查询 #

select 
  retailerCode, 
  coalesce(rules_GM, GM) as GM, 
  year, 
  taxableValue, 
  category, 
  date 
from 
  margin 
where 
  date not in (
    select 
      distinct date 
    from 
      margin_mis)
 AND date >= '2019-01-01'  
union all 
  
select 
  retailerCode, 
  coalesce(updated_GM, rules_GM) as GM, 
  year, 
  taxableValue, 
  category, 
  date 
from 
  margin_mis
WHERE date >= '2019-01-01';

执行计划 #

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: margin
   partitions: NULL
         type: ALL
possible_keys: idx_date_invoiceNumber,date
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5931014
     filtered: 50.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: margin_mis
   partitions: NULL
         type: index
possible_keys: idx_date_invoiceNumber,idx_date
          key: idx_date
      key_len: 4
          ref: NULL
         rows: 3756279
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: margin_mis
   partitions: NULL
         type: ALL
possible_keys: idx_date_invoiceNumber,idx_date
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3756279
     filtered: 50.00
        Extra: Using where
3 rows in set, 1 warning (0.02 sec)

表结构 #

margin表 #

CREATE TABLE `margin` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `Indexing` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `margin` double DEFAULT NULL,
  `COGS` double DEFAULT NULL,
  `GM` double DEFAULT NULL,
  `retailerCode` varchar(250) DEFAULT NULL,
  `status` varchar(250) DEFAULT NULL,
  `originalAdnMedicineId` int DEFAULT NULL,
  `orderTimeStamp` datetime DEFAULT NULL,
  `marginSign` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `rules_margin` double DEFAULT NULL,
  `rules_COGS` double DEFAULT NULL,
  `rules_GM` double DEFAULT NULL,
  `batchId` varchar(255) DEFAULT NULL,
  `orderType` varchar(255) DEFAULT NULL,
  `mappedBDRetailerCode` varchar(255) DEFAULT NULL,
  `grn_COGS` double DEFAULT NULL,
  `tax` int DEFAULT NULL,
  `orderRetrieveId` int DEFAULT NULL,
  `expected_COGS` double DEFAULT NULL,
  `grn_itemName` varchar(255) DEFAULT NULL,
  `lenderName` varchar(255) DEFAULT NULL,
  `grn_tax` int DEFAULT NULL,
  `expected_GM` double DEFAULT NULL,
  `grnId` int DEFAULT NULL,
  `billType` varchar(255) DEFAULT NULL,
  `grn_packaging` varchar(255) DEFAULT NULL,
  `grn_tcs` double DEFAULT NULL,
  `grn_rules_GM` double DEFAULT NULL,
  `grn_quantity` int DEFAULT NULL,
  `region` varchar(255) DEFAULT NULL,
  `hubCode` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_date_invoiceNumber` (`Date`,`invoiceNumber`),
  KEY `category` (`category`),
  KEY `Manufacturer` (`manufacturerName`),
  KEY `Dist` (`distributorId`),
  KEY `Req_hub_id` (`requestingHubId`),
  KEY `MediicneId` (`uniqueCode`),
  KEY `Ser_Hub_id` (`servicingHubId`),
  KEY `medicineCategory` (`medicineCategory`),
  KEY `rules_GM` (`rules_GM`),
  KEY `year` (`year`),
  KEY `month` (`month`),
  KEY `mapedBDRetailerCode` (`mappedBDRetailerCode`),
  KEY `date` (`Date`),
  KEY `retrieval_spoke_code` (`retrievalSpokeCode`),
  KEY `idx_mappedBDRetailerCode_month` (`mappedBDRetailerCode`,`month`),
  KEY `idx_BUYER_NET_VALUE_mappedBDRetailerCode` (`BUYER_NET_VALUE`,`mappedBDRetailerCode`),
  KEY `idx_BUYER_NET_VALUE_retailerCode_mappedBDRetailerCode` (`BUYER_NET_VALUE`,`retailerCode`,`mappedBDRetailerCode`),
  KEY `idx_com` (`month`,`year`)
) ENGINE=InnoDB AUTO_INCREMENT=8687695 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

margin_mis表 #

CREATE TABLE `margin_mis` (
  `_id` int unsigned NOT NULL AUTO_INCREMENT,
  `Indexing` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `margin` double DEFAULT NULL,
  `COGS` double DEFAULT NULL,
  `GM` double DEFAULT NULL,
  `manufacturerName` varchar(250) DEFAULT NULL,
  `medicineSubSectionType` varchar(250) DEFAULT NULL,
  `retailerCode` varchar(250) DEFAULT NULL,
  `status` varchar(250) DEFAULT NULL,
  `purchaseDetailsinvoiceNumber` varchar(255) DEFAULT NULL,
  `adnId` int DEFAULT NULL,
  `mappedBDRetailerCode` varchar(255) DEFAULT NULL,
  `orderRetrieveId` int DEFAULT NULL,
  `igst` int DEFAULT NULL,
  -- 省略其他字段
  `grn_margin` double DEFAULT NULL,
  `margin_cn_retail` double DEFAULT NULL,
  `margin_cn_carin` double DEFAULT NULL,
  `margin_cn_wholesale` double DEFAULT NULL,
  PRIMARY KEY (`_id`),
  KEY `idx_date_invoiceNumber` (`Date`,`invoiceNumber`),
  KEY `category` (`category`),
  KEY `Manufacturer` (`manufacturerName`),
  KEY `Dist` (`distributorId`),
  KEY `Req_hub_id` (`requestingHubId`),
  KEY `MediicneId` (`uniqueCode`),
  KEY `Ser_Hub_id` (`servicingHubId`),
  KEY `idx_date` (`Date`)
) ENGINE=InnoDB AUTO_INCREMENT=3908752 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

优化及改写方案 #

1. 改写NOT IN为LEFT JOIN + IS NULL #

MySQL对NOT IN子查询的优化通常不如LEFT JOIN，尤其是当子查询数据量较大时。改写后能更好地利用索引：

select 
  m.retailerCode, 
  coalesce(m.rules_GM, m.GM) as GM, 
  m.year, 
  m.taxableValue, 
  m.category, 
  m.date 
from 
  margin m
left join (
  select distinct date from margin_mis
) mm on m.date = mm.date
where 
  mm.date is null
  AND m.date >= '2019-01-01'  
union all 
select 
  retailerCode, 
  coalesce(updated_GM, rules_GM) as GM, 
  year, 
  taxableValue, 
  category, 
  date 
from 
  margin_mis
WHERE date >= '2019-01-01';

2. 创建覆盖索引 #

当前查询需要返回多个字段，如果创建包含查询所需字段的覆盖索引，MySQL会直接从索引中获取数据，避免全表扫描：

给margin表创建覆盖索引

CREATE INDEX idx_date_covering ON margin (`Date`, retailerCode, rules_GM, GM, year, taxableValue, category);

给margin_mis表创建覆盖索引

CREATE INDEX idx_date_mis_covering ON margin_mis (`Date`, retailerCode, updated_GM, rules_GM, year, taxableValue, category);

3. 统一字段大小写 #

注意到表结构中索引定义的是Date（首字母大写），而查询中使用的是date（小写）。在大小写敏感的文件系统（如Linux）中，MySQL会将其视为不同字段，导致索引无法匹配。统一改为大写或小写：

-- 修改查询中的字段名与索引一致
select 
  retailerCode, 
  coalesce(rules_GM, GM) as GM, 
  year, 
  taxableValue, 
  category, 
  Date 
from 
  margin 
where 
  Date not in (
    select 
      distinct Date 
    from 
      margin_mis)
 AND Date >= '2019-01-01'  
union all 
select 
  retailerCode, 
  coalesce(updated_GM, rules_GM) as GM, 
  year, 
  taxableValue, 
  category, 
  Date 
from 
  margin_mis
WHERE Date >= '2019-01-01';

4. 强制使用索引（针对UNION ALL的第二个查询） #

如果FORCE INDEX之前无效，尝试结合覆盖索引使用：

select 
  retailerCode, 
  coalesce(updated_GM, rules_GM) as GM, 
  year, 
  taxableValue, 
  category, 
  Date 
from 
  margin_mis FORCE INDEX(idx_date_mis_covering)
WHERE Date >= '2019-01-01';

验证优化效果 #

修改后重新执行EXPLAIN查看执行计划，确认type列是否变为range或ref，key列是否显示使用了对应的索引，rows列的预估行数是否大幅减少。

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