mysql - 我应该编写冗长的 SQL 查询还是分解为几个迭代

Question

我有一个非常冗长的 SQL 查询来获取预期的输出，但另一方面，我也可以通过使用多次迭代来生成预期的输出。

我应该使用哪一个？我关心性能和编写更好的代码。

• 通过使用长度 SQL 查询，生成输出大约需要 3000 毫秒
• 需要大约 4 ~ 5 次迭代来生成输出

查询/代码在做什么

此代码根据财政年度生成预测记录的总数，无论总数是否为 0。

使用长度 SQL 查询

SELECT
    CONCAT('FY\'', SUBSTR(`quarters`.fy, 3), ' Q', `quarters`.fy_quarter) AS name,
    (
        SELECT
            COUNT(*) 
        FROM
            member_project_stages 
        WHERE
            YEAR ( member_project_stages.start_at ) = `quarters`.fy 
            AND QUARTER ( member_project_stages.start_at ) = `quarters`.fy_quarter 
            AND member_project_stages.stage_id = 9 
    ) AS actual,
    (
    SELECT
        COUNT(*)
    FROM
        projects AS a 
    WHERE
        ( a.forecast IS NOT NULL AND a.forecast > '' ) 
        AND a.forecast LIKE CONCAT( '%FY\'', SUBSTR( `quarters`.fy, 3 ), '%' ) 
        AND a.forecast LIKE CONCAT( '% Q', `quarters`.fy_quarter, '%' ) 
        AND a.deleted_at IS NULL 
    GROUP BY
        a.forecast 
    ) AS forecast 
FROM
    `member_project_stages`,
    (
    SELECT YEAR
        (
        DATE_ADD( CURDATE(), INTERVAL - 9 MONTH )) AS fy,
        QUARTER (
        DATE_ADD( CURDATE(), INTERVAL - 9 MONTH )) AS fy_quarter UNION
    SELECT YEAR
        (
        DATE_ADD( CURDATE(), INTERVAL - 6 MONTH )) AS fy,
        QUARTER (
        DATE_ADD( CURDATE(), INTERVAL - 6 MONTH )) AS fy_quarter UNION
    SELECT YEAR
        (
        DATE_ADD( CURDATE(), INTERVAL - 3 MONTH )) AS fy,
        QUARTER (
        DATE_ADD( CURDATE(), INTERVAL - 3 MONTH )) AS fy_quarter UNION
    SELECT YEAR
        (
        CURDATE()) AS fy,
        QUARTER (
        CURDATE()) AS fy_quarter UNION
    SELECT YEAR
        (
        DATE_ADD( CURDATE(), INTERVAL 3 MONTH )) AS fy,
        QUARTER (
        DATE_ADD( CURDATE(), INTERVAL 3 MONTH )) AS fy_quarter UNION
    SELECT YEAR
        (
        DATE_ADD( CURDATE(), INTERVAL 6 MONTH )) AS fy,
        QUARTER (
        DATE_ADD( CURDATE(), INTERVAL 6 MONTH )) AS fy_quarter UNION
    SELECT YEAR
        (
        DATE_ADD( CURDATE(), INTERVAL 9 MONTH )) AS fy,
        QUARTER (
        DATE_ADD( CURDATE(), INTERVAL 9 MONTH )) AS fy_quarter
    ) AS `quarters`
GROUP BY
    `quarters`.fy,
    `quarters`.fy_quarter"

使用迭代

for(...) {
    run SQL query
}

for(...) {
    using the previous output and run SQL query again
}

for(...) {
    using the previous output and run SQL query again
}

for(...) {
    using the previous output and run SQL query again
}

Finally I have my output

标签： mysqlperformanceiteration

Answer 1

确实，与 DB 交互成本更高。因此人们更喜欢将几个查询组合成一个查询来优化性能。为了更好的代码，为什么不在注释中解释逻辑。