如何提高View/Query的性能?

Question

我有一个Mysql视图，它运行大约9秒。我真的需要提高这个视图的性能。下面是我的代码:

SELECT
`tad`.`TRIP_ASSIGNMENT_DETAILS_ID` AS `TRIP_ASSIGNMENT_DETAILS_ID`,
`lvdu`.`USERNAME` AS `USERNAME`,
`cdm`.`DOMICILE_LOOK_UP_ID` AS `DOMICILE_LOOK_UP_ID`,
CAST(
GROUP_CONCAT(
DISTINCT `ptlm`.`LOAD_ID` SEPARATOR ','
) AS CHAR(255) CHARSET utf8mb4
) AS `LOAD_RELATED`,
MIN(`ptlm`.`PLANNED_START_DATETIME`) AS `PLANNED_START_DATETIME`
FROM
`Appian`.`LDA_TRIP_ASSIGNMENT_DETAILS` `tad`
LEFT JOIN `Appian`.`LDA_VIEW_DRIVER_AND_USER_DETAILS` `lvdu`
ON
(
`tad`.`DRIVER_DETAILS_ID` = `lvdu`.`DRIVER_DETAILS_ID`
)                    
LEFT JOIN `Appian`.`LDA_VIEW_CARRIER_DOMICILE_MAPPING` `cdm`
ON
(
`lvdu`.`CARRIER_LOOK_UP_ID` = `cdm`.`CARRIER_LOOK_UP_ID`
)
LEFT JOIN `Appian`.`LDA_POD_TRIP_LOAD_MAPPING` `ptlm`
ON
(
`tad`.`TRIP_ASSIGNMENT_DETAILS_ID` = `ptlm`.`TRIP_ASSIGNMENT_ID`
)
WHERE
`tad`.`TRIP_STATUS` IN('Force De-Assigned', 'Completed') AND `tad`.`TRIP_ASSIGNED_TIME` > CURRENT_TIMESTAMP() - INTERVAL 10 DAY
GROUP BY
`tad`.`TRIP_ASSIGNMENT_DETAILS_ID`
ORDER BY
`tad`.`TRIP_ASSIGNMENT_DETAILS_ID`
DESC

查看LDA_VIEW_DRIVER_AND_USER_DETAILS(1329行)andLDA_VIEW_CARRIER_DOMICILE_MAPPING(216行)有很好的性能，只需要0.01秒，但是一旦我运行这个查询，它需要大约9秒。

LDA_TRIP_ASSIGNMENT_DETAILS: only 19401 rows

LDA_POD_TRIP_LOAD_MAPPING: only 11056行

TRIP_ASSIGNMENT_DETAILS_ID是LDA_TRIP_ASSIGNMENT_DETAILS的主键。

LDA_POD_TRIP_LOAD_MAPPING表没有索引

请给我一些提示来解决这个问题，

我将非常感激!!!!

Answer 1

需要猜测才能提供关于优化该查询的明确建议。为什么?

1. 您还没有向我们展示表的定义。

2. 您还没有向我们展示视图定义，或者底层表的定义。

MySQL查询规划器将视图定义编译到每个查询中，然后找出满足它的最佳方法。请阅读这篇文章以获得更多关于如何组合一个好的查询优化问题的信息。

话虽如此，你的问题的这些部分引起了我的注意。

SELECT tad.TRIP_ASSIGNMENT_DETAILS_ID AS TRIP_ASSIGNMENT_DETAILS_ID,
....
ON tad.DRIVER_DETAILS_ID = lvdu.DRIVER_DETAILS_ID
...
WHERE tad.TRIP_STATUS IN('Force De-Assigned', 'Completed')
AND tad.TRIP_ASSIGNED_TIME > CURRENT_TIMESTAMP() - INTERVAL 10 DAY
...
ORDER BY tad.TRIP_ASSIGNMENT_DETAILS_ID DESC

如果你创建一个覆盖索引，它可以优化你的WHERE子句，并为你的SELECT和ON子句提供列数据。这可能有助于您的查询运行得更快。

这是我建议的索引

CREATE INDEX TAD_LOOKUP 
ON Appian.LDA_TRIP_ASSIGNMENT_DETAILS 
(TRIP_STATUS, 
TRIP_ASSIGNED_TIME, 
TRIP_ASSIGNMENT_DETAILS_ID DESC,
DRIVER_DETAILS_ID);

这被称为覆盖索引的，因为它包含满足查询所需的所有列。TRIP_STATUS是第一个，因为它在WHERE子句中显示为相等过滤器。TRIP_ASSIGNED_TIME是第二个，因为它是一个范围匹配。这意味着MySQL可以随机访问索引到第一个相关行，然后依次扫描索引以获得所需的数据。

其他两列在索引中只是因为您的查询需要它们的值，而不是因为它们是搜索的一部分。

出于类似的原因，另一个索引可能也会有所帮助。

CREATE INDEX PTLM_LOOKUP 
ON Appian.LDA_POD_TRIP_LOAD_MAPPING
(PTLM_TRIP_ASSIGNMENT_ID, LOAD_ID);

专业提示:至关重要的是，您和下一个处理您代码的人能够对您的查询进行推理。这意味着你花每一秒的时间去格式化它们是值得的。看一下上面我是如何格式化WHERE子句的，以便所有过滤谓词都出现在左边距附近。(我还省略了名字周围的反引号，因为它们让我很不舒服。但那只是我。)