如何使用MYSQLJOIN結構？以及如何優化查詢時間-有解無憂

我有一些我無法解決的 MYSQL 資料庫問題。

我在下面的查詢花費了太多時間并使系統掛起。我正在嘗試使用“JOIN”構造來開發它。但是這次我嘗試使用“SUM”進行聚合，將查詢減少到一行。用“JOIN”做這個作業可以嗎？或者我應該如何改進這個查詢。
該資料庫與 ASP .NET 應用程式中的總共 22 個客戶端設備一起作業。正如我上面提到的，在查詢時間較長的情況下，當客戶端設備同時向資料庫發送查詢時，客戶端設備會凍結。我不明白為什么瀏覽器應用程式中的查詢會讓所有設備等待。在 MYSQL 中不是每個查詢都作為單獨的“執行緒”處理嗎？那么如果一個查詢的回傳時間為 10 秒，那么所有客戶端會等待 10 秒等待瀏覽器中回答該查詢嗎？

SELECT *,
(SELECT MachModel FROM machine WHERE MachCode=workorder.MachCode) AS MachModel,  
(SELECT RawMaterialDescription FROM rawmaterials WHERE RawMaterialCode=workorder.ProductRawMaterial) AS RawMaterialDescr, 
(SELECT RawMaterialColor FROM rawmaterials WHERE RawMaterialCode=workorder.ProductRawMaterial) AS RawMaterialColor,
(SELECT StaffName FROM staff WHERE AccountName=workorder.AssignStaff) AS AssignStaffName, 
(SELECT StaffCode FROM staff WHERE AccountName=workorder.AssignStaff) AS AssignStaffCode,
(SELECT MachStatus FROM machine WHERE MachCode=workorder.MachCode) AS MachStatus,
(SELECT SUM(xStopTime) FROM workorderb WHERE xWoNumber=workorder.WoNumber) AS WoTotalStopTime 
FROM workorder 
WHERE WoStatus=3 
ORDER BY PlanProdStartDate DESC, WoSortNumber, WoNumber LIMIT 100

SELECT workorder.*,machine.MachModel,machine.MachStatus,rawmaterials.RawMaterialDescription,rawmaterials.RawMaterialColor,staff.StaffName,staff.StaffCode,SUM(workorderb.xStopTime) 
FROM workorder  
LEFT JOIN machine ON machine.MachCode=workorder.MachCode  
LEFT JOIN rawmaterials ON rawmaterials.RawMaterialCode=workorder.ProductRawMaterial 
LEFT JOIN staff ON staff.AccountName=workorder.AssignStaff  
LEFT JOIN workorderb ON workorderb.xWoNumber=workorder.WoNumber 
WHERE workorder.WoStatus=3 
ORDER BY workorder.PlanProdStartDate DESC, workorder.WoSortNumber, workorder.WoNumber LIMIT 100

