mysql - 优化MySQL结构和查询

Question

大大改变了问题，由于我在 VPN 上而不是 MySQL 服务器的本地网络，所以我犯了错误，但问题仍然是这个查询太慢。我的完整查询现已包含在内，并且我正在添加连接表的所有布局。

此查询花费的时间太长，它确实返回一个大数据集，但限制数据集似乎没有帮助。我已经尝试过使用和不使用基于日期范围的 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;

我正在加入我创建的这个 View ...这似乎就是问题所在...但是它所基于的表不是我在没有此 View 的情况下所需的数据格式。

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`

View 所基于的表位于此处:

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                      

有没有办法更快地获得相同的结果？

谢谢!

Answer 1

ShipmentLabelsPrintLog 需要 INDEX(ShipmentLabelsRecNbr, User)

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