问题描述
mysql查询时间过长
问题出现的环境背景及自己尝试过哪些方法
加过数据表的索引后,时间缩短了很多,但任然不是很理想
相关代码
SELECT
count(
DISTINCT (auo.user_wxopenid)
) AS countPeople,
count(DISTINCT(auo.session_id)) AS count
FROM
analysis_user_operation auo
LEFT JOIN wechat we ON we.wxopenid = auo.user_wxopenid
LEFT JOIN analysis_user_operation_new auon ON auon.user_wxopenid = auo.user_wxopenid
WHERE
1 = 1
AND (
(
auo.resource_type = 0
AND auo.resource_id = 20
)
OR (
auo.resource_type = 2
AND auo.resource_id IN (
SELECT
id
FROM
museum_exhibit_audio
WHERE
exhibit_id IN (
SELECT
id
FROM
museum_exhibit
WHERE
area_id IN (
SELECT
id
FROM
museum_area
WHERE
room_id = 20
)
)
)
)
OR (
auo.resource_type = 1
AND auo.resource_id IN (
SELECT
id
FROM
museum_exhibit
WHERE
area_id IN (
SELECT
id
FROM
museum_area
WHERE
room_id = 20
)
)
)
OR (
auo.resource_type = 4
AND auo.resource_id = 20
)
OR (
auo.resource_type = 5
AND auo.resource_id IN (
SELECT
id
FROM
museum_area
WHERE
room_id = 20
)
)
)
AND auo.operate_starttime >= '2018-10-01 00:00:00'
AND auo.operate_starttime < '2018-10-30 23:59:59'
ORDER BY
auo.gmt_create
sql explain的结果:
###
数据表已添加的索引:
你期待的结果是什么？
业务需求大概是求访问该room的访问人数和访问次数(访问该room下的音频,展品,展区都算),
希望大神能帮忙看看sql查询慢在什么地方,如何优化sql能使其查询时间能在1~3秒左右,现为7~10秒.
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
