Mysql查询未优化（union all查询）

huangapple
go评论66阅读模式
英文:

Mysql query not optimized (union all query)

问题

以下是您要求的代码部分的翻译：

这里我需要帮助，如何优化这个union all查询。表中对于where子句列有索引，但它没有使用它，我甚至尝试了使用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. 行 ***************************
           id: 1
  select_type: 主查询
        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: 使用where
*************************** 2. 行 ***************************
           id: 2
  select_type: 子查询
        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: 使用索引
*************************** 3. 行 ***************************
           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: 使用where
3 行在集合中，1 警告 (0.02 秒)


表结构：

表：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,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=8687695 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

*************************** 1. 行 ***************************
       表：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,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=3908752 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

英文:

here i need an help like how to optimize this union all query the table has index for where clause column but it's not picking it up and i even tried with force index none of that works any better way to optimize it,

Edited;

Query;

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

    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

If there are any rewrite options, kindly suggest them and share your thoughts here.

答案1

得分: 1

我不理解，为什么你一开始就有一个派生表，所以你可以摆脱它。

还有，你真的想要那么多索引吗，或者你添加了它们以覆盖所有可能性，所以我建议摆脱大部分，并在需要时添加一些组合索引。

选择
retailerCode，
coalesce（rules_GM，GM）作为GM，
年，
应税价值，
类别，
日期
来自
边际
在哪里
日期不在（
选择
不同的日期
来自
margin_mis）
并且日期> = '2019-01-01'
union all

选择
retailerCode，
coalesce（updated_GM，rules_GM）作为GM，
年，
应税价值，
类别，
日期
来自
margin_mis
其中日期> = '2019-01-01';

如果你有大量的数据，NOT IN 不够快，所以你可以尝试

选择
margin.retailerCode，
coalesce（margin.rules_GM，margin.GM）作为GM，
margin.year，
margin.taxableValue，
margin.category，
margin.date
来自
margin JOIN margin_mis ON marign.date <> margin_mis.date
在哪里
margin.date> = '2019-01-01'
union all

选择
retailerCode，
coalesce（updated_GM，rules_GM）作为GM，
年，
应税价值，
类别，
日期
来自
saveo_analytics.margin_mis
其中日期> = '2019-01-01'。

英文:

I can't understand, why you have a derived table in the first place, soyou can get rid of it.

also do you really want that many indexes in the first place, or have you added to cover all possibiities, so iwould recomend toget rid of the most, and add some combined ones where needed

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 &gt;= &#39;2019-01-01&#39;  
union all 
select 
retailerCode, 
coalesce(updated_GM, rules_GM) as GM, 
year, 
taxableValue, 
category, 
date 
from 
margin_mis
WHERE date &gt;= &#39;2019-01-01&#39;;

If you have lots of data, NOT IN is not that fast, so you can try

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 &lt;&gt; margin_mis.date
where 
margin.date &gt;= &#39;2019-01-01&#39;  
union all 
select 
retailerCode, 
coalesce(updated_GM, rules_GM) as GM, 
year, 
taxableValue, 
category, 
date 
from 
saveo_analytics.margin_mis
WHERE date &gt;= &#39;2019-01-01&#39;

答案2

得分: 1

不要在以下位置：

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

英文:

Instead of

date not in (
select 
distinct date 
from 
margin_mis)

do

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

What is the datatype of Date? (Hopefully it is DATE.)

It is usually better to extract year and month as needed, rather than having the as separate columns.

Whenever you have INDEX(a,b) do not also have INDEX(a)

huangapple
  • 本文由 发表于 2023年2月27日 02:23:09
  • 转载请务必保留本文链接：https://go.coder-hub.com/75574122.html
  • mysql
  • query-optimization
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定