Mysql

無法弄清楚為什麼我的查詢如此緩慢

  • January 8, 2019

我有一個查詢需要 15 秒才能在 MySQL 5.6 伺服器中獲得 350 個結果，但我無法診斷原因，我對數據庫優化仍然很陌生。ü

EXPLAIN 視覺確實顯示了一些非唯一的鍵查找這裡，但每個僅表示一個 1 行查找。

我無法解釋的表格解釋，我希望其他人可以在這裡看起來像這。

我嘗試將結尾切換為 100、10LIMIT = 350和 1，並且查詢的執行時間完全相同，大約 15 秒。

我曾嘗試取消視圖，但除了使重新創建此查詢變得困難之外，它並沒有提高性能。

也許相關，在我們的 MySQL 數據庫中的其他 EXPLAIN 語句中，我看到了一個在它旁邊引用了 Materialized 的視圖，但它並沒有出現在這個查詢中使用的三個視圖中的任何一個旁邊，事實上我什至沒有查看完全引用的視圖，而不是僅查看它們引用的表。這是一個因素嗎？

我的最後一次嘗試是用listlineitems.*特定的列替換最終選定的列，因為我讀過這可以提高速度並且只是更好的做法，但我覺得不會顯著改善這種情況。

這是查詢 -

SELECT 
0 AS 'Check',
DATE_FORMAT(`listlineitems`.`dateEntered`,
       '%Y-%m-%d') AS 'Date Entered',
`listlineitems`.`itemId` AS 'parentTableIdx',
`listlineitems`.`parentProjectId` AS 'parentProjectIdx',
`listlineitems`.`idx` AS 'ID',
IF(`listlineitems`.`active` = 1,
   'Active',
   'Inactive') AS 'Active/Inactive',
CONCAT(`listUsers`.`FirstName`,
       ' ',
       `listUsers`.`LastName`) AS 'Employee',
CASE `listlineitems`.`type`
   WHEN 1 THEN 'Time Entry'
   WHEN 2 THEN 'Expense Entry'
END AS 'Type',
`listcustomers`.`name` AS 'Customer',
`listlocations`.`name` AS 'Location',
`listareas`.`name` AS 'Area',
`listassets`.`name` AS 'Asset',
`listprojects`.`name` AS 'Project',
`listprojects`.`number` 'Project #',
`listprojects`.`autoassign` 'autoassign',
`listactivities`.`name` AS 'Activity',
(CASE `listlineitems`.`type`
   WHEN 1 THEN `listlineitems`.`qty`
   WHEN 2 THEN `listlineitems`.`qty`
END) AS 'Quantity',
`listlineitems`.`taxable` AS 'Taxable',
`listlineitems`.`totalAmount` - `listlineitems`.`taxAmount` AS 'Pre-Tax Amount',
`listlineitems`.`taxAmount` AS 'Tax Amount',
`listlineitems`.`totalAmount` AS 'Total Amount',
`listCustomers`.`idx` AS 'parentCustomerIdx',
`listLocations`.`idx` AS 'parentLocationIdx',
`listAreas`.`idx` AS 'parentAreaIdx',
`listAssets`.`idx` AS 'parentAssetIdx',
CONCAT(`listcustomers`.`name`,
       '/',
       `listlocations`.`name`,
       '/',
       `listareas`.`name`,
       '/',
       `listassets`.`name`,
       '/',
       `listprojects`.`name`) AS 'Path',
IF(`listlineitems`.`customerViewable` = 1,
   'Yes',
   'No') AS 'Cust. Viewable',