CREATE TABLE `workorder` (
  `WoNumber` varchar(20) NOT NULL,
  `MachCode` varchar(15) NOT NULL,
  `PlannedMoldCode` varchar(10) NOT NULL,
  `PartyNumber` smallint(6) NOT NULL,
  `PlanProdCycleTime` smallint(6) NOT NULL,
  `CalAverageCycleTime` float(15,1) unsigned NOT NULL,
  `ProductRawMaterial` varchar(30) NOT NULL,
  `PlanProdStartDate` date NOT NULL,
  `PlanProdFinishDate` int(10) unsigned NOT NULL,
  `WoStartDate` datetime DEFAULT NULL,
  `WoFinishDate` datetime DEFAULT NULL,
  `WoWorkTime` int(10) unsigned NOT NULL,
  `WoSystemProductivity` smallint(6) unsigned NOT NULL,
  `AssignStaff` varchar(50) DEFAULT '',
  `WoStatus` smallint(6) NOT NULL,
  `WoSortNumber` int(10) unsigned NOT NULL,
  `CycleCount` int(11) unsigned NOT NULL,
  `ControlDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `WoProductionStatus` smallint(6) NOT NULL DEFAULT '0',
  `Creator` varchar(50) NOT NULL,
  `Changer` varchar(50) NOT NULL,
  `CreateDate` datetime NOT NULL,
  PRIMARY KEY (`WoNumber`) USING BTREE,
  KEY `WoNumber` (`WoNumber`) USING BTREE,
  KEY `WoNumber_2` (`WoNumber`) USING BTREE,
  KEY `WoNumber_3` (`WoNumber`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

CREATE TABLE `machine` (
  `MachCode` varchar(15) NOT NULL,
  `MachModel` varchar(30) NOT NULL,
  `FirstProdDate` date NOT NULL,
  `MachCapacity` smallint(6) NOT NULL,
  `MachStatus` smallint(6) NOT NULL,
  `NowMoldOnMach` varchar(10) NOT NULL DEFAULT '',
  `NowMachOperator` varchar(50) NOT NULL DEFAULT '',
  `NowWorkOrder` varchar(20) NOT NULL DEFAULT '',
  `IPNumber` varchar(15) NOT NULL,
  `Creator` varchar(50) NOT NULL,
  `Changer` varchar(50) NOT NULL,
  `ControlDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `OperatorLoginDate` datetime DEFAULT NULL,
  `Message` varchar(500) DEFAULT NULL,
  `MessageReaded` smallint(6) DEFAULT '0',
  `StaffName` varchar(50) DEFAULT 'OSIS',
  `StaffImage` varchar(255) DEFAULT '',
  `StopDesc` varchar(30) DEFAULT 'OSIS',
  `StopTime` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`MachCode`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

CREATE TABLE `rawmaterials` (
  `RawMaterialCode` varchar(15) NOT NULL,
  `RawMaterialDescription` varchar(30) NOT NULL,
  `RawMaterialColor` varchar(30) NOT NULL,
  PRIMARY KEY (`RawMaterialCode`) USING BTREE,
  KEY `RawMaterialCode` (`RawMaterialCode`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

CREATE TABLE `staff` (
  `StaffCode` varchar(15) DEFAULT NULL,
  `StaffCardCode` varchar(10) DEFAULT NULL,
  `StaffName` varchar(50) NOT NULL,
  `StaffPassword` varchar(10) NOT NULL,
  `StaffStatus` smallint(6) NOT NULL DEFAULT '2',
  `StaffDateOfStart` date NOT NULL,
  `StaffBirthDay` date DEFAULT NULL,
  `StaffGender` varchar(5) DEFAULT NULL,
  `StaffRoleA` smallint(6) NOT NULL,
  `StaffEmail` varchar(100) NOT NULL,
  `StaffImageLink` varchar(255) DEFAULT NULL,
  `AccountName` varchar(50) NOT NULL,
  `StaffRoleB` smallint(6) NOT NULL,
  `StaffRoleD` smallint(6) NOT NULL,
  `StaffRoleE` smallint(6) NOT NULL,
  `StaffRoleC` smallint(6) NOT NULL,
  `StaffRoleF` smallint(6) NOT NULL,
  `StaffRoleG` smallint(6) NOT NULL,
  `StaffRoleH` smallint(6) NOT NULL,
  `StaffRoleI` smallint(6) NOT NULL,
  `StaffRoleJ` smallint(6) NOT NULL,
  `StaffRoleK` smallint(6) NOT NULL,
  `StaffRoleL` smallint(6) NOT NULL,
  `StaffRoleM` smallint(6) NOT NULL,
  `StaffRoleN` smallint(6) NOT NULL,
  `StaffConnection` smallint(6) NOT NULL DEFAULT '2',
  `MachineWorked` varchar(15) DEFAULT NULL,
  `WorkOrderWorked` varchar(20) DEFAULT NULL,
  `StaffGroup` varchar(50) NOT NULL,
  `Creator` varchar(50) NOT NULL,
  `Changer` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`AccountName`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

CREATE TABLE `workorderb` (
  `xWoNumber` varchar(20) NOT NULL,
  `xMachCode` varchar(15) NOT NULL,
  `xPlannedMoldCode` varchar(10) NOT NULL,
  `xPartyNumber` smallint(6) NOT NULL,
  `xStaffName` varchar(50) NOT NULL,
  `xStopCode` smallint(6) NOT NULL,
  `xStopStartTime` datetime NOT NULL,
  `xStopFinishTime` datetime DEFAULT NULL,
  `xStopTime` int(11) DEFAULT NULL,
  PRIMARY KEY (`xMachCode`,`xStopStartTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

uj5u.com熱心網友回復：

您對連接的查詢幾乎就在那里：它只是缺少一個GROUP BY子句。
我已經替換workorder.*為workorder.WoNumberinSELECT并添加了GROUP BY workorder.WoNumber.
您可以從workorder中添加任意數量的列，SELECT但您必須在中列出它們GROUP BY。

SELECT workorder.WoNumber,machine.MachModel,machine.MachStatus,rawmaterials.RawMaterialDescription,rawmaterials.RawMaterialColor,staff.StaffName,staff.StaffCode,SUM(workorderb.xStopTime) 
FROM workorder  
LEFT JOIN machine ON machine.MachCode=workorder.MachCode  
LEFT JOIN rawmaterials ON rawmaterials.RawMaterialCode=workorder.ProductRawMaterial 
LEFT JOIN staff ON staff.AccountName=workorder.AssignStaff  
LEFT JOIN workorderb ON workorderb.xWoNumber=workorder.WoNumber 
WHERE workorder.WoStatus=3 
GROUP BY workorder.WoNumber  \* <<= ADD OTHER COLUMNS HERE AS NEEDED *\
ORDER BY workorder.PlanProdStartDate DESC, workorder.WoSortNumber, workorder.WoNumber LIMIT 100;

db<>在這里擺弄

uj5u.com熱心網友回復：

使用 InnoDB，而不是 MyISAM。 MyISAM 在以下情況下鎖定整個表INSERTing；InnoDB 在插入時通常可以允許其他執行緒運行。

其他注意事項

workorder上有 4 個相同的索引wonumber；保持PK，其余折騰。注意 aPRIMARY KEY是一個索引。檢查其他表是否有冗余鍵。

你需要DESC和ASC的混合物ORDER BY PlanProdStartDate DESC, WoSortNumber, WoNumber嗎？如果沒有，這里可能有優化。

正如 Kendle 所建議的那樣，JOINs會更快，因為在某些情況下同一張表需要兩次。如果值可能丟失，那么LEFT可能有用；它不會改變性能。

需要：

workorderb: INDEX(xWoNumber, xStopTime)

是xStopTime經過的時間嗎？還是一天中的某個時間？

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/449603.html

標籤：mysql 加入

上一篇：回傳IEnumerable<Object>而不是IEnumerable<IEnumerable><Object>

下一篇：大熊貓加入組中的字串，跳過na值