gpt4 book ai didi

mysql - 这个多嵌套查询可以优化吗?

转载 作者:行者123 更新时间:2023-11-30 22:37:42 25 4
gpt4 key购买 nike

我有一个包含超过 10 万条记录的表,对于 300 个用户 (uid),这个查询最多需要 30 秒才能完成(在我的 Drupal 网站中)。

------------------------------------------------------------
|id |uid |lesson_id |game_id |score |duration |date |
------------------------------------------------------------
|1 |144 |65 |0 |70 |12303 |1392479414 |
|2 |145 |65 |0 |80 |12001 |1392474564 |
|3 |145 |65 |1 |60 |11056 |1392479467 |
|4 |144 |66 |1 |60 |12001 |1392479854 |
|... |... |... |... |... |... |... |
|... |... |... |... |... |... |... |
|... |... |... |... |... |... |... |
------------------------------------------------------------

我正在尝试获取每个 uid:

对于 game_id 为 0 的每个 lesson_id:

所有最好成绩的平均值,总时长,一个计数

对于每个 game_id 的每个 lesson_id (> 0):

所有最好成绩的总和,所有第一成绩的总和,总时长

SELECT 
q1.*,
q2.*,
q3.*
FROM (
SELECT
SUM(sq.max_score) / 71 AS avg_max_scores,
SUM(sq.total_duration) AS sum_total_duration,
SUM(1) AS assessment_count
FROM (
SELECT
up.*,
MAX(up.score) AS max_score,
SUM(up.duration) AS total_duration
FROM cdu_user_progress up
WHERE
(up.uid = '145') AND
(up.lesson_id IN ('65', '66', '67')) AND
(up.score > '-1')
GROUP BY up.lesson_id, up.game_id
) sq
WHERE (sq.game_id = '0')
) q1
INNER JOIN (
SELECT
SUM(sq.max_score) AS sum_max_scores_games,
SUM(sq.total_duration) AS sum_total_duration_games
FROM (
SELECT
up.*,
MAX(up.score) AS max_score,
SUM(up.duration) AS total_duration
FROM cdu_user_progress up
WHERE
(up.uid = '145') AND
(up.lesson_id IN ('65', '66', '67'))
AND (up.score > '-1')
GROUP BY up.lesson_id, up.game_id
) sq
WHERE (sq.game_id > '0')
) q2
INNER JOIN (
SELECT
SUM(q.first_score) / 71 AS avg_first_scores
FROM (
SELECT
sq.max_date AS max_date,
up.score AS first_score
FROM (
SELECT
up.*,
MIN(up.date) AS first_date,
MAX(up.date) AS max_date
FROM cdu_user_progress up
WHERE
(up.uid = '145') AND
(up.lesson_id IN ('65', '66', '67')) AND
(up.score > '-1') AND
(up.game_id = '0')
GROUP BY up.lesson_id
) sq
LEFT OUTER JOIN cdu_user_progress up ON up.lesson_id = sq.lesson_id AND up.game_id = sq.game_id AND up.date = sq.first_date
) q
) q3

解释输出:

id  select_type table   type    possible_keys                                           key                                                     key_len ref                     rows    Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1 PRIMARY <derived4> system NULL NULL NULL NULL 1
1 PRIMARY <derived6> system NULL NULL NULL NULL 1
6 DERIVED <derived7> ALL NULL NULL NULL NULL 71
7 DERIVED <derived8> ALL NULL NULL NULL NULL 71
7 DERIVED up ref cdu_user_progress(lesson_id, game_id) cdu_user_progress(lesson_id, game_id) 8 sq.lesson_id,sq.game_id 7
8 DERIVED up range cdu_user_progress(uid, lesson_id, level, game_id, ... cdu_user_progress(uid, lesson_id, level, game_id, ... 12 NULL 628 Using where
4 DERIVED <derived5> ALL NULL NULL NULL NULL 90 Using where
5 DERIVED up ref cdu_user_progress(uid, lesson_id, level, game_id, ... cdu_user_progress(uid, lesson_id, level, game_id, ... 4 678 Using where
2 DERIVED <derived3> ALL NULL NULL NULL NULL 90 Using where
3 DERIVED up ref cdu_user_progress(uid, lesson_id, level, game_id, ... cdu_user_progress(uid, lesson_id, level, game_id, ... 4 678 Using where

最佳答案

避免在 SQL 中嵌套请求,它会降低性能。如果你别无选择,制作一些带有索引的临时表,会快得多。

喜欢:

CREATE TABLE pop AS (
SELECT
up.*,
MAX(up.score) AS max_score,
SUM(up.duration) AS total_duration
FROM cdu_user_progress up
WHERE
(up.uid = '145') AND
(up.lesson_id IN ('65', '66', '67')) AND
(up.score > '-1')
GROUP BY up.lesson_id, up.game_id);

然后:

CREATE INDEX index_pop ON pop (game_id);

所以你的请求看起来像这样:

SELECT q1.*, q2.*, q3.* 
FROM (
SELECT
SUM(sq.max_score) / 71 AS avg_max_scores,
SUM(sq.total_duration) AS sum_total_duration,
SUM(1) AS assessment_count
FROM pop sq
WHERE (sq.game_id = '0')
) q1
INNER JOIN (
SELECT
SUM(sq.max_score) AS sum_max_scores_games,
SUM(sq.total_duration) AS sum_total_duration_games
FROM pop sq
WHERE (sq.game_id > '0')
) q2
INNER JOIN (
SELECT
SUM(q.first_score) / 71 AS avg_first_scores
FROM (
SELECT
sq.max_date AS max_date,
up.score AS first_score
FROM (
SELECT
up.*,
MIN(up.date) AS first_date,
MAX(up.date) AS max_date
FROM cdu_user_progress up
WHERE
(up.uid = '145') AND
(up.lesson_id IN ('65', '66', '67')) AND
(up.score > '-1') AND
(up.game_id = '0')
GROUP BY up.lesson_id
) sq
LEFT OUTER JOIN cdu_user_progress up ON up.lesson_id = sq.lesson_id AND up.game_id = sq.game_id AND up.date = sq.first_date
) q
) q3;

如果在更多表中削减请求,则可以大大减少请求执行时间。如果您不想保留它们,请记住将它们丢弃

关于mysql - 这个多嵌套查询可以优化吗?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32014028/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com