优化MySQL结构和查询

Question

问题发生在VPN上，而不是MySQL服务器的本地网络，但仍存在一个问题，即此查询太慢。 我将完整的查询和所有加入表格的布局都包含在内。

这个查询时间太长了，它确实返回了大量的数据集，但限制数据集似乎并没有帮助它。我已经尝试过根据日期范围使用WHERE和不使用WHERE。

它仍然需要8秒或更长时间才能返回。使用LIMIT也没有区别。

我担心每个表中可能有太多记录，这种情况下我的唯一选择就是创建一组“工作”表，其中包含最近一周的装运记录，然后将它们移动到一组“历史”表中，查询都会很慢。除非有人能找到一种使这个查询更快的方法。谢谢。

SELECT 
  ShipmentHeader.RecNbr,
  ShipmentHeader.Void,
  ShipmentHeader.SONum,
  ShipmentHeader.DateofReq,
  ShipmentHeader.ShipToName,
  ShipmentHeader.ShipToCity,
  ShipmentHeader.ShipToState,
  ShipmentHeader.ShipToZip,
  ShipmentHeader.ShipToCountry,
  Carrier.RecNbr AS CarrierRecNbr,
  CarrierService.RecNbr AS CarrierServiceRecNbr,
  ShipmentLabelsPrintLogView.Users 
FROM
  ShipmentHeader 
  INNER JOIN ShipmentLabels 
    ON ShipmentHeader.RecNbr = ShipmentLabels.HeaderRecNbr 
  LEFT JOIN VendorService 
    ON ShipmentLabels.VendorServiceRecNbr = VendorService.RecNbr 
  LEFT JOIN CarrierService 
    ON VendorService.CarrierServiceRecNbr = CarrierService.RecNbr 
  LEFT JOIN Carrier 
    ON CarrierService.CarrierRecNbr = Carrier.RecNbr 
  LEFT JOIN ShipmentLabelsPrintLogView 
    ON ShipmentLabels.RecNbr = ShipmentLabelsPrintLogView.ShipmentLabelsRecNbr 
ORDER BY  ShipmentHeader.RecNbr DESC

以下是表结构：

