Mysql查询未优化(union all查询)

z9gpfhce 于 2023-02-28 发布在 Mysql

关注(0)|答案(2)|浏览(155)

这里我需要一个帮助，比如如何优化这个union all查询，表有where子句列的索引，但是它没有拾取它，我甚至尝试了强制索引，没有一个更好的优化方法，
编辑;
质疑;

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';

explain plan;

*************************** 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)

表格结构;

Table: margin
Create Table: 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


*************************** 1. row ***************************
       Table: margin_mis
Create Table: 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

如果有任何重写选项，请在这里提出建议并分享您的想法。

mysql

来源：https://stackoverflow.com/questions/75574122/mysql-query-not-optimized-union-all-query

2条答案

按热度按时间

kninwzqo1#

我不明白，为什么一开始你要有一个派生表，这样你就可以摆脱它了。
另外，你真的想要那么多的索引摆在首位，或者你已经添加，以涵盖所有的可能性，所以我会建议摆脱最，并添加一些组合的需要

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';

如果您有大量数据，NOT IN并不那么快，因此您可以尝试

select 
  margin.retailerCode, 
  coalesce(margin.rules_GM, margin.GM) as GM, 
  margin.year, 
  margin.taxableValue, 
  margin.category, 
  margin.date 
from 
  margin JOIN margin_mis ON marign.date <> margin_mis.date
where 
   margin.date >= '2019-01-01'  
union all 
  
    select 
      retailerCode, 
      coalesce(updated_GM, rules_GM) as GM, 
      year, 
      taxableValue, 
      category, 
      date 
    from 
      saveo_analytics.margin_mis
  WHERE date >= '2019-01-01'

赞(0）回复(0）举报 2023-02-28

6ie5vjzr2#

代替

date not in (
    select 
      distinct date 
    from 
      margin_mis)

做

NOT EXISTS ( SELECT 1 FROM margin_mis
                 WHERE margin_mis.date = margin.date )

Date的数据类型是什么？（希望是DATE。）

通常 * 最好根据需要提取year和month，而不是将作为单独的列。

只要有INDEX(a,b)，就不要同时有INDEX(a)

赞(0）回复(0）举报 2023-02-28

我来回答

Mysql查询未优化(union all查询)

2条答案

相关问题

热门标签

最新问答