这个语句如何提高查询速度

yuanbb0820

SELECT
        subq.groupname,
        --subq.members,
        --subq.billno,
        --subq.optime,
        subq.node,
        subq.cop,
        subq.brand,
        --subq.billtype,
        --subq.billstyle,
        subq.num_bill,
        subq.times_ssc_approve,
        subq.times_ssc_reject,
        subq.times_ssc_cancel,
        round(subq.times_ssc_approve + subq.times_ssc_reject - subq.times_ssc_cancel, 0) AS times_ssc_final,
        CASE
                WHEN subq.times_ssc_reject = 0 THEN NULL
                ELSE round((subq.times_ssc_reject / subq.num_bill) * 100, 2)
        END AS reject_percentage,
        subq.times_bus_reject
FROM
        (
        SELECT
                d.name AS groupname,
                --a.billno billno,
                --us.user_name AS members,
                --SUBSTR(a.operatetime,0,10)  AS optime,
                sn.name AS node,
                org.shortname as cop,
                ad2.name AS brand,
                --c.BILLTYPENAME as billtype,
                --c.parentbilltype as billstyle,
                COUNT(DISTINCT CASE WHEN (a.actiontype = 'sscapprove' OR (a.actiontype = 'sscreject' AND a.BEGINSTATE <> 'sscapprove')) THEN a.billno END) AS num_bill,
                COUNT(CASE WHEN a.actiontype = 'sscapprove' THEN a.billno END) AS times_ssc_approve,
                COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE <> 'sscapprove') THEN a.billno END) AS times_ssc_reject,
                COUNT(CASE WHEN (a.actiontype = 'un-sscapprove' AND a.BEGINSTATE <> 'notake') THEN a.billno END) AS times_ssc_cancel,
                COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE = 'sscapprove') THEN a.billno END) AS times_bus_reject
        FROM
                ncc.ssctp_flowpath a
        LEFT JOIN ncc.bd_billtype c ON
                c.pk_billtypeid = a.pk_tradetype
        LEFT JOIN ncc.ssctp_workinggroup d ON
                d.pk_workinggroup = a.pk_sscgroup
        LEFT JOIN ncc.org_orgs org ON
                org.pk_org = a.pk_org
        LEFT JOIN org_adminorg ad ON 
                ad.name = org.name
        LEFT JOIN org_adminorg ad2 ON 
                ad2.pk_adminorg = ad.pk_fatherorg
        LEFT JOIN ncc.sm_user us ON
                us.cuserid = a.operater
        LEFT JOIN ncc.ssctp_node sn ON
                sn.pk_sscnode = a.pk_sscnode
        WHERE
                a.operatetime >= '2024-01-01 00:00:00'
                AND a.operatetime <= '2024-08-31 23:59:59'
                AND org.name <> '虚拟店'
                --AND org.name = '杭州和诚之宝汽车销售服务有限公司'
                --AND org.def12 = '商用车品线'
                AND d.name = '费用核算组'
                AND c.billtypename IN ('差旅费用报销单','业务招待费用报销单')
        GROUP BY
                d.name,
                --a.billno,
                --us.user_name,
                sn.name,
        --SUBSTR(a.operatetime,0,10)
        --sn.name
                org.shortname,
                ad2.name ) subq
        --c.BILLTYPENAME 
        --c.parentbilltype 
WHERE
        num_bill <> 0
        OR times_ssc_reject <> 0;

FishC

要提高这个查询语句的速度，可以从以下几个方面入手：

1. **索引优化**：确保在 `WHERE` 子句中使用的列上创建了适当的索引，例如 `a.operatetime` 和 `a.pk_sscnode`。此外，连接条件中的列也应该有索引，例如 `a.pk_tradetype`、`a.pk_sscgroup`、`a.pk_org` 等。

2. **减少子查询**：如果可能，尝试将子查询转换为联接，以减少查询的复杂性。

3. **选择性字段**：只选择需要的字段，避免选择不必要的字段。

4. **优化聚合函数**：确保聚合函数（如 `COUNT`）的使用是必要的，并且在索引列上进行。

5. **避免重复计算**：将重复计算的部分提取出来，避免在查询中多次计算相同的值。

以下是优化后的查询示例：

SELECT
    d.name AS groupname,
    sn.name AS node,
    org.shortname as cop,
    ad2.name AS brand,
    COUNT(DISTINCT CASE WHEN (a.actiontype = 'sscapprove' OR (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove')) THEN a.billno END) AS num_bill,
    COUNT(CASE WHEN a.actiontype = 'sscapprove' THEN a.billno END) AS times_ssc_approve,
    COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove') THEN a.billno END) AS times_ssc_reject,
    COUNT(CASE WHEN (a.actiontype = 'un-sscapprove' AND a.BEGINSTATE != 'notake') THEN a.billno END) AS times_ssc_cancel,
    COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE = 'sscapprove') THEN a.billno END) AS times_bus_reject,
    round(COUNT(CASE WHEN a.actiontype = 'sscapprove' THEN a.billno END) + COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove') THEN a.billno END) - COUNT(CASE WHEN (a.actiontype = 'un-sscapprove' AND a.BEGINSTATE != 'notake') THEN a.billno END), 0) AS times_ssc_final,
    CASE
        WHEN COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove') THEN a.billno END) = 0 THEN NULL
        ELSE round((COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove') THEN a.billno END) / COUNT(DISTINCT CASE WHEN (a.actiontype = 'sscapprove' OR (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove')) THEN a.billno END)) * 100, 2)
    END AS reject_percentage
