请问除了加索引以外，还可以如何优化 sql 呢？

需要查询出总数、成功数和失败数渲染到页面，43W 条数据中查询出 600 多条需要查询 9s 以上。后来给所有 where 以及 order by 的字段加上了索引，用 EXPLAIN 检查发现创建了临时表和全表扫描，遂把 distinct 去掉，但效果还是不理想。想请教下还应该如何进行优化呢？
初始 sql 如下：
SELECT
t.business_no AS businessNo,
t.service_type AS serviceType,
(
SELECT
count(d.datagram_id)
FROM
t_issue_plat_datagram d
WHERE
t.business_no = d.business_no
) AS limitCount,
(
SELECT
count(b.datagram_id)
FROM
t_issue_plat_datagram b
WHERE
t.business_no = b.business_no
AND b.batch_status = '2'
) AS sucessCount,
(
SELECT
count(c.datagram_id)
FROM
t_issue_plat_datagram c
WHERE
t.business_no = c.business_no
AND c.batch_status = '3'
) AS falseCount
FROM
(
SELECT DISTINCT
g.business_no,
g.service_type
FROM
t_issue_plat_datagram g
WHERE
1 = 1
ORDER BY
g.updated_date DESC
) t
修改后的 sql:
SELECT
t.business_no AS businessNo,
t.service_type AS serviceType,
(
SELECT
count(d.datagram_id)
FROM
t_issue_plat_datagram d
WHERE
t.business_no = d.business_no
) AS limitCount,
(
SELECT
count(b.datagram_id)
FROM
t_issue_plat_datagram b
WHERE
t.business_no = b.business_no
AND b.batch_status = '2'
) AS sucessCount,
(
SELECT
count(c.datagram_id)
FROM
t_issue_plat_datagram c
WHERE
t.business_no = c.business_no
AND c.batch_status = '3'
) AS falseCount
FROM
t_issue_plat_datagram t
GROUP BY
t.business_no
ORDER BY
t.updated_date

select

Where

business_no

from

6 条回复 • 2019-01-23 17:27:51 +08:00

Rush9999

2019-01-23 15:27:01 +08:00

SELECT
businessNo,
serviceType,
SUM(s.num),
SUM(
CASE
WHEN s.stauts = '2' THEN
s.num
END
),
SUM(
CASE
WHEN s.stauts = '3' THEN
s.num
END
)
FROM
(
SELECT
t.business_no AS businessNo,
t.service_type AS serviceType,
t.batch_status AS stauts,
COUNT(t.datagram_id) AS num,
updated_date AS date
FROM
t_issue_plat_datagram t
GROUP BY
t.business_no,
t.batch_status
) s
GROUP BY
s.businessNo
ORDER BY
s.date

nulihuxi

2019-01-23 15:44:28 +08:00

@Rush9999 十分感谢，查询时间从 10s 减少至 1.5s

lxy42

2019-01-23 16:33:04 +08:00

SELECT
business_no,
service_type,
COUNT(1) as limitCount,
COUNT(CASE WHEN batch_status = '2' THEN 1 ELSE 0 END) as successCount,
COUNT(CASE WHEN batch_status = '3' THEN 1 ELSE 0 END) as falseCount,
FROM t_issue_plat_datagram
GROUP BY business_no
ORDER BY updated_date

楼主试试这个，不知道是否符合查询需求

nulihuxi

2019-01-23 16:54:24 +08:00

@lxy42 感谢回复！查询出的条数是对的，不过 limitCount、successCount、falseCount 的值是一样的

lxy42

2019-01-23 16:59:35 +08:00

SELECT
business_no,
service_type,
COUNT(1) as limitCount,
COUNT(CASE WHEN batch_status = '2' THEN 1 ELSE NULL END) as successCount,
COUNT(CASE WHEN batch_status = '3' THEN 1 ELSE NULL END) as falseCount,
FROM t_issue_plat_datagram
GROUP BY business_no
ORDER BY updated_date

这个呢？小改了一下。

nulihuxi

2019-01-23 17:27:51 +08:00

@lxy42 这个非常 ok （去掉 as falseCount 后的逗号）：）