(CASE
   WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TotalCostToPSI` - `listexpenseentry`.`TaxCostToPSI`
   ELSE `listlineitems`.`totalAmount` - `listlineitems`.`taxAmount`
END) AS 'preTaxCostPSI',
(CASE
   WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TaxCostToPSI`
   ELSE `listlineitems`.`taxAmount`
END) AS 'taxCostPSI',
(CASE
   WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TotalCostToPSI`
   ELSE `listlineitems`.`totalAmount`
END) AS 'totalCostPSI',
view_solinx2.lastAltered AS 'lastalteredSO',
view_polinx2.lastAlteredPO AS 'lastalteredPO',
view_invlinx2.lastAlteredInv AS 'lastalteredInv',
view_solinx2.lastAlteredAfterConfirmation AS 'lastAlteredAfterConfirmation',
view_solinx2.roleIdSO AS 'roleIdSO',
view_polinx2.roleIdPO AS 'roleIdPO',
view_polinx2.userIdPO AS 'userIdPO',
view_polinx2.lastAlteredafterConfirmation AS 'lastAlteredAfterConfirmationPO',
view_invlinx2.roleIdInv AS 'roleIdInv',
view_invlinx2.userIdInv AS 'userIdInv',
view_invlinx2.lastAlteredafterConfirmation AS 'lastAlteredAfterConfirmationInv',
view_solinx2.roleId AS 'roleId',
view_solinx2.userId AS 'userId',
view_solinx2.soId AS 'SOId',
view_solinx2.autoassignSO AS 'autoassignSO',
IF(view_solinx2.notNeeded = 1,
   'Not Needed',
   view_solinx2.number) AS 'SOname',
view_solinx2.dateEntered AS 'SoDate',
view_solinx2.totalSOAmount AS 'SoTotal',
view_invlinx2.invId AS 'InvId',
IF(view_solinx2.notNeeded = 1,
   '------',
   view_invlinx2.`number`) AS 'InvName',
view_invlinx2.dateEntered AS 'InvDate',
view_invlinx2.amount AS 'InvTotal',
view_polinx2.poId AS 'POId',
IF(view_solinx2.notNeeded = 1,
   '------',
   view_polinx2.`number`) AS 'POName',
view_polinx2.dateEntered AS 'PODate',
view_polinx2.amount AS 'POTotal',
(SELECT 
       listsalesorders.number
   FROM
       listsalesorders
   WHERE
       listsalesorders.idx = autoassign) AS 'test',
`listlineitems`.*
FROM
`listlineitems`
   LEFT JOIN
`listUsers` ON `listlineitems`.`individualId` = `listUsers`.`idx`
   LEFT JOIN
`listprojects` ON `listlineitems`.`parentProjectId` = `listprojects`.`idx`
   LEFT JOIN
`listassets` ON `listlineitems`.`parentAssetId` = `listassets`.`idx`
   LEFT JOIN
`listareas` ON `listlineitems`.`parentAreaId` = `listareas`.`idx`
   LEFT JOIN
`listlocations` ON `listlineitems`.`parentLocationId` = `listlocations`.`idx`
   LEFT JOIN
`listcustomers` ON `listlineitems`.`parentCustomerId` = `listcustomers`.`idx`
   LEFT JOIN
`listactivities` ON `listactivities`.`idx` = `listlineitems`.`activityCode`
   LEFT JOIN
`listexpenseentry` ON (`listexpenseentry`.`idx` = `listlineitems`.`itemId`
   AND `listlineitems`.`type` = 2)
   LEFT JOIN
view_solinx2 ON view_solinx2.idx = listlineitems.idx
   LEFT JOIN
view_polinx2 ON view_polinx2.idx = listlineitems.idx
   LEFT JOIN
view_invlinx2 ON view_invlinx2.idx = listlineitems.idx
GROUP BY `listlineitems`.`idx`
ORDER BY `listlineitems`.`dateEntered` DESC
LIMIT 10;

附加資訊：有客戶、位置、區域、資產、項目、dateEntered、itemId、lineitemtype 和其他一些我認為未使用的索引（它們是連接中未使用的列，如果我忽略索引為他們？）。

此外，如果知道有用的話，項目的客戶有他們自己的聯繫。項目有一個資產外鍵，資產有一個區域外鍵，區域有一個位置外鍵，位置有一個客戶外鍵，都是多對一的（一個客戶有很多位置，一個位置有很多區域等）。

非常感謝任何建議或攻擊計劃/要尋找的內容。有關數據庫結構的任何其他問題，請詢問。我已經為此工作了兩天，但沒有找到任何地方。

將評論變成答案……

首先：不同LIMIT的 s 幾乎沒有什麼變化是因為，所有收集和排序數據的艱苦工作都必須在應用限制之前完成。

為了找到資源消耗的來源：

  • 刪除ORDER BY(only) 和 (then)
  • LEFT從所有s中刪除JOIN（一次一個）。
  • 檢查效果。
  • （因為沒有使用聚合函式）考慮刪除GROUP BY（並可能使用 aWHERE代替）。

如果需要調整/進一步詳細資訊，請發表評論。

引用自：https://dba.stackexchange.com/questions/226601

相關問答

Mysql

MySQL選擇查詢不使用必需索引

  • December 20, 2019
檢視問答
Mysql

選擇具有來自其他表的值的動態列

  • May 31, 2017
檢視問答
Mysql

如何檢查和報告查詢時間

  • January 18, 2017
檢視問答
Mysql

優化表和查詢，按日期或日期-小時聚合

  • January 28, 2014
檢視問答
Mysql

更改 ORDER BY 方向會導致性能問題

  • July 1, 2013
檢視問答
Mysql

MySQL 能否在 10GB 網路儲存上使用 raid 與本地 HDD 表現得更好？

  • May 16, 2022
檢視問答