FROM
    ncc.ssctp_flowpath a
LEFT JOIN ncc.bd_billtype c ON
    c.pk_billtypeid = a.pk_tradetype
LEFT JOIN ncc.ssctp_workinggroup d ON
    d.pk_workinggroup = a.pk_sscgroup
LEFT JOIN ncc.org_orgs org ON
    org.pk_org = a.pk_org
LEFT JOIN org_adminorg ad ON 
    ad.name = org.name
LEFT JOIN org_adminorg ad2 ON 
    ad2.pk_adminorg = ad.pk_fatherorg
LEFT JOIN ncc.sm_user us ON
    us.cuserid = a.operater
LEFT JOIN ncc.ssctp_node sn ON
    sn.pk_sscnode = a.pk_sscnode
WHERE
    a.operatetime >= '2024-01-01 00:00:00'
    AND a.operatetime < '2024-12-31 23:59:59'
GROUP BY
    d.name, sn.name, org.shortname, ad2.name;

通过这些优化措施，可以显著提高查询的执行速度。

以上回复来自 FishC AI 小助理，如未能正确解答您的问题，请继续追问。

sunshine_8205

yuanbb0820

FishC 发表于 2024-8-28 09:26
要提高这个查询语句的速度，可以从以下几个方面入手：

1. **索引优化**：确保在 `WHERE` 子句中使用的列 ...

你优化完的语句确实速度提升了，但是在你的基础上在where里加上其他条件，速度又变的很慢，是为什么呢

SELECT
    d.name AS groupname,
    sn.name AS node,
    org.shortname as cop,
    ad2.name AS brand,
    COUNT(DISTINCT CASE WHEN (a.actiontype = 'sscapprove' OR (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove')) THEN a.billno END) AS num_bill,
    COUNT(CASE WHEN a.actiontype = 'sscapprove' THEN a.billno END) AS times_ssc_approve,
    COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove') THEN a.billno END) AS times_ssc_reject,
    COUNT(CASE WHEN (a.actiontype = 'un-sscapprove' AND a.BEGINSTATE != 'notake') THEN a.billno END) AS times_ssc_cancel,
    COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE = 'sscapprove') THEN a.billno END) AS times_bus_reject,
    round(COUNT(CASE WHEN a.actiontype = 'sscapprove' THEN a.billno END) + COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove') THEN a.billno END) - COUNT(CASE WHEN (a.actiontype = 'un-sscapprove' AND a.BEGINSTATE != 'notake') THEN a.billno END), 0) AS times_ssc_final,
    CASE
        WHEN COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove') THEN a.billno END) = 0 THEN NULL
        ELSE round((COUNT(CASE WHEN (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove') THEN a.billno END) / COUNT(DISTINCT CASE WHEN (a.actiontype = 'sscapprove' OR (a.actiontype = 'sscreject' AND a.BEGINSTATE != 'sscapprove')) THEN a.billno END)) * 100, 2)
    END AS reject_percentage
FROM
    ncc.ssctp_flowpath a
LEFT JOIN ncc.bd_billtype c ON
    c.pk_billtypeid = a.pk_tradetype
LEFT JOIN ncc.ssctp_workinggroup d ON
    d.pk_workinggroup = a.pk_sscgroup
LEFT JOIN ncc.org_orgs org ON
    org.pk_org = a.pk_org
LEFT JOIN org_adminorg ad ON 
    ad.name = org.name
LEFT JOIN org_adminorg ad2 ON 
    ad2.pk_adminorg = ad.pk_fatherorg
LEFT JOIN ncc.sm_user us ON
    us.cuserid = a.operater
LEFT JOIN ncc.ssctp_node sn ON
    sn.pk_sscnode = a.pk_sscnode
WHERE
    a.operatetime >= '2024-01-01 00:00:00'
    AND a.operatetime <= '2024-08-15 23:59:59'
    AND org.name <> '虚拟店'
    AND d.name = '费用核算组'
        AND c.billtypename IN ('差旅费用报销单','业务招待费用报销单')
        AND sn.name <> '共享制单'
GROUP BY
    d.name, sn.name, org.shortname, ad2.name;

FishC

yuanbb0820 发表于 2024-08-28 09:46
你优化完的语句确实速度提升了，但是在你的基础上在wh ...

查询速度变慢的原因通常有以下几点，可以逐项检查和优化：

1. **缺乏索引**：
    - 新添加的条件字段是否有索引？没有索引的话，会导致全表扫描，影响查询速度。

2. **索引失效**：
    - 尽量避免在索引字段上进行函数操作或者使用不等于 (`!=`)，这可能导致索引失效。

3. **查询复杂度增加**：
    - 复杂的 `WHERE` 条件会增加查询的计算量。可以

以上回复来自 FishC AI 小助理，如未能正确解答您的问题，请继续追问。

月暮日出

这个代码是用在哪里的，应该不是在Python那个里面里面吧

yuanbb0820

月暮日出 发表于 2024-8-28 17:39
这个代码是用在哪里的，应该不是在Python那个里面里面吧

数据库的