时间戳

首页 > 解决方案 > 优化运行缓慢的 3 个表的 mysql 连接

mysql - 优化运行缓慢的 3 个表的 mysql 连接

问题描述

我继承了一个广告 CRM 应用程序，并且一直难以解决几个缓慢的问题。问题之一是 3 个表的连接查询运行缓慢：

订单包含产品，而产品又包含订单广告。Products 表充当 2 之间的中介。

ads_campaign（订单）- 50,000 行（过去 1 年 12,000 行）

CREATE TABLE `advertiser_campaign` (
   `Id` INT(10,0) NOT NULL AUTO_INCREMENT,
   `BusinessName` VARCHAR(100) NOT NULL COLLATE 'utf8_general_ci',
   `CreatedAt` DATETIME NOT NULL,
   `account_id` INT(10,0) NULL DEFAULT NULL,
   `is_executed` TINYINT(1) NOT NULL DEFAULT '0',
   `order_status` INT(10,0) NULL DEFAULT NULL,
   `created_by` INT(10,0) NULL DEFAULT NULL,
   PRIMARY KEY (`Id`) USING BTREE,
   INDEX `FK_AvertiserCampaign_AccountId` (`account_id`) USING BTREE,
   INDEX `CreatedAt` (`CreatedAt`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=53385;

报纸产品投标（产品） - 56,000 行（过去 1 年 13,000 行）

CREATE TABLE `newspaper_product_bid` (
`Id` INT(10,0) NOT NULL AUTO_INCREMENT,
`AdvertiserCampaignId` INT(10,0) NOT NULL,
`Status` INT(10,0) NOT NULL DEFAULT '10',
`CreatedAt` DATETIME NOT NULL,
PRIMARY KEY (`Id`) USING BTREE,
INDEX `FK_NewspaperProductBid_AdvertiserCampaign_idx` (`AdvertiserCampaignId`) USING BTREE,
CONSTRAINT `FK_NewspaperProductBid_AdvertiserCampaign` FOREIGN KEY (`AdvertiserCampaignId`) REFERENCES `iihssp_prod_20210820`.`advertiser_campaign` (`Id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=59246;

insert_usage (ads) - 82,000 行（过去 1 年 18,000 行）

CREATE TABLE `insert_usage` (
`insert_usage_id` INT(10,0) NOT NULL AUTO_INCREMENT,
`publish_date` DATE NULL DEFAULT NULL,
`published` TINYINT(1) NULL DEFAULT NULL COMMENT 'Was this specific ad published',
`bid_id` INT(10,0) NULL DEFAULT NULL,
`ad_status` INT(10,0) NULL DEFAULT NULL,
PRIMARY KEY (`insert_usage_id`) USING BTREE,
INDEX `newspaper_product_bid.id` (`bid_id`) USING BTREE,
INDEX `publish_date` (`publish_date`) USING BTREE,
INDEX `ix_ad_status` (`ad_status`) USING BTREE,
INDEX `published` (`published`) USING BTREE,
---------------------------------------
-- Add this index didn't help either
---------------------------------------
-- INDEX `bid_id_ad_status` (`bid_id`, `ad_status`) USING BTREE,
CONSTRAINT `newspaper_product_bid.id` FOREIGN KEY (`bid_id`) REFERENCES `iihssp_prod_20210820`.`newspaper_product_bid` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=100821;

这是我用于获取销售数据的简化连接语句（更大存储过程的一部分）：

SELECT 
    DATE_FORMAT(iu.publish_date, '%m-%d') AS CurrDate,
    ROUND(ifnull(sum(ifnull(iu.ad_price, 0)),0),2) as Printed
FROM insert_usage iu
JOIN newspaper_product_bid as npb on iu.bid_id = npb.id 
     -- The cuplrit (slows down query by 200%)
     AND(iu.ad_status != 7 || iu.ad_status is null) AND npb.`Status` != -10
JOIN advertiser_campaign ac on ac.Id = npb.AdvertiserCampaignId
WHERE iu.published=1 
AND DATE(iu.publish_date) BETWEEN  DATE_FORMAT(QtrStartDt, '%Y-%m-01') AND CurrentDt
GROUP BY DATE_FORMAT(iu.publish_date, '%m-%d')

我已经在连接中涉及的所有列上都有索引。

解释查询给出了这个： 解释结果

当我删除对 iu.ad_status 和 npb.status 的检查时，查询运行速度很快并使用了正确的索引。 删除检查结果

如何提高查询速度？

标签： mysql

解决方案

没有必要 DATE(iu.publish_date)；您可以简单地iu.publish_date在这种情况下使用。当在函数调用（甚至）中隐藏一列时，DATE()它变得不可“sargeable”并且优化器看不到它。

更改后，此索引可能会有所帮助iu： Drop INDEX published (published) USING BTREE and Add

INDEX(published, publish_date)

advertiser_campaign好像没用过；考虑删除JOIN它。

为了可读性，请ON用于定义表格的关联方式；用于WHERE过滤。也就是说，将其移至WHERE：

 AND(iu.ad_status != 7 || iu.ad_status is null) AND npb.`Status` != -10

（为与“ AND”保持一致，请使用OR代替||。）

CurrentDt这种“简化”似乎缺少。也许它只是简单的NOW()？

我猜想这些更改会说服优化器使用我建议的索引而不是您显示的任何一个查询计划。然后，如果它有足够的选择性，它将运行得更快。

推荐阅读