CREATE TABLE `ShipmentHeader` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `Void` varchar(1) NOT NULL DEFAULT 'N',
  `SONum` varchar(7) NOT NULL,
  `User` varchar(6) NOT NULL,
  `DateofReq` datetime NOT NULL,
  `ShipToName` varchar(255) DEFAULT NULL,
  `ShipToAddress1` varchar(255) DEFAULT NULL,
  `ShipToAddress2` varchar(255) DEFAULT NULL,
  `ShipToAddress3` varchar(255) DEFAULT NULL,
  `ShipToCity` varchar(255) DEFAULT NULL,
  `ShipToState` varchar(255) DEFAULT NULL,
  `ShipToZip` varchar(255) DEFAULT NULL,
  `ShipToCountry` varchar(255) DEFAULT NULL,
  `PackageHeaderRecNbr` int(11) DEFAULT NULL,
  `NegotiatedShipmentCharge` decimal(10,2) DEFAULT NULL,
  `PublishedShipmentCharge` decimal(10,2) DEFAULT NULL,
  `CustomerShipmentCharge` decimal(10,2) DEFAULT NULL,
  `VoidTimeStamp` datetime DEFAULT NULL,
  `VoidUser` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  KEY `SONum` (`SONum`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=539140 DEFAULT CHARSET=utf8



CREATE TABLE `ShipmentLabels` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `Whse` varchar(3) DEFAULT NULL,
  `HeaderRecNbr` int(11) NOT NULL,
  `PackageRecNbr` int(11) NOT NULL,
  `Carrier` varchar(60) NOT NULL DEFAULT '',
  `Service` varchar(60) DEFAULT NULL,
  `Charges` decimal(10,2) NOT NULL,
  `RatedCost` decimal(10,2) NOT NULL,
  `PublishedRate` decimal(10,2) NOT NULL,
  `CustomerCharge` decimal(10,2) NOT NULL,
  `Weight` int(11) NOT NULL,
  `LabelFormat` varchar(30) NOT NULL DEFAULT '',
  `ShipmentID` varchar(60) NOT NULL,
  `TrackingNumber` varchar(60) NOT NULL DEFAULT '',
  `Label` mediumtext NOT NULL COMMENT 'Base64 Encoded',
  `DateofLabel` datetime NOT NULL,
  `LabelSource` varchar(50) DEFAULT NULL,
  `VendorServiceRecNbr` int(11) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  KEY `HeaderRecNbr` (`HeaderRecNbr`) USING BTREE,
  KEY `PackageRecNbr` (`PackageRecNbr`) USING BTREE,
  CONSTRAINT `ShipmentLabels_ibfk_1` FOREIGN KEY (`HeaderRecNbr`) REFERENCES `ShipmentHeader` (`RecNbr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=542542 DEFAULT CHARSET=utf8

CREATE TABLE `VendorService` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `CarrierServiceRecNbr` int(11) DEFAULT NULL,
  `VendorRecNbr` int(11) DEFAULT NULL,
  `VendorServiceCode` varchar(255) DEFAULT NULL,
  `VendorServiceDesc` varchar(255) DEFAULT NULL,
  `PackageType` varchar(60) DEFAULT NULL,
  `LabelServiceCode` varchar(255) DEFAULT NULL,
  `LabelPackageType` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  UNIQUE KEY `Unique` (`CarrierServiceRecNbr`,`VendorRecNbr`,`PackageType`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `CarrierService` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `CarrierRecNbr` int(11) DEFAULT NULL,
  `CarrierServiceCode` varchar(50) DEFAULT NULL,
  `CarrierServiceDesc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  UNIQUE KEY `unique` (`CarrierRecNbr`,`CarrierServiceCode`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Carrier` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `CarrierName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  UNIQUE KEY `unique` (`CarrierName`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

我加入了这个我创建的视图...似乎问题就出在这里...但是没有这个视图，基于它的表格不符合我需要的数据格式。

CREATE ALGORITHM=UNDEFINED DEFINER=`atr`@`%` SQL SECURITY DEFINER VIEW `ShipmentLabelsPrintLogView` 
AS 
select `A`.`ShipmentLabelsRecNbr` AS `ShipmentLabelsRecNbr`,
  (
    select group_concat(distinct `ShipmentLabelsPrintLog`.`User` 
      order by `ShipmentLabelsPrintLog`.`User` ASC separator ', ') 
    from `ShipmentLabelsPrintLog` 
    where (`ShipmentLabelsPrintLog`.`ShipmentLabelsRecNbr` = `A`.`ShipmentLabelsRecNbr`)
  ) AS `Users` 
from `ShipmentLabelsPrintLog` `A` 
group by `A`.`ShipmentLabelsRecNbr`

视图所基于的表格在这里：

CREATE TABLE `ShipmentLabelsPrintLog` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `ShipmentLabelsRecNbr` int(11) NOT NULL,
  `User` varchar(10) NOT NULL,
  `Workstation` varchar(20) NOT NULL,
  `DateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`RecNbr`)
) ENGINE=InnoDB AUTO_INCREMENT=5908 DEFAULT CHARSET=utf8mb4;

这里是说明：

id  select_type         table                   type    possible_keys  key           key_len  ref                                          rows     Extra                            
1   PRIMARY             ShipmentHeader          index                  PRIMARY       4                                                     1979351                                   
1   PRIMARY             ShipmentLabels          ref     HeaderRecNbr   HeaderRecNbr  4        Shipping.ShipmentHeader.RecNbr               1                                         
1   PRIMARY             VendorService           eq_ref  PRIMARY        PRIMARY       4        Shipping.ShipmentLabels.VendorServiceRecNbr  1        Using where                      
1   PRIMARY             CarrierService          eq_ref  PRIMARY        PRIMARY       4        Shipping.VendorService.CarrierServiceRecNbr  1        Using where                      
1   PRIMARY             Carrier                 eq_ref  PRIMARY        PRIMARY       4        Shipping.CarrierService.CarrierRecNbr        1        Using where; Using index         
1   PRIMARY             <derived2>              ref     key0           key0          5        Shipping.ShipmentLabels.RecNbr               10       Using where                      
2   DERIVED             A                       ALL                                                                                        6576     Using temporary; Using filesort  
3   DEPENDENT SUBQUERY  ShipmentLabelsPrintLog  ALL                                                                                        6576     Using where                      

有没有一种更快地获得相同结果的方法？

谢谢！

- Yourguide

---

ShipmentLabels表中的行大小似乎不是问题；MySQL只访问HeaderRecNbr索引，甚至没有查看底层表中的数据页。我们知道这一点，因为解释输出显示“使用索引”，这是从ShipmentLabels引用的唯一列。解释输出还显示MySQL避免了潜在昂贵的“使用文件排序”操作，满足ORDER BY子句，按簇键顺序访问行。在我看来，大部分成本都在对ShipmentHeader表的全扫描中。 - spencer7593

你真的正在检索所有那些50万条记录吗？还是在你的查询中有一个额外的条件过滤记录？50万条记录是一个巨大的数据集，可能是几个GB的数据在网络上传输。我怀疑这实际上比执行查询本身需要更多的时间。 - Kate

由于每次只显示50个记录，建议使用LIMIT和OFFSET子句一次获取50个记录，并可能在用户界面中添加更多过滤器。例如，请参见：https://www.guru99.com/limit.html。由于数据集每天都在增长，响应时间只会随着时间的推移而增加。不要获取整个数据库，而是获取小块，然后您可以计算出一个索引以优化两个表之间的连接。 - Kate

还想补充一下，如果我加入这样的内容： WHERE ShipmentHeader.DateofReq BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() 即使它只返回约22k条记录，它仍然需要同样的时间。 - Yourguide

即使您减小了结果集的大小，MySQL仍然必须执行完整的表扫描以获取结果，除非它可以使用索引。即使在此字段上有索引，MySQL也无法使用它，因为它必须为每个记录评估curdate。例如，请参见：https://riptutorial.com/mysql/example/23472/using-an-index-for-a-date-and-time-lookup。 Explain命令将告诉您在处理查询时是否使用索引。 - Kate

Answer 1

ShipmentLabelsPrintLog 需要 INDEX(ShipmentLabelsRecNbr, User)

除非你确实需要它，否则不要使用 LEFT。在你的情况下，它似乎阻止了优化器从依赖子查询开始。

- Rick James