这个语句如何提高查询速度,数据库,综合交流区,鱼C论坛

yuanbb0820 发表于 2024-8-28 09:26:13

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

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 发表于 2024-8-28 09:26:36

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

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 发表于 2024-8-28 09:41:20

{:5_107:}

yuanbb0820 发表于 2024-8-28 09:46:10

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 发表于 2024-8-28 09:46:14

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

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

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

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

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

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

月暮日出 发表于 2024-8-28 17:39:35

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

yuanbb0820 发表于 2024-8-30 09:10:43

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

数据库的

页: [1]

鱼C论坛's Archiver

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