解释使我的查询如此缓慢的原因有困难

Question

我有一个查询，需要15秒才能在MySQL 5.6Server中获得350个结果，而且我无法诊断为什么，我仍然是数据库优化的新手。使用

“解释”可视化显示了一些非唯一的键查找。

但每个人只说一排往上看。

我无法解释的表格解释，我希望其他人能在这里看起来像

。

我已经尝试将结束的LIMIT = 350切换到100，10，并且查询所需的时间完全相同，大约15秒。

我尝试过忽略视图，但是除了很难重新创建这个查询之外，它并没有提高性能。

也许是相关的，在我们的MySQL DB中的其他解释语句中，我看到了一个视图，它旁边有一个物化的视图，但在这个查询中使用的三个视图中的任何一个旁边，它都没有出现，事实上，我甚至根本没有看到引用的视图，而只是它们引用的表。这是一个因素吗？

我的最后一次尝试是用特定的列替换最后选定的列，这是一个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;

我不知道我还能做些什么来改善这一点，任何建议都是非常感谢的。

Answer 1

您正在选择列表项表(100+ K records)中的所有内容，加入许多表，然后按idx进行分组，然后丢弃大部分结果。

您可以：

• 尝试将唯一索引(dateEntered，idx)添加到列表项中
• 尝试通过dateEntered限制列表列项目(如果可以接受的话)(其中dateEntered >DATE_SUB(现在)，间隔30天)。必须对dateEntered进行索引
• 尝试将select从列表项+分组+限制放到子查询中，这样MySQL将只对子查询返回的10行进行连接。
• 将依赖子查询(listsalesorders)转换为